1 files changed, 18 insertions, 15 deletions
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