As promised in the previous post, in this one, I will show how can one “hack” into the UCSC Genome Browser (local) MySQL database in order to create permanent tracks (annotation, signal or other). One way is of course the track hubs, but they are not permanent in the sense that the user has to load them at least once before start working.
The concept is quite simple and follows the strategy that UCSC programmers use to host ENCODE tracks. In fact, all ENCODE signal tracks are not separate co-ordinate tables in the database structure but they are external bigWig and/or .bam files, stored somewhere (genome) specific in the /gbdb directory. The relative path is then stored in a table in the genome browser database. Please see this post for an explanation of the genome browser directory structure.
So before reading and executing the following MySQL script, you should first create the signal or annotation files. I am going to use the same annotation tracks described in this post, so make sure you have created the .bam files first by reading the post.
We have to touch 2 tables and create as many new ones as our tracks, so the steps are summarized below:
- Edit the grp table of the relative genome, so as to create a new display group for our tracks (like the default groups, “Mapping and Sequencing Tracks”, “Comparative Genomics” etc.
- Edit the trackDb table of the relative genome so as to add information about our custom tracks (format, colours and other attirbutes, see here).
- Create n new tables storing the signal/annotation file path, where n the number of tracks we want to add.
Here is an SQL script to serve as a template for your case. It uses the lincRNA annotations described here.
/* hg19 *****/
INSERT INTO `hg19`.`grp` (`name`,`label`,`priority`,`defaultIsClosed`)
VALUES ('lincRNALocal','lincRNAs @Fleming','3.1','0');
CREATE TABLE `hg19`.`ensemblHg19LincRNAsGRCh3770` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg19`.`broadHg19LincRNAs` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg19`.`broadHg19LincRNAsStrict` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg19`.`noncodeLincRNAsHg19` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
INSERT INTO `hg19`.`ensemblHg19LincRNAsGRCh3770` (`fileName`)
VALUES ('/your/track/storage/path//hg19/ensembl.hg19.lincRNAs.GRCh37.70.bam');
INSERT INTO `hg19`.`broadHg19LincRNAs` (`fileName`)
VALUES ('/your/track/storage/path//hg19/broad.hg19.lincRNAs.bam');
INSERT INTO `hg19`.`broadHg19LincRNAsStrict` (`fileName`)
VALUES ('/your/track/storage/path//hg19/broad.hg19.lincRNAs.stringent.bam');
INSERT INTO `hg19`.`noncodeLincRNAsHg19` (`fileName`)
VALUES ('/your/track/storage/path//hg19/noncode.lincRNAs.hg19.bam');
INSERT INTO `hg19`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'ensemblHg19LincRNAsGRCh3770',
'Ensembl lincRNAs',
'bam',
'Ensembl GRCh37 (hg19) lincRNAs',
'3',
'1',
'0',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg19/ensembl.hg19.lincRNAs.GRCh37.70.txt'
);
INSERT INTO `hg19`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'broadHg19LincRNAs',
'Broad lincRNAs',
'bam',
'Broad Institute hg19 lincRNAs',
'3',
'1',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg19/broad.hg19.lincRNAs.txt'
);
INSERT INTO `hg19`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'broadHg19LincRNAsStrict',
'Broad lincRNAs strict',
'bam',
'Broad Institute hg19 lincRNAs strict thresholds',
'3',
'1',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg19/broad.hg19.lincRNAs.stringent.txt'
);
INSERT INTO `hg19`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'noncodeLincRNAsHg19',
' NONCODE lincRNAs',
'bam',
'NONCODE project hg19 lincRNAs',
'3',
'1',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg19/noncode.lincRNAs.hg19.txt'
);
/* hg18 *****/
INSERT INTO `hg18`.`grp` (`name`,`label`,`priority`,`defaultIsClosed`)
VALUES ('lincRNALocal','lincRNAs @Fleming','3.1','0');
CREATE TABLE `hg18`.`ensemblHg18LincRNAsLiftOver` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg18`.`broadHg18LincRNAs` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg18`.`broadHg18LincRNAsStrict` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `hg18`.`noncodeLincRNAsHg18` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
INSERT INTO `hg18`.`ensemblHg18LincRNAsLiftOver` (`fileName`)
VALUES ('/your/track/storage/path//hg18/ensembl.hg18.lincRNAs.liftOver.bam');
INSERT INTO `hg18`.`broadHg18LincRNAs` (`fileName`)
VALUES ('/your/track/storage/path//hg18/broad.hg18.lincRNAs.bam');
INSERT INTO `hg18`.`broadHg18LincRNAsStrict` (`fileName`)
VALUES ('/your/track/storage/path//hg18/broad.hg18.lincRNAs.stringent.bam');
INSERT INTO `hg18`.`noncodeLincRNAsHg18` (`fileName`)
VALUES ('/your/track/storage/path//hg18/noncode.lincRNAs.hg18.bam');
INSERT INTO `hg18`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'ensemblHg18LincRNAsLiftOver',
'Ensembl lincRNAs',
'bam',
'Ensembl NCBI36 (hg18) lincRNAs',
'3',
'1',
'0',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg18/ensembl.hg18.lincRNAs.liftOver.txt'
);
INSERT INTO `hg18`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'broadHg18LincRNAs',
'Broad lincRNAs',
'bam',
'Broad Institute hg18 lincRNAs',
'3',
'1',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg18/broad.hg18.lincRNAs.txt'
);
INSERT INTO `hg18`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'broadHg18LincRNAsStrict',
'Broad lincRNAs strict',
'bam',
'Broad Institute hg18 lincRNAs strict thresholds',
'3',
'1',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg18/broad.hg18.lincRNAs.stringent.txt'
);
INSERT INTO `hg18`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'noncodeLincRNAsHg18',
' NONCODE lincRNAs',
'bam',
'NONCODE project hg18 lincRNAs',
'3',
'1',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//hg18/noncode.lincRNAs.hg18.txt'
);
/* mm10 *****/
INSERT INTO `mm10`.`grp` (`name`,`label`,`priority`,`defaultIsClosed`)
VALUES ('lincRNALocal','lincRNAs @Fleming','3.1','0');
CREATE TABLE `mm10`.`ensemblMm10LincRNAsGRCm3870` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `mm10`.`noncodeLincRNAsMm10` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
INSERT INTO `mm10`.`ensemblMm10LincRNAsGRCm3870` (`fileName`)
VALUES ('/your/track/storage/path//mm10/ensembl.mm10.lincRNAs.GRCm38.70.bam');
INSERT INTO `mm10`.`noncodeLincRNAsMm10` (`fileName`)
VALUES ('/your/track/storage/path//mm10/noncode.lincRNAs.mm10.bam');
INSERT INTO `mm10`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'ensemblMm10LincRNAsGRCm3870',
'Ensembl lincRNAs',
'bam',
'Ensembl GRCm38 (mm10) lincRNAs',
'3',
'1',
'0',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//mm10/ensembl.mm10.lincRNAs.GRCm38.70.txt'
);
INSERT INTO `mm10`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'noncodeLincRNAsMm10',
'NONCODE lincRNAs',
'bam',
'NONCODE lincRNAs',
'3',
'1',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//mm10/noncode.mm10.lincRNAs.txt'
);
/* mm9 **********************************************************************************/
INSERT INTO `mm9`.`grp` (`name`,`label`,`priority`)
VALUES ('lincRNALocal','lincRNAs @Fleming','3.1');
CREATE TABLE `mm9`.`ensemblMm9LincRNAsLiftOver` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
CREATE TABLE `mm9`.`noncodeLincRNAsMm9` (`fileName` VARCHAR(255) NOT NULL) ENGINE = MyISAM;
INSERT INTO `mm9`.`ensemblMm9LincRNAsLiftOver` (`fileName`)
VALUES ('/your/track/storage/path//mm9/ensembl.mm9.lincRNAs.liftOver.bam');
INSERT INTO `mm9`.`noncodeLincRNAsMm9` (`fileName`)
VALUES ('/your/track/storage/path//mm9/noncode.lincRNAs.mm9.bam');
INSERT INTO `mm9`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'ensemblMm9LincRNAsLiftOver',
'Ensembl lincRNAs',
'bam',
'Ensembl NVBI37 (mm9) lincRNAs',
'3',
'1',
'0',
'0',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//mm9/ensembl.mm9.lincRNAs.liftOver.txt'
);
INSERT INTO `mm9`.`trackDb` (
`tableName`,
`shortLabel`,
`type`,
`longLabel`,
`visibility`,
`priority`,
`colorR`,
`colorG`,
`colorB`,
`altColorR`,
`altColorG`,
`altColorB`,
`useScore`,
`private`,
`restrictCount`,
`grp`,
`canPack`,
`settings`
)
VALUES (
'noncodeLincRNAsMm9',
'NONCODE lincRNAs',
'bam',
'NONCODE lincRNAs',
'3',
'1',
'192',
'0',
'0',
'192',
'0',
'0',
'0',
'0',
'0',
'lincRNALocal',
'1',
'/your/track/storage/path//mm9/noncode.mm9.lincRNAs.txt'
);