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,
311 branchcode char(4) DEFAULT '' NOT NULL,
312 branchholding int(11)
316 # Table structure for table 'branches'
318 CREATE TABLE branches (
319 branchcode varchar(4) DEFAULT '' NOT NULL,
320 branchname text NOT NULL,
328 UNIQUE branchcode (branchcode)
332 # Table structure for table 'branchtransfers'
334 CREATE TABLE branchtransfers (
335 itemnumber int(11) DEFAULT '0' NOT NULL,
336 datesent date DEFAULT '0000-00-00' NOT NULL,
337 frombranch varchar(4),
344 # Table structure for table 'catalogueentry'
346 CREATE TABLE catalogueentry (
347 catalogueentry text NOT NULL,
356 # Table structure for table 'categories'
358 CREATE TABLE categories (
359 categorycode char(2) DEFAULT '' NOT NULL,
361 enrolmentperiod smallint(6),
362 upperagelimit smallint(6),
363 dateofbirthrequired tinyint(1),
364 finetype varchar(30),
366 enrolmentfee decimal(28,6),
367 overduenoticerequired tinyint(1),
368 issuelimit smallint(6),
369 reservefee decimal(28,6),
370 UNIQUE categorycode (categorycode)
374 # Table structure for table 'categoryitem'
376 CREATE TABLE categoryitem (
377 categorycode char(2) DEFAULT '' NOT NULL,
378 itemtype varchar(4) DEFAULT '' NOT NULL,
379 restrictedtype tinyint(1),
380 rentaldiscount decimal(28,6),
381 reservecharge decimal(28,6),
384 chargeperiod int(11),
386 chargename varchar(100)
390 # Table structure for table 'classification'
392 CREATE TABLE classification (
393 classification varchar(12) DEFAULT '' NOT NULL
397 # Table structure for table 'currency'
399 CREATE TABLE currency (
400 currency varchar(10),
405 # Table structure for table 'deletedbiblio'
407 CREATE TABLE deletedbiblio (
408 biblionumber int(11) DEFAULT '0' NOT NULL,
416 copyrightdate smallint(6),
417 timestamp timestamp(14),
418 KEY blbnoidx (biblionumber),
419 PRIMARY KEY (biblionumber)
423 # Table structure for table 'deletedbiblioitems'
425 CREATE TABLE deletedbiblioitems (
426 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
427 biblionumber int(11) DEFAULT '0' NOT NULL,
430 classification varchar(25),
436 publicationyear smallint(6),
437 publishercode varchar(255),
439 volumeddesc varchar(255),
440 timestamp timestamp(14),
445 KEY bibinoidx (biblioitemnumber),
446 KEY bibnoidx (biblionumber),
447 PRIMARY KEY (biblioitemnumber)
451 # Table structure for table 'deletedborrowers'
453 CREATE TABLE deletedborrowers (
454 borrowernumber int(11) DEFAULT '0' NOT NULL,
455 cardnumber varchar(9) DEFAULT '' NOT NULL,
456 surname text NOT NULL,
457 firstname text NOT NULL,
460 initials text NOT NULL,
461 streetaddress text NOT NULL,
467 altstreetaddress text,
472 branchcode varchar(4) DEFAULT '' NOT NULL,
473 categorycode char(2),
475 gonenoaddress tinyint(1),
484 ethnicity varchar(50),
485 ethnotes varchar(255),
488 altnotes varchar(255),
489 altrelationship varchar(100),
491 phoneday varchar(50),
492 preferredcont varchar(100),
493 physstreet varchar(100),
494 KEY borrowernumber (borrowernumber),
495 KEY cardnumber (cardnumber)
499 # Table structure for table 'deleteditems'
501 CREATE TABLE deleteditems (
502 itemnumber int(11) DEFAULT '0' NOT NULL,
503 biblionumber int(11) DEFAULT '0' NOT NULL,
504 multivolumepart varchar(30),
505 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
506 barcode varchar(9) DEFAULT '' NOT NULL,
507 dateaccessioned date,
508 booksellerid varchar(10),
509 homebranch varchar(4),
511 replacementprice decimal(28,6),
512 replacementpricedate date,
513 datelastborrowed date,
515 multivolume tinyint(1),
517 notforloan tinyint(1),
522 renewals smallint(6),
523 reserves smallint(6),
524 restricted tinyint(1),
525 binding decimal(28,6),
527 holdingbranch varchar(4),
529 timestamp timestamp(14),
530 KEY itembarcodeidx (barcode),
531 KEY itembinoidx (biblioitemnumber),
532 KEY itembibnoidx (biblionumber),
533 PRIMARY KEY (itemnumber),
534 UNIQUE barcode (barcode)
538 # Table structure for table 'ethnicity'
541 CREATE TABLE ethnicity (
542 code varchar(10) NOT NULL default '',
543 name varchar(255) default NULL,
549 # Table structure for table 'issues'
551 CREATE TABLE issues (
552 borrowernumber int(11) DEFAULT '0' NOT NULL,
553 itemnumber int(11) DEFAULT '0' NOT NULL,
556 issuingbranch char(18),
558 lastreneweddate date,
561 timestamp timestamp(14),
562 KEY issuesborridx (borrowernumber),
563 KEY issuesitemidx (itemnumber),
564 KEY bordate (borrowernumber,timestamp)
568 # Table structure for table 'items'
571 itemnumber int(11) DEFAULT '0' NOT NULL,
572 biblionumber int(11) DEFAULT '0' NOT NULL,
573 multivolumepart varchar(30),
574 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
575 barcode varchar(9) DEFAULT '' NOT NULL,
576 dateaccessioned date,
577 booksellerid varchar(10),
578 homebranch varchar(4),
580 replacementprice decimal(8,2),
581 replacementpricedate date,
582 datelastborrowed date,
584 multivolume tinyint(1),
586 notforloan tinyint(1),
591 renewals smallint(6),
592 reserves smallint(6),
593 restricted tinyint(1),
594 binding decimal(28,6),
596 holdingbranch varchar(4),
598 timestamp timestamp(14),
599 KEY itembarcodeidx (barcode),
600 KEY itembinoidx (biblioitemnumber),
601 KEY itembibnoidx (biblionumber),
602 PRIMARY KEY (itemnumber),
603 UNIQUE barcode (barcode)
607 # Table structure for table 'itemsprices'
609 CREATE TABLE itemsprices (
611 price1 decimal(28,6),
616 # Table structure for table 'itemtypes'
618 CREATE TABLE itemtypes (
619 itemtype varchar(4) DEFAULT '' NOT NULL,
621 loanlength smallint(6),
622 renewalsallowed smallint(6),
623 rentalcharge double(16,4),
624 UNIQUE itemtype (itemtype)
628 # Table structure for table 'multipart'
630 CREATE TABLE multipart (
631 itemnumber int(11) DEFAULT '0' NOT NULL,
632 other int(11) DEFAULT '0' NOT NULL
636 # Table structure for table 'multivolume'
638 CREATE TABLE multivolume (
639 biblionumber int(11) DEFAULT '0' NOT NULL,
640 multivolumepart varchar(30) DEFAULT '' NOT NULL
644 # Table structure for table 'newitems'
646 CREATE TABLE newitems (
647 itemnumber int(11) DEFAULT '0' NOT NULL,
648 publishercode varchar(18),
649 biblionumber int(11) DEFAULT '0' NOT NULL,
650 multivolumepart varchar(30),
651 barcode varchar(9) DEFAULT '' NOT NULL,
652 dateaccessioned date,
653 booksellerid varchar(10),
654 homebranch varchar(4),
656 replacementprice decimal(28,6),
657 replacementpricedate date,
658 datelastborrowed date,
660 multivolume tinyint(1),
662 notforloan tinyint(1),
667 renewals smallint(6),
668 reserves smallint(6),
669 restricted tinyint(1),
670 binding decimal(28,6),
672 holdingbranch varchar(4),
676 classification varchar(12),
682 publicationyear smallint(6),
683 KEY itembarcodeidx (barcode),
684 KEY itembibnoidx (biblionumber),
685 PRIMARY KEY (itemnumber)
689 # Table structure for table 'printers'
691 CREATE TABLE printers (
692 printername char(40),
698 # Table structure for table 'procedures'
700 CREATE TABLE procedures (
703 proclevel smallint(6)
707 # Table structure for table 'publisher'
709 CREATE TABLE publisher (
710 publishercode varchar(18) DEFAULT '' NOT NULL,
711 publishername text NOT NULL
715 # Table structure for table 'reserveconstraints'
717 CREATE TABLE reserveconstraints (
718 borrowernumber int(11) DEFAULT '0' NOT NULL,
719 reservedate date DEFAULT '0000-00-00' NOT NULL,
720 biblionumber int(11) DEFAULT '0' NOT NULL,
721 biblioitemnumber int(11),
722 timestamp timestamp(14)
726 # Table structure for table 'reserves'
728 CREATE TABLE reserves (
729 borrowernumber int(11) DEFAULT '0' NOT NULL,
730 reservedate date DEFAULT '0000-00-00' NOT NULL,
731 biblionumber int(11) DEFAULT '0' NOT NULL,
732 constrainttype char(1),
733 branchcode varchar(4),
734 notificationdate date,
736 cancellationdate date,
738 priority smallint(6),
740 timestamp timestamp(14),
745 # Table structure for table 'searchstats'
747 CREATE TABLE searchstats (
753 # Table structure for table 'serialissues'
755 CREATE TABLE serialissues (
756 biblionumber int(11) DEFAULT '0' NOT NULL,
757 volume varchar(20) DEFAULT '' NOT NULL,
758 number varchar(20) DEFAULT '' NOT NULL,
759 ordernumber smallint(6),
760 issuedate varchar(20),
765 # Table structure for table 'statistics'
767 CREATE TABLE statistics (
768 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
774 usercode varchar(10),
777 KEY timeidx (datetime)
781 # Table structure for table 'stopwords'
783 CREATE TABLE stopwords (
788 # Table structure for table 'systempreferences'
790 CREATE TABLE systempreferences (
791 variable char(50) DEFAULT '' NOT NULL,
793 explanation char(200) default '',
794 PRIMARY KEY (variable)
798 # Table structure for table 'users'
801 usercode varchar(10),