key fixup, pkg index, remove blank rows from sql

1 files changed, 7 insertions, 9 deletions

diff --git a/db.sql b/db.sql
index 26c9667..456a86a 100644
--- a/db.sql
+++ b/db.sql
@@ -1,5 +1,8 @@
+-- db schema for patch tracker.  note that every distinct command (omitting
+-- comments) must be preceded and followed by a blank line.
+
 CREATE TABLE packages (
-  id int UNIQUE NOT NULL,
+  id int UNIQUE NOT NULL PRIMARY KEY,
   name text NOT NULL,
   version text NOT NULL,
   format text NOT NULL,
@@ -9,6 +12,8 @@ CREATE TABLE packages (
   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
@@ -16,7 +21,7 @@ CREATE TABLE suites (
 
 CREATE TABLE components (
   id int UNIQUE NOT NULL,
-  name text PRIMARY KEY
+  name text UNIQUE NOT NULL PRIMARY KEY
 );
 
 CREATE TABLE package_rel_map (
@@ -119,10 +124,3 @@ CREATE TRIGGER fku_package_rel_map_prune_packages_package_id
                      WHERE package_id = OLD.package_id AND rowid != OLD.rowid) 
       IS NULL;
   END;
-
-INSERT INTO packages VALUES (0,'','','','',NULL,NULL,NULL);
-
-INSERT INTO suites VALUES (0,'');
-
-INSERT INTO components VALUES (0,'');
-