Overriding the Edit Object

It is possible to override the database object (accessed through an entity and a spatial mapper) that Weave will update when the user submits a spatial edit. This helps when working with views that can't be updated but the table that underlies the view can be.

The database view we will use in this section is defined as follows.

DROP VIEW IF EXISTS public.vw_graffiti;

CREATE VIEW public.vw_graffiti
AS
SELECT ID,
       DESCRIPTION,
	   REPORTEDBY,
	   TO_CHAR(REPORTEDON, 'DDth Month YYYY') AS reportedon,
	   GEOM
  FROM public.graffiti;
  
SELECT * 
  FROM public.vw_graffiti 
 WHERE id = 1;

The data for id = 1 visible in the view is as shown.

The view’s geometry_columns metadata entry is as follows.

SELECT * 
  FROM public.geometry_columns 
 WHERE f_table_name = 'vw_graffiti';

Firstly we create an entity and spatial mapper for the vw_graffiti view which our primary edits will be focused.

<entity:entity id="v.graffiti" label="Graffiti" />

<!-- Create mapper that points to the view -->
<mapper:mapper id="me.v.graffiti">
	<spatialengine>se.postgresql</spatialengine>
	<mapping entity="v.graffiti">
		<table>vw_graffiti</table>
		<key>id</key>
	</mapping>
</mapper:mapper>

To visualise this vw_graffiti based data we need to create a mapengine (me.view), modify the table of contents (toc.main) to include an entry for vw_graffiti , then add me.view to the client XML.

<!-- The mapengine for this view is as follows -->
<weave:mapengine id='me.view'>
    <spatialengine>se.postgresql</spatialengine>
    <format>image/png32</format>
    <layers>
		<layer id='l_view_graffiti_p' label='Graffiti (View)' layer='vw_graffiti' style='/weave/platform/workspace/SpatialDataEditing/graffiti_p.sld'/>
	</layers>
	<crs>EPSG:4326</crs>
</weave:mapengine>

<-- The updated table of contents -->
<toc:model id="toc.main">
		<entry id="l_selection.active"  label="Selections"        layer="_selection.active"   mapengine="me.selection"/>
		<entry id="l_vector_graffiti"   label="Graffiti"          layer="l_graffiti"          mapengine="me.single" entity="graffiti"   checked="true"/>		
		<entry id="l_view_graffiti"     label="Graffiti (View)"   layer="l_view_graffiti_p"   mapengine="me.view"   entity="v.graffiti" checked="true"/>
        <entry id="l_mapengine.osm_0"  	label="OpenStreetMap"     layer="0"                   mapengine="me.osm"/>
</toc:model>

The relevant part of the client XML which is modified to include the me.view mapengine is:

<view id="com.cohga.html.client.map.mapView">
  ...
  <mapEngine id="me.osm"/>
  <mapEngine id="me.single"/>
  <mapEngine id="me.view"/>
  <mapEngine id="me.selection"/>
  <toc ref="toc.main" />
  ...
</view>

The client should now look like this.

Now, an example of how the view cannot be used to insert a new graffiti record is shown as follows.

We can’t insert a new record because the reportedon column in the public.graffiti table is not directly accessible via the view.

To tell Weave to update a different table than vw_graffiti, an edit configuration needs to be created in which a spatialEngine, table and key are defined.

<edit:config id="view.edit">
	<label>Grafitti (view)</label>
	<entity>v.graffiti</entity> 				 <!-- this entity is backed by a DB view that can't be edited directly -->
	<spatialEngine>se.postgresql</spatialEngine> <!-- set the spatial engine that contains the table that should be edited instead, could be the same as the spatial mapper, but might not be -->
	<table>graffiti</table> 					 <!-- set the name of the table in the spatial engine that should be edited instead, this should be different than the spatial mapper, since that's the whole point of this -->
	<key>id</key> 								 <!-- set the column that uniquely identifies the rows in the table that should be used instead, generally this would be the same as the spatial mapper -->
	<description>Report graffiti for removal via view</description>
	<geometry>
	  <point minimum="1" maximum="1"/>
	</geometry>
	<parameter id="description" label="Description" controlType="textarea" column="description" />
	<!-- Hidden parameters to record recorder information -->
    <parameter id="reportedby">
      <hidden>true</hidden>
      <column>reportedby</column>
      <value>userid()</value>
      <updatable>false</updatable>
    </parameter>
    <parameter id="reportedon">
      <hidden>true</hidden>
      <column>reportedon</column>
      <value>datetime()</value>
      <updatable>false</updatable>
    </parameter>
</edit:config>

To create a new graffiti point using the view, first we set the Graffiti (View) entity active.

Then we can create a new graffiti point via the Graffiti (view) edit configuration as follows:

Checking the database shows that the record has been successfully written to the Graffiti table.

The inserted data is now visible via the view.

Editing via a view can also be implemented at the database level via what is known as an “INSTEAD OF” trigger.

An INSTEAD OF trigger, say for insert, is fired when Weave creates (inserts) a new entity through the view.

The trigger, when fired, could grab all the values supplied with the view’s attributes and write them to an associated table. It could also compute additional column values e.g. audit values, polygon areas, etc. (Once the INSTEAD OF trigger terminates, no values are written to the view.)

CREATE TRIGGER graffiti_bi 
BEFORE INSERT 
ON public.vw_graffiti 
FOR EACH ROW 
EXECUTE PROCEDURE insert_graffiti_record_with_audit();

-- With the procedure insert_graffiti_record_with_audit doing the actual processing.

CREATE OR REPLACE FUNCTION insert_graffiti_record_with_audit()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
AS
$$
BEGIN
  INSERT INTO public.graffiti (description,reportedby,reportedon,createdby,createdon)
		 VALUES(new.description,
				new.reportedby,
				new.reportedon,
				current_user,
				now() );
  RETURN NEW;
END;
$$

This example shows the power of combining Weave processing and/or database processing.

<< Previous

Next >>