New Schema

CREATE TABLE user ( nick varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, hostmask varchar NOT NULL, releaseCount integer DEFAULT 0 );CREATE TABLE network ( channel varchar UNIQUE NOT NULL COLLATE NOCASE, network varchar UNIQUE NOT NULL COLLATE NOCASE, PRIMARY KEY(channel, network) );CREATE TABLE category ( categoryName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, releasecount integer DEFAULT 0, nukeCount integer DEFAULT 0 );CREATE TABLE pre ( releaseName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, category varchar NOT NULL CONSTRAINT fk_precategory REFERENCES category(categoryName), pretime integer NOT NULL, releaseGroup varchar UNIQUE NOT NULL COLLATE NOCASE, userID integer NOT NULL CONSTRAINT fk_preuser REFERENCES user(ROWID), networkID integer NOT NULL CONSTRAINT fk_prenetwork REFERENCES network(ROWID), nukeID integer);CREATE TABLE delpre ( releaseName varchar NOT NULL PRIMARY KEY, category varchar NOT NULL, pretime integer NOT NULL, releaseGroup varchar NOT NULL, userID integer NOT NULL, networkID integer NOT NULL, nukeID integer );CREATE TABLE nuke ( releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_nukepre REFERENCES pre(releaseName) ON DELETE CASCADE, nuketime integer NOT NULL, nukeReason varchar NOT NULL, userID integer NOT NULL CONSTRAINT fk_nukeuser REFERENCES user(ROWID), networkID integer NOT NULL CONSTRAINT fk_nukenetwork REFERENCES network(ROWID), PRIMARY KEY(releaseName, nukeReason) );CREATE TABLE unnuke ( releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_unnukepre REFERENCES pre(releaseName) ON DELETE CASCADE, unnuketime integer NOT NULL, unnukeReason varchar NOT NULL, userID integer NOT NULL CONSTRAINT fk_unnukeuser REFERENCES user(ROWID), networkID integer NOT NULL CONSTRAINT fk_unnukenetwork REFERENCES network(ROWID), PRIMARY KEY(releaseName, unnukeReason) );CREATE TABLE info ( releaseName varchar UNIQUE NOT NULL COLLATE NOCASE CONSTRAINT fk_infopre REFERENCES pre(releaseName) ON DELETE CASCADE, releaseSize integer, fileCount integer, userID integer NOT NULL CONSTRAINT fk_infouser REFERENCES user(ROWID), networkID integer NOT NULL CONSTRAINT fk_infonetwork REFERENCES network(ROWID), PRIMARY KEY(releaseName) );CREATE TABLE stats ( groupName varchar UNIQUE NOT NULL COLLATE NOCASE PRIMARY KEY, releaseCount integer DEFAULT 0, nukeCount integer DEFAULT 0 );-- Foreign Key Preventing insertCREATE TRIGGER fki_pre_category_category_categoryNameBEFORE INSERT ON [pre]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_category_category_categoryName"')  WHERE (SELECT categoryName FROM category WHERE categoryName = NEW.category) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_pre_category_category_categoryNameBEFORE UPDATE ON [pre]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_category_category_categoryName"')      WHERE (SELECT categoryName FROM category WHERE categoryName = NEW.category) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_pre_category_category_categoryNameBEFORE DELETE ON categoryFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "category" violates foreign key constraint "fkd_pre_category_category_categoryName"')  WHERE (SELECT category FROM pre WHERE category = OLD.categoryName) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_pre_userID_user_ROWIDBEFORE INSERT ON [pre]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_userID_user_ROWID"')  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_pre_userID_user_ROWIDBEFORE UPDATE ON [pre]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_userID_user_ROWID"')      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_pre_userID_user_ROWIDBEFORE DELETE ON userFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_pre_userID_user_ROWID"')  WHERE (SELECT userID FROM pre WHERE userID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_pre_networkID_network_ROWIDBEFORE INSERT ON [pre]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "pre" violates foreign key constraint "fki_pre_networkID_network_ROWID"')  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_pre_networkID_network_ROWIDBEFORE UPDATE ON [pre]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "pre" violates foreign key constraint "fku_pre_networkID_network_ROWID"')      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_pre_networkID_network_ROWIDBEFORE DELETE ON networkFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_pre_networkID_network_ROWID"')  WHERE (SELECT networkID FROM pre WHERE networkID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_nuke_releaseName_pre_releaseNameBEFORE INSERT ON [nuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_releaseName_pre_releaseName"')  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_nuke_releaseName_pre_releaseNameBEFORE UPDATE ON [nuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_releaseName_pre_releaseName"')      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Cascading DeleteCREATE TRIGGER fkdc_nuke_releaseName_pre_releaseNameBEFORE DELETE ON preFOR EACH ROW BEGIN    DELETE FROM nuke WHERE nuke.releaseName = OLD.releaseName;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_nuke_userID_user_ROWIDBEFORE INSERT ON [nuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_userID_user_ROWID"')  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_nuke_userID_user_ROWIDBEFORE UPDATE ON [nuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_userID_user_ROWID"')      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_nuke_userID_user_ROWIDBEFORE DELETE ON userFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_nuke_userID_user_ROWID"')  WHERE (SELECT userID FROM nuke WHERE userID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_nuke_networkID_network_ROWIDBEFORE INSERT ON [nuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "nuke" violates foreign key constraint "fki_nuke_networkID_network_ROWID"')  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_nuke_networkID_network_ROWIDBEFORE UPDATE ON [nuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "nuke" violates foreign key constraint "fku_nuke_networkID_network_ROWID"')      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_nuke_networkID_network_ROWIDBEFORE DELETE ON networkFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_nuke_networkID_network_ROWID"')  WHERE (SELECT networkID FROM nuke WHERE networkID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_unnuke_releaseName_pre_releaseNameBEFORE INSERT ON [unnuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_releaseName_pre_releaseName"')  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_unnuke_releaseName_pre_releaseNameBEFORE UPDATE ON [unnuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_releaseName_pre_releaseName"')      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Cascading DeleteCREATE TRIGGER fkdc_unnuke_releaseName_pre_releaseNameBEFORE DELETE ON preFOR EACH ROW BEGIN    DELETE FROM unnuke WHERE unnuke.releaseName = OLD.releaseName;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_unnuke_userID_user_ROWIDBEFORE INSERT ON [unnuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_userID_user_ROWID"')  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_unnuke_userID_user_ROWIDBEFORE UPDATE ON [unnuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_userID_user_ROWID"')      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_unnuke_userID_user_ROWIDBEFORE DELETE ON userFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_unnuke_userID_user_ROWID"')  WHERE (SELECT userID FROM unnuke WHERE userID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_unnuke_networkID_network_ROWIDBEFORE INSERT ON [unnuke]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "unnuke" violates foreign key constraint "fki_unnuke_networkID_network_ROWID"')  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_unnuke_networkID_network_ROWIDBEFORE UPDATE ON [unnuke]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "unnuke" violates foreign key constraint "fku_unnuke_networkID_network_ROWID"')      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_unnuke_networkID_network_ROWIDBEFORE DELETE ON networkFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_unnuke_networkID_network_ROWID"')  WHERE (SELECT networkID FROM unnuke WHERE networkID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_info_releaseName_pre_releaseNameBEFORE INSERT ON [info]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_releaseName_pre_releaseName"')  WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_info_releaseName_pre_releaseNameBEFORE UPDATE ON [info]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_releaseName_pre_releaseName"')      WHERE (SELECT releaseName FROM pre WHERE releaseName = NEW.releaseName) IS NULL;END;-- Cascading DeleteCREATE TRIGGER fkdc_info_releaseName_pre_releaseNameBEFORE DELETE ON preFOR EACH ROW BEGIN    DELETE FROM info WHERE info.releaseName = OLD.releaseName;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_info_userID_user_ROWIDBEFORE INSERT ON [info]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_userID_user_ROWID"')  WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_info_userID_user_ROWIDBEFORE UPDATE ON [info]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_userID_user_ROWID"')      WHERE (SELECT ROWID FROM user WHERE ROWID = NEW.userID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_info_userID_user_ROWIDBEFORE DELETE ON userFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "user" violates foreign key constraint "fkd_info_userID_user_ROWID"')  WHERE (SELECT userID FROM info WHERE userID = OLD.ROWID) IS NOT NULL;END;-- Foreign Key Preventing insertCREATE TRIGGER fki_info_networkID_network_ROWIDBEFORE INSERT ON [info]FOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'insert on table "info" violates foreign key constraint "fki_info_networkID_network_ROWID"')  WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing updateCREATE TRIGGER fku_info_networkID_network_ROWIDBEFORE UPDATE ON [info]FOR EACH ROW BEGIN    SELECT RAISE(ROLLBACK, 'update on table "info" violates foreign key constraint "fku_info_networkID_network_ROWID"')      WHERE (SELECT ROWID FROM network WHERE ROWID = NEW.networkID) IS NULL;END;-- Foreign key preventing deleteCREATE TRIGGER fkd_info_networkID_network_ROWIDBEFORE DELETE ON networkFOR EACH ROW BEGIN  SELECT RAISE(ROLLBACK, 'delete on table "network" violates foreign key constraint "fkd_info_networkID_network_ROWID"')  WHERE (SELECT networkID FROM info WHERE networkID = OLD.ROWID) IS NOT NULL;END;CREATE TRIGGER statsCounterAFTER INSERT ON preBEGIN  INSERT INTO stats (groupName) SELECT new.releaseGroup WHERE new.releaseGroup NOT IN (SELECT groupName FROM stats);  UPDATE stats SET releaseCount = releaseCount+1 WHERE groupName = new.releaseGroup;  UPDATE stats SET releaseCount = releaseCount+1 WHERE groupName = 'COMPLETE_DB';  UPDATE category SET releaseCount = releaseCount+1 WHERE categoryName = new.category;  UPDATE user SET releaseCount = releaseCount+1 WHERE ROWID = new.userID;END;CREATE TRIGGER flag_nuke AFTER INSERT ON nukeBEGIN  UPDATE pre SET nukeID = new.ROWID WHERE releaseName = new.releaseName;  UPDATE stats SET nukecount = nukeCount+1 WHERE groupName = (SELECT releaseGroup FROM pre WHERE releaseName = new.releaseName LIMIT 1);  UPDATE stats SET nukeCount = nukeCount+1 WHERE groupName = 'COMPLETE_DB';  UPDATE category SET nukecount = nukeCount+1 WHERE categoryName = (SELECT category FROM pre WHERE releaseName = new.releaseName LIMIT 1);END;CREATE TRIGGER unflag_nuke AFTER INSERT ON unnukeBEGIN  UPDATE pre SET nukeID = NULL WHERE releaseName = new.releaseName;  UPDATE stats SET nukeCount = nukeCount-1 WHERE groupName = 'COMPLETE_DB';  UPDATE stats SET nukeCount = nukeCount-1 WHERE groupName = (SELECT releaseGroup FROM pre WHERE releaseName = new.releaseName LIMIT 1);  END;CREATE TRIGGER recycleBin BEFORE DELETE ON preBEGIN  INSERT INTO delpre VALUES ( old.releaseName, old.category, old.pretime, old.releaseGroup, old.userID, old.networkID, old.nukeID );  UPDATE stats SET releasecount = releaseCount-1 WHERE groupName = 'COMPLETE_DB';END;