WashU Epigenome Browser

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

--                  --

--    hg19heatmap   --

--                  --

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

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 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/hg19/",

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

"GSM959044_1,GSM521901,GSM469970,GSM521897,GSM521895,GSM521913,GSM469974,GSM521889,GSM469968,E12_25_bothAc_dense",

"Assay,Sample,Institution",

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

"3,http://vizhub.wustl.edu/hubSample/hg19/qual3.gz,1,http://vizhub.wustl.edu/hubSample/hg19/bed.gz,10,http://vizhub.wustl.edu/hubSample/hg19/K562POL2.gz,15,http://vizhub.wustl.edu/hubSample/hg19/sample.bigWig,100,http://vizhub.wustl.edu/hubSample/hg19/hub.json,18,http://vizhub.wustl.edu/hubSample/hg19/bam1.bam",

"chr7,27053398,chr7,27373766",

"roadmapepigenome,longrange",

"refGene,rmsk_ensemble",

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

"knownGene,0",

\N,

true,

true,

"hsa",

"Assembly version|hg19|Sequence source|<a href=http://hgdownload.cse.ucsc.edu/goldenPath/hg19/bigZips/ target=_blank>UCSC browser</a>|Date parsed|June 1, 2011|Chromosomes|25|Contigs & misc|68|Total bases|3,137,144,693|Logo art|<a href=http://turing.iimas.unam.mx/~cgg/gallery/EverybodysHive.html target=_blank>link</a>",

0,

false

);

-- grouping types on genomic features

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, "G/C related");

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

-- for genomic features that can be used as horizontal axis

-- each type of gf (except genome) will have a corresponding covering table

/* 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"

   ambiguity with hasStruct field:

   if it is set to true, it indicates existance of both [x]symbol and [x]struct tables

   so currently it only works for gene model track (must belong to gene group)

   where generic genomic feature track wouldn't necessarily require [x]symbol table (might not be big trouble?)

*/

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, /* value must be 0/1, must not be boolean */

  queryUrl varchar(255) null

);

load data local infile 'decorInfo' into table decorInfo;

-- insert into decorInfo values ('wgRna',"sno/miRNA",\N, 5, 0, 0,\N);

/*

insert into decorInfo values('decorTrackgrp7', 'no name', \N, 7, 11, 0, \N);

drop table if exists decorTrackselectiongrid;

create table decorTrackselectiongrid (

  groupname varchar(255) not null primary key,

  row_terms varchar(255) not null,

  col_terms varchar(255) not null

);

insert into decorTrackselectiongrid values('decorTrackgrp7','14018,14005,13047,14001,14028,11310,11101,13076','27001,27002,27003');

*/

drop table if exists track2Label;

create table track2Label (

  name varchar(255) not null primary key,

  label text null

);

load data local infile 'track2Label_roadmap' into table track2Label;

load data local infile 'track2Label_encode' into table track2Label;

load data local infile 'track2Label_longrange' into table track2Label;

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

load data local infile 'track2ProcessInfo_encode' 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_roadmap" into table track2BamInfo;

load data local infile "track2BamInfo_encode" into table track2BamInfo;

load data local infile "track2BamInfo_test" 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_roadmap' 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_roadmap' into table track2GEO;

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_roadmap' into table track2VersionInfo;

load data local infile 'track2VersionInfo_encode' into table track2VersionInfo;

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_roadmap" into table track2Annotation;

load data local infile "track2Annotation_encode" into table track2Annotation;

load data local infile "track2Annotation_longrange" into table track2Annotation;

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

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_roadmap' 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

 );

 load data local infile 'track2Regions' into table track2Regions;

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 roadmapepigenome;

create table roadmapepigenome (

  tkname varchar(255) not null

);

load data local infile "roadmap" into table roadmapepigenome;

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 mock;

create table mock (

  tkname varchar(255) not null

);

load data local infile "mock" into table mock;

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 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;

*/