Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 788 lines (740 sloc) 17.27 kb
eac3a7b1 »
2001-03-12 Database definition file, checked into cvs to make keeping database c…
1 # MySQL dump 7.1
2 #
3 # Host: localhost Database: c4test
4 #--------------------------------------------------------
5 # Server version 3.22.32-log
6
7 #
8 # Table structure for table 'accountlines'
9 #
10 CREATE TABLE accountlines (
11 borrowernumber int(11) DEFAULT '0' NOT NULL,
12 accountno smallint(6) DEFAULT '0' NOT NULL,
13 itemnumber int(11),
14 date date,
15 amount decimal(28,6),
16 description text,
17 dispute text,
18 accounttype varchar(5),
19 amountoutstanding decimal(28,6),
20 timestamp timestamp(14),
21 KEY acctsborridx (borrowernumber),
22 KEY timeidx (timestamp)
23 );
24
25 #
26 # Table structure for table 'accountoffsets'
27 #
28 CREATE TABLE accountoffsets (
29 borrowernumber int(11) DEFAULT '0' NOT NULL,
30 accountno smallint(6) DEFAULT '0' NOT NULL,
31 offsetaccount smallint(6) DEFAULT '0' NOT NULL,
32 offsetamount decimal(28,6),
33 timestamp timestamp(14)
34 );
35
36 #
37 # Table structure for table 'additionalauthors'
38 #
39 CREATE TABLE additionalauthors (
40 author text NOT NULL,
41 biblionumber int(11) DEFAULT '0' NOT NULL,
42 KEY bibidx (biblionumber)
43 );
44
45 #
46 # Table structure for table 'aqbookfund'
47 #
48 CREATE TABLE aqbookfund (
49 bookfundid varchar(5) DEFAULT '' NOT NULL,
50 bookfundname text,
51 bookfundgroup varchar(5)
52 );
53
54 #
55 # Table structure for table 'aqbooksellers'
56 #
57 CREATE TABLE aqbooksellers (
58 id int(11),
59 name text,
60 address1 text,
61 address2 text,
62 address3 text,
63 address4 text,
64 phone varchar(30),
65 accountnumber text,
66 othersupplier text,
67 currency char(3) DEFAULT '' NOT NULL,
68 deliverydays smallint(6),
69 followupdays smallint(6),
70 followupscancel smallint(6),
71 specialty text,
72 booksellerfax text,
73 notes text,
74 bookselleremail text,
75 booksellerurl text,
76 contact varchar(100),
77 postal text,
78 url varchar(255),
79 contpos varchar(100),
80 contphone varchar(100),
81 contfax varchar(100),
82 contaltphone varchar(100),
83 contemail varchar(100),
84 contnotes text,
85 active tinyint(4),
86 listprice varchar(5),
87 invoiceprice varchar(5),
88 gstreg tinyint(4),
89 listincgst tinyint(4),
90 invoiceincgst tinyint(4),
91 discount float(6,4),
92 fax varchar(50)
93 );
94
95 #
96 # Table structure for table 'aqbudget'
97 #
98 CREATE TABLE aqbudget (
99 bookfundid char(5) DEFAULT '' NOT NULL,
100 startdate date DEFAULT '0000-00-00' NOT NULL,
101 enddate date,
102 budgetamount decimal(13,2)
103 );
104
105 #
106 # Table structure for table 'aqorderbreakdown'
107 #
108 CREATE TABLE aqorderbreakdown (
109 ordernumber int(11),
110 linenumber int(11),
111 branchcode char(4),
112 bookfundid char(5) DEFAULT '' NOT NULL,
113 allocation smallint(6)
114 );
115
116 #
117 # Table structure for table 'aqorderdelivery'
118 #
119 CREATE TABLE aqorderdelivery (
120 ordernumber date DEFAULT '0000-00-00' NOT NULL,
121 deliverynumber smallint(6) DEFAULT '0' NOT NULL,
122 deliverydate varchar(18),
123 qtydelivered smallint(6),
124 deliverycomments text
125 );
126
127 #
128 # Table structure for table 'aqorders'
129 #
130 CREATE TABLE aqorders (
131 ordernumber int(11) DEFAULT '0' NOT NULL auto_increment,
132 biblionumber int(11),
133 title text,
134 requisitionedby varchar(10),
135 authorisedby varchar(10),
136 booksellerid varchar(10) DEFAULT '' NOT NULL,
137 deliverydays smallint(6),
138 followupdays smallint(6),
139 numberfollowupsallowed smallint(6),
140 numberfollowupssent smallint(6),
141 entrydate date,
142 dateprinted date,
143 quantity smallint(6),
144 currency char(3),
145 listprice decimal(28,6),
146 totalamount decimal(28,6),
147 datereceived date,
148 booksellerinvoicenumber text,
149 freight decimal(28,6),
150 unitprice decimal(28,6),
151 quantityreceived smallint(6),
152 sourced text,
153 cancelledby varchar(10),
154 quantityreceiveddamaged smallint(6),
155 datecancellationprinted date,
156 notes text,
157 supplierreference text,
158 purchaseordernumber text,
159 subscription tinyint(1),
160 subscriptionfrom date,
161 subscriptionto date,
162 serialid varchar(30),
163 basketno int(11),
164 biblioitemnumber int(11),
165 timestamp timestamp(14),
166 rrp decimal(13,2),
167 ecost decimal(13,2),
168 gst decimal(13,2),
169 PRIMARY KEY (ordernumber)
170 );
171
172 #
173 # Table structure for table 'biblio'
174 #
175 CREATE TABLE biblio (
176 biblionumber int(11) DEFAULT '0' NOT NULL,
177 author text,
178 title text,
179 unititle text,
180 notes text,
181 serial tinyint(1),
182 seriestitle text,
183 copyrightdate smallint(6),
184 timestamp timestamp(14),
185 KEY blbnoidx (biblionumber),
186 PRIMARY KEY (biblionumber)
187 );
188
189 #
190 # Table structure for table 'biblioanalysis'
191 #
192 CREATE TABLE biblioanalysis (
193 analyticaltitle text,
194 biblionumber int(11) DEFAULT '0' NOT NULL,
195 analyticalauthor text
196 );
197
198 #
199 # Table structure for table 'biblioitems'
200 #
201 CREATE TABLE biblioitems (
202 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
203 biblionumber int(11) DEFAULT '0' NOT NULL,
204 volume text,
205 number text,
206 classification varchar(25),
207 itemtype varchar(4),
208 isbn varchar(14),
209 issn varchar(9),
210 dewey double(8,6),
211 subclass char(3),
212 publicationyear smallint(6),
213 publishercode varchar(255),
214 volumedate date,
215 volumeddesc varchar(255),
216 timestamp timestamp(14),
217 illus varchar(255),
218 pages varchar(255),
219 notes text,
220 size varchar(255),
221 place varchar(255),
222 KEY bibinoidx (biblioitemnumber),
223 KEY bibnoidx (biblionumber),
224 PRIMARY KEY (biblioitemnumber)
225 );
226
227 #
228 # Table structure for table 'bibliosubject'
229 #
230 CREATE TABLE bibliosubject (
231 subject text NOT NULL,
232 biblionumber int(11) DEFAULT '0' NOT NULL
233 );
234
235 #
236 # Table structure for table 'bibliosubtitle'
237 #
238 CREATE TABLE bibliosubtitle (
239 subtitle text NOT NULL,
240 biblionumber int(11) DEFAULT '0' NOT NULL,
241 KEY bibsubidx (biblionumber)
242 );
243
244 #
245 # Table structure for table 'borexp'
246 #
247 CREATE TABLE borexp (
248 borrowernumber int(11),
249 newexp date
250 );
251
252 #
253 # Table structure for table 'borrowers'
254 #
255 CREATE TABLE borrowers (
256 borrowernumber int(11) DEFAULT '0' NOT NULL,
257 cardnumber varchar(9) DEFAULT '' NOT NULL,
258 surname text NOT NULL,
259 firstname text NOT NULL,
260 title text,
261 othernames text,
262 initials text NOT NULL,
263 streetaddress text NOT NULL,
264 suburb text,
265 city text NOT NULL,
266 phone text NOT NULL,
267 emailaddress text,
268 faxnumber text,
269 altstreetaddress text,
270 altsuburb text,
271 altcity text,
272 altphone text,
273 dateofbirth date,
274 branchcode varchar(4) DEFAULT '' NOT NULL,
275 categorycode char(2),
276 dateenrolled date,
277 gonenoaddress tinyint(1),
278 lost tinyint(1),
279 debarred tinyint(1),
280 studentnumber text,
281 school text,
282 contactname text,
283 borrowernotes text,
284 guarantor int(11),
285 area char(2),
286 ethnicity varchar(50),
287 ethnotes varchar(255),
288 sex char(1),
289 expiry date,
290 altnotes varchar(255),
291 altrelationship varchar(100),
292 streetcity text,
293 phoneday varchar(50),
294 preferredcont char(1),
295 physstreet varchar(100),
296 KEY borrowernumber (borrowernumber),
297 KEY cardnumber (cardnumber)
298 );
299
300 #
301 # Table structure for table 'branchcategories'
302 #
303 CREATE TABLE branchcategories (
304 categorycode char(2) DEFAULT '' NOT NULL,
305 branchcode char(4) DEFAULT '' NOT NULL,
306 branchholding int(11)
307 );
308
309 #
310 # Table structure for table 'branches'
311 #
312 CREATE TABLE branches (
313 branchcode varchar(4) DEFAULT '' NOT NULL,
314 branchname text NOT NULL,
315 branchaddress1 text,
316 branchaddress2 text,
317 branchaddress3 text,
318 branchphone text,
319 branchfax text,
320 branchemail text,
321 issuing tinyint(4),
322 UNIQUE branchcode (branchcode)
323 );
324
325 #
326 # Table structure for table 'branchtransfers'
327 #
328 CREATE TABLE branchtransfers (
329 itemnumber int(11) DEFAULT '0' NOT NULL,
330 datesent date DEFAULT '0000-00-00' NOT NULL,
331 frombranch varchar(4),
332 datearrived date,
333 tobranch varchar(4),
334 comments text
335 );
336
337 #
338 # Table structure for table 'catalogueentry'
339 #
340 CREATE TABLE catalogueentry (
341 catalogueentry text NOT NULL,
342 entrytype char(2),
343 see text,
344 seealso text,
345 seeinstead text,
346 biblionumber int(11)
347 );
348
349 #
350 # Table structure for table 'categories'
351 #
352 CREATE TABLE categories (
353 categorycode char(2) DEFAULT '' NOT NULL,
354 description text,
355 enrolmentperiod smallint(6),
356 upperagelimit smallint(6),
357 dateofbirthrequired tinyint(1),
358 finetype varchar(30),
359 bulk tinyint(1),
360 enrolmentfee decimal(28,6),
361 overduenoticerequired tinyint(1),
362 issuelimit smallint(6),
363 reservefee decimal(28,6),
364 UNIQUE categorycode (categorycode)
365 );
366
367 #
368 # Table structure for table 'categoryitem'
369 #
370 CREATE TABLE categoryitem (
371 categorycode char(2) DEFAULT '' NOT NULL,
372 itemtype varchar(4) DEFAULT '' NOT NULL,
373 restrictedtype tinyint(1),
374 rentaldiscount decimal(28,6),
375 reservecharge decimal(28,6),
376 fine decimal(28,6),
377 firstremind int(11),
378 chargeperiod int(11),
379 accountsent int(11),
380 chargename varchar(100)
381 );
382
383 #
384 # Table structure for table 'classification'
385 #
386 CREATE TABLE classification (
387 classification varchar(12) DEFAULT '' NOT NULL
388 );
389
390 #
391 # Table structure for table 'currency'
392 #
393 CREATE TABLE currency (
394 currency varchar(10),
395 rate float(7,5)
396 );
397
398 #
399 # Table structure for table 'deletedbiblio'
400 #
401 CREATE TABLE deletedbiblio (
402 biblionumber int(11) DEFAULT '0' NOT NULL,
403 author text,
404 title text,
405 unititle text,
406 notes text,
407 serial tinyint(1),
408 seriestitle text,
409 copyrightdate smallint(6),
410 timestamp timestamp(14),
411 KEY blbnoidx (biblionumber),
412 PRIMARY KEY (biblionumber)
413 );
414
415 #
416 # Table structure for table 'deletedbiblioitems'
417 #
418 CREATE TABLE deletedbiblioitems (
419 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
420 biblionumber int(11) DEFAULT '0' NOT NULL,
421 volume text,
422 number text,
423 classification varchar(25),
424 itemtype varchar(4),
425 isbn varchar(14),
426 issn varchar(9),
427 dewey double(8,6),
428 subclass char(3),
429 publicationyear smallint(6),
430 publishercode varchar(255),
431 volumedate date,
432 volumeddesc varchar(255),
433 timestamp timestamp(14),
434 illus varchar(255),
435 pages varchar(255),
436 notes text,
437 size varchar(255),
438 KEY bibinoidx (biblioitemnumber),
439 KEY bibnoidx (biblionumber),
440 PRIMARY KEY (biblioitemnumber)
441 );
442
443 #
444 # Table structure for table 'deletedborrowers'
445 #
446 CREATE TABLE deletedborrowers (
447 borrowernumber int(11) DEFAULT '0' NOT NULL,
448 cardnumber varchar(9) DEFAULT '' NOT NULL,
449 surname text NOT NULL,
450 firstname text NOT NULL,
451 title text,
452 othernames text,
453 initials text NOT NULL,
454 streetaddress text NOT NULL,
455 suburb text,
456 city text NOT NULL,
457 phone text NOT NULL,
458 emailaddress text,
459 faxnumber text,
460 altstreetaddress text,
461 altsuburb text,
462 altcity text,
463 altphone text,
464 dateofbirth date,
465 branchcode varchar(4) DEFAULT '' NOT NULL,
466 categorycode char(2),
467 dateenrolled date,
468 gonenoaddress tinyint(1),
469 lost tinyint(1),
470 debarred tinyint(1),
471 studentnumber text,
472 school text,
473 contactname text,
474 borrowernotes text,
475 guarantor int(11),
476 area char(2),
477 ethnicity varchar(50),
478 ethnotes varchar(255),
479 sex char(1),
480 expiry date,
481 altnotes varchar(255),
482 altrelationship varchar(100),
483 streetcity text,
484 phoneday varchar(50),
485 preferredcont varchar(100),
486 physstreet varchar(100),
487 KEY borrowernumber (borrowernumber),
488 KEY cardnumber (cardnumber)
489 );
490
491 #
492 # Table structure for table 'deleteditems'
493 #
494 CREATE TABLE deleteditems (
495 itemnumber int(11) DEFAULT '0' NOT NULL,
496 biblionumber int(11) DEFAULT '0' NOT NULL,
497 multivolumepart varchar(30),
498 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
499 barcode varchar(9) DEFAULT '' NOT NULL,
500 dateaccessioned date,
501 booksellerid varchar(10),
502 homebranch varchar(4),
503 price decimal(28,6),
504 replacementprice decimal(28,6),
505 replacementpricedate date,
506 datelastborrowed date,
507 datelastseen date,
508 multivolume tinyint(1),
509 stack tinyint(1),
510 notforloan tinyint(1),
511 itemlost tinyint(1),
512 wthdrawn tinyint(1),
513 bulk varchar(30),
514 issues smallint(6),
515 renewals smallint(6),
516 reserves smallint(6),
517 restricted tinyint(1),
518 binding decimal(28,6),
519 itemnotes text,
520 holdingbranch varchar(4),
521 interim tinyint(1),
522 timestamp timestamp(14),
523 KEY itembarcodeidx (barcode),
524 KEY itembinoidx (biblioitemnumber),
525 KEY itembibnoidx (biblionumber),
526 PRIMARY KEY (itemnumber),
527 UNIQUE barcode (barcode)
528 );
529
530 #
531 # Table structure for table 'issues'
532 #
533 CREATE TABLE issues (
534 borrowernumber int(11) DEFAULT '0' NOT NULL,
535 itemnumber int(11) DEFAULT '0' NOT NULL,
536 date_due date,
537 branchcode char(4),
538 issuingbranch char(18),
539 returndate date,
540 lastreneweddate date,
541 return char(4),
542 renewals tinyint(4),
543 timestamp timestamp(14),
544 KEY issuesborridx (borrowernumber),
545 KEY issuesitemidx (itemnumber),
546 KEY bordate (borrowernumber,timestamp)
547 );
548
549 #
550 # Table structure for table 'items'
551 #
552 CREATE TABLE items (
553 itemnumber int(11) DEFAULT '0' NOT NULL,
554 biblionumber int(11) DEFAULT '0' NOT NULL,
555 multivolumepart varchar(30),
556 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
557 barcode varchar(9) DEFAULT '' NOT NULL,
558 dateaccessioned date,
559 booksellerid varchar(10),
560 homebranch varchar(4),
561 price decimal(8,2),
562 replacementprice decimal(8,2),
563 replacementpricedate date,
564 datelastborrowed date,
565 datelastseen date,
566 multivolume tinyint(1),
567 stack tinyint(1),
568 notforloan tinyint(1),
569 itemlost tinyint(1),
570 wthdrawn tinyint(1),
571 bulk varchar(30),
572 issues smallint(6),
573 renewals smallint(6),
574 reserves smallint(6),
575 restricted tinyint(1),
576 binding decimal(28,6),
577 itemnotes text,
578 holdingbranch varchar(4),
177d7f99 »
2001-05-17 Changed the interim field in the items table, to be paidfor
579 paidfor text,
eac3a7b1 »
2001-03-12 Database definition file, checked into cvs to make keeping database c…
580 timestamp timestamp(14),
581 KEY itembarcodeidx (barcode),
582 KEY itembinoidx (biblioitemnumber),
583 KEY itembibnoidx (biblionumber),
584 PRIMARY KEY (itemnumber),
585 UNIQUE barcode (barcode)
586 );
587
588 #
589 # Table structure for table 'itemsprices'
590 #
591 CREATE TABLE itemsprices (
592 itemnumber int(11),
593 price1 decimal(28,6),
594 price2 decimal(28,6)
595 );
596
597 #
598 # Table structure for table 'itemtypes'
599 #
600 CREATE TABLE itemtypes (
601 itemtype varchar(4) DEFAULT '' NOT NULL,
602 description text,
603 loanlength smallint(6),
604 renewalsallowed smallint(6),
605 rentalcharge double(16,4),
606 UNIQUE itemtype (itemtype)
607 );
608
609 #
610 # Table structure for table 'multipart'
611 #
612 CREATE TABLE multipart (
613 itemnumber int(11) DEFAULT '0' NOT NULL,
614 other int(11) DEFAULT '0' NOT NULL
615 );
616
617 #
618 # Table structure for table 'multivolume'
619 #
620 CREATE TABLE multivolume (
621 biblionumber int(11) DEFAULT '0' NOT NULL,
622 multivolumepart varchar(30) DEFAULT '' NOT NULL
623 );
624
625 #
626 # Table structure for table 'newitems'
627 #
628 CREATE TABLE newitems (
629 itemnumber int(11) DEFAULT '0' NOT NULL,
630 publishercode varchar(18),
631 biblionumber int(11) DEFAULT '0' NOT NULL,
632 multivolumepart varchar(30),
633 barcode varchar(9) DEFAULT '' NOT NULL,
634 dateaccessioned date,
635 booksellerid varchar(10),
636 homebranch varchar(4),
637 price decimal(28,6),
638 replacementprice decimal(28,6),
639 replacementpricedate date,
640 datelastborrowed date,
641 datelastseen date,
642 multivolume tinyint(1),
643 stack tinyint(1),
644 notforloan tinyint(1),
645 itemlost tinyint(1),
646 wthdrawn tinyint(1),
647 bulk varchar(30),
648 issues smallint(6),
649 renewals smallint(6),
650 reserves smallint(6),
651 restricted tinyint(1),
652 binding decimal(28,6),
653 itemnotes text,
654 holdingbranch varchar(4),
655 interim tinyint(1),
656 volume text,
657 number text,
658 classification varchar(12),
659 itemtype varchar(4),
660 isbn varchar(14),
661 issn varchar(9),
662 dewey double(16,4),
663 subclass char(3),
664 publicationyear smallint(6),
665 KEY itembarcodeidx (barcode),
666 KEY itembibnoidx (biblionumber),
667 PRIMARY KEY (itemnumber)
668 );
669
670 #
671 # Table structure for table 'printers'
672 #
673 CREATE TABLE printers (
674 printername char(40),
675 printqueue char(20),
676 printtype char(20)
677 );
678
679 #
680 # Table structure for table 'procedures'
681 #
682 CREATE TABLE procedures (
683 proccode varchar(4),
684 procdesc text,
685 proclevel smallint(6)
686 );
687
688 #
689 # Table structure for table 'publisher'
690 #
691 CREATE TABLE publisher (
692 publishercode varchar(18) DEFAULT '' NOT NULL,
693 publishername text NOT NULL
694 );
695
696 #
697 # Table structure for table 'reserveconstraints'
698 #
699 CREATE TABLE reserveconstraints (
700 borrowernumber int(11) DEFAULT '0' NOT NULL,
701 reservedate date DEFAULT '0000-00-00' NOT NULL,
702 biblionumber int(11) DEFAULT '0' NOT NULL,
703 biblioitemnumber int(11),
704 timestamp timestamp(14)
705 );
706
707 #
708 # Table structure for table 'reserves'
709 #
710 CREATE TABLE reserves (
711 borrowernumber int(11) DEFAULT '0' NOT NULL,
712 reservedate date DEFAULT '0000-00-00' NOT NULL,
713 biblionumber int(11) DEFAULT '0' NOT NULL,
714 constrainttype char(1),
715 branchcode varchar(4),
716 notificationdate date,
717 reminderdate date,
718 cancellationdate date,
719 reservenotes text,
720 priority smallint(6),
721 found char(1),
722 timestamp timestamp(14),
723 itemnumber int(11)
724 );
725
726 #
727 # Table structure for table 'searchstats'
728 #
729 CREATE TABLE searchstats (
730 time datetime,
731 searchstring text
732 );
733
734 #
735 # Table structure for table 'serialissues'
736 #
737 CREATE TABLE serialissues (
738 biblionumber int(11) DEFAULT '0' NOT NULL,
739 volume varchar(20) DEFAULT '' NOT NULL,
740 number varchar(20) DEFAULT '' NOT NULL,
741 ordernumber smallint(6),
742 issuedate varchar(20),
743 received varchar(18)
744 );
745
746 #
747 # Table structure for table 'statistics'
748 #
749 CREATE TABLE statistics (
750 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
751 branch varchar(4),
752 proccode varchar(4),
753 value double(16,4),
754 type varchar(16),
755 other text,
756 usercode varchar(10),
757 itemnumber int(11),
758 itemtype varchar(4),
759 KEY timeidx (datetime)
760 );
761
762 #
763 # Table structure for table 'stopwords'
764 #
765 CREATE TABLE stopwords (
766 word varchar(255)
767 );
768
769 #
770 # Table structure for table 'systempreferences'
771 #
772 CREATE TABLE systempreferences (
773 variable char(50) DEFAULT '' NOT NULL,
774 value char(200),
775 PRIMARY KEY (variable)
776 );
777
778 #
779 # Table structure for table 'users'
780 #
781 CREATE TABLE users (
782 usercode varchar(10),
783 username text,
784 password text,
785 level smallint(6)
786 );
787
Something went wrong with that request. Please try again.