Changeset 1489 for trunk/LATMOS-Accounts/bin

Timestamp:

12/01/15 09:25:35 (9 years ago)

Author:

nanardon

Message:

Adding employment object

File:

• trunk/LATMOS-Accounts/bin/la-sql-upgrade.in (modified) (1 diff)

trunk/LATMOS-Accounts/bin/la-sql-upgrade.in

@@ -714 +714 @@
             ALTER TABLE objects ADD COLUMN modifiedby text;
             },
+        ],
+    },
+    {
+        ver => 15,
+        sql => [
+            q{
+            CREATE TABLE "employment"
+            (
+            name text NOT NULL,
+            "user" text NOT NULL,
+            firstday date NOT NULL default now(),
+            lastday  date,
+            CONSTRAINT employment_pkey PRIMARY KEY (name),
+            CONSTRAINT employment_okey_uniq UNIQUE (ikey),
+            CONSTRAINT employment_user_fkey FOREIGN KEY ("user")
+                REFERENCES "user" (name) MATCH SIMPLE
+                ON UPDATE CASCADE ON DELETE CASCADE
+            )
+            INHERITS (objects);
+            CREATE INDEX employment_exported_idx
+            ON employment USING btree (exported);
+            CREATE UNIQUE INDEX employment_rev_idx
+            ON employment USING btree (rev);
+
+            CREATE OR REPLACE FUNCTION employment_attr_update_ref()
+            RETURNS trigger AS
+            $BODY$begin
+
+            IF (TG_OP != 'INSERT') then
+            update "employment" set date = now() where "employment".ikey = old.okey;
+            end if;
+            IF (TG_OP != 'DELETE') then
+            update "employment"  set date = now() where "employment".ikey = new.okey;
+            end if;
+
+            IF (TG_OP = 'DELETE') then
+            return old;
+            ELSE
+            return new;
+            END IF;
+
+            END;$BODY$
+            LANGUAGE plpgsql VOLATILE
+            COST 100;
+            },
+            q{
+            CREATE TABLE employment_attributes
+            (
+            CONSTRAINT employment_attributes_pkey PRIMARY KEY (attr_key),
+            CONSTRAINT employment FOREIGN KEY (okey)
+            REFERENCES employment (ikey) MATCH SIMPLE
+            ON UPDATE CASCADE ON DELETE CASCADE
+            )
+            INHERITS (attributes);
+            CREATE INDEX employment_attributes_attr_fkey
+            ON employment_attributes
+            USING btree
+            (attr);
+            CREATE INDEX employment_attr_value_idx
+            ON employment_attributes
+            USING btree
+            (value);
+            CREATE TRIGGER employment_attr_update
+            AFTER INSERT OR UPDATE OR DELETE
+            ON employment_attributes
+            FOR EACH ROW
+            EXECUTE PROCEDURE employment_attr_update_ref();
+            },
+            q{
+            CREATE TABLE employment_attributes_list
+            (
+            CONSTRAINT employment_attr_list_pkey PRIMARY KEY (ikey),
+            CONSTRAINT employment_attr_l_c_uniq UNIQUE (canonical)
+            )
+            INHERITS (revisions, attributes_list);
+            },
+            q{
+            CREATE INDEX employment_firstday_idx
+              ON employment
+                USING btree
+                  (firstday NULLS FIRST);
+            CREATE INDEX employment_lastday_idx
+              ON employment
+                USING btree
+                  (lastday NULLS LAST);
         ],
     },