WashU Epigenome Browser

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

--                  --

--  Pvulgaris_218

--                  --

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

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

  defaultCustomtracks text 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/Pvulgaris_218/",

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

\N,

"Sample",

\N,

\N,

"Chr01,142266,Chr01,501441",

"mock",

"gene,gc5Base",

"Chr01,Chr02,Chr03,Chr04,Chr05,Chr06,Chr07,Chr08,Chr09,Chr10,Chr11",

\N,\N,

true,

true,

"Pvulgaris",

"Assembly version|Pvulgaris_218|Sequence source|<a href=ftp://ftp.jgi-psf.org/pub/compgen/phytozome/v9.0/Pvulgaris/ target=_blank>phytozome</a>|Date parsed|August 22, 2013|Chromosomes|11|Misc|697|Total bases|521,076,998|Logo art|<a href=http://systemsbiology.usm.edu/Legume/common_bean.html 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");

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;

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;

load data local infile 'track2Detail_decor' 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 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 track2VersionInfo;

create table track2VersionInfo (

  name varchar(255) not null primary key,

  info varchar(255) not null

);

load data local infile 'track2VersionInfo' 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" 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

);

/*

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;

*/

drop table if exists genestruct;

create table genestruct (

id int unsigned not null primary key,

chrom varchar(255) not null,

strand char(1) not null,

txStart int unsigned not null,

txEnd int unsigned not null,

cdsStart int unsigned not null,

cdsEnd int unsigned not null,

exonCount int unsigned not null,

exonStarts text not null,

exonEnds text not null,

name varchar(255) not null

);

load data local infile 'geneStruct' into table genestruct;

drop table if exists genesymbol;

create table genesymbol (

name varchar(255) not null,

symbol varchar(255) null,

description text null,

id int unsigned not null primary key,

index(name)

);

load data local infile 'geneSymbol' into table genesymbol;