package LATMOS::Accounts::Bases::Sql::upgrade;

package LATMOS::Accounts::Bases::Sql;

use strict;
use warnings;
use LATMOS::Accounts::Log;

=head1 NAME

    LATMOS::Accounts::Bases::Sql::upgrade

=head1 DESCRIPTION

Upgrade the SQL schema for SQL Base

=head1 FUNCTIONS

=cut

sub _SqlSchemaUpgrade {
    my ($self) = @_;

    my $rev = $self->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
                );

                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
                );

                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',
            ],
        },
        {
            ver => '26',
            sql => [ ],
        },
        {
            ver => 27,
            sql => [
                q{
                CREATE OR REPLACE VIEW nethost_attributes_netzone AS
                SELECT netzone.name AS value, 'netZone' AS attr, nethost_attributes_ips.attr_key, nethost_attributes_ips.okey
                FROM nethost_attributes_ips, netzone_attributes, netzone
                WHERE netzone.ikey = netzone_attributes.okey AND netzone_attributes.attr = 'net'::text AND nethost_attributes_ips.value::inet <<= netzone_attributes.value::inet
                EXCEPT
                SELECT netzone.name AS value, 'netZone' AS attr, nethost_attributes_ips.attr_key, nethost_attributes_ips.okey
                FROM nethost_attributes_ips, netzone_attributes, netzone
                WHERE netzone.ikey = netzone_attributes.okey AND netzone_attributes.attr = 'netExclude'::text AND nethost_attributes_ips.value::inet <<= netzone_attributes.value::inet;
                },
                q{
                CREATE OR REPLACE VIEW nethost_attributes_read AS
                SELECT nethost_attributes.value, nethost_attributes.attr, nethost_attributes.attr_key, nethost_attributes.okey
                FROM nethost_attributes
                UNION
                SELECT nethost_attributes_netzone.value, nethost_attributes_netzone.attr, nethost_attributes_netzone.attr_key, nethost_attributes_netzone.okey
                FROM nethost_attributes_netzone;
                }
            ],
        },
        {
            ver => 28,
            sql => [
                q{
                CREATE TABLE attributes_values
                (
                -- 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),
                otype text NOT NULL,
                attributes text NOT NULL,
                value text NOT NULL,
                CONSTRAINT attributes_values_pkey PRIMARY KEY (ikey)
                )
                INHERITS (revisions);

                CREATE UNIQUE INDEX attributes_values_idx
                ON attributes_values
                USING btree
                (otype, attributes, value);
                }
            ]
        },
        {
            ver => 29,
            sql => [
                q{
                ALTER TABLE objectslogs
                   ADD COLUMN parentotype text;
                ALTER TABLE objectslogs
                   ADD COLUMN parentname text;
                ALTER TABLE objectslogs
                   ADD COLUMN parentikey bigint;
                CREATE INDEX objectslogs_parentotype_idx
                    ON public.objectslogs
                    USING btree (parentotype);
                CREATE INDEX objectslogs_parentname_idx
                    ON objectslogs
                    USING btree (parentname);
                CREATE INDEX objectslogs_parentikey_idx
                    ON objectslogs
                    USING btree (parentikey);
                }
            ],
        },
        {
            ver => 30,
            recompute => 1,
        },
        {
            ver => 31,
            sql => [
                q{
                DROP TABLE attributes_list CASCADE;
                }
            ],
        },
        {
            ver => 32,
            recompute => 1,
        },
        {
            ver => 33,
            sql => [
                q{
                CREATE OR REPLACE FUNCTION public.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'
		  OR  new.attr='autoExclude'
		  OR  new.attr='autoInclude') 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 NOT LEAKPROOF
        COST 100;
                }
            ],
        },
        {
            ver => 34,
            code => sub { return 1 },
            recompute => 1,
        },
    );

    my $dbi = $self->{_db};

    my $NeedRecompute = 0;

    foreach my $maj (@updates) {
        if ($rev >= $maj->{ver}) {
            next;
        }
        $self->log(LA_NOTICE, 'Switching to schema revision: ' . $maj->{ver} . "\n");
        foreach my $sql (@{ $maj->{sql} || []}) {
            {
                my $sqlv = $sql;
                $sqlv =~ s/^/  /mg;
            }
            my $err = $dbi->do($sql);
            defined($err) or do {
                $self->log(LA_ERR, "Erreur :\\" . $dbi->errstr . "\n");
                return;
            };

        }
        if ($maj->{recompute}) {
            $NeedRecompute = 1;
        }

        $self->log(LA_INFO, 'Updating schema_version to ' . $maj->{ver} . "\n");

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

        if ($maj->{code}) {
            $maj->{code}->() or return;
        }
    }

    if ( $NeedRecompute ) {
        $self->set_global_value('NeedRecomputeObject', 'Yes');
    }

    return 1;
}

=head2 SchemaUpgrade

Upgrade the SQL schema

=cut

sub _SchemaUpgrade {
    my ($self) = @_;

    $self->log(LA_INFO, "Waiting to lock database to upgrade the schema...");

    $self->db->do(q{SELECT 1 FROM settings WHERE varname = 'schema_version' FOR UPDATE});

    $self->log(LA_INFO, "Database locked upgrading schema !");
 
    $self->temp_switch_unexported(
        sub {
            if($self->_SqlSchemaUpgrade) {
                $self->log(LA_DEBUG, "upgrade::_SqlSchemaUpgrade done");
            } else {
                $self->{_db}->rollback;
                return;
            }

            if ($self->_CreateInternalObjects) {
                $self->log(LA_DEBUG, "upgrade::_CreateInternalObjects done");
            } else {
                $self->{_db}->rollback;
                return;
            }

            return 1;
        }, 1
    ) or return;

    if ($self->get_global_value('NeedRecomputeObject')) {
        foreach my $otype (qw(user)) {
            foreach my $name ($self->listRealObjects($otype)) {
                my $object = $self->get_object($otype, $name);
                $object->PostSetAttribute();
            }
        }
        $self->del_global_value('NeedRecomputeObject');
    }

    $self->commit;

    return 1;
}

1;
