{"type":"doc","content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"The database view we will use in this section is defined as follows.","type":"text"}]},{"type":"codeBlock","content":[{"text":"DROP VIEW IF EXISTS public.vw_graffiti;\n\nCREATE VIEW public.vw_graffiti\nAS\nSELECT ID,\n DESCRIPTION,\n\t REPORTEDBY,\n\t TO_CHAR(REPORTEDON, 'DDth Month YYYY') AS reportedon,\n\t GEOM\n FROM public.graffiti;\n \nSELECT * \n FROM public.vw_graffiti \n WHERE id = 1;","type":"text"}]},{"type":"paragraph","content":[{"text":"The data for ","type":"text"},{"text":"id = 1","type":"text","marks":[{"type":"code"}]},{"text":" visible in the view is as shown.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":836,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":836,"alt":"image-20240110-043333.png","id":"e14451e9-14a4-44e4-80fa-7988ccb9915f","collection":"contentId-2698086768","type":"file","height":243}},{"type":"caption","content":[{"text":"View Showing Select Columns From ","type":"text"},{"text":"public.graffiti.","type":"text","marks":[{"type":"code"}]}]}]},{"type":"paragraph","content":[{"text":"The view’s ","type":"text"},{"text":"geometry_columns ","type":"text","marks":[{"type":"code"}]},{"text":"metadata entry is as follows.","type":"text"}]},{"type":"codeBlock","content":[{"text":"SELECT * \n FROM public.geometry_columns \n WHERE f_table_name = 'vw_graffiti';","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":849,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":849,"alt":"image-20240110-011256.png","id":"c78db083-f095-4a4d-99bd-adcb7ec46a3a","collection":"contentId-2698086768","type":"file","height":239}},{"type":"caption","content":[{"text":"Geometry Metadata Entry for ","type":"text"},{"text":"vw_graffiti.","type":"text","marks":[{"type":"code"}]}]}]},{"type":"paragraph","content":[{"text":"Firstly we create an entity and spatial mapper for the ","type":"text"},{"text":"vw_graffiti","type":"text","marks":[{"type":"code"}]},{"text":" view which our primary edits will be focused.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n\n\n\n\tse.postgresql\n\t\n\t\tvw_graffiti

\n\t\tid\n\t\n","type":"text"}]},{"type":"paragraph","content":[{"text":"To visualise this ","type":"text"},{"text":"vw_graffiti","type":"text","marks":[{"type":"code"}]},{"text":" based data we need to create a mapengine (","type":"text"},{"text":"me.view","type":"text","marks":[{"type":"code"}]},{"text":"), modify the table of contents (","type":"text"},{"text":"toc.main","type":"text","marks":[{"type":"code"}]},{"text":") to include an entry for ","type":"text"},{"text":"vw_graffiti","type":"text","marks":[{"type":"code"}]},{"text":" , then add ","type":"text"},{"text":"me.view","type":"text","marks":[{"type":"code"}]},{"text":" to the client XML.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n\n se.postgresql\n image/png32\n \n\t\t\n\t\n\tEPSG:4326\n\n\n<-- The updated table of contents -->\n\n\t\t\n\t\t\t\t\n\t\t\n \n","type":"text"}]},{"type":"paragraph","content":[{"text":"The relevant part of the client XML which is modified to include the ","type":"text"},{"text":"me.view","type":"text","marks":[{"type":"code"}]},{"text":" mapengine is:","type":"text"}]},{"type":"codeBlock","content":[{"text":"\n ...\n \n \n \n \n \n ...\n","type":"text"}]},{"type":"paragraph","content":[{"text":"The client should now look like this.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":281,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":281,"alt":"toc_view.png","id":"f5e90d8a-47cb-40ab-b699-676a98fbad5e","collection":"contentId-2698086768","type":"file","height":281}},{"type":"caption","content":[{"text":"Client Showing View Layer in TOC.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Now, an example of how the view cannot be used to insert a new graffiti record is shown as follows.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":882,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":882,"alt":"image-20240110-043520.png","id":"7ea9718c-4b41-4bc7-9ace-010de3a7b8aa","collection":"contentId-2698086768","type":"file","height":301}},{"type":"caption","content":[{"text":"SQL Showing Inability to Insert a New Record via the View.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"We can’t insert a new record because the ","type":"text"},{"text":"reportedon","type":"text","marks":[{"type":"code"}]},{"text":" column in the ","type":"text"},{"text":"public.graffiti","type":"text","marks":[{"type":"code"}]},{"text":" table is not directly accessible via the view.","type":"text"}]},{"type":"paragraph","content":[{"text":"To tell Weave to update a different table than ","type":"text"},{"text":"vw_graffiti,","type":"text","marks":[{"type":"code"}]},{"text":" an edit configuration needs to be created in which a ","type":"text"},{"text":"spatialEngine","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"table","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"key","type":"text","marks":[{"type":"code"}]},{"text":" are defined.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n\tGrafitti (view)\n\tv.graffiti \t\t\t\t \n\tse.postgresql \n\tgraffiti

\t\t\t\t\t \n\tid \t\t\t\t\t\t\t\t \n\tReport graffiti for removal via view\n\t\n\t \n\t\n\t\n\t\n \n true\n reportedby\n userid()\n false\n \n \n true\n reportedon\n datetime()\n false\n \n","type":"text"}]},{"type":"paragraph","content":[{"text":"To create a new graffiti point using the view, first we set the ","type":"text"},{"text":"Graffiti (View)","type":"text","marks":[{"type":"strong"}]},{"text":" entity active.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"24da6107-ba9d-4733-a557-4019bd37ad14"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"center","width":343,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":343,"alt":"image-20240110-044914.png","id":"2c7e570a-05cc-4bdb-9a22-999e0f0b562e","collection":"contentId-2698086768","type":"file","height":166}},{"type":"caption","content":[{"text":"Via Right Mouse Click Menu","type":"text"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"center","width":342,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":342,"alt":"image-20240110-045107.png","id":"d0bb40d6-0ea6-4093-8ef6-e793655d213a","collection":"contentId-2698086768","type":"file","height":136}},{"type":"caption","content":[{"text":"Via Entity Pulldown","type":"text"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Then we can create a new graffiti point via the ","type":"text"},{"text":"Graffiti (view)","type":"text","marks":[{"type":"strong"}]},{"text":" edit configuration as follows:","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":516,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":516,"id":"eaeec71a-5989-4fa2-b26b-84ff3ba4c807","collection":"contentId-2698086768","type":"file","height":315}},{"type":"caption","content":[{"text":"Graffiti Created Using View Edit Configuration","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Checking the database shows that the record has been successfully written to the ","type":"text"},{"text":"Graffiti ","type":"text","marks":[{"type":"strong"}]},{"text":"table.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":952,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":953,"alt":"image-20240110-050012.png","id":"0506b815-05d7-4924-bc76-ae770923f718","collection":"contentId-2698086768","type":"file","height":272}},{"type":"caption","content":[{"text":"Graffiti Record Written to ","type":"text"},{"text":"public.graffiti","type":"text","marks":[{"type":"strong"}]},{"text":" Table.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The inserted data is now visible via the view.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"align-start","width":907,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":907,"alt":"image-20240110-050228.png","id":"ee0438c5-f149-4339-935b-fccf997df277","collection":"contentId-2698086768","type":"file","height":253}},{"type":"caption","content":[{"text":"Inserted Record Visible in View (note formatted ","type":"text"},{"text":"reportedon","type":"text","marks":[{"type":"strong"}]},{"text":" column).","type":"text"}]}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"Editing via a view can also be implemented at the database level via what is known as an “INSTEAD OF” trigger. ","type":"text"}]},{"type":"paragraph","content":[{"text":"An INSTEAD OF trigger, say for ","type":"text"},{"text":"insert,","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" is fired when Weave creates (","type":"text"},{"text":"inserts","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":") a new entity through the view.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.)","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"CREATE OR REPLACE FUNCTION vw_graffiti_tf()\n RETURNS TRIGGER \n LANGUAGE PLPGSQL\nAS\n$$\nBEGIN\n INSERT INTO public.graffiti (description,reportedby,reportedon,createdby,createdon)\n\t\t VALUES(new.description,\n\t\t\t\tnew.reportedby,\n\t\t\t\tnew.reportedon,\n\t\t\t\tcurrent_user,\n\t\t\t\tnow() );\n RETURN NEW;\nEND;\n$$;\n\nCREATE TRIGGER graffiti_bi \nINSTEAD OF INSERT\nON public.vw_graffiti \nFOR EACH ROW \nEXECUTE PROCEDURE vw_graffiti_tf();","type":"text"}]},{"type":"paragraph","content":[{"text":"This example shows the power of combining Weave processing and/or database processing.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"a09e2c30-3637-41f7-a009-4f1e1725010f"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"<< Previous","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/WUG/pages/2698086505"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","marks":[{"type":"alignment","attrs":{"align":"end"}}],"content":[{"text":"Next >>","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/WUG/pages/2698086999"}}]}]}]}]}]}],"version":1}