Show
Ignore:
Timestamp:
08/04/09 04:29:58 (4 years ago)
Author:
ignotus
Message:

Added primary and foreign key constraints.

Files:
1 modified

Legend:

Unmodified
Added
Removed

  • branch/zion/umit/zion/db/model.sql

    r5128 r5167  
    1 CREATE TABLE software( 
    2 -- Attributes -- 
    3 pk integer, 
    4 fk_vendor integer, 
    5 fk_type integer, 
    6 fk_name integer, 
    7 fk_note integer, 
    8 description text, 
    9 added datetime, 
    10 updated datetime); 
     1-- Copyright (C) 2009 Adriano Monteiro Marques. 
     2-- 
     3-- Authors: Joao Paulo de Souza Medeiros <ignotus21@gmail.com>, 
     4-- 
     5-- This program is free software; you can redistribute it and/or modify 
     6-- it under the terms of the GNU General Public License as published by 
     7-- the Free Software Foundation; either version 2 of the License, or 
     8-- (at your option) any later version. 
     9-- 
     10-- This program is distributed in the hope that it will be useful, 
     11-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
     12-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
     13-- GNU General Public License for more details. 
     14-- 
     15-- You should have received a copy of the GNU General Public License 
     16-- along with this program; if not, write to the Free Software 
     17-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 
    1118 
    12 CREATE TABLE fingerprint( 
    13 -- Attributes -- 
    14 pk integer, 
    15 fk_sig1 integer, 
    16 fk_raw1 integer, 
    17 added datetime, 
    18 updated datetime, 
    19 fk_software integer); 
     19-------------------------------------------------------------------------------- 
     20-- DATABASE SCHEMA                                                            -- 
     21-------------------------------------------------------------------------------- 
     22-------------------------------------------------------------------------------- 
     23-- TABLES                                                                     -- 
     24-------------------------------------------------------------------------------- 
    2025 
    21 CREATE TABLE s_attractor( 
    22 -- Attributes -- 
    23 pk interger, 
    24 samples integer, 
    25 fp blob, 
    26 description text); 
     26-- DROP TABLE software CASCADE; 
    2727 
    28 CREATE TABLE r_tcpisn( 
    29 -- Attributes -- 
    30 pk integer, 
    31 samples integer, 
    32 series blob, 
    33 description text); 
     28CREATE TABLE software 
     29( 
     30    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     31    fk_vendor INTEGER NOT NULL REFERENCES vendor (pk), 
     32    fk_stype INTEGER NOT NULL REFERENCES stype (pk), 
     33    fk_name INTEGER NOT NULL REFERENCES name (pk), 
     34    fk_note INTEGER REFERENCES note (pk), 
     35    description TEXT, 
     36    added DATETIME NOT NULL, 
     37    updated DATETIME NOT NULL 
     38); 
    3439 
    35 CREATE TABLE vendor( 
    36 -- Attributes -- 
    37 pk integer, 
    38 name text, 
    39 description text); 
     40-- ALTER TABLE software ADD CONSTRAINT software_pk PRIMARY KEY (pk); 
    4041 
    41 CREATE TABLE type( 
    42 -- Attributes -- 
    43 pk integer, 
    44 name text, 
    45 description text); 
     42-------------------------------------------------------------------------------- 
    4643 
    47 CREATE TABLE name( 
    48 -- Attributes -- 
    49 pk integer, 
    50 name text, 
    51 version text, 
    52 description text); 
     44-- DROP TABLE fingerprint CASCADE; 
    5345 
    54 CREATE TABLE note( 
    55 -- Attributes -- 
    56 pk integer, 
    57 note text, 
    58 keywords text); 
     46CREATE TABLE fingerprint 
     47( 
     48    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     49    fk_sig1 INTEGER NOT NULL REFERENCES s_attractor (pk), 
     50    fk_raw1 INTEGER REFERENCES r_tcpisn (pk), 
     51    added DATETIME NOT NULL, 
     52    updated DATETIME NOT NULL, 
     53    fk_software INTEGER NOT NULL 
     54); 
     55 
     56-- ALTER TABLE fingerprint ADD CONSTRAINT fingerprint_pk PRIMARY KEY (pk); 
     57 
     58-------------------------------------------------------------------------------- 
     59 
     60-- DROP TABLE s_attractor CASCADE; 
     61 
     62CREATE TABLE s_attractor 
     63( 
     64    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     65    samples INTEGER NOT NULL, 
     66    fp BLOB NOT NULL, 
     67    description TEXT 
     68); 
     69 
     70-- ALTER TABLE s_attractor ADD CONSTRAINT s_attractor_pk PRIMARY KEY (pk); 
     71 
     72-------------------------------------------------------------------------------- 
     73 
     74-- DROP TABLE r_tcpisn CASCADE; 
     75 
     76CREATE TABLE r_tcpisn 
     77( 
     78    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     79    samples INTEGER NOT NULL, 
     80    series BLOB NOT NULL, 
     81    description TEXT 
     82); 
     83 
     84-- ALTER TABLE r_tcpisn ADD CONSTRAINT r_tcpisn_pk PRIMARY KEY (pk); 
     85 
     86-------------------------------------------------------------------------------- 
     87 
     88-- DROP TABLE vendor CASCADE; 
     89 
     90CREATE TABLE vendor 
     91( 
     92    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     93    name TEXT NOT NULL, 
     94    description TEXT 
     95); 
     96 
     97-- ALTER TABLE vendor ADD CONSTRAINT vendor_pk PRIMARY KEY (pk); 
     98 
     99-------------------------------------------------------------------------------- 
     100 
     101-- DROP TABLE stype CASCADE; 
     102 
     103CREATE TABLE stype 
     104( 
     105    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     106    name TEXT NOT NULL, 
     107    description TEXT 
     108); 
     109 
     110-- ALTER TABLE stype ADD CONSTRAINT stype_pk PRIMARY KEY (pk); 
     111 
     112-------------------------------------------------------------------------------- 
     113 
     114-- DROP TABLE name CASCADE; 
     115 
     116CREATE TABLE name 
     117( 
     118    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     119    name TEXT NOT NULL, 
     120    version TEXT NOT NULL, 
     121    description TEXT 
     122); 
     123 
     124-- ALTER TABLE name ADD CONSTRAINT name_pk PRIMARY KEY (pk); 
     125 
     126-------------------------------------------------------------------------------- 
     127 
     128-- DROP TABLE note CASCADE; 
     129 
     130CREATE TABLE note 
     131( 
     132    pk INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     133    note TEXT NOT NULL, 
     134    keywords TEXT 
     135); 
     136 
     137-- ALTER TABLE note ADD CONSTRAINT note_pk PRIMARY KEY (pk); 
    59138 
    60139 
     140-------------------------------------------------------------------------------- 
     141-- FOREIGN KEYS                                                               -- 
     142-------------------------------------------------------------------------------- 
    61143 
    62 -- ALTER TABLE note ADD 
    63 --     CONSTRAINT  FK_note_software  FOREIGN KEY(unnamed) REFERENCES software (unnamed); 
     144-- ALTER TABLE software ADD CONSTRAINT 
     145--     software_vendor_fk FOREIGN KEY(fk_vendor) REFERENCES vendor (pk); 
    64146 
     147-- ALTER TABLE software ADD CONSTRAINT 
     148--     software_stype_fk FOREIGN KEY(fk_stype) REFERENCES stype (pk); 
    65149 
    66 -- ALTER TABLE type ADD 
    67 --     CONSTRAINT  FK_type_software  FOREIGN KEY(unnamed) REFERENCES software (unnamed); 
     150-- ALTER TABLE software ADD CONSTRAINT 
     151--     software_name_fk FOREIGN KEY(fk_name) REFERENCES name (pk); 
    68152 
     153-- ALTER TABLE software ADD CONSTRAINT 
     154--     software_note_fk FOREIGN KEY(fk_note) REFERENCES note (pk); 
    69155 
    70 -- ALTER TABLE s_attractor ADD 
    71 --     CONSTRAINT  FK_s_attractor_fingerprint  FOREIGN KEY(unnamed) REFERENCES fingerprint (unnamed); 
     156-- ALTER TABLE fingerprint ADD CONSTRAINT 
     157--     fingerprint_sig1_fk FOREIGN KEY(fk_sig1) REFERENCES s_attractor (pk); 
    72158 
    73  
    74 -- ALTER TABLE r_tcpisn ADD 
    75 --     CONSTRAINT  FK_r_tcpisn_fingerprint  FOREIGN KEY(unnamed) REFERENCES fingerprint (unnamed); 
    76  
    77  
    78 -- ALTER TABLE software ADD 
    79 --     CONSTRAINT  FK_software_fingerprint  FOREIGN KEY(unnamed) REFERENCES fingerprint (unnamed); 
    80  
    81  
    82 -- ALTER TABLE software ADD 
    83 --     CONSTRAINT  FK_software_vendor  FOREIGN KEY(unnamed) REFERENCES vendor (unnamed); 
    84  
    85  
    86 -- ALTER TABLE software ADD 
    87 --     CONSTRAINT  FK_software_name  FOREIGN KEY(unnamed) REFERENCES name (unnamed); 
     159-- ALTER TABLE fingerprint ADD CONSTRAINT 
     160--     fingerprint_raw1_fk FOREIGN KEY(fk_raw1) REFERENCES r_tcpisn (pk);