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) NOT NULL auto_increment,
132 biblionumber int(11) default NULL,
134 requisitionedby varchar(10) default NULL,
135 authorisedby varchar(10) default NULL,
136 booksellerid varchar(10) NOT NULL default '',
137 deliverydays smallint(6) default NULL,
138 followupdays smallint(6) default NULL,
139 numberfollowupsallowed smallint(6) default NULL,
140 numberfollowupssent smallint(6) default NULL,
141 entrydate date default NULL,
142 dateprinted date default NULL,
143 quantity smallint(6) default NULL,
144 currency char(3) default NULL,
145 listprice decimal(28,6) default NULL,
146 totalamount decimal(28,6) default NULL,
147 datereceived date default NULL,
148 booksellerinvoicenumber text,
149 freight decimal(28,6) default NULL,
150 unitprice decimal(28,6) default NULL,
151 quantityreceived smallint(6) default NULL,
153 cancelledby varchar(10) default NULL,
154 quantityreceiveddamaged smallint(6) default NULL,
155 datecancellationprinted date default NULL,
157 supplierreference text,
158 purchaseordernumber text,
159 subscription tinyint(1) default NULL,
160 subscriptionfrom date default NULL,
161 subscriptionto date default NULL,
162 serialid varchar(30) default NULL,
163 basketno int(11) default NULL,
164 biblioitemnumber int(11) default NULL,
165 timestamp timestamp(14) NOT NULL,
166 rrp decimal(13,2) default NULL,
167 ecost decimal(13,2) default NULL,
168 gst decimal(13,2) default NULL,
169 PRIMARY KEY (ordernumber),
170 KEY booksellerid (booksellerid),
171 KEY biblionumber (biblionumber),
172 KEY biblioitemnumber (biblioitemnumber),
173 KEY biblioitemnumber_2 (biblioitemnumber)
177 # Table structure for table 'biblio'
179 CREATE TABLE biblio (
180 biblionumber int(11) DEFAULT '0' NOT NULL,
188 copyrightdate smallint(6),
189 timestamp timestamp(14),
190 KEY blbnoidx (biblionumber),
191 PRIMARY KEY (biblionumber)
195 # Table structure for table 'biblioanalysis'
197 CREATE TABLE biblioanalysis (
198 analyticaltitle text,
199 biblionumber int(11) DEFAULT '0' NOT NULL,
200 analyticalauthor text
204 # Table structure for table 'biblioitems'
206 CREATE TABLE biblioitems (
207 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
208 biblionumber int(11) DEFAULT '0' NOT NULL,
211 classification varchar(25),
218 publicationyear smallint(6),
219 publishercode varchar(255),
221 volumeddesc varchar(255),
222 timestamp timestamp(14),
228 KEY bibinoidx (biblioitemnumber),
229 KEY bibnoidx (biblionumber),
230 PRIMARY KEY (biblioitemnumber)
234 # Table structure for table 'bibliosubject'
236 CREATE TABLE bibliosubject (
237 subject text NOT NULL,
238 biblionumber int(11) DEFAULT '0' NOT NULL
242 # Table structure for table 'bibliosubtitle'
244 CREATE TABLE bibliosubtitle (
245 subtitle text NOT NULL,
246 biblionumber int(11) DEFAULT '0' NOT NULL,
247 KEY bibsubidx (biblionumber)
251 # Table structure for table 'borexp'
253 CREATE TABLE borexp (
254 borrowernumber int(11),
259 # Table structure for table 'borrowers'
261 CREATE TABLE borrowers (
262 borrowernumber int(11) DEFAULT '0' NOT NULL,
263 cardnumber varchar(9) DEFAULT '' NOT NULL,
264 surname text NOT NULL,
265 firstname text NOT NULL,
268 initials text NOT NULL,
269 streetaddress text NOT NULL,
275 altstreetaddress text,
280 branchcode varchar(4) DEFAULT '' NOT NULL,
281 categorycode char(2),
283 gonenoaddress tinyint(1),
292 ethnicity varchar(50),
293 ethnotes varchar(255),
296 altnotes varchar(255),
297 altrelationship varchar(100),
299 phoneday varchar(50),
300 preferredcont char(1),
301 physstreet varchar(100),
302 KEY borrowernumber (borrowernumber),
303 KEY cardnumber (cardnumber)
307 # Table structure for table 'branchcategories'
309 CREATE TABLE branchcategories (
310 categorycode char(2) DEFAULT '' NOT NULL,
312 codedescription text,
313 PRIMARY KEY (categorycode)
316 # Table structure for table 'branchrelations'
318 CREATE TABLE branchrelations (
319 branchcode varchar(4) DEFAULT '' NOT NULL,
320 categorycode varchar(4) DEFAULT '' NOT NULL
324 # Table structure for table 'branches'
326 CREATE TABLE branches (
327 branchcode varchar(4) DEFAULT '' NOT NULL,
328 branchname text NOT NULL,
336 UNIQUE branchcode (branchcode)
340 # Table structure for table 'branchtransfers'
342 CREATE TABLE branchtransfers (
343 itemnumber int(11) DEFAULT '0' NOT NULL,
344 datesent datetime DEFAULT '0000-00-00' NOT NULL,
345 frombranch varchar(4),
346 datearrived datetime,
352 # Table structure for table 'catalogueentry'
354 CREATE TABLE catalogueentry (
355 catalogueentry text NOT NULL,
364 # Table structure for table 'categories'
366 CREATE TABLE categories (
367 categorycode char(2) DEFAULT '' NOT NULL,
369 enrolmentperiod smallint(6),
370 upperagelimit smallint(6),
371 dateofbirthrequired tinyint(1),
372 finetype varchar(30),
374 enrolmentfee decimal(28,6),
375 overduenoticerequired tinyint(1),
376 issuelimit smallint(6),
377 reservefee decimal(28,6),
378 UNIQUE categorycode (categorycode)
382 # Table structure for table 'categoryitem'
384 CREATE TABLE categoryitem (
385 categorycode char(2) DEFAULT '' NOT NULL,
386 itemtype varchar(4) DEFAULT '' NOT NULL,
387 restrictedtype tinyint(1),
388 rentaldiscount decimal(28,6),
389 reservecharge decimal(28,6),
392 chargeperiod int(11),
394 chargename varchar(100)
398 # Table structure for table 'classification'
400 CREATE TABLE classification (
401 classification varchar(12) DEFAULT '' NOT NULL
405 # Table structure for table 'currency'
407 CREATE TABLE currency (
408 currency varchar(10),
413 # Table structure for table 'deletedbiblio'
415 CREATE TABLE deletedbiblio (
416 biblionumber int(11) DEFAULT '0' NOT NULL,
423 copyrightdate smallint(6),
424 timestamp timestamp(14),
425 KEY blbnoidx (biblionumber),
426 PRIMARY KEY (biblionumber)
430 # Table structure for table 'deletedbiblioitems'
432 CREATE TABLE deletedbiblioitems (
433 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
434 biblionumber int(11) DEFAULT '0' NOT NULL,
437 classification varchar(25),
443 publicationyear smallint(6),
444 publishercode varchar(255),
446 volumeddesc varchar(255),
447 timestamp timestamp(14),
452 KEY bibinoidx (biblioitemnumber),
453 KEY bibnoidx (biblionumber),
454 PRIMARY KEY (biblioitemnumber)
458 # Table structure for table 'deletedborrowers'
460 CREATE TABLE deletedborrowers (
461 borrowernumber int(11) DEFAULT '0' NOT NULL,
462 cardnumber varchar(9) DEFAULT '' NOT NULL,
463 surname text NOT NULL,
464 firstname text NOT NULL,
467 initials text NOT NULL,
468 streetaddress text NOT NULL,
474 altstreetaddress text,
479 branchcode varchar(4) DEFAULT '' NOT NULL,
480 categorycode char(2),
482 gonenoaddress tinyint(1),
491 ethnicity varchar(50),
492 ethnotes varchar(255),
495 altnotes varchar(255),
496 altrelationship varchar(100),
498 phoneday varchar(50),
499 preferredcont varchar(100),
500 physstreet varchar(100),
501 KEY borrowernumber (borrowernumber),
502 KEY cardnumber (cardnumber)
506 # Table structure for table 'deleteditems'
508 CREATE TABLE deleteditems (
509 itemnumber int(11) DEFAULT '0' NOT NULL,
510 biblionumber int(11) DEFAULT '0' NOT NULL,
511 multivolumepart varchar(30),
512 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
513 barcode varchar(9) DEFAULT '' NOT NULL,
514 dateaccessioned date,
515 booksellerid varchar(10),
516 homebranch varchar(4),
518 replacementprice decimal(28,6),
519 replacementpricedate date,
520 datelastborrowed date,
522 multivolume tinyint(1),
524 notforloan tinyint(1),
529 renewals smallint(6),
530 reserves smallint(6),
531 restricted tinyint(1),
532 binding decimal(28,6),
534 holdingbranch varchar(4),
536 timestamp timestamp(14),
537 KEY itembarcodeidx (barcode),
538 KEY itembinoidx (biblioitemnumber),
539 KEY itembibnoidx (biblionumber),
540 PRIMARY KEY (itemnumber),
541 UNIQUE barcode (barcode)
545 # Table structure for table 'issues'
547 CREATE TABLE issues (
548 borrowernumber int(11) DEFAULT '0' NOT NULL,
549 itemnumber int(11) DEFAULT '0' NOT NULL,
552 issuingbranch char(18),
554 lastreneweddate date,
557 timestamp timestamp(14),
558 KEY issuesborridx (borrowernumber),
559 KEY issuesitemidx (itemnumber),
560 KEY bordate (borrowernumber,timestamp)
564 # Table structure for table 'items'
567 itemnumber int(11) DEFAULT '0' NOT NULL,
568 biblionumber int(11) DEFAULT '0' NOT NULL,
569 multivolumepart varchar(30),
570 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
571 barcode varchar(9) DEFAULT '' NOT NULL,
572 dateaccessioned date,
573 booksellerid varchar(10),
574 homebranch varchar(4),
576 replacementprice decimal(8,2),
577 replacementpricedate date,
578 datelastborrowed date,
580 multivolume tinyint(1),
582 notforloan tinyint(1),
587 renewals smallint(6),
588 reserves smallint(6),
589 restricted tinyint(1),
590 binding decimal(28,6),
592 holdingbranch varchar(4),
594 timestamp timestamp(14),
595 KEY itembarcodeidx (barcode),
596 KEY itembinoidx (biblioitemnumber),
597 KEY itembibnoidx (biblionumber),
598 PRIMARY KEY (itemnumber),
599 UNIQUE barcode (barcode)
603 # Table structure for table 'itemsprices'
605 CREATE TABLE itemsprices (
607 price1 decimal(28,6),
612 # Table structure for table 'itemtypes'
614 CREATE TABLE itemtypes (
615 itemtype varchar(4) DEFAULT '' NOT NULL,
617 loanlength smallint(6),
618 renewalsallowed smallint(6),
619 rentalcharge double(16,4),
620 UNIQUE itemtype (itemtype)
624 # Table structure for table 'multipart'
626 CREATE TABLE multipart (
627 itemnumber int(11) DEFAULT '0' NOT NULL,
628 other int(11) DEFAULT '0' NOT NULL
632 # Table structure for table 'multivolume'
634 CREATE TABLE multivolume (
635 biblionumber int(11) DEFAULT '0' NOT NULL,
636 multivolumepart varchar(30) DEFAULT '' NOT NULL
640 # Table structure for table 'newitems'
642 CREATE TABLE newitems (
643 itemnumber int(11) DEFAULT '0' NOT NULL,
644 publishercode varchar(18),
645 biblionumber int(11) DEFAULT '0' NOT NULL,
646 multivolumepart varchar(30),
647 barcode varchar(9) DEFAULT '' NOT NULL,
648 dateaccessioned date,
649 booksellerid varchar(10),
650 homebranch varchar(4),
652 replacementprice decimal(28,6),
653 replacementpricedate date,
654 datelastborrowed date,
656 multivolume tinyint(1),
658 notforloan tinyint(1),
663 renewals smallint(6),
664 reserves smallint(6),
665 restricted tinyint(1),
666 binding decimal(28,6),
668 holdingbranch varchar(4),
672 classification varchar(12),
678 publicationyear smallint(6),
679 KEY itembarcodeidx (barcode),
680 KEY itembibnoidx (biblionumber),
681 PRIMARY KEY (itemnumber)
685 # Table structure for table 'printers'
687 CREATE TABLE printers (
688 printername char(40),
694 # Table structure for table 'procedures'
696 CREATE TABLE procedures (
699 proclevel smallint(6)
703 # Table structure for table 'publisher'
705 CREATE TABLE publisher (
706 publishercode varchar(18) DEFAULT '' NOT NULL,
707 publishername text NOT NULL
711 # Table structure for table 'reserveconstraints'
713 CREATE TABLE reserveconstraints (
714 borrowernumber int(11) DEFAULT '0' NOT NULL,
715 reservedate date DEFAULT '0000-00-00' NOT NULL,
716 biblionumber int(11) DEFAULT '0' NOT NULL,
717 biblioitemnumber int(11),
718 timestamp timestamp(14)
722 # Table structure for table 'reserves'
724 CREATE TABLE reserves (
725 borrowernumber int(11) DEFAULT '0' NOT NULL,
726 reservedate date DEFAULT '0000-00-00' NOT NULL,
727 biblionumber int(11) DEFAULT '0' NOT NULL,
728 constrainttype char(1),
729 branchcode varchar(4),
730 notificationdate date,
732 cancellationdate date,
734 priority smallint(6),
736 timestamp timestamp(14),
741 # Table structure for table 'searchstats'
743 CREATE TABLE searchstats (
749 # Table structure for table 'serialissues'
751 CREATE TABLE serialissues (
752 biblionumber int(11) DEFAULT '0' NOT NULL,
753 volume varchar(20) DEFAULT '' NOT NULL,
754 number varchar(20) DEFAULT '' NOT NULL,
755 ordernumber smallint(6),
756 issuedate varchar(20),
761 # Table structure for table 'statistics'
763 CREATE TABLE statistics (
764 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
770 usercode varchar(10),
773 KEY timeidx (datetime)
777 # Table structure for table 'stopwords'
779 CREATE TABLE stopwords (
784 # Table structure for table 'systempreferences'
786 CREATE TABLE systempreferences (
787 variable char(50) DEFAULT '' NOT NULL,
789 PRIMARY KEY (variable)
793 # Table structure for table 'users'
796 usercode varchar(10),
803 # Table structure for table 'websites'
806 CREATE TABLE websites (
807 websitenumber int(11) NOT NULL auto_increment,
808 biblionumber int(11) NOT NULL default '0',
812 PRIMARY KEY (websitenumber)
813 ) TYPE=ISAM PACK_KEYS=1;