/usr/share/stacks/sql/stacks.sql is in stacks-web 1.35-1ubuntu1.
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 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 | SET default_storage_engine=MYISAM;
create table batches (
id int unsigned not null primary key auto_increment,
date DATE not null,
description tinytext,
type enum('map', 'population')
);
create table samples (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
sample_id int unsigned not null,
type enum('parent', 'progeny', 'sample'),
file varchar(128),
pop_id varchar(32),
group_id varchar(32)
);
create table populations (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
pop_id int unsigned not null,
pop_name varchar(128)
);
create table sumstats (
batch_id int unsigned not null,
tag_id int unsigned not null,
chr varchar(32),
bp int unsigned default 0,
col int unsigned default 0,
pop_id varchar(32),
p_nuc char,
q_nuc char,
n int unsigned default 0,
p double,
obs_het double,
obs_hom double,
exp_het double,
exp_hom double,
pi double,
pi_s double,
pi_s_pval double,
fis double,
fis_s double,
fis_s_pval double,
private int unsigned default 0,
INDEX batch_id_index (batch_id),
INDEX tag_id_index (tag_id)
);
create table fst (
batch_id int unsigned not null,
tag_id int unsigned not null,
pop_id_1 varchar(32),
pop_id_2 varchar(32),
chr varchar(32),
bp int unsigned default 0,
col int unsigned default 0,
pi_o double,
fst double,
fishers_p double,
odds_ratio double,
ci_low double,
ci_high double,
lod double,
fst_c double,
fst_s double,
amova_fst double,
amova_fst_c double,
amova_fst_s double,
amova_fst_s_pval double,
snp_cnt int unsigned default 0,
INDEX batch_id_index (batch_id),
INDEX tag_id_index (tag_id)
);
create table hapstats (
batch_id int unsigned not null,
tag_id int unsigned not null,
chr varchar(32),
bp int unsigned default 0,
pop_id varchar(32),
n int unsigned default 0,
hapcnt double,
gene_div double,
gene_div_s double,
gene_div_pval double,
hap_div double,
hap_div_s double,
hap_div_pval double,
haplotypes tinytext,
INDEX batch_id_index (batch_id),
INDEX tag_id_index (tag_id)
);
create table phist (
batch_id int unsigned not null,
tag_id int unsigned not null,
pop_id_1 varchar(32),
pop_id_2 varchar(32),
chr varchar(32),
bp int unsigned default 0,
phist double,
phist_s double,
phist_pval double,
fpst double,
fpst_s double,
fpst_pval double,
dest double,
dest_s double,
dest_pval double,
INDEX batch_id_index (batch_id),
INDEX tag_id_index (tag_id)
);
create table catalog_tags (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
tag_id int unsigned not null,
chr varchar(32),
bp int unsigned default 0,
strand enum('+', '-'),
relationship enum('consensus', 'primary', 'secondary', 'tertiary'),
sub_id int unsigned not null,
merge_type tinytext,
seq text,
INDEX batch_id_index (batch_id),
INDEX tag_id_index (tag_id)
);
create table catalog_snps (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
tag_id int unsigned not null,
col int unsigned not null,
type enum('E', 'O', 'U'),
lratio float,
rank_1 char(1),
rank_2 char(1),
rank_3 char(1),
rank_4 char(1),
INDEX batch_index (batch_id),
INDEX tag_index (tag_id)
);
create table catalog_alleles (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
tag_id int unsigned not null,
allele varchar(32),
read_pct float,
read_cnt int unsigned,
INDEX batch_index (batch_id),
INDEX tag_index (tag_id)
);
create table catalog_genotypes (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
sample_id int unsigned not null,
genotype char(2),
INDEX batch_index (batch_id),
INDEX catalog_index (catalog_id),
INDEX sample_index (sample_id)
);
create table genotype_corrections (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
sample_id int unsigned not null,
genotype char(2),
INDEX cat_index (catalog_id),
INDEX batch_index (batch_id),
INDEX sample_index (sample_id)
);
create table catalog_annotations (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
external_id varchar(64),
INDEX batch_index (batch_id),
INDEX catalog_index (catalog_id),
INDEX external_index (external_id)
);
create table pileup (
id int unsigned not null primary key auto_increment,
sample_id int unsigned not null,
tag_id int unsigned not null,
chr varchar(32),
bp int unsigned default 0,
relationship enum('consensus', 'model', 'primary', 'secondary'),
sub_id int unsigned not null,
seq_id varchar(32),
seq text,
INDEX tag_id_index (tag_id),
INDEX sample_id_index (sample_id),
INDEX rel_index (relationship)
);
create table unique_tags (
id int unsigned not null primary key auto_increment,
sample_id int unsigned not null,
tag_id int unsigned not null,
chr varchar(32),
bp int unsigned default 0,
strand enum('+', '-'),
relationship enum('consensus', 'model', 'primary', 'secondary'),
sub_id int unsigned not null,
seq_id varchar(32),
seq text,
deleveraged bool default false,
blacklisted bool default false,
removed bool default false,
lnl float,
INDEX tag_id_index (tag_id),
INDEX sample_id_index (sample_id),
INDEX rel_index (relationship)
);
create table snps (
id int unsigned not null primary key auto_increment,
sample_id int unsigned not null,
tag_id int unsigned not null,
col int unsigned not null,
type enum('E', 'O', 'U'),
lratio float,
rank_1 char(1),
rank_2 char(1),
rank_3 char(1),
rank_4 char(1),
INDEX samp_index (sample_id),
INDEX tag_index (tag_id)
);
create table alleles (
id int unsigned not null primary key auto_increment,
sample_id int unsigned not null,
tag_id int unsigned not null,
allele varchar(32),
read_pct float,
read_cnt int unsigned,
INDEX samp_index (sample_id),
INDEX tag_index (tag_id)
);
create table matches (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
sample_id int unsigned not null,
tag_id int unsigned not null,
allele varchar(256),
depth int unsigned not null,
lnl float,
INDEX batch_id_index (batch_id),
INDEX catalog_id_index (catalog_id),
INDEX sample_id_index (sample_id),
INDEX tag_id_index (tag_id)
);
create table markers (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
type enum('aa/bb', 'aa/b-', '-a/bb', 'ab/--', '--/ab', 'aa/ab', 'ab/aa', 'ab/a-', '-a/ab', 'ab/ab', 'ab/ac', 'ab/cd', 'ab/cc', 'cc/ab', 'ab/c-', '-c/ab'),
progeny int unsigned not null default 0,
max_pct float,
ratio varchar(512),
chisq_pval float,
lnl float,
geno_map text,
uncor_type enum('aa/bb', 'aa/b-', '-a/bb', 'ab/--', '--/ab', 'aa/ab', 'ab/aa', 'ab/a-', '-a/ab', 'ab/ab', 'ab/ac', 'ab/cd', 'ab/cc', 'cc/ab', 'ab/c-', '-c/ab')
);
create table sequence (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
catalog_id int unsigned not null,
type enum('pe_radtag', 'est'),
seq_id varchar(64),
seq text,
INDEX catalog_id_index (catalog_id)
);
create table sequence_blast (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null default 0,
catalog_id int unsigned not null default 0,
seq_id int unsigned not null default 0,
algorithm enum('blastn', 'blastp', 'blastx', 'tblastn', 'tblastx'),
query_id varchar(64),
query_len int unsigned not null default 0,
hit_id varchar(128),
hit_len int unsigned not null default 0,
score double,
e_value double,
percent_ident double,
hsp_rank int unsigned not null default 0,
aln_len int unsigned not null default 0,
aln_homology_str text,
query_aln text,
query_aln_start int unsigned not null default 0,
query_aln_end int unsigned not null default 0,
hit_aln text,
hit_aln_start int unsigned not null default 0,
hit_aln_end int unsigned not null default 0
);
create table chr_index (
id int unsigned not null primary key auto_increment,
batch_id int unsigned not null,
chr varchar(32),
max_len int unsigned not null
);
create table ref_radome (
id int unsigned not null primary key,
chr varchar(32),
bp int unsigned not null,
strand enum('1', '-1'),
type enum('genomic', 'exon', 'intron'),
gene varchar(32),
ext_id varchar(32),
exon varchar(32),
ex_start int unsigned not null,
ex_end int unsigned not null,
ex_index int unsigned not null,
tran_cnt int unsigned not null,
dist int unsigned not null,
uniq bool default false,
tran varchar(32)
);
|