[pkg-bioc] svn commit r339 r339 - /trunk/tools-ng/database_schema

[psychedelys-guest at users.alioth.debian.org]

Author: psychedelys-guest
Date: Sat Oct  6 03:49:05 2007
New Revision: 339

URL: http://svn.debian.org/wsvn/pkg-bioc/?sc=1&rev=339
Log:
updated databases schema

Modified:
    trunk/tools-ng/database_schema

Modified: trunk/tools-ng/database_schema
URL: http://svn.debian.org/wsvn/pkg-bioc/trunk/tools-ng/database_schema?rev=339&op=diff
==============================================================================
--- trunk/tools-ng/database_schema (original)
+++ trunk/tools-ng/database_schema Sat Oct  6 03:49:05 2007
@@ -12,33 +12,36 @@ TODO : rename some COLUMN to a more engl
 # Howto support the any category ?
 CREATE TABLE IF NOT EXISTS architectures (
   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-  name TEXT UNIQUE,
+    name VARCHAR(10) UNIQUE
   );
 
 CREATE TABLE IF NOT EXISTS tracking (
   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-  deamon TEXT UNIQUE,
+    deamon VARCHAR(50) UNIQUE,
   version TEXT,
   last_start TIMESTAMP,
   last_stop TIMESTAMP,
-  toberun BOOLEAN,
+    toberun BOOLEAN
   );
 
 #every packages name is getting an uniq id
-CREATE TABLE IF NOT EXISTS packages_name (
+CREATE TABLE IF NOT EXISTS packagesname (
   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-  name TEXT,
+    name  VARCHAR(70) UNIQUE
   );
 
 # each repository is getting an uniq id,
 # ext will refer to Debian package
 CREATE TABLE IF NOT EXISTS repository (
   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-  name TEXT UNIQUE,
-  url TEXT,
+    name VARCHAR(20) UNIQUE,
+    url TEXT
   );
 
-
+CREATE TABLE IF NOT EXISTS anomalies (
+     id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
+     name VARCHAR(100) UNIQUE
+     );
 
 === done in r_pkg_update ===
 
@@ -47,17 +50,32 @@ CREATE TABLE IF NOT EXISTS repository (
 # I just index, (repository, version)
 # last seen just tell me when last time I saw this revision on this repository.
 # size is the trunk of the size, who care about decimals! 
-CREATE TABLE IF NOT EXISTS packages_presence ( 
+CREATE TABLE IF NOT EXISTS packagesupdate ( 
   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
-  id.name NOT NULL FOREIGN KEY packages_name.id,
-  id.repo NOT NULL FOREIGN KEY repository.id,
+    packagesname_id INTEGER NOT NULL,
+    repository_id INTEGER NOT NULL,
   version TEXT,
   size INTEGER,
   lastseen TIMESTAMP,
-  UNIQUE (id.name, id.repo)
+    UNIQUE (packagesname_id, repository_id),
+    FOREIGN KEY (packagesname_id) REFERENCES packagesname(id),
+    FOREIGN KEY (repository_id) REFERENCES repository(id)
   );
 
 
+
+CREATE TABLE IF NOT EXISTS cannotbuild (
+    id  INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
+    packagesupdate_id INTEGER NOT NULL,
+    anomalies_id INTEGER NOT NULL,
+    lastcheck TIMESTAMP,
+    UNIQUE (packagesupdate_id, anomalies_id),
+    FOREIGN KEY (packagesupdate_id) REFERENCES packagesupdate(id),
+    FOREIGN KEY (anomalies_id) REFERENCES anomalies(id)
+    );
+
+
+
 === done in r_pkg_ordering ===
 
 # updated during r_pkg_ordering.
@@ -68,8 +86,8 @@ CREATE TABLE IF NOT EXISTS packages_pres
 #            unclean: something happen during the parsing, check the log to see what
 #
 CREATE TABLE IF NOT EXISTS packages (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
   #[...] all debian/control_fields
+    id INTEGER PRIMARY KEY NOT NULL,
   Author TEXT,
   Package TEXT,
   Date TEXT,
@@ -81,26 +99,33 @@ CREATE TABLE IF NOT EXISTS packages (
   biocViews TEXT,
   isBinary BOOLEAN,
   status ENUM('error','clean','unclean'), 
+    FOREIGN KEY (id) REFERENCES packagesupdate(id)
 )
 
 
 #list of dependencies of a package
 CREATE TABLE IF NOT EXISTS dependencies (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
-  depend.id NOT NULL FOREIGN KEY packages_presence.id,
+    id INTEGER PRIMARY KEY NOT NULL,
+    depend_id INTEGER NOT NULL,
+    FOREIGN KEY (id) REFERENCES packagesupdate(id),
+    FOREIGN KEY (depend_id) REFERENCES packagesupdate(id)
   );
 
 
 # list of suggestion for a package.
 CREATE TABLE IF NOT EXISTS Suggested (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
-  suggest.id NOT NULL FOREIGN KEY packages_presence.id,
+    id INTEGER PRIMARY KEY NOT NULL,
+    suggest_id INTEGER NOT NULL,
+    FOREIGN KEY (id) REFERENCES packagesupdate(id),
+    FOREIGN KEY (suggest_id) REFERENCES packagesupdate(id)
   );
 
 # list of recommanded for a package.
 CREATE TABLE IF NOT EXISTS Recommended (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
-  recommend.id NOT NULL FOREIGN KEY packages_presence.id,
+    id INTEGER PRIMARY KEY NOT NULL,
+    recommend_id INTEGER NOT NULL,
+    FOREIGN KEY (id) REFERENCES packagesupdate(id),
+    FOREIGN KEY (recommend_id) REFERENCES packagesupdate(id)
   );
 
 
@@ -108,11 +133,12 @@ CREATE TABLE IF NOT EXISTS Recommended (
 
 # information extracted from r_pkg_import_cran.pl
 CREATE TABLE IF NOT EXISTS externals_information (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
+    id INTEGER PRIMARY KEY NOT NULL,
   status_32 TEXT NOT NULL,
   url_32 TEXT,
   status_64 TEXT NOT NULL,
   url_64 TEXT,
+    FOREIGN KEY (id) REFERENCES packagesupdate(id)
 );
 
 
@@ -131,7 +157,7 @@ CREATE TABLE IF NOT EXISTS externals_inf
 
 # This table is needed to generate r_pkg_web
 CREATE TABLE IF NOT EXISTS debianized (
-  id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
+    id INTEGER PRIMARY KEY NOT NULL,
   debversion TEXT, 
   baseversion TEXT, 
   date TIMESTAMP, 
@@ -140,5 +166,6 @@ CREATE TABLE IF NOT EXISTS debianized (
   status_pkg ENUM('donotpackage','failed','deb') NOT NULL,
   status_qa  ENUM('ok','warning','error') NOT NULL,
   motif TEXT,
+    FOREIGN KEY (id) REFERENCES packagesupdate(id)
   );