3 # Host: localhost Database: c4test
4 #--------------------------------------------------------
5 # Server version 3.22.32-log
8 # Table structure for table 'accountlines'
10 CREATE TABLE accountlines (
11 borrowernumber int(11) DEFAULT '0' NOT NULL,
12 accountno smallint(6) DEFAULT '0' NOT NULL,
18 accounttype varchar(5),
19 amountoutstanding decimal(28,6),
20 timestamp timestamp(14),
21 KEY acctsborridx (borrowernumber),
22 KEY timeidx (timestamp)
26 # Table structure for table 'accountoffsets'
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)
37 # Table structure for table 'additionalauthors'
39 CREATE TABLE additionalauthors (
41 biblionumber int(11) DEFAULT '0' NOT NULL,
42 KEY bibidx (biblionumber)
46 # Table structure for table 'aqbookfund'
48 CREATE TABLE aqbookfund (
49 bookfundid varchar(5) DEFAULT '' NOT NULL,
51 bookfundgroup varchar(5)
55 # Table structure for table 'aqbooksellers'
57 CREATE TABLE aqbooksellers (
67 currency char(3) DEFAULT '' NOT NULL,
68 deliverydays smallint(6),
69 followupdays smallint(6),
70 followupscancel smallint(6),
80 contphone varchar(100),
82 contaltphone varchar(100),
83 contemail varchar(100),
87 invoiceprice varchar(5),
89 listincgst tinyint(4),
90 invoiceincgst tinyint(4),
96 # Table structure for table 'aqbudget'
98 CREATE TABLE aqbudget (
99 bookfundid char(5) DEFAULT '' NOT NULL,
100 startdate date DEFAULT '0000-00-00' NOT NULL,
102 budgetamount decimal(13,2)
106 # Table structure for table 'aqorderbreakdown'
108 CREATE TABLE aqorderbreakdown (
112 bookfundid char(5) DEFAULT '' NOT NULL,
113 allocation smallint(6)
117 # Table structure for table 'aqorderdelivery'
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
128 # Table structure for table 'aqorders'
130 CREATE TABLE aqorders (
131 ordernumber int(11) DEFAULT '0' NOT NULL auto_increment,
132 biblionumber int(11),
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),
143 quantity smallint(6),
145 listprice decimal(28,6),
146 totalamount decimal(28,6),
148 booksellerinvoicenumber text,
149 freight decimal(28,6),
150 unitprice decimal(28,6),
151 quantityreceived smallint(6),
153 cancelledby varchar(10),
154 quantityreceiveddamaged smallint(6),
155 datecancellationprinted date,
157 supplierreference text,
158 purchaseordernumber text,
159 subscription tinyint(1),
160 subscriptionfrom date,
162 serialid varchar(30),
164 biblioitemnumber int(11),
165 timestamp timestamp(14),
169 PRIMARY KEY (ordernumber)
173 # Table structure for table 'biblio'
175 CREATE TABLE biblio (
176 biblionumber int(11) DEFAULT '0' NOT NULL,
184 copyrightdate smallint(6),
185 timestamp timestamp(14),
186 KEY blbnoidx (biblionumber),
187 PRIMARY KEY (biblionumber)
191 # Table structure for table 'biblioanalysis'
193 CREATE TABLE biblioanalysis (
194 analyticaltitle text,
195 biblionumber int(11) DEFAULT '0' NOT NULL,
196 analyticalauthor text
200 # Table structure for table 'biblioitems'
202 CREATE TABLE biblioitems (
203 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
204 biblionumber int(11) DEFAULT '0' NOT NULL,
207 classification varchar(25),
214 publicationyear smallint(6),
215 publishercode varchar(255),
217 volumeddesc varchar(255),
218 timestamp timestamp(14),
224 KEY bibinoidx (biblioitemnumber),
225 KEY bibnoidx (biblionumber),
226 PRIMARY KEY (biblioitemnumber)
230 # Table structure for table 'bibliosubject'
232 CREATE TABLE bibliosubject (
233 subject text NOT NULL,
234 biblionumber int(11) DEFAULT '0' NOT NULL
238 # Table structure for table 'bibliosubtitle'
240 CREATE TABLE bibliosubtitle (
241 subtitle text NOT NULL,
242 biblionumber int(11) DEFAULT '0' NOT NULL,
243 KEY bibsubidx (biblionumber)
247 # Table structure for table 'borexp'
249 CREATE TABLE borexp (
250 borrowernumber int(11),
255 # Table structure for table 'borrowers'
257 CREATE TABLE borrowers (
258 borrowernumber int(11) DEFAULT '0' NOT NULL,
259 cardnumber varchar(9) DEFAULT '' NOT NULL,
260 surname text NOT NULL,
261 firstname text NOT NULL,
264 initials text NOT NULL,
265 streetaddress text NOT NULL,
271 altstreetaddress text,
276 branchcode varchar(4) DEFAULT '' NOT NULL,
277 categorycode char(2),
279 gonenoaddress tinyint(1),
288 ethnicity varchar(50),
289 ethnotes varchar(255),
292 altnotes varchar(255),
293 altrelationship varchar(100),
295 phoneday varchar(50),
296 preferredcont char(1),
297 physstreet varchar(100),
298 KEY borrowernumber (borrowernumber),
299 KEY cardnumber (cardnumber)
303 # Table structure for table 'branchcategories'
305 CREATE TABLE branchcategories (
306 categorycode char(2) DEFAULT '' NOT NULL,
307 branchcode char(4) DEFAULT '' NOT NULL,
308 branchholding int(11)
312 # Table structure for table 'branches'
314 CREATE TABLE branches (
315 branchcode varchar(4) DEFAULT '' NOT NULL,
316 branchname text NOT NULL,
324 UNIQUE branchcode (branchcode)
328 # Table structure for table 'branchtransfers'
330 CREATE TABLE branchtransfers (
331 itemnumber int(11) DEFAULT '0' NOT NULL,
332 datesent date DEFAULT '0000-00-00' NOT NULL,
333 frombranch varchar(4),
340 # Table structure for table 'catalogueentry'
342 CREATE TABLE catalogueentry (
343 catalogueentry text NOT NULL,
352 # Table structure for table 'categories'
354 CREATE TABLE categories (
355 categorycode char(2) DEFAULT '' NOT NULL,
357 enrolmentperiod smallint(6),
358 upperagelimit smallint(6),
359 dateofbirthrequired tinyint(1),
360 finetype varchar(30),
362 enrolmentfee decimal(28,6),
363 overduenoticerequired tinyint(1),
364 issuelimit smallint(6),
365 reservefee decimal(28,6),
366 UNIQUE categorycode (categorycode)
370 # Table structure for table 'categoryitem'
372 CREATE TABLE categoryitem (
373 categorycode char(2) DEFAULT '' NOT NULL,
374 itemtype varchar(4) DEFAULT '' NOT NULL,
375 restrictedtype tinyint(1),
376 rentaldiscount decimal(28,6),
377 reservecharge decimal(28,6),
380 chargeperiod int(11),
382 chargename varchar(100)
386 # Table structure for table 'classification'
388 CREATE TABLE classification (
389 classification varchar(12) DEFAULT '' NOT NULL
393 # Table structure for table 'currency'
395 CREATE TABLE currency (
396 currency varchar(10),
401 # Table structure for table 'deletedbiblio'
403 CREATE TABLE deletedbiblio (
404 biblionumber int(11) DEFAULT '0' NOT NULL,
411 copyrightdate smallint(6),
412 timestamp timestamp(14),
413 KEY blbnoidx (biblionumber),
414 PRIMARY KEY (biblionumber)
418 # Table structure for table 'deletedbiblioitems'
420 CREATE TABLE deletedbiblioitems (
421 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
422 biblionumber int(11) DEFAULT '0' NOT NULL,
425 classification varchar(25),
431 publicationyear smallint(6),
432 publishercode varchar(255),
434 volumeddesc varchar(255),
435 timestamp timestamp(14),
440 KEY bibinoidx (biblioitemnumber),
441 KEY bibnoidx (biblionumber),
442 PRIMARY KEY (biblioitemnumber)
446 # Table structure for table 'deletedborrowers'
448 CREATE TABLE deletedborrowers (
449 borrowernumber int(11) DEFAULT '0' NOT NULL,
450 cardnumber varchar(9) DEFAULT '' NOT NULL,
451 surname text NOT NULL,
452 firstname text NOT NULL,
455 initials text NOT NULL,
456 streetaddress text NOT NULL,
462 altstreetaddress text,
467 branchcode varchar(4) DEFAULT '' NOT NULL,
468 categorycode char(2),
470 gonenoaddress tinyint(1),
479 ethnicity varchar(50),
480 ethnotes varchar(255),
483 altnotes varchar(255),
484 altrelationship varchar(100),
486 phoneday varchar(50),
487 preferredcont varchar(100),
488 physstreet varchar(100),
489 KEY borrowernumber (borrowernumber),
490 KEY cardnumber (cardnumber)
494 # Table structure for table 'deleteditems'
496 CREATE TABLE deleteditems (
497 itemnumber int(11) DEFAULT '0' NOT NULL,
498 biblionumber int(11) DEFAULT '0' NOT NULL,
499 multivolumepart varchar(30),
500 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
501 barcode varchar(9) DEFAULT '' NOT NULL,
502 dateaccessioned date,
503 booksellerid varchar(10),
504 homebranch varchar(4),
506 replacementprice decimal(28,6),
507 replacementpricedate date,
508 datelastborrowed date,
510 multivolume tinyint(1),
512 notforloan tinyint(1),
517 renewals smallint(6),
518 reserves smallint(6),
519 restricted tinyint(1),
520 binding decimal(28,6),
522 holdingbranch varchar(4),
524 timestamp timestamp(14),
525 KEY itembarcodeidx (barcode),
526 KEY itembinoidx (biblioitemnumber),
527 KEY itembibnoidx (biblionumber),
528 PRIMARY KEY (itemnumber),
529 UNIQUE barcode (barcode)
533 # Table structure for table 'issues'
535 CREATE TABLE issues (
536 borrowernumber int(11) DEFAULT '0' NOT NULL,
537 itemnumber int(11) DEFAULT '0' NOT NULL,
540 issuingbranch char(18),
542 lastreneweddate date,
545 timestamp timestamp(14),
546 KEY issuesborridx (borrowernumber),
547 KEY issuesitemidx (itemnumber),
548 KEY bordate (borrowernumber,timestamp)
552 # Table structure for table 'items'
555 itemnumber int(11) DEFAULT '0' NOT NULL,
556 biblionumber int(11) DEFAULT '0' NOT NULL,
557 multivolumepart varchar(30),
558 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
559 barcode varchar(9) DEFAULT '' NOT NULL,
560 dateaccessioned date,
561 booksellerid varchar(10),
562 homebranch varchar(4),
564 replacementprice decimal(8,2),
565 replacementpricedate date,
566 datelastborrowed date,
568 multivolume tinyint(1),
570 notforloan tinyint(1),
575 renewals smallint(6),
576 reserves smallint(6),
577 restricted tinyint(1),
578 binding decimal(28,6),
580 holdingbranch varchar(4),
582 timestamp timestamp(14),
583 KEY itembarcodeidx (barcode),
584 KEY itembinoidx (biblioitemnumber),
585 KEY itembibnoidx (biblionumber),
586 PRIMARY KEY (itemnumber),
587 UNIQUE barcode (barcode)
591 # Table structure for table 'itemsprices'
593 CREATE TABLE itemsprices (
595 price1 decimal(28,6),
600 # Table structure for table 'itemtypes'
602 CREATE TABLE itemtypes (
603 itemtype varchar(4) DEFAULT '' NOT NULL,
605 loanlength smallint(6),
606 renewalsallowed smallint(6),
607 rentalcharge double(16,4),
608 UNIQUE itemtype (itemtype)
612 # Table structure for table 'multipart'
614 CREATE TABLE multipart (
615 itemnumber int(11) DEFAULT '0' NOT NULL,
616 other int(11) DEFAULT '0' NOT NULL
620 # Table structure for table 'multivolume'
622 CREATE TABLE multivolume (
623 biblionumber int(11) DEFAULT '0' NOT NULL,
624 multivolumepart varchar(30) DEFAULT '' NOT NULL
628 # Table structure for table 'newitems'
630 CREATE TABLE newitems (
631 itemnumber int(11) DEFAULT '0' NOT NULL,
632 publishercode varchar(18),
633 biblionumber int(11) DEFAULT '0' NOT NULL,
634 multivolumepart varchar(30),
635 barcode varchar(9) DEFAULT '' NOT NULL,
636 dateaccessioned date,
637 booksellerid varchar(10),
638 homebranch varchar(4),
640 replacementprice decimal(28,6),
641 replacementpricedate date,
642 datelastborrowed date,
644 multivolume tinyint(1),
646 notforloan tinyint(1),
651 renewals smallint(6),
652 reserves smallint(6),
653 restricted tinyint(1),
654 binding decimal(28,6),
656 holdingbranch varchar(4),
660 classification varchar(12),
666 publicationyear smallint(6),
667 KEY itembarcodeidx (barcode),
668 KEY itembibnoidx (biblionumber),
669 PRIMARY KEY (itemnumber)
673 # Table structure for table 'printers'
675 CREATE TABLE printers (
676 printername char(40),
682 # Table structure for table 'procedures'
684 CREATE TABLE procedures (
687 proclevel smallint(6)
691 # Table structure for table 'publisher'
693 CREATE TABLE publisher (
694 publishercode varchar(18) DEFAULT '' NOT NULL,
695 publishername text NOT NULL
699 # Table structure for table 'reserveconstraints'
701 CREATE TABLE reserveconstraints (
702 borrowernumber int(11) DEFAULT '0' NOT NULL,
703 reservedate date DEFAULT '0000-00-00' NOT NULL,
704 biblionumber int(11) DEFAULT '0' NOT NULL,
705 biblioitemnumber int(11),
706 timestamp timestamp(14)
710 # Table structure for table 'reserves'
712 CREATE TABLE reserves (
713 borrowernumber int(11) DEFAULT '0' NOT NULL,
714 reservedate date DEFAULT '0000-00-00' NOT NULL,
715 biblionumber int(11) DEFAULT '0' NOT NULL,
716 constrainttype char(1),
717 branchcode varchar(4),
718 notificationdate date,
720 cancellationdate date,
722 priority smallint(6),
724 timestamp timestamp(14),
729 # Table structure for table 'searchstats'
731 CREATE TABLE searchstats (
737 # Table structure for table 'serialissues'
739 CREATE TABLE serialissues (
740 biblionumber int(11) DEFAULT '0' NOT NULL,
741 volume varchar(20) DEFAULT '' NOT NULL,
742 number varchar(20) DEFAULT '' NOT NULL,
743 ordernumber smallint(6),
744 issuedate varchar(20),
749 # Table structure for table 'statistics'
751 CREATE TABLE statistics (
752 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
758 usercode varchar(10),
761 KEY timeidx (datetime)
765 # Table structure for table 'stopwords'
767 CREATE TABLE stopwords (
772 # Table structure for table 'systempreferences'
774 CREATE TABLE systempreferences (
775 variable char(50) DEFAULT '' NOT NULL,
777 PRIMARY KEY (variable)
781 # Table structure for table 'users'
784 usercode varchar(10),