[pkg-bioc] svn commit r338 r338 - /trunk/tools-ng/database_schema
psychedelys-guest at users.alioth.debian.org
psychedelys-guest at users.alioth.debian.org
Wed Oct 3 14:14:57 UTC 2007
Author: psychedelys-guest
Date: Wed Oct 3 14:14:57 2007
New Revision: 338
URL: http://svn.debian.org/wsvn/pkg-bioc/?sc=1&rev=338
Log:
worked on the SQL 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=338&op=diff
==============================================================================
--- trunk/tools-ng/database_schema (original)
+++ trunk/tools-ng/database_schema Wed Oct 3 14:14:57 2007
@@ -1,32 +1,41 @@
-# Howto support the any category ?
-architecture (
- id
- name
+# sqlite is not supporting foreign key.
+# sqlite is storing CURRENT_TIMESTAMP in text, no special date format.
+# sqlite is not ....
+# sqlite is not ....
+# sqlite is not ....
+# sqlite is not ....
+# I will not used sqlite....
+
+schema should work under mysql && postgresql, still in draft mode.
+TODO : rename some COLUMN to a more english native name....
+# Howto support the any category ?
+CREATE TABLE IF NOT EXISTS architectures (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
+ name TEXT UNIQUE,
);
-tracking (
- deamon text,
- version text,
- last_start date,
- last_stop date,
- toberun boolean,
+CREATE TABLE IF NOT EXISTS tracking (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
+ deamon TEXT UNIQUE,
+ version TEXT,
+ last_start TIMESTAMP,
+ last_stop TIMESTAMP,
+ toberun BOOLEAN,
);
#every packages name is getting an uniq id
-packages_name (
- id ,
- name text,
+CREATE TABLE IF NOT EXISTS packages_name (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
+ name TEXT,
);
# each repository is getting an uniq id,
-# ext to refer to Debian package
-repository (
- id ,
- name text,
- url text,
-
- 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,
);
@@ -37,14 +46,15 @@ repository (
# create a uniq id for each couple (packages_name.id, repository.id).
# I just index, (repository, version)
# last seen just tell me when last time I saw this revision on this repository.
-packages_presence (
- id
- id.name refto packages_name.id,
- id.repo refto repository.id,
- version text
- size text or numeriq ?
- lastseen date
- uniq (id.name, id.repo)
+# size is the trunk of the size, who care about decimals!
+CREATE TABLE IF NOT EXISTS packages_presence (
+ 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,
+ version TEXT,
+ size INTEGER,
+ lastseen TIMESTAMP,
+ UNIQUE (id.name, id.repo)
);
@@ -52,54 +62,57 @@ packages_presence (
# updated during r_pkg_ordering.
# parsing all the Descriptions file.
-packages (
- id refto packages_presence.id,
+# status: 'error'|'clean'|'unclean'
+# error : something happen before which block the package from being buildable.
+# clean : everything goes without problem, parsed dependencies,....
+# 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
- Author text,
- Package text,
- Date text,
- Description text,
- License text,
- Title text,
- Imports text,
- URL text,
- biocViews text,
- isBinary bool,
- status 'error'|'clean'|'unclean' : error : something happen before which block the package from being buildable.
- clean : everything goes without problem, parsed dependencies,....
- unclean: something happen during the parsing, check the log to see what
-
- Depends => table.dependencies,
- Suggests => table.Suggests,
-}
+ Author TEXT,
+ Package TEXT,
+ Date TEXT,
+ Description TEXT,
+ License TEXT,
+ Title TEXT,
+ Imports TEXT,
+ URL TEXT,
+ biocViews TEXT,
+ isBinary BOOLEAN,
+ status ENUM('error','clean','unclean'),
+)
#list of dependencies of a package
-dependencies (
- id refto packages_presence.id,
- depend.id
-
+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,
);
# list of suggestion for a package.
-Suggestions (
- id refto packages_presence.id,
- suggest.id
+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,
+ );
+# 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,
);
=== done in r_pkg_import_cran.pl ===
# information extracted from r_pkg_import_cran.pl
-externals_information (
- id refto packages_presence.id,
- status.32
- url.32
- status.64
- url.64
-
+CREATE TABLE IF NOT EXISTS externals_information (
+ id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
+ status_32 TEXT NOT NULL,
+ url_32 TEXT,
+ status_64 TEXT NOT NULL,
+ url_64 TEXT,
);
@@ -109,8 +122,6 @@ externals_information (
# debianized(name, version, date, status, scriptversion, current) unique(name,version)
# * date= date where the packages pass the last time.
# * scriptversion= version of the script which build it.
-# * status_pkg = 'donotpackage'|'failed'|'deb'
-# * status_qa = 'ok'|'warning'|'error'
# * debversion = current debian version of the package.
# * baseversion = based on the following source package version.
# * current = 'nil'|'last'|'depreciated'
@@ -119,15 +130,15 @@ externals_information (
# * motif = motif, if any.
# This table is needed to generate r_pkg_web
-debianized (
- id refto packages_presence.id,
- debversion text,
- baseversion text,
- date text,
- scriptversion text,
- current 'nil'|'last'|'depreciated'
- status_pkg 'donotpackage'|'failed'|'deb'
- status_qa 'ok'|'warning'|'error'
- motif text
+CREATE TABLE IF NOT EXISTS debianized (
+ id INTEGER PRIMARY KEY NOT NULL FOREIGN KEY packages_presence.id,
+ debversion TEXT,
+ baseversion TEXT,
+ date TIMESTAMP,
+ scriptversion TEXT,
+ current ENUM('nil','last','depreciated') NOT NULL,
+ status_pkg ENUM('donotpackage','failed','deb') NOT NULL,
+ status_qa ENUM('ok','warning','error') NOT NULL,
+ motif TEXT,
);
More information about the pkg-bioc-devel
mailing list