# cdw.sql was originally generated by the autoSql program, which also 
# generated cdw.c and cdw.h.  This creates the database representation of
# an object which can be loaded and saved from RAM in a fairly 
# automatic way.

#Settings used to configure warehouse
CREATE TABLE cdwSettings (
    id int unsigned auto_increment,	# Settings ID
    name varchar(255) default '',	# Settings name, can't be reused
    val varchar(255) default '',	# Settings value, some undefined but not huge thing
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name),
    INDEX(val)
);

#Someone who submits files to or otherwise interacts with big data warehouse
CREATE TABLE cdwUser (
    id int unsigned auto_increment,	# Autoincremented user ID
    email varchar(255) default '',	# Email address - required
    uuid char(37) default '',	# Help to synchronize us with Stanford.
    isAdmin tinyint default 0,	# If true the use can modify other people's files too.
    primaryGroup int unsigned default 0,	# If this is non-zero then we'll make files with this group association.
              #Indices
    PRIMARY KEY(id),
    UNIQUE(email),
    INDEX(uuid)
);

#A group in the access control sense
CREATE TABLE cdwGroup (
    id int unsigned auto_increment,	# Autoincremented user ID
    name varchar(255) default '',	# Symbolic name for group, should follow rules of a lowercase C symbol.
    description longblob,	# Description of group
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name)
);

#Association table between cdwFile and cdwGroup
CREATE TABLE cdwGroupFile (
    fileId int unsigned default 0,	# What is the file
    groupId int unsigned default 0,	# What is the group
              #Indices
    INDEX(fileId)
);

#Association table between cdwGroup and cdwUser
CREATE TABLE cdwGroupUser (
    userId int unsigned default 0,	# What is the user
    groupId int unsigned default 0,	# What is the group
              #Indices
    INDEX(userId),
    INDEX(groupId)
);

#A contributing lab
CREATE TABLE cdwLab (
    id int unsigned auto_increment,	# Autoincremented user ID
    name varchar(255) default '',	# Shorthand name for lab, all lower case
    pi varchar(255) default '',	# Principle investigator responsible for lab
    institution varchar(255) default '',	# University or other institution hosting lab
    url varchar(255) default '',	# URL of lab page
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name)
);

#A script that is authorized to submit on behalf of a user
CREATE TABLE cdwScriptRegistry (
    id int unsigned auto_increment,	# Autoincremented script ID
    userId int unsigned default 0,	# Associated user
    name varchar(255) default '',	# Script name - unique in system and autogenerated
    description longblob,	# Script description
    secretHash varchar(255) default '',	# Hashed script password
    submitCount int default 0,	# Number of submissions attempted
              #Indices
    PRIMARY KEY(id),
    INDEX(userId),
    UNIQUE(name)
);

#A web host we have collected files from - something like www.ncbi.nlm.gov or google.com
CREATE TABLE cdwHost (
    id int unsigned auto_increment,	# Autoincremented host id
    name varchar(255) default '',	# Name (before DNS lookup)
    lastOkTime bigint default 0,	# Last time host was ok in seconds since 1970
    lastNotOkTime bigint default 0,	# Last time host was not ok in seconds since 1970
    firstAdded bigint default 0,	# Time host was first seen
    errorMessage longblob,	# If non-empty contains last error message from host. If empty host is ok
    openSuccesses bigint default 0,	# Number of times files have been opened ok from this host
    openFails bigint default 0,	# Number of times files have failed to open from this host
    historyBits bigint default 0,	# Open history with most recent in least significant bit. 0 for connection failed, 1 for success
    paraFetchStreams int default 0,	# Number of open streams for paraFetch command.  10 for most places, 30 for Barcelona
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name)
);

#An external data directory we have collected a submit from
CREATE TABLE cdwSubmitDir (
    id int unsigned auto_increment,	# Autoincremented id
    url longblob,	# Web-mounted directory. Includes protocol, host, and final '/'
    hostId int unsigned default 0,	# Id of host it's on
    lastOkTime bigint default 0,	# Last time submit dir was ok in seconds since 1970
    lastNotOkTime bigint default 0,	# Last time submit dir was not ok in seconds since 1970
    firstAdded bigint default 0,	# Time submit dir was first seen
    errorMessage longblob,	# If non-empty contains last error message from dir. If empty dir is ok
    openSuccesses bigint default 0,	# Number of times files have been opened ok from this dir
    openFails bigint default 0,	# Number of times files have failed to open from this dir
    historyBits bigint default 0,	# Open history with most recent in least significant bit. 0 for upload failed, 1 for success
              #Indices
    PRIMARY KEY(id),
    INDEX(url(64)),
    INDEX(hostId)
);

#Where we keep expanded metadata tags for each file, though many share.
CREATE TABLE cdwMetaTags (
    id int unsigned auto_increment,	# Autoincrementing table id
    md5 char(32) default '',	# md5 sum of tags string
    tags longblob,	# CGI encoded name=val pairs from manifest
              #Indices
    PRIMARY KEY(id),
    INDEX(md5)
);

#A file we are tracking that we intend to and maybe have uploaded
CREATE TABLE cdwFile (
    id int unsigned auto_increment,	# Autoincrementing file id
    submitId int unsigned default 0,	# Links to id in submit table
    submitDirId int unsigned default 0,	# Links to id in submitDir table
    userId int unsigned default 0,	# Id in user table of file owner
    submitFileName longblob,	# File name in submit relative to submit dir
    cdwFileName longblob,	# File name in big data warehouse relative to cdw root dir
    startUploadTime bigint default 0,	# Time when upload started - 0 if not started
    endUploadTime bigint default 0,	# Time when upload finished - 0 if not finished
    updateTime bigint default 0,	# Update time (on system it was uploaded from)
    size bigint default 0,	# File size in manifest
    md5 char(32) default '',	# md5 sum of file contents
    tags longblob,	# CGI encoded name=val pairs from manifest
    metaTagsId int unsigned default 0,	# ID of associated metadata tags
    errorMessage longblob,	# If non-empty contains last error message from upload. If empty upload is ok
    deprecated varchar(255) default '',	# If non-empty why you shouldn't use this file any more.
    replacedBy int unsigned default 0,	# If non-zero id of file that replaces this one.
    userAccess tinyint default 0,	# 0 - no, 1 - read, 2 - read/write
    groupAccess tinyint default 0,	# 0 - no, 1 - read, 2 - read/write
    allAccess tinyint default 0,	# 0 - no, 1 - read, 2 - read/write
              #Indices
    PRIMARY KEY(id),
    INDEX(submitId),
    INDEX(submitDirId),
    INDEX(userId),
    INDEX(submitFileName(64)),
    INDEX(cdwFileName(32)),
    INDEX(md5)
);

#A data submit, typically containing many files.  Always associated with a submit dir.
CREATE TABLE cdwSubmit (
    id int unsigned auto_increment,	# Autoincremented submit id
    url longblob,	# Url to validated.txt format file. We copy this file over and give it a fileId if we can.
    startUploadTime bigint default 0,	# Time at start of submit
    endUploadTime bigint default 0,	# Time at end of upload - 0 if not finished
    userId int unsigned default 0,	# Connects to user table id field
    manifestFileId int unsigned default 0,	# Points to metadata.txt file for submit.
    metaFileId int unsigned default 0,	# Points to meta.txt file for submit
    submitDirId int unsigned default 0,	# Points to the submitDir
    fileCount int unsigned default 0,	# Number of files that will be in submit if it were complete.
    oldFiles int unsigned default 0,	# Number of files in submission that were already in warehouse.
    newFiles int unsigned default 0,	# Number of files in submission that are newly uploaded.
    byteCount bigint default 0,	# Total bytes in submission including old and new
    oldBytes bigint default 0,	# Bytes in old files.
    newBytes bigint default 0,	# Bytes in new files (so far).
    errorMessage longblob,	# If non-empty contains last error message. If empty submit is ok
    fileIdInTransit int unsigned default 0,	# cdwFile.id of file currently being transferred or zero
    metaChangeCount int unsigned default 0,	# Number of files where metadata changed by submission
    wrangler longblob,	# The UNIX ID of the person who ran cdwSubmit.
              #Indices
    PRIMARY KEY(id),
    INDEX(url(32)),
    INDEX(userId),
    INDEX(manifestFileId),
    INDEX(metaFileId),
    INDEX(submitDirId)
);

#Subscribers can have programs that are called at various points during data submission
CREATE TABLE cdwSubscriber (
    id int unsigned auto_increment,	# ID of subscriber
    name varchar(255) default '',	# Name of subscriber
    runOrder double default 0,	# Determines order subscribers run in. In case of tie lowest id wins.
    filePattern varchar(255) default '',	# A string with * and ? wildcards to match files we care about
    dirPattern varchar(255) default '',	# A string with * and ? wildcards to match hub dir URLs we care about
    tagPattern longblob,	# A cgi-encoded string of tag=wildcard pairs.
    onFileEndUpload varchar(255) default '',	# A unix command string to run with a %u where file id goes
              #Indices
    PRIMARY KEY(id)
);

#An assembly - includes reference to a two bit file, and a little name and summary info.
CREATE TABLE cdwAssembly (
    id int unsigned auto_increment,	# Assembly ID
    taxon int unsigned default 0,	# NCBI taxon number
    name varchar(255) default '',	# Some human readable name to distinguish this from other collections of DNA
    ucscDb varchar(255) default '',	# Which UCSC database (mm9?  hg19?) associated with it.
    twoBitId int unsigned default 0,	# File ID of associated twoBit file
    baseCount bigint default 0,	# Count of bases including N's
    realBaseCount bigint default 0,	# Count of non-N bases in assembly
    seqCount int unsigned default 0,	# Number of chromosomes or other distinct sequences in assembly
              #Indices
    PRIMARY KEY(id)
);

#A biosample - not much info here, just enough to drive analysis pipeline
CREATE TABLE cdwBiosample (
    id int unsigned auto_increment,	# Biosample id
    term varchar(255) default '',	# Human readable..
    taxon int unsigned default 0,	# NCBI taxon number - 9606 for human.
    sex varchar(255) default '',	# One letter code: M male, F female, B both, U unknown
              #Indices
    PRIMARY KEY(id),
    INDEX(term)
);

#An experiment - ideally will include a couple of biological replicates. Downloaded from Stanford.
CREATE TABLE cdwExperiment (
    accession char(16) default '',	# ID shared with metadata system.
    dataType varchar(255) default '',	# Something liek RNA-seq, DNase-seq, ChIP-seq. Computed at UCSC.
    lab varchar(255) default '',	# Lab PI name and institution. Is lab.title at Stanford.
    biosample varchar(255) default '',	# Cell line name, tissue source, etc. Is biosample_term_name at Stanford.
    rfa varchar(255) default '',	# Something like 'ENCODE2' or 'ENCODE3'.  Is award.rfa at Stanford.
    assayType varchar(255) default '',	# Similar to dataType. Is assay_term_name at Stanford.
    ipTarget varchar(255) default '',	# The target for the immunoprecipitation in ChIP & RIP.
    control varchar(255) default '',	# Primary control for experiment.  Usually another experiment accession.
              #Indices
    UNIQUE(accession)
);

#A file that has been uploaded, the format checked, and for which at least minimal metadata exists
CREATE TABLE cdwValidFile (
    id int unsigned auto_increment,	# ID of validated file
    licensePlate char(16) default '',	# A abc123 looking license-platish thing.
    fileId int unsigned default 0,	# Pointer to file in main file table
    format varchar(255) default '',	# What format it's in from manifest
    outputType varchar(255) default '',	# What output_type it is from manifest
    experiment varchar(255) default '',	# What experiment it's in from manifest
    replicate varchar(255) default '',	# What replicate it is from manifest.  Values 1,2,3... pooled, or ''
    enrichedIn varchar(255) default '',	# The enriched_in tag from manifest
    ucscDb varchar(255) default '',	# Something like hg19 or mm9
    itemCount bigint default 0,	# # of items in file: reads for fastqs, lines for beds, bases w/data for wig.
    basesInItems bigint default 0,	# # of bases in items
    sampleCount bigint default 0,	# # of items in sample if we are just subsampling as we do for reads.
    basesInSample bigint default 0,	# # of bases in our sample
    sampleBed varchar(255) default '',	# Path to a temporary bed file holding sample items
    mapRatio double default 0,	# Proportion of items that map to genome
    sampleCoverage double default 0,	# Proportion of assembly covered by at least one item in sample
    depth double default 0,	# Estimated genome-equivalents covered by possibly overlapping data
    singleQaStatus tinyint default 0,	# 0 = untested, 1 =  pass, -1 = fail, 2 = forced pass, -2 = forced fail
    replicateQaStatus tinyint default 0,	# 0 = untested, 1 = pass, -1 = fail, 2 = forced pass, -2 = forced fail
    part varchar(255) default '',	# Manifest's file_part. Values 1,2,3... Used for fastqs split for analysis
    pairedEnd varchar(255) default '',	# The paired_end tag from the manifest.  Values 1,2 or ''
    qaVersion tinyint default 0,	# Version of QA pipeline making status decisions
    uniqueMapRatio double default 0,	# Fraction of reads that map uniquely to genome for bams and fastqs
    lane varchar(255) default '',	# What sequencing lane if any associated with this file.
              #Indices
    PRIMARY KEY(id),
    INDEX(licensePlate),
    INDEX(fileId),
    INDEX(format(12)),
    INDEX(outputType(16)),
    INDEX(experiment(16))
);

#info on a file in fastq short read format beyond what's in cdwValidFile
CREATE TABLE cdwFastqFile (
    id int unsigned auto_increment,	# ID in this table
    fileId int unsigned default 0,	# ID in cdwFile table
    sampleCount bigint default 0,	# # of reads in sample.
    basesInSample bigint default 0,	# # of bases in sample.
    sampleFileName varchar(255) default '',	# Name of file containing sampleCount randomly selected items from file.
    readCount bigint default 0,	# # of reads in file
    baseCount bigint default 0,	# # of bases in all reads added up
    readSizeMean double default 0,	# Average read size
    readSizeStd double default 0,	# Standard deviation of read size
    readSizeMin int default 0,	# Minimum read size
    readSizeMax int default 0,	# Maximum read size
    qualMean double default 0,	# Mean quality scored as 10*-log10(errorProbability) or close to it.  >25 is good
    qualStd double default 0,	# Standard deviation of quality
    qualMin double default 0,	# Minimum observed quality
    qualMax double default 0,	# Maximum observed quality
    qualType varchar(255) default '',	# For fastq files either 'sanger' or 'illumina'
    qualZero int default 0,	# For fastq files offset to get to zero value in ascii encoding
    atRatio double default 0,	# Ratio of A+T to total sequence (not including Ns)
    aRatio double default 0,	# Ratio of A to total sequence (including Ns)
    cRatio double default 0,	# Ratio of C to total sequence (including Ns)
    gRatio double default 0,	# Ratio of G to total sequence (including Ns)
    tRatio double default 0,	# Ratio of T to total sequence (including Ns)
    nRatio double default 0,	# Ratio of N or . to total sequence
    qualPos longblob,	# Mean value for each position in a read up to some max.
    aAtPos longblob,	# % of As at each pos
    cAtPos longblob,	# % of Cs at each pos
    gAtPos longblob,	# % of Gs at each pos
    tAtPos longblob,	# % of Ts at each pos
    nAtPos longblob,	# % of '.' or 'N' at each pos
              #Indices
    PRIMARY KEY(id),
    UNIQUE(fileId)
);

#Info on what is in a bam file beyond whet's in cdwValidFile
CREATE TABLE cdwBamFile (
    id int unsigned auto_increment,	# ID in this table
    fileId int unsigned default 0,	# ID in cdwFile table.
    isPaired tinyint default 0,	# Set to 1 if paired reads, 0 if single
    isSortedByTarget tinyint default 0,	# Set to 1 if sorted by target,pos
    readCount bigint default 0,	# # of reads in file
    readBaseCount bigint default 0,	# # of bases in all reads added up
    mappedCount bigint default 0,	# # of reads that map
    uniqueMappedCount bigint default 0,	# # of reads that map to a unique position
    readSizeMean double default 0,	# Average read size
    readSizeStd double default 0,	# Standard deviation of read size
    readSizeMin int default 0,	# Minimum read size
    readSizeMax int default 0,	# Maximum read size
    u4mReadCount int default 0,	# Uniquely-mapped 4 million read actual read # (usually 4M)
    u4mUniquePos int default 0,	# Unique positions in target of the 4M reads that map to single pos
    u4mUniqueRatio double default 0,	# u4mUniqPos/u4mReadCount - measures library diversity
    targetBaseCount bigint default 0,	# Count of bases in mapping target
    targetSeqCount int unsigned default 0,	# Number of chromosomes or other distinct sequences in mapping target
              #Indices
    PRIMARY KEY(id),
    UNIQUE(fileId)
);

#Info on what is in a vcf file beyond whet's in cdwValidFile
CREATE TABLE cdwVcfFile (
    id int unsigned auto_increment,	# ID in this table
    fileId int unsigned default 0,	# ID in cdwFile table.
    vcfMajorVersion int default 0,	# VCF file major version
    vcfMinorVersion int default 0,	# VCF file minor version
    genotypeCount int default 0,	# How many genotypes of data
    itemCount bigint default 0,	# Number of records in VCF file
    chromsHit int default 0,	# Number of chromosomes (or contigs) with data
    passItemCount bigint default 0,	# Number of records that PASS listed filter
    passRatio double default 0,	# passItemCount/itemCount
    snpItemCount bigint default 0,	# Number of records that are just single base substitution, no indels
    snpRatio double default 0,	# snpItemCount/itemCount
    sumOfSizes bigint default 0,	# The sum of sizes of all records
    basesCovered bigint default 0,	# Bases with data. Equals sumOfSizes if no overlap of records.
    xBasesCovered int default 0,	# Number of bases of chrX covered
    yBasesCovered int default 0,	# Number of bases of chrY covered
    mBasesCovered int default 0,	# Number of bases of chrM covered
    haploidCount bigint default 0,	# Number of genotype calls that are haploid
    haploidRatio double default 0,	# Ratio of hapload to total calls
    phasedCount bigint default 0,	# Number of genotype calls that are phased
    phasedRatio double default 0,	# Ration of phased calls to total calls
    gotDepth tinyint default 0,	# If true then have DP value in file and in depth stats below
    depthMin double default 0,	# Min DP reported depth
    depthMean double default 0,	# Mean DP value
    depthMax double default 0,	# Max DP value
    depthStd double default 0,	# Standard DP deviation
              #Indices
    PRIMARY KEY(id),
    UNIQUE(fileId)
);

#Record of a QA failure.
CREATE TABLE cdwQaFail (
    id int unsigned auto_increment,	# ID of failure
    fileId int unsigned default 0,	# File that failed
    qaVersion int unsigned default 0,	# QA pipeline version
    reason longblob,	# reason for failure
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId)
);

#A target for our enrichment analysis.
CREATE TABLE cdwQaEnrichTarget (
    id int unsigned auto_increment,	# ID of this enrichment target
    assemblyId int unsigned default 0,	# Which assembly this goes to
    name varchar(255) default '',	# Something like 'exon' or 'promoter'
    fileId int unsigned default 0,	# A simple BED 3 format file that defines target. Bases covered are unique
    targetSize bigint default 0,	# Total number of bases covered by target
              #Indices
    PRIMARY KEY(id),
    INDEX(assemblyId),
    INDEX(name)
);

#An enrichment analysis applied to file.
CREATE TABLE cdwQaEnrich (
    id int unsigned auto_increment,	# ID of this enrichment analysis
    fileId int unsigned default 0,	# File we are looking at skeptically
    qaEnrichTargetId int unsigned default 0,	# Information about a target for this analysis
    targetBaseHits bigint default 0,	# Number of hits to bases in target
    targetUniqHits bigint default 0,	# Number of unique bases hit in target
    coverage double default 0,	# Coverage of target - just targetUniqHits/targetSize
    enrichment double default 0,	# Amount we hit target/amount we hit genome
    uniqEnrich double default 0,	# coverage/sampleCoverage
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId)
);

#A target for our contamination analysis.
CREATE TABLE cdwQaContamTarget (
    id int unsigned auto_increment,	# ID of this contamination target
    assemblyId int unsigned default 0,	# Assembly we're aligning against to check  for contamination.
              #Indices
    PRIMARY KEY(id),
    UNIQUE(assemblyId)
);

#Results of contamination analysis of one file against one target
CREATE TABLE cdwQaContam (
    id int unsigned auto_increment,	# ID of this contamination analysis
    fileId int unsigned default 0,	# File we are looking at skeptically
    qaContamTargetId int unsigned default 0,	# Information about a target for this analysis
    mapRatio double default 0,	# Proportion of items that map to target
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId)
);

#What percentage of data set aligns to various repeat classes.
CREATE TABLE cdwQaRepeat (
    id int unsigned auto_increment,	# ID of this repeat analysis.
    fileId int unsigned default 0,	# File we are analysing.
    repeatClass varchar(255) default '',	# RepeatMasker high end classification,  or 'total' for all repeats.
    mapRatio double default 0,	# Proportion that map to this repeat.
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId)
);

#A comparison of the amount of overlap between two samples that cover ~0.1% to 10% of target.
CREATE TABLE cdwQaPairSampleOverlap (
    id int unsigned auto_increment,	# Id of this qa pair
    elderFileId int unsigned default 0,	# Id of elder (smaller fileId) in correlated pair
    youngerFileId int unsigned default 0,	# Id of younger (larger fileId) in correlated pair
    elderSampleBases bigint default 0,	# Number of bases in elder sample
    youngerSampleBases bigint default 0,	# Number of bases in younger sample
    sampleOverlapBases bigint default 0,	# Number of bases that overlap between younger and elder sample
    sampleSampleEnrichment double default 0,	# Amount samples overlap more than expected.
              #Indices
    PRIMARY KEY(id),
    INDEX(elderFileId),
    INDEX(youngerFileId)
);

#A correlation between two files of the same type.
CREATE TABLE cdwQaPairCorrelation (
    id int unsigned auto_increment,	# Id of this correlation pair
    elderFileId int unsigned default 0,	# Id of elder (smaller fileId) in correlated pair
    youngerFileId int unsigned default 0,	# Id of younger (larger fileId) in correlated pair
    pearsonInEnriched double default 0,	# Pearson's R inside enriched areas where there is overlap
    pearsonOverall double default 0,	# Pearson's R over all places where both have data
    pearsonClipped double default 0,	# Pearson's R clipped at two standard deviations up from the mean
              #Indices
    PRIMARY KEY(id),
    INDEX(elderFileId),
    INDEX(youngerFileId)
);

#Information about two paired-end fastqs
CREATE TABLE cdwQaPairedEndFastq (
    id int unsigned auto_increment,	# Id of this set of paired end files
    fileId1 int unsigned default 0,	# Id of first in pair
    fileId2 int unsigned default 0,	# Id of second in pair
    concordance double default 0,	# % of uniquely aligning reads where pairs nearby and point right way
    distanceMean double default 0,	# Average distance between reads
    distanceStd double default 0,	# Standard deviation of distance
    distanceMin double default 0,	# Minimum distance
    distanceMax double default 0,	# Maximum distatnce
    recordComplete tinyint default 0,	# Flag to avoid a race condition. Ignore record if this is 0
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId1),
    INDEX(fileId2)
);

#Information about proportion of signal in a wig that lands under spots in a peak or bed file
CREATE TABLE cdwQaWigSpot (
    id int unsigned auto_increment,	# Id of this wig/spot intersection
    wigId int unsigned default 0,	# Id of bigWig file
    spotId int unsigned default 0,	# Id of a bigBed file probably broadPeak or narrowPeak
    spotRatio double default 0,	# Ratio of signal in spots to total signal,  between 0 and 1
    enrichment double default 0,	# Enrichment in spots compared to genome overall
    basesInGenome bigint default 0,	# Number of bases in genome
    basesInSpots bigint default 0,	# Number of bases in spots
    sumSignal double default 0,	# Total signal
    spotSumSignal double default 0,	# Total signal in spots
              #Indices
    PRIMARY KEY(id),
    INDEX(wigId),
    INDEX(spotId)
);

#Statistics calculated based on a 5M sample of DNAse aligned reads from a bam file.
CREATE TABLE cdwQaDnaseSingleStats5m (
    id int unsigned auto_increment,	# Id of this row in table.
    fileId int unsigned default 0,	# Id of bam file this is calculated from
    sampleReads int unsigned default 0,	# Number of mapped reads 
    spotRatio double default 0,	# Ratio of signal in spots to total signal,  between 0 and 1
    enrichment double default 0,	# Enrichment in spots compared to genome overall
    basesInGenome bigint default 0,	# Number of bases in genome
    basesInSpots bigint default 0,	# Number of bases in spots
    sumSignal double default 0,	# Total signal
    spotSumSignal double default 0,	# Total signal in spots
    estFragLength varchar(255) default '',	# Up to three comma separated strand cross-correlation peaks
    corrEstFragLen varchar(255) default '',	# Up to three cross strand correlations at the given peaks
    phantomPeak int default 0,	# Read length/phantom peak strand shift
    corrPhantomPeak double default 0,	# Correlation value at phantom peak
    argMinCorr int default 0,	# strand shift at which cross-correlation is lowest
    minCorr double default 0,	# minimum value of cross-correlation
    nsc double default 0,	# Normalized strand cross-correlation coefficient (NSC) = corrEstFragLen/minCorr
    rsc double default 0,	# Relative strand cross-correlation coefficient (RSC)
    rscQualityTag int default 0,	# based on thresholded RSC (codes: -2:veryLow,-1:Low,0:Medium,1:High,2:veryHigh)
              #Indices
    PRIMARY KEY(id),
    INDEX(fileId)
);

#A job to be run asynchronously and not too many all at once.
CREATE TABLE cdwJob (
    id int unsigned auto_increment,	# Job id
    commandLine longblob,	# Command line of job
    startTime bigint default 0,	# Start time in seconds since 1970
    endTime bigint default 0,	# End time in seconds since 1970
    stderr longblob,	# The output to stderr of the run - may be nonempty even with success
    returnCode int default 0,	# The return code from system command - 0 for success
    pid int default 0,	# Process ID for running processes
    submitId int default 0,	# Associated submission ID if any
              #Indices
    PRIMARY KEY(id)
);

#Some files can be visualized as a track. Stuff to help define that track goes here.
CREATE TABLE cdwTrackViz (
    id int unsigned auto_increment,	# Id of this row in the table
    fileId int unsigned default 0,	# File this is a viz of
    shortLabel varchar(255) default '',	# Up to 17 char label for track
    longLabel varchar(255) default '',	# Up to 100 char label for track
    type varchar(255) default '',	# One of the customTrack types such as bam,vcfTabix,bigWig,bigBed
    bigDataFile varchar(255) default '',	# Where big data file lives relative to cdwRootDir
              #Indices
    PRIMARY KEY(id)
);

#A dataset is a collection of files, usually associated with a paper
CREATE TABLE cdwDataset (
    id int unsigned auto_increment,	# Dataset ID
    name varchar(255) default '',	# Short name of this dataset, one word, no spaces
    label varchar(255) default '',	# short title of the dataset, a few words
    description longblob,	# Description of dataset, can be a complete html paragraph.
    pmid varchar(255) default '',	# Pubmed ID of abstract
    pmcid varchar(255) default '',	# PubmedCentral ID of paper full text
    metaDivTags varchar(255) default '',	# Comma separated list of fields used to make tree out of metadata
    metaLabelTags longblob,	# Comma separated list of fields good to use in labels for graphs etc.
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name)
);

#A joint dataset is a collection of datasets, usually associated with a common trait.
CREATE TABLE cdwJointDataset (
    id int unsigned auto_increment,	# Dataset ID
    name varchar(255) default '',	# Short name of this dataset, one word, no spaces
    label varchar(255) default '',	# short title of the dataset, a few words
    description longblob,	# Description of dataset, can be a complete html paragraph.
    childrenNames varchar(255) default '',	# Comma separated list of children data set names.
    metaDivTags varchar(255) default '',	# Comma separated list of fields used to make tree out of metadata
              #Indices
    PRIMARY KEY(id),
    UNIQUE(name)
);
