source: trunk/LATMOS-Accounts/bin/la-sql-upgrade.in @ 1907

#!/usr/bin/perl

use strict;
use warnings;
use LATMOS::Accounts;
use Getopt::Long;
use Pod::Usage;

my $DATADIR = '@DATADIR@';

=head1 NAME

    la-sql-upgrade - Update SQL base schema

=head1 SYNOPSIS

    la-sql-upgrade [options] [name]

=cut

GetOptions(
    'c|config=s'     => \my $config,
    'b|base=s'       => \my $base,
    'no-commit'      => \my $nocommit,
    'v|verbose'      => \my $verbose,
    'h|help'         => sub { pod2usage(1); },
) or pod2usage();

=head1 OPTIONS

=over 4

=item -c|--config configdir

Use this configuration directory instead of the default one.

=item -b|--base basename

Query this specific base instead of the default one.

=back

=cut

$| = 1; # autoflush

my $LA = LATMOS::Accounts->new($config, noacl => 1);
my $labase = $LA->base($base);
$labase->unexported(1);

my $dbi = $labase->db;

my $rev = $labase->get_global_value('schema_version') || 1;

my @updates = (
    {
        ver => 3,
        sql => [
            q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
            nethost_is_single_ip},
            q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON nethost},
            q{DROP TRIGGER IF EXISTS check_zone_name_unity_tg ON netzone},
            q{DROP FUNCTION IF EXISTS check_zone_name_unity()},
            q{ALTER TABLE nethost_attributes_ips DROP CONSTRAINT
            nethost_is_single_ip},
            q{INSERT INTO settings(varname, val) VALUES ('schema_version', 3)},
        ],
    },
    {
        ver => 4,
        sql => [
            q{
            CREATE OR REPLACE FUNCTION 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;
            end if;


            if (TG_OP='DELETE') THEN
            RETURN old;
            else
            RETURN new;
            end if;
            END;$BODY$
            LANGUAGE plpgsql VOLATILE
            COST 100;
            },
        ],
    },
    {
        ver => 5,
        sql => [
            q{
            CREATE OR REPLACE VIEW address_attributes AS
            ( ( SELECT address."user" AS value, 'user' AS attr, address.rev AS attr_key, address.ikey AS okey
            FROM address
            UNION ALL
            SELECT address.name AS value, 'name' AS attr, address.rev AS attr_key, address.ikey AS okey
            FROM address )
            UNION ALL
            SELECT address_attributes.value, address_attributes.attr, address_attributes.attr_key, address_attributes.okey
            FROM address_attributes_base address_attributes )
            UNION ALL
            SELECT '1' AS value, 'active' AS attr, address.rev AS attr_key, address.ikey AS okey
            FROM "user" JOIN address ON "user".name = address."user"
            WHERE "user".exported AND address.exported and
            ("user".expire IS NULL or "user".expire > now())
            }
        ],
    },
    {
        ver => 6,
        sql => [
            q{
            CREATE TABLE request
            (
                id serial NOT NULL,
                "create" timestamp with time zone NOT NULL DEFAULT now(),
                name text NOT NULL,
                object text,
                apply timestamp with time zone NOT NULL DEFAULT now(),
                done timestamp with time zone,
                "user" text,
                CONSTRAINT request_pkey PRIMARY KEY (id ),
                CONSTRAINT request_name_fkey FOREIGN KEY (name)
                REFERENCES accreq (name) MATCH SIMPLE
                ON UPDATE CASCADE ON DELETE CASCADE,
                CONSTRAINT request_user_fkey FOREIGN KEY ("user")
                REFERENCES "user" (name) MATCH SIMPLE
                ON UPDATE CASCADE ON DELETE SET NULL
            )
            },
            q{
            CREATE INDEX fki_request_name_fkey ON request
            USING btree (name )
            },
            q{
            CREATE INDEX fki_request_user_fkey ON request
            USING btree ("user" )
            },
            q{
            CREATE TABLE request_attributes
            (
                reqid bigint,
                attribute text NOT NULL,
                value text,
                CONSTRAINT request_id_fkey FOREIGN KEY (reqid)
                REFERENCES request (id) MATCH SIMPLE
                ON UPDATE CASCADE ON DELETE CASCADE
            )
            },
            q{
            CREATE INDEX fki_request_id_fkey ON request_attributes
            USING btree (reqid )
            },
            q{
            delete from accreq_attributes_list
            }
        ],
    },
    {
        ver => 7,
        sql => [
            q{
            ALTER TABLE request ADD COLUMN automated boolean NOT NULL DEFAULT false;
            },
            q{
            ALTER TABLE request ADD COLUMN objrev bigint;
            },
            q{
            ALTER TABLE aliases ADD COLUMN description text;
            },
            q{
            ALTER TABLE revaliases ADD COLUMN description text;
            },
            q{
            CREATE OR REPLACE FUNCTION group_sort_fields()
              RETURNS trigger AS
              $BODY$BEGIN

              IF (TG_OP='INSERT') then
              IF (new.attr='managedBy'
                  OR  new.attr='managedAlsoBy'
                  OR  new.attr='member'
                  OR  new.attr='memberUID') THEN
              insert into group_attributes_users VALUES (new.*);
              RETURN NULL;
              END IF;

              IF (new.attr='sutype') THEN
              insert into group_attributes_sutypes VALUES (new.*);
              RETURN NULL;
              END IF;
              end if;


              IF (TG_OP = 'UPDATE') THEN
              IF (new.attr='gidNumber') then
              update "group" set gidnumber = new.value::integer where
              "group".ikey = new.okey;
              RETURN NULL;
              END IF;
              END IF;

              IF (TG_OP = 'DELETE') THEN
              IF (old.attr='exported') then
              update "group" set exported = false where "group".ikey = old.okey;
              return null;
              end if;
              else
              IF (new.attr='exported') then
              update "group" set exported = true where "group".ikey = new.okey;
              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;
            },
        ],
    },
    {
        ver => 8,
        sql => [
            q{
            CREATE TABLE objectslogs
            (
              logkey SERIAL NOT NULL,
              ikey bigint NOT NULL,
              irev bigint,
              otype text NOT NULL,
              name text NOT NULL,
              changetype text NOT NULL,
              username text NOT NULL,
              message text NOT NULL,
              logdate timestamp with time zone NOT NULL DEFAULT now(),
              CONSTRAINT objectlogs_pkey PRIMARY KEY (logkey)
            );},
            q{
            CREATE INDEX objectlogs_name_idx
              ON objectslogs
              USING btree
              (name);
            },
            q{
            CREATE INDEX objectslogs_ikey_idx
              ON objectslogs
              USING btree
              (ikey);
            },
            q{
            CREATE INDEX objectslogs_otype_idx
              ON objectslogs
              USING btree
              (otype);
            }
        ],
    },
    {
        ver => 9,
        sql => [
            q{
            CREATE TABLE nethost_attributes_nethosts
            (
            CONSTRAINT nethost_attributes_nethosts_pkey PRIMARY KEY (attr_key),
            CONSTRAINT nethost_attr_nethosts_fkey FOREIGN KEY (okey)
            REFERENCES nethost (ikey) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT nethost_attr_nethosts_nethosts FOREIGN KEY ("value")
            REFERENCES nethost (name) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE
            )
            INHERITS (nethost_attributes);

            CREATE INDEX fki_nethost_attr_nethosts_fkey
            ON nethost_attributes_nethosts
            USING btree
            (okey);

            CREATE TRIGGER nethost_attr_nethosts_update
            AFTER INSERT OR UPDATE OR DELETE
            ON nethost_attributes_nethosts
            FOR EACH ROW
            EXECUTE PROCEDURE nethost_attr_update_ref();


            CREATE OR REPLACE FUNCTION 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;
              end if;

              if (TG_OP='DELETE') THEN
              RETURN old;
              else
              RETURN new;
              end if;
              END;$BODY$
              LANGUAGE plpgsql VOLATILE
              COST 100;

            }
        ],
    },
    {
        ver => 10,
        sql => [
            q{
            CREATE OR REPLACE FUNCTION service_attr_update_ref()
            RETURNS trigger AS
            $BODY$begin

            IF (TG_OP != 'INSERT') then
            update "service" set date = now() where "service".ikey = old.okey;
            end if;
            IF (TG_OP != 'DELETE') then
            update "service"  set date = now() where "service".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 service
            (
            "start" date,
            "end" date,
            CONSTRAINT service_pkey PRIMARY KEY (name),
            CONSTRAINT service_ikey_uniq UNIQUE (ikey)
            )
            INHERITS (objects);
            },
            q{
            CREATE TABLE service_attributes_list
            (
            ikey integer NOT NULL DEFAULT nextval('ikey_seq'::regclass),
            canonical text NOT NULL,
            description text,
            CONSTRAINT service_attributes_list_pkey PRIMARY KEY (ikey),
            CONSTRAINT g_attr_l_service_uniq UNIQUE (canonical)
            )
            INHERITS (revisions, attributes_list)
            WITH (
            OIDS=FALSE
            );
            ALTER TABLE service_attributes_list
            OWNER TO latmos;

            CREATE TABLE service_attributes
            (
            CONSTRAINT service_attributes_pkey PRIMARY KEY (attr_key),
            CONSTRAINT service_attr_users_okey_fkey FOREIGN KEY (okey)
            REFERENCES service (ikey) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT service_attributes_attr_fkey FOREIGN KEY (attr)
            REFERENCES service_attributes_list (canonical) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE NO ACTION
            )
            INHERITS (attributes)
            WITH (
            OIDS=FALSE
            );
            ALTER TABLE service_attributes
            OWNER TO latmos;

            CREATE INDEX fki_service_attributes_attr_fkey
            ON nethost_attributes
            USING btree
            (attr);

            CREATE INDEX service_attr_value_idx
            ON nethost_attributes
            USING btree
            (value);

            CREATE TRIGGER serivce_attr_update
            AFTER INSERT OR UPDATE OR DELETE
            ON service_attributes
            FOR EACH ROW
            EXECUTE PROCEDURE service_attr_update_ref();
            },
            q{
            CREATE TABLE service_attributes_users
            (
            CONSTRAINT service_attributes_user_pkey PRIMARY KEY (attr_key),
            CONSTRAINT service_attr_users_okey_fkey FOREIGN KEY (okey)
            REFERENCES service (ikey) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT service_attributes_users_attr_fkey FOREIGN KEY (attr)
            REFERENCES service_attributes_list (canonical) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE NO ACTION,
            CONSTRAINT service_attributes_users_user_fkey FOREIGN KEY (value)
            REFERENCES "user" (name) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT service_attributes_users_uniq UNIQUE (okey, attr, value)
            )
            INHERITS (service_attributes);

            CREATE INDEX fki_service_attr_users_okey_fkey
            ON service_attributes_users
            USING btree
            (okey);

            CREATE INDEX fki_service_attributes_users_attr_fkey
            ON service_attributes_users
            USING btree
            (attr);

            CREATE INDEX fki_service_attributes_users_user_fkey
            ON service_attributes_users
            USING btree
            (value);

            CREATE TRIGGER service_attributes_users_update_ref
            AFTER INSERT OR UPDATE OR DELETE
            ON service_attributes_users
            FOR EACH ROW
            EXECUTE PROCEDURE service_attr_update_ref();

            CREATE OR REPLACE FUNCTION service_sort_fields()
              RETURNS trigger AS
              $BODY$BEGIN

              if (TG_OP='INSERT' or TG_OP='UPDATE') THEN
              IF (new.attr='manager') THEN
              insert into service_attributes_users 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;

              CREATE TRIGGER service_sort_field_tg
              BEFORE INSERT OR UPDATE OR DELETE
              ON service_attributes
              FOR EACH ROW
              EXECUTE PROCEDURE service_sort_fields();

            CREATE TRIGGER service_rev_tg
            BEFORE INSERT OR UPDATE OR DELETE
            ON service
            FOR EACH ROW
            EXECUTE PROCEDURE rev_tg_f();
            },
        ],
    },
    {
        ver => 11,
        sql => [
            q{
            ALTER TABLE "user" ADD COLUMN endcircuit timestamp with time zone
            }
        ],
    },
    {
        ver => 12,
        sql => [
            q{
            CREATE OR REPLACE VIEW user_attributes AS
            (        (        (        (        (        (        (        (        (        (        (
            SELECT "user".uidnumber::text AS value, 'uidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user"
            UNION ALL
            SELECT "user".gidnumber::text AS value, 'gidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user")
            UNION ALL
            SELECT "user".name AS value, 'name' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user")
            UNION ALL
            SELECT "user".expire::text AS value, 'expire' AS attr, "user".rev AS attr_key, "user".ikey AS okey FROM "user"
            WHERE "user".expire IS NOT NULL)
            UNION ALL
            SELECT '1'::text AS value, 'exported' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            WHERE "user".exported = true)
            UNION ALL
            SELECT address_attributes_site.value, 'allsite' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            JOIN address ON address."user" = "user".name
            JOIN address_attributes_site ON address_attributes_site.okey = address.ikey
            WHERE address_attributes_site.attr = 'site'::text AND address.exported = true)
            UNION ALL
            SELECT "group".name AS value, 'memberOf' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            JOIN group_attributes ON group_attributes.value = "user".name
            JOIN "group" ON group_attributes.okey = "group".ikey
            WHERE group_attributes.attr = 'memberUID'::text)
            UNION ALL
            SELECT user_attributes.value, user_attributes.attr, user_attributes.attr_key, user_attributes.okey
            FROM user_attributes_base user_attributes)
            UNION ALL
            SELECT "group".name AS value, 'departments' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "group"
            JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
            JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
            JOIN "user" ON "user".name = group_attributes_users.value
            WHERE group_attributes_sutypes.value = 'dpmt'::text AND group_attributes_sutypes.attr = 'sutype'::text)
            UNION ALL
            SELECT "group".name AS value, 'cells' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "group"
            JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
            JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
            JOIN "user" ON "user".name = group_attributes_users.value
            WHERE group_attributes_sutypes.value = 'cell'::text AND group_attributes_sutypes.attr = 'sutype'::text)
            UNION ALL
            SELECT "group".name AS value, 'managedObjects' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            JOIN group_attributes ON group_attributes.value = "user".name
            JOIN "group" ON group_attributes.okey = "group".ikey
            WHERE group_attributes.attr = 'managedBy'::text)
            UNION ALL
            SELECT justify_interval(now() - "user".expire)::text AS value, 'expired' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            WHERE "user".expire <= now())
            UNION ALL
            SELECT 1::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            WHERE ("user".expire IS NULL OR "user".expire >= now()) AND "user".exported = true
            UNION ALL
            SELECT 0::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
            FROM "user"
            WHERE ("user".expire IS NOT NULL and "user".expire <= now()) or "user".exported = false;
            },
            q{
            CREATE OR REPLACE FUNCTION rev_tg_aliases_f()
            RETURNS trigger AS
            $BODY$begin

            IF (TG_OP = 'DELETE') THEN
            update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (old.forward);
            return old;
            ELSE
                IF (TG_OP = 'UPDATE') THEN
                update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (old.forward);
                END IF;
            update "user" set rev = nextval('revisions_rev_seq'::regclass), "date" = now() where "name" = ANY (new.forward);
            return new;
            END IF;

            end;$BODY$
            LANGUAGE plpgsql VOLATILE
            COST 100;

            CREATE TRIGGER aliases_user_tg
            AFTER INSERT OR UPDATE OR DELETE
            ON aliases
            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);
            CREATE INDEX stat_attr_value_idx
            ON stat_attributes
            USING btree
            (value);
            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
            )
            },
        ],
    },
    {
        ver => 14,
        sql => [
            q{
            ALTER TABLE objects ADD COLUMN createdby text;
            },
            q{
            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);
            },
        ],
    },
    {
        ver => 16,
        sql => [
            q{
            ALTER TABLE aliases
              ADD CONSTRAINT aliases_ikey_uniq UNIQUE(ikey);
            CREATE OR REPLACE FUNCTION aliases_attr_update_ref()
            RETURNS trigger AS
            $BODY$begin

            IF (TG_OP != 'INSERT') then
            update "aliases" set date = now() where "aliases".ikey = old.okey;
            end if;
            IF (TG_OP != 'DELETE') then
            update "aliases"  set date = now() where "aliases".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 aliases_attributes_base
            (
            CONSTRAINT aliases_attributes_base_pkey PRIMARY KEY (attr_key),
            CONSTRAINT aliases_fkey FOREIGN KEY (okey)
            REFERENCES aliases (ikey) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE
            )
            INHERITS (attributes);
            CREATE INDEX aliases_attributes_base_attr_fkey
            ON aliases_attributes_base
            USING btree
            (attr);
            CREATE INDEX aliases_attr_value_idx
            ON aliases_attributes_base
            USING btree
            (value);
            CREATE TRIGGER aliases_attr_update
            AFTER INSERT OR UPDATE OR DELETE
            ON aliases_attributes_base
            FOR EACH ROW
            EXECUTE PROCEDURE aliases_attr_update_ref();
            },
            q{
            CREATE OR REPLACE VIEW aliases_attributes AS
            SELECT "user".name AS value,
            'user'::text AS attr,
            aliases.rev AS attr_key,
            aliases.ikey AS okey
            FROM aliases
            JOIN "user" ON aliases.name = "user".name OR aliases.forward = ARRAY["user".name]
            union all
               select value, attr, attr_key, okey from aliases_attributes_base
            UNION ALL
                SELECT "aliases".name AS value,
                'name'::text AS attr,
                "aliases".rev AS attr_key,
                "aliases".ikey AS okey
                FROM "aliases";

           CREATE OR REPLACE RULE aliases_attributes_insert AS
           ON INSERT TO aliases_attributes DO INSTEAD  INSERT INTO aliases_attributes_base (value, attr, okey)
           VALUES (new.value, new.attr, new.okey);

           CREATE OR REPLACE RULE aliases_attributes_update AS
           ON UPDATE TO aliases_attributes DO INSTEAD  UPDATE aliases_attributes_base SET value = new.value, attr = new.attr, okey = new.okey
           WHERE aliases_attributes_base.attr_key = old.attr_key AND aliases_attributes_base.attr = old.attr;

           CREATE OR REPLACE RULE aliases_atttributes_delete AS
           ON DELETE TO aliases_attributes DO INSTEAD  DELETE FROM aliases_attributes_base
           WHERE aliases_attributes_base.attr_key = old.attr_key AND aliases_attributes_base.attr = old.attr;
            }

        ],
    },
    {
        ver => 17,
        sql => [
            q{
            CREATE TABLE passwordreset
            (
            -- Hérité(e) from table revisions:  rev integer NOT NULL DEFAULT nextval('revisions_rev_seq'::regclass),
            -- Hérité(e) from table revisions:  date timestamp with time zone NOT NULL DEFAULT now(),
            -- Hérité(e) from table revisions:  "create" timestamp with time zone NOT NULL DEFAULT now(),
            -- Hérité(e) from table revisions:  ikey integer NOT NULL DEFAULT nextval('ikey_seq'::regclass),
            "user" text NOT NULL,
            id text NOT NULL,
            CONSTRAINT passwordreset_pkey PRIMARY KEY (id),
            CONSTRAINT password_reset_user_fkey FOREIGN KEY ("user")
            REFERENCES "user" (name) MATCH SIMPLE
            ON UPDATE CASCADE ON DELETE CASCADE
            )
            INHERITS (revisions)
            WITH (
            OIDS=FALSE
            );
            },
        ],
    },
    {
        ver => 18,
        sql => [
            q{
            ALTER TABLE employment
            ADD CHECK (lastday is null or firstday <= lastday);
            }
        ],
    },
    {
        ver => 19,
        sql => [
            q{
            ALTER TABLE "nethost" ADD COLUMN expire timestamp with time zone;
            }
        ],
    },
    {
        ver => 20,
        sql => [
            q{
            CREATE OR REPLACE VIEW user_attributes AS
                SELECT "user".uidnumber::text AS value, 'uidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
            UNION ALL
                SELECT "user".gidnumber::text AS value, 'gidnumber' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
            UNION ALL
                SELECT "user".name AS value, 'name' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
            UNION ALL
                SELECT "user".expire::text AS value, 'expire' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                WHERE "user".expire IS NOT NULL
            UNION ALL
                SELECT '1'::text AS value, 'exported' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                WHERE "user".exported = true
            UNION ALL
                SELECT address_attributes_site.value, 'allsite' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                JOIN address ON address."user" = "user".name
                JOIN address_attributes_site ON address_attributes_site.okey = address.ikey
                WHERE address_attributes_site.attr = 'site'::text AND address.exported = true
            UNION ALL
                SELECT "group".name AS value, 'memberOf' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                JOIN group_attributes ON group_attributes.value = "user".name
                JOIN "group" ON group_attributes.okey = "group".ikey
                WHERE group_attributes.attr = 'memberUID'::text
            UNION ALL
                SELECT user_attributes.value, user_attributes.attr, user_attributes.attr_key, user_attributes.okey
                FROM user_attributes_base user_attributes
            UNION ALL
                SELECT "group".name AS value, 'departments' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "group"
                JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
                JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
                JOIN "user" ON "user".name = group_attributes_users.value
                WHERE group_attributes_sutypes.value = 'dpmt'::text AND group_attributes_sutypes.attr = 'sutype'::text
            UNION ALL
                SELECT "group".name AS value, 'cells' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "group"
                JOIN group_attributes_sutypes ON "group".ikey = group_attributes_sutypes.okey
                JOIN group_attributes_users ON "group".ikey = group_attributes_users.okey AND group_attributes_users.attr = 'memberUID'::text
                JOIN "user" ON "user".name = group_attributes_users.value
                WHERE group_attributes_sutypes.value = 'cell'::text AND group_attributes_sutypes.attr = 'sutype'::text
            UNION ALL
                SELECT "group".name AS value, 'managedObjects' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                JOIN group_attributes ON group_attributes.value = "user".name
                JOIN "group" ON group_attributes.okey = "group".ikey
                WHERE group_attributes.attr = 'managedBy'::text
            UNION ALL
                SELECT justify_interval(now() - "user".expire)::text AS value, 'expired' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                WHERE "user".expire <= now()
            UNION ALL
                SELECT 1::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                WHERE ("user".expire IS NULL OR "user".expire >= now()) AND "user".exported = true
            UNION ALL
                SELECT 0::text AS value, 'active' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
                WHERE "user".expire IS NOT NULL AND "user".expire <= now() OR "user".exported = false
            UNION ALL
                SELECT CASE WHEN "user".exported = false THEN 'unexported'
                WHEN "user".expire IS NOT NULL AND "user".expire <= now() THEN 'expired'
                ELSE 'active' END AS value, 'status' AS attr, "user".rev AS attr_key, "user".ikey AS okey
                FROM "user"
            ;

            }
        ],
    },
    {
        ver => 21,
        sql => [
            'ALTER TABLE objects ADD COLUMN oalias text',
            'ALTER TABLE objects
               ADD COLUMN nodelete boolean NOT NULL DEFAULT false',
            'ALTER TABLE objects
               ADD COLUMN internobject boolean NOT NULL DEFAULT false',
        ],
    },
    {
        ver => 22,
        sql => [
            'CREATE INDEX fki_employment
            ON employment_attributes
            USING btree (okey);'
        ],
    },
    {
        ver => 23,
        sql => [
            'ALTER TABLE address ADD COLUMN expire timestamp with time zone'
        ],
    },
    {
        ver => 24,
        sql => [
            q{
            CREATE OR REPLACE FUNCTION user_sort_fields()
            RETURNS trigger AS
            $BODY$DECLARE
            rec RECORD;
            BEGIN

            IF (TG_OP='UPDATE' or TG_OP='INSERT') THEN

            IF (new.attr='locked') THEN
            IF (TG_OP = 'INSERT') THEN
            new.value = now()::text;
            ELSIF (TG_OP='UPDATE') THEN
            new.value = old.value;
            END IF;
            END IF;

            IF (new.attr='nickname') THEN
            PERFORM 1 from "user" where "user".ikey = new.okey
                and new.value = "user".name;
            IF NOT FOUND THEN
            select aliases.name into rec from aliases join "user"
            on array["user".name] = aliases.forward
            where "user".ikey = new.okey and aliases.name = new.value;
            IF NOT FOUND THEN
            insert into aliases ("name", "forward", "description", exported)
            select new.value, array["user".name], 'Forward for ' || "user".name || ' nickname', "user".exported from "user" where "user".ikey = new.okey;
            END IF;
            END IF;
            END IF;

            IF (new.attr='uidNumber') THEN
            update "user" set uidnumber = new.value::integer where ikey = new.okey;
            RETURN NULL;
            END IF;

            IF (new.attr='gidNumber') THEN
            update "user" set gidnumber = new.value::integer where ikey = new.okey;
            RETURN null;
            END IF;

            IF (new.attr='expire') THEN
            update "user" set expire = new.value::timestamp where ikey = new.okey;
            RETURN NULL;
            END IF;

            IF (new.attr='exported') THEN
            update "user" set exported = true where ikey = new.okey;
            RETURN NULL;
            END IF;

            END IF;

            IF (TG_OP = 'DELETE') THEN

            IF (old.attr='expire') THEN
            update "user" set expire = NULL where ikey = old.okey;
            RETURN NULL;
            END IF;

            IF (old.attr='exported') THEN
            update "user" set exported = false where ikey = old.okey;
            RETURN NULL;
            END IF;

            END IF;


            IF (TG_OP='INSERT') THEN
            IF (new.attr='manager') THEN
            insert into user_attributes_users VALUES (new.*);
            RETURN NULL;
            END IF;

            IF (new.attr='site') THEN
            insert into user_attributes_site VALUES (new.*);
            RETURN NULL;
            END IF;

            IF (new.attr='department') THEN
            insert into user_attributes_groups VALUES (new.*);
            RETURN NULL;
            END IF;

            IF (new.attr='contratType') THEN
            insert into user_attributes_groups VALUES (new.*);
            RETURN NULL;
            END IF;

            IF (new.attr='jobType') THEN
            insert into user_attributes_groups 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;
            },
        ],
    },
    {
        ver => 25,
        sql => [
            'ALTER TABLE objects ADD COLUMN oaliascache text',
        ],
    },
);

my @objects = (
    {
        name => 'dpmt',
        otype => 'sutype',
        attrs => {
            description => 'Department',
        },
    },
    {
        name => 'contrattype',
        otype => 'sutype',
        attrs => {
            description => 'Contract',
        },
    },
    {
        name => '-useralias',
        otype => 'group',
        attrs => {
            description => 'Internal group for user alias object',
            gidnumber => -1,
            unexported => 1,
        },
        intern => 1,
    },
);

$dbi->rollback;

foreach my $maj (@updates) {
    if ($rev >= $maj->{ver}) {
        next;
    }
    print "\n";
    print 'Switching to schema revision: ' . $maj->{ver} . "\n";
    foreach my $sql (@{ $maj->{sql} }) {
        {
            my $sqlv = $sql;
            $sqlv =~ s/^/  /mg;
            if ($verbose) {
                warn '  >' . $sqlv . "\n";
            } else {
                print 'x';
            }
        }
        my $err = $dbi->do($sql);
        defined($err) or die "Erreur :\\" . $dbi->errstr . "\n";
    }

    print "\n";

    print 'Updating schema_version to ' . $maj->{ver} . "\n";

    $dbi->do(
        'UPDATE settings SET val = ? where varname = ?',
        undef,
        $maj->{ver}, 'schema_version'
    );
    print "Done\n\n";

}

my $setnodel  = $dbi->prepare('UPDATE objects SET nodelete     = true where name = ?');
my $setintern = $dbi->prepare('UPDATE objects SET internobject = true where name = ?');

foreach (@objects) {
    if (!$labase->GetRawObject($_->{otype}, $_->{name})) {
        printf("Creating object %s/%s\n", $_->{otype}, $_->{name});
        $labase->create_object($_->{otype}, $_->{name}, %{$_->{attrs} || {}})
            or die sprintf("cannot create %s/%s\n", $_->{otype}, $_->{name});

    }

    $setnodel->execute($_->{name});
    $setintern->execute($_->{name}) if ($_->{intern});
}

if ($nocommit) {
    $dbi->rollback;
} else {
    $dbi->commit;
}
print "Process terminated successfully\n";