-- upgrade script to migrate the Koji database schema

-- from version 1.18 to 1.19

BEGIN;

CREATE TABLE tag_package_owners (

	package_id INTEGER NOT NULL REFERENCES package(id),

	tag_id INTEGER NOT NULL REFERENCES tag (id),

	owner INTEGER NOT NULL REFERENCES users(id),

-- versioned - see earlier description of versioning

	create_event INTEGER NOT NULL REFERENCES events(id) DEFAULT get_event(),

	revoke_event INTEGER REFERENCES events(id),

	creator_id INTEGER NOT NULL REFERENCES users(id),

	revoker_id INTEGER REFERENCES users(id),

	active BOOLEAN DEFAULT 'true' CHECK (active),

	CONSTRAINT active_revoke_sane CHECK (

		(active IS NULL AND revoke_event IS NOT NULL AND revoker_id IS NOT NULL)

		OR (active IS NOT NULL AND revoke_event IS NULL AND revoker_id IS NULL)),

	PRIMARY KEY (create_event, package_id, tag_id),

	UNIQUE (package_id,tag_id,active)

) WITHOUT OIDS;

CREATE OR REPLACE FUNCTION convert_owners() RETURNS SETOF tag_packages AS

$BODY$

DECLARE

    r tag_packages%rowtype;

    r2 tag_packages%rowtype;

    last_owner int;

BEGIN

    FOR r IN SELECT package_id, tag_id FROM tag_packages GROUP BY package_id, tag_id ORDER BY package_id, tag_id

    LOOP

        last_owner := 0;

        FOR r2 IN SELECT * FROM tag_packages WHERE package_id = r.package_id AND tag_id = r.tag_id ORDER BY create_event

        LOOP

            -- always use first and last (active) row

            IF last_owner = 0 OR r2.active IS TRUE THEN

                last_owner := r2.owner;

                RETURN NEXT r2; -- return current row of SELECT

            ELSE

                -- copy others only if owner changed

                IF last_owner <> r2.owner THEN

                    RETURN NEXT r2;

                    last_owner := r2.owner;

                END IF;

            END IF;

        END LOOP;

    END LOOP;

    RETURN;

END

$BODY$

LANGUAGE plpgsql;

INSERT INTO tag_package_owners (SELECT package_id, tag_id, owner, create_event, revoke_event, creator_id, revoker_id, active FROM convert_owners());

DROP INDEX IF EXISTS tag_packages_owner;

ALTER TABLE tag_packages DROP COLUMN owner;

DROP FUNCTION convert_owners();

-- add compressed iso-compressed, vhd-compressed, vhdx-compressed, and vmdk-compressed

insert into archivetypes (name, description, extensions) values ('iso-compressed', 'Compressed iso image', 'iso.gz iso.xz');

insert into archivetypes (name, description, extensions) values ('vhd-compressed', 'Compressed VHD image', 'vhd.gz vhd.xz');

insert into archivetypes (name, description, extensions) values ('vhdx-compressed', 'Compressed VHDx image', 'vhd.gz vhd.xz');

insert into archivetypes (name, description, extensions) values ('vmdk-compressed', 'Compressed VMDK image', 'vmdk.gz vmdk.xz');

-- add kernel-image and imitramfs

insert into archivetypes (name, description, extensions) values ('kernel-image', 'Kernel BZ2 Image', 'vmlinuz vmlinuz.gz vmlinuz.xz');

insert into archivetypes (name, description, extensions) values ('initramfs', 'Compressed Initramfs Image', 'img');

-- schema update for https://pagure.io/koji/issue/1629

CREATE TABLE user_krb_principals (

    user_id INTEGER NOT NULL REFERENCES users(id),

    krb_principal VARCHAR(255) NOT NULL UNIQUE,

    PRIMARY KEY (user_id, krb_principal)

) WITHOUT OIDS;

INSERT INTO user_krb_principals ( SELECT id, krb_principal FROM users WHERE users.krb_principal IS NOT NULL);

ALTER TABLE users DROP COLUMN krb_principal;

-- Disallow duplicate content generator names

ALTER TABLE content_generator ADD UNIQUE (name);

ALTER TABLE content_generator ALTER COLUMN name SET NOT NULL;

-- add all basic permissions

INSERT INTO permissions (name) SELECT 'dist-repo' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'dist-repo');

INSERT INTO permissions (name) SELECT 'host' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'host');

INSERT INTO permissions (name) SELECT 'image-import' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'image-import');

INSERT INTO permissions (name) SELECT 'sign' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'sign');

INSERT INTO permissions (name) SELECT 'tag' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'tag');

INSERT INTO permissions (name) SELECT 'target' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'target');

COMMIT;