Non-spatial Editing Introduction

The non-spatial editing bundle supports three types of editing

  1. Standalone tables

    • These tables have no link to any entities and the user will be able to access all rows, with the ability to create, update and delete rows.

    • These will generally be lookup tables, but could also be used for creating, updating and deleting non-spatial entities.

    • They will be presented to the user as a grid.

    • Initially, editing of the values for an individual record will be done in a separate window, with a form showing the values to edit, rather than being performed directly within the grid because of limitations in the current grid editing capabilities in Ext 3.4, specifically relating to cascading parameters (list parameters where the list content depends on values being set in other fields).

    • There can be two types of client configurations for this editor:

      • One that provides the user with a list box so they can choose what edit to perform, for editing lookup tables.

      • And another where the editor displayed in the view is pre-set (via a config option) and the view will then only display rows for that edit.

  2. Related tables

    • These are tables that are related to an entity in a one to many relationships.

    • The content for display will change based on the currently selected entity.

    • They will be presented to the user as a grid.

    • These are tables that can contain zero or more rows that relate to the currently selected entity (spatial or non-spatial).

    • There can be two types of configuration for this editor:

      • One that provides the user with a list box so they can choose what edit to perform if there is more than one editor configured for the current entity.

      • And another is where the editor displayed in the view is pre-set (via a config option) and the view will then only display the rows for that edit that are associated with the currently selected entity. This would be used to create a more customised editing client, where for example there are a number of tabs available that each corresponds to editing a different part of the current entity.

  3. Entity tables

    • These are tables that are related to an entity in a one-to-one relationship.

    • They will be presented to the user as a form.

    • These are tables that can contain zero or one rows that relate to the currently selected entity (spatial or non-spatial).

    • To the user there will be no difference if there exists a corresponding row or not, beyond the fact that if there is a corresponding row the content of that row will be displayed, as opposed to empty (or default) values being displayed if the corresponding row does not exist. Other than that the user will have the option to change the values and submit the results. On the server if the corresponding row exists it will be updated, if it does not exist it will be created.

    • When the source entity is a spatial entity this type of editor will be a related non-spatial (this should NOT be the table underlying the spatial entity itself, unless you know what you're doing, and if you're using ArcSDE they almost certainly should NOT be, because of versioning) that can be linked to the entity via a one-to-one attribute join. If the entity is a spatial entity and direct create, update and delete operations are required then the spatial editing bundle should be used.

    • When the source entity is a non-spatial entity this type of editor can be either a related non-spatial table or a table that directly represents the entity. Since there is no concept of a non-spatial entity-specific table in Weave that corresponds to the spatial table (or layer) for a spatial entity practically there is no difference between a related non-spatial table and a table that directly represents the entity.

There is also the option to create an entity-specific edit panel that combines entity and related edits, generally representing a single entity table and one or more related tables.

Configuration

Client

Simple grid editing

There are a number of client panels available, the first and simplest is the "simple edit grid", which provides a panel similar to the data grid, in that it shows a grid of records and a list box to allow switching between different "editors".

From there the user can select and edit a single row.

The only config option for the simple grid panel is "entity" to restrict the panel to only showing "related" editors associated with the indicated entity.
type to restrict the panel to only showing editors of the specified type ("table" and "related" are the available options, more on those later).
Finally edit is also available to restrict the display to a single specific edit config, or to refine the edits included when one of the other options is used.

If none of entitytype or edit are specified then the result would be the same as if type were set to "related".

Example simple editor grid showing only 'table' type editors
<view id="editor.panel.simplegrid"> <label>Tables</label> <location>center</location> <type>table</type> </view>



Example simple editor grid showing only 'related' type editors
<view id="editor.panel.simplegrid"> <label>Tables</label> <location>center</location> <type>related</type> </view>



Example simple editor grid showing only editors associated with the 'property' entity
<view id="editor.panel.simplegrid"> <label>Property</label> <location>center</location> <entity>property</entity> </view>



Example simple editor grid showing only the 'edit.property' editor



Example simple editor grid showing only a subset of available editors



Example combined editor based on a single edit configuration, that allows viewing and editing records on the fly

Simple entity editing

In addition to the simple editor grid, there is also the editor panel, this panel is intended to support editing of an entity editor in a form (along with additional related editors)

Example entity editor form

In its simplest form above the view presents a form for a single edit (the panel can only be setup to edit a single entity). In addition to this you can also specify additional edit tag to add more forms or grids to the view, e.g.

Extended entity editor form

In this example the edit.property_owners editor is a related editor config for the property entity (the edit.property editor is an entity editor config for properties), and so the editor panel will display the property form and then the property owner's grid.

Server

To have something to edit a server editor config must be created, and currently, there are three different types of edits that you can configure (as previously mentioned).

Direct Table Editing

The most basic edit you can create is a 'table' edit, which directly represents a single table where the users can edit all rows in the table.

Example table editor config

In this example we've created a table editor and the user will be able to select it from the list using the name 'Action'. The table the editor will be updating is the 'lutAction' table in the 'ora-prod' database, and the table must have a column called 'Action_ID' that contains the unique keys for the rows in the table.

Note that you don't need to specify the key column if the table contains a single primary key column, Weave will determine that and use that column to identify rows.

By default, the editor in the client will display all columns for the user, and allow them to edit all columns (except the key column). You can specify which columns you want to expose by adding 'parameter' tags to the editor config (in a similar manner to the spatial editing configuration, and the attribute search configuration), this also allows you to explicitly specify how you want Weave to expose the column to the user, for example, if you need to set a list of values that the user can enter for a particular column since Weave will just create a basic representation of the columns if they're not explicitly specified.

Note, you can create a default configuration for a table from the osgi console using the 'editor' command, e.g.

osgi> editor conf ora_prod lutAction

will produce something like:

lutAction example conf

which you can cut and paste into your config file and update to reflect the changes you'd like, for example changing the landstatus_id so that it selects the value from a list.

Looking up land status

It's also possible to have Weave generate the parameters and just override those that you want to change in the config, rather than having to specify each and every parameter if you just want to change one, by setting 'parameters' to true in the config.

Entity Table Editing

You can also define an editor that represents a non-spatial entity

Sample Entity Editor Config

Here we're setting up an entity editing configuration, you can still use the 'editor conf' command to generate the base configuration, but then you need to change the type from 'table' to 'entity' and set the 'entity' attribute to the name of the entity that the table rows represent.

The final type of editor is the related table editor, this allows you to edit rows in a table that are related in a one-to-many relationship to an entity.

This is a simple related table editor. In this example, we're editing Incident records that are related to the Document entity. We need to define two keys for a related table, one that uniquely identifies the row to be edited, the 'key' attribute, which is optional and will be set if there's a single column primary key on the table, but there's also the 'entitykey' column, this column should contain the values that link the records in this table to the rows in the parent entity table.

Note that if you manually specify the parameters for a related editor you must specify the value for the entitykey parameter as the formula entitykey(), plus you should also set it as readonly on update and hidden.

This will ensure that when the record is written the value for the column that links to the parent entity will have the correct value, i.e. the key value of the selected parent entity.

Once the user selects an entity they will be able to edit all related records that contain the same 'entitykey' value as the selected entities key value.

Autogenerated values

Some parameters are autogenerated by the database and for some databases, nothing needs to be configured for this to happen (aside from specifying that the column is autogenerated), but some databases require additional information in some circumstances, for example, Oracle and Postgres use something they call Sequences to generate a sequence of values, which is where the value to be inserted into the database column comes from. If you're trying to create a record that contains a column with a value that's supposed to be generated based on a sequence then you need to specify the name of the sequence that the database should use when creating the record, which can be done by setting the sequence attribute for the parameter.

Setting a sequence for a parameter

Enabling and Disabling create, update and/or delete

As of Weave 2.6.5, or version 1.23.5 of the com.cohga.server.editor plugin, it's possible to declare that a particular edit configuration supports, or does not support, create, update and/or delete operations.

By default, all three operations are allowed for a given edit configuration, but you can disable create, update and/or delete for a specific edit by setting the creatable, updatable, or deletable flags (you can also use create, update and delete which are equivalent settings).

Disabling creation and deletion for a given edit config

Adding additional Where clause

As of Weave 2.6.9 It's possible to specify additional where clauses for table and related entities, to restrict what rows are available for editing.

The format of the where tag for non-spatial editing is different from there were clauses in the other configurations and follows the format of either of the three following formats, where "columnname" is the name of the column in the table to filter on, "operator" is one of the supported comparison operators, equal, notequal, lessthan, moreorequal, greaterthan, lessorequal, like, notlike, null, notnull, between, notbetween, in or notin (if not set equal will be used).

operator is "equal", "notequal", "lessthan", "moreorequal", "greaterthan", "lessorequal", "like", "notlike", "in" or "notin"

or

operator is either "between" or "notbetween"

or

operator is either "null" or "notnull"

e.g

The column attribute can either be a simple name and Weave will determine which table it relates to, which is easy if you're only editing a single table with no joins, e.g. column="state".

Or, if there are multiple tables because you're joining additional tables to the one you're editing, then you can specify the column name in the format of "tablename.columnname", e.g. column="lut_states.state".
Note that in this situation you can also specify the table and column separately using two attributes, e.g. table="lut_states" column="state".

Finally, if you also need to specify the schema for the table to disambiguate the column then you can specify the column as "schemaname.tablename.columnname", e.g. column="dbo.lut_states.state".
Note that in this situation you can also specify the schema, and table, as separate attributes, e.g. schema="dbo" table="lut_states" column="state"

Parameters substitution

User attribute substitution is available for the value attribute in the where tag, so you can filter rows dynamically based on a user-related attribute, e.g.

Note that if multiple values are returned for the attribute then they will be combined using an or clause, and if you know that there may be a lot of values returned it could be worth using the in operator, in reality, it may be that only equal, notequal, in and notin are useful operators when using user attribute substitution.

Also, if no attributes are returned then currently (unless someone can suggest a reason otherwise) Weave will ensure that no rows will be returned.