ykarel / centos / cbs-tools

Forked from centos/cbs-tools 4 years ago
Clone

Blame scripts/migration/1.16.3-1.19.1/schema-upgrade-1.18-1.19.sql

Thomas Oulevey 98f397
-- upgrade script to migrate the Koji database schema
Thomas Oulevey 98f397
-- from version 1.18 to 1.19
Thomas Oulevey 98f397
Thomas Oulevey 98f397
Thomas Oulevey 98f397
BEGIN;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
CREATE TABLE tag_package_owners (
Thomas Oulevey 98f397
	package_id INTEGER NOT NULL REFERENCES package(id),
Thomas Oulevey 98f397
	tag_id INTEGER NOT NULL REFERENCES tag (id),
Thomas Oulevey 98f397
	owner INTEGER NOT NULL REFERENCES users(id),
Thomas Oulevey 98f397
-- versioned - see earlier description of versioning
Thomas Oulevey 98f397
	create_event INTEGER NOT NULL REFERENCES events(id) DEFAULT get_event(),
Thomas Oulevey 98f397
	revoke_event INTEGER REFERENCES events(id),
Thomas Oulevey 98f397
	creator_id INTEGER NOT NULL REFERENCES users(id),
Thomas Oulevey 98f397
	revoker_id INTEGER REFERENCES users(id),
Thomas Oulevey 98f397
	active BOOLEAN DEFAULT 'true' CHECK (active),
Thomas Oulevey 98f397
	CONSTRAINT active_revoke_sane CHECK (
Thomas Oulevey 98f397
		(active IS NULL AND revoke_event IS NOT NULL AND revoker_id IS NOT NULL)
Thomas Oulevey 98f397
		OR (active IS NOT NULL AND revoke_event IS NULL AND revoker_id IS NULL)),
Thomas Oulevey 98f397
	PRIMARY KEY (create_event, package_id, tag_id),
Thomas Oulevey 98f397
	UNIQUE (package_id,tag_id,active)
Thomas Oulevey 98f397
) WITHOUT OIDS;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
CREATE OR REPLACE FUNCTION convert_owners() RETURNS SETOF tag_packages AS
Thomas Oulevey 98f397
$BODY$
Thomas Oulevey 98f397
DECLARE
Thomas Oulevey 98f397
    r tag_packages%rowtype;
Thomas Oulevey 98f397
    r2 tag_packages%rowtype;
Thomas Oulevey 98f397
    last_owner int;
Thomas Oulevey 98f397
BEGIN
Thomas Oulevey 98f397
    FOR r IN SELECT package_id, tag_id FROM tag_packages GROUP BY package_id, tag_id ORDER BY package_id, tag_id
Thomas Oulevey 98f397
    LOOP
Thomas Oulevey 98f397
        last_owner := 0;
Thomas Oulevey 98f397
        FOR r2 IN SELECT * FROM tag_packages WHERE package_id = r.package_id AND tag_id = r.tag_id ORDER BY create_event
Thomas Oulevey 98f397
        LOOP
Thomas Oulevey 98f397
            -- always use first and last (active) row
Thomas Oulevey 98f397
            IF last_owner = 0 OR r2.active IS TRUE THEN
Thomas Oulevey 98f397
                last_owner := r2.owner;
Thomas Oulevey 98f397
                RETURN NEXT r2; -- return current row of SELECT
Thomas Oulevey 98f397
            ELSE
Thomas Oulevey 98f397
                -- copy others only if owner changed
Thomas Oulevey 98f397
                IF last_owner <> r2.owner THEN
Thomas Oulevey 98f397
                    RETURN NEXT r2;
Thomas Oulevey 98f397
                    last_owner := r2.owner;
Thomas Oulevey 98f397
                END IF;
Thomas Oulevey 98f397
            END IF;
Thomas Oulevey 98f397
        END LOOP;
Thomas Oulevey 98f397
    END LOOP;
Thomas Oulevey 98f397
    RETURN;
Thomas Oulevey 98f397
END
Thomas Oulevey 98f397
$BODY$
Thomas Oulevey 98f397
LANGUAGE plpgsql;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
INSERT INTO tag_package_owners (SELECT package_id, tag_id, owner, create_event, revoke_event, creator_id, revoker_id, active FROM convert_owners());
Thomas Oulevey 98f397
DROP INDEX IF EXISTS tag_packages_owner;
Thomas Oulevey 98f397
ALTER TABLE tag_packages DROP COLUMN owner;
Thomas Oulevey 98f397
DROP FUNCTION convert_owners();
Thomas Oulevey 98f397
Thomas Oulevey 98f397
-- add compressed iso-compressed, vhd-compressed, vhdx-compressed, and vmdk-compressed
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('iso-compressed', 'Compressed iso image', 'iso.gz iso.xz');
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('vhd-compressed', 'Compressed VHD image', 'vhd.gz vhd.xz');
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('vhdx-compressed', 'Compressed VHDx image', 'vhd.gz vhd.xz');
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('vmdk-compressed', 'Compressed VMDK image', 'vmdk.gz vmdk.xz');
Thomas Oulevey 98f397
Thomas Oulevey 98f397
-- add kernel-image and imitramfs
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('kernel-image', 'Kernel BZ2 Image', 'vmlinuz vmlinuz.gz vmlinuz.xz');
Thomas Oulevey 98f397
insert into archivetypes (name, description, extensions) values ('initramfs', 'Compressed Initramfs Image', 'img');
Thomas Oulevey 98f397
Thomas Oulevey 98f397
-- schema update for https://pagure.io/koji/issue/1629
Thomas Oulevey 98f397
CREATE TABLE user_krb_principals (
Thomas Oulevey 98f397
    user_id INTEGER NOT NULL REFERENCES users(id),
Thomas Oulevey 98f397
    krb_principal VARCHAR(255) NOT NULL UNIQUE,
Thomas Oulevey 98f397
    PRIMARY KEY (user_id, krb_principal)
Thomas Oulevey 98f397
) WITHOUT OIDS;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
INSERT INTO user_krb_principals ( SELECT id, krb_principal FROM users WHERE users.krb_principal IS NOT NULL);
Thomas Oulevey 98f397
Thomas Oulevey 98f397
ALTER TABLE users DROP COLUMN krb_principal;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
-- Disallow duplicate content generator names
Thomas Oulevey 98f397
ALTER TABLE content_generator ADD UNIQUE (name);
Thomas Oulevey 98f397
ALTER TABLE content_generator ALTER COLUMN name SET NOT NULL;
Thomas Oulevey 98f397
Thomas Oulevey 98f397
Thomas Oulevey 98f397
-- add all basic permissions
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'dist-repo' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'dist-repo');
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'host' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'host');
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'image-import' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'image-import');
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'sign' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'sign');
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'tag' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'tag');
Thomas Oulevey 98f397
INSERT INTO permissions (name) SELECT 'target' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE name = 'target');
Thomas Oulevey 98f397
Thomas Oulevey 98f397
COMMIT;