a days work II

git-svn-id: file:///home/lennart/svn/public/sse/trunk@17 5fbabb74-0606-0410-a5e4-b5cc6a42724e

1 files changed, 17 insertions, 3 deletions

diff --git a/sse.sql b/sse.sql
index 2e4e9cc..b7b1622 100644
--- a/sse.sql
+++ b/sse.sql
@@ -1,6 +1,9 @@
+-- $Id$
+
 DROP TABLE word;
 DROP TABLE file;
 DROP TABLE package;
+DROP TABLE provider_record;
 
 CREATE TABLE word (
     text VARCHAR(40) NOT NULL,
@@ -15,16 +18,27 @@ CREATE TABLE file (
     package_id INTEGER UNSIGNED NOT NULL,
     path VARBINARY(255) NOT NULL,
     language_id TINYINT UNSIGNED NOT NULL,
-    PRIMARY KEY (id)
+    PRIMARY KEY (id),
+    UNIQUE KEY (package_id, path)
 ) ENGINE=InnoDB;
 
 CREATE TABLE package (
     id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
     path VARBINARY(255) NOT NULL,
-    name VARBINARY(255) NOT NULL DEFAULT 'noname',
-    url VARBINARY(255) NOT NULL DEFAULT '',
     timestamp TIMESTAMP NOT NULL,
     md CHAR(32) NOT NULL DEFAULT '',
     PRIMARY KEY(id),
     UNIQUE KEY (md)
 ) ENGINE=InnoDB;
+
+CREATE TABLE provider_record (
+    id VARBINARY(64) NOT NULL,    
+    package_id INTEGER UNSIGNED NOT NULL,
+    provider_id TINYINT UNSIGNED NOT NULL,
+    name VARBINARY(255) NOT NULL DEFAULT 'noname',
+    url VARBINARY(255) NOT NULL DEFAULT '',
+    download_url VARBINARY(255) NOT NULL DEFAULT '',
+    license VARCHAR(64) NOT NULL DEFAULT '',
+    PRIMARY KEY(id),
+    UNIQUE KEY (package_id, provider_id)
+) ENGINE=InnoDB;