Changeset 251


Ignore:
Timestamp:
Nov 3, 2009, 3:32:24 PM (10 years ago)
Author:
jjbot
Message:

Added range to item_link_prop.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • sql/preimport.sql

    r217 r251  
    3131-- * relations are stored twice in both orientations, although with the same link_id (primary key).
    3232-- * 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
     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
    3434-- in this format saves an extra join (also here we store the data in both orientations).
    3535-- CLARIFICATION: when we store for example protein interactions, with some expermiental techniques we not only have pairs of proteins but frequently also complex
    3636-- 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.
     37-- complex interactinos which are essentially hyperedges. To store this we need an extra dimension, i.e. use the hierarchical (directed) graph strucutre.
    3838-- However, there is no biological reason to store complexes in a hierarchical way and duo interactions not. In essence, also duo-interactions form an
    3939-- 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
     
    318318) INHERITS (prop);
    319319
     320CREATE TABLE prop_range (
     321    start_pos BIGINT NOT NULL,
     322    end_pos BIGINT NOT NULL,
     323    strand CHAR(1) NOT NULL
     324) INHERITS (prop)
     325
    320326CREATE TABLE prop_term (
    321327    term_id INTEGER,
     
    332338    PRIMARY KEY (prop_id)
    333339) INHERITS (prop);
    334 
    335340
    336341CREATE INDEX p_type ON prop(type_id,source_id);
     
    594599------------ item_link --------------
    595600CREATE TABLE item_link_prop (
     601    item_link_id INTEGER NOT NULL,
    596602    PRIMARY KEY (prop_id)
    597603) INHERITS (prop);
     
    621627) INHERITS (item_link_prop,prop_item);
    622628
     629CREATE TABLE item_link_prop_range (
     630) INHERITS (item_link_prop, prop_range)
    623631
    624632CREATE INDEX thlp_type ON item_link_prop(type_id,source_id);
     
    629637CREATE INDEX thlpset_type ON item_link_prop_term(type_id,source_id);
    630638CREATE INDEX thlpitem_type ON item_link_prop_item(type_id,source_id);
     639CREATE INDEX thlprange_item_type ON item_link_prop_range(type_id, source_id);
     640CREATE INDEX thlprange_type_start ON item_link_prop_range(type_id, start_pos);
     641CREATE INDEX thlprange_type_end ON item_link_prop_range(type_id, end_pos);
    631642
    632643CREATE INDEX thlpt_value ON item_link_prop_txt(value);
     
    639650
    640651-- Added by Patrick
    641 --
     652
    642653CREATE SEQUENCE set_pg_group_pk_seq
    643654  INCREMENT 1
Note: See TracChangeset for help on using the changeset viewer.