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

Timestamp:

06/24/20 18:57:32 (4 years ago)

Author:

nanardon

Message:

Add object support feature

File:

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

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

@@ -1351 +1351 @@
             ],
         },
+        {
+            ver => 38,
+            sql => [
+                q{
+
+                CREATE TABLE public.support
+                (
+                  CONSTRAINT support_pkey PRIMARY KEY (name),
+                  CONSTRAINT support_ikey_uniq UNIQUE (ikey)
+                )
+                INHERITS (public.objects)
+                WITH (
+                  OIDS=FALSE
+                );
+                ALTER TABLE public.support
+                  OWNER TO latmos;
+
+
+                CREATE TRIGGER support_update_rev_tg
+                  BEFORE INSERT OR UPDATE OR DELETE
+                  ON public.support
+                  FOR EACH ROW
+                  EXECUTE PROCEDURE public.rev_tg_f();
+
+
+                CREATE TABLE public.support_attributes
+                (
+                  CONSTRAINT support_attributes_pkey PRIMARY KEY (attr_key),
+                  CONSTRAINT support_attr_support_okey_fkey FOREIGN KEY (okey)
+                      REFERENCES public.support (ikey) MATCH SIMPLE
+                      ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (public.attributes)
+                WITH (
+                  OIDS=FALSE
+                );
+
+                CREATE OR REPLACE FUNCTION public.support_attr_update_ref()
+                  RETURNS trigger AS
+                $BODY$begin
+
+                IF (TG_OP != 'INSERT') then
+                update "support" set date = now() where "support".ikey = old.okey;
+                end if;
+                IF (TG_OP != 'DELETE') then
+                update "support"  set date = now() where "support".ikey = new.okey;
+                end if;
+
+                IF (TG_OP = 'DELETE') then
+                  return old;
+                ELSE
+                  return new;
+                END IF;
+
+                END;$BODY$
+                  LANGUAGE plpgsql VOLATILE
+                  COST 100;
+
+                CREATE INDEX fki_support_attributes_attr_fkey
+                  ON public.support_attributes
+                  USING btree
+                  (attr COLLATE pg_catalog."default");
+
+                CREATE INDEX support_attr_value_idx
+                  ON public.support_attributes
+                  USING btree
+                  (value COLLATE pg_catalog."default");
+
+                  CREATE TRIGGER support_attr_update
+                  AFTER INSERT OR UPDATE OR DELETE
+                  ON public.support_attributes
+                  FOR EACH ROW
+                  EXECUTE PROCEDURE public.support_attr_update_ref();
+
+                CREATE TABLE public.nethost_attributes_supports
+                (
+                  CONSTRAINT nethost_attributes_support_pkey PRIMARY KEY (attr_key),
+                  CONSTRAINT nethost_attr_support_okey_fkey FOREIGN KEY (okey)
+                      REFERENCES public.nethost (ikey) MATCH SIMPLE
+                      ON UPDATE CASCADE ON DELETE CASCADE,
+                  CONSTRAINT nethost_attributes_support_support_fkey FOREIGN KEY (value)
+                      REFERENCES public."support" (name) MATCH SIMPLE
+                      ON UPDATE CASCADE ON DELETE CASCADE,
+                  CONSTRAINT nethost_attributes_support_uniq UNIQUE (okey, attr, value)
+                )
+                INHERITS (public.nethost_attributes)
+                WITH (
+                  OIDS=FALSE
+                );
+
+                CREATE INDEX fki_nethost_attr_support_okey_fkey
+                  ON public.nethost_attributes_supports
+                  USING btree
+                  (okey);
+
+                CREATE INDEX fki_nethost_attributes_support_attr_fkey
+                  ON public.nethost_attributes_supports
+                  USING btree
+                  (attr COLLATE pg_catalog."default");
+
+
+                CREATE INDEX fki_nethost_attributes_support_support_fkey
+                  ON public.nethost_attributes_supports
+                  USING btree
+                  (value COLLATE pg_catalog."default");
+
+                CREATE TRIGGER nethost_attributes_support_update_ref
+                  AFTER INSERT OR UPDATE OR DELETE
+                  ON public.nethost_attributes_supports
+                  FOR EACH ROW
+                  EXECUTE PROCEDURE public.nethost_attr_update_ref();
+
+                CREATE OR REPLACE FUNCTION public.nethost_sort_fields()
+                  RETURNS trigger AS
+                $BODY$BEGIN
+
+                              IF (TG_OP='INSERT') then
+                              IF (new.attr='ip') THEN
+                              insert into nethost_attributes_ips VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                              IF (new.attr='macaddr') THEN
+                              insert into nethost_attributes_macs VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                              IF (new.attr='owner') THEN
+                              insert into nethost_attributes_users VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                              IF (new.attr='user') THEN
+                              insert into nethost_attributes_users VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                          IF (new.attr='related') THEN
+                              insert into nethost_attributes_nethosts VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                          IF (new.attr='support') THEN
+                              insert into nethost_attributes_supports VALUES (new.*);
+                              RETURN NULL;
+                              END IF;
+
+                              end if;
+
+                              if (TG_OP='DELETE') THEN
+                              RETURN old;
+                              else
+                              RETURN new;
+                              end if;
+                              END;$BODY$
+                  LANGUAGE plpgsql VOLATILE
+                  COST 100;
+
+                }
+            ],
+        },
     );