Changeset 2296 for trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm

Timestamp:

11/13/19 19:37:12 (5 years ago)

Author:

nanardon

Message:

Add Employmentsummary objects (summarize user activity)

File:

• trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm (modified) (1 diff)

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm

@@ -1269 +1269 @@
             ],
         },
+        {
+            ver => 36,
+            sql => [
+                q{
+                CREATE TABLE public.employmentsum
+                (
+                  "user" text NOT NULL,
+                  firstday date NOT NULL DEFAULT now(),
+                  lastday date,
+                  contrattype text,
+                  CONSTRAINT employmentsum_pkey PRIMARY KEY (name),
+                  CONSTRAINT employmentsum_check CHECK (lastday IS NULL OR firstday <= lastday),
+                  CONSTRAINT employmentsum_name_user_fkey FOREIGN KEY ("user")
+                      REFERENCES public."user" (name) MATCH SIMPLE
+                      ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (public.objects)
+                WITH (
+                  OIDS=FALSE
+                );
+
+                CREATE INDEX fki_employmentsum_name_user_fkey
+                  ON public.employmentsum
+                  USING btree
+                  (name COLLATE pg_catalog."default");
+
+                CREATE INDEX fki_employmentsum_user_fkey
+                  ON public.employmentsum
+                  USING btree
+                  (name COLLATE pg_catalog."default");
+
+                CREATE INDEX employmentsum_exported_idx
+                  ON public.employmentsum
+                  USING btree
+                  (exported);
+
+                CREATE INDEX employmentsum_rev_idx
+                  ON public.employmentsum
+                  USING btree
+                  (rev);
+
+                CREATE TRIGGER employmentsum_rev_tg
+                  BEFORE INSERT OR UPDATE OR DELETE
+                  ON public.employmentsum
+                  FOR EACH ROW
+                  EXECUTE PROCEDURE public.rev_tg_f();
+                }
+            ],
+        },
     );