Merge branch functionid

Actual savings on the full data set are around 7%.

Conflicts:
	README

5 files changed, 31 insertions, 21 deletions

diff --git a/README b/README
index c749a86..ef0ae48 100644
--- a/README
+++ b/README
@@ -43,12 +43,12 @@ Finding the 100 largest files shared with multiple packages.
 Finding those top 100 files that save most space when being reduced to only
 one copy in the archive.
 
-    SELECT hash, sum(size)-min(size), count(*), count(distinct pid) FROM content JOIN hash ON content.id = hash.cid WHERE hash.function = "sha512" GROUP BY hash ORDER BY sum(size)-min(size) DESC LIMIT 100;
+    SELECT hash, sum(size)-min(size), count(*), count(distinct pid) FROM content JOIN hash ON content.id = hash.cid JOIN function ON hash.fid = function.id WHERE function.name = "sha512" GROUP BY hash ORDER BY sum(size)-min(size) DESC LIMIT 100;
 
 Finding PNG images that do not carry a .png file extension.
 
-    SELECT package.name, content.filename, content.size FROM content JOIN hash ON content.id = hash.cid JOIN package ON content.pid = package.id WHERE function = "image_sha512" AND lower(filename) NOT LIKE "%.png";
+    SELECT package.name, content.filename, content.size FROM content JOIN hash ON content.id = hash.cid JOIN package ON content.pid = package.id JOIN function ON hash.fid = function.id WHERE function.name = "image_sha512" AND lower(filename) NOT LIKE "%.png";
 
 Finding .gz files which either are not gziped or contain errors.
 
-    SELECT package.name, content.filename FROM content JOIN package ON content.pid = package.id WHERE filename LIKE "%.gz" AND (SELECT count(*) FROM hash WHERE hash.cid = content.id AND hash.function = "gzip_sha512") = 0;
+    SELECT package.name, content.filename FROM content JOIN package ON content.pid = package.id WHERE filename LIKE "%.gz" AND (SELECT count(*) FROM hash JOIN function ON hash.fid = function.id WHERE hash.cid = content.id AND function.name = "gzip_sha512") = 0;
diff --git a/readyaml.py b/readyaml.py
index bb8ac54..21b1ca1 100755
--- a/readyaml.py
+++ b/readyaml.py
@@ -25,6 +25,8 @@ def readyaml(db, stream):
         pid = None
 
     cur.execute("BEGIN;")
+    cur.execute("SELECT name, id FROM function;")
+    funcmapping = dict(cur.fetchall())
     if pid is not None:
         cur.execute("DELETE FROM content WHERE pid = ?;", (pid,))
         cur.execute("DELETE FROM dependency WHERE pid = ?;", (pid,))
@@ -45,8 +47,8 @@ def readyaml(db, stream):
         cur.execute("INSERT INTO content (pid, filename, size) VALUES (?, ?, ?);",
                     (pid, entry["name"], entry["size"]))
         cid = cur.lastrowid
-        cur.executemany("INSERT INTO hash (cid, function, hash) VALUES (?, ?, ?);",
-                        ((cid, func, hexhash)
+        cur.executemany("INSERT INTO hash (cid, fid, hash) VALUES (?, ?, ?);",
+                        ((cid, funcmapping[func], hexhash)
                          for func, hexhash in entry["hashes"].items()))
     raise ValueError("missing commit block")
 
diff --git a/schema.sql b/schema.sql
index 8a94882..13a65aa 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,12 +1,20 @@
 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);
 CREATE TABLE issue (cid INTEGER REFERENCES content(id) ON DELETE CASCADE, issue TEXT);
diff --git a/update_sharing.py b/update_sharing.py
index 62a3ab5..4669759 100755
--- a/update_sharing.py
+++ b/update_sharing.py
@@ -5,23 +5,23 @@ import sqlite3
 from dedup.utils import fetchiter
 
 def add_values(cursor, insert_key, files, size):
-    cursor.execute("UPDATE sharing SET files = files + ?, size = size + ? WHERE pid1 = ? AND pid2 = ? AND func1 = ? AND func2 = ?;",
+    cursor.execute("UPDATE sharing SET files = files + ?, size = size + ? WHERE pid1 = ? AND pid2 = ? AND fid1 = ? AND fid2 = ?;",
                    (files, size) + insert_key)
     if cursor.rowcount > 0:
         return
-    cursor.execute("INSERT INTO sharing (pid1, pid2, func1, func2, files, size) VALUES (?, ?, ?, ?, ?, ?);",
+    cursor.execute("INSERT INTO sharing (pid1, pid2, fid1, fid2, files, size) VALUES (?, ?, ?, ?, ?, ?);",
                    insert_key + (files, size))
 
 def compute_pkgdict(rows):
     pkgdict = dict()
-    for pid, _, filename, size, function in rows:
+    for pid, _, filename, size, fid in rows:
         funcdict = pkgdict.setdefault(pid, {})
-        funcdict.setdefault(function, []).append((size, filename))
+        funcdict.setdefault(fid, []).append((size, filename))
     return pkgdict
 
 def process_pkgdict(cursor, pkgdict):
     for pid1, funcdict1 in pkgdict.items():
-        for function1, files in funcdict1.items():
+        for fid1, files in funcdict1.items():
             numfiles = len(files)
             size = sum(entry[0] for entry in files)
             for pid2, funcdict2 in pkgdict.items():
@@ -33,8 +33,8 @@ def process_pkgdict(cursor, pkgdict):
                 else:
                     pkgnumfiles = numfiles
                     pkgsize = size
-                for function2 in funcdict2.keys():
-                    insert_key = (pid1, pid2, function1, function2)
+                for fid2 in funcdict2.keys():
+                    insert_key = (pid1, pid2, fid1, fid2)
                     add_values(cursor, insert_key, pkgnumfiles, pkgsize)
 
 def main():
@@ -47,7 +47,7 @@ def main():
     readcur = db.cursor()
     readcur.execute("SELECT hash FROM hash GROUP BY hash HAVING count(*) > 1;")
     for hashvalue, in fetchiter(readcur):
-        cur.execute("SELECT content.pid, content.id, content.filename, content.size, hash.function FROM hash JOIN content ON hash.cid = content.id WHERE hash = ?;",
+        cur.execute("SELECT content.pid, content.id, content.filename, content.size, hash.fid FROM hash JOIN content ON hash.cid = content.id WHERE hash = ?;",
                     (hashvalue,))
         rows = cur.fetchall()
         print("processing hash %s with %d entries" % (hashvalue, len(rows)))
diff --git a/webapp.py b/webapp.py
index c442ebe..c080d41 100755
--- a/webapp.py
+++ b/webapp.py
@@ -262,7 +262,7 @@ class Application(object):
     def cached_sharedstats(self, pid):
         cur = self.db.cursor()
         sharedstats = {}
-        cur.execute("SELECT pid2, package.name, func1, func2, files, size FROM sharing JOIN package ON sharing.pid2 = package.id WHERE pid1 = ?;",
+        cur.execute("SELECT pid2, package.name, f1.name, f2.name, files, size FROM sharing JOIN package ON sharing.pid2 = package.id JOIN function AS f1 ON sharing.fid1 = f1.id JOIN function AS f2 ON sharing.fid2 = f2.id WHERE pid1 = ?;",
                     (pid,))
         for pid2, package2, func1, func2, files, size in fetchiter(cur):
             if (func1, func2) not in hash_functions:
@@ -297,7 +297,7 @@ class Application(object):
            from hash function pairs to hash values.
         """
         cur = self.db.cursor()
-        cur.execute("SELECT id, filename, size, hash FROM content JOIN hash ON content.id = hash.cid JOIN duplicate ON content.id = duplicate.cid WHERE pid = ? AND function = 'sha512' ORDER BY size DESC;",
+        cur.execute("SELECT content.id, content.filename, content.size, hash.hash FROM content JOIN hash ON content.id = hash.cid JOIN duplicate ON content.id = duplicate.cid JOIN function ON hash.fid = function.id WHERE pid = ? AND function.name = 'sha512' ORDER BY size DESC;",
                     (pid1,))
         cursize = -1
         files = dict()
@@ -318,7 +318,7 @@ class Application(object):
             files[hashvalue] = entry
 
             cur2 = self.db.cursor()
-            cur2.execute("SELECT ha.function, ha.hash, hb.function, filename FROM hash AS ha JOIN hash AS hb ON ha.hash = hb.hash JOIN content ON hb.cid = content.id WHERE ha.cid = ? AND pid = ?;",
+            cur2.execute("SELECT fa.name, ha.hash, fb.name, filename FROM hash AS ha JOIN hash AS hb ON ha.hash = hb.hash JOIN content ON hb.cid = content.id JOIN function AS fa ON ha.fid = fa.id JOIN function AS fb ON hb.fid = fb.id WHERE ha.cid = ? AND pid = ?;",
                          (cid, pid2))
             for func1, hashvalue, func2, filename in fetchiter(cur2):
                 entry["matches"].setdefault(filename, {})[func1, func2] = \
@@ -345,7 +345,7 @@ class Application(object):
 
     def show_hash(self, function, hashvalue):
         cur = self.db.cursor()
-        cur.execute("SELECT package.name, content.filename, content.size, hash.function FROM hash JOIN content ON hash.cid = content.id JOIN package ON content.pid = package.id WHERE hash = ?;",
+        cur.execute("SELECT package.name, content.filename, content.size, function.name FROM hash JOIN content ON hash.cid = content.id JOIN package ON content.pid = package.id JOIN function ON hash.fid = function.id WHERE hash = ?;",
                     (hashvalue,))
         entries = [dict(package=package, filename=filename, size=size,
                         function=otherfunc)
@@ -364,7 +364,7 @@ class Application(object):
         binpkgs = dict.fromkeys(pkg for pkg, in fetchiter(cur))
         if not binpkgs:
             raise NotFound
-        cur.execute("SELECT p1.name, p2.name, sharing.func1, sharing.func2, sharing.files, sharing.size FROM sharing JOIN package AS p1 ON sharing.pid1 = p1.id JOIN package AS p2 ON sharing.pid2 = p2.id WHERE p1.source = ?;",
+        cur.execute("SELECT p1.name, p2.name, f1.name, f2.name, sharing.files, sharing.size FROM sharing JOIN package AS p1 ON sharing.pid1 = p1.id JOIN package AS p2 ON sharing.pid2 = p2.id JOIN function AS f1 ON sharing.fid1 = f1.id JOIN function AS f2 ON sharing.fid2 = f2.id WHERE p1.source = ?;",
                     (package,))
         for binary, otherbin, func1, func2, files, size in fetchiter(cur):
             entry = dict(package=otherbin,