Changeset 1427 for trunk/LATMOS-Accounts/bin

Timestamp:

10/28/15 21:30:40 (9 years ago)

Author:

nanardon

Message:

Add a statistics collecting functions

Location:

trunk/LATMOS-Accounts/bin

Files:

• la-sql-regstat (added)
• la-sql-upgrade.in (modified) (2 diffs)

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

@@ -610 +610 @@
             FOR EACH ROW
             EXECUTE PROCEDURE rev_tg_aliases_f();
+            },
+        ],
+    },
+    {
+        ver => 13,
+        sql => [
+            q{
+            CREATE TABLE "stat"
+            (
+            name text NOT NULL,
+            CONSTRAINT stat_pkey PRIMARY KEY (name),
+            CONSTRAINT stat_okey_uniq UNIQUE (ikey)
+            )
+            INHERITS (objects);
+            CREATE INDEX stat_exported_idx
+            ON stat USING btree (exported);
+            CREATE UNIQUE INDEX stat_rev_idx
+            ON stat USING btree (rev);
+
+            CREATE OR REPLACE FUNCTION stat_attr_update_ref()
+            RETURNS trigger AS
+            $BODY$begin
+
+            IF (TG_OP != 'INSERT') then
+            update "stat" set date = now() where "stat".ikey = old.okey;
+            end if;
+            IF (TG_OP != 'DELETE') then
+            update "stat"  set date = now() where "stat".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 stat_attributes
+            (
+            CONSTRAINT stat_attributes_pkey PRIMARY KEY (attr_key),
+            CONSTRAINT stat FOREIGN KEY (okey)
+            REFERENCES stat (ikey) MATCH SIMPLE
+            ON UPDATE CASCADE ON DELETE CASCADE
+            )
+            INHERITS (attributes);
+            CREATE INDEX stat_attributes_attr_fkey
+            ON stat_attributes
+            USING btree
+            (attr COLLATE pg_catalog."default");
+            CREATE INDEX stat_attr_value_idx
+            ON stat_attributes
+            USING btree
+            (value COLLATE pg_catalog."default");
+            CREATE TRIGGER stat_attr_update
+            AFTER INSERT OR UPDATE OR DELETE
+            ON stat_attributes
+            FOR EACH ROW
+            EXECUTE PROCEDURE stat_attr_update_ref();
+            },
+            q{
+            CREATE TABLE stat_attributes_list
+            (
+            CONSTRAINT stat_attr_list_pkey PRIMARY KEY (ikey),
+            CONSTRAINT stat_attr_l_c_uniq UNIQUE (canonical)
+            )
+            INHERITS (revisions, attributes_list);
+            },
+            q{
+            CREATE TABLE statsentry
+            (
+            okey integer NOT NULL,
+            id bigint NOT NULL DEFAULT nextval('revisions_rev_seq'::regclass),
+            tstamp timestamp with time zone NOT NULL DEFAULT now(),
+            CONSTRAINT stat FOREIGN KEY (okey)
+            REFERENCES stat (ikey) MATCH SIMPLE
+            ON UPDATE CASCADE ON DELETE CASCADE,
+            CONSTRAINT statsentry_pkey PRIMARY KEY (id)
+            );
+            CREATE TABLE statvalues
+            (
+            sid bigint NOT NULL,
+            value text NOT NULL,
+            count bigint NOT NULL,
+            CONSTRAINT statvalues_pkey PRIMARY KEY (sid, value),
+            CONSTRAINT statvalues_fkey FOREIGN KEY (sid)
+            REFERENCES statsentry (id) MATCH SIMPLE
+            ON UPDATE CASCADE ON DELETE CASCADE
+            )
             },
         ],
@@ -650 +742 @@
             }
         }
-        $dbi->do($sql) or die "Erreur :\\" . $dbi->errstr . "\n";
+        my $err = $dbi->do($sql);
+        warn $err;
+        defined($err) or die "Erreur :\\" . $dbi->errstr . "\n";
     }