/usr/lib/R/site-library/AnnotationDbi/DBschemas/schemas_0.9/RODENT_DB.sql is in r-bioc-annotationdbi 1.26.1-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 | --
-- RODENT_DB schema
-- ================
--
-- The "genes" table is the central table.
CREATE TABLE genes (
id INTEGER PRIMARY KEY,
gene_id VARCHAR(10) NOT NULL UNIQUE -- Entrez Gene ID
);
-- Data linked to the "genes" table.
CREATE TABLE accessions (
id INTEGER NOT NULL, -- REFERENCES genes
accession VARCHAR(20) NOT NULL, -- GenBank accession number
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE alias (
id INTEGER NOT NULL, -- REFERENCES genes
alias_symbol VARCHAR(80) NOT NULL, -- gene symbol or alias
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE chromosomes (
id INTEGER NOT NULL, -- REFERENCES genes
chromosome VARCHAR(2) NOT NULL, -- chromosome name
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE chromosome_locations (
id INTEGER NOT NULL, -- REFERENCES genes
chromosome VARCHAR(20) NOT NULL, -- sequence name
start_location INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE cytogenetic_locations (
id INTEGER NOT NULL, -- REFERENCES genes
cytogenetic_location VARCHAR(20) NOT NULL, -- cytoband location
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE ec (
id INTEGER NOT NULL, -- REFERENCES genes
ec_number VARCHAR(13) NOT NULL, -- EC number (no "EC:" prefix)
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE gene_info (
id INTEGER NOT NULL UNIQUE, -- REFERENCES genes
gene_name VARCHAR(255) NOT NULL, -- gene name
symbol VARCHAR(80) NOT NULL, -- gene symbol
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_bp (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_bp_all (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_cc (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_cc_all (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_mf (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE go_mf_all (
id INTEGER NOT NULL, -- REFERENCES genes
go_id CHAR(10) NOT NULL, -- GO ID
evidence CHAR(3) NOT NULL, -- GO evidence code
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE kegg (
id INTEGER NOT NULL, -- REFERENCES genes
kegg_id CHAR(5) NOT NULL, -- KEGG pathway short ID
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE pfam (
id INTEGER NOT NULL, -- REFERENCES genes
ipi_id CHAR(11) NOT NULL, -- IPI accession number
pfam_id CHAR(7) NULL, -- Pfam ID
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE prosite (
id INTEGER NOT NULL, -- REFERENCES genes
ipi_id CHAR(11) NOT NULL, -- IPI accession number
prosite_id CHAR(7) NULL, -- PROSITE ID
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE pubmed (
id INTEGER NOT NULL, -- REFERENCES genes
pubmed_id VARCHAR(10) NOT NULL, -- PubMed ID
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE refseq (
id INTEGER NOT NULL, -- REFERENCES genes
accession VARCHAR(20) NOT NULL, -- RefSeq accession number
FOREIGN KEY (id) REFERENCES genes (id)
);
CREATE TABLE unigene (
id INTEGER NOT NULL, -- REFERENCES genes
unigene_id VARCHAR(10) NOT NULL, -- UniGene ID
FOREIGN KEY (id) REFERENCES genes (id)
);
-- Standalone data tables.
CREATE TABLE chrlengths (
chr VARCHAR(2) PRIMARY KEY, -- chromosome name
length INTEGER NOT NULL
);
-- Metadata tables.
CREATE TABLE metadata (
name VARCHAR(80) PRIMARY KEY,
value VARCHAR(255)
);
CREATE TABLE qcdata (
map_name VARCHAR(80) PRIMARY KEY,
count INTEGER NOT NULL
);
CREATE TABLE map_metadata (
map_name VARCHAR(80) NOT NULL,
source_name VARCHAR(80) NOT NULL,
source_url VARCHAR(255) NOT NULL,
source_date VARCHAR(20) NOT NULL
);
-- Explicit index creation on the referencing column of all the foreign keys.
-- Note that this is only needed for SQLite: PostgreSQL and MySQL create those
-- indexes automatically.
CREATE INDEX Faccessions ON accessions (id);
CREATE INDEX Falias ON alias (id);
CREATE INDEX Fchromosomes ON chromosomes (id);
CREATE INDEX Fchromosome_locations ON chromosome_locations (id);
CREATE INDEX Fcytogenetic_locations ON cytogenetic_locations (id);
CREATE INDEX Fec ON ec (id);
CREATE INDEX Fgo_bp ON go_bp (id);
CREATE INDEX Fgo_bp_all ON go_bp_all (id);
CREATE INDEX Fgo_cc ON go_cc (id);
CREATE INDEX Fgo_cc_all ON go_cc_all (id);
CREATE INDEX Fgo_mf ON go_mf (id);
CREATE INDEX Fgo_mf_all ON go_mf_all (id);
CREATE INDEX Fkegg ON kegg (id);
CREATE INDEX Fpfam ON pfam (id);
CREATE INDEX Fprosite ON prosite (id);
CREATE INDEX Fpubmed ON pubmed (id);
CREATE INDEX Frefseq ON refseq (id);
CREATE INDEX Funigene ON unigene (id);
|