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,
187 copyrightdate smallint(6),
188 timestamp timestamp(14),
189 KEY blbnoidx (biblionumber),
190 PRIMARY KEY (biblionumber)
194 # Table structure for table 'biblioanalysis'
196 CREATE TABLE biblioanalysis (
197 analyticaltitle text,
198 biblionumber int(11) DEFAULT '0' NOT NULL,
199 analyticalauthor text
203 # Table structure for table 'biblioitems'
205 CREATE TABLE biblioitems (
206 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
207 biblionumber int(11) DEFAULT '0' NOT NULL,
210 classification varchar(25),
216 publicationyear smallint(6),
217 publishercode varchar(255),
219 volumeddesc varchar(255),
220 timestamp timestamp(14),
226 KEY bibinoidx (biblioitemnumber),
227 KEY bibnoidx (biblionumber),
228 PRIMARY KEY (biblioitemnumber)
232 # Table structure for table 'bibliosubject'
234 CREATE TABLE bibliosubject (
235 subject text NOT NULL,
236 biblionumber int(11) DEFAULT '0' NOT NULL
240 # Table structure for table 'bibliosubtitle'
242 CREATE TABLE bibliosubtitle (
243 subtitle text NOT NULL,
244 biblionumber int(11) DEFAULT '0' NOT NULL,
245 KEY bibsubidx (biblionumber)
249 # Table structure for table 'borexp'
251 CREATE TABLE borexp (
252 borrowernumber int(11),
257 # Table structure for table 'borrowers'
259 CREATE TABLE borrowers (
260 borrowernumber int(11) DEFAULT '0' NOT NULL,
261 cardnumber varchar(9) DEFAULT '' NOT NULL,
262 surname text NOT NULL,
263 firstname text NOT NULL,
266 initials text NOT NULL,
267 streetaddress text NOT NULL,
273 altstreetaddress text,
278 branchcode varchar(4) DEFAULT '' NOT NULL,
279 categorycode char(2),
281 gonenoaddress tinyint(1),
290 ethnicity varchar(50),
291 ethnotes varchar(255),
294 altnotes varchar(255),
295 altrelationship varchar(100),
297 phoneday varchar(50),
298 preferredcont char(1),
299 physstreet varchar(100),
300 KEY borrowernumber (borrowernumber),
301 KEY cardnumber (cardnumber)
305 # Table structure for table 'branchcategories'
307 CREATE TABLE branchcategories (
308 categorycode char(2) DEFAULT '' NOT NULL,
309 branchcode char(4) DEFAULT '' NOT NULL,
310 branchholding int(11)
314 # Table structure for table 'branches'
316 CREATE TABLE branches (
317 branchcode varchar(4) DEFAULT '' NOT NULL,
318 branchname text NOT NULL,
326 UNIQUE branchcode (branchcode)
330 # Table structure for table 'branchtransfers'
332 CREATE TABLE branchtransfers (
333 itemnumber int(11) DEFAULT '0' NOT NULL,
334 datesent date DEFAULT '0000-00-00' NOT NULL,
335 frombranch varchar(4),
342 # Table structure for table 'catalogueentry'
344 CREATE TABLE catalogueentry (
345 catalogueentry text NOT NULL,
354 # Table structure for table 'categories'
356 CREATE TABLE categories (
357 categorycode char(2) DEFAULT '' NOT NULL,
359 enrolmentperiod smallint(6),
360 upperagelimit smallint(6),
361 dateofbirthrequired tinyint(1),
362 finetype varchar(30),
364 enrolmentfee decimal(28,6),
365 overduenoticerequired tinyint(1),
366 issuelimit smallint(6),
367 reservefee decimal(28,6),
368 UNIQUE categorycode (categorycode)
372 # Table structure for table 'categoryitem'
374 CREATE TABLE categoryitem (
375 categorycode char(2) DEFAULT '' NOT NULL,
376 itemtype varchar(4) DEFAULT '' NOT NULL,
377 restrictedtype tinyint(1),
378 rentaldiscount decimal(28,6),
379 reservecharge decimal(28,6),
382 chargeperiod int(11),
384 chargename varchar(100)
388 # Table structure for table 'classification'
390 CREATE TABLE classification (
391 classification varchar(12) DEFAULT '' NOT NULL
395 # Table structure for table 'currency'
397 CREATE TABLE currency (
398 currency varchar(10),
403 # Table structure for table 'deletedbiblio'
405 CREATE TABLE deletedbiblio (
406 biblionumber int(11) DEFAULT '0' NOT NULL,
413 copyrightdate smallint(6),
414 timestamp timestamp(14),
415 KEY blbnoidx (biblionumber),
416 PRIMARY KEY (biblionumber)
420 # Table structure for table 'deletedbiblioitems'
422 CREATE TABLE deletedbiblioitems (
423 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
424 biblionumber int(11) DEFAULT '0' NOT NULL,
427 classification varchar(25),
433 publicationyear smallint(6),
434 publishercode varchar(255),
436 volumeddesc varchar(255),
437 timestamp timestamp(14),
442 KEY bibinoidx (biblioitemnumber),
443 KEY bibnoidx (biblionumber),
444 PRIMARY KEY (biblioitemnumber)
448 # Table structure for table 'deletedborrowers'
450 CREATE TABLE deletedborrowers (
451 borrowernumber int(11) DEFAULT '0' NOT NULL,
452 cardnumber varchar(9) DEFAULT '' NOT NULL,
453 surname text NOT NULL,
454 firstname text NOT NULL,
457 initials text NOT NULL,
458 streetaddress text NOT NULL,
464 altstreetaddress text,
469 branchcode varchar(4) DEFAULT '' NOT NULL,
470 categorycode char(2),
472 gonenoaddress tinyint(1),
481 ethnicity varchar(50),
482 ethnotes varchar(255),
485 altnotes varchar(255),
486 altrelationship varchar(100),
488 phoneday varchar(50),
489 preferredcont varchar(100),
490 physstreet varchar(100),
491 KEY borrowernumber (borrowernumber),
492 KEY cardnumber (cardnumber)
496 # Table structure for table 'deleteditems'
498 CREATE TABLE deleteditems (
499 itemnumber int(11) DEFAULT '0' NOT NULL,
500 biblionumber int(11) DEFAULT '0' NOT NULL,
501 multivolumepart varchar(30),
502 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
503 barcode varchar(9) DEFAULT '' NOT NULL,
504 dateaccessioned date,
505 booksellerid varchar(10),
506 homebranch varchar(4),
508 replacementprice decimal(28,6),
509 replacementpricedate date,
510 datelastborrowed date,
512 multivolume tinyint(1),
514 notforloan tinyint(1),
519 renewals smallint(6),
520 reserves smallint(6),
521 restricted tinyint(1),
522 binding decimal(28,6),
524 holdingbranch varchar(4),
526 timestamp timestamp(14),
527 KEY itembarcodeidx (barcode),
528 KEY itembinoidx (biblioitemnumber),
529 KEY itembibnoidx (biblionumber),
530 PRIMARY KEY (itemnumber),
531 UNIQUE barcode (barcode)
535 # Table structure for table 'issues'
537 CREATE TABLE issues (
538 borrowernumber int(11) DEFAULT '0' NOT NULL,
539 itemnumber int(11) DEFAULT '0' NOT NULL,
542 issuingbranch char(18),
544 lastreneweddate date,
547 timestamp timestamp(14),
548 KEY issuesborridx (borrowernumber),
549 KEY issuesitemidx (itemnumber),
550 KEY bordate (borrowernumber,timestamp)
554 # Table structure for table 'items'
557 itemnumber int(11) DEFAULT '0' NOT NULL,
558 biblionumber int(11) DEFAULT '0' NOT NULL,
559 multivolumepart varchar(30),
560 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
561 barcode varchar(9) DEFAULT '' NOT NULL,
562 dateaccessioned date,
563 booksellerid varchar(10),
564 homebranch varchar(4),
566 replacementprice decimal(8,2),
567 replacementpricedate date,
568 datelastborrowed date,
570 multivolume tinyint(1),
572 notforloan tinyint(1),
577 renewals smallint(6),
578 reserves smallint(6),
579 restricted tinyint(1),
580 binding decimal(28,6),
582 holdingbranch varchar(4),
584 timestamp timestamp(14),
585 KEY itembarcodeidx (barcode),
586 KEY itembinoidx (biblioitemnumber),
587 KEY itembibnoidx (biblionumber),
588 PRIMARY KEY (itemnumber),
589 UNIQUE barcode (barcode)
593 # Table structure for table 'itemsprices'
595 CREATE TABLE itemsprices (
597 price1 decimal(28,6),
602 # Table structure for table 'itemtypes'
604 CREATE TABLE itemtypes (
605 itemtype varchar(4) DEFAULT '' NOT NULL,
607 loanlength smallint(6),
608 renewalsallowed smallint(6),
609 rentalcharge double(16,4),
610 UNIQUE itemtype (itemtype)
614 # Table structure for table 'multipart'
616 CREATE TABLE multipart (
617 itemnumber int(11) DEFAULT '0' NOT NULL,
618 other int(11) DEFAULT '0' NOT NULL
622 # Table structure for table 'multivolume'
624 CREATE TABLE multivolume (
625 biblionumber int(11) DEFAULT '0' NOT NULL,
626 multivolumepart varchar(30) DEFAULT '' NOT NULL
630 # Table structure for table 'newitems'
632 CREATE TABLE newitems (
633 itemnumber int(11) DEFAULT '0' NOT NULL,
634 publishercode varchar(18),
635 biblionumber int(11) DEFAULT '0' NOT NULL,
636 multivolumepart varchar(30),
637 barcode varchar(9) DEFAULT '' NOT NULL,
638 dateaccessioned date,
639 booksellerid varchar(10),
640 homebranch varchar(4),
642 replacementprice decimal(28,6),
643 replacementpricedate date,
644 datelastborrowed date,
646 multivolume tinyint(1),
648 notforloan tinyint(1),
653 renewals smallint(6),
654 reserves smallint(6),
655 restricted tinyint(1),
656 binding decimal(28,6),
658 holdingbranch varchar(4),
662 classification varchar(12),
668 publicationyear smallint(6),
669 KEY itembarcodeidx (barcode),
670 KEY itembibnoidx (biblionumber),
671 PRIMARY KEY (itemnumber)
675 # Table structure for table 'printers'
677 CREATE TABLE printers (
678 printername char(40),
684 # Table structure for table 'procedures'
686 CREATE TABLE procedures (
689 proclevel smallint(6)
693 # Table structure for table 'publisher'
695 CREATE TABLE publisher (
696 publishercode varchar(18) DEFAULT '' NOT NULL,
697 publishername text NOT NULL
701 # Table structure for table 'reserveconstraints'
703 CREATE TABLE reserveconstraints (
704 borrowernumber int(11) DEFAULT '0' NOT NULL,
705 reservedate date DEFAULT '0000-00-00' NOT NULL,
706 biblionumber int(11) DEFAULT '0' NOT NULL,
707 biblioitemnumber int(11),
708 timestamp timestamp(14)
712 # Table structure for table 'reserves'
714 CREATE TABLE reserves (
715 borrowernumber int(11) DEFAULT '0' NOT NULL,
716 reservedate date DEFAULT '0000-00-00' NOT NULL,
717 biblionumber int(11) DEFAULT '0' NOT NULL,
718 constrainttype char(1),
719 branchcode varchar(4),
720 notificationdate date,
722 cancellationdate date,
724 priority smallint(6),
726 timestamp timestamp(14),
731 # Table structure for table 'searchstats'
733 CREATE TABLE searchstats (
739 # Table structure for table 'serialissues'
741 CREATE TABLE serialissues (
742 biblionumber int(11) DEFAULT '0' NOT NULL,
743 volume varchar(20) DEFAULT '' NOT NULL,
744 number varchar(20) DEFAULT '' NOT NULL,
745 ordernumber smallint(6),
746 issuedate varchar(20),
751 # Table structure for table 'statistics'
753 CREATE TABLE statistics (
754 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
760 usercode varchar(10),
763 KEY timeidx (datetime)
767 # Table structure for table 'stopwords'
769 CREATE TABLE stopwords (
774 # Table structure for table 'systempreferences'
776 CREATE TABLE systempreferences (
777 variable char(50) DEFAULT '' NOT NULL,
779 PRIMARY KEY (variable)
783 # Table structure for table 'users'
786 usercode varchar(10),