← Back to branch summary

~ubuntu-branches/ubuntu/utopic/r-bioc-cummerbund/utopic

« back to all changes in this revision

Viewing changes to inst/extdata/cuffData_schema.sql

  • Committer: Package Import Robot
  • Author(s): Andreas Tille
  • Date: 2013-12-28 17:17:25 UTC
  • mfrom: (1.1.2)
  • Revision ID: package-import@ubuntu.com-20131228171725-polmzo8go4m371c6
Tags: 2.4.1-1
* New upstream version
* debian/rules: Remove useless creation of ${R-Depends}
* debian/control: Versioned Build-Depends: r-base-dev (>= 3.0)
* debian/README.test: add hint how to test the package

Show diffs side-by-side

added added

removed removed

Lines of Context:
inst/extdata/cuffData_schema.sql
1
1
-- Creator:       MySQL Workbench 5.2.33/ExportSQLite plugin 2009.12.02
2
2
-- Author:        Loyal Goff
 
3
-- Caption:       New Model
 
4
-- Project:       Name of the project
 
5
-- Changed:       2012-05-01 15:20
3
6
-- Created:       2011-05-02 12:52
4
7
PRAGMA foreign_keys = OFF;
5
8
 
6
9
-- Schema: cuffData
7
10
BEGIN;
8
 
DROP TABLE IF EXISTS "genes";
9
11
CREATE TABLE "genes"(
10
12
  "gene_id" VARCHAR(45) PRIMARY KEY NOT NULL,
11
13
  "class_code" VARCHAR(45),
18
17
  "length" INTEGER,
19
18
  "coverage" FLOAT
20
19
);
21
 
DROP TABLE IF EXISTS "biasData";
 
20
CREATE INDEX "genes.gsn_index" ON "genes"("gene_short_name");
 
21
CREATE INDEX "genes.cc_index" ON "genes"("class_code");
22
22
CREATE TABLE "biasData"(
23
23
  "biasData_id" INTEGER PRIMARY KEY NOT NULL
24
24
);
25
 
DROP TABLE IF EXISTS "samples";
26
25
CREATE TABLE "samples"(
27
 
  "sample_index" INTEGER PRIMARY KEY NOT NULL,
28
 
  "sample_name" VARCHAR(45) NOT NULL
 
26
  "sample_index" INTEGER NOT NULL,
 
27
  "sample_name" VARCHAR(45) PRIMARY KEY NOT NULL
29
28
);
30
 
DROP TABLE IF EXISTS "TSS";
31
29
CREATE TABLE "TSS"(
32
30
  "TSS_group_id" VARCHAR(45) PRIMARY KEY NOT NULL,
33
31
  "class_code" VARCHAR(45),
34
32
  "nearest_ref_id" VARCHAR(45),
35
33
  "gene_id" VARCHAR(45) NOT NULL,
 
34
  "gene_short_name" VARCHAR(45),
36
35
  "locus" VARCHAR(45),
37
36
  "length" INTEGER,
38
37
  "coverage" FLOAT,
41
40
    REFERENCES "genes"("gene_id")
42
41
);
43
42
CREATE INDEX "TSS.fk_TSS_genes1" ON "TSS"("gene_id");
44
 
DROP TABLE IF EXISTS "TSSData";
45
43
CREATE TABLE "TSSData"(
46
44
  "TSS_group_id" VARCHAR(45) NOT NULL,
47
45
  "sample_name" VARCHAR(45) NOT NULL,
58
56
);
59
57
CREATE INDEX "TSSData.fk_TSSData_TSS1" ON "TSSData"("TSS_group_id");
60
58
CREATE INDEX "TSSData.fk_TSSData_samples1" ON "TSSData"("sample_name");
61
 
DROP TABLE IF EXISTS "CDS";
62
59
CREATE TABLE "CDS"(
63
60
  "CDS_id" VARCHAR(45) PRIMARY KEY NOT NULL,
64
61
  "class_code" VARCHAR(45),
68
65
  "locus" VARCHAR(45),
69
66
  "length" INTEGER,
70
67
  "coverage" FLOAT,
 
68
  "gene_short_name" VARCHAR(45),
71
69
  CONSTRAINT "fk_CDS_genes1"
72
70
    FOREIGN KEY("gene_id")
73
71
    REFERENCES "genes"("gene_id"),
77
75
);
78
76
CREATE INDEX "CDS.fk_CDS_genes1" ON "CDS"("gene_id");
79
77
CREATE INDEX "CDS.fk_CDS_TSS1" ON "CDS"("TSS_group_id");
80
 
DROP TABLE IF EXISTS "CDSData";
81
78
CREATE TABLE "CDSData"(
82
79
  "CDS_id" VARCHAR(45) NOT NULL,
83
80
  "sample_name" VARCHAR(45) NOT NULL,
94
91
);
95
92
CREATE INDEX "CDSData.fk_CDSData_CDS1" ON "CDSData"("CDS_id");
96
93
CREATE INDEX "CDSData.fk_CDSData_samples1" ON "CDSData"("sample_name");
97
 
DROP TABLE IF EXISTS "splicingDiffData";
98
94
CREATE TABLE "splicingDiffData"(
99
95
  "TSS_group_id" VARCHAR(45) NOT NULL,
100
96
  "gene_id" VARCHAR(45) NOT NULL,
125
121
CREATE INDEX "splicingDiffData.fk_splicingDiffData_samples2" ON "splicingDiffData"("sample_2");
126
122
CREATE INDEX "splicingDiffData.fk_splicingDiffData_TSS1" ON "splicingDiffData"("TSS_group_id");
127
123
CREATE INDEX "splicingDiffData.fk_splicingDiffData_genes1" ON "splicingDiffData"("gene_id");
128
 
DROP TABLE IF EXISTS "TSSExpDiffData";
129
124
CREATE TABLE "TSSExpDiffData"(
130
125
  "TSS_group_id" VARCHAR(45) NOT NULL,
131
126
  "sample_1" VARCHAR(45) NOT NULL,
133
128
  "status" VARCHAR(45),
134
129
  "value_1" FLOAT,
135
130
  "value_2" FLOAT,
136
 
  "log2_fold_change" FLOAT,
 
131
  "ln_fold_change" FLOAT,
137
132
  "test_stat" FLOAT,
138
133
  "p_value" FLOAT,
139
134
  "q_value" FLOAT,
151
146
CREATE INDEX "TSSExpDiffData.fk_TSSExpDiffData_TSS1" ON "TSSExpDiffData"("TSS_group_id");
152
147
CREATE INDEX "TSSExpDiffData.fk_TSSExpDiffData_samples1" ON "TSSExpDiffData"("sample_1");
153
148
CREATE INDEX "TSSExpDiffData.fk_TSSExpDiffData_samples2" ON "TSSExpDiffData"("sample_2");
154
 
DROP TABLE IF EXISTS "CDSDiffData";
 
149
CREATE INDEX "TSSExpDiffData.TSSExpDiffData_sig_index" ON "TSSExpDiffData"("test_stat","p_value","q_value","significant");
155
150
CREATE TABLE "CDSDiffData"(
156
151
  "gene_id" VARCHAR(45) NOT NULL,
157
152
  "sample_1" VARCHAR(45) NOT NULL,
177
172
CREATE INDEX "CDSDiffData.fk_CDSDiffData_samples1" ON "CDSDiffData"("sample_1");
178
173
CREATE INDEX "CDSDiffData.fk_CDSDiffData_samples2" ON "CDSDiffData"("sample_2");
179
174
CREATE INDEX "CDSDiffData.fk_CDSDiffData_genes1" ON "CDSDiffData"("gene_id");
180
 
DROP TABLE IF EXISTS "CDSExpDiffData";
181
175
CREATE TABLE "CDSExpDiffData"(
182
176
  "CDS_id" VARCHAR(45) NOT NULL,
183
177
  "sample_1" VARCHAR(45) NOT NULL,
185
179
  "status" VARCHAR(45),
186
180
  "value_1" FLOAT,
187
181
  "value_2" FLOAT,
188
 
  "log2_fold_change" FLOAT,
 
182
  "ln_fold_change" FLOAT,
189
183
  "test_stat" FLOAT,
190
184
  "p_value" FLOAT,
191
185
  "q_value" FLOAT,
203
197
CREATE INDEX "CDSExpDiffData.fk_CDSExpDiffData_CDS1" ON "CDSExpDiffData"("CDS_id");
204
198
CREATE INDEX "CDSExpDiffData.fk_CDSExpDiffData_samples1" ON "CDSExpDiffData"("sample_1");
205
199
CREATE INDEX "CDSExpDiffData.fk_CDSExpDiffData_samples2" ON "CDSExpDiffData"("sample_2");
206
 
DROP TABLE IF EXISTS "promoterDiffData";
 
200
CREATE INDEX "CDSExpDiffData.CDSExpDiffData_sig_index" ON "CDSExpDiffData"("test_stat","p_value","q_value","significant");
207
201
CREATE TABLE "promoterDiffData"(
208
202
  "gene_id" VARCHAR(45) NOT NULL,
209
203
  "sample_1" VARCHAR(45) NOT NULL,
229
223
CREATE INDEX "promoterDiffData.fk_promoterDiffData_genes1" ON "promoterDiffData"("gene_id");
230
224
CREATE INDEX "promoterDiffData.fk_promoterDiffData_samples1" ON "promoterDiffData"("sample_1");
231
225
CREATE INDEX "promoterDiffData.fk_promoterDiffData_samples2" ON "promoterDiffData"("sample_2");
232
 
DROP TABLE IF EXISTS "geneFeatures";
233
226
CREATE TABLE "geneFeatures"(
234
227
  "gene_id" VARCHAR(45) NOT NULL,
235
228
  CONSTRAINT "fk_geneFeatures_genes1"
237
230
    REFERENCES "genes"("gene_id")
238
231
);
239
232
CREATE INDEX "geneFeatures.fk_geneFeatures_genes1" ON "geneFeatures"("gene_id");
240
 
DROP TABLE IF EXISTS "TSSFeatures";
241
233
CREATE TABLE "TSSFeatures"(
242
234
  "TSS_group_id" VARCHAR(45) NOT NULL,
243
235
  CONSTRAINT "fk_TSSFeatures_TSS1"
245
237
    REFERENCES "TSS"("TSS_group_id")
246
238
);
247
239
CREATE INDEX "TSSFeatures.fk_TSSFeatures_TSS1" ON "TSSFeatures"("TSS_group_id");
248
 
DROP TABLE IF EXISTS "CDSFeatures";
249
240
CREATE TABLE "CDSFeatures"(
250
241
  "CDS_id" VARCHAR(45) NOT NULL,
251
242
  CONSTRAINT "fk_CDSFeatures_CDS1"
253
244
    REFERENCES "CDS"("CDS_id")
254
245
);
255
246
CREATE INDEX "CDSFeatures.fk_CDSFeatures_CDS1" ON "CDSFeatures"("CDS_id");
256
 
DROP TABLE IF EXISTS "geneData";
 
247
CREATE TABLE "model_transcripts"(
 
248
  "model_transcript_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
 
249
);
 
250
CREATE TABLE "geneCount"(
 
251
  "gene_id" VARCHAR(45) NOT NULL,
 
252
  "sample_name" VARCHAR(45) NOT NULL,
 
253
  "count" FLOAT,
 
254
  "variance" FLOAT,
 
255
  "uncertainty" FLOAT,
 
256
  "dispersion" FLOAT,
 
257
  "status" VARCHAR(45),
 
258
  CONSTRAINT "fk_geneCount_samples1"
 
259
    FOREIGN KEY("sample_name")
 
260
    REFERENCES "samples"("sample_name"),
 
261
  CONSTRAINT "fk_geneCount_genes1"
 
262
    FOREIGN KEY("gene_id")
 
263
    REFERENCES "genes"("gene_id")
 
264
);
 
265
CREATE INDEX "geneCount.fk_geneCount_samples1" ON "geneCount"("sample_name");
 
266
CREATE INDEX "geneCount.fk_geneCount_genes1" ON "geneCount"("gene_id");
 
267
CREATE TABLE "CDSCount"(
 
268
  "CDS_id" VARCHAR(45) NOT NULL,
 
269
  "sample_name" VARCHAR(45) NOT NULL,
 
270
  "count" FLOAT,
 
271
  "variance" FLOAT,
 
272
  "uncertainty" FLOAT,
 
273
  "dispersion" FLOAT,
 
274
  "status" VARCHAR(45),
 
275
  CONSTRAINT "fk_CDSCount_CDS1"
 
276
    FOREIGN KEY("CDS_id")
 
277
    REFERENCES "CDS"("CDS_id"),
 
278
  CONSTRAINT "fk_CDSCount_samples1"
 
279
    FOREIGN KEY("sample_name")
 
280
    REFERENCES "samples"("sample_name")
 
281
);
 
282
CREATE INDEX "CDSCount.fk_CDSCount_CDS1" ON "CDSCount"("CDS_id");
 
283
CREATE INDEX "CDSCount.fk_CDSCount_samples1" ON "CDSCount"("sample_name");
 
284
CREATE TABLE "TSSCount"(
 
285
  "TSS_group_id" VARCHAR(45) NOT NULL,
 
286
  "sample_name" VARCHAR(45) NOT NULL,
 
287
  "count" FLOAT,
 
288
  "variance" FLOAT,
 
289
  "uncertainty" FLOAT,
 
290
  "dispersion" FLOAT,
 
291
  "status" VARCHAR(45),
 
292
  CONSTRAINT "fk_TSSCount_TSS1"
 
293
    FOREIGN KEY("TSS_group_id")
 
294
    REFERENCES "TSS"("TSS_group_id"),
 
295
  CONSTRAINT "fk_TSSCount_samples1"
 
296
    FOREIGN KEY("sample_name")
 
297
    REFERENCES "samples"("sample_name")
 
298
);
 
299
CREATE INDEX "TSSCount.fk_TSSCount_TSS1" ON "TSSCount"("TSS_group_id");
 
300
CREATE INDEX "TSSCount.fk_TSSCount_samples1" ON "TSSCount"("sample_name");
 
301
CREATE TABLE "replicates"(
 
302
  "file" INTEGER NOT NULL,
 
303
  "sample_name" VARCHAR(45) NOT NULL,
 
304
  "replicate" VARCHAR(45),
 
305
  "rep_name" VARCHAR(45) PRIMARY KEY NOT NULL,
 
306
  "total_mass" FLOAT,
 
307
  "norm_mass" FLOAT,
 
308
  "internal_scale" FLOAT,
 
309
  "external_scale" FLOAT,
 
310
  CONSTRAINT "fk_replicates_samples1"
 
311
    FOREIGN KEY("sample_name")
 
312
    REFERENCES "samples"("sample_name")
 
313
);
 
314
CREATE INDEX "replicates.fk_replicates_samples1" ON "replicates"("sample_name");
 
315
CREATE TABLE "geneReplicateData"(
 
316
  "gene_id" VARCHAR(45) NOT NULL,
 
317
  "sample_name" VARCHAR(45) NOT NULL,
 
318
  "replicate" INTEGER,
 
319
  "rep_name" VARCHAR(45) NOT NULL,
 
320
  "raw_frags" FLOAT,
 
321
  "internal_scaled_frags" FLOAT,
 
322
  "external_scaled_frags" FLOAT,
 
323
  "fpkm" FLOAT,
 
324
  "effective_length" FLOAT,
 
325
  "status" VARCHAR(45),
 
326
  CONSTRAINT "fk_geneData_genes10"
 
327
    FOREIGN KEY("gene_id")
 
328
    REFERENCES "genes"("gene_id"),
 
329
  CONSTRAINT "fk_geneReplicateData_replicates1"
 
330
    FOREIGN KEY("rep_name")
 
331
    REFERENCES "replicates"("rep_name"),
 
332
  CONSTRAINT "fk_geneReplicateData_samples1"
 
333
    FOREIGN KEY("sample_name")
 
334
    REFERENCES "samples"("sample_name")
 
335
);
 
336
CREATE INDEX "geneReplicateData.fk_geneReplicateData_genes1" ON "geneReplicateData"("gene_id");
 
337
CREATE INDEX "geneReplicateData.fk_geneReplicateData_replicates1" ON "geneReplicateData"("rep_name");
 
338
CREATE INDEX "geneReplicateData.fk_geneReplicateData_samples1" ON "geneReplicateData"("sample_name");
 
339
CREATE TABLE "CDSReplicateData"(
 
340
  "CDS_id" VARCHAR(45) NOT NULL,
 
341
  "sample_name" VARCHAR(45) NOT NULL,
 
342
  "replicate" INTEGER,
 
343
  "rep_name" VARCHAR(45) NOT NULL,
 
344
  "raw_frags" FLOAT,
 
345
  "internal_scaled_frags" FLOAT,
 
346
  "external_scaled_frags" FLOAT,
 
347
  "fpkm" FLOAT,
 
348
  "effective_length" FLOAT,
 
349
  "status" VARCHAR(45),
 
350
  CONSTRAINT "fk_geneReplicateData_replicates100"
 
351
    FOREIGN KEY("rep_name")
 
352
    REFERENCES "replicates"("rep_name"),
 
353
  CONSTRAINT "fk_CDSReplicateData_CDS1"
 
354
    FOREIGN KEY("CDS_id")
 
355
    REFERENCES "CDS"("CDS_id"),
 
356
  CONSTRAINT "fk_CDSReplicateData_samples1"
 
357
    FOREIGN KEY("sample_name")
 
358
    REFERENCES "samples"("sample_name")
 
359
);
 
360
CREATE INDEX "CDSReplicateData.fk_CDSReplicateData_replicates1" ON "CDSReplicateData"("rep_name");
 
361
CREATE INDEX "CDSReplicateData.fk_CDSReplicateData_CDS1" ON "CDSReplicateData"("CDS_id");
 
362
CREATE INDEX "CDSReplicateData.fk_CDSReplicateData_samples1" ON "CDSReplicateData"("sample_name");
 
363
CREATE TABLE "TSSReplicateData"(
 
364
  "TSS_group_id" VARCHAR(45) NOT NULL,
 
365
  "sample_name" VARCHAR(45) NOT NULL,
 
366
  "replicate" VARCHAR(45),
 
367
  "rep_name" VARCHAR(45) NOT NULL,
 
368
  "raw_frags" FLOAT,
 
369
  "internal_scaled_frags" FLOAT,
 
370
  "external_scaled_frags" FLOAT,
 
371
  "fpkm" FLOAT,
 
372
  "effective_length" FLOAT,
 
373
  "status" VARCHAR(45),
 
374
  CONSTRAINT "fk_geneReplicateData_replicates10000"
 
375
    FOREIGN KEY("rep_name")
 
376
    REFERENCES "replicates"("rep_name"),
 
377
  CONSTRAINT "fk_TSSReplicateData_TSS1"
 
378
    FOREIGN KEY("TSS_group_id")
 
379
    REFERENCES "TSS"("TSS_group_id"),
 
380
  CONSTRAINT "fk_TSSReplicateData_samples1"
 
381
    FOREIGN KEY("sample_name")
 
382
    REFERENCES "samples"("sample_name")
 
383
);
 
384
CREATE INDEX "TSSReplicateData.fk_TSSReplicateData_replicates1" ON "TSSReplicateData"("rep_name");
 
385
CREATE INDEX "TSSReplicateData.fk_TSSReplicateData_TSS1" ON "TSSReplicateData"("TSS_group_id");
 
386
CREATE INDEX "TSSReplicateData.fk_TSSReplicateData_samples1" ON "TSSReplicateData"("sample_name");
 
387
CREATE TABLE "runInfo"(
 
388
  "param" VARCHAR(45),
 
389
  "value" TEXT
 
390
);
257
391
CREATE TABLE "geneData"(
258
392
  "gene_id" VARCHAR(45) NOT NULL,
259
393
  "sample_name" VARCHAR(45) NOT NULL,
270
404
);
271
405
CREATE INDEX "geneData.fk_geneData_genes1" ON "geneData"("gene_id");
272
406
CREATE INDEX "geneData.fk_geneData_samples1" ON "geneData"("sample_name");
273
 
DROP TABLE IF EXISTS "phenoData";
274
407
CREATE TABLE "phenoData"(
275
408
  "sample_name" VARCHAR(45) NOT NULL,
276
409
  "parameter" VARCHAR(45) NOT NULL,
280
413
    REFERENCES "samples"("sample_name")
281
414
);
282
415
CREATE INDEX "phenoData.fk_phenoData_samples" ON "phenoData"("sample_name");
283
 
DROP TABLE IF EXISTS "geneExpDiffData";
284
416
CREATE TABLE "geneExpDiffData"(
285
417
  "gene_id" VARCHAR(45) NOT NULL,
286
418
  "sample_1" VARCHAR(45) NOT NULL,
288
420
  "status" VARCHAR(45),
289
421
  "value_1" FLOAT,
290
422
  "value_2" FLOAT,
291
 
  "log2_fold_change" FLOAT,
 
423
  "ln_fold_change" FLOAT,
292
424
  "test_stat" FLOAT,
293
425
  "p_value" FLOAT,
294
426
  "q_value" FLOAT,
306
438
CREATE INDEX "geneExpDiffData.fk_geneExpDiffData_genes1" ON "geneExpDiffData"("gene_id");
307
439
CREATE INDEX "geneExpDiffData.fk_geneExpDiffData_samples1" ON "geneExpDiffData"("sample_1");
308
440
CREATE INDEX "geneExpDiffData.fk_geneExpDiffData_samples2" ON "geneExpDiffData"("sample_2");
309
 
DROP TABLE IF EXISTS "isoforms";
 
441
CREATE INDEX "geneExpDiffData.geneExpDiff_status_index" ON "geneExpDiffData"("status");
 
442
CREATE INDEX "geneExpDiffData.geneExpDiff_sig_index" ON "geneExpDiffData"("significant","p_value","q_value","test_stat");
310
443
CREATE TABLE "isoforms"(
311
444
  "isoform_id" VARCHAR(45) PRIMARY KEY NOT NULL,
312
445
  "gene_id" VARCHAR(45),
313
446
  "CDS_id" VARCHAR(45),
 
447
  "gene_short_name" VARCHAR(45),
314
448
  "TSS_group_id" VARCHAR(45),
315
449
  "class_code" VARCHAR(45),
316
450
  "nearest_ref_id" VARCHAR(45),
330
464
CREATE INDEX "isoforms.fk_isoforms_TSS1" ON "isoforms"("TSS_group_id");
331
465
CREATE INDEX "isoforms.fk_isoforms_CDS1" ON "isoforms"("CDS_id");
332
466
CREATE INDEX "isoforms.fk_isoforms_genes1" ON "isoforms"("gene_id");
333
 
DROP TABLE IF EXISTS "isoformData";
334
467
CREATE TABLE "isoformData"(
335
468
  "isoform_id" VARCHAR(45) NOT NULL,
336
469
  "sample_name" VARCHAR(45) NOT NULL,
347
480
);
348
481
CREATE INDEX "isoformData.fk_isoformData_samples1" ON "isoformData"("sample_name");
349
482
CREATE INDEX "isoformData.fk_isoformData_isoforms1" ON "isoformData"("isoform_id");
350
 
DROP TABLE IF EXISTS "isoformExpDiffData";
351
483
CREATE TABLE "isoformExpDiffData"(
352
484
  "isoform_id" VARCHAR(45) NOT NULL,
353
485
  "sample_1" VARCHAR(45) NOT NULL,
355
487
  "status" VARCHAR(45),
356
488
  "value_1" FLOAT,
357
489
  "value_2" FLOAT,
358
 
  "log2_fold_change" FLOAT,
 
490
  "ln_fold_change" FLOAT,
359
491
  "test_stat" FLOAT,
360
492
  "p_value" FLOAT,
361
493
  "q_value" FLOAT,
373
505
CREATE INDEX "isoformExpDiffData.fk_isoformExpDiffData_isoforms1" ON "isoformExpDiffData"("isoform_id");
374
506
CREATE INDEX "isoformExpDiffData.fk_isoformExpDiffData_samples1" ON "isoformExpDiffData"("sample_1");
375
507
CREATE INDEX "isoformExpDiffData.fk_isoformExpDiffData_samples2" ON "isoformExpDiffData"("sample_2");
376
 
DROP TABLE IF EXISTS "isoformFeatures";
 
508
CREATE INDEX "isoformExpDiffData.isoformExpDiffData_sig_index" ON "isoformExpDiffData"("test_stat","p_value","q_value","significant");
377
509
CREATE TABLE "isoformFeatures"(
378
510
  "isoform_id" VARCHAR(45) NOT NULL,
379
511
  CONSTRAINT "fk_isoformFeatures_isoforms1"
381
513
    REFERENCES "isoforms"("isoform_id")
382
514
);
383
515
CREATE INDEX "isoformFeatures.fk_isoformFeatures_isoforms1" ON "isoformFeatures"("isoform_id");
 
516
CREATE TABLE "features"(
 
517
--   GTF Features (all lines/records from reference .gtf file)
 
518
  "feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 
519
  "genes_gene_id" VARCHAR(45) NOT NULL,
 
520
  "isoforms_isoform_id" VARCHAR(45) NOT NULL,
 
521
  "seqname" VARCHAR(45) NOT NULL,
 
522
  "source" VARCHAR(45) NOT NULL,
 
523
  "type_id" INTEGER,
 
524
  "start" INTEGER,
 
525
  "end" INTEGER,
 
526
  "score" FLOAT,
 
527
  "strand" VARCHAR(45),
 
528
  "frame" VARCHAR(45),
 
529
  CONSTRAINT "fk_features_genes1"
 
530
    FOREIGN KEY("genes_gene_id")
 
531
    REFERENCES "genes"("gene_id"),
 
532
  CONSTRAINT "fk_features_isoforms1"
 
533
    FOREIGN KEY("isoforms_isoform_id")
 
534
    REFERENCES "isoforms"("isoform_id")
 
535
);
 
536
CREATE INDEX "features.features_seqname_index" ON "features"("seqname");
 
537
CREATE INDEX "features.features_type_index" ON "features"("type_id");
 
538
CREATE INDEX "features.features_strand_index" ON "features"("strand");
 
539
CREATE INDEX "features.features_start_end_index" ON "features"("start","end");
 
540
CREATE INDEX "features.fk_features_genes1" ON "features"("genes_gene_id");
 
541
CREATE INDEX "features.fk_features_isoforms1" ON "features"("isoforms_isoform_id");
 
542
CREATE TABLE "attributes"(
 
543
  "attribute_lookup_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 
544
  "feature_id" INTEGER NOT NULL,
 
545
  "attribute" VARCHAR(45) NOT NULL,
 
546
  "value" VARCHAR(45) NOT NULL,
 
547
  CONSTRAINT "fk_attribute_lookup_features1"
 
548
    FOREIGN KEY("feature_id")
 
549
    REFERENCES "features"("feature_id")
 
550
);
 
551
CREATE INDEX "attributes.fk_attributes_feature_id" ON "attributes"("feature_id");
 
552
CREATE INDEX "attributes.attributes_attribute_index" ON "attributes"("attribute");
 
553
CREATE INDEX "attributes.attributes_value_index" ON "attributes"("value");
 
554
CREATE TABLE "isoformCount"(
 
555
  "isoform_id" VARCHAR(45) NOT NULL,
 
556
  "sample_name" VARCHAR(45) NOT NULL,
 
557
  "count" FLOAT,
 
558
  "variance" FLOAT,
 
559
  "uncertainty" FLOAT,
 
560
  "dispersion" FLOAT,
 
561
  "status" VARCHAR(45),
 
562
  CONSTRAINT "fk_isoformCount_isoforms1"
 
563
    FOREIGN KEY("isoform_id")
 
564
    REFERENCES "isoforms"("isoform_id"),
 
565
  CONSTRAINT "fk_isoformCount_samples1"
 
566
    FOREIGN KEY("sample_name")
 
567
    REFERENCES "samples"("sample_name")
 
568
);
 
569
CREATE INDEX "isoformCount.fk_isoformCount_isoforms1" ON "isoformCount"("isoform_id");
 
570
CREATE INDEX "isoformCount.fk_isoformCount_samples1" ON "isoformCount"("sample_name");
 
571
CREATE TABLE "isoformReplicateData"(
 
572
  "isoform_id" VARCHAR(45) NOT NULL,
 
573
  "sample_name" VARCHAR(45) NOT NULL,
 
574
  "replicate" INTEGER,
 
575
  "rep_name" VARCHAR(45) NOT NULL,
 
576
  "raw_frags" FLOAT,
 
577
  "internal_scaled_frags" FLOAT,
 
578
  "external_scaled_frags" FLOAT,
 
579
  "fpkm" FLOAT,
 
580
  "effective_length" FLOAT,
 
581
  "status" VARCHAR(45),
 
582
  CONSTRAINT "fk_geneReplicateData_replicates10"
 
583
    FOREIGN KEY("rep_name")
 
584
    REFERENCES "replicates"("rep_name"),
 
585
  CONSTRAINT "fk_isoformReplicateData_isoforms1"
 
586
    FOREIGN KEY("isoform_id")
 
587
    REFERENCES "isoforms"("isoform_id"),
 
588
  CONSTRAINT "fk_isoformReplicateData_samples1"
 
589
    FOREIGN KEY("sample_name")
 
590
    REFERENCES "samples"("sample_name")
 
591
);
 
592
CREATE INDEX "isoformReplicateData.fk_isoformReplicateData_replicates1" ON "isoformReplicateData"("rep_name");
 
593
CREATE INDEX "isoformReplicateData.fk_isoformReplicateData_isoforms1" ON "isoformReplicateData"("isoform_id");
 
594
CREATE INDEX "isoformReplicateData.fk_isoformReplicateData_samples1" ON "isoformReplicateData"("sample_name");
384
595
COMMIT;