WashU Epigenome Browser

-- -------------------

--                  --

--    mm9heatmap    --

--                  --

-- -------------------

drop table if exists config;

create table config (

  bbiPath text not null,

  seqPath text null,

  defaultTracks text 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 null,

  ideogram_wiggle1 varchar(255) null,

  ideogram_wiggle2 varchar(255) null,

  hasGene boolean not null,

  allowJuxtaposition boolean not null,

  keggSpeciesCode varchar(255) not null,

  information text not null,

  runmode tinyint not null,

  initmatplot boolean not null

);

insert into config values(

"/srv/epgg/data/data/subtleKnife/mm9/",

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

\N,

-- "GSM929716_1,GSM915181_1,GSM970852_6,GSM900187_4,GSM900195_3,GSM970851_1,GSM915163_1,GSM912935_1,GSM946530_2,GSM915160_1,GSM918746_1,GSM918744_1,GSM918712_1,GSM970874_3,GSM915164_1,GSM923589_1,GSM923584_2",

"Sample,Assay,Institution",

"CYP4Z1\\nCYP2A7\\nCYP2A6\\nCYP3A4\\nCYP1A1\\nCYP4V2\\nCYP51A1\\nCYP2C19\\nCYP26B1\\nCYP11B2\\nCYP24A1\\nCYP4B1\\nCYP2C8",

"3,http://vizhub.wustl.edu/hubSample/mm9/wgEncodeLicrHistoneBatInputMAdult24wksC57bl6StdSig.gz,1,http://vizhub.wustl.edu/hubSample/mm9/bed.gz,5,http://vizhub.wustl.edu/hubSample/mm9/wgEncodeLicrHistoneBatInputMAdult24wksC57bl6StdAlnRep2.gz,100,http://vizhub.wustl.edu/hubSample/mm9/hub.txt",

"chr6,51999773,chr6,52368420",

"longrange,encode",

"knownGene,rmsk_ensemble",

"chr1,chr2,chr3,chr4,chr5,chr6,chr7,chr8,chr9,chr10,chr11,chr12,chr13,chr14,chr15,chr16,chr17,chr18,chr19,chrX,chrY,chrM",

\N,\N,

true,

true,

"mmu",

"Assembly version|mm9|Sequence source|<a href=http://hgdownload.cse.ucsc.edu/goldenPath/mm9/bigZips/ target=_blank>UCSC browser</a>|Date parsed|August 1, 2011|Chromosomes|22|Misc|13|Total bases|3,137,144,693|Logo art|<a href=http://free-extras.com/images/mouse-8552.htm 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");

insert into gfGrouping values (7, "Mappability (ENCODE)");

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('ccdsGene','CCDS genes',\N,2,0,1,'http://www.ncbi.nlm.nih.gov/CCDS/CcdsBrowse.cgi?REQUEST=CCDS&BUILDS=ALLBUILDS&DATA=');

insert into decorInfo values('ccdsGeneexons','exons (CCDS genes)','ccdsGene',2,0,0,\N);

insert into decorInfo values('ccdsGeneintrons','introns (CCDS genes)','ccdsGene',2,0,0,\N);

insert into decorInfo values('vegaGene','Vega genes',\N,2,0,1,'http://vega.sanger.ac.uk/Mus_musculus/geneview?gene=');

insert into decorInfo values('vegaGenepromoter','promoters (Vega genes)','vegaGene',2,0,0,\N);

insert into decorInfo values('vegaGeneutr3',"3' UTRs (Vega genes)",'vegaGene',2,0,0,\N);

insert into decorInfo values('vegaGeneutr5',"5' UTRs (Vega genes)",'vegaGene',2,0,0,\N);

insert into decorInfo values('vegaGeneexons','exons (Vega genes)','vegaGene',2,0,0,\N);

insert into decorInfo values('vegaGeneintrons','introns (Vega genes)','vegaGene',2,0,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_encode' into table track2Label;

load data local infile 'track2Label_longrange' 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_encode' into table track2ProcessInfo;

load data local infile 'track2ProcessInfo_longrange' 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_decor' into table track2Detail;

load data local infile 'track2Detail_encode' into table track2Detail;

load data local infile 'track2Detail_longrange' 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_encode' into table track2GEO;

load data local infile 'track2GEO_longrange' 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_encode' 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_encode" into table track2Annotation;

load data local infile "track2Annotation_longrange" 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_encode" into table track2Ft;

load data local infile "track2Ft_longrange" into table track2Ft;

load data local infile "track2Ft_mock" into table track2Ft;

drop table if exists track2Categorical;

create table track2Categorical (

  name varchar(255) not null primary key,

  info text not null

);

load data local infile 'track2Categorical' into table track2Categorical;

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;

load data local infile "track2Style_encode" 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 longrange;

create table longrange (

  tkname varchar(255) not null

);

load data local infile "longrange" into table longrange;

drop table if exists encode;

create table encode (

  tkname varchar(255) not null

);

load data local infile "encode" into table encode;

/* drop table if exists geocollection;

create table geocollection (

  tkname varchar(255) not null

);

load data local infile "geocollection" into table geocollection;

*/

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

);

load data local infile "cytoband" into table cytoband;

/*

DROP TABLE IF EXISTS `rmsk`;

CREATE TABLE `rmsk` (

  `bin` smallint(5) unsigned NOT NULL default '0',

  `swScore` int(10) unsigned NOT NULL default '0',

  `milliDiv` int(10) unsigned NOT NULL default '0',

  `milliDel` int(10) unsigned NOT NULL default '0',

  `milliIns` int(10) unsigned NOT NULL default '0',

  `genoName` varchar(255) NOT NULL default '',

  `genoStart` int(10) unsigned NOT NULL default '0',

  `genoEnd` int(10) unsigned NOT NULL default '0',

  `genoLeft` int(11) NOT NULL default '0',

  `strand` char(1) NOT NULL default '',

  `repName` varchar(255) NOT NULL default '',

  `repClass` varchar(255) NOT NULL default '',

  `repFamily` varchar(255) NOT NULL default '',

  `repStart` int(11) NOT NULL default '0',

  `repEnd` int(11) NOT NULL default '0',

  `repLeft` int(11) NOT NULL default '0',

  `id` char(1) NOT NULL default '',

  KEY `genoName` (`genoName`(14),`bin`)

);

load data local infile 'rmsk.txt' into table rmsk;

*/