BiblioteQ

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