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,
184 copyrightdate smallint(6),
185 timestamp timestamp(14),
186 KEY blbnoidx (biblionumber),
187 PRIMARY KEY (biblionumber)
191 # Table structure for table 'biblioanalysis'
193 CREATE TABLE biblioanalysis (
194 analyticaltitle text,
195 biblionumber int(11) DEFAULT '0' NOT NULL,
196 analyticalauthor text
200 # Table structure for table 'biblioitems'
202 CREATE TABLE biblioitems (
203 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
204 biblionumber int(11) DEFAULT '0' NOT NULL,
207 classification varchar(25),
214 publicationyear smallint(6),
215 publishercode varchar(255),
217 volumeddesc varchar(255),
218 timestamp timestamp(14),
224 KEY bibinoidx (biblioitemnumber),
225 KEY bibnoidx (biblionumber),
226 PRIMARY KEY (biblioitemnumber)
230 # Table structure for table 'bibliosubject'
232 CREATE TABLE bibliosubject (
233 subject text NOT NULL,
234 biblionumber int(11) DEFAULT '0' NOT NULL
238 # Table structure for table 'bibliosubtitle'
240 CREATE TABLE bibliosubtitle (
241 subtitle text NOT NULL,
242 biblionumber int(11) DEFAULT '0' NOT NULL,
243 KEY bibsubidx (biblionumber)
247 # Table structure for table 'borexp'
249 CREATE TABLE borexp (
250 borrowernumber int(11),
255 # Table structure for table 'borrowers'
257 CREATE TABLE borrowers (
258 borrowernumber int(11) DEFAULT '0' NOT NULL,
259 cardnumber varchar(9) DEFAULT '' NOT NULL,
260 surname text NOT NULL,
261 firstname text NOT NULL,
264 initials text NOT NULL,
265 streetaddress text NOT NULL,
271 altstreetaddress text,
276 branchcode varchar(4) DEFAULT '' NOT NULL,
277 categorycode char(2),
279 gonenoaddress tinyint(1),
288 ethnicity varchar(50),
289 ethnotes varchar(255),
292 altnotes varchar(255),
293 altrelationship varchar(100),
295 phoneday varchar(50),
296 preferredcont char(1),
297 physstreet varchar(100),
298 KEY borrowernumber (borrowernumber),
299 KEY cardnumber (cardnumber)
303 # Table structure for table 'branchcategories'
305 CREATE TABLE branchcategories (
306 categorycode char(2) DEFAULT '' NOT NULL,
308 codedescription text,
309 PRIMARY KEY (categorycode)
313 # Table structure for table 'branchrelations'
315 CREATE TABLE branchrelations (
316 branchcode varchar(4) DEFAULT '' NOT NULL,
317 categorycode varchar(4) DEFAULT '' NOT NULL
321 # Table structure for table 'branches'
323 CREATE TABLE branches (
324 branchcode varchar(4) DEFAULT '' NOT NULL,
325 branchname text NOT NULL,
333 UNIQUE branchcode (branchcode)
337 # Table structure for table 'branchtransfers'
339 CREATE TABLE branchtransfers (
340 itemnumber int(11) DEFAULT '0' NOT NULL,
341 datesent datetime DEFAULT '0000-00-00' NOT NULL,
342 frombranch varchar(4),
343 datearrived datetime,
349 # Table structure for table 'catalogueentry'
351 CREATE TABLE catalogueentry (
352 catalogueentry text NOT NULL,
361 # Table structure for table 'categories'
363 CREATE TABLE categories (
364 categorycode char(2) DEFAULT '' NOT NULL,
366 enrolmentperiod smallint(6),
367 upperagelimit smallint(6),
368 dateofbirthrequired tinyint(1),
369 finetype varchar(30),
371 enrolmentfee decimal(28,6),
372 overduenoticerequired tinyint(1),
373 issuelimit smallint(6),
374 reservefee decimal(28,6),
375 UNIQUE categorycode (categorycode)
379 # Table structure for table 'categoryitem'
381 CREATE TABLE categoryitem (
382 categorycode char(2) DEFAULT '' NOT NULL,
383 itemtype varchar(4) DEFAULT '' NOT NULL,
384 restrictedtype tinyint(1),
385 rentaldiscount decimal(28,6),
386 reservecharge decimal(28,6),
389 chargeperiod int(11),
391 chargename varchar(100)
395 # Table structure for table 'classification'
397 CREATE TABLE classification (
398 classification varchar(12) DEFAULT '' NOT NULL
402 # Table structure for table 'currency'
404 CREATE TABLE currency (
405 currency varchar(10),
410 # Table structure for table 'deletedbiblio'
412 CREATE TABLE deletedbiblio (
413 biblionumber int(11) DEFAULT '0' NOT NULL,
420 copyrightdate smallint(6),
421 timestamp timestamp(14),
422 KEY blbnoidx (biblionumber),
423 PRIMARY KEY (biblionumber)
427 # Table structure for table 'deletedbiblioitems'
429 CREATE TABLE deletedbiblioitems (
430 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
431 biblionumber int(11) DEFAULT '0' NOT NULL,
434 classification varchar(25),
440 publicationyear smallint(6),
441 publishercode varchar(255),
443 volumeddesc varchar(255),
444 timestamp timestamp(14),
449 KEY bibinoidx (biblioitemnumber),
450 KEY bibnoidx (biblionumber),
451 PRIMARY KEY (biblioitemnumber)
455 # Table structure for table 'deletedborrowers'
457 CREATE TABLE deletedborrowers (
458 borrowernumber int(11) DEFAULT '0' NOT NULL,
459 cardnumber varchar(9) DEFAULT '' NOT NULL,
460 surname text NOT NULL,
461 firstname text NOT NULL,
464 initials text NOT NULL,
465 streetaddress text NOT NULL,
471 altstreetaddress text,
476 branchcode varchar(4) DEFAULT '' NOT NULL,
477 categorycode char(2),
479 gonenoaddress tinyint(1),
488 ethnicity varchar(50),
489 ethnotes varchar(255),
492 altnotes varchar(255),
493 altrelationship varchar(100),
495 phoneday varchar(50),
496 preferredcont varchar(100),
497 physstreet varchar(100),
498 KEY borrowernumber (borrowernumber),
499 KEY cardnumber (cardnumber)
503 # Table structure for table 'deleteditems'
505 CREATE TABLE deleteditems (
506 itemnumber int(11) DEFAULT '0' NOT NULL,
507 biblionumber int(11) DEFAULT '0' NOT NULL,
508 multivolumepart varchar(30),
509 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
510 barcode varchar(9) DEFAULT '' NOT NULL,
511 dateaccessioned date,
512 booksellerid varchar(10),
513 homebranch varchar(4),
515 replacementprice decimal(28,6),
516 replacementpricedate date,
517 datelastborrowed date,
519 multivolume tinyint(1),
521 notforloan tinyint(1),
526 renewals smallint(6),
527 reserves smallint(6),
528 restricted tinyint(1),
529 binding decimal(28,6),
531 holdingbranch varchar(4),
533 timestamp timestamp(14),
534 KEY itembarcodeidx (barcode),
535 KEY itembinoidx (biblioitemnumber),
536 KEY itembibnoidx (biblionumber),
537 PRIMARY KEY (itemnumber),
538 UNIQUE barcode (barcode)
542 # Table structure for table 'issues'
544 CREATE TABLE issues (
545 borrowernumber int(11) DEFAULT '0' NOT NULL,
546 itemnumber int(11) DEFAULT '0' NOT NULL,
549 issuingbranch char(18),
551 lastreneweddate date,
554 timestamp timestamp(14),
555 KEY issuesborridx (borrowernumber),
556 KEY issuesitemidx (itemnumber),
557 KEY bordate (borrowernumber,timestamp)
561 # Table structure for table 'items'
564 itemnumber int(11) DEFAULT '0' NOT NULL,
565 biblionumber int(11) DEFAULT '0' NOT NULL,
566 multivolumepart varchar(30),
567 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
568 barcode varchar(9) DEFAULT '' NOT NULL,
569 dateaccessioned date,
570 booksellerid varchar(10),
571 homebranch varchar(4),
573 replacementprice decimal(8,2),
574 replacementpricedate date,
575 datelastborrowed date,
577 multivolume tinyint(1),
579 notforloan tinyint(1),
584 renewals smallint(6),
585 reserves smallint(6),
586 restricted tinyint(1),
587 binding decimal(28,6),
589 holdingbranch varchar(4),
591 timestamp timestamp(14),
592 KEY itembarcodeidx (barcode),
593 KEY itembinoidx (biblioitemnumber),
594 KEY itembibnoidx (biblionumber),
595 PRIMARY KEY (itemnumber),
596 UNIQUE barcode (barcode)
600 # Table structure for table 'itemsprices'
602 CREATE TABLE itemsprices (
604 price1 decimal(28,6),
609 # Table structure for table 'itemtypes'
611 CREATE TABLE itemtypes (
612 itemtype varchar(4) DEFAULT '' NOT NULL,
614 loanlength smallint(6),
615 renewalsallowed smallint(6),
616 rentalcharge double(16,4),
617 UNIQUE itemtype (itemtype)
621 # Table structure for table 'multipart'
623 CREATE TABLE multipart (
624 itemnumber int(11) DEFAULT '0' NOT NULL,
625 other int(11) DEFAULT '0' NOT NULL
629 # Table structure for table 'multivolume'
631 CREATE TABLE multivolume (
632 biblionumber int(11) DEFAULT '0' NOT NULL,
633 multivolumepart varchar(30) DEFAULT '' NOT NULL
637 # Table structure for table 'newitems'
639 CREATE TABLE newitems (
640 itemnumber int(11) DEFAULT '0' NOT NULL,
641 publishercode varchar(18),
642 biblionumber int(11) DEFAULT '0' NOT NULL,
643 multivolumepart varchar(30),
644 barcode varchar(9) DEFAULT '' NOT NULL,
645 dateaccessioned date,
646 booksellerid varchar(10),
647 homebranch varchar(4),
649 replacementprice decimal(28,6),
650 replacementpricedate date,
651 datelastborrowed date,
653 multivolume tinyint(1),
655 notforloan tinyint(1),
660 renewals smallint(6),
661 reserves smallint(6),
662 restricted tinyint(1),
663 binding decimal(28,6),
665 holdingbranch varchar(4),
669 classification varchar(12),
675 publicationyear smallint(6),
676 KEY itembarcodeidx (barcode),
677 KEY itembibnoidx (biblionumber),
678 PRIMARY KEY (itemnumber)
682 # Table structure for table 'printers'
684 CREATE TABLE printers (
685 printername char(40),
691 # Table structure for table 'procedures'
693 CREATE TABLE procedures (
696 proclevel smallint(6)
700 # Table structure for table 'publisher'
702 CREATE TABLE publisher (
703 publishercode varchar(18) DEFAULT '' NOT NULL,
704 publishername text NOT NULL
708 # Table structure for table 'reserveconstraints'
710 CREATE TABLE reserveconstraints (
711 borrowernumber int(11) DEFAULT '0' NOT NULL,
712 reservedate date DEFAULT '0000-00-00' NOT NULL,
713 biblionumber int(11) DEFAULT '0' NOT NULL,
714 biblioitemnumber int(11),
715 timestamp timestamp(14)
719 # Table structure for table 'reserves'
721 CREATE TABLE reserves (
722 borrowernumber int(11) DEFAULT '0' NOT NULL,
723 reservedate date DEFAULT '0000-00-00' NOT NULL,
724 biblionumber int(11) DEFAULT '0' NOT NULL,
725 constrainttype char(1),
726 branchcode varchar(4),
727 notificationdate date,
729 cancellationdate date,
731 priority smallint(6),
733 timestamp timestamp(14),
738 # Table structure for table 'searchstats'
740 CREATE TABLE searchstats (
746 # Table structure for table 'serialissues'
748 CREATE TABLE serialissues (
749 biblionumber int(11) DEFAULT '0' NOT NULL,
750 volume varchar(20) DEFAULT '' NOT NULL,
751 number varchar(20) DEFAULT '' NOT NULL,
752 ordernumber smallint(6),
753 issuedate varchar(20),
758 # Table structure for table 'statistics'
760 CREATE TABLE statistics (
761 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
767 usercode varchar(10),
770 KEY timeidx (datetime)
774 # Table structure for table 'stopwords'
776 CREATE TABLE stopwords (
781 # Table structure for table 'systempreferences'
783 CREATE TABLE systempreferences (
784 variable char(50) DEFAULT '' NOT NULL,
786 PRIMARY KEY (variable)
790 # Table structure for table 'users'
793 usercode varchar(10),