-- upgrade script to migrate the Koji database schema
-- from version 1.17 to 1.18


BEGIN;

-- new table for notifications' optouts
CREATE TABLE build_notifications_block (
    id SERIAL NOT NULL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id),
    package_id INTEGER REFERENCES package (id),
    tag_id INTEGER REFERENCES tag (id)
) WITHOUT OIDS;

-- add tgz to list of tar's extensions
UPDATE archivetypes SET extensions = 'tar tar.gz tar.bz2 tar.xz tgz' WHERE name = 'tar';
INSERT INTO archivetypes (name, description, extensions) VALUES ('vhdx', 'Hyper-V Virtual Hard Disk v2 image', 'vhdx');

-- add compressed raw-gzip and compressed qcow2 images
insert into archivetypes (name, description, extensions) values ('raw-gz', 'GZIP compressed raw disk image', 'raw.gz');
insert into archivetypes (name, description, extensions) values ('qcow2-compressed', 'Compressed QCOW2 image', 'qcow2.gz qcow2.xz');

-- add better index for sessions
CREATE INDEX sessions_expired ON sessions(expired);

-- table for content generator build reservations
CREATE TABLE build_reservations (
	build_id INTEGER NOT NULL REFERENCES build(id),
	token VARCHAR(64),
        created TIMESTAMP NOT NULL,
	PRIMARY KEY (build_id)
) WITHOUT OIDS;
CREATE INDEX build_reservations_created ON build_reservations(created);

ALTER TABLE build ADD COLUMN cg_id INTEGER REFERENCES content_generator(id);


-- new indexes added in 1.18
CREATE INDEX tag_packages_active_tag_id ON tag_packages(active, tag_id);
CREATE INDEX tag_packages_create_event ON tag_packages(create_event);
CREATE INDEX tag_packages_revoke_event ON tag_packages(revoke_event);
CREATE INDEX tag_packages_owner ON tag_packages(owner);


COMMIT;