From 4cd588ae898c2abc8035cf0165ccdd038f2321bd Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Sat, 7 Jul 2012 16:50:06 -0500 Subject: Add triggers for adding package update rows This will be done instead of doing this logic at the application level, which has some subtle race conditions. When two simultaneous threads attempt to delete the same package, two update rows for the delete action are inserted. When done at the database level, we can ensure a one-to-one mapping between row operations and entries in this table. Signed-off-by: Dan McGee --- packages/sql/update.postgresql_psycopg2.sql | 45 +++++++++++++++++++++++++++++ packages/sql/update.sqlite3.sql | 30 +++++++++++++++++++ 2 files changed, 75 insertions(+) create mode 100644 packages/sql/update.postgresql_psycopg2.sql create mode 100644 packages/sql/update.sqlite3.sql (limited to 'packages/sql') 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; -- cgit v1.2.3-2-g168b From f2a6316be0b025a9ee22f22d34df1c00f60a8bdf Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Sat, 13 Apr 2013 11:56:26 -0500 Subject: Add additional pg_trgm indexes for quicker searches This allows our normal keyword-based search to be index-optimized rather than always doing full table scans. It requires the pg_trgm extension which is shipped out of the box with any sane install of PostgreSQL. Signed-off-by: Dan McGee --- packages/sql/search_indexes.postgresql_psycopg2.sql | 3 +++ 1 file changed, 3 insertions(+) create mode 100644 packages/sql/search_indexes.postgresql_psycopg2.sql (limited to 'packages/sql') diff --git a/packages/sql/search_indexes.postgresql_psycopg2.sql b/packages/sql/search_indexes.postgresql_psycopg2.sql new file mode 100644 index 00000000..a7eaf998 --- /dev/null +++ b/packages/sql/search_indexes.postgresql_psycopg2.sql @@ -0,0 +1,3 @@ +CREATE EXTENSION IF NOT EXISTS pg_trgm; +CREATE INDEX packages_pkgname_trgm_gist ON packages USING gist (UPPER(pkgname) gist_trgm_ops); +CREATE INDEX packages_pkgdesc_trgm_gist ON packages USING gist (UPPER(pkgdesc) gist_trgm_ops); -- cgit v1.2.3-2-g168b