· 6 years ago · Apr 16, 2019, 08:44 PM
1import { SQLite } from 'expo';
2
3export const _initDB = (db: SQLite.Database) => {
4 db.transaction(tx => {
5 tx.executeSql(
6 `CREATE TABLE if not exists companies (
7 uid TEXT PRIMARY KEY NOT NULL,
8 name TEXT,
9 csv_remaining INT,
10 company_status_id INT,
11 created_at TEXT,
12 csv_plan TEXT,
13 temp_unit TEXT,
14 spence INT,
15 audit_forms INT,
16 incident_module INT,
17 reminders INT
18 );
19 `,
20 [],
21 (_, x) => console.log('s company'),
22 (_, e) => console.log('f company'),
23 );
24 tx.executeSql(
25 `CREATE TABLE if not exists sites (
26 uid TEXT PRIMARY KEY NOT NULL,
27 company_uid TEXT,
28 name TEXT,
29 created_at INT,
30 location TEXT,
31 FOREIGN KEY(company_uid) REFERENCES company(uid) ON DELETE CASCADE
32 );
33 `,
34 [],
35 (_, x) => console.log('s site'),
36 (_, e) => console.log('f site'),
37 );
38 tx.executeSql(
39 `CREATE TABLE if not exists crews (
40 uid TEXT PRIMARY KEY NOT NULL,
41 created_at INT,
42 name TEXT
43 );
44 `,
45 [],
46 (_, x) => console.log('s crew'),
47 (_, e) => console.log('f crew'),
48 );
49 tx.executeSql(
50 `CREATE TABLE if not exists crew_company (
51 company_uid TEXT,
52 crew_uid TEXT,
53 FOREIGN KEY(company_uid) REFERENCES company(uid) ON DELETE CASCADE,
54 FOREIGN KEY(crew_uid) REFERENCES crew(uid) ON DELETE CASCADE,
55 PRIMARY KEY (company_uid,crew_uid)
56 );
57 `,
58 [],
59 (_, x) => console.log('s crew company'),
60 (_, e) => console.log('f crew company'),
61 );
62 tx.executeSql(
63 `CREATE TABLE if not exists users (
64 uid TEXT PRIMARY KEY NOT NULL,
65 email TEXT,
66 username TEXT,
67 fname TEXT,
68 lname TEXT,
69 has_wildcard_access INT,
70 certificats TEXT
71 );
72 `,
73 [],
74 (_, x) => console.log('s users'),
75 (_, e) => console.log('f users'),
76 );
77 tx.executeSql(
78 `CREATE TABLE if not exists user_company (
79 company_uid TEXT,
80 user_uid TEXT,
81 FOREIGN KEY(company_uid) REFERENCES company(uid) ON DELETE CASCADE,
82 FOREIGN KEY(user_uid) REFERENCES user(uid) ON DELETE CASCADE,
83 PRIMARY KEY (company_uid,user_uid)
84 );
85 `,
86 [],
87 (_, x) => console.log('s user company'),
88 (_, e) => console.log('f user company'),
89 );
90 tx.executeSql(
91 `CREATE TABLE if not exists actions (
92 uid TEXT PRIMARY KEY NOT NULL,
93 user_uid TEXT,
94 site_uid TEXT,
95 company_uid TEXT,
96 form_option_id INT,
97 submission_uid TEXT,
98 created_at INT,
99 description TEXT,
100 corrective TEXT,
101 date_due INT,
102 status INT,
103 urgent INT
104 is_assigned INT,
105 changed_offline INT,
106 is_assigned INT,
107 FOREIGN KEY(form_option_id) REFERENCES form_options(id) ON DELETE CASCADE,
108 FOREIGN KEY(site_uid) REFERENCES site(uid) ON DELETE CASCADE,
109 FOREIGN KEY(submission_uid) REFERENCES submission(uid) ON DELETE CASCADE,
110 FOREIGN KEY(user_uid) REFERENCES user(uid) ON DELETE CASCADE,
111 FOREIGN KEY(company_uid) REFERENCES companies(uid) ON DELETE CASCADE
112 );
113 `,
114 [],
115 (_, x) => console.log('s actions'),
116 (_, e) => console.log('f actions'),
117 );
118 tx.executeSql(
119 `CREATE TABLE if not exists action_relations (
120 company_uid TEXT,
121 site_uid TEXT,
122 user_uid TEXT,
123 FOREIGN KEY(company_uid) REFERENCES company(uid) ON DELETE CASCADE,
124 FOREIGN KEY(site_uid) REFERENCES site(uid) ON DELETE CASCADE,
125 FOREIGN KEY(user_uid) REFERENCES user(uid) ON DELETE CASCADE,
126 PRIMARY KEY (company_uid,user_uid,site_uid)
127 );
128 `,
129 [],
130 (_, x) => console.log('s action_rel'),
131 (_, e) => console.log('f action_rel'),
132 );
133 tx.executeSql(
134 `CREATE TABLE IF NOT EXISTS deployees(
135 uid TEXT PRIMARY KEY NOT NULL,
136 created_at INT,
137 location TEXT,
138 name TEXT,
139 status INT
140 );
141 `,
142 [],
143 (_, x) => console.log('s deployees'),
144 (_, e) => console.log('f deployees'),
145 );
146 tx.executeSql(
147 `CREATE TABLE IF NOT EXISTS deployments(
148 uid TEXT PRIMARY KEY NOT NULL,
149 deployment_all INT,
150 approval INT,
151 type TEXT
152 );
153 `,
154 [],
155 (_, x) => console.log('s deployments'),
156 (_, e) => console.log('f deployments'),
157 );
158 tx.executeSql(
159 `CREATE TABLE IF NOT EXISTS fcs(
160 uid TEXT PRIMARY KEY NOT NULL
161 );
162 `,
163 [],
164 (_, x) => console.log('s fcs'),
165 (_, e) => console.log('f fcs'),
166 );
167 tx.executeSql(
168 `CREATE TABLE IF NOT EXISTS forms(
169 uid TEXT PRIMARY KEY NOT NULL,
170 name TEXT,
171 created_at INT,
172 type TEXT
173 );
174 `,
175 [],
176 (_, x) => console.log('s form'),
177 (_, e) => console.log('f form'),
178 );
179 tx.executeSql(
180 `CREATE TABLE if not exists submissions (
181 uid TEXT PRIMARY KEY,
182 created_at INT,
183 rev INT,
184 status INT,
185 geo_location TEXT,
186 fc_uid TEXT,
187 offline_created,
188 FOREIGN KEY(fc_uid) REFERENCES fc(uid) ON DELETE CASCADE
189 );
190 `,
191 [],
192 (_, x) => console.log('s form response'),
193 (_, e) => console.log(e),
194 );
195 tx.executeSql(
196 `CREATE TABLE if not exists form_responses (
197 site_uid TEXT,
198 deployee_uid TEXT,
199 deployee_type TEXT,
200 deployment_uid TEXT,
201 fc_uid TEXT,
202 form_uid TEXT,
203 FOREIGN KEY(site_uid) REFERENCES site(uid) ON DELETE CASCADE,
204 FOREIGN KEY(fc_uid) REFERENCES fc(uid) ON DELETE CASCADE,
205 FOREIGN KEY(deployment_uid) REFERENCES deployment(uid) ON DELETE CASCADE,
206 FOREIGN KEY(form_uid) REFERENCES form(uid) ON DELETE CASCADE,
207 PRIMARY KEY (form_uid,site_uid,deployee_uid)
208 );
209 `,
210 [],
211 (_, x) => console.log('s form response'),
212 (_, e) => console.log(e),
213 );
214 tx.executeSql(
215 `CREATE TABLE IF NOT EXISTS form_options(
216 form_node_id TEXT,
217 created INT,
218 creator_node_id TEXT,
219 f_id INT,
220 id INT PRIMARY KEY NOT NULL,
221 mandatory INT,
222 mandatory_rev INT,
223 old_id TEXT,
224 parent_id INT,
225 sort INT,
226 status_id INT,
227 text TEXT,
228 text_rev TEXT,
229 title TEXT,
230 title_rev TEXT,
231 form_uid TEXT,
232 published INT,
233 FOREIGN KEY(form_uid) REFERENCES form(uid) ON DELETE CASCADE
234 );
235 `,
236 [],
237 (_, x) => console.log('s form option'),
238 (_, e) => console.log('f form option'),
239 );
240 tx.executeSql(
241 `CREATE TABLE IF NOT EXISTS option_responses(
242 option_id INT,
243 submission_uid TEXT,
244 data TEXT,
245 rev INT,
246 updated INT,
247 FOREIGN KEY(option_id) REFERENCES form(id) ON DELETE CASCADE,
248 FOREIGN KEY(submission_uid) REFERENCES submissions(uid) ON DELETE CASCADE,
249 PRIMARY KEY (option_id,submission_uid,rev)
250 );
251 `,
252 [],
253 (_, x) => console.log('s option_responses'),
254 (_, e) => console.log(e),
255 );
256 tx.executeSql(
257 `CREATE TABLE IF NOT EXISTS images(
258 form_option_id INT,
259 submission_uid TEXT,
260 rev INT,
261 id TEXT PRIMARY KEY NOT NULL,
262 FOREIGN KEY(form_option_id) REFERENCES form(id) ON DELETE CASCADE,
263 FOREIGN KEY(submission_uid) REFERENCES submissions(uid) ON DELETE CASCADE,
264 );
265 `,
266 [],
267 (_, x) => console.log('s images'),
268 (_, e) => console.log(e),
269 );
270 });
271};
272
273export const _clearDB = (db: SQLite.Database) => {
274 db.transaction(tx => {
275 tx.executeSql(
276 `
277 DROP TABLE companies;
278 `,
279 [],
280 (_, x) => console.log('s company'),
281 (_, e) => console.log('f company'),
282 );
283 tx.executeSql(
284 `
285 DROP TABLE sites;
286 `,
287 [],
288 (_, x) => console.log('s sites'),
289 (_, e) => console.log('f sites'),
290 );
291 tx.executeSql(
292 `
293 DROP TABLE crews;
294 `,
295 [],
296 (_, x) => console.log('s crews'),
297 (_, e) => console.log('f crews'),
298 );
299 tx.executeSql(
300 `
301 DROP TABLE crew_company;
302 `,
303 [],
304 (_, x) => console.log('s crew_company'),
305 (_, e) => console.log('f crew_company'),
306 );
307 tx.executeSql(
308 `
309 DROP TABLE users;
310 `,
311 [],
312 (_, x) => console.log('s users'),
313 (_, e) => console.log('f users'),
314 );
315 tx.executeSql(
316 `
317 DROP TABLE user_company;
318 `,
319 [],
320 (_, x) => console.log('s user_company'),
321 (_, e) => console.log('f user_company'),
322 );
323 tx.executeSql(
324 `
325 DROP TABLE actions;
326 `,
327 [],
328 (_, x) => console.log('s actions'),
329 (_, e) => console.log('f actions'),
330 );
331 tx.executeSql(
332 `
333 DROP TABLE action_relations;
334 `,
335 [],
336 (_, x) => console.log('s action_relations'),
337 (_, e) => console.log('f action_relations'),
338 );
339 tx.executeSql(
340 `
341 DROP TABLE deployments;
342 `,
343 [],
344 (_, x) => console.log('s deployments'),
345 (_, e) => console.log('f deployments'),
346 );
347 tx.executeSql(
348 `
349 DROP TABLE forms;
350 `,
351 [],
352 (_, x) => console.log('s forms'),
353 (_, e) => console.log('f forms'),
354 );
355 tx.executeSql(
356 `
357 DROP TABLE fcs;
358 `,
359 [],
360 (_, x) => console.log('s fcs'),
361 (_, e) => console.log('f fcs'),
362 );
363 tx.executeSql(
364 `
365 DROP TABLE form_responses;
366 `,
367 [],
368 (_, x) => console.log('s form_responses'),
369 (_, e) => console.log(e),
370 );
371 tx.executeSql(
372 `
373 DROP TABLE form_options;
374 `,
375 [],
376 (_, x) => console.log('s form_options'),
377 (_, e) => console.log('f form_options'),
378 );
379 tx.executeSql(
380 `
381 DROP TABLE submissions;
382 `,
383 [],
384 (_, x) => console.log('s submissions'),
385 (_, e) => console.log('f submissions'),
386 );
387 tx.executeSql(
388 `
389 DROP TABLE option_responses;
390 `,
391 [],
392 (_, x) => console.log('s option_responses'),
393 (_, e) => console.log('f option_responses'),
394 );
395 tx.executeSql(
396 `
397 DROP TABLE images;
398 `,
399 [],
400 (_, x) => console.log('s images'),
401 (_, e) => console.log('f images'),
402 );
403 });
404};