diff options
| -rw-r--r-- | packages/sql/update.postgresql_psycopg2.sql | 45 | ||||
| -rw-r--r-- | packages/sql/update.sqlite3.sql | 30 |
2 files changed, 75 insertions, 0 deletions
diff --git a/packages/sql/update.postgresql_psycopg2.sql b/packages/sql/update.postgresql_psycopg2.sql new file mode 100644 index 00000000..6d678387 --- /dev/null +++ b/packages/sql/update.postgresql_psycopg2.sql @@ -0,0 +1,45 @@ +CREATE OR REPLACE FUNCTION packages_on_insert() RETURNS trigger AS $body$ +BEGIN + INSERT INTO packages_update + (action_flag, created, package_id, arch_id, repo_id, pkgname, pkgbase, new_pkgver, new_pkgrel, new_epoch) + VALUES (1, now(), NEW.id, NEW.arch_id, NEW.repo_id, NEW.pkgname, NEW.pkgbase, NEW.pkgver, NEW.pkgrel, NEW.epoch); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION packages_on_update() RETURNS trigger AS $body$ +BEGIN + INSERT INTO packages_update + (action_flag, created, package_id, arch_id, repo_id, pkgname, pkgbase, old_pkgver, old_pkgrel, old_epoch, new_pkgver, new_pkgrel, new_epoch) + VALUES (2, now(), NEW.id, NEW.arch_id, NEW.repo_id, NEW.pkgname, NEW.pkgbase, OLD.pkgver, OLD.pkgrel, OLD.epoch, NEW.pkgver, NEW.pkgrel, NEW.epoch); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION packages_on_delete() RETURNS trigger AS $body$ +BEGIN + INSERT INTO packages_update + (action_flag, created, arch_id, repo_id, pkgname, pkgbase, old_pkgver, old_pkgrel, old_epoch) + VALUES (3, now(), OLD.arch_id, OLD.repo_id, OLD.pkgname, OLD.pkgbase, OLD.pkgver, OLD.pkgrel, OLD.epoch); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS packages_insert ON packages; +CREATE TRIGGER packages_insert + AFTER INSERT ON packages + FOR EACH ROW + EXECUTE PROCEDURE packages_on_insert(); + +DROP TRIGGER IF EXISTS packages_update ON packages; +CREATE TRIGGER packages_update + AFTER UPDATE ON packages + FOR EACH ROW + WHEN (OLD.pkgver != NEW.pkgver OR OLD.pkgrel != NEW.pkgrel OR OLD.epoch != NEW.epoch) + EXECUTE PROCEDURE packages_on_update(); + +DROP TRIGGER IF EXISTS packages_delete ON packages; +CREATE TRIGGER packages_delete + AFTER DELETE ON packages + FOR EACH ROW + EXECUTE PROCEDURE packages_on_delete(); diff --git a/packages/sql/update.sqlite3.sql b/packages/sql/update.sqlite3.sql new file mode 100644 index 00000000..6f151bdd --- /dev/null +++ b/packages/sql/update.sqlite3.sql @@ -0,0 +1,30 @@ +DROP TRIGGER IF EXISTS packages_insert; +CREATE TRIGGER packages_insert + AFTER INSERT ON packages + FOR EACH ROW + BEGIN + INSERT INTO packages_update + (action_flag, created, package_id, arch_id, repo_id, pkgname, pkgbase, new_pkgver, new_pkgrel, new_epoch) + VALUES (1, strftime('%Y-%m-%d %H:%M:%f', 'now'), NEW.id, NEW.arch_id, NEW.repo_id, NEW.pkgname, NEW.pkgbase, NEW.pkgver, NEW.pkgrel, NEW.epoch); + END; + +DROP TRIGGER IF EXISTS packages_update; +CREATE TRIGGER packages_update + AFTER UPDATE ON packages + FOR EACH ROW + WHEN (OLD.pkgver != NEW.pkgver OR OLD.pkgrel != NEW.pkgrel OR OLD.epoch != NEW.epoch) + BEGIN + INSERT INTO packages_update + (action_flag, created, package_id, arch_id, repo_id, pkgname, pkgbase, old_pkgver, old_pkgrel, old_epoch, new_pkgver, new_pkgrel, new_epoch) + VALUES (2, strftime('%Y-%m-%d %H:%M:%f', 'now'), NEW.id, NEW.arch_id, NEW.repo_id, NEW.pkgname, NEW.pkgbase, OLD.pkgver, OLD.pkgrel, OLD.epoch, NEW.pkgver, NEW.pkgrel, NEW.epoch); + END; + +DROP TRIGGER IF EXISTS packages_delete; +CREATE TRIGGER packages_delete + AFTER DELETE ON packages + FOR EACH ROW + BEGIN + INSERT INTO packages_update + (action_flag, created, arch_id, repo_id, pkgname, pkgbase, old_pkgver, old_pkgrel, old_epoch) + VALUES (3, strftime('%Y-%m-%d %H:%M:%f', 'now'), OLD.arch_id, OLD.repo_id, OLD.pkgname, OLD.pkgbase, OLD.pkgver, OLD.pkgrel, OLD.epoch); + END; |