BiblioteQ

biblioteq_sqlite_create_schema.h
1 /*
2 ** The sequence table is used for generating unique integers. Please see
3 ** biblioteq_misc_functions::getSqliteUniqueId().
4 */
5
6 const char *sqlite_create_schema_text = "\
7 CREATE TABLE book \
8 ( \
9 accession_number TEXT, \
10 author TEXT NOT NULL, \
11 back_cover BYTEA, \
12 binding_type VARCHAR(32) NOT NULL, \
13 book_read INTEGER DEFAULT 0, \
14 callnumber VARCHAR(64), \
15 category TEXT NOT NULL, \
16 condition TEXT, \
17 description TEXT NOT NULL, \
18 deweynumber VARCHAR(64), \
19 edition VARCHAR(8) NOT NULL, \
20 front_cover BYTEA, \
21 id VARCHAR(32) UNIQUE, \
22 isbn13 VARCHAR(32) UNIQUE, \
23 keyword TEXT, \
24 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
25 lccontrolnumber VARCHAR(64), \
26 location TEXT NOT NULL, \
27 marc_tags TEXT, \
28 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
29 myoid BIGINT NOT NULL, \
30 originality TEXT, \
31 pdate VARCHAR(32) NOT NULL, \
32 place TEXT NOT NULL, \
33 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
34 publisher TEXT NOT NULL, \
35 quantity INTEGER NOT NULL DEFAULT 1, \
36 title TEXT NOT NULL, \
37 type VARCHAR(16) NOT NULL DEFAULT 'Book', \
38 url TEXT \
39 ); \
40 \
41 CREATE TABLE book_copy_info \
42 ( \
43 copy_number INTEGER NOT NULL DEFAULT 1, \
44 copyid VARCHAR(64) NOT NULL, \
45 myoid BIGINT NOT NULL, \
46 condition TEXT, \
47 item_oid BIGINT NOT NULL, \
48 originality TEXT, \
49 status TEXT, \
50 PRIMARY KEY(item_oid, copyid), \
51 FOREIGN KEY(item_oid) REFERENCES book(myoid) ON DELETE CASCADE \
52 ); \
53 \
54 CREATE TABLE book_files \
55 ( \
56 description TEXT, \
57 file BYTEA NOT NULL, \
58 file_digest TEXT NOT NULL, \
59 file_name TEXT NOT NULL, \
60 item_oid BIGINT NOT NULL, \
61 myoid BIGINT NOT NULL, \
62 FOREIGN KEY(item_oid) REFERENCES book(myoid) ON DELETE CASCADE, \
63 PRIMARY KEY(file_digest, item_oid) \
64 ); \
65 \
66 CREATE TABLE book_sequence \
67 ( \
68 value INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT \
69 ); \
70 \
71 CREATE TABLE cd \
72 ( \
73 accession_number TEXT, \
74 artist TEXT NOT NULL, \
75 back_cover BYTEA, \
76 category TEXT NOT NULL, \
77 cdaudio VARCHAR(32) NOT NULL DEFAULT 'Mono', \
78 cddiskcount INTEGER NOT NULL DEFAULT 1, \
79 cdformat VARCHAR(128) NOT NULL, \
80 cdrecording VARCHAR(32) NOT NULL DEFAULT 'Live', \
81 cdruntime VARCHAR(32) NOT NULL, \
82 description TEXT NOT NULL, \
83 front_cover BYTEA, \
84 id VARCHAR(32) NOT NULL PRIMARY KEY, \
85 keyword TEXT, \
86 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
87 location TEXT NOT NULL, \
88 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
89 myoid BIGINT NOT NULL, \
90 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
91 quantity INTEGER NOT NULL DEFAULT 1, \
92 rdate VARCHAR(32) NOT NULL, \
93 recording_label TEXT NOT NULL, \
94 title TEXT NOT NULL, \
95 type VARCHAR(16) NOT NULL DEFAULT 'CD' \
96 ); \
97 \
98 CREATE TABLE cd_copy_info \
99 ( \
100 copy_number INTEGER NOT NULL DEFAULT 1, \
101 copyid VARCHAR(64) NOT NULL, \
102 item_oid BIGINT NOT NULL, \
103 myoid BIGINT NOT NULL, \
104 status TEXT, \
105 PRIMARY KEY(item_oid, copyid), \
106 FOREIGN KEY(item_oid) REFERENCES cd(myoid) ON DELETE CASCADE \
107 ); \
108 \
109 CREATE TABLE cd_songs \
110 ( \
111 albumnum INTEGER NOT NULL DEFAULT 1, \
112 artist TEXT NOT NULL DEFAULT 'UNKNOWN', \
113 composer TEXT NOT NULL DEFAULT 'UNKNOWN', \
114 item_oid BIGINT NOT NULL, \
115 runtime VARCHAR(32) NOT NULL, \
116 songnum INTEGER NOT NULL DEFAULT 1, \
117 songtitle VARCHAR(256) NOT NULL, \
118 PRIMARY KEY(item_oid, albumnum, songnum), \
119 FOREIGN KEY(item_oid) REFERENCES cd(myoid) ON DELETE CASCADE \
120 ); \
121 \
122 CREATE TABLE dvd \
123 ( \
124 accession_number TEXT, \
125 back_cover BYTEA, \
126 category TEXT NOT NULL, \
127 description TEXT NOT NULL, \
128 dvdactor TEXT NOT NULL, \
129 dvdaspectratio VARCHAR(64) NOT NULL, \
130 dvddirector TEXT NOT NULL, \
131 dvddiskcount INTEGER NOT NULL DEFAULT 1, \
132 dvdformat TEXT NOT NULL, \
133 dvdrating VARCHAR(64) NOT NULL, \
134 dvdregion VARCHAR(64) NOT NULL, \
135 dvdruntime VARCHAR(32) NOT NULL, \
136 front_cover BYTEA, \
137 id VARCHAR(32) NOT NULL PRIMARY KEY, \
138 keyword TEXT, \
139 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
140 location TEXT NOT NULL, \
141 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
142 myoid BIGINT NOT NULL, \
143 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
144 quantity INTEGER NOT NULL DEFAULT 1, \
145 rdate VARCHAR(32) NOT NULL, \
146 studio TEXT NOT NULL, \
147 title TEXT NOT NULL, \
148 type VARCHAR(16) NOT NULL DEFAULT 'DVD' \
149 ); \
150 \
151 CREATE TABLE dvd_copy_info \
152 ( \
153 copy_number INTEGER NOT NULL DEFAULT 1, \
154 copyid VARCHAR(64) NOT NULL, \
155 item_oid BIGINT NOT NULL, \
156 myoid BIGINT NOT NULL, \
157 status TEXT, \
158 PRIMARY KEY(item_oid, copyid), \
159 FOREIGN KEY(item_oid) REFERENCES dvd(myoid) ON DELETE CASCADE \
160 ); \
161 \
162 CREATE TABLE grey_literature \
163 ( \
164 author TEXT NOT NULL, \
165 client TEXT, \
166 document_code_a TEXT NOT NULL, \
167 document_code_b TEXT NOT NULL, \
168 document_date TEXT NOT NULL, \
169 document_id TEXT NOT NULL PRIMARY KEY, \
170 document_status TEXT, \
171 document_title TEXT NOT NULL, \
172 document_type TEXT NOT NULL, \
173 front_cover BYTEA, \
174 job_number TEXT NOT NULL, \
175 location TEXT, \
176 myoid BIGINT UNIQUE, \
177 notes TEXT, \
178 quantity INTEGER NOT NULL DEFAULT 1, \
179 type VARCHAR(16) NOT NULL DEFAULT 'Grey Literature' \
180 ); \
181 \
182 CREATE TABLE grey_literature_files \
183 ( \
184 description TEXT, \
185 file BYTEA NOT NULL, \
186 file_digest TEXT NOT NULL, \
187 file_name TEXT NOT NULL, \
188 item_oid BIGINT NOT NULL, \
189 myoid BIGINT NOT NULL, \
190 FOREIGN KEY(item_oid) REFERENCES grey_literature(myoid) ON \
191 DELETE CASCADE, \
192 PRIMARY KEY(file_digest, item_oid) \
193 ); \
194 \
195 CREATE TABLE journal \
196 ( \
197 accession_number TEXT, \
198 back_cover BYTEA, \
199 callnumber VARCHAR(64), \
200 category TEXT NOT NULL, \
201 description TEXT NOT NULL, \
202 deweynumber VARCHAR(64), \
203 front_cover BYTEA, \
204 id VARCHAR(32), \
205 issueno INTEGER NOT NULL DEFAULT 0, \
206 issuevolume INTEGER NOT NULL DEFAULT 0, \
207 keyword TEXT, \
208 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
209 lccontrolnumber VARCHAR(64), \
210 location TEXT NOT NULL, \
211 marc_tags TEXT, \
212 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
213 myoid BIGINT NOT NULL, \
214 pdate VARCHAR(32) NOT NULL, \
215 place TEXT NOT NULL, \
216 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
217 publisher TEXT NOT NULL, \
218 quantity INTEGER NOT NULL DEFAULT 1, \
219 title TEXT NOT NULL, \
220 type VARCHAR(16) NOT NULL DEFAULT 'Journal', \
221 UNIQUE(id, issueno, issuevolume) \
222 ); \
223 \
224 CREATE TABLE journal_copy_info \
225 ( \
226 copy_number INTEGER NOT NULL DEFAULT 1, \
227 copyid VARCHAR(64) NOT NULL, \
228 item_oid BIGINT NOT NULL, \
229 myoid BIGINT NOT NULL, \
230 status TEXT, \
231 PRIMARY KEY(item_oid, copyid), \
232 FOREIGN KEY(item_oid) REFERENCES journal(myoid) ON DELETE CASCADE \
233 ); \
234 \
235 CREATE TABLE journal_files \
236 ( \
237 description TEXT, \
238 file BYTEA NOT NULL, \
239 file_digest TEXT NOT NULL, \
240 file_name TEXT NOT NULL, \
241 item_oid BIGINT NOT NULL, \
242 myoid BIGINT NOT NULL, \
243 FOREIGN KEY(item_oid) REFERENCES journal(myoid) ON DELETE CASCADE, \
244 PRIMARY KEY(file_digest, item_oid) \
245 ); \
246 \
247 CREATE TABLE magazine \
248 ( \
249 accession_number TEXT, \
250 back_cover BYTEA, \
251 callnumber VARCHAR(64), \
252 category TEXT NOT NULL, \
253 description TEXT NOT NULL, \
254 deweynumber VARCHAR(64), \
255 front_cover BYTEA, \
256 id VARCHAR(32), \
257 issueno INTEGER NOT NULL DEFAULT 0, \
258 issuevolume INTEGER NOT NULL DEFAULT 0, \
259 keyword TEXT, \
260 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
261 lccontrolnumber VARCHAR(64), \
262 location TEXT NOT NULL, \
263 marc_tags TEXT, \
264 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
265 myoid BIGINT NOT NULL, \
266 pdate VARCHAR(32) NOT NULL, \
267 place TEXT NOT NULL, \
268 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
269 publisher TEXT NOT NULL, \
270 quantity INTEGER NOT NULL DEFAULT 1, \
271 title TEXT NOT NULL, \
272 type VARCHAR(16) NOT NULL DEFAULT 'Magazine', \
273 UNIQUE(id, issuevolume, issueno) \
274 ); \
275 \
276 CREATE TABLE magazine_copy_info \
277 ( \
278 copy_number INTEGER NOT NULL DEFAULT 1, \
279 copyid VARCHAR(64) NOT NULL, \
280 item_oid BIGINT NOT NULL, \
281 myoid BIGINT NOT NULL, \
282 status TEXT, \
283 PRIMARY KEY(item_oid, copyid), \
284 FOREIGN KEY(item_oid) REFERENCES magazine(myoid) ON DELETE CASCADE \
285 ); \
286 \
287 CREATE TABLE magazine_files \
288 ( \
289 description TEXT, \
290 file BYTEA NOT NULL, \
291 file_digest TEXT NOT NULL, \
292 file_name TEXT NOT NULL, \
293 item_oid BIGINT NOT NULL, \
294 myoid BIGINT NOT NULL, \
295 FOREIGN KEY(item_oid) REFERENCES magazine(myoid) ON DELETE CASCADE, \
296 PRIMARY KEY(file_digest, item_oid) \
297 ); \
298 \
299 CREATE TABLE photograph_collection \
300 ( \
301 about TEXT, \
302 accession_number TEXT, \
303 id TEXT PRIMARY KEY NOT NULL, \
304 image BYTEA, \
305 image_scaled BYTEA, \
306 location TEXT NOT NULL, \
307 myoid BIGINT NOT NULL, \
308 notes TEXT, \
309 title TEXT NOT NULL, \
310 type VARCHAR(32) NOT NULL DEFAULT 'Photograph Collection' \
311 ); \
312 \
313 CREATE TABLE photograph \
314 ( \
315 accession_number TEXT, \
316 callnumber VARCHAR(64), \
317 collection_oid BIGINT NOT NULL, \
318 copyright TEXT NOT NULL, \
319 creators TEXT NOT NULL, \
320 format TEXT, \
321 id TEXT NOT NULL, \
322 image BYTEA, \
323 image_scaled BYTEA, \
324 medium TEXT NOT NULL, \
325 myoid BIGINT NOT NULL, \
326 notes TEXT, \
327 other_number TEXT, \
328 pdate VARCHAR(32) NOT NULL, \
329 quantity INTEGER NOT NULL DEFAULT 1, \
330 reproduction_number TEXT NOT NULL, \
331 subjects TEXT, \
332 title TEXT NOT NULL, \
333 PRIMARY KEY(id, collection_oid), \
334 FOREIGN KEY(collection_oid) REFERENCES \
335 photograph_collection(myoid) ON \
336 DELETE CASCADE \
337 ); \
338 \
339 CREATE TABLE videogame \
340 ( \
341 accession_number TEXT, \
342 back_cover BYTEA, \
343 description TEXT NOT NULL, \
344 developer TEXT NOT NULL, \
345 front_cover BYTEA, \
346 genre TEXT NOT NULL, \
347 id VARCHAR(32) NOT NULL PRIMARY KEY, \
348 keyword TEXT, \
349 language VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
350 location TEXT NOT NULL, \
351 monetary_units VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN', \
352 myoid BIGINT NOT NULL, \
353 place TEXT NOT NULL, \
354 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
355 publisher TEXT NOT NULL, \
356 quantity INTEGER NOT NULL DEFAULT 1, \
357 rdate VARCHAR(32) NOT NULL, \
358 title TEXT NOT NULL, \
359 type VARCHAR(16) NOT NULL DEFAULT 'Video Game', \
360 vgmode VARCHAR(16) NOT NULL DEFAULT 'Multiplayer', \
361 vgplatform VARCHAR(64) NOT NULL, \
362 vgrating VARCHAR(64) NOT NULL \
363 ); \
364 \
365 CREATE TABLE videogame_copy_info \
366 ( \
367 copy_number INTEGER NOT NULL DEFAULT 1, \
368 copyid VARCHAR(64) NOT NULL, \
369 item_oid BIGINT NOT NULL, \
370 myoid BIGINT NOT NULL, \
371 status TEXT, \
372 PRIMARY KEY(item_oid, copyid), \
373 FOREIGN KEY(item_oid) REFERENCES videogame(myoid) ON \
374 DELETE CASCADE \
375 ); \
376 \
377 CREATE TRIGGER book_purge_trigger AFTER DELETE ON book \
378 FOR EACH row \
379 BEGIN \
380 DELETE FROM book_copy_info WHERE item_oid = old.myoid; \
381 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
382 DELETE FROM member_history WHERE item_oid = old.myoid AND \
383 type = old.type; \
384 END; \
385 \
386 CREATE TRIGGER cd_purge_trigger AFTER DELETE ON cd \
387 FOR EACH row \
388 BEGIN \
389 DELETE FROM cd_copy_info WHERE item_oid = old.myoid; \
390 DELETE FROM cd_songs WHERE item_oid = old.myoid; \
391 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
392 DELETE FROM member_history WHERE item_oid = old.myoid AND \
393 type = old.type; \
394 END; \
395 \
396 CREATE TRIGGER dvd_purge_trigger AFTER DELETE ON dvd \
397 FOR EACH row \
398 BEGIN \
399 DELETE FROM dvd_copy_info WHERE item_oid = old.myoid; \
400 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
401 DELETE FROM member_history WHERE item_oid = old.myoid AND \
402 type = old.type; \
403 END; \
404 \
405 CREATE TRIGGER grey_literature_purge_trigger AFTER DELETE ON \
406 grey_literature \
407 FOR EACH row \
408 BEGIN \
409 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
410 DELETE FROM member_history WHERE item_oid = old.myoid AND \
411 type = old.type; \
412 END; \
413 \
414 CREATE TRIGGER journal_purge_trigger AFTER DELETE ON journal \
415 FOR EACH row \
416 BEGIN \
417 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
418 DELETE FROM journal_copy_info WHERE item_oid = old.myoid; \
419 DELETE FROM member_history WHERE item_oid = old.myoid AND \
420 type = old.type; \
421 END; \
422 \
423 CREATE TRIGGER magazine_purge_trigger AFTER DELETE ON magazine \
424 FOR EACH row \
425 BEGIN \
426 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
427 DELETE FROM magazine_copy_info WHERE item_oid = old.myoid; \
428 DELETE FROM member_history WHERE item_oid = old.myoid AND \
429 type = old.type; \
430 END; \
431 \
432 CREATE TRIGGER videogame_purge_trigger AFTER DELETE ON videogame \
433 FOR EACH row \
434 BEGIN \
435 DELETE FROM item_borrower WHERE item_oid = old.myoid; \
436 DELETE FROM member_history WHERE item_oid = old.myoid AND \
437 type = old.type; \
438 DELETE FROM videogame_copy_info WHERE item_oid = old.myoid; \
439 END; \
440 \
441 CREATE TABLE item_borrower \
442 ( \
443 copy_number INTEGER NOT NULL DEFAULT 1, \
444 copyid VARCHAR(64) NOT NULL, \
445 duedate VARCHAR(32) NOT NULL, \
446 item_oid BIGINT NOT NULL, \
447 memberid VARCHAR(16) NOT NULL, \
448 myoid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
449 reserved_by VARCHAR(128) NOT NULL, \
450 reserved_date VARCHAR(32) NOT NULL, \
451 type VARCHAR(16) NOT NULL, \
452 FOREIGN KEY(memberid) REFERENCES member ON DELETE RESTRICT \
453 ); \
454 \
455 CREATE TABLE member \
456 ( \
457 city VARCHAR(256) NOT NULL, \
458 comments TEXT, \
459 dob VARCHAR(32) NOT NULL, \
460 email VARCHAR(128), \
461 expiration_date VARCHAR(32) NOT NULL, \
462 first_name VARCHAR(128) NOT NULL, \
463 general_registration_number TEXT, \
464 last_name VARCHAR(128) NOT NULL, \
465 maximum_reserved_books INTEGER NOT NULL DEFAULT 0, \
466 memberclass TEXT, \
467 memberid VARCHAR(16) NOT NULL PRIMARY KEY DEFAULT 1, \
468 membersince VARCHAR(32) NOT NULL, \
469 middle_init VARCHAR(1), \
470 overdue_fees NUMERIC(10, 2) NOT NULL DEFAULT 0.00, \
471 sex VARCHAR(32) NOT NULL DEFAULT 'Private', \
472 state_abbr VARCHAR(16) NOT NULL DEFAULT 'N/A', \
473 street VARCHAR(256) NOT NULL, \
474 telephone_num VARCHAR(32), \
475 zip VARCHAR(16) NOT NULL DEFAULT 'N/A' \
476 ); \
477 \
478 CREATE TABLE member_history \
479 ( \
480 memberid VARCHAR(16) NOT NULL, \
481 item_oid BIGINT NOT NULL, \
482 copyid VARCHAR(64) NOT NULL, \
483 reserved_date VARCHAR(32) NOT NULL, \
484 duedate VARCHAR(32) NOT NULL, \
485 returned_date VARCHAR(32) NOT NULL, \
486 myoid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
487 reserved_by VARCHAR(128) NOT NULL, \
488 type VARCHAR(16) NOT NULL, \
489 FOREIGN KEY(memberid) REFERENCES member(memberid) ON DELETE CASCADE \
490 ); \
491 \
492 CREATE TRIGGER member_history_trigger AFTER DELETE ON member \
493 FOR EACH row \
494 BEGIN \
495 DELETE FROM member_history WHERE memberid = old.memberid; \
496 END; \
497 \
498 CREATE TABLE book_binding_types \
499 ( \
500 binding_type TEXT NOT NULL PRIMARY KEY \
501 ); \
502 \
503 CREATE TABLE cd_formats \
504 ( \
505 cd_format TEXT NOT NULL PRIMARY KEY \
506 ); \
507 \
508 CREATE TABLE dvd_aspect_ratios \
509 ( \
510 dvd_aspect_ratio TEXT NOT NULL PRIMARY KEY \
511 ); \
512 \
513 CREATE TABLE dvd_ratings \
514 ( \
515 dvd_rating TEXT NOT NULL PRIMARY KEY \
516 ); \
517 \
518 CREATE TABLE dvd_regions \
519 ( \
520 dvd_region TEXT NOT NULL PRIMARY KEY \
521 ); \
522 \
523 CREATE TABLE grey_literature_types \
524 ( \
525 document_type TEXT NOT NULL PRIMARY KEY \
526 ); \
527 \
528 CREATE TABLE languages \
529 ( \
530 language TEXT NOT NULL PRIMARY KEY \
531 ); \
532 \
533 CREATE TABLE locations \
534 ( \
535 location TEXT NOT NULL, \
536 type VARCHAR(32) NOT NULL, \
537 PRIMARY KEY(location, type) \
538 ); \
539 \
540 CREATE TABLE minimum_days \
541 ( \
542 days INTEGER NOT NULL, \
543 type VARCHAR(16) NOT NULL PRIMARY KEY \
544 ); \
545 \
546 CREATE TABLE monetary_units \
547 ( \
548 monetary_unit TEXT NOT NULL PRIMARY KEY \
549 ); \
550 \
551 CREATE TABLE sequence \
552 ( \
553 value INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT \
554 ); \
555 \
556 CREATE TABLE videogame_platforms \
557 ( \
558 videogame_platform TEXT NOT NULL PRIMARY KEY \
559 ); \
560 \
561 CREATE TABLE videogame_ratings \
562 ( \
563 videogame_rating TEXT NOT NULL PRIMARY KEY \
564 ); \
565 ";