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,
183 copyrightdate smallint(6),
184 timestamp timestamp(14),
185 KEY blbnoidx (biblionumber),
186 PRIMARY KEY (biblionumber)
190 # Table structure for table 'biblioanalysis'
192 CREATE TABLE biblioanalysis (
193 analyticaltitle text,
194 biblionumber int(11) DEFAULT '0' NOT NULL,
195 analyticalauthor text
199 # Table structure for table 'biblioitems'
201 CREATE TABLE biblioitems (
202 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
203 biblionumber int(11) DEFAULT '0' NOT NULL,
206 classification varchar(25),
212 publicationyear smallint(6),
213 publishercode varchar(255),
215 volumeddesc varchar(255),
216 timestamp timestamp(14),
222 KEY bibinoidx (biblioitemnumber),
223 KEY bibnoidx (biblionumber),
224 PRIMARY KEY (biblioitemnumber)
228 # Table structure for table 'bibliosubject'
230 CREATE TABLE bibliosubject (
231 subject text NOT NULL,
232 biblionumber int(11) DEFAULT '0' NOT NULL
236 # Table structure for table 'bibliosubtitle'
238 CREATE TABLE bibliosubtitle (
239 subtitle text NOT NULL,
240 biblionumber int(11) DEFAULT '0' NOT NULL,
241 KEY bibsubidx (biblionumber)
245 # Table structure for table 'borexp'
247 CREATE TABLE borexp (
248 borrowernumber int(11),
253 # Table structure for table 'borrowers'
255 CREATE TABLE borrowers (
256 borrowernumber int(11) DEFAULT '0' NOT NULL,
257 cardnumber varchar(9) DEFAULT '' NOT NULL,
258 surname text NOT NULL,
259 firstname text NOT NULL,
262 initials text NOT NULL,
263 streetaddress text NOT NULL,
270 altstreetaddress text,
275 branchcode varchar(4) DEFAULT '' NOT NULL,
276 categorycode char(2),
278 gonenoaddress tinyint(1),
287 ethnicity varchar(50),
288 ethnotes varchar(255),
291 altnotes varchar(255),
292 altrelationship varchar(100),
294 phoneday varchar(50),
295 preferredcont char(1),
296 physstreet varchar(100),
297 KEY borrowernumber (borrowernumber),
298 KEY cardnumber (cardnumber)
302 # Table structure for table 'branchcategories'
304 CREATE TABLE branchcategories (
305 categorycode char(2) DEFAULT '' NOT NULL,
306 branchcode char(4) DEFAULT '' NOT NULL,
307 branchholding int(11)
311 # Table structure for table 'branches'
313 CREATE TABLE branches (
314 branchcode varchar(4) DEFAULT '' NOT NULL,
315 branchname text NOT NULL,
323 UNIQUE branchcode (branchcode)
327 # Table structure for table 'branchtransfers'
329 CREATE TABLE branchtransfers (
330 itemnumber int(11) DEFAULT '0' NOT NULL,
331 datesent date DEFAULT '0000-00-00' NOT NULL,
332 frombranch varchar(4),
339 # Table structure for table 'catalogueentry'
341 CREATE TABLE catalogueentry (
342 catalogueentry text NOT NULL,
351 # Table structure for table 'categories'
353 CREATE TABLE categories (
354 categorycode char(2) DEFAULT '' NOT NULL,
356 enrolmentperiod smallint(6),
357 upperagelimit smallint(6),
358 dateofbirthrequired tinyint(1),
359 finetype varchar(30),
361 enrolmentfee decimal(28,6),
362 overduenoticerequired tinyint(1),
363 issuelimit smallint(6),
364 reservefee decimal(28,6),
365 UNIQUE categorycode (categorycode)
369 # Table structure for table 'categoryitem'
371 CREATE TABLE categoryitem (
372 categorycode char(2) DEFAULT '' NOT NULL,
373 itemtype varchar(4) DEFAULT '' NOT NULL,
374 restrictedtype tinyint(1),
375 rentaldiscount decimal(28,6),
376 reservecharge decimal(28,6),
379 chargeperiod int(11),
381 chargename varchar(100)
385 # Table structure for table 'classification'
387 CREATE TABLE classification (
388 classification varchar(12) DEFAULT '' NOT NULL
392 # Table structure for table 'currency'
394 CREATE TABLE currency (
395 currency varchar(10),
400 # Table structure for table 'deletedbiblio'
402 CREATE TABLE deletedbiblio (
403 biblionumber int(11) DEFAULT '0' NOT NULL,
410 copyrightdate smallint(6),
411 timestamp timestamp(14),
412 KEY blbnoidx (biblionumber),
413 PRIMARY KEY (biblionumber)
417 # Table structure for table 'deletedbiblioitems'
419 CREATE TABLE deletedbiblioitems (
420 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
421 biblionumber int(11) DEFAULT '0' NOT NULL,
424 classification varchar(25),
430 publicationyear smallint(6),
431 publishercode varchar(255),
433 volumeddesc varchar(255),
434 timestamp timestamp(14),
439 KEY bibinoidx (biblioitemnumber),
440 KEY bibnoidx (biblionumber),
441 PRIMARY KEY (biblioitemnumber)
445 # Table structure for table 'deletedborrowers'
447 CREATE TABLE deletedborrowers (
448 borrowernumber int(11) DEFAULT '0' NOT NULL,
449 cardnumber varchar(9) DEFAULT '' NOT NULL,
450 surname text NOT NULL,
451 firstname text NOT NULL,
454 initials text NOT NULL,
455 streetaddress text NOT NULL,
461 altstreetaddress text,
466 branchcode varchar(4) DEFAULT '' NOT NULL,
467 categorycode char(2),
469 gonenoaddress tinyint(1),
478 ethnicity varchar(50),
479 ethnotes varchar(255),
482 altnotes varchar(255),
483 altrelationship varchar(100),
485 phoneday varchar(50),
486 preferredcont varchar(100),
487 physstreet varchar(100),
488 KEY borrowernumber (borrowernumber),
489 KEY cardnumber (cardnumber)
493 # Table structure for table 'deleteditems'
495 CREATE TABLE deleteditems (
496 itemnumber int(11) DEFAULT '0' NOT NULL,
497 biblionumber int(11) DEFAULT '0' NOT NULL,
498 multivolumepart varchar(30),
499 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
500 barcode varchar(9) DEFAULT '' NOT NULL,
501 dateaccessioned date,
502 booksellerid varchar(10),
503 homebranch varchar(4),
505 replacementprice decimal(28,6),
506 replacementpricedate date,
507 datelastborrowed date,
509 multivolume tinyint(1),
511 notforloan tinyint(1),
516 renewals smallint(6),
517 reserves smallint(6),
518 restricted tinyint(1),
519 binding decimal(28,6),
521 holdingbranch varchar(4),
523 timestamp timestamp(14),
524 KEY itembarcodeidx (barcode),
525 KEY itembinoidx (biblioitemnumber),
526 KEY itembibnoidx (biblionumber),
527 PRIMARY KEY (itemnumber),
528 UNIQUE barcode (barcode)
532 # Table structure for table 'issues'
534 CREATE TABLE issues (
535 borrowernumber int(11) DEFAULT '0' NOT NULL,
536 itemnumber int(11) DEFAULT '0' NOT NULL,
539 issuingbranch char(18),
541 lastreneweddate date,
544 timestamp timestamp(14),
545 KEY issuesborridx (borrowernumber),
546 KEY issuesitemidx (itemnumber),
547 KEY bordate (borrowernumber,timestamp)
551 # Table structure for table 'items'
554 itemnumber int(11) DEFAULT '0' NOT NULL,
555 biblionumber int(11) DEFAULT '0' NOT NULL,
556 multivolumepart varchar(30),
557 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
558 barcode varchar(9) DEFAULT '' NOT NULL,
559 dateaccessioned date,
560 booksellerid varchar(10),
561 homebranch varchar(4),
563 replacementprice decimal(8,2),
564 replacementpricedate date,
565 datelastborrowed date,
567 multivolume tinyint(1),
569 notforloan tinyint(1),
574 renewals smallint(6),
575 reserves smallint(6),
576 restricted tinyint(1),
577 binding decimal(28,6),
579 holdingbranch varchar(4),
581 timestamp timestamp(14),
582 KEY itembarcodeidx (barcode),
583 KEY itembinoidx (biblioitemnumber),
584 KEY itembibnoidx (biblionumber),
585 PRIMARY KEY (itemnumber),
586 UNIQUE barcode (barcode)
590 # Table structure for table 'itemsprices'
592 CREATE TABLE itemsprices (
594 price1 decimal(28,6),
599 # Table structure for table 'itemtypes'
601 CREATE TABLE itemtypes (
602 itemtype varchar(4) DEFAULT '' NOT NULL,
604 loanlength smallint(6),
605 renewalsallowed smallint(6),
606 rentalcharge double(16,4),
607 UNIQUE itemtype (itemtype)
611 # Table structure for table 'multipart'
613 CREATE TABLE multipart (
614 itemnumber int(11) DEFAULT '0' NOT NULL,
615 other int(11) DEFAULT '0' NOT NULL
619 # Table structure for table 'multivolume'
621 CREATE TABLE multivolume (
622 biblionumber int(11) DEFAULT '0' NOT NULL,
623 multivolumepart varchar(30) DEFAULT '' NOT NULL
627 # Table structure for table 'newitems'
629 CREATE TABLE newitems (
630 itemnumber int(11) DEFAULT '0' NOT NULL,
631 publishercode varchar(18),
632 biblionumber int(11) DEFAULT '0' NOT NULL,
633 multivolumepart varchar(30),
634 barcode varchar(9) DEFAULT '' NOT NULL,
635 dateaccessioned date,
636 booksellerid varchar(10),
637 homebranch varchar(4),
639 replacementprice decimal(28,6),
640 replacementpricedate date,
641 datelastborrowed date,
643 multivolume tinyint(1),
645 notforloan tinyint(1),
650 renewals smallint(6),
651 reserves smallint(6),
652 restricted tinyint(1),
653 binding decimal(28,6),
655 holdingbranch varchar(4),
659 classification varchar(12),
665 publicationyear smallint(6),
666 KEY itembarcodeidx (barcode),
667 KEY itembibnoidx (biblionumber),
668 PRIMARY KEY (itemnumber)
672 # Table structure for table 'printers'
674 CREATE TABLE printers (
675 printername char(40),
681 # Table structure for table 'procedures'
683 CREATE TABLE procedures (
686 proclevel smallint(6)
690 # Table structure for table 'publisher'
692 CREATE TABLE publisher (
693 publishercode varchar(18) DEFAULT '' NOT NULL,
694 publishername text NOT NULL
698 # Table structure for table 'reserveconstraints'
700 CREATE TABLE reserveconstraints (
701 borrowernumber int(11) DEFAULT '0' NOT NULL,
702 reservedate date DEFAULT '0000-00-00' NOT NULL,
703 biblionumber int(11) DEFAULT '0' NOT NULL,
704 biblioitemnumber int(11),
705 timestamp timestamp(14)
709 # Table structure for table 'reserves'
711 CREATE TABLE reserves (
712 borrowernumber int(11) DEFAULT '0' NOT NULL,
713 reservedate date DEFAULT '0000-00-00' NOT NULL,
714 biblionumber int(11) DEFAULT '0' NOT NULL,
715 constrainttype char(1),
716 branchcode varchar(4),
717 notificationdate date,
719 cancellationdate date,
721 priority smallint(6),
723 timestamp timestamp(14),
728 # Table structure for table 'searchstats'
730 CREATE TABLE searchstats (
736 # Table structure for table 'serialissues'
738 CREATE TABLE serialissues (
739 biblionumber int(11) DEFAULT '0' NOT NULL,
740 volume varchar(20) DEFAULT '' NOT NULL,
741 number varchar(20) DEFAULT '' NOT NULL,
742 ordernumber smallint(6),
743 issuedate varchar(20),
748 # Table structure for table 'statistics'
750 CREATE TABLE statistics (
751 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
757 usercode varchar(10),
760 borrowernumber int(11) default NULL,
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),