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'
341 # The date sent and datearrived fields are datetime not just date
343 CREATE TABLE branchtransfers (
344 itemnumber int(11) DEFAULT '0' NOT NULL,
345 datesent datetime DEFAULT '0000-00-00' NOT NULL,
346 frombranch varchar(4),
347 datearrived datetime,
353 # Table structure for table 'catalogueentry'
355 CREATE TABLE catalogueentry (
356 catalogueentry text NOT NULL,
365 # Table structure for table 'categories'
367 CREATE TABLE categories (
368 categorycode char(2) DEFAULT '' NOT NULL,
370 enrolmentperiod smallint(6),
371 upperagelimit smallint(6),
372 dateofbirthrequired tinyint(1),
373 finetype varchar(30),
375 enrolmentfee decimal(28,6),
376 overduenoticerequired tinyint(1),
377 issuelimit smallint(6),
378 reservefee decimal(28,6),
379 UNIQUE categorycode (categorycode)
383 # Table structure for table 'categoryitem'
385 CREATE TABLE categoryitem (
386 categorycode char(2) DEFAULT '' NOT NULL,
387 itemtype varchar(4) DEFAULT '' NOT NULL,
388 restrictedtype tinyint(1),
389 rentaldiscount decimal(28,6),
390 reservecharge decimal(28,6),
393 chargeperiod int(11),
395 chargename varchar(100)
399 # Table structure for table 'classification'
401 CREATE TABLE classification (
402 classification varchar(12) DEFAULT '' NOT NULL
406 # Table structure for table 'currency'
408 CREATE TABLE currency (
409 currency varchar(10),
414 # Table structure for table 'deletedbiblio'
416 CREATE TABLE deletedbiblio (
417 biblionumber int(11) DEFAULT '0' NOT NULL,
425 copyrightdate smallint(6),
426 timestamp timestamp(14),
427 KEY blbnoidx (biblionumber),
428 PRIMARY KEY (biblionumber)
432 # Table structure for table 'deletedbiblioitems'
434 CREATE TABLE deletedbiblioitems (
435 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
436 biblionumber int(11) DEFAULT '0' NOT NULL,
439 classification varchar(25),
445 publicationyear smallint(6),
446 publishercode varchar(255),
448 volumeddesc varchar(255),
449 timestamp timestamp(14),
454 KEY bibinoidx (biblioitemnumber),
455 KEY bibnoidx (biblionumber),
456 PRIMARY KEY (biblioitemnumber)
460 # Table structure for table 'deletedborrowers'
462 CREATE TABLE deletedborrowers (
463 borrowernumber int(11) DEFAULT '0' NOT NULL,
464 cardnumber varchar(9) DEFAULT '' NOT NULL,
465 surname text NOT NULL,
466 firstname text NOT NULL,
469 initials text NOT NULL,
470 streetaddress text NOT NULL,
476 altstreetaddress text,
481 branchcode varchar(4) DEFAULT '' NOT NULL,
482 categorycode char(2),
484 gonenoaddress tinyint(1),
493 ethnicity varchar(50),
494 ethnotes varchar(255),
497 altnotes varchar(255),
498 altrelationship varchar(100),
500 phoneday varchar(50),
501 preferredcont varchar(100),
502 physstreet varchar(100),
503 KEY borrowernumber (borrowernumber),
504 KEY cardnumber (cardnumber)
508 # Table structure for table 'deleteditems'
510 CREATE TABLE deleteditems (
511 itemnumber int(11) DEFAULT '0' NOT NULL,
512 biblionumber int(11) DEFAULT '0' NOT NULL,
513 multivolumepart varchar(30),
514 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
515 barcode varchar(9) DEFAULT '' NOT NULL,
516 dateaccessioned date,
517 booksellerid varchar(10),
518 homebranch varchar(4),
520 replacementprice decimal(28,6),
521 replacementpricedate date,
522 datelastborrowed date,
524 multivolume tinyint(1),
526 notforloan tinyint(1),
531 renewals smallint(6),
532 reserves smallint(6),
533 restricted tinyint(1),
534 binding decimal(28,6),
536 holdingbranch varchar(4),
538 timestamp timestamp(14),
539 KEY itembarcodeidx (barcode),
540 KEY itembinoidx (biblioitemnumber),
541 KEY itembibnoidx (biblionumber),
542 PRIMARY KEY (itemnumber),
543 UNIQUE barcode (barcode)
547 # Table structure for table 'ethnicity'
550 CREATE TABLE ethnicity (
551 code varchar(10) NOT NULL default '',
552 name varchar(255) default NULL,
557 # Table structure for table 'issues'
559 CREATE TABLE issues (
560 borrowernumber int(11) DEFAULT '0' NOT NULL,
561 itemnumber int(11) DEFAULT '0' NOT NULL,
564 issuingbranch char(18),
566 lastreneweddate date,
569 timestamp timestamp(14),
570 KEY issuesborridx (borrowernumber),
571 KEY issuesitemidx (itemnumber),
572 KEY bordate (borrowernumber,timestamp)
576 # Table structure for table 'items'
579 itemnumber int(11) DEFAULT '0' NOT NULL,
580 biblionumber int(11) DEFAULT '0' NOT NULL,
581 multivolumepart varchar(30),
582 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
583 barcode varchar(9) DEFAULT '' NOT NULL,
584 dateaccessioned date,
585 booksellerid varchar(10),
586 homebranch varchar(4),
588 replacementprice decimal(8,2),
589 replacementpricedate date,
590 datelastborrowed date,
592 multivolume tinyint(1),
594 notforloan tinyint(1),
599 renewals smallint(6),
600 reserves smallint(6),
601 restricted tinyint(1),
602 binding decimal(28,6),
604 holdingbranch varchar(4),
606 timestamp timestamp(14),
607 KEY itembarcodeidx (barcode),
608 KEY itembinoidx (biblioitemnumber),
609 KEY itembibnoidx (biblionumber),
610 PRIMARY KEY (itemnumber),
611 UNIQUE barcode (barcode)
615 # Table structure for table 'itemsprices'
617 CREATE TABLE itemsprices (
619 price1 decimal(28,6),
624 # Table structure for table 'itemtypes'
626 CREATE TABLE itemtypes (
627 itemtype varchar(4) DEFAULT '' NOT NULL,
629 loanlength smallint(6),
630 renewalsallowed smallint(6),
631 rentalcharge double(16,4),
632 UNIQUE itemtype (itemtype)
636 # Table structure for table 'multipart'
638 CREATE TABLE multipart (
639 itemnumber int(11) DEFAULT '0' NOT NULL,
640 other int(11) DEFAULT '0' NOT NULL
644 # Table structure for table 'multivolume'
646 CREATE TABLE multivolume (
647 biblionumber int(11) DEFAULT '0' NOT NULL,
648 multivolumepart varchar(30) DEFAULT '' NOT NULL
652 # Table structure for table 'newitems'
654 CREATE TABLE newitems (
655 itemnumber int(11) DEFAULT '0' NOT NULL,
656 publishercode varchar(18),
657 biblionumber int(11) DEFAULT '0' NOT NULL,
658 multivolumepart varchar(30),
659 barcode varchar(9) DEFAULT '' NOT NULL,
660 dateaccessioned date,
661 booksellerid varchar(10),
662 homebranch varchar(4),
664 replacementprice decimal(28,6),
665 replacementpricedate date,
666 datelastborrowed date,
668 multivolume tinyint(1),
670 notforloan tinyint(1),
675 renewals smallint(6),
676 reserves smallint(6),
677 restricted tinyint(1),
678 binding decimal(28,6),
680 holdingbranch varchar(4),
684 classification varchar(12),
690 publicationyear smallint(6),
691 KEY itembarcodeidx (barcode),
692 KEY itembibnoidx (biblionumber),
693 PRIMARY KEY (itemnumber)
697 # Table structure for table 'printers'
699 CREATE TABLE printers (
700 printername char(40),
706 # Table structure for table 'procedures'
708 CREATE TABLE procedures (
711 proclevel smallint(6)
715 # Table structure for table 'publisher'
717 CREATE TABLE publisher (
718 publishercode varchar(18) DEFAULT '' NOT NULL,
719 publishername text NOT NULL
723 # Table structure for table 'reserveconstraints'
725 CREATE TABLE reserveconstraints (
726 borrowernumber int(11) DEFAULT '0' NOT NULL,
727 reservedate date DEFAULT '0000-00-00' NOT NULL,
728 biblionumber int(11) DEFAULT '0' NOT NULL,
729 biblioitemnumber int(11),
730 timestamp timestamp(14)
734 # Table structure for table 'reserves'
736 CREATE TABLE reserves (
737 borrowernumber int(11) DEFAULT '0' NOT NULL,
738 reservedate date DEFAULT '0000-00-00' NOT NULL,
739 biblionumber int(11) DEFAULT '0' NOT NULL,
740 constrainttype char(1),
741 branchcode varchar(4),
742 notificationdate date,
744 cancellationdate date,
746 priority smallint(6),
748 timestamp timestamp(14),
753 # Table structure for table 'searchstats'
755 CREATE TABLE searchstats (
761 # Table structure for table 'serialissues'
763 CREATE TABLE serialissues (
764 biblionumber int(11) DEFAULT '0' NOT NULL,
765 volume varchar(20) DEFAULT '' NOT NULL,
766 number varchar(20) DEFAULT '' NOT NULL,
767 ordernumber smallint(6),
768 issuedate varchar(20),
773 # Table structure for table 'statistics'
775 CREATE TABLE statistics (
776 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
782 usercode varchar(10),
785 KEY timeidx (datetime)
789 # Table structure for table 'stopwords'
791 CREATE TABLE stopwords (
796 # Table structure for table 'systempreferences'
798 CREATE TABLE systempreferences (
799 variable char(50) DEFAULT '' NOT NULL,
801 explanation char(200) default '',
802 PRIMARY KEY (variable)
806 # Table structure for table 'users'
809 usercode varchar(10),
816 # Table structure for table 'websites'
819 CREATE TABLE websites (
820 websitenumber int(11) NOT NULL auto_increment,
821 biblionumber int(11) NOT NULL default '0',
825 PRIMARY KEY (websitenumber)
826 ) TYPE=ISAM PACK_KEYS=1;
828 CREATE TABLE uploadedmarc (
829 id int(11) NOT NULL auto_increment,
831 hidden smallint(6) default NULL,
832 name varchar(255) default NULL,
837 CREATE TABLE marcrecorddone (
838 isbn char(40) default NULL,
839 issn char(40) default NULL,
840 lccn char(40) default NULL