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