[Mapbender-commits] r8628 - trunk/mapbender/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2013-05-26 01:43:56 -0700 (Sun, 26 May 2013)
New Revision: 8628

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql
Log:
Extent mapbenders information modell for mb_user, mb_group, termsofuse timestamps to allow differential syncing of dynamically built metadata with e.g. ckan catalogues

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql	2013-05-23 15:25:13 UTC (rev 8627)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.3_to_2.7.4_pgsql_UTF-8.sql	2013-05-26 08:43:56 UTC (rev 8628)
@@ -180,6 +180,64 @@
 );
 ALTER TABLE mb_metadata_keyword
   OWNER TO postgres;
+--extent mapbenders information modell for mb_user, mb_group, termsofuse timestamps to allow differential syncing of dynamically built metadata with e.g. ckan catalogues 
+--mb_user 
+--first creation
+ALTER TABLE mb_user ADD COLUMN timestamp_create timestamp without time zone;
+ALTER TABLE mb_user ALTER COLUMN timestamp_create SET DEFAULT now();
+UPDATE mb_user SET timestamp_create = now() WHERE timestamp_create IS NULL;
+ALTER TABLE mb_user ALTER COLUMN timestamp_create SET NOT NULL;
+--last changed
+ALTER TABLE mb_user ADD COLUMN timestamp timestamp without time zone;
+UPDATE mb_user SET timestamp = now() WHERE timestamp IS NULL;
+ALTER TABLE mb_user ALTER COLUMN timestamp SET DEFAULT now();
+ALTER TABLE mb_user ALTER COLUMN timestamp SET NOT NULL;
+--trigger function for all tables, that have a timestamp column!
+CREATE OR REPLACE FUNCTION update_timestamp_column()
+  RETURNS trigger AS
+$BODY$
+BEGIN
+   NEW.timestamp = now(); 
+   RETURN NEW;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION update_timestamp_column()
+  OWNER TO postgres;
+--trigger itself
+ CREATE TRIGGER update_mb_user_timestamp BEFORE UPDATE
+        ON mb_user FOR EACH ROW EXECUTE PROCEDURE 
+        update_timestamp_column();
+--mb_group
+ALTER TABLE mb_group ADD COLUMN timestamp_create timestamp without time zone;
+ALTER TABLE mb_group ALTER COLUMN timestamp_create SET DEFAULT now();
+UPDATE mb_group SET timestamp_create = now() WHERE timestamp_create IS NULL;
+ALTER TABLE mb_group ALTER COLUMN timestamp_create SET NOT NULL;
+--last changed
+ALTER TABLE mb_group ADD COLUMN timestamp timestamp without time zone;
+UPDATE mb_group SET timestamp = now() WHERE timestamp IS NULL;
+ALTER TABLE mb_group ALTER COLUMN timestamp SET DEFAULT now();
+ALTER TABLE mb_group ALTER COLUMN timestamp SET NOT NULL;
+--trigger itself
+ CREATE TRIGGER update_mb_group_timestamp BEFORE UPDATE
+        ON mb_group FOR EACH ROW EXECUTE PROCEDURE 
+        update_timestamp_column();
+--termsofuse
+ALTER TABLE termsofuse ADD COLUMN timestamp_create timestamp without time zone;
+ALTER TABLE termsofuse ALTER COLUMN timestamp_create SET DEFAULT now();
+UPDATE termsofuse SET timestamp_create = now() WHERE timestamp_create IS NULL;
+ALTER TABLE termsofuse ALTER COLUMN timestamp_create SET NOT NULL;
+--last changed
+ALTER TABLE termsofuse ADD COLUMN timestamp timestamp without time zone;
+UPDATE termsofuse SET timestamp = now() WHERE timestamp IS NULL;
+ALTER TABLE termsofuse ALTER COLUMN timestamp SET DEFAULT now();
+ALTER TABLE termsofuse ALTER COLUMN timestamp SET NOT NULL;
+--trigger itself
+ CREATE TRIGGER update_termsofuse_timestamp BEFORE UPDATE
+        ON termsofuse FOR EACH ROW EXECUTE PROCEDURE 
+        update_timestamp_column();
+-- maybe some other information needed?