From ac4c52c7a4501e90ed53f3ca5780bec4551a8c9f Mon Sep 17 00:00:00 2001 From: Helmut Grohne Date: Thu, 7 Mar 2013 08:43:15 +0100 Subject: enable enforcing foreign keys --- autoimport.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) (limited to 'autoimport.py') diff --git a/autoimport.py b/autoimport.py index a7ea5ff..bc2c6aa 100755 --- a/autoimport.py +++ b/autoimport.py @@ -14,6 +14,7 @@ def main(): urlbase = sys.argv[1] db = sqlite3.connect("test.sqlite3") cur = db.cursor() + cur.execute("PRAGMA foreign_keys = ON;") cur.execute("SELECT package, version FROM package;") knownpkgs = dict((row[0], row[1]) for row in cur.fetchall()) @@ -37,7 +38,10 @@ def main(): delpkgs = set(knownpkgs) - distpkgs print("clearing packages %s" % " ".join(delpkgs)) - cur.execute("PRAGMA foreign_keys=1;") + cur.executemany("DELETE FROM sharing WHERE package1 = ?", + ((pkg,) for pkg in delpkgs)) + cur.executemany("DELETE FROM sharing WHERE package2 = ?", + ((pkg,) for pkg in delpkgs)) cur.executemany("DELETE FROM content WHERE package = ?;", ((pkg,) for pkg in delpkgs)) cur.executemany("DELETE FROM dependency WHERE package = ?;", -- cgit v1.2.3 From e0dd701f41a874c1c06702d849fe4b860a450e3e Mon Sep 17 00:00:00 2001 From: Helmut Grohne Date: Thu, 7 Mar 2013 09:05:48 +0100 Subject: use "ON DELETE CASCADE" clauses --- autoimport.py | 10 ++-------- schema.sql | 6 +++--- 2 files changed, 5 insertions(+), 11 deletions(-) (limited to 'autoimport.py') diff --git a/autoimport.py b/autoimport.py index bc2c6aa..453a839 100755 --- a/autoimport.py +++ b/autoimport.py @@ -38,16 +38,10 @@ def main(): delpkgs = set(knownpkgs) - distpkgs print("clearing packages %s" % " ".join(delpkgs)) - cur.executemany("DELETE FROM sharing WHERE package1 = ?", - ((pkg,) for pkg in delpkgs)) - cur.executemany("DELETE FROM sharing WHERE package2 = ?", - ((pkg,) for pkg in delpkgs)) - cur.executemany("DELETE FROM content WHERE package = ?;", - ((pkg,) for pkg in delpkgs)) - cur.executemany("DELETE FROM dependency WHERE package = ?;", - ((pkg,) for pkg in delpkgs)) cur.executemany("DELETE FROM package WHERE package = ?;", ((pkg,) for pkg in delpkgs)) + # Tables content, dependency and sharing will also be pruned + # due to ON DELETE CASCADE clauses. db.commit() if __name__ == "__main__": diff --git a/schema.sql b/schema.sql index 72357e3..c329465 100644 --- a/schema.sql +++ b/schema.sql @@ -1,9 +1,9 @@ CREATE TABLE package (package TEXT PRIMARY KEY, version TEXT, architecture TEXT, source TEXT); -CREATE TABLE content (package TEXT, filename TEXT, size INTEGER, function TEXT, hash TEXT, FOREIGN KEY (package) REFERENCES package(package)); -CREATE TABLE dependency (package TEXT, required TEXT, FOREIGN KEY (package) REFERENCES package(package)); +CREATE TABLE content (package TEXT, filename TEXT, size INTEGER, function TEXT, hash TEXT, FOREIGN KEY (package) REFERENCES package(package) 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 INDEX content_hash_index ON content (hash); -CREATE TABLE sharing (package1 TEXT, package2 TEXT, func1 TEXT, func2 TEXT, files INTEGER, size INTEGER, FOREIGN KEY (package1) REFERENCES package(package), FOREIGN KEY (package2) REFERENCES package(package)); +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 INDEX sharing_package_index ON sharing (package1); -- cgit v1.2.3