From d2dd07de4e0d9b8c00cd2db49aa2e7c0a5150bbc Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 24 Jan 2010 09:45:02 +0100 Subject: Versioned the deleting and locking of database entries This is implemented by adding ihid (item hidden) and ilock (item locked) columns to the changes table, The (vn|release|producer).(hidden|locked) columns now work as a cache, refering to the changes.(ihid|ilock) columns with changes.id = (vn|release|producer).latest. The cached columns are updated automatically each time a new revision is inserted. This is a pretty large change, bugs are quite likely. --- util/sql/all.sql | 4 ++++ util/sql/func.sql | 42 ++++++++++++++++++++++++++-------- util/sql/schema.sql | 4 +++- util/updates/update_2.11.sql | 54 ++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 93 insertions(+), 11 deletions(-) create mode 100644 util/updates/update_2.11.sql (limited to 'util') diff --git a/util/sql/all.sql b/util/sql/all.sql index 04e1dbf9..b2e82bfb 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -27,6 +27,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid -- triggers +CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); + CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); diff --git a/util/sql/func.sql b/util/sql/func.sql index d87450a6..f32bd2ee 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -125,12 +125,20 @@ BEGIN iid integer, requester integer, ip inet, - comments text + comments text, + ihid boolean, + ilock boolean ); EXCEPTION WHEN duplicate_table THEN TRUNCATE edit_revision; END; - INSERT INTO edit_revision (type, iid) VALUES (t, i); + INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t, + ( SELECT vid FROM vn_rev WHERE id = i + UNION SELECT rid FROM releases_rev WHERE id = i + UNION SELECT pid FROM producers_rev WHERE id = i), + COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), + COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) + ); END; $$ LANGUAGE plpgsql; @@ -159,8 +167,8 @@ BEGIN LIMIT 1; END IF; -- insert change - INSERT INTO changes (type, requester, ip, comments, rev) - SELECT t, requester, ip, comments, r.rev + INSERT INTO changes (type, requester, ip, comments, ihid, ilock, rev) + SELECT t, requester, ip, comments, ihid, ilock, r.rev FROM edit_revision RETURNING id INTO r.cid; -- insert DB item @@ -196,13 +204,12 @@ BEGIN EXCEPTION WHEN duplicate_table THEN TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; END; + PERFORM edit_revtable('v', cid); -- new VN, load defaults IF cid IS NULL THEN - PERFORM edit_revtable('v', NULL); INSERT INTO edit_vn DEFAULT VALUES; -- otherwise, load revision ELSE - PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid)); INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid; INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid; INSERT INTO edit_vn_relations SELECT vid2, relation FROM vn_relations WHERE vid1 = cid; @@ -252,13 +259,12 @@ BEGIN EXCEPTION WHEN duplicate_table THEN TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; END; + PERFORM edit_revtable('r', cid); -- new release IF cid IS NULL THEN - PERFORM edit_revtable('r', NULL); INSERT INTO edit_release DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid)); INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid; INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid; INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid; @@ -306,13 +312,12 @@ BEGIN EXCEPTION WHEN duplicate_table THEN TRUNCATE edit_producer, edit_producer_relations; END; + PERFORM edit_revtable('p', cid); -- new producer IF cid IS NULL THEN - PERFORM edit_revtable('p', NULL); INSERT INTO edit_producer DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid; INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid; END IF; @@ -553,3 +558,20 @@ BEGIN END; $$ LANGUAGE plpgsql; + + +-- update (vn|release|producer).(hidden|locked) on a new revision +-- NOTE: this is a /before/ trigger, it modifies NEW +CREATE OR REPLACE FUNCTION update_hidlock() RETURNS trigger AS $$ +DECLARE + r record; +BEGIN + IF OLD.latest IS DISTINCT FROM NEW.latest THEN + SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest; + NEW.hidden := r.ihid; + NEW.locked := r.ilock; + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/sql/schema.sql b/util/sql/schema.sql index d5286a94..cbd061c4 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -20,7 +20,9 @@ CREATE TABLE changes ( added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '' + comments text NOT NULL DEFAULT '', + ihid boolean NOT NULL DEFAULT FALSE, + ilock boolean NOT NULL DEFAULT FALSE ); -- producers diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql new file mode 100644 index 00000000..d311165c --- /dev/null +++ b/util/updates/update_2.11.sql @@ -0,0 +1,54 @@ + +ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE; +ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE; + +\i util/sql/func.sql + +CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); + + +CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ +BEGIN + IF t = 'v' THEN + PERFORM edit_vn_init(latest) FROM vn WHERE id = iid; + IF EXISTS(SELECT 1 FROM vn WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_vn_commit(); + ELSIF t = 'r' THEN + PERFORM edit_release_init(latest) FROM releases WHERE id = iid; + IF EXISTS(SELECT 1 FROM releases WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_release_commit(); + ELSE + PERFORM edit_producer_init(latest) FROM producers WHERE id = iid; + IF EXISTS(SELECT 1 FROM producers WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_producer_commit(); + END IF; +END; +$$ LANGUAGE plpgsql; + + SELECT 'v', COUNT(*) FROM (SELECT tmp_edit_hidlock('v', id) FROM vn WHERE (hidden OR locked)) x +UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases WHERE hidden OR locked) x +UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x; +DROP FUNCTION tmp_edit_hidlock(text, integer); + + + -- cgit v1.2.3