WashU Epigenome Browser

drop table if exists config;

create table config (

  bbiPath text not null,

  seqPath text null,

  defaultTracks text not null,

  defaultMdcategory varchar(255) not null,

  defaultGenelist text not null,

  defaultCustomtracks text not null,

  defaultPosition varchar(255) not null,

  defaultDataset varchar(255) not null,

  defaultDecor text null,

  defaultScaffold text not null,

  ideogram_wiggle1 varchar(255) null,

  ideogram_wiggle2 varchar(255) null,

  hasGene boolean not null,

  allowJuxtaposition boolean not null,

  keggSpeciesCode varchar(255) null,

  information text not null,

  runmode tinyint not null,

  initmatplot boolean not null

);

insert into config values(

"/srv/epgg/data/data/subtleKnife/spombe201203/",

"/srv/epgg/data/data/subtleKnife/seq/spombe201203.gz",

"mock1,mock2,mock3,mock4,mock5,mock6,mock7,mock8,mock9,mock10,mock11,mock12,mock13,mock14,mock15,mock16,mock17,mock18,mock19,mock20", 

"Sample,mock term",

"no gene",

"3,http://vizhub.wustl.edu/hubSample/spombe201203/rand.gz,15,http://vizhub.wustl.edu/hubSample/spombe201203/rand1.bigWig,1,http://vizhub.wustl.edu/hubSample/spombe201203/bed.gz,100,http://vizhub.wustl.edu/hubSample/spombe201203/hub.txt",

"chromosome3,322500,chromosome3,353700",

"mock",

"pombase_gene,repeat",

"chromosome1,chromosome2,chromosome3,mating_type_region,Spmit,telomeric_contig",

\N,\N,

true,

true,

"spo",

"Assembly release|March 2012|Sequence source|<a href=http://www.pombase.org target=_blank>PomBase</a>|Date parsed|November 6, 2012|Chromosomes|3|Total bases|12,631,379|Logo art|<a href=http://cwp.embo.org/pc09-17/ target=_blank>link</a>",

0,

false

);

-- grouping types on genomic features

-- table name defined in macro: TBN_GF_GRP

drop table if exists gfGrouping;

create table gfGrouping (

  id TINYINT not null primary key,

  name char(50) not null

);

insert into gfGrouping values (2, "Genes");

-- insert into gfGrouping values (3, "non-coding RNA");

-- insert into gfGrouping values (4, "RepeatMasker");

-- insert into gfGrouping values (6, "Sequence conservation");

insert into gfGrouping values (5, "Others");

-- for stuff that can be plotted as decorative tracks (no genome)

-- the grp is also from gfGrouping table

-- filetype: 0: server bigBed, 2: server bigWig

-- name will be used to compose bbi file "name.bigBed"

drop table if exists decorInfo;

create table decorInfo (

  name char(50) not null primary key,

  printname char(100) not null,

  parent char(50) null,

  grp tinyint not null,

  fileType tinyint not null,

  hasStruct tinyint null,

  queryUrl varchar(255) null

);

load data local infile 'decorInfo' into table decorInfo;

/*

insert into decorInfo values('pombase_gene','PomBase gene',\N,2,0,1,'http://genomebrowser.pombase.org/Schizosaccharomyces_pombe/Transcript/Summary?db=core;t=');

insert into decorInfo values('pombase_genepromoter','promoters (PomBase genes)','pombase_gene',2,0,0,\N);

insert into decorInfo values('pombase_geneutr3',"3' UTRs (PomBase genes)",'pombase_gene',2,0,0,\N);

insert into decorInfo values('pombase_geneutr5',"5' UTRs (PomBase genes)",'pombase_gene',2,0,0,\N);

insert into decorInfo values('pombase_geneexons','exons (PomBase genes)','pombase_gene',2,0,0,\N);

insert into decorInfo values('pombase_geneintrons','introns (PomBase genes)','pombase_gene',2,0,0,\N);

insert into decorInfo values('repeat','LTR and repeats',\N,5,0,0,\N);

insert into decorInfo values('gc5Base','GC percent',\N,5,8,0,\N);

*/

drop table if exists track2Label;

create table track2Label (

  name varchar(255) not null primary key,

  label text null

);

load data local infile 'track2Label' into table track2Label;

drop table if exists track2ProcessInfo;

create table track2ProcessInfo (

  name varchar(255) not null primary key,

  detail text null

);

load data local infile 'track2ProcessInfo' into table track2ProcessInfo;

drop table if exists track2BamInfo;

create table track2BamInfo (

  name varchar(255) not null,

  bamfile varchar(255) not null,

  bamfilelabel varchar(255) not null

);

load data local infile "track2BamInfo" into table track2BamInfo;

drop table if exists track2Detail;

create table track2Detail (

  name varchar(255) not null primary key,

  detail text null

);

load data local infile 'track2Detail' into table track2Detail;

drop table if exists track2GEO;

create table track2GEO (

  name varchar(255) not null primary key,

  geo char(20) not null

);

load data local infile 'track2GEO' into table track2GEO;

drop table if exists track2VersionInfo;

create table track2VersionInfo (

  name varchar(255) not null primary key,

  info varchar(255) not null

);

load data local infile 'track2VersionInfo' into table track2VersionInfo;

-- new trackDetail end here

drop table if exists track2Annotation;

create table track2Annotation (

  name varchar(255) not null primary key,

  attridx varchar(255) not null

);

load data local infile "track2Annotation" into table track2Annotation;

drop table if exists track2Ft;

create table track2Ft (

  name varchar(255) not null primary key,

  ft tinyint not null

);

load data local infile "track2Ft" into table track2Ft;

drop table if exists track2Style;

create table track2Style (

  name varchar(255) not null primary key,

  style text not null

);

load data local infile "track2Style" into table track2Style;

drop table if exists track2Regions;

create table track2Regions (

  name varchar(255) not null primary key,

    regionname varchar(255) not null,

          regions text not null

           );

drop table if exists metadataVocabulary;

create table metadataVocabulary (

  child varchar(255) not null,

  parent varchar(255) not null

);

load data local infile "metadataVocabulary" into table metadataVocabulary;

drop table if exists trackAttr2idx;

create table trackAttr2idx (

  idx varchar(255) not null primary key,

  attr varchar(255) not null,

  note varchar(255) null,

  description text null

);

load data local infile "trackAttr2idx" into table trackAttr2idx;

drop table if exists tempURL;

create table tempURL (

  session varchar(100) not null,

  offset INT unsigned not null,

  urlpiece text not null

);

drop table if exists dataset;

create table dataset (

  tablename varchar(255) not null,

  logo varchar(255) null,

  name varchar(255) not null,

  url varchar(255) null,

  description text not null

);

load data local infile "dataset" into table dataset;

drop table if exists mock;

create table mock (

  tkname varchar(255) not null

);

load data local infile "mock" into table mock;

drop table if exists scaffoldInfo;

create table scaffoldInfo (

  parent varchar(255) not null,

  child varchar(255) not null,

  childLength int unsigned not null

);

load data local infile "scaffoldInfo" into table scaffoldInfo;

drop table if exists cytoband;

create table cytoband (

  id int null auto_increment primary key,

  chrom char(20) not null,

  start int not null,

  stop int not null,

  name char(20) not null,

  colorIdx int not null

);