From 14020d53fd4853aa72f159885edad1dcbfce2ee7 Mon Sep 17 00:00:00 2001 From: Helmut Grohne Date: Wed, 10 Jul 2013 16:16:45 +0200 Subject: schema: reference package table by integer key One approach to improve performance is to reduce the database size. A package name takes up 15 bytes in average. A number of a package takes up two bytes. Multiply that difference with the number of references and it should be noticeably. A small test set show a reduction by 10%. --- schema.sql | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index 94f0317..b839a51 100644 --- a/schema.sql +++ b/schema.sql @@ -1,11 +1,11 @@ -CREATE TABLE package (package TEXT PRIMARY KEY, version TEXT, architecture TEXT, source TEXT); -CREATE TABLE content (id INTEGER PRIMARY KEY, package TEXT, filename TEXT, size INTEGER, FOREIGN KEY (package) REFERENCES package(package) ON DELETE CASCADE); +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 dependency (package TEXT, required TEXT, FOREIGN KEY (package) REFERENCES package(package) ON DELETE CASCADE); -CREATE INDEX content_package_index ON content (package); +CREATE TABLE dependency (pid INTEGER, required TEXT, FOREIGN KEY (pid) REFERENCES package(id) ON DELETE CASCADE); +CREATE INDEX content_package_index ON content (pid); CREATE INDEX hash_cid_index ON hash (cid); CREATE INDEX hash_hash_index ON hash (hash); -CREATE TABLE sharing (package1 TEXT, package2 TEXT, func1 TEXT, func2 TEXT, files INTEGER, size INTEGER, FOREIGN KEY (package1) REFERENCES package(package) ON DELETE CASCADE, FOREIGN KEY (package2) REFERENCES package(package) ON DELETE CASCADE); -CREATE INDEX sharing_insert_index ON sharing (package1, package2, func1, func2); +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 duplicate (cid INTEGER PRIMARY KEY, FOREIGN KEY (cid) REFERENCES content(id) ON DELETE CASCADE); -- cgit v1.2.3