diff options
author | Yorhel <git@yorhel.nl> | 2009-07-18 13:32:32 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-07-18 13:32:32 +0200 |
commit | 8299ff15d3c1940377cb830c0ac4f3585cf228cf (patch) | |
tree | f24db1c4bbebcaf3651a2d8509fecfe75e6af6e4 /util | |
parent | 07467841ff1670b2f365aa265de94fb6b5c421b3 (diff) |
Automatically insert rows into anime table for new vn_anime.aid items
This is a lot less error-prone than doing it from Perl.
<3 PostgreSQL
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 12 | ||||
-rw-r--r-- | util/updates/update_2.6.sql | 15 |
2 files changed, 27 insertions, 0 deletions
diff --git a/util/dump.sql b/util/dump.sql index db185d18..40347df8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -687,6 +687,18 @@ CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_post CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +-- insert rows into anime for new vn_anime.aid items +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN + INSERT INTO anime (id) VALUES (NEW.aid); + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); + diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql index 2b0f5478..e3516e30 100644 --- a/util/updates/update_2.6.sql +++ b/util/updates/update_2.6.sql @@ -33,3 +33,18 @@ ALTER TABLE anime ALTER COLUMN year DROP NOT NULL; ALTER TABLE anime ALTER COLUMN year DROP DEFAULT; UPDATE anime SET year = NULL WHERE year = 0; + +-- automatically insert rows into the anime table for unknown aids +-- when inserted into vn_anime +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN + INSERT INTO anime (id) VALUES (NEW.aid); + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); + + |