From eac3a7b19a8aa7cda34aac396f5093c213a4aa5a Mon Sep 17 00:00:00 2001 From: rangi Date: Mon, 12 Mar 2001 22:43:47 +0000 Subject: [PATCH] Database definition file, checked into cvs to make keeping database current easier --- database.mysql | 787 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 787 insertions(+) create mode 100644 database.mysql diff --git a/database.mysql b/database.mysql new file mode 100644 index 0000000000..f51d5d9d47 --- /dev/null +++ b/database.mysql @@ -0,0 +1,787 @@ +# MySQL dump 7.1 +# +# Host: localhost Database: c4test +#-------------------------------------------------------- +# Server version 3.22.32-log + +# +# Table structure for table 'accountlines' +# +CREATE TABLE accountlines ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + accountno smallint(6) DEFAULT '0' NOT NULL, + itemnumber int(11), + date date, + amount decimal(28,6), + description text, + dispute text, + accounttype varchar(5), + amountoutstanding decimal(28,6), + timestamp timestamp(14), + KEY acctsborridx (borrowernumber), + KEY timeidx (timestamp) +); + +# +# Table structure for table 'accountoffsets' +# +CREATE TABLE accountoffsets ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + accountno smallint(6) DEFAULT '0' NOT NULL, + offsetaccount smallint(6) DEFAULT '0' NOT NULL, + offsetamount decimal(28,6), + timestamp timestamp(14) +); + +# +# Table structure for table 'additionalauthors' +# +CREATE TABLE additionalauthors ( + author text NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + KEY bibidx (biblionumber) +); + +# +# Table structure for table 'aqbookfund' +# +CREATE TABLE aqbookfund ( + bookfundid varchar(5) DEFAULT '' NOT NULL, + bookfundname text, + bookfundgroup varchar(5) +); + +# +# Table structure for table 'aqbooksellers' +# +CREATE TABLE aqbooksellers ( + id int(11), + name text, + address1 text, + address2 text, + address3 text, + address4 text, + phone varchar(30), + accountnumber text, + othersupplier text, + currency char(3) DEFAULT '' NOT NULL, + deliverydays smallint(6), + followupdays smallint(6), + followupscancel smallint(6), + specialty text, + booksellerfax text, + notes text, + bookselleremail text, + booksellerurl text, + contact varchar(100), + postal text, + url varchar(255), + contpos varchar(100), + contphone varchar(100), + contfax varchar(100), + contaltphone varchar(100), + contemail varchar(100), + contnotes text, + active tinyint(4), + listprice varchar(5), + invoiceprice varchar(5), + gstreg tinyint(4), + listincgst tinyint(4), + invoiceincgst tinyint(4), + discount float(6,4), + fax varchar(50) +); + +# +# Table structure for table 'aqbudget' +# +CREATE TABLE aqbudget ( + bookfundid char(5) DEFAULT '' NOT NULL, + startdate date DEFAULT '0000-00-00' NOT NULL, + enddate date, + budgetamount decimal(13,2) +); + +# +# Table structure for table 'aqorderbreakdown' +# +CREATE TABLE aqorderbreakdown ( + ordernumber int(11), + linenumber int(11), + branchcode char(4), + bookfundid char(5) DEFAULT '' NOT NULL, + allocation smallint(6) +); + +# +# Table structure for table 'aqorderdelivery' +# +CREATE TABLE aqorderdelivery ( + ordernumber date DEFAULT '0000-00-00' NOT NULL, + deliverynumber smallint(6) DEFAULT '0' NOT NULL, + deliverydate varchar(18), + qtydelivered smallint(6), + deliverycomments text +); + +# +# Table structure for table 'aqorders' +# +CREATE TABLE aqorders ( + ordernumber int(11) DEFAULT '0' NOT NULL auto_increment, + biblionumber int(11), + title text, + requisitionedby varchar(10), + authorisedby varchar(10), + booksellerid varchar(10) DEFAULT '' NOT NULL, + deliverydays smallint(6), + followupdays smallint(6), + numberfollowupsallowed smallint(6), + numberfollowupssent smallint(6), + entrydate date, + dateprinted date, + quantity smallint(6), + currency char(3), + listprice decimal(28,6), + totalamount decimal(28,6), + datereceived date, + booksellerinvoicenumber text, + freight decimal(28,6), + unitprice decimal(28,6), + quantityreceived smallint(6), + sourced text, + cancelledby varchar(10), + quantityreceiveddamaged smallint(6), + datecancellationprinted date, + notes text, + supplierreference text, + purchaseordernumber text, + subscription tinyint(1), + subscriptionfrom date, + subscriptionto date, + serialid varchar(30), + basketno int(11), + biblioitemnumber int(11), + timestamp timestamp(14), + rrp decimal(13,2), + ecost decimal(13,2), + gst decimal(13,2), + PRIMARY KEY (ordernumber) +); + +# +# Table structure for table 'biblio' +# +CREATE TABLE biblio ( + biblionumber int(11) DEFAULT '0' NOT NULL, + author text, + title text, + unititle text, + notes text, + serial tinyint(1), + seriestitle text, + copyrightdate smallint(6), + timestamp timestamp(14), + KEY blbnoidx (biblionumber), + PRIMARY KEY (biblionumber) +); + +# +# Table structure for table 'biblioanalysis' +# +CREATE TABLE biblioanalysis ( + analyticaltitle text, + biblionumber int(11) DEFAULT '0' NOT NULL, + analyticalauthor text +); + +# +# Table structure for table 'biblioitems' +# +CREATE TABLE biblioitems ( + biblioitemnumber int(11) DEFAULT '0' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + volume text, + number text, + classification varchar(25), + itemtype varchar(4), + isbn varchar(14), + issn varchar(9), + dewey double(8,6), + subclass char(3), + publicationyear smallint(6), + publishercode varchar(255), + volumedate date, + volumeddesc varchar(255), + timestamp timestamp(14), + illus varchar(255), + pages varchar(255), + notes text, + size varchar(255), + place varchar(255), + KEY bibinoidx (biblioitemnumber), + KEY bibnoidx (biblionumber), + PRIMARY KEY (biblioitemnumber) +); + +# +# Table structure for table 'bibliosubject' +# +CREATE TABLE bibliosubject ( + subject text NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL +); + +# +# Table structure for table 'bibliosubtitle' +# +CREATE TABLE bibliosubtitle ( + subtitle text NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + KEY bibsubidx (biblionumber) +); + +# +# Table structure for table 'borexp' +# +CREATE TABLE borexp ( + borrowernumber int(11), + newexp date +); + +# +# Table structure for table 'borrowers' +# +CREATE TABLE borrowers ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + cardnumber varchar(9) DEFAULT '' NOT NULL, + surname text NOT NULL, + firstname text NOT NULL, + title text, + othernames text, + initials text NOT NULL, + streetaddress text NOT NULL, + suburb text, + city text NOT NULL, + phone text NOT NULL, + emailaddress text, + faxnumber text, + altstreetaddress text, + altsuburb text, + altcity text, + altphone text, + dateofbirth date, + branchcode varchar(4) DEFAULT '' NOT NULL, + categorycode char(2), + dateenrolled date, + gonenoaddress tinyint(1), + lost tinyint(1), + debarred tinyint(1), + studentnumber text, + school text, + contactname text, + borrowernotes text, + guarantor int(11), + area char(2), + ethnicity varchar(50), + ethnotes varchar(255), + sex char(1), + expiry date, + altnotes varchar(255), + altrelationship varchar(100), + streetcity text, + phoneday varchar(50), + preferredcont char(1), + physstreet varchar(100), + KEY borrowernumber (borrowernumber), + KEY cardnumber (cardnumber) +); + +# +# Table structure for table 'branchcategories' +# +CREATE TABLE branchcategories ( + categorycode char(2) DEFAULT '' NOT NULL, + branchcode char(4) DEFAULT '' NOT NULL, + branchholding int(11) +); + +# +# Table structure for table 'branches' +# +CREATE TABLE branches ( + branchcode varchar(4) DEFAULT '' NOT NULL, + branchname text NOT NULL, + branchaddress1 text, + branchaddress2 text, + branchaddress3 text, + branchphone text, + branchfax text, + branchemail text, + issuing tinyint(4), + UNIQUE branchcode (branchcode) +); + +# +# Table structure for table 'branchtransfers' +# +CREATE TABLE branchtransfers ( + itemnumber int(11) DEFAULT '0' NOT NULL, + datesent date DEFAULT '0000-00-00' NOT NULL, + frombranch varchar(4), + datearrived date, + tobranch varchar(4), + comments text +); + +# +# Table structure for table 'catalogueentry' +# +CREATE TABLE catalogueentry ( + catalogueentry text NOT NULL, + entrytype char(2), + see text, + seealso text, + seeinstead text, + biblionumber int(11) +); + +# +# Table structure for table 'categories' +# +CREATE TABLE categories ( + categorycode char(2) DEFAULT '' NOT NULL, + description text, + enrolmentperiod smallint(6), + upperagelimit smallint(6), + dateofbirthrequired tinyint(1), + finetype varchar(30), + bulk tinyint(1), + enrolmentfee decimal(28,6), + overduenoticerequired tinyint(1), + issuelimit smallint(6), + reservefee decimal(28,6), + UNIQUE categorycode (categorycode) +); + +# +# Table structure for table 'categoryitem' +# +CREATE TABLE categoryitem ( + categorycode char(2) DEFAULT '' NOT NULL, + itemtype varchar(4) DEFAULT '' NOT NULL, + restrictedtype tinyint(1), + rentaldiscount decimal(28,6), + reservecharge decimal(28,6), + fine decimal(28,6), + firstremind int(11), + chargeperiod int(11), + accountsent int(11), + chargename varchar(100) +); + +# +# Table structure for table 'classification' +# +CREATE TABLE classification ( + classification varchar(12) DEFAULT '' NOT NULL +); + +# +# Table structure for table 'currency' +# +CREATE TABLE currency ( + currency varchar(10), + rate float(7,5) +); + +# +# Table structure for table 'deletedbiblio' +# +CREATE TABLE deletedbiblio ( + biblionumber int(11) DEFAULT '0' NOT NULL, + author text, + title text, + unititle text, + notes text, + serial tinyint(1), + seriestitle text, + copyrightdate smallint(6), + timestamp timestamp(14), + KEY blbnoidx (biblionumber), + PRIMARY KEY (biblionumber) +); + +# +# Table structure for table 'deletedbiblioitems' +# +CREATE TABLE deletedbiblioitems ( + biblioitemnumber int(11) DEFAULT '0' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + volume text, + number text, + classification varchar(25), + itemtype varchar(4), + isbn varchar(14), + issn varchar(9), + dewey double(8,6), + subclass char(3), + publicationyear smallint(6), + publishercode varchar(255), + volumedate date, + volumeddesc varchar(255), + timestamp timestamp(14), + illus varchar(255), + pages varchar(255), + notes text, + size varchar(255), + KEY bibinoidx (biblioitemnumber), + KEY bibnoidx (biblionumber), + PRIMARY KEY (biblioitemnumber) +); + +# +# Table structure for table 'deletedborrowers' +# +CREATE TABLE deletedborrowers ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + cardnumber varchar(9) DEFAULT '' NOT NULL, + surname text NOT NULL, + firstname text NOT NULL, + title text, + othernames text, + initials text NOT NULL, + streetaddress text NOT NULL, + suburb text, + city text NOT NULL, + phone text NOT NULL, + emailaddress text, + faxnumber text, + altstreetaddress text, + altsuburb text, + altcity text, + altphone text, + dateofbirth date, + branchcode varchar(4) DEFAULT '' NOT NULL, + categorycode char(2), + dateenrolled date, + gonenoaddress tinyint(1), + lost tinyint(1), + debarred tinyint(1), + studentnumber text, + school text, + contactname text, + borrowernotes text, + guarantor int(11), + area char(2), + ethnicity varchar(50), + ethnotes varchar(255), + sex char(1), + expiry date, + altnotes varchar(255), + altrelationship varchar(100), + streetcity text, + phoneday varchar(50), + preferredcont varchar(100), + physstreet varchar(100), + KEY borrowernumber (borrowernumber), + KEY cardnumber (cardnumber) +); + +# +# Table structure for table 'deleteditems' +# +CREATE TABLE deleteditems ( + itemnumber int(11) DEFAULT '0' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + multivolumepart varchar(30), + biblioitemnumber int(11) DEFAULT '0' NOT NULL, + barcode varchar(9) DEFAULT '' NOT NULL, + dateaccessioned date, + booksellerid varchar(10), + homebranch varchar(4), + price decimal(28,6), + replacementprice decimal(28,6), + replacementpricedate date, + datelastborrowed date, + datelastseen date, + multivolume tinyint(1), + stack tinyint(1), + notforloan tinyint(1), + itemlost tinyint(1), + wthdrawn tinyint(1), + bulk varchar(30), + issues smallint(6), + renewals smallint(6), + reserves smallint(6), + restricted tinyint(1), + binding decimal(28,6), + itemnotes text, + holdingbranch varchar(4), + interim tinyint(1), + timestamp timestamp(14), + KEY itembarcodeidx (barcode), + KEY itembinoidx (biblioitemnumber), + KEY itembibnoidx (biblionumber), + PRIMARY KEY (itemnumber), + UNIQUE barcode (barcode) +); + +# +# Table structure for table 'issues' +# +CREATE TABLE issues ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + itemnumber int(11) DEFAULT '0' NOT NULL, + date_due date, + branchcode char(4), + issuingbranch char(18), + returndate date, + lastreneweddate date, + return char(4), + renewals tinyint(4), + timestamp timestamp(14), + KEY issuesborridx (borrowernumber), + KEY issuesitemidx (itemnumber), + KEY bordate (borrowernumber,timestamp) +); + +# +# Table structure for table 'items' +# +CREATE TABLE items ( + itemnumber int(11) DEFAULT '0' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + multivolumepart varchar(30), + biblioitemnumber int(11) DEFAULT '0' NOT NULL, + barcode varchar(9) DEFAULT '' NOT NULL, + dateaccessioned date, + booksellerid varchar(10), + homebranch varchar(4), + price decimal(8,2), + replacementprice decimal(8,2), + replacementpricedate date, + datelastborrowed date, + datelastseen date, + multivolume tinyint(1), + stack tinyint(1), + notforloan tinyint(1), + itemlost tinyint(1), + wthdrawn tinyint(1), + bulk varchar(30), + issues smallint(6), + renewals smallint(6), + reserves smallint(6), + restricted tinyint(1), + binding decimal(28,6), + itemnotes text, + holdingbranch varchar(4), + interim tinyint(1), + timestamp timestamp(14), + KEY itembarcodeidx (barcode), + KEY itembinoidx (biblioitemnumber), + KEY itembibnoidx (biblionumber), + PRIMARY KEY (itemnumber), + UNIQUE barcode (barcode) +); + +# +# Table structure for table 'itemsprices' +# +CREATE TABLE itemsprices ( + itemnumber int(11), + price1 decimal(28,6), + price2 decimal(28,6) +); + +# +# Table structure for table 'itemtypes' +# +CREATE TABLE itemtypes ( + itemtype varchar(4) DEFAULT '' NOT NULL, + description text, + loanlength smallint(6), + renewalsallowed smallint(6), + rentalcharge double(16,4), + UNIQUE itemtype (itemtype) +); + +# +# Table structure for table 'multipart' +# +CREATE TABLE multipart ( + itemnumber int(11) DEFAULT '0' NOT NULL, + other int(11) DEFAULT '0' NOT NULL +); + +# +# Table structure for table 'multivolume' +# +CREATE TABLE multivolume ( + biblionumber int(11) DEFAULT '0' NOT NULL, + multivolumepart varchar(30) DEFAULT '' NOT NULL +); + +# +# Table structure for table 'newitems' +# +CREATE TABLE newitems ( + itemnumber int(11) DEFAULT '0' NOT NULL, + publishercode varchar(18), + biblionumber int(11) DEFAULT '0' NOT NULL, + multivolumepart varchar(30), + barcode varchar(9) DEFAULT '' NOT NULL, + dateaccessioned date, + booksellerid varchar(10), + homebranch varchar(4), + price decimal(28,6), + replacementprice decimal(28,6), + replacementpricedate date, + datelastborrowed date, + datelastseen date, + multivolume tinyint(1), + stack tinyint(1), + notforloan tinyint(1), + itemlost tinyint(1), + wthdrawn tinyint(1), + bulk varchar(30), + issues smallint(6), + renewals smallint(6), + reserves smallint(6), + restricted tinyint(1), + binding decimal(28,6), + itemnotes text, + holdingbranch varchar(4), + interim tinyint(1), + volume text, + number text, + classification varchar(12), + itemtype varchar(4), + isbn varchar(14), + issn varchar(9), + dewey double(16,4), + subclass char(3), + publicationyear smallint(6), + KEY itembarcodeidx (barcode), + KEY itembibnoidx (biblionumber), + PRIMARY KEY (itemnumber) +); + +# +# Table structure for table 'printers' +# +CREATE TABLE printers ( + printername char(40), + printqueue char(20), + printtype char(20) +); + +# +# Table structure for table 'procedures' +# +CREATE TABLE procedures ( + proccode varchar(4), + procdesc text, + proclevel smallint(6) +); + +# +# Table structure for table 'publisher' +# +CREATE TABLE publisher ( + publishercode varchar(18) DEFAULT '' NOT NULL, + publishername text NOT NULL +); + +# +# Table structure for table 'reserveconstraints' +# +CREATE TABLE reserveconstraints ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + reservedate date DEFAULT '0000-00-00' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + biblioitemnumber int(11), + timestamp timestamp(14) +); + +# +# Table structure for table 'reserves' +# +CREATE TABLE reserves ( + borrowernumber int(11) DEFAULT '0' NOT NULL, + reservedate date DEFAULT '0000-00-00' NOT NULL, + biblionumber int(11) DEFAULT '0' NOT NULL, + constrainttype char(1), + branchcode varchar(4), + notificationdate date, + reminderdate date, + cancellationdate date, + reservenotes text, + priority smallint(6), + found char(1), + timestamp timestamp(14), + itemnumber int(11) +); + +# +# Table structure for table 'searchstats' +# +CREATE TABLE searchstats ( + time datetime, + searchstring text +); + +# +# Table structure for table 'serialissues' +# +CREATE TABLE serialissues ( + biblionumber int(11) DEFAULT '0' NOT NULL, + volume varchar(20) DEFAULT '' NOT NULL, + number varchar(20) DEFAULT '' NOT NULL, + ordernumber smallint(6), + issuedate varchar(20), + received varchar(18) +); + +# +# Table structure for table 'statistics' +# +CREATE TABLE statistics ( + datetime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, + branch varchar(4), + proccode varchar(4), + value double(16,4), + type varchar(16), + other text, + usercode varchar(10), + itemnumber int(11), + itemtype varchar(4), + KEY timeidx (datetime) +); + +# +# Table structure for table 'stopwords' +# +CREATE TABLE stopwords ( + word varchar(255) +); + +# +# Table structure for table 'systempreferences' +# +CREATE TABLE systempreferences ( + variable char(50) DEFAULT '' NOT NULL, + value char(200), + PRIMARY KEY (variable) +); + +# +# Table structure for table 'users' +# +CREATE TABLE users ( + usercode varchar(10), + username text, + password text, + level smallint(6) +); + -- 2.11.0