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'
342 CREATE TABLE branchtransfers (
343 itemnumber int(11) DEFAULT '0' NOT NULL,
344 datesent datetime DEFAULT '0000-00-00' NOT NULL,
345 frombranch varchar(4),
346 datearrived datetime,
352 # Table structure for table 'catalogueentry'
354 CREATE TABLE catalogueentry (
355 catalogueentry text NOT NULL,
364 # Table structure for table 'categories'
366 CREATE TABLE categories (
367 categorycode char(2) DEFAULT '' NOT NULL,
369 enrolmentperiod smallint(6),
370 upperagelimit smallint(6),
371 dateofbirthrequired tinyint(1),
372 finetype varchar(30),
374 enrolmentfee decimal(28,6),
375 overduenoticerequired tinyint(1),
376 issuelimit smallint(6),
377 reservefee decimal(28,6),
378 UNIQUE categorycode (categorycode)
382 # Table structure for table 'categoryitem'
384 CREATE TABLE categoryitem (
385 categorycode char(2) DEFAULT '' NOT NULL,
386 itemtype varchar(4) DEFAULT '' NOT NULL,
387 restrictedtype tinyint(1),
388 rentaldiscount decimal(28,6),
389 reservecharge decimal(28,6),
392 chargeperiod int(11),
394 chargename varchar(100)
398 # Table structure for table 'classification'
400 CREATE TABLE classification (
401 classification varchar(12) DEFAULT '' NOT NULL
405 # Table structure for table 'currency'
407 CREATE TABLE currency (
408 currency varchar(10),
413 # Table structure for table 'deletedbiblio'
415 CREATE TABLE deletedbiblio (
416 biblionumber int(11) DEFAULT '0' NOT NULL,
424 copyrightdate smallint(6),
425 timestamp timestamp(14),
426 KEY blbnoidx (biblionumber),
427 PRIMARY KEY (biblionumber)
431 # Table structure for table 'deletedbiblioitems'
433 CREATE TABLE deletedbiblioitems (
434 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
435 biblionumber int(11) DEFAULT '0' NOT NULL,
438 classification varchar(25),
444 publicationyear smallint(6),
445 publishercode varchar(255),
447 volumeddesc varchar(255),
448 timestamp timestamp(14),
453 KEY bibinoidx (biblioitemnumber),
454 KEY bibnoidx (biblionumber),
455 PRIMARY KEY (biblioitemnumber)
459 # Table structure for table 'deletedborrowers'
461 CREATE TABLE deletedborrowers (
462 borrowernumber int(11) DEFAULT '0' NOT NULL,
463 cardnumber varchar(9) DEFAULT '' NOT NULL,
464 surname text NOT NULL,
465 firstname text NOT NULL,
468 initials text NOT NULL,
469 streetaddress text NOT NULL,
475 altstreetaddress text,
480 branchcode varchar(4) DEFAULT '' NOT NULL,
481 categorycode char(2),
483 gonenoaddress tinyint(1),
492 ethnicity varchar(50),
493 ethnotes varchar(255),
496 altnotes varchar(255),
497 altrelationship varchar(100),
499 phoneday varchar(50),
500 preferredcont varchar(100),
501 physstreet varchar(100),
502 KEY borrowernumber (borrowernumber),
503 KEY cardnumber (cardnumber)
507 # Table structure for table 'deleteditems'
509 CREATE TABLE deleteditems (
510 itemnumber int(11) DEFAULT '0' NOT NULL,
511 biblionumber int(11) DEFAULT '0' NOT NULL,
512 multivolumepart varchar(30),
513 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
514 barcode varchar(9) DEFAULT '' NOT NULL,
515 dateaccessioned date,
516 booksellerid varchar(10),
517 homebranch varchar(4),
519 replacementprice decimal(28,6),
520 replacementpricedate date,
521 datelastborrowed date,
523 multivolume tinyint(1),
525 notforloan tinyint(1),
530 renewals smallint(6),
531 reserves smallint(6),
532 restricted tinyint(1),
533 binding decimal(28,6),
535 holdingbranch varchar(4),
537 timestamp timestamp(14),
538 KEY itembarcodeidx (barcode),
539 KEY itembinoidx (biblioitemnumber),
540 KEY itembibnoidx (biblionumber),
541 PRIMARY KEY (itemnumber),
542 UNIQUE barcode (barcode)
546 # Table structure for table 'ethnicity'
549 CREATE TABLE ethnicity (
550 code varchar(10) NOT NULL default '',
551 name varchar(255) default NULL,
556 # Table structure for table 'issues'
558 CREATE TABLE issues (
559 borrowernumber int(11) DEFAULT '0' NOT NULL,
560 itemnumber int(11) DEFAULT '0' NOT NULL,
563 issuingbranch char(18),
565 lastreneweddate date,
568 timestamp timestamp(14),
569 KEY issuesborridx (borrowernumber),
570 KEY issuesitemidx (itemnumber),
571 KEY bordate (borrowernumber,timestamp)
575 # Table structure for table 'items'
578 itemnumber int(11) DEFAULT '0' NOT NULL,
579 biblionumber int(11) DEFAULT '0' NOT NULL,
580 multivolumepart varchar(30),
581 biblioitemnumber int(11) DEFAULT '0' NOT NULL,
582 barcode varchar(9) DEFAULT '' NOT NULL,
583 dateaccessioned date,
584 booksellerid varchar(10),
585 homebranch varchar(4),
587 replacementprice decimal(8,2),
588 replacementpricedate date,
589 datelastborrowed date,
591 multivolume tinyint(1),
593 notforloan tinyint(1),
598 renewals smallint(6),
599 reserves smallint(6),
600 restricted tinyint(1),
601 binding decimal(28,6),
603 holdingbranch varchar(4),
605 timestamp timestamp(14),
606 KEY itembarcodeidx (barcode),
607 KEY itembinoidx (biblioitemnumber),
608 KEY itembibnoidx (biblionumber),
609 PRIMARY KEY (itemnumber),
610 UNIQUE barcode (barcode)
614 # Table structure for table 'itemsprices'
616 CREATE TABLE itemsprices (
618 price1 decimal(28,6),
623 # Table structure for table 'itemtypes'
625 CREATE TABLE itemtypes (
626 itemtype varchar(4) DEFAULT '' NOT NULL,
628 loanlength smallint(6),
629 renewalsallowed smallint(6),
630 rentalcharge double(16,4),
631 UNIQUE itemtype (itemtype)
635 # Table structure for table 'multipart'
637 CREATE TABLE multipart (
638 itemnumber int(11) DEFAULT '0' NOT NULL,
639 other int(11) DEFAULT '0' NOT NULL
643 # Table structure for table 'multivolume'
645 CREATE TABLE multivolume (
646 biblionumber int(11) DEFAULT '0' NOT NULL,
647 multivolumepart varchar(30) DEFAULT '' NOT NULL
651 # Table structure for table 'newitems'
653 CREATE TABLE newitems (
654 itemnumber int(11) DEFAULT '0' NOT NULL,
655 publishercode varchar(18),
656 biblionumber int(11) DEFAULT '0' NOT NULL,
657 multivolumepart varchar(30),
658 barcode varchar(9) DEFAULT '' NOT NULL,
659 dateaccessioned date,
660 booksellerid varchar(10),
661 homebranch varchar(4),
663 replacementprice decimal(28,6),
664 replacementpricedate date,
665 datelastborrowed date,
667 multivolume tinyint(1),
669 notforloan tinyint(1),
674 renewals smallint(6),
675 reserves smallint(6),
676 restricted tinyint(1),
677 binding decimal(28,6),
679 holdingbranch varchar(4),
683 classification varchar(12),
689 publicationyear smallint(6),
690 KEY itembarcodeidx (barcode),
691 KEY itembibnoidx (biblionumber),
692 PRIMARY KEY (itemnumber)
696 # Table structure for table 'printers'
698 CREATE TABLE printers (
699 printername char(40),
705 # Table structure for table 'procedures'
707 CREATE TABLE procedures (
710 proclevel smallint(6)
714 # Table structure for table 'publisher'
716 CREATE TABLE publisher (
717 publishercode varchar(18) DEFAULT '' NOT NULL,
718 publishername text NOT NULL
722 # Table structure for table 'reserveconstraints'
724 CREATE TABLE reserveconstraints (
725 borrowernumber int(11) DEFAULT '0' NOT NULL,
726 reservedate date DEFAULT '0000-00-00' NOT NULL,
727 biblionumber int(11) DEFAULT '0' NOT NULL,
728 biblioitemnumber int(11),
729 timestamp timestamp(14)
733 # Table structure for table 'reserves'
735 CREATE TABLE reserves (
736 borrowernumber int(11) DEFAULT '0' NOT NULL,
737 reservedate date DEFAULT '0000-00-00' NOT NULL,
738 biblionumber int(11) DEFAULT '0' NOT NULL,
739 constrainttype char(1),
740 branchcode varchar(4),
741 notificationdate date,
743 cancellationdate date,
745 priority smallint(6),
747 timestamp timestamp(14),
752 # Table structure for table 'searchstats'
754 CREATE TABLE searchstats (
760 # Table structure for table 'serialissues'
762 CREATE TABLE serialissues (
763 biblionumber int(11) DEFAULT '0' NOT NULL,
764 volume varchar(20) DEFAULT '' NOT NULL,
765 number varchar(20) DEFAULT '' NOT NULL,
766 ordernumber smallint(6),
767 issuedate varchar(20),
772 # Table structure for table 'statistics'
774 CREATE TABLE statistics (
775 datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
781 usercode varchar(10),
784 KEY timeidx (datetime)
788 # Table structure for table 'stopwords'
790 CREATE TABLE stopwords (
795 # Table structure for table 'systempreferences'
797 CREATE TABLE systempreferences (
798 variable char(50) DEFAULT '' NOT NULL,
800 explanation char(200) default '',
801 PRIMARY KEY (variable)
805 # Table structure for table 'users'
808 usercode varchar(10),
815 # Table structure for table 'websites'
818 CREATE TABLE websites (
819 websitenumber int(11) NOT NULL auto_increment,
820 biblionumber int(11) NOT NULL default '0',
824 PRIMARY KEY (websitenumber)
825 ) TYPE=ISAM PACK_KEYS=1;
827 CREATE TABLE uploadedmarc (
828 id int(11) NOT NULL auto_increment,
830 hidden smallint(6) default NULL,
831 name varchar(255) default NULL,
836 CREATE TABLE marcrecorddone (
837 isbn char(40) default NULL,
838 issn char(40) default NULL,
839 lccn char(40) default NULL