forked from ga4gh/ga4gh-starter-kit-drs
-
Notifications
You must be signed in to change notification settings - Fork 0
/
create-tables.sql
50 lines (45 loc) · 1.2 KB
/
create-tables.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
CREATE TABLE drs_object (
id TEXT PRIMARY KEY,
description TEXT,
created_time TEXT,
mime_type TEXT,
name TEXT,
size UNSIGNED BIG INT,
updated_time TEXT,
version TEXT,
is_bundle INT
);
CREATE TABLE file_access_object(
id INTEGER PRIMARY KEY,
drs_object_id TEXT,
path TEXT,
FOREIGN KEY (drs_object_id) REFERENCES drs_object(id)
);
CREATE TABLE aws_s3_access_object(
id INTEGER PRIMARY KEY,
drs_object_id TEXT,
region TEXT,
bucket TEXT,
key TEXT,
FOREIGN KEY (drs_object_id) REFERENCES drs_object(id)
);
CREATE TABLE drs_object_alias (
drs_object_id TEXT,
alias TEXT,
PRIMARY KEY (drs_object_id, alias),
FOREIGN KEY(drs_object_id) REFERENCES drs_object(id)
);
CREATE TABLE drs_object_checksum (
id INTEGER PRIMARY KEY,
drs_object_id TEXT,
checksum TEXT,
type TEXT CHECK (type IN ('md5', 'etag', 'crc32c', 'trunc512', 'sha1', 'sha256')),
FOREIGN KEY(drs_object_id) REFERENCES drs_object(id)
);
CREATE TABLE drs_object_bundle (
parent_id TEXT,
child_id TEXT,
PRIMARY KEY (parent_id, child_id),
FOREIGN KEY(parent_id) REFERENCES drs_object(id),
FOREIGN KEY(child_id) REFERENCES drs_object(id)
);