Update and fix SIM Creation Scripts

[p-a-s-c-a-l]

Update and fix several database creation scripts e.g.

• the dynamic catalogue statements (see https://github.com/switchonproject/cids-custom-switchon/issues/36)
• new default tag groups and tags
• general changes to the database model

[p-a-s-c-a-l]

1. Write the function in _public.cs_dynamic_childrenhelper
2. Refresh dynamic children helper functions in schema csdc by executing the function select public.cs_refresh_dynchilds_functions();

[p-a-s-c-a-l]

Updates script https://github.com/switchonproject/cids-custom-switchon/issues/100#issuecomment-58197784 to

INSERT INTO taggroup (description, "name") 
    VALUES ('The publish type is used to determine how an imported file has to be postprocessed in order to publish it to an Andvanced Data Repository.', 'publish type');

INSERT INTO tag (description, "name", taggroup) 
    VALUES ('The imported representation is published as WMS or WFS Layer to a Geoserver instance. Supported file types are for example SHP and GeoTIFF.', 'Geoserver', (SELECT id FROM taggroup WHERE "name" = 'publish type'));

INSERT INTO tag (description, "name", taggroup) 
    VALUES ('The imported representation is published to a THREDDS Data Server. A supported file type is for example NetCDF.', 'THREDDS', (SELECT id FROM taggroup WHERE "name" = 'publish type'));

INSERT INTO taggroup (description, "name") 
    VALUES ('The upload status represents a transient property that temporarily stores the status of the upload process to an Advanced Data Repository.', 'upload status');

INSERT INTO tag (description, "name", taggroup) 
    VALUES ('The newly imported representation is currently being uploaded to an Advanced Data Repository like Geoserver or THREDDS.', 'uploading', (SELECT id FROM taggroup WHERE "name" = 'upload status'));

INSERT INTO tag (description, "name", taggroup) 
    VALUES ('The upload of the newly imported representation to an Advanced Data Repository like Geoserver or THREDDS has been completed successfully.', 'finished', (SELECT id FROM taggroup WHERE "name" = 'upload status'));

INSERT INTO tag (description, "name", taggroup) 
    VALUES ('The upload of the newly imported representation to an Advanced Data Repository like Geoserver or THREDDS has failed.', 'failed', (SELECT id FROM taggroup WHERE "name" = 'upload status'));

[p-a-s-c-a-l]

cs_ sequences broken. Adjust with

-- Function: adjust_seqences(text)

-- DROP FUNCTION adjust_seqences(text);

CREATE OR REPLACE FUNCTION adjust_seqences(namespace text)
  RETURNS text AS
$BODY$
 DECLARE
  rec           record;
  startval     bigint;
  sql            text;
  seqname  text;
 BEGIN
  FOR rec in EXECUTE 'SELECT n.nspname,
       c.relname,
       a.attname,
       SUBSTRING(d.adsrc FROM E''nextval'||E'\\\\'||'(''''([^'''')]+)'''''') AS seqname
FROM pg_attrdef   AS d
JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
JOIN pg_class     AS c ON c.oid = d.adrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = '''||namespace||'''
AND adsrc LIKE ''nextval(''''%''' LOOP

    seqname := rec.seqname;
    sql := 'select max("'|| rec.attname ||'") + 1 from "' ||rec.nspname||'"."'|| rec.relname||'"';
    EXECUTE sql INTO startval;
    IF startval IS NOT NULL THEN
      sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' || startval;
      EXECUTE sql;
      RAISE NOTICE '%', sql;
    END IF;
  END LOOP;
  RETURN 'OK';
 END;
 $BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100; 

[p-a-s-c-a-l]

Update tags of tag group protocol according to http://sourceforge.net/p/geoportal/feature-requests/18/

[p-a-s-c-a-l]

Metadata creation date has a wrong type. Update with:

ALTER TABLE metadata ALTER creationdate SET DATA TYPE timestamp without time zone;

[ci-cismet-de]

Integrated branch 'bug/22-fix-database-scripts (e504c542f8af168581d611c95190099ddd9e7054)' into 'dev (959aad93d71650ce52cca9be567b433b892d8939)' -- Build 21

[p-a-s-c-a-l]

update creation script with https://github.com/switchonproject/cids-custom-switchon-server/blob/dev/src/model/SIM_relational_model_schema.bat