From 4cd588ae898c2abc8035cf0165ccdd038f2321bd Mon Sep 17 00:00:00 2001
From: Dan McGee <dan@archlinux.org>
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 <dan@archlinux.org>
---
 packages/sql/update.sqlite3.sql | 30 ++++++++++++++++++++++++++++++
 1 file changed, 30 insertions(+)
 create mode 100644 packages/sql/update.sqlite3.sql

(limited to 'packages/sql/update.sqlite3.sql')

diff --git a/packages/sql/update.sqlite3.sql b/packages/sql/update.sqlite3.sql
new file mode 100644
index 0000000..6f151bd
--- /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-24-g4f1b

