BiblioteQ
Source
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
"
;
Generated by
1.8.17