-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathshadestream_schema.sql
186 lines (179 loc) · 6.94 KB
/
shadestream_schema.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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
CREATE SEQUENCE USER_SERIAL;
CREATE SEQUENCE VIDEO_SERIAL;
CREATE TABLE USER_PROFILES (
USER_ID BIGSERIAL PRIMARY KEY,
USER_NAME VARCHAR(255) NOT NULL,
USER_PASSWORD VARCHAR(256) NOT NULL,
FIRST_NAME VARCHAR(25) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
LAST_LOGIN TIMESTAMP NOT NULL,
LAST_IPADDR CHAR(45) NOT NULL,
SALT VARCHAR(250));
CREATE TABLE VIDEO_METADATA (
VIDEO_ID BIGSERIAL PRIMARY KEY,
USER_ID BIGINT NOT NULL,
SRC_VIDEO_FILE VARCHAR(255) NOT NULL,
OUT_VIDEO_FILE VARCHAR(255) NOT NULL,
NUM_FRAMES INTEGER NOT NULL,
FRAME_RATE REAL NOT NULL,
FRAME_WIDTH INTEGER NOT NULL,
FRAME_HEIGHT INTEGER NOT NULL);
CREATE TABLE VIDEO_DATA (
VIDEO_ID BIGINT NOT NULL REFERENCES VIDEO_METADATA(VIDEO_ID),
FRAME_NUM INTEGER NOT NULL,
HEAD_ROLL REAL NOT NULL,
HEAD_PITCH REAL NOT NULL,
HEAD_YAW REAL NOT NULL,
LEFT_PUPIL_OF_LOC_X REAL NOT NULL,
LEFT_PUPIL_OF_LOC_Y REAL NOT NULL,
RIGHT_PUPIL_OF_LOC_X REAL NOT NULL,
RIGHT_PUPIL_OF_LOC_Y REAL NOT NULL,
LEFT_PUPIL_FT_LOC_X REAL NOT NULL,
LEFT_PUPIL_FT_LOC_Y REAL NOT NULL,
RIGHT_PUPIL_FT_LOC_X REAL NOT NULL,
RIGHT_PUPIL_FT_LOC_Y REAL NOT NULL,
OF_DATA_POINT_1_LOC_X REAL NOT NULL,
OF_DATA_POINT_1_LOC_Y REAL NOT NULL,
OF_DATA_POINT_2_LOC_X REAL NOT NULL,
OF_DATA_POINT_2_LOC_Y REAL NOT NULL,
OF_DATA_POINT_3_LOC_X REAL NOT NULL,
OF_DATA_POINT_3_LOC_Y REAL NOT NULL,
OF_DATA_POINT_4_LOC_X REAL NOT NULL,
OF_DATA_POINT_4_LOC_Y REAL NOT NULL,
OF_DATA_POINT_5_LOC_X REAL NOT NULL,
OF_DATA_POINT_5_LOC_Y REAL NOT NULL,
OF_DATA_POINT_6_LOC_X REAL NOT NULL,
OF_DATA_POINT_6_LOC_Y REAL NOT NULL,
OF_DATA_POINT_7_LOC_X REAL NOT NULL,
OF_DATA_POINT_7_LOC_Y REAL NOT NULL,
OF_DATA_POINT_8_LOC_X REAL NOT NULL,
OF_DATA_POINT_8_LOC_Y REAL NOT NULL,
OF_DATA_POINT_9_LOC_X REAL NOT NULL,
OF_DATA_POINT_9_LOC_Y REAL NOT NULL,
OF_DATA_POINT_10_LOC_X REAL NOT NULL,
OF_DATA_POINT_10_LOC_Y REAL NOT NULL,
OF_DATA_POINT_11_LOC_X REAL NOT NULL,
OF_DATA_POINT_11_LOC_Y REAL NOT NULL,
OF_DATA_POINT_12_LOC_X REAL NOT NULL,
OF_DATA_POINT_12_LOC_Y REAL NOT NULL,
OF_DATA_POINT_13_LOC_X REAL NOT NULL,
OF_DATA_POINT_13_LOC_Y REAL NOT NULL,
OF_DATA_POINT_14_LOC_X REAL NOT NULL,
OF_DATA_POINT_14_LOC_Y REAL NOT NULL,
OF_DATA_POINT_15_LOC_X REAL NOT NULL,
OF_DATA_POINT_15_LOC_Y REAL NOT NULL,
OF_DATA_POINT_16_LOC_X REAL NOT NULL,
OF_DATA_POINT_16_LOC_Y REAL NOT NULL,
OF_DATA_POINT_17_LOC_X REAL NOT NULL,
OF_DATA_POINT_17_LOC_Y REAL NOT NULL,
OF_DATA_POINT_18_LOC_X REAL NOT NULL,
OF_DATA_POINT_18_LOC_Y REAL NOT NULL,
OF_DATA_POINT_19_LOC_X REAL NOT NULL,
OF_DATA_POINT_19_LOC_Y REAL NOT NULL,
OF_DATA_POINT_20_LOC_X REAL NOT NULL,
OF_DATA_POINT_20_LOC_Y REAL NOT NULL,
OF_DATA_POINT_21_LOC_X REAL NOT NULL,
OF_DATA_POINT_21_LOC_Y REAL NOT NULL,
OF_DATA_POINT_22_LOC_X REAL NOT NULL,
OF_DATA_POINT_22_LOC_Y REAL NOT NULL,
OF_DATA_POINT_23_LOC_X REAL NOT NULL,
OF_DATA_POINT_23_LOC_Y REAL NOT NULL,
OF_DATA_POINT_24_LOC_X REAL NOT NULL,
OF_DATA_POINT_24_LOC_Y REAL NOT NULL,
OF_DATA_POINT_25_LOC_X REAL NOT NULL,
OF_DATA_POINT_25_LOC_Y REAL NOT NULL,
OF_DATA_POINT_26_LOC_X REAL NOT NULL,
OF_DATA_POINT_26_LOC_Y REAL NOT NULL,
OF_DATA_POINT_27_LOC_X REAL NOT NULL,
OF_DATA_POINT_27_LOC_Y REAL NOT NULL,
OF_DATA_POINT_28_LOC_X REAL NOT NULL,
OF_DATA_POINT_28_LOC_Y REAL NOT NULL,
OF_DATA_POINT_29_LOC_X REAL NOT NULL,
OF_DATA_POINT_29_LOC_Y REAL NOT NULL,
OF_DATA_POINT_30_LOC_X REAL NOT NULL,
OF_DATA_POINT_30_LOC_Y REAL NOT NULL,
OF_DATA_POINT_31_LOC_X REAL NOT NULL,
OF_DATA_POINT_31_LOC_Y REAL NOT NULL,
OF_DATA_POINT_32_LOC_X REAL NOT NULL,
OF_DATA_POINT_32_LOC_Y REAL NOT NULL,
OF_DATA_POINT_33_LOC_X REAL NOT NULL,
OF_DATA_POINT_33_LOC_Y REAL NOT NULL,
OF_DATA_POINT_34_LOC_X REAL NOT NULL,
OF_DATA_POINT_34_LOC_Y REAL NOT NULL,
OF_DATA_POINT_35_LOC_X REAL NOT NULL,
OF_DATA_POINT_35_LOC_Y REAL NOT NULL,
OF_DATA_POINT_36_LOC_X REAL NOT NULL,
OF_DATA_POINT_36_LOC_Y REAL NOT NULL,
OF_DATA_POINT_37_LOC_X REAL NOT NULL,
OF_DATA_POINT_37_LOC_Y REAL NOT NULL,
OF_DATA_POINT_38_LOC_X REAL NOT NULL,
OF_DATA_POINT_38_LOC_Y REAL NOT NULL,
OF_DATA_POINT_39_LOC_X REAL NOT NULL,
OF_DATA_POINT_39_LOC_Y REAL NOT NULL,
OF_DATA_POINT_40_LOC_X REAL NOT NULL,
OF_DATA_POINT_40_LOC_Y REAL NOT NULL,
OF_DATA_POINT_41_LOC_X REAL NOT NULL,
OF_DATA_POINT_41_LOC_Y REAL NOT NULL,
OF_DATA_POINT_42_LOC_X REAL NOT NULL,
OF_DATA_POINT_42_LOC_Y REAL NOT NULL,
OF_DATA_POINT_43_LOC_X REAL NOT NULL,
OF_DATA_POINT_43_LOC_Y REAL NOT NULL,
OF_DATA_POINT_44_LOC_X REAL NOT NULL,
OF_DATA_POINT_44_LOC_Y REAL NOT NULL,
OF_DATA_POINT_45_LOC_X REAL NOT NULL,
OF_DATA_POINT_45_LOC_Y REAL NOT NULL,
OF_DATA_POINT_46_LOC_X REAL NOT NULL,
OF_DATA_POINT_46_LOC_Y REAL NOT NULL,
OF_DATA_POINT_47_LOC_X REAL NOT NULL,
OF_DATA_POINT_47_LOC_Y REAL NOT NULL,
OF_DATA_POINT_48_LOC_X REAL NOT NULL,
OF_DATA_POINT_48_LOC_Y REAL NOT NULL,
OF_DATA_POINT_49_LOC_X REAL NOT NULL,
OF_DATA_POINT_49_LOC_Y REAL NOT NULL,
OF_DATA_POINT_50_LOC_X REAL NOT NULL,
OF_DATA_POINT_50_LOC_Y REAL NOT NULL,
OF_DATA_POINT_51_LOC_X REAL NOT NULL,
OF_DATA_POINT_51_LOC_Y REAL NOT NULL,
OF_DATA_POINT_52_LOC_X REAL NOT NULL,
OF_DATA_POINT_52_LOC_Y REAL NOT NULL,
OF_DATA_POINT_53_LOC_X REAL NOT NULL,
OF_DATA_POINT_53_LOC_Y REAL NOT NULL,
OF_DATA_POINT_54_LOC_X REAL NOT NULL,
OF_DATA_POINT_54_LOC_Y REAL NOT NULL,
OF_DATA_POINT_55_LOC_X REAL NOT NULL,
OF_DATA_POINT_55_LOC_Y REAL NOT NULL,
OF_DATA_POINT_56_LOC_X REAL NOT NULL,
OF_DATA_POINT_56_LOC_Y REAL NOT NULL,
OF_DATA_POINT_57_LOC_X REAL NOT NULL,
OF_DATA_POINT_57_LOC_Y REAL NOT NULL,
OF_DATA_POINT_58_LOC_X REAL NOT NULL,
OF_DATA_POINT_58_LOC_Y REAL NOT NULL,
OF_DATA_POINT_59_LOC_X REAL NOT NULL,
OF_DATA_POINT_59_LOC_Y REAL NOT NULL,
OF_DATA_POINT_60_LOC_X REAL NOT NULL,
OF_DATA_POINT_60_LOC_Y REAL NOT NULL,
OF_DATA_POINT_61_LOC_X REAL NOT NULL,
OF_DATA_POINT_61_LOC_Y REAL NOT NULL,
OF_DATA_POINT_62_LOC_X REAL NOT NULL,
OF_DATA_POINT_62_LOC_Y REAL NOT NULL,
OF_DATA_POINT_63_LOC_X REAL NOT NULL,
OF_DATA_POINT_63_LOC_Y REAL NOT NULL,
OF_DATA_POINT_64_LOC_X REAL NOT NULL,
OF_DATA_POINT_64_LOC_Y REAL NOT NULL,
OF_DATA_POINT_65_LOC_X REAL NOT NULL,
OF_DATA_POINT_65_LOC_Y REAL NOT NULL,
OF_DATA_POINT_66_LOC_X REAL NOT NULL,
OF_DATA_POINT_66_LOC_Y REAL NOT NULL,
OF_DATA_POINT_67_LOC_X REAL NOT NULL,
OF_DATA_POINT_67_LOC_Y REAL NOT NULL,
OF_DATA_POINT_68_LOC_X REAL NOT NULL,
OF_DATA_POINT_68_LOC_Y REAL NOT NULL,
PRIMARY KEY (VIDEO_ID, FRAME_NUM));
CREATE TABLE SESSIONS (
SESSION_ID VARCHAR (255) PRIMARY KEY,
SESSION_TIME TIMESTAMP,
IPADDR VARCHAR(255));
CREATE TABLE SESSION_LOOKUP(
SESSION_ID VARCHAR (255) NOT NULL UNIQUE,
USER_NAME VARCHAR (255) UNIQUE,
FOREIGN KEY (SESSION_ID) REFERENCES SESSIONS (SESSION_ID) ON DELETE CASCADE ON UPDATE CASCADE);