Project

General

Profile

sql.log

Michael Gheith, 12/24/2014 10:55 AM

 
1
CREATE TABLE experiment(
2
    id INTEGER PRIMARY KEY AUTOINCREMENT,
3
    name CHAR(512) UNIQUE,
4
    created_on TIMESTAMP
5
);
6

    
7

    
8
CREATE TABLE tag(
9
    id INTEGER PRIMARY KEY AUTOINCREMENT,
10
    name INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
11
    created_on TIMESTAMP
12
);
13

    
14

    
15
CREATE TABLE datatier(
16
    id INTEGER PRIMARY KEY AUTOINCREMENT,
17
    name CHAR(512),
18
    experiment INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
19
    is_active CHAR(1),
20
    tag INTEGER REFERENCES tag (id) ON DELETE CASCADE,
21
    modified_on TIMESTAMP
22
);
23

    
24

    
25
CREATE TABLE type(
26
    id INTEGER PRIMARY KEY AUTOINCREMENT,
27
    name CHAR(512) UNIQUE,
28
    created_on TIMESTAMP
29
);
30

    
31

    
32
CREATE TABLE snapshot(
33
    id INTEGER PRIMARY KEY AUTOINCREMENT,
34
    type INTEGER REFERENCES type (id) ON DELETE CASCADE,
35
    experiment INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
36
    created_on TIMESTAMP
37
);
38

    
39

    
40
CREATE TABLE metric(
41
    id INTEGER PRIMARY KEY AUTOINCREMENT,
42
    snapshot INTEGER REFERENCES snapshot (id) ON DELETE CASCADE,
43
    total_count INTEGER,
44
    total_size INTEGER,
45
    datatier INTEGER REFERENCES datatier (id) ON DELETE CASCADE,
46
    tag INTEGER REFERENCES tag (id) ON DELETE CASCADE
47
);
48

    
49

    
50
CREATE TABLE project_status(
51
    id INTEGER PRIMARY KEY AUTOINCREMENT,
52
    name CHAR(512) UNIQUE,
53
    created_on TIMESTAMP
54
);
55

    
56

    
57
CREATE TABLE analysis_project(
58
    id INTEGER PRIMARY KEY AUTOINCREMENT,
59
    snapshot INTEGER REFERENCES snapshot (id) ON DELETE CASCADE,
60
    username CHAR(512),
61
    project_status INTEGER REFERENCES project_status (id) ON DELETE CASCADE,
62
    total_count INTEGER
63
);
64

    
65

    
66
CREATE TABLE analysis_project_2(
67
    id INTEGER PRIMARY KEY AUTOINCREMENT,
68
    snapshot INTEGER REFERENCES snapshot (id) ON DELETE CASCADE,
69
    project_status INTEGER REFERENCES project_status (id) ON DELETE CASCADE,
70
    total_count INTEGER
71
);
72

    
73

    
74
CREATE TABLE location_type(
75
    id INTEGER PRIMARY KEY AUTOINCREMENT,
76
    name CHAR(512) UNIQUE,
77
    created_on TIMESTAMP
78
);
79

    
80

    
81
CREATE TABLE data_file_location(
82
    id INTEGER PRIMARY KEY AUTOINCREMENT,
83
    snapshot INTEGER REFERENCES snapshot (id) ON DELETE CASCADE,
84
    location_type INTEGER REFERENCES location_type (id) ON DELETE CASCADE,
85
    total_count INTEGER
86
);
87

    
88

    
89
CREATE TABLE consumed_file_status(
90
    id INTEGER PRIMARY KEY AUTOINCREMENT,
91
    name CHAR(512) UNIQUE,
92
    created_on TIMESTAMP
93
);
94

    
95

    
96
CREATE TABLE consumed_file(
97
    id INTEGER PRIMARY KEY AUTOINCREMENT,
98
    snapshot INTEGER REFERENCES snapshot (id) ON DELETE CASCADE,
99
    consumed_file_status INTEGER REFERENCES consumed_file_status (id) ON DELETE CASCADE,
100
    total_count INTEGER
101
);
102

    
103

    
104
CREATE TABLE response_time(
105
    id INTEGER PRIMARY KEY AUTOINCREMENT,
106
    time INTEGER,
107
    experiment INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
108
    created_on TIMESTAMP
109
);
110

    
111

    
112
CREATE TABLE bookmark(
113
    id INTEGER PRIMARY KEY AUTOINCREMENT,
114
    experiment INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
115
    delta CHAR(512),
116
    datatiers CHAR(512),
117
    created_on TIMESTAMP
118
);
119

    
120

    
121
CREATE TABLE connectivity_notification(
122
    id INTEGER PRIMARY KEY AUTOINCREMENT,
123
    experiment INTEGER REFERENCES experiment (id) ON DELETE CASCADE,
124
    exception TEXT,
125
    created_on TIMESTAMP
126
);
127

    
128

    
129
CREATE TABLE scheduler_task(
130
    id INTEGER PRIMARY KEY AUTOINCREMENT,
131
    application_name CHAR(512),
132
    task_name CHAR(512),
133
    group_name CHAR(512),
134
    status CHAR(512),
135
    function_name CHAR(512),
136
    uuid CHAR(255) UNIQUE,
137
    args TEXT,
138
    vars TEXT,
139
    enabled CHAR(1),
140
    start_time TIMESTAMP,
141
    next_run_time TIMESTAMP,
142
    stop_time TIMESTAMP,
143
    repeats INTEGER,
144
    retry_failed INTEGER,
145
    period INTEGER,
146
    prevent_drift CHAR(1),
147
    timeout INTEGER,
148
    sync_output INTEGER,
149
    times_run INTEGER,
150
    times_failed INTEGER,
151
    last_run_time TIMESTAMP,
152
    assigned_worker_name CHAR(512)
153
);
154

    
155

    
156
CREATE TABLE scheduler_run(
157
    id INTEGER PRIMARY KEY AUTOINCREMENT,
158
    task_id INTEGER REFERENCES scheduler_task (id) ON DELETE CASCADE,
159
    status CHAR(512),
160
    start_time TIMESTAMP,
161
    stop_time TIMESTAMP,
162
    run_output TEXT,
163
    run_result TEXT,
164
    traceback TEXT,
165
    worker_name CHAR(512)
166
);
167

    
168

    
169
CREATE TABLE scheduler_worker(
170
    id INTEGER PRIMARY KEY AUTOINCREMENT,
171
    worker_name CHAR(255) UNIQUE,
172
    first_heartbeat TIMESTAMP,
173
    last_heartbeat TIMESTAMP,
174
    status CHAR(512),
175
    is_ticker CHAR(1),
176
    group_names TEXT
177
);