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 ";