tables.sql 3.24 KB
Newer Older
1

2 3 4 5 6 7 8 9 10 11 12 13 14
-- ---------------------------------------------------------------------
-- The schema for the ensembl_ontology_NN database.
-- ---------------------------------------------------------------------

CREATE TABLE meta (
  meta_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  meta_key      VARCHAR(64) NOT NULL,
  meta_value    VARCHAR(128),

  PRIMARY KEY (meta_id),
  UNIQUE INDEX key_value_idx (meta_key, meta_value)
);

15 16 17
INSERT INTO meta (meta_key, meta_value)
  VALUES ('schema_type', 'ontology');

18 19 20 21 22 23 24 25 26
CREATE TABLE ontology (
  ontology_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(64) NOT NULL,
  namespace     VARCHAR(64) NOT NULL,

  PRIMARY KEY (ontology_id),
  UNIQUE INDEX name_namespace_idx (name, namespace)
);

27 28 29 30 31 32 33 34 35
CREATE TABLE subset (
  subset_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name          VARCHAR(64) NOT NULL,
  definition    VARCHAR(128) NOT NULL,

  PRIMARY KEY (subset_id),
  UNIQUE INDEX name_idx (name)
);

36 37 38
CREATE TABLE term (
  term_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ontology_id   INT UNSIGNED NOT NULL,
39
  subsets       TEXT,
40
  accession     VARCHAR(64) NOT NULL,
41 42
  name          VARCHAR(255) NOT NULL,
  definition    TEXT,
43
  is_root       INT,
Magali Ruffier's avatar
Magali Ruffier committed
44
  is_obsolete   INT,
45 46

  PRIMARY KEY (term_id),
47
  UNIQUE INDEX accession_idx (accession),
48 49
  UNIQUE INDEX ontology_acc_idx (ontology_id, accession),
  INDEX name_idx (name)
50 51
);

52 53
CREATE TABLE synonym (
  synonym_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
54
  term_id       INT UNSIGNED NOT NULL,
55
  name          TEXT NOT NULL,
56 57

  PRIMARY KEY (synonym_id),
58
  UNIQUE INDEX term_synonym_idx (term_id, synonym_id),
59
  INDEX name_idx (name(50))
60 61
);

62 63 64 65 66 67 68 69 70 71
CREATE TABLE alt_id (
  alt_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  term_id       INT UNSIGNED NOT NULL,
  accession     VARCHAR(64) NOT NULL,

  PRIMARY KEY (alt_id),
  UNIQUE INDEX term_alt_idx (term_id, alt_id),
  INDEX accession_idx (accession(50))
);

72 73
CREATE TABLE relation_type (
  relation_type_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
74
  name              VARCHAR(64) NOT NULL,
75 76 77 78 79 80 81 82 83 84

  PRIMARY KEY (relation_type_id),
  UNIQUE INDEX name_idx (name)
);

CREATE TABLE relation (
  relation_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  child_term_id     INT UNSIGNED NOT NULL,
  parent_term_id    INT UNSIGNED NOT NULL,
  relation_type_id  INT UNSIGNED NOT NULL,
85
  intersection_of   TINYINT UNSIGNED NOT NULL DEFAULT 0,
86
  ontology_id       INT UNSIGNED NOT NULL,
87 88

  PRIMARY KEY (relation_id),
89
  UNIQUE INDEX child_parent_idx
90
    (child_term_id, parent_term_id, relation_type_id, intersection_of, ontology_id),
91 92 93 94 95 96 97 98
  INDEX parent_idx (parent_term_id)
);

CREATE TABLE closure (
  closure_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  child_term_id     INT UNSIGNED NOT NULL,
  parent_term_id    INT UNSIGNED NOT NULL,
  subparent_term_id INT UNSIGNED,
99
  distance          TINYINT UNSIGNED NOT NULL,
100
  ontology_id       INT UNSIGNED NOT NULL,
101 102

  PRIMARY KEY (closure_id),
103
  UNIQUE INDEX child_parent_idx
104
    (child_term_id, parent_term_id, subparent_term_id, ontology_id),
105 106
  INDEX parent_subparent_idx
    (parent_term_id, subparent_term_id)
107
);
108

109 110 111 112
-- There are additional tables in the released databases called
-- "aux_XX_YY_map".  These are created by the "add_subset_maps.pl"
-- scripts.  Please see the README document for further information.

113
-- $Id$