From 6f88561d726327c90f83b8aad1db26abbd4cdf1e Mon Sep 17 00:00:00 2001
From: Helmut Grohne <helmut@subdivi.de>
Date: Tue, 23 Jul 2013 18:53:55 +0200
Subject: schema: reference hash functions by integer key

This already worked quite well for package.id. On a test data set of 5%
size this transformation reduces the database size by about 4%.
---
 schema.sql | 14 +++++++++++---
 1 file changed, 11 insertions(+), 3 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index e9e0bcc..cb6a2c5 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,11 +1,19 @@
 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 function (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
+INSERT INTO function (name) VALUES ("sha512"), ("gzip_sha512"), ("image_sha512");
+CREATE TABLE hash (cid INTEGER, fid INTEGER NOT NULL, hash TEXT, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE, FOREIGN KEY (fid) REFERENCES function(id));
 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 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);
+CREATE TABLE sharing (
+	pid1 INTEGER NOT NULL REFERENCES package(id) ON DELETE CASCADE,
+	pid2 INTEGER NOT NULL REFERENCES package(id) ON DELETE CASCADE,
+	fid1 INTEGER NOT NULL REFERENCES function(id),
+	fid2 INTEGER NOT NULL REFERENCES function(id),
+	files INTEGER,
+	size INTEGER);
+CREATE INDEX sharing_insert_index ON sharing (pid1, pid2, fid1, fid2);
 CREATE TABLE duplicate (cid INTEGER PRIMARY KEY, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE);
-- 
cgit v1.2.3

