From 74fc254a08433f5d61fcc085ef95099d5dadaac9 Mon Sep 17 00:00:00 2001
From: Sean Finney <seanius@debian.org>
Date: Sat, 28 Jun 2008 14:33:31 +0200
Subject: db "optimizations" fsvo "optimization"

stop the voodoo with home-rolled "auto increment" type stuff.

also, use sqlite "extensions" for conflict resolution (in schema for
all but the source package table, which has it in saveSourcePackage),
so that we don't need to check for pre-existing records.  the "OR REPLACE"
was doing this previously, but had wierd effects with modifying the
primary key, which would cause db inconsistency and *really* wierd effects
when combined with the triggers).
---
 db.sql | 33 ++++++++++++++++++---------------
 1 file changed, 18 insertions(+), 15 deletions(-)

(limited to 'db.sql')

diff --git a/db.sql b/db.sql
index 456a86a..ec8e9b3 100644
--- a/db.sql
+++ b/db.sql
@@ -2,35 +2,38 @@
 -- comments) must be preceded and followed by a blank line.
 
 CREATE TABLE packages (
-  id int UNIQUE NOT NULL PRIMARY KEY,
-  name text NOT NULL,
-  version text NOT NULL,
-  format text NOT NULL,
-  loc text NOT NULL,
-  diffgz_name text DEFAULT NULL,
-  diffgz_size int DEFAULT NULL,
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT NOT NULL,
+  version TEXT NOT NULL,
+  format TEXT NOT NULL,
+  loc TEXT NOT NULL,
+  diffgz_name TEXT DEFAULT NULL,
+  diffgz_size INTEGER DEFAULT NULL,
   diffgz_md5sum varchar(32) DEFAULT NULL
 );
 
 CREATE UNIQUE INDEX idx_pkg_name_version ON packages (name,version);
 
 CREATE TABLE suites (
-  id int UNIQUE NOT NULL,
-  name text UNIQUE NOT NULL PRIMARY KEY
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT UNIQUE NOT NULL,
+  UNIQUE (name) ON CONFLICT IGNORE
 );
 
 CREATE TABLE components (
-  id int UNIQUE NOT NULL,
-  name text UNIQUE NOT NULL PRIMARY KEY
+  id INTEGER UNIQUE NOT NULL PRIMARY KEY,
+  name TEXT UNIQUE NOT NULL,
+  UNIQUE (name) ON CONFLICT IGNORE
 );
 
 CREATE TABLE package_rel_map (
-  package_id int NOT NULL
+  package_id INTEGER NOT NULL
     CONSTRAINT fk_package_id REFERENCES packages(id),
-  suite_id int NOT NULL
+  suite_id INTEGER NOT NULL
     CONSTRAINT fk_suite_id REFERENCES suites(id) ON DELETE CASCADE,
-  component_id int NOT NULL
-    CONSTRAINT fk_component_id REFERENCES components(id)
+  component_id INTEGER NOT NULL
+    CONSTRAINT fk_component_id REFERENCES components(id),
+  UNIQUE (package_id, suite_id, component_id) ON CONFLICT IGNORE
 );
 
 -- sqlite doesn't actually implement FK constraints, therefore we
-- 
cgit v1.2.3

