Changeset 67


Ignore:
Timestamp:
Jun 2, 2009, 10:36:22 AM (11 years ago)
Author:
marchulsman
Message:

Modifications to schema (taxon -> term, rename fields)

Location:
sql
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • sql/clear.sql

    r66 r67  
    1 DROP DATABASE ibidas_test;
    2 CREATE DATABASE ibidas_test;
    3 \c ibidas_test
     1DROP DATABASE ibidas_test_nieuw;
     2CREATE DATABASE ibidas_test_nieuw;
     3\c ibidas_test_nieuw
    44\i preimport.sql
  • sql/preimport.sql

    r66 r67  
    1 CREATE SEQUENCE dataset_pk_seq CACHE 25;
    2 CREATE TABLE dataset (
    3    dataset_id INTEGER DEFAULT nextval('dataset_pk_seq') NOT NULL,
     1-- IBIDAS database structure
     2-- To store all biological data one needs a flexible structure.
     3-- In this database the main element is the object.
     4--
     5-- |||OBJECT STRUCTURE||||
     6-- To structurize the database we split the objects in 3 groups.
     7-- set: contains data sources, data selections (e.g. REFSEQ, research article, selection of genes I am intested in)
     8-- term: contains ontology terms, describing the type of objects (e.g. GO ontology terms, type of propertys, links, objects)
     9-- thing: the real objects this database is describing (e.g. genes, proteins, chromosomes)
     10--
     11-- |||OBJECT DESCRIPTION||||
     12-- To describe each object, we have properties and links.
     13-- * Properties describe an objects (possibly by reffering to records from the other object categories).
     14-- * Links describe connections between objects from the same category (e.g. things to things, terms to terms)
     15-- Each object,property and link has a type (refers to term) and a source (refers to set) to describe what
     16-- kind of object/property/link this is and from which source we obtained the information. One exception is term,
     17-- which does not have a type field, as we did not find a need for it yet.
     18-- Propertys and links themself are a special kind of object and can also have properties themself. These are almost only used
     19-- to describe the evidence for a certain link/property. As we do not have found a need for it yet, links between links and properties can
     20-- not be stored in this database currently.
     21--
     22-- |||STORAGE|||
     23-- To store objects we use for each group a main table with an object_id and common properties such as name and type.
     24-- An inheritance hierarchy can be used to store 1:1 properties which are only used by a subset of the objects.
     25--
     26-- To store properties (which have multiple data types and references to other tables) we use an inheritance hierarchy. Each subtable
     27-- specializes in a certain (set of) data types.
     28--
     29-- Links describe a graph structure. A graph can have both undirected and directed edges. We store these respectively in relation_link tables
     30-- and child_link tables. Then, for optimization purposes we have the following modifications:
     31-- * relations are stored twice in both orientations, although with the same link_id (primary key).
     32-- * we have a family table storing relations as A + B = C, containing both an undirected and a directed component. This could also be stored
     33-- using two record in the child_link table. HOwever, in biology we are quite often interested in the interactions between A and B. Storing them
     34-- in this format saves an extra join (also here we store the data in both orientations).
     35-- CLARIFICATION: when we store for example protein interactions, with some expermiental techniques we not only have pairs of proteins but frequently also complex
     36-- measurements, containing multiple proteins. While pairs of proteins would be easily stored in a undirected graph format, this is not possible for the-
     37-- ocmplex interactinos which are essentially hyperedges. To store this we need an extra dimension, i.e. use the hierarchical (directed) graph strucutre.
     38-- However, there is no biological reason to store complexes in a hierarchical way and duo interactions not. In essence, also duo-interactions form an
     39-- product, being it either a modified form of one of the proteins or a complex of both proteins. To store this in a consequent manner we would also need
     40-- to store these interactions hierarchically, but this would increate the number of joins. In the family_link table we can store both links simultaneously.
     41-- * also here we might choose to store certain 1:1 properties using an inhertance hierarchy.
     42--
     43
     44---------------------------------------
     45--------------- OBJECTS ----------------
     46----------------------------------------
     47
     48CREATE SEQUENCE set_pk_seq CACHE 25;
     49CREATE TABLE set (
     50   set_id INTEGER DEFAULT nextval('set_pk_seq') NOT NULL,
    451   type_id INTEGER,
     52   source_id INTEGER,
     53   version INTEGER NOT NULL DEFAULT 0,
    554   name TEXT NOT NULL,
    6    version INTEGER NOT NULL DEFAULT 0,
    755   description TEXT,
    8    PRIMARY KEY(dataset_id),
     56   PRIMARY KEY(set_id),
    957   UNIQUE (name,version)
    1058);
    1159
    12 CREATE SEQUENCE dataset_relation_pk_seq CACHE 25;
    13 CREATE TABLE dataset_relation (
    14    dataset_relation_id INTEGER DEFAULT nextval('dataset_relation_pk_seq') NOT NULL,
    15    target_dataset_id INTEGER,
    16    source_dataset_id INTEGER,
    17    type_id INTEGER,
    18    rank INTEGER,
    19    PRIMARY KEY (dataset_relation_id)
    20 );
    21 
    22 ALTER TABLE dataset_relation ADD CONSTRAINT fkterm_dataset_relation_source_id FOREIGN KEY (dataset_source_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    23 ALTER TABLE dataset_relation ADD CONSTRAINT fkterm_dataset_relation_target_id FOREIGN KEY (dataset_target_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    24 
    25 ---------------------------------------------------------------
    26 -- ONTOLOGIES
    27 ---------------------------------------------------------------
    28 
    29 -- any controlled vocab term, everything from full ontology
    30 -- terms eg GO IDs to the various keys allowed as qualifiers
     60CREATE TABLE set_tree (
     61    parent_id INTEGER,
     62    begin_pos INTEGER,
     63    end_pos INTEGER,
     64    PRIMARY KEY(set_id),
     65    UNIQUE (name,version)
     66) INHERITS (set);
     67
     68
    3169CREATE SEQUENCE term_pk_seq;
    3270CREATE TABLE term (
    3371         term_id INTEGER DEFAULT nextval ( 'term_pk_seq' ) NOT NULL ,
    34          name VARCHAR ( 255 ) NOT NULL ,
     72         source_id INTEGER NOT NULL ,
     73         name TEXT NOT NULL ,
     74         identifier TEXT,
    3575         description TEXT ,
    36          identifier VARCHAR ( 40 ) ,
    37          dataset_id INTEGER NOT NULL ,
    3876         PRIMARY KEY ( term_id ) ,
    39     UNIQUE ( identifier,dataset_id ) ) ;
    40 
     77     UNIQUE (identifier,source_id )
     78);
     79
     80CREATE TABLE term_tree (
     81    parent_id INTEGER,
     82    begin_pos INTEGER,
     83    end_pos INTEGER,
     84    PRIMARY KEY ( term_id ) ,
     85    UNIQUE ( identifier,source_id )
     86) INHERITS (term);
     87
     88CREATE TABLE term_tree_taxon (
     89    ncbi_taxon_id INTEGER,
     90    taxon_rank_id INTEGER,
     91    division_id INTEGER,
     92    genetic_code_id SMALLINT,
     93    mito_genetic_code_id SMALLINT,
     94    PRIMARY KEY (term_id),
     95    UNIQUE(ncbi_taxon_id)
     96) INHERITS (term_tree);
     97
     98
     99CREATE SEQUENCE thing_pk_seq;
     100CREATE TABLE thing (
     101   thing_id INTEGER DEFAULT nextval ( 'thing_pk_seq' ) NOT NULL ,
     102   source_id INTEGER NOT NULL,
     103   type_id INTEGER NOT NULL,
     104   name TEXT,
     105   PRIMARY KEY (thing_id)   
     106);
     107
     108
     109CREATE TABLE thing_tree (
     110    parent_id INTEGER,
     111    begin_pos INTEGER,
     112    end_pos INTEGER,
     113    PRIMARY KEY (thing_id)
     114) INHERITS (thing);
     115
     116
     117
     118CREATE TABLE thing_tree_location (
     119    strand BOOLEAN NOT NULL,
     120    farleft_pos INTEGER NOT NULL,
     121    farright_pos INTEGER DEFAULT -1,
     122    loctype INTEGER,
     123    PRIMARY KEY (thing_id)
     124) INHERITS (thing_tree);
     125
     126
     127
     128CREATE INDEX set_name ON set(name);
     129CREATE INDEX set_tree_name ON set_tree(name);
    41130CREATE INDEX term_name ON term(name);
    42 CREATE INDEX term_dataset ON term(dataset_id);
    43 
    44 ALTER TABLE term ADD CONSTRAINT fkterm_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    45 -- relationship between controlled vocabulary / ontology term
    46 -- we use subject/predicate/object but this could also
    47 -- be thought of as child/relationship-type/parent.
    48 -- the subject/predicate/object naming is better as we
    49 -- can think of the graph as composed of statements.
    50 
    51 CREATE SEQUENCE term_relationship_pk_seq;
    52 CREATE TABLE term_relationship (
    53          term_relationship_id INTEGER DEFAULT nextval ( 'term_relationship_pk_seq' ) NOT NULL ,
    54          source_term_id INTEGER NOT NULL ,
    55          predicate_term_id INTEGER NOT NULL ,
    56          target_term_id INTEGER NOT NULL ,
    57     distance INTEGER DEFAULT 1,
    58     PRIMARY KEY (term_relationship_id),
    59     UNIQUE(source_term_id,target_term_id,predicate_term_id)  -- distance should be only single value
    60     );
    61 
    62 CREATE INDEX term_relationship_source_term_id ON term_relationship(source_term_id,distance);
    63 CREATE INDEX term_relationship_target_term_id ON term_relationship(target_term_id,distance);
    64 
    65 ALTER TABLE term_relationship ADD CONSTRAINT fkterm_relationship_source_term_id FOREIGN KEY (source_term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    66 ALTER TABLE term_relationship ADD CONSTRAINT fkterm_relationship_target_term_id FOREIGN KEY (target_term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    67 ALTER TABLE term_relationship ADD CONSTRAINT fkterm_relationship_predicate_term_id FOREIGN KEY (predicate_term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    68  
    69 CREATE SEQUENCE accession_pk_seq;
    70 CREATE TABLE accession (
    71     accession_id INTEGER DEFAULT nextval ( 'accession_pk_seq' ) NOT NULL ,
    72     accession VARCHAR(64),
    73     version INTEGER NOT NULL DEFAULT 0,
     131CREATE INDEX term_tree_name ON term_tree(name);
     132CREATE INDEX term_tree_taxon_name ON term_tree_taxon(name);
     133CREATE INDEX thing_name ON thing(name);
     134CREATE INDEX thing_tree_name ON thing_tree(name);
     135CREATE INDEX thing_tree_location_name ON thing_tree_location(name);
     136
     137CREATE INDEX set_type ON set(type_id,source_id);
     138CREATE INDEX set_tree_type ON set_tree(type_id,source_id);
     139CREATE INDEX term_type ON term(source_id);
     140CREATE INDEX term_tree_type ON term_tree(source_id);
     141CREATE INDEX term_tree_taxon_type ON term_tree_taxon(type_id,source_id);
     142CREATE INDEX thing_type ON thing(type_id,source_id);
     143CREATE INDEX thing_tree_type ON thing_tree(type_id,source_id);
     144CREATE INDEX thing_tree_location_type ON thing_tree_location(type_id,source_id);
     145
     146-------------------------------------
     147----------- LINKS -------------------
     148-------------------------------------
     149
     150CREATE SEQUENCE set_link_pk_seq CACHE 25;
     151CREATE TABLE set_link (
     152    set_link_id INTEGER DEFAULT nextval('set_link_pk_seq') NOT NULL,
    74153    type_id INTEGER,
    75     PRIMARY KEY (accession_id),
    76     UNIQUE (accession,version,type_id));
    77 
    78 ALTER TABLE accession ADD CONSTRAINT fkaccession_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    79 
    80 CREATE SEQUENCE reference_pk_seq;
    81 CREATE TABLE reference (
    82     reference_id INTEGER DEFAULT nextval( 'reference_pk_seq' ) NOT NULL ,
    83     title TEXT,
    84     authors TEXT,
    85     location TEXT,
    86     PRIMARY KEY (reference_id),
    87     UNIQUE(title,authors,location));
    88 
    89 CREATE SEQUENCE reference_accession_pk_seq;
    90 CREATE TABLE reference_accession (
    91    reference_accession_id INTEGER DEFAULT nextval( 'reference_accession_pk_seq') NOT NULL,
    92    reference_id INTEGER NOT NULL,
    93    accession_id INTEGER NOT NULL,
    94    PRIMARY KEY (reference_accession_id));
    95 
    96 
    97 ALTER TABLE reference_accession ADD CONSTRAINT fkreference_accession_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) DEFERRABLE INITIALLY DEFERRED;
    98 ALTER TABLE reference_accession ADD CONSTRAINT fkreference_accession_accession_id FOREIGN KEY (accession_id) REFERENCES accession(accession_id) DEFERRABLE INITIALLY DEFERRED;
    99 
    100 
    101 CREATE SEQUENCE gencode_pk_seq CACHE 25;
    102 CREATE TABLE genetic_code (
    103     gencode_id SMALLINT DEFAULT nextval ( 'gencode_pk_seq' ) NOT NULL,
    104     identifier SMALLINT NOT NULL,
    105     name VARCHAR(255) NOT NULL ,
    106     code VARCHAR(255) NOT NULL ,
    107     cstart VARCHAR(255) NOT NULL,
    108     PRIMARY KEY (gencode_id),
    109     UNIQUE (name),
    110     UNIQUE (identifier));
    111 
    112 ----------------------------------------------------------------
    113 -- TAXONS
    114 ---------------------------------------------------------------
    115 
    116 -- we could insist that taxa are NCBI taxon id, but on reflection I made this
    117 -- an optional extra line, as many flat file formats do not have the NCBI id
    118 --
    119 -- no organelle/sub species
    120 --
    121 -- this corresponds to the node table of the NCBI taxonomy database
    122 
    123 CREATE SEQUENCE taxon_pk_seq CACHE 25;
    124 CREATE TABLE taxon (
    125          taxon_id INTEGER DEFAULT nextval ( 'taxon_pk_seq' ) NOT NULL,
    126          ncbi_taxon_id INTEGER ,
    127          parent_taxon_id INTEGER ,
    128          taxon_rank_id INTEGER ,
    129          genetic_code_id SMALLINT ,
    130          mito_genetic_code_id SMALLINT ,
    131     division_id INTEGER,
    132     PRIMARY KEY (taxon_id),
    133     UNIQUE(ncbi_taxon_id)
    134     );
    135 
    136 CREATE INDEX taxon_parent_taxon_id ON taxon(parent_taxon_id);
    137 ALTER TABLE taxon ADD CONSTRAINT fktaxon_taxon_rank FOREIGN KEY (taxon_rank_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    138 ALTER TABLE taxon ADD CONSTRAINT fktaxon_division FOREIGN KEY (division_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    139 ALTER TABLE taxon ADD CONSTRAINT fktaxon_genterm FOREIGN KEY (genetic_code_id) REFERENCES genetic_code(gencode_id) DEFERRABLE INITIALLY DEFERRED;
    140 ALTER TABLE taxon ADD CONSTRAINT fktaxon_mitogenterm FOREIGN KEY (mito_genetic_code_id) REFERENCES genetic_code(gencode_id) DEFERRABLE INITIALLY DEFERRED;
    141  
    142 
    143 -- corresponds to the names table of the NCBI taxonomy databaase
    144 CREATE SEQUENCE taxon_name_pk_seq CACHE 25;
    145 CREATE TABLE taxon_name (
    146     taxon_name_id INTEGER DEFAULT nextval('taxon_name_pk_seq') NOT NULL,
    147          taxon_id INTEGER NOT NULL ,
    148          name VARCHAR ( 255 ) NOT NULL ,
    149          name_class_id INTEGER NOT NULL,
    150     PRIMARY KEY(taxon_name_id),
    151     UNIQUE(name,name_class_id,taxon_id)
    152     );
    153 
    154 CREATE INDEX taxon_name_taxon_id ON taxon_name(taxon_id);
    155 CREATE INDEX taxon_name_name ON taxon_name(name);
    156 
    157 ALTER TABLE taxon_name ADD CONSTRAINT fktaxon_name_taxonid FOREIGN KEY (taxon_id) REFERENCES taxon(taxon_id) DEFERRABLE INITIALLY DEFERRED;
    158 ALTER TABLE taxon_name ADD CONSTRAINT fktaxon_name_name_class FOREIGN KEY (name_class_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    159 
    160 
    161 
    162 CREATE SEQUENCE location_set_id_seq;
    163 CREATE SEQUENCE location_pk_seq;
    164 CREATE TABLE location (
    165    location_id INTEGER DEFAULT nextval('location_pk_seq') NOT NULL,
    166    location_set_id INTEGER DEFAULT nextval('location_set_id_seq') NOT NULL,
    167    strand BOOLEAN,
    168    startpos INTEGER NOT NULL,
    169    endpos INTEGER DEFAULT -1,
    170    type_id INTEGER,
    171    rank INTEGER,
    172    PRIMARY KEY(location_id));
    173 
    174 CREATE INDEX location_location_set_id ON location(location_set_id,startpos,endpos);
    175 
    176 
    177 -----------------------------------------------------------------
    178 -- BIOENTRY
    179 ----------------------------------------------------------------
    180 
    181 CREATE SEQUENCE bioentry_pk_seq;
    182 CREATE TABLE bioentry (
    183    bioentry_id INTEGER DEFAULT nextval ( 'bioentry_pk_seq' ) NOT NULL ,
    184    dataset_id INTEGER NOT NULL,
    185    name TEXT,
    186    type_id INTEGER NOT NULL,
    187    PRIMARY KEY (bioentry_id)
    188    );
    189 --   UNIQUE (name,type_id,dataset_id),  (multiple exons: same name, same type, same db))
    190 
    191 CREATE INDEX bioentry_type_id ON bioentry(type_id,dataset_id);
    192 CREATE INDEX bioentry_name_id ON bioentry(name);
    193 ALTER TABLE bioentry ADD CONSTRAINT fkbioentry_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    194 ALTER TABLE bioentry ADD CONSTRAINT fkbioentry_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    195  
    196 CREATE TABLE biosequence (
    197    bioentry_id INTEGER NOT NULL,
    198    length INTEGER NOT NULL,
    199    strand BOOLEAN NOT NULL,
    200    linear BOOLEAN NOT NULL,
    201    moleculetype VARCHAR(32),
    202    sequence TEXT NOT NULL,
    203    PRIMARY KEY(bioentry_id));
    204 
    205 CREATE SEQUENCE taxon_bioentry_pk_seq CACHE 25;
    206 CREATE TABLE taxon_bioentry (
    207     taxon_bioentry_id INTEGER DEFAULT nextval('taxon_bioentry_pk_seq') NOT NULL,
    208          taxon_id INTEGER NOT NULL,
    209     bioentry_id INTEGER NOT NULL,
    210     PRIMARY KEY (taxon_bioentry_id)
    211     );
    212 
    213 CREATE INDEX taxon_bioentry_taxon_id ON taxon_bioentry(taxon_id);
    214 CREATE INDEX taxon_bioentry_bioentry_id ON taxon_bioentry(bioentry_id);
    215 ALTER TABLE taxon_bioentry ADD CONSTRAINT fktaxon_bioentry_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    216 
    217 
    218 
    219 CREATE SEQUENCE bioentry_qualifier_pk_seq;
    220 CREATE TABLE bioentry_qualifier (
    221    bioentry_qualifier_id INTEGER DEFAULT nextval ('bioentry_qualifier_pk_seq') NOT NULL,
    222    dataset_id INTEGER NOT NULL,
    223    bioentry_id INTEGER NOT NULL,
    224    type_id INTEGER NOT NULL,
    225    PRIMARY KEY(bioentry_qualifier_id));
    226 
    227 CREATE TABLE bioentry_qualifier_term (
    228    term_id INTEGER,
    229    PRIMARY KEY(bioentry_qualifier_id)
    230 ) INHERITS (bioentry_qualifier);
    231 
    232 CREATE TABLE bioentry_qualifier_accession (
    233    accession_id INTEGER,
    234    PRIMARY KEY(bioentry_qualifier_id)
    235 ) INHERITS (bioentry_qualifier);
    236 
    237 CREATE TABLE bioentry_qualifier_reference (
    238    reference_id INTEGER,
    239    PRIMARY KEY(bioentry_qualifier_id)
    240 ) INHERITS (bioentry_qualifier);
    241 
    242 CREATE TABLE bioentry_qualifier_location (
    243    location_set_id INTEGER,
    244    PRIMARY KEY(bioentry_qualifier_id)
    245 ) INHERITS (bioentry_qualifier);
    246 
    247 CREATE TABLE bioentry_qualifier_txt (
    248    txtvalue TEXT,
    249    PRIMARY KEY(bioentry_qualifier_id)
    250 ) INHERITS (bioentry_qualifier);
    251 
    252 CREATE TABLE bioentry_qualifier_data (
    253    data DOUBLE PRECISION[],
    254    PRIMARY KEY(bioentry_qualifier_id)
    255 ) INHERITS (bioentry_qualifier);
    256 
    257 CREATE TABLE bioentry_qualifier_value (
    258    value DOUBLE PRECISION,
    259    PRIMARY KEY(bioentry_qualifier_id)
    260 ) INHERITS (bioentry_qualifier);
    261 
    262 CREATE TABLE bioentry_qualifier_term_accession_txt (
    263    PRIMARY KEY(bioentry_qualifier_id)
    264 ) INHERITS (bioentry_qualifier_term,bioentry_qualifier_accession,bioentry_qualifier_txt);
    265 
    266 CREATE TABLE bioentry_qualifier_location_reference_txt (
    267    PRIMARY KEY(bioentry_qualifier_id)
    268 ) INHERITS (bioentry_qualifier_location,bioentry_qualifier_reference,bioentry_qualifier_txt);
    269 
    270 CREATE INDEX bioentry_qualifier_bioentry_id ON bioentry_qualifier(bioentry_id,type_id);
    271 CREATE INDEX bioentry_qualifier_type_id ON bioentry_qualifier(dataset_id);
    272 
    273 ALTER TABLE bioentry_qualifier ADD CONSTRAINT fkbioentry_qualifier_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    274 ALTER TABLE bioentry_qualifier ADD CONSTRAINT fkbioentry_qualifier_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    275 ALTER TABLE bioentry_qualifier ADD CONSTRAINT fkbioentry_qualifier_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    276 
    277 ALTER TABLE bioentry_qualifier_accession ADD CONSTRAINT fkbioentry_qualifier_accession_accession_id FOREIGN KEY (accession_id) REFERENCES accession(accession_id) DEFERRABLE INITIALLY DEFERRED;
    278 ALTER TABLE bioentry_qualifier_reference ADD CONSTRAINT fkbioentry_qualifier_reference_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) DEFERRABLE INITIALLY DEFERRED;
    279 ALTER TABLE bioentry_qualifier_term ADD CONSTRAINT fkbioentry_qualifier_term_term_id FOREIGN KEY (term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    280 --ALTER TABLE bioentry_qualifier_location ADD CONSTRAINT fkbioentry_qualifier_location_location_set_id FOREIGN KEY (location_set_id) REFERENCES location(location_set_id) DEFERRABLE INITIALLY DEFERRED;
    281 
    282 ALTER TABLE bioentry_qualifier_term ADD CONSTRAINT fkbioentry_qualifier_term_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    283 ALTER TABLE bioentry_qualifier_term ADD CONSTRAINT fkbioentry_qualifier_term_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    284 ALTER TABLE bioentry_qualifier_term ADD CONSTRAINT fkbioentry_qualifier_term_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    285 CREATE INDEX bioentry_qualifier_term_bioentry_id ON bioentry_qualifier_term(bioentry_id,type_id);
    286 CREATE INDEX bioentry_qualifier_term_dataset_id ON bioentry_qualifier_term(dataset_id);
    287 CREATE INDEX bioentry_qualifier_term_term_id ON bioentry_qualifier_term(term_id);
    288 
    289 ALTER TABLE bioentry_qualifier_accession ADD CONSTRAINT fkbioentry_qualifier_accession_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    290 ALTER TABLE bioentry_qualifier_accession ADD CONSTRAINT fkbioentry_qualifier_accession_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    291 ALTER TABLE bioentry_qualifier_accession ADD CONSTRAINT fkbioentry_qualifier_accession_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    292 CREATE INDEX bioentry_qualifier_accession_bioentry_id ON bioentry_qualifier_accession(bioentry_id,type_id);
    293 CREATE INDEX bioentry_qualifier_accession_dataset_id ON bioentry_qualifier_accession(dataset_id);
    294 CREATE INDEX bioentry_qualifier_accession_accession_id ON bioentry_qualifier_accession(accession_id);
    295 
    296 ALTER TABLE bioentry_qualifier_reference ADD CONSTRAINT fkbioentry_qualifier_reference_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    297 ALTER TABLE bioentry_qualifier_reference ADD CONSTRAINT fkbioentry_qualifier_reference_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    298 ALTER TABLE bioentry_qualifier_reference ADD CONSTRAINT fkbioentry_qualifier_reference_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    299 CREATE INDEX bioentry_qualifier_reference_bioentry_id ON bioentry_qualifier_reference(bioentry_id,type_id);
    300 CREATE INDEX bioentry_qualifier_reference_dataset_id ON bioentry_qualifier_reference(dataset_id);
    301 CREATE INDEX bioentry_qualifier_reference_reference_id ON bioentry_qualifier_reference(reference_id);
    302 
    303 ALTER TABLE bioentry_qualifier_location ADD CONSTRAINT fkbioentry_qualifier_location_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    304 ALTER TABLE bioentry_qualifier_location ADD CONSTRAINT fkbioentry_qualifier_location_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    305 ALTER TABLE bioentry_qualifier_location ADD CONSTRAINT fkbioentry_qualifier_location_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    306 CREATE INDEX bioentry_qualifier_location_bioentry_id ON bioentry_qualifier_location(bioentry_id,type_id);
    307 CREATE INDEX bioentry_qualifier_location_dataset_id ON bioentry_qualifier_location(dataset_id);
    308 CREATE INDEX bioentry_qualifier_location_location_set_id ON bioentry_qualifier_location(location_set_id);
    309 
    310 ALTER TABLE bioentry_qualifier_data ADD CONSTRAINT fkbioentry_qualifier_data_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    311 ALTER TABLE bioentry_qualifier_data ADD CONSTRAINT fkbioentry_qualifier_data_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    312 ALTER TABLE bioentry_qualifier_data ADD CONSTRAINT fkbioentry_qualifier_data_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    313 CREATE INDEX bioentry_qualifier_data_bioentry_id ON bioentry_qualifier_data(bioentry_id,type_id);
    314 CREATE INDEX bioentry_qualifier_data_dataset_id ON bioentry_qualifier_data(dataset_id);
    315 
    316 ALTER TABLE bioentry_qualifier_value ADD CONSTRAINT fkbioentry_qualifier_value_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    317 ALTER TABLE bioentry_qualifier_value ADD CONSTRAINT fkbioentry_qualifier_value_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    318 ALTER TABLE bioentry_qualifier_value ADD CONSTRAINT fkbioentry_qualifier_value_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    319 CREATE INDEX bioentry_qualifier_value_bioentry_id ON bioentry_qualifier_value(bioentry_id,type_id);
    320 CREATE INDEX bioentry_qualifier_value_dataset_id ON bioentry_qualifier_value(dataset_id);
    321 
    322 ALTER TABLE bioentry_qualifier_txt ADD CONSTRAINT fkbioentry_qualifier_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    323 ALTER TABLE bioentry_qualifier_txt ADD CONSTRAINT fkbioentry_qualifier_txt_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    324 ALTER TABLE bioentry_qualifier_txt ADD CONSTRAINT fkbioentry_qualifier_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    325 CREATE INDEX bioentry_qualifier_txt_bioentry_id ON bioentry_qualifier_txt(bioentry_id,type_id);
    326 CREATE INDEX bioentry_qualifier_txt_dataset_id ON bioentry_qualifier_txt(dataset_id);
    327 
    328 ALTER TABLE bioentry_qualifier_term_accession_txt ADD CONSTRAINT fkbioentry_qualifier_term_accession_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    329 ALTER TABLE bioentry_qualifier_term_accession_txt ADD CONSTRAINT fkbioentry_qualifier_term_accession_txt_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    330 ALTER TABLE bioentry_qualifier_term_accession_txt ADD CONSTRAINT fkbioentry_qualifier_term_accession_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    331 ALTER TABLE bioentry_qualifier_term_accession_txt ADD CONSTRAINT fkbioentry_qualifier_term_accession_txt_accession_id FOREIGN KEY (accession_id) REFERENCES accession(accession_id) DEFERRABLE INITIALLY DEFERRED;
    332 ALTER TABLE bioentry_qualifier_term_accession_txt ADD CONSTRAINT fkbioentry_qualifier_term_accession_txt_term_id FOREIGN KEY (term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    333 CREATE INDEX bioentry_qualifier_term_accession_txt_bioentry_id ON bioentry_qualifier_term_accession_txt(bioentry_id,type_id);
    334 CREATE INDEX bioentry_qualifier_term_accession_txt_dataset_id ON bioentry_qualifier_term_accession_txt(dataset_id);
    335 CREATE INDEX bioentry_qualifier_term_accession_txt_accession_id ON bioentry_qualifier_term_accession_txt(accession_id);
    336 CREATE INDEX bioentry_qualifier_term_accession_txt_term_id ON bioentry_qualifier_term_accession_txt(term_id);
    337 
    338 ALTER TABLE bioentry_qualifier_location_reference_txt ADD CONSTRAINT fkbioentry_qualifier_location_reference_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    339 ALTER TABLE bioentry_qualifier_location_reference_txt ADD CONSTRAINT fkbioentry_qualifier_location_reference_txt_bioentry_id FOREIGN KEY (bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    340 ALTER TABLE bioentry_qualifier_location_reference_txt ADD CONSTRAINT fkbioentry_qualifier_location_reference_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    341 ALTER TABLE bioentry_qualifier_location_reference_txt ADD CONSTRAINT fkbioentry_qualifier_location_reference_txt_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) DEFERRABLE INITIALLY DEFERRED;
    342 CREATE INDEX bioentry_qualifier_location_reference_txt_bioentry_id ON bioentry_qualifier_location_reference_txt(bioentry_id,type_id);
    343 CREATE INDEX bioentry_qualifier_location_reference_txt_dataset_id ON bioentry_qualifier_location_reference_txt(dataset_id);
    344 CREATE INDEX bioentry_qualifier_location_reference_txt_reference_id ON bioentry_qualifier_location_reference_txt(reference_id);
    345 CREATE INDEX bioentry_qualifier_location_reference_txt_location_set_id ON bioentry_qualifier_location_reference_txt(location_set_id);
    346 
    347 CREATE SEQUENCE biorelationship_pk_seq;
    348 CREATE TABLE biorelationship (
    349    biorelationship_id INTEGER DEFAULT nextval ( 'biorelationship_pk_seq' ) NOT NULL ,
    350    source_bioentry_id INTEGER NOT NULL ,
    351    target_bioentry_id INTEGER NOT NULL ,
    352    directed BOOLEAN DEFAULT FALSE,
    353    dataset_id INTEGER NOT NULL ,
    354    type_id INTEGER NOT NULL,
    355    PRIMARY KEY(biorelationship_id));
    356 
    357 CREATE INDEX bioerelationship_dataset_id ON biorelationship(dataset_id,type_id);
    358 CREATE INDEX bioerelationship_source_id ON biorelationship(source_bioentry_id,type_id);
    359 CREATE INDEX bioerelationship_target_id ON biorelationship(target_bioentry_id,type_id);
    360 
    361 ALTER TABLE biorelationship ADD CONSTRAINT fkbiorelationship_source_bioentry_id FOREIGN KEY (source_bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    362 ALTER TABLE biorelationship ADD CONSTRAINT fkbiorelationship_target_bioentry_id FOREIGN KEY (target_bioentry_id) REFERENCES bioentry(bioentry_id) DEFERRABLE INITIALLY DEFERRED;
    363 ALTER TABLE biorelationship ADD CONSTRAINT fkbiorelationship_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    364 ALTER TABLE biorelationship ADD CONSTRAINT fkbiorelationship_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    365 --ALTER TABLE biorelationship ADD CONSTRAINT fkbiorelationship_location_set_id FOREIGN KEY (location_set_id) REFERENCES location(location_set_id) DEFERRABLE INITIALLY DEFERRED;
    366 
    367 CREATE SEQUENCE biorelationship_qualifier_pk_seq;
    368 CREATE TABLE biorelationship_qualifier (
    369    biorelationship_qualifier_id INTEGER DEFAULT nextval ('biorelationship_qualifier_pk_seq') NOT NULL,
    370    dataset_id INTEGER NOT NULL,
    371    biorelationship_id INTEGER NOT NULL,
    372    type_id INTEGER NOT NULL,
    373    PRIMARY KEY(biorelationship_qualifier_id));
    374 
    375 CREATE TABLE biorelationship_qualifier_term (
    376    term_id INTEGER,
    377    PRIMARY KEY(biorelationship_qualifier_id)
    378 ) INHERITS (biorelationship_qualifier);
    379 
    380 CREATE TABLE biorelationship_qualifier_accession (
    381    accession_id INTEGER,
    382    PRIMARY KEY(biorelationship_qualifier_id)
    383 ) INHERITS (biorelationship_qualifier);
    384 
    385 CREATE TABLE biorelationship_qualifier_reference (
    386    reference_id INTEGER,
    387    PRIMARY KEY(biorelationship_qualifier_id)
    388 ) INHERITS (biorelationship_qualifier);
    389 
    390 CREATE TABLE biorelationship_qualifier_location (
    391    location_set_id INTEGER,
    392    PRIMARY KEY(biorelationship_qualifier_id)
    393 ) INHERITS (biorelationship_qualifier);
    394 
    395 CREATE TABLE biorelationship_qualifier_txt (
    396    txtvalue TEXT,
    397    PRIMARY KEY(biorelationship_qualifier_id)
    398 ) INHERITS (biorelationship_qualifier);
    399 
    400 CREATE TABLE biorelationship_qualifier_data (
    401    data DOUBLE PRECISION[],
    402    PRIMARY KEY(biorelationship_qualifier_id)
    403 ) INHERITS (biorelationship_qualifier);
    404 
    405 CREATE TABLE biorelationship_qualifier_value (
    406    value DOUBLE PRECISION,
    407    PRIMARY KEY(biorelationship_qualifier_id)
    408 ) INHERITS (biorelationship_qualifier);
    409 
    410 CREATE TABLE biorelationship_qualifier_term_accession_txt (
    411    PRIMARY KEY(biorelationship_qualifier_id)
    412 ) INHERITS (biorelationship_qualifier_term,biorelationship_qualifier_accession,biorelationship_qualifier_txt);
    413 
    414 CREATE TABLE biorelationship_qualifier_location_reference_txt (
    415    PRIMARY KEY(biorelationship_qualifier_id)
    416 ) INHERITS (biorelationship_qualifier_location,biorelationship_qualifier_reference,biorelationship_qualifier_txt);
    417 
    418 CREATE INDEX biorelationship_qualifier_biorelationship_id ON biorelationship_qualifier(biorelationship_id,type_id);
    419 CREATE INDEX biorelationship_qualifier_type_id ON biorelationship_qualifier(dataset_id);
    420 
    421 ALTER TABLE biorelationship_qualifier ADD CONSTRAINT fkbiorelationship_qualifier_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    422 ALTER TABLE biorelationship_qualifier ADD CONSTRAINT fkbiorelationship_qualifier_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    423 ALTER TABLE biorelationship_qualifier ADD CONSTRAINT fkbiorelationship_qualifier_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    424 
    425 ALTER TABLE biorelationship_qualifier_accession ADD CONSTRAINT fkbiorelationship_qualifier_accession_accession_id FOREIGN KEY (accession_id) REFERENCES accession(accession_id) DEFERRABLE INITIALLY DEFERRED;
    426 ALTER TABLE biorelationship_qualifier_reference ADD CONSTRAINT fkbiorelationship_qualifier_reference_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) DEFERRABLE INITIALLY DEFERRED;
    427 ALTER TABLE biorelationship_qualifier_term ADD CONSTRAINT fkbiorelationship_qualifier_term_term_id FOREIGN KEY (term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    428 --ALTER TABLE biorelationship_qualifier_location ADD CONSTRAINT fkbiorelationship_qualifier_location_location_set_id FOREIGN KEY (location_set_id) REFERENCES location(location_set_id) DEFERRABLE INITIALLY DEFERRED;
    429 
    430 ALTER TABLE biorelationship_qualifier_term ADD CONSTRAINT fkbiorelationship_qualifier_term_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    431 ALTER TABLE biorelationship_qualifier_term ADD CONSTRAINT fkbiorelationship_qualifier_term_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    432 ALTER TABLE biorelationship_qualifier_term ADD CONSTRAINT fkbiorelationship_qualifier_term_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    433 CREATE INDEX biorelationship_qualifier_term_biorelationship_id ON biorelationship_qualifier_term(biorelationship_id,type_id);
    434 CREATE INDEX biorelationship_qualifier_term_dataset_id ON biorelationship_qualifier_term(dataset_id);
    435 CREATE INDEX biorelationship_qualifier_term_term_id ON biorelationship_qualifier_term(term_id);
    436 
    437 ALTER TABLE biorelationship_qualifier_accession ADD CONSTRAINT fkbiorelationship_qualifier_accession_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    438 ALTER TABLE biorelationship_qualifier_accession ADD CONSTRAINT fkbiorelationship_qualifier_accession_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    439 ALTER TABLE biorelationship_qualifier_accession ADD CONSTRAINT fkbiorelationship_qualifier_accession_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    440 CREATE INDEX biorelationship_qualifier_accession_biorelationship_id ON biorelationship_qualifier_accession(biorelationship_id,type_id);
    441 CREATE INDEX biorelationship_qualifier_accession_dataset_id ON biorelationship_qualifier_accession(dataset_id);
    442 CREATE INDEX biorelationship_qualifier_accession_accession_id ON biorelationship_qualifier_accession(accession_id);
    443 
    444 ALTER TABLE biorelationship_qualifier_reference ADD CONSTRAINT fkbiorelationship_qualifier_reference_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    445 ALTER TABLE biorelationship_qualifier_reference ADD CONSTRAINT fkbiorelationship_qualifier_reference_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    446 ALTER TABLE biorelationship_qualifier_reference ADD CONSTRAINT fkbiorelationship_qualifier_reference_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    447 CREATE INDEX biorelationship_qualifier_reference_biorelationship_id ON biorelationship_qualifier_reference(biorelationship_id,type_id);
    448 CREATE INDEX biorelationship_qualifier_reference_dataset_id ON biorelationship_qualifier_reference(dataset_id);
    449 CREATE INDEX biorelationship_qualifier_reference_reference_id ON biorelationship_qualifier_reference(reference_id);
    450 
    451 ALTER TABLE biorelationship_qualifier_location ADD CONSTRAINT fkbiorelationship_qualifier_location_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    452 ALTER TABLE biorelationship_qualifier_location ADD CONSTRAINT fkbiorelationship_qualifier_location_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    453 ALTER TABLE biorelationship_qualifier_location ADD CONSTRAINT fkbiorelationship_qualifier_location_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    454 CREATE INDEX biorelationship_qualifier_location_biorelationship_id ON biorelationship_qualifier_location(biorelationship_id,type_id);
    455 CREATE INDEX biorelationship_qualifier_location_dataset_id ON biorelationship_qualifier_location(dataset_id);
    456 CREATE INDEX biorelationship_qualifier_location_location_set_id ON biorelationship_qualifier_location(location_set_id);
    457 
    458 ALTER TABLE biorelationship_qualifier_data ADD CONSTRAINT fkbiorelationship_qualifier_data_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    459 ALTER TABLE biorelationship_qualifier_data ADD CONSTRAINT fkbiorelationship_qualifier_data_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    460 ALTER TABLE biorelationship_qualifier_data ADD CONSTRAINT fkbiorelationship_qualifier_data_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    461 CREATE INDEX biorelationship_qualifier_data_biorelationship_id ON biorelationship_qualifier_data(biorelationship_id,type_id);
    462 CREATE INDEX biorelationship_qualifier_data_dataset_id ON biorelationship_qualifier_data(dataset_id);
    463 
    464 ALTER TABLE biorelationship_qualifier_value ADD CONSTRAINT fkbiorelationship_qualifier_value_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    465 ALTER TABLE biorelationship_qualifier_value ADD CONSTRAINT fkbiorelationship_qualifier_value_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    466 ALTER TABLE biorelationship_qualifier_value ADD CONSTRAINT fkbiorelationship_qualifier_value_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    467 CREATE INDEX biorelationship_qualifier_value_biorelationship_id ON biorelationship_qualifier_value(biorelationship_id,type_id);
    468 CREATE INDEX biorelationship_qualifier_value_dataset_id ON biorelationship_qualifier_value(dataset_id);
    469 
    470 ALTER TABLE biorelationship_qualifier_txt ADD CONSTRAINT fkbiorelationship_qualifier_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    471 ALTER TABLE biorelationship_qualifier_txt ADD CONSTRAINT fkbiorelationship_qualifier_txt_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    472 ALTER TABLE biorelationship_qualifier_txt ADD CONSTRAINT fkbiorelationship_qualifier_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    473 CREATE INDEX biorelationship_qualifier_txt_biorelationship_id ON biorelationship_qualifier_txt(biorelationship_id,type_id);
    474 CREATE INDEX biorelationship_qualifier_txt_dataset_id ON biorelationship_qualifier_txt(dataset_id);
    475 
    476 ALTER TABLE biorelationship_qualifier_term_accession_txt ADD CONSTRAINT fkbiorelationship_qualifier_term_accession_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    477 ALTER TABLE biorelationship_qualifier_term_accession_txt ADD CONSTRAINT fkbiorelationship_qualifier_term_accession_txt_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    478 ALTER TABLE biorelationship_qualifier_term_accession_txt ADD CONSTRAINT fkbiorelationship_qualifier_term_accession_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    479 ALTER TABLE biorelationship_qualifier_term_accession_txt ADD CONSTRAINT fkbiorelationship_qualifier_term_accession_txt_accession_id FOREIGN KEY (accession_id) REFERENCES accession(accession_id) DEFERRABLE INITIALLY DEFERRED;
    480 ALTER TABLE biorelationship_qualifier_term_accession_txt ADD CONSTRAINT fkbiorelationship_qualifier_term_accession_txt_term_id FOREIGN KEY (term_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    481 CREATE INDEX biorelationship_qualifier_term_accession_txt_biorelationship_id ON biorelationship_qualifier_term_accession_txt(biorelationship_id,type_id);
    482 CREATE INDEX biorelationship_qualifier_term_accession_txt_dataset_id ON biorelationship_qualifier_term_accession_txt(dataset_id);
    483 CREATE INDEX biorelationship_qualifier_term_accession_txt_accession_id ON biorelationship_qualifier_term_accession_txt(accession_id);
    484 CREATE INDEX biorelationship_qualifier_term_accession_txt_term_id ON biorelationship_qualifier_term_accession_txt(term_id);
    485 
    486 ALTER TABLE biorelationship_qualifier_location_reference_txt ADD CONSTRAINT fkbiorelationship_qualifier_location_reference_txt_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    487 ALTER TABLE biorelationship_qualifier_location_reference_txt ADD CONSTRAINT fkbiorelationship_qualifier_location_reference_txt_biorelationship_id FOREIGN KEY (biorelationship_id) REFERENCES biorelationship(biorelationship_id) DEFERRABLE INITIALLY DEFERRED;
    488 ALTER TABLE biorelationship_qualifier_location_reference_txt ADD CONSTRAINT fkbiorelationship_qualifier_location_reference_txt_dataset_id FOREIGN KEY (dataset_id) REFERENCES dataset(dataset_id) DEFERRABLE INITIALLY DEFERRED;
    489 ALTER TABLE biorelationship_qualifier_location_reference_txt ADD CONSTRAINT fkbiorelationship_qualifier_location_reference_txt_reference_id FOREIGN KEY (reference_id) REFERENCES reference(reference_id) DEFERRABLE INITIALLY DEFERRED;
    490 CREATE INDEX biorelationship_qualifier_location_reference_txt_biorelationship_id ON biorelationship_qualifier_location_reference_txt(biorelationship_id,type_id);
    491 CREATE INDEX biorelationship_qualifier_location_reference_txt_dataset_id ON biorelationship_qualifier_location_reference_txt(dataset_id);
    492 CREATE INDEX biorelationship_qualifier_location_reference_txt_reference_id ON biorelationship_qualifier_location_reference_txt(reference_id);
    493 CREATE INDEX biorelationship_qualifier_location_reference_txt_location_set_id ON biorelationship_qualifier_location_reference_txt(location_set_id);
    494 
    495 --definition table on top
    496 ALTER TABLE dataset ADD CONSTRAINT fkterm_dataset_term_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    497 ALTER TABLE dataset_relation ADD CONSTRAINT fkterm_dataset_relation_type_id FOREIGN KEY (type_id) REFERENCES term(term_id) DEFERRABLE INITIALLY DEFERRED;
    498 
     154    source_id INTEGER,
     155    parent_id INTEGER,
     156    PRIMARY KEY (set_link_id)
     157);
     158
     159CREATE TABLE set_relation_link (
     160    partner_id INTEGER,
     161    PRIMARY KEY (set_link_id)
     162) INHERITS (set_link);
     163
     164CREATE TABLE set_child_link (
     165    child_id INTEGER,
     166    PRIMARY KEY (set_link_id)
     167) INHERITS (set_link);
     168
     169CREATE TABLE set_family_link (
     170    PRIMARY KEY (set_link_id)
     171) INHERITS (set_relation_link,set_child_link);
     172
     173
     174CREATE INDEX sl_type ON set_link(type_id,source_id);
     175CREATE INDEX srl_type ON set_relation_link(type_id,source_id);
     176CREATE INDEX scl_type ON set_child_link(type_id,source_id);
     177CREATE INDEX sfl_type ON set_family_link(type_id,source_id);
     178
     179CREATE INDEX sl_parent ON set_link(parent_id);
     180CREATE INDEX srl_parent ON set_relation_link(parent_id);
     181CREATE INDEX scl_parent ON set_child_link(parent_id);
     182CREATE INDEX sfl_parent ON set_family_link(parent_id);
     183
     184CREATE INDEX srl_partner ON set_relation_link(partner_id);
     185CREATE INDEX sfl_partner ON set_family_link(partner_id);
     186
     187CREATE INDEX scl_child ON set_child_link(child_id);
     188CREATE INDEX sfl_child ON set_family_link(child_id);
     189
     190
     191CREATE SEQUENCE term_link_pk_seq CACHE 25;
     192CREATE TABLE term_link (
     193    term_link_id INTEGER DEFAULT nextval('term_link_pk_seq') NOT NULL,
     194    type_id INTEGER,
     195    source_id INTEGER,
     196    parent_id INTEGER,
     197    PRIMARY KEY (term_link_id)
     198);
     199
     200CREATE TABLE term_relation_link (
     201    partner_id INTEGER,
     202    PRIMARY KEY (term_link_id)
     203) INHERITS (term_link);
     204
     205CREATE TABLE term_child_link (
     206    child_id INTEGER,
     207    PRIMARY KEY (term_link_id)
     208) INHERITS (term_link);
     209
     210CREATE TABLE term_family_link (
     211    PRIMARY KEY (term_link_id)
     212) INHERITS (term_relation_link,term_child_link);
     213
     214
     215CREATE INDEX tl_type ON term_link(type_id,source_id);
     216CREATE INDEX trl_type ON term_relation_link(type_id,source_id);
     217CREATE INDEX tcl_type ON term_child_link(type_id,source_id);
     218CREATE INDEX tfl_type ON term_family_link(type_id,source_id);
     219
     220CREATE INDEX tl_parent ON term_link(parent_id);
     221CREATE INDEX trl_parent ON term_relation_link(parent_id);
     222CREATE INDEX tcl_parent ON term_child_link(parent_id);
     223CREATE INDEX tfl_parent ON term_family_link(parent_id);
     224
     225CREATE INDEX trl_partner ON term_relation_link(partner_id);
     226CREATE INDEX tfl_partner ON term_family_link(partner_id);
     227
     228CREATE INDEX tcl_child ON term_child_link(child_id);
     229CREATE INDEX tfl_child ON term_family_link(child_id);
     230
     231
     232CREATE SEQUENCE thing_link_pk_seq CACHE 25;
     233CREATE TABLE thing_link (
     234    thing_link_id INTEGER DEFAULT nextval('thing_link_pk_seq') NOT NULL,
     235    type_id INTEGER,
     236    source_id INTEGER,
     237    parent_id INTEGER,
     238    PRIMARY KEY (thing_link_id)
     239);
     240
     241CREATE TABLE thing_relation_link (
     242    partner_id INTEGER,
     243    PRIMARY KEY (thing_link_id)
     244) INHERITS (thing_link);
     245
     246CREATE TABLE thing_child_link (
     247    child_id INTEGER,
     248    PRIMARY KEY (thing_link_id)
     249) INHERITS (thing_link);
     250
     251CREATE TABLE thing_family_link (
     252    PRIMARY KEY (thing_link_id)
     253) INHERITS (thing_relation_link,thing_child_link);
     254
     255
     256CREATE INDEX thl_type ON thing_link(type_id,source_id);
     257CREATE INDEX thrl_type ON thing_relation_link(type_id,source_id);
     258CREATE INDEX thcl_type ON thing_child_link(type_id,source_id);
     259CREATE INDEX thfl_type ON thing_family_link(type_id,source_id);
     260
     261CREATE INDEX thl_parent ON thing_link(parent_id);
     262CREATE INDEX thrl_parent ON thing_relation_link(parent_id);
     263CREATE INDEX thcl_parent ON thing_child_link(parent_id);
     264CREATE INDEX thfl_parent ON thing_family_link(parent_id);
     265
     266CREATE INDEX thrl_partner ON thing_relation_link(partner_id);
     267CREATE INDEX thfl_partner ON thing_family_link(partner_id);
     268
     269CREATE INDEX thcl_child ON thing_child_link(child_id);
     270CREATE INDEX thfl_child ON thing_family_link(child_id);
     271
     272
     273
     274
     275
     276
     277-------------------------------------------------------
     278---------------- Propertys----------------------------
     279------------------------------------------------------
     280
     281-- One prop id sequence needed to be able to make properties on properties
     282CREATE SEQUENCE prop_pk_seq;
     283
     284CREATE TABLE prop (
     285    prop_id INTEGER DEFAULT nextval('prop_pk_seq') NOT NULL,
     286    type_id INTEGER NOT NULL,
     287    source_id INTEGER NOT NULL,
     288    PRIMARY KEY (prop_id)
     289);
     290
     291CREATE TABLE prop_txt (
     292    value TEXT NOT NULL,
     293    PRIMARY KEY (prop_id)
     294) INHERITS (prop);
     295
     296CREATE TABLE prop_doublearray (
     297    value DOUBLE PRECISION[],
     298    PRIMARY KEY (prop_id)
     299) INHERITS (prop);
     300
     301CREATE TABLE prop_double (
     302    value DOUBLE PRECISION,
     303    PRIMARY KEY (prop_id)
     304) INHERITS (prop);
     305
     306CREATE TABLE prop_term (
     307    term_id INTEGER,
     308    PRIMARY KEY (prop_id)
     309) INHERITS (prop);
     310
     311CREATE TABLE prop_set (
     312    set_id INTEGER,
     313    PRIMARY KEY (prop_id)
     314) INHERITS (prop);
     315
     316CREATE TABLE prop_thing (
     317    thing_id INTEGER,
     318    PRIMARY KEY (prop_id)
     319) INHERITS (prop);
     320
     321
     322CREATE INDEX p_type ON prop(type_id,source_id);
     323CREATE INDEX pt_type ON prop_txt(type_id,source_id);
     324CREATE INDEX pda_type ON prop_doublearray(type_id,source_id);
     325CREATE INDEX pd_type ON prop_double(type_id,source_id);
     326CREATE INDEX pterm_type ON prop_term(type_id,source_id);
     327CREATE INDEX pset_type ON prop_set(type_id,source_id);
     328CREATE INDEX pthing_type ON prop_thing(type_id,source_id);
     329
     330CREATE INDEX pt_value ON prop_txt(value);
     331CREATE INDEX pda_value ON prop_doublearray(value);
     332CREATE INDEX pd_value ON prop_double(value);
     333
     334CREATE INDEX pterm_term ON prop_term(term_id);
     335CREATE INDEX pset_set ON prop_set(set_id);
     336CREATE INDEX pthing_thing ON prop_thing(thing_id);
     337
     338
     339CREATE TABLE set_prop (
     340    PRIMARY KEY (prop_id)
     341) INHERITS (prop);
     342
     343CREATE TABLE set_prop_txt (
     344    PRIMARY KEY (prop_id)
     345) INHERITS (set_prop,prop_txt);
     346
     347CREATE TABLE set_prop_doublearray (
     348    PRIMARY KEY (prop_id)
     349) INHERITS (set_prop,prop_doublearray);
     350
     351CREATE TABLE set_prop_double (
     352    PRIMARY KEY (prop_id)
     353) INHERITS (set_prop,prop_double);
     354
     355CREATE TABLE set_prop_term (
     356    PRIMARY KEY (prop_id)
     357) INHERITS (set_prop,prop_term);
     358
     359CREATE TABLE set_prop_thing (
     360    PRIMARY KEY (prop_id)
     361) INHERITS (set_prop,prop_thing);
     362
     363
     364CREATE INDEX sp_type ON set_prop(type_id,source_id);
     365CREATE INDEX spt_type ON set_prop_txt(type_id,source_id);
     366CREATE INDEX spda_type ON set_prop_doublearray(type_id,source_id);
     367CREATE INDEX spd_type ON set_prop_double(type_id,source_id);
     368CREATE INDEX spterm_type ON set_prop_term(type_id,source_id);
     369CREATE INDEX spthing_type ON set_prop_thing(type_id,source_id);
     370
     371CREATE INDEX spt_value ON set_prop_txt(value);
     372CREATE INDEX spda_value ON set_prop_doublearray(value);
     373CREATE INDEX spd_value ON set_prop_double(value);
     374
     375CREATE INDEX spterm_term ON set_prop_term(term_id);
     376CREATE INDEX spthing_thing ON set_prop_thing(thing_id);
     377
     378
     379
     380CREATE TABLE term_prop (
     381    PRIMARY KEY (prop_id)
     382) INHERITS (prop);
     383
     384CREATE TABLE term_prop_txt (
     385    PRIMARY KEY (prop_id)
     386) INHERITS (term_prop,prop_txt);
     387
     388CREATE TABLE term_prop_doublearray (
     389    PRIMARY KEY (prop_id)
     390) INHERITS (term_prop,prop_doublearray);
     391
     392CREATE TABLE term_prop_double (
     393    PRIMARY KEY (prop_id)
     394) INHERITS (term_prop,prop_double);
     395
     396CREATE TABLE term_prop_set (
     397    PRIMARY KEY (prop_id)
     398) INHERITS (term_prop,prop_set);
     399
     400CREATE TABLE term_prop_thing (
     401    PRIMARY KEY (prop_id)
     402) INHERITS (term_prop,prop_thing);
     403
     404
     405CREATE INDEX tp_type ON term_prop(type_id,source_id);
     406CREATE INDEX tpt_type ON term_prop_txt(type_id,source_id);
     407CREATE INDEX tpda_type ON term_prop_doublearray(type_id,source_id);
     408CREATE INDEX tpd_type ON term_prop_double(type_id,source_id);
     409CREATE INDEX tpset_type ON term_prop_set(type_id,source_id);
     410CREATE INDEX tpthing_type ON term_prop_thing(type_id,source_id);
     411
     412CREATE INDEX tpt_value ON term_prop_txt(value);
     413CREATE INDEX tpda_value ON term_prop_doublearray(value);
     414CREATE INDEX tpd_value ON term_prop_double(value);
     415
     416CREATE INDEX tpset_set ON term_prop_set(set_id);
     417CREATE INDEX tpthing_thing ON term_prop_thing(thing_id);
     418
     419
     420
     421
     422
     423CREATE TABLE thing_prop (
     424    PRIMARY KEY (prop_id)
     425) INHERITS (prop);
     426
     427CREATE TABLE thing_prop_txt (
     428    PRIMARY KEY (prop_id)
     429) INHERITS (thing_prop,prop_txt);
     430
     431CREATE TABLE thing_prop_doublearray (
     432    PRIMARY KEY (prop_id)
     433) INHERITS (thing_prop,prop_doublearray);
     434
     435CREATE TABLE thing_prop_double (
     436    PRIMARY KEY (prop_id)
     437) INHERITS (thing_prop,prop_double);
     438
     439CREATE TABLE thing_prop_term (
     440    PRIMARY KEY (prop_id)
     441) INHERITS (thing_prop,prop_term);
     442
     443CREATE TABLE thing_prop_set (
     444    PRIMARY KEY (prop_id)
     445) INHERITS (thing_prop,prop_set);
     446
     447CREATE TABLE thing_prop_sequence (
     448    length INTEGER NOT NULL,
     449    strand BOOLEAN NOT NULL,
     450    linear BOOLEAN NOT NULL,
     451    moleculetype INTEGER,
     452    sequence TEXT,
     453    PRIMARY KEY (prop_id)
     454) INHERITS (thing_prop);
     455
     456CREATE INDEX thp_type ON thing_prop(type_id,source_id);
     457CREATE INDEX thpt_type ON thing_prop_txt(type_id,source_id);
     458CREATE INDEX thpda_type ON thing_prop_doublearray(type_id,source_id);
     459CREATE INDEX thpd_type ON thing_prop_double(type_id,source_id);
     460CREATE INDEX thpterm_type ON thing_prop_term(type_id,source_id);
     461CREATE INDEX thpset_type ON thing_prop_set(type_id,source_id);
     462CREATE INDEX thpsequence_type ON thing_prop_sequence(type_id,source_id);
     463
     464CREATE INDEX thpt_value ON thing_prop_txt(value);
     465CREATE INDEX thpda_value ON thing_prop_doublearray(value);
     466CREATE INDEX thpd_value ON thing_prop_double(value);
     467
     468CREATE INDEX thpterm_term ON thing_prop_term(term_id);
     469CREATE INDEX thpset_set ON thing_prop_set(set_id);
     470
     471
     472CREATE TABLE set_link_prop (
     473    PRIMARY KEY (prop_id)
     474) INHERITS (prop);
     475
     476CREATE TABLE set_link_prop_txt (
     477    PRIMARY KEY (prop_id)
     478) INHERITS (set_link_prop,prop_txt);
     479
     480CREATE TABLE set_link_prop_doublearray (
     481    PRIMARY KEY (prop_id)
     482) INHERITS (set_link_prop,prop_doublearray);
     483
     484CREATE TABLE set_link_prop_double (
     485    PRIMARY KEY (prop_id)
     486) INHERITS (set_link_prop,prop_double);
     487
     488CREATE TABLE set_link_prop_term (
     489    PRIMARY KEY (prop_id)
     490) INHERITS (set_link_prop,prop_term);
     491
     492CREATE TABLE set_link_prop_set (
     493    PRIMARY KEY (prop_id)
     494) INHERITS (set_link_prop,prop_set);
     495
     496CREATE TABLE set_link_prop_thing (
     497    PRIMARY KEY (prop_id)
     498) INHERITS (set_link_prop,prop_thing);
     499
     500
     501CREATE INDEX slp_type ON set_link_prop(type_id,source_id);
     502CREATE INDEX slpt_type ON set_link_prop_txt(type_id,source_id);
     503CREATE INDEX slpda_type ON set_link_prop_doublearray(type_id,source_id);
     504CREATE INDEX slpd_type ON set_link_prop_double(type_id,source_id);
     505CREATE INDEX slpterm_type ON set_link_prop_term(type_id,source_id);
     506CREATE INDEX slpset_type ON set_link_prop_term(type_id,source_id);
     507CREATE INDEX slpthing_type ON set_link_prop_thing(type_id,source_id);
     508
     509CREATE INDEX slpt_value ON set_link_prop_txt(value);
     510CREATE INDEX slpda_value ON set_link_prop_doublearray(value);
     511CREATE INDEX slpd_value ON set_link_prop_double(value);
     512
     513CREATE INDEX slpterm_term ON set_link_prop_term(term_id);
     514CREATE INDEX slpset_set ON set_link_prop_set(set_id);
     515CREATE INDEX slpthing_thing ON set_link_prop_thing(thing_id);
     516
     517CREATE TABLE term_link_prop (
     518    PRIMARY KEY (prop_id)
     519) INHERITS (prop);
     520
     521CREATE TABLE term_link_prop_txt (
     522    PRIMARY KEY (prop_id)
     523) INHERITS (term_link_prop,prop_txt);
     524
     525CREATE TABLE term_link_prop_doublearray (
     526    PRIMARY KEY (prop_id)
     527) INHERITS (term_link_prop,prop_doublearray);
     528
     529CREATE TABLE term_link_prop_double (
     530    PRIMARY KEY (prop_id)
     531) INHERITS (term_link_prop,prop_double);
     532
     533CREATE TABLE term_link_prop_term (
     534    PRIMARY KEY (prop_id)
     535) INHERITS (term_link_prop,prop_term);
     536
     537CREATE TABLE term_link_prop_set (
     538    PRIMARY KEY (prop_id)
     539) INHERITS (term_link_prop,prop_set);
     540
     541CREATE TABLE term_link_prop_thing (
     542    PRIMARY KEY (prop_id)
     543) INHERITS (term_link_prop,prop_thing);
     544
     545
     546CREATE INDEX tlp_type ON term_link_prop(type_id,source_id);
     547CREATE INDEX tlpt_type ON term_link_prop_txt(type_id,source_id);
     548CREATE INDEX tlpda_type ON term_link_prop_doublearray(type_id,source_id);
     549CREATE INDEX tlpd_type ON term_link_prop_double(type_id,source_id);
     550CREATE INDEX tlpterm_type ON term_link_prop_term(type_id,source_id);
     551CREATE INDEX tlpset_type ON term_link_prop_term(type_id,source_id);
     552CREATE INDEX tlpthing_type ON term_link_prop_thing(type_id,source_id);
     553
     554CREATE INDEX tlpt_value ON term_link_prop_txt(value);
     555CREATE INDEX tlpda_value ON term_link_prop_doublearray(value);
     556CREATE INDEX tlpd_value ON term_link_prop_double(value);
     557
     558CREATE INDEX tlpterm_term ON term_link_prop_term(term_id);
     559CREATE INDEX tlpset_set ON term_link_prop_set(set_id);
     560CREATE INDEX tlpthing_thing ON term_link_prop_thing(thing_id);
     561
     562
     563
     564CREATE TABLE thing_link_prop (
     565    PRIMARY KEY (prop_id)
     566) INHERITS (prop);
     567
     568CREATE TABLE thing_link_prop_txt (
     569    PRIMARY KEY (prop_id)
     570) INHERITS (thing_link_prop,prop_txt);
     571
     572CREATE TABLE thing_link_prop_doublearray (
     573    PRIMARY KEY (prop_id)
     574) INHERITS (thing_link_prop,prop_doublearray);
     575
     576CREATE TABLE thing_link_prop_double (
     577    PRIMARY KEY (prop_id)
     578) INHERITS (thing_link_prop,prop_double);
     579
     580CREATE TABLE thing_link_prop_term (
     581    PRIMARY KEY (prop_id)
     582) INHERITS (thing_link_prop,prop_term);
     583
     584CREATE TABLE thing_link_prop_set (
     585    PRIMARY KEY (prop_id)
     586) INHERITS (thing_link_prop,prop_set);
     587
     588CREATE TABLE thing_link_prop_thing (
     589    PRIMARY KEY (prop_id)
     590) INHERITS (thing_link_prop,prop_thing);
     591
     592
     593CREATE INDEX thlp_type ON thing_link_prop(type_id,source_id);
     594CREATE INDEX thlpt_type ON thing_link_prop_txt(type_id,source_id);
     595CREATE INDEX thlpda_type ON thing_link_prop_doublearray(type_id,source_id);
     596CREATE INDEX thlpd_type ON thing_link_prop_double(type_id,source_id);
     597CREATE INDEX thlpterm_type ON thing_link_prop_term(type_id,source_id);
     598CREATE INDEX thlpset_type ON thing_link_prop_term(type_id,source_id);
     599CREATE INDEX thlpthing_type ON thing_link_prop_thing(type_id,source_id);
     600
     601CREATE INDEX thlpt_value ON thing_link_prop_txt(value);
     602CREATE INDEX thlpda_value ON thing_link_prop_doublearray(value);
     603CREATE INDEX thlpd_value ON thing_link_prop_double(value);
     604
     605CREATE INDEX thlpterm_term ON thing_link_prop_term(term_id);
     606CREATE INDEX thlpset_set ON thing_link_prop_set(set_id);
     607CREATE INDEX thlpthing_thing ON thing_link_prop_thing(thing_id);
     608
Note: See TracChangeset for help on using the changeset viewer.