schema: identify hash values by an integerhashid

This one is a bit more complex, than the other transformations, because
the new hashvalue table has to be cleaned with a trigger. During a test
import the -wal file exploded. The resulting db is similar in size to
the original.

1 files changed, 5 insertions, 2 deletions

diff --git a/schema.sql b/schema.sql
index e9e0bcc..1e33bd0 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,10 +1,13 @@
 CREATE TABLE package (id INTEGER PRIMARY KEY, name TEXT UNIQUE, version TEXT, architecture TEXT, source TEXT);
 CREATE TABLE content (id INTEGER PRIMARY KEY, pid INTEGER, filename TEXT, size INTEGER, FOREIGN KEY (pid) REFERENCES package(id) ON DELETE CASCADE);
-CREATE TABLE hash (cid INTEGER, function TEXT, hash TEXT, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE);
+CREATE TABLE hashvalue (id INTEGER PRIMARY KEY, hash TEXT UNIQUE NOT NULL);
+CREATE TABLE hash (cid INTEGER, function TEXT, hid INTEGER, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE, FOREIGN KEY (hid) REFERENCES hashvalue(id));
+CREATE TRIGGER hashvalue_cleanup_trigger AFTER DELETE ON hash FOR EACH ROW BEGIN
+	DELETE FROM hashvalue WHERE hashvalue.id = old.hid AND NOT EXISTS (SELECT hid FROM hash WHERE hash.hid = old.hid); END;
 CREATE TABLE dependency (pid INTEGER, required TEXT, FOREIGN KEY (pid) REFERENCES package(id) ON DELETE CASCADE);
 CREATE INDEX content_package_size_index ON content (pid, size);
 CREATE INDEX hash_cid_index ON hash (cid);
-CREATE INDEX hash_hash_index ON hash (hash);
+CREATE INDEX hash_hid_index ON hash (hid);
 
 CREATE TABLE sharing (pid1 INTEGER, pid2 INTEGER, func1 TEXT, func2 TEXT, files INTEGER, size INTEGER, FOREIGN KEY (pid1) REFERENCES package(id) ON DELETE CASCADE, FOREIGN KEY (pid2) REFERENCES package(id) ON DELETE CASCADE);
 CREATE INDEX sharing_insert_index ON sharing (pid1, pid2, func1, func2);