-
Notifications
You must be signed in to change notification settings - Fork 2
/
samplesLite.sql
106 lines (96 loc) · 2.6 KB
/
samplesLite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
CREATE TABLE sample (
idsample INTEGER UNSIGNED NOT NULL PRIMARY KEY,
identification TEXT NULL,
sdate DATE NULL,
sampletype INTEGER UNSIGNED NULL,
source INTEGER UNSIGNED NULL,
cryotubes INTEGER UNSIGNED NULL,
pathology BOOL NULL,
rnaex BOOL NULL,
dnaex BOOL NULL,
comments TEXT NULL,
refs LONGTEXT NULL,
nitrogen TEXT NULL,
surgery INTEGER UNSIGNED NULL
);
CREATE TABLE bac (
idbac INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sdate DATE NULL,
clone TEXT NULL,
source TEXT NULL,
location TEXT NULL,
startpos INTEGER UNSIGNED NULL,
endpos INTEGER UNSIGNED NULL,
gene TEXT NULL,
genelink TEXT NULL,
dnaex TEXT NULL,
validation BOOL NULL,
pcr BOOL NULL,
refs LONGTEXT NULL
);
CREATE TABLE sampletype (
idtype INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sample_idsample INTEGER UNSIGNED NOT NULL,
stype TEXT NULL,
FOREIGN KEY(sample_idsample) REFERENCES sample(idsample)
);
CREATE TABLE suspension (
idsuspension INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sample_idsample INTEGER UNSIGNED NOT NULL,
sdate DATE NULL,
susptype TEXT NULL,
nitrogen BOOL NULL,
nitrogeninfo TEXT NULL,
colcemid TIME NULL,
tubes INTEGER UNSIGNED NULL,
location TEXT NULL,
metaphase BOOL NULL,
sky BOOL NULL,
karyotype TEXT NULL,
dna BOOL NULL,
rna BOOL NULL,
refs LONGTEXT NULL,
FOREIGN KEY(sample_idsample) REFERENCES sample(idsample)
);
CREATE TABLE source (
idsource INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sample_idsample INTEGER UNSIGNED NOT NULL,
name TEXT NULL,
FOREIGN KEY(sample_idsample)
REFERENCES sample(idsample)
);
CREATE TABLE dna (
iddna INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sample_idsample INTEGER UNSIGNED NOT NULL,
sdate DATE NULL,
concentration FLOAT NULL,
tubes INTEGER UNSIGNED NULL,
location INTEGER UNSIGNED NULL,
exmethod TEXT NULL,
rnase BOOL NULL,
refs LONGTEXT NULL,
FOREIGN KEY(sample_idsample)
REFERENCES sample(idsample)
);
CREATE TABLE glycerol (
idglycerol INTEGER UNSIGNED NOT NULL PRIMARY KEY,
bac_idbac INTEGER UNSIGNED NOT NULL,
sdate DATE NULL,
freezer INTEGER UNSIGNED NULL,
box INTEGER UNSIGNED NULL,
row_2 INTEGER UNSIGNED NULL,
FOREIGN KEY(bac_idbac)
REFERENCES bac(idbac)
);
CREATE TABLE rna (
idrna INTEGER UNSIGNED NOT NULL PRIMARY KEY,
sample_idsample INTEGER UNSIGNED NOT NULL,
sdate DATE NULL,
extraction BOOL NULL,
tubes INTEGER UNSIGNED NULL,
exmethod INTEGER UNSIGNED NULL,
dnase BOOL NULL,
refs LONGTEXT NULL,
FOREIGN KEY(sample_idsample)
REFERENCES sample(idsample)
);