-print "connected to your DB. Checking & modifying it\n" unless $silent;
-
-#-------------------
-# Defines
-
-# Tables to add if they don't exist
-my %requiretables = (
- shelfcontents => "( shelfnumber int not null,
- itemnumber int not null,
- flags int)",
- bookshelf => "( shelfnumber int auto_increment primary key,
- shelfname char(255))",
- z3950queue => "( id int auto_increment primary key,
- term text,
- type char(10),
- startdate int,
- enddate int,
- done smallint,
- results longblob,
- numrecords int,
- servers text,
- identifier char(30))",
- z3950results => "( id int auto_increment primary key,
- queryid int,
- server char(255),
- startdate int,
- enddate int,
- results longblob,
- numrecords int,
- numdownloaded int,
- highestseen int,
- active smallint)",
- branchrelations => "( branchcode varchar(4),
- categorycode varchar(4))",
- websites => "( websitenumber int(11) NOT NULL auto_increment,
- biblionumber int(11) NOT NULL default '0',
- title text,
- description text,
- url varchar(255),
- PRIMARY KEY (websitenumber) )",
- marcrecorddone => "( isbn char(40),
- issn char(40),
- lccn char(40),
- controlnumber char(40))",
- uploadedmarc => "( id int(11) NOT NULL auto_increment PRIMARY KEY,
- marc longblob,
- hidden smallint(6) default NULL,
- name varchar(255) default NULL)",
- ethnicity => "( code varchar(10) NOT NULL default '',
- name varchar(255) default NULL,
- PRIMARY KEY (code) )",
- sessions => "( sessionID varchar(255) NOT NULL default '',
- userid varchar(255) default NULL,
- ip varchar(16) default NULL,
- lasttime int,
- PRIMARY KEY (sessionID) )",
- sessionqueries => "( sessionID varchar(255) NOT NULL default '',
- userid char(100) NOT NULL default '',
- ip char(18) NOT NULL default '',
- url text NOT NULL default '' )",
- bibliothesaurus => "( id bigint(20) NOT NULL auto_increment,
- freelib char(255) NOT NULL default '',
- stdlib char(255) NOT NULL default '',
- category char(10) NOT NULL default '',
- level tinyint(4) NOT NULL default '1',
- hierarchy char(80) NOT NULL default '',
- father char(80) NOT NULL default '',
- PRIMARY KEY (id),
- KEY freelib (freelib),
- KEY stdlib (stdlib),
- KEY category (category),
- KEY hierarchy (hierarchy)
- )",
- marc_biblio => "(
- bibid bigint(20) unsigned NOT NULL auto_increment,
- biblionumber int(11) NOT NULL default '0',
- datecreated date NOT NULL default '0000-00-00',
- datemodified date default NULL,
- origincode char(20) default NULL,
- PRIMARY KEY (bibid),
- KEY origincode (origincode),
- KEY biblionumber (biblionumber)
- ) ",
- marc_blob_subfield => "(
- blobidlink bigint(20) NOT NULL auto_increment,
- subfieldvalue longtext NOT NULL,
- PRIMARY KEY (blobidlink)
- ) ",
- marc_subfield_structure => "(
- tagfield char(3) NOT NULL default '',
- tagsubfield char(1) NOT NULL default '',
- liblibrarian char(255) NOT NULL default '',
- libopac char(255) NOT NULL default '',
- repeatable tinyint(4) NOT NULL default '0',
- mandatory tinyint(4) NOT NULL default '0',
- kohafield char(40) default NULL,
- tab tinyint(1) default NULL,
- authorised_value char(10) default NULL,
- thesaurus_category char(10) default NULL,
- value_builder char(80) default NULL,
- PRIMARY KEY (tagfield,tagsubfield),
- KEY kohafield (kohafield),
- KEY tab (tab)
- )",
- marc_subfield_table => "(
- subfieldid bigint(20) unsigned NOT NULL auto_increment,
- bibid bigint(20) unsigned NOT NULL default '0',
- tag char(3) NOT NULL default '',
- tagorder tinyint(4) NOT NULL default '1',
- tag_indicator char(2) NOT NULL default '',
- subfieldcode char(1) NOT NULL default '',
- subfieldorder tinyint(4) NOT NULL default '1',
- subfieldvalue varchar(255) default NULL,
- valuebloblink bigint(20) default NULL,
- PRIMARY KEY (subfieldid),
- KEY bibid (bibid),
- KEY tag (tag),
- KEY tag_indicator (tag_indicator),
- KEY subfieldorder (subfieldorder),
- KEY subfieldcode (subfieldcode),
- KEY subfieldvalue (subfieldvalue),
- KEY tagorder (tagorder)
- )",
- marc_tag_structure => "(
- tagfield char(3) NOT NULL default '',
- liblibrarian char(255) NOT NULL default '',
- libopac char(255) NOT NULL default '',
- repeatable tinyint(4) NOT NULL default '0',
- mandatory tinyint(4) NOT NULL default '0',
- authorised_value char(10) default NULL,
- PRIMARY KEY (tagfield)
- )",
- marc_word => "(
- bibid bigint(20) NOT NULL default '0',
- tag char(3) NOT NULL default '',
- tagorder tinyint(4) NOT NULL default '1',
- subfieldid char(1) NOT NULL default '',
- subfieldorder tinyint(4) NOT NULL default '1',
- word varchar(255) NOT NULL default '',
- sndx_word varchar(255) NOT NULL default '',
- KEY bibid (bibid),
- KEY tag (tag),
- KEY tagorder (tagorder),
- KEY subfieldid (subfieldid),
- KEY subfieldorder (subfieldorder),
- KEY word (word),
- KEY sndx_word (sndx_word)
- )",
- marc_breeding => "( id bigint(20) NOT NULL auto_increment,
- file varchar(80) NOT NULL default '',
- isbn varchar(10) NOT NULL default '',
- title varchar(128) default NULL,
- author varchar(80) default NULL,
- marc text NOT NULL,
- encoding varchar(40) default NULL,
- PRIMARY KEY (id),
- KEY title (title),
- KEY isbn (isbn)
- )",
- authorised_values => "(id int(11) NOT NULL auto_increment,
- category char(10) NOT NULL default '',
- authorised_value char(80) NOT NULL default '',
- lib char(80) NULL,
- PRIMARY KEY (id),
- KEY name (category)
- )",
- userflags => "( bit int(11) NOT NULL default '0',
- flag char(30), flagdesc char(255),
- defaulton int(11)
- )",
- auth_types => "(
- authtypecode char(10) not NULL,
- authtypetext char(255) not NULL,
- auth_tag_to_report char(3) not NULL,
- summary text not NULL,
- PRIMARY KEY (authtypecode)
- )",
- biblio_framework => "(
- frameworkcode char(4) not NULL,
- frameworktext char(255) not NULL,
- PRIMARY KEY (frameworkcode)
- )",
- auth_subfield_structure => "(
- authtypecode char(10) NOT NULL default '',
- tagfield char(3) NOT NULL default '',
- tagsubfield char(1) NOT NULL default '',
- liblibrarian char(255) NOT NULL default '',
- libopac char(255) NOT NULL default '',
- repeatable tinyint(4) NOT NULL default '0',
- mandatory tinyint(4) NOT NULL default '0',
- tab tinyint(1) default NULL,
- authorised_value char(10) default NULL,
- value_builder char(80) default NULL,
- seealso char(255) default NULL,
- PRIMARY KEY (authtypecode,tagfield,tagsubfield),
- KEY tab (authtypecode,tab)
- )",
- auth_tag_structure => "(
- authtypecode char(10) NOT NULL default '',
- tagfield char(3) NOT NULL default '',
- liblibrarian char(255) NOT NULL default '',
- libopac char(255) NOT NULL default '',
- repeatable tinyint(4) NOT NULL default '0',
- mandatory tinyint(4) NOT NULL default '0',
- authorised_value char(10) default NULL,
- PRIMARY KEY (authtypecode,tagfield)
- )",
- auth_header => "(
- authid bigint(20) unsigned NOT NULL auto_increment,
- authtypecode char(10) NOT NULL default '',
- datecreated date NOT NULL default '0000-00-00',
- datemodified date default NULL,
- origincode char(20) default NULL,
- PRIMARY KEY (authid),
- KEY origincode (origincode),
- ) ",
- auth_subfield_table => "(
- subfieldid bigint(20) unsigned NOT NULL auto_increment,
- authid bigint(20) unsigned NOT NULL default '0',
- tag char(3) NOT NULL default '',
- tagorder tinyint(4) NOT NULL default '1',
- tag_indicator char(2) NOT NULL default '',
- subfieldcode char(1) NOT NULL default '',
- subfieldorder tinyint(4) NOT NULL default '1',
- subfieldvalue varchar(255) default NULL,
- PRIMARY KEY (subfieldid),
- KEY authid (authid),
- KEY tag (tag),
- KEY subfieldcode (subfieldcode),
- KEY subfieldvalue (subfieldvalue)
- )",
- auth_word => "(
- authid bigint(20) NOT NULL default '0',
- tagsubfield char(4) NOT NULL default '',
- tagorder tinyint(4) NOT NULL default '1',
- subfieldorder tinyint(4) NOT NULL default '1',
- word varchar(255) NOT NULL default '',
- sndx_word varchar(255) NOT NULL default '',
- KEY authid (authid),
- KEY marc_search (tagsubfield,word),
- KEY word (word),
- KEY sndx_word (sndx_word)
- )",
- suggestions => "(
- suggestionid int(8) NOT NULL auto_increment,
- suggestedby int(11) NOT NULL default '0',
- managedby int(11) default NULL ,
- STATUS varchar(10) NOT NULL default '',
- note text,
- author varchar(80) default NULL ,
- title varchar(80) default NULL ,
- copyrightdate smallint(6) default NULL ,
- publishercode varchar(255) default NULL ,
- date timestamp(8) NOT NULL ,
- volumedesc varchar(255) default NULL ,
- publicationyear smallint(6) default '0',
- place varchar(255) default NULL ,
- isbn varchar(10) default NULL ,
- mailoverseeing smallint(1) default '0',
- biblionumber int(11) default NULL ,
- PRIMARY KEY (suggestionid) ,
- KEY suggestedby(suggestedby) ,
- KEY managedby(managedby)
- )",
- aqbasket => "(basketno int(11) NOT NULL auto_increment,
- creationdate date,
- closedate date,
- booksellerid varchar(10),
- authorisedby varchar(10),
- booksellerinvoicenumber text,
- PRIMARY KEY (basketno)
- )",
- serial => "(serialid int(11) NOT NULL auto_increment,
- biblionumber varchar(100) NOT NULL default '',
- subscriptionid varchar(100) NOT NULL default '',
- serialseq varchar(100) NOT NULL default '',
- status tinyint(4) NOT NULL default '0',
- planneddate date NOT NULL default '0000-00-00',
- PRIMARY KEY (serialid)
- )",
- subscription => "(biblionumber int(11) NOT NULL default '0',
- subscriptionid int(11) NOT NULL auto_increment,
- librarian varchar(100) default '',
- startdate date default '0000-00-00',
- aqbooksellerid int(11) default '0',
- cost int(11) default '0',
- aqbudgetid int(11) default '0',
- weeklength tinyint(4) default '0',
- monthlength tinyint(4) default '0',
- numberlength tinyint(4) default '0',
- periodicity tinyint(4) default '0',
- dow varchar(100) default '',
- numberingmethod varchar(100) default '',
- notes text,
- status varchar(100) NOT NULL default '',
- add1 int(11) default 0,
- every1 int(11) default 0,
- whenmorethan1 int(11) default 0,
- setto1 int(11),
- lastvalue1 int(11),
- add2 int(11) default 0,
- every2 int(11) default 0,
- whenmorethan2 int(11) default 0,
- setto2 int(11),
- lastvalue2 int(11),
- add3 int(11) default 0,
- every3 int(11) default 0,
- innerloop1 int(11) default 0,
- innerloop2 int(11) default 0,
- innerloop3 int(11) default 0,
- whenmorethan3 int(11) default 0,
- setto3 int(11),
- lastvalue3 int(11),
- PRIMARY KEY (subscriptionid)
- )",
- subscriptionhistory => "(biblionumber int(11) NOT NULL default '0',
- subscriptionid int(11) NOT NULL default '0',
- histstartdate date NOT NULL default '0000-00-00',
- enddate date default '0000-00-00',
- missinglist longtext NOT NULL,
- recievedlist longtext NOT NULL,
- opacnote varchar(150) NOT NULL default '',
- librariannote varchar(150) NOT NULL default '',
- PRIMARY KEY (subscriptionid),
- KEY biblionumber (biblionumber)
- )",
-);
-
-my %requirefields = (
- biblio => { 'abstract' => 'text' },
- deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
- deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
- biblioitems => {
- 'lccn' => 'char(25)',
- 'url' => 'varchar(255)',
- 'marc' => 'text'
- },
- deletedbiblioitems => {
- 'lccn' => 'char(25)',
- 'url' => 'varchar(255)',
- 'marc' => 'text'
- },
- branchtransfers => { 'datearrived' => 'datetime' },
- statistics => { 'borrowernumber' => 'int(11)' },
- aqbooksellers => {
- 'invoicedisc' => 'float(6,4)',
- 'nocalc' => 'int(11)'
- },
- borrowers => {
- 'userid' => 'char(30)',
- 'password' => 'char(30)',
- 'flags' => 'int(11)',
- 'textmessaging' => 'varchar(30)',
- 'zipcode' => 'varchar(25)',
- 'homezipcode' => 'varchar(25)',
- 'sort1' => 'char(80)',
- 'sort2' => 'char(80)',
- },
- aqorders => { 'budgetdate' => 'date',
- 'sort1' => 'char(80)',
- 'sort2' => 'char(80)', },
- aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
- items => {'paidfor' => 'text', 'location' => 'char(80)'},
-
- #added so that reference items are not available for reserves...
- itemtypes => { 'notforloan' => 'smallint(6)' },
- systempreferences => { 'explanation' => 'char(80)',
- 'type' => 'char(20)',
- 'options' => 'text' },
- z3950servers => { 'syntax' => 'char(80)' },
- marc_tag_structure =>{
- 'frameworkcode' => 'char(4) not NULL default \'\''},
- marc_subfield_structure =>{'seealso' => 'char(255)',
- 'frameworkcode' => 'char(4) not NULL default \'\'',
- 'hidden' => 'tinyint(1)',
- 'isurl' => 'tinyint(1)',
- 'link' => 'char(80)',
- },
- bookshelf => {'owner' => 'char(80)',
- 'category' => 'char(1)',
- },
- marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
-);
-
-my %dropable_table = (
- classification => 'classification',
- multipart => 'multipart',
- multivolume => 'multivolume',
- newitems => 'newitems',
- procedures => 'procedures',
- publisher => 'publisher',
- searchstats => 'searchstats',
- serialissues => 'serialissues',
-);
-
-my %uselessfields = (
- aqorders => "requisitionedby,authorisedby,booksellerid,
- deliverydays,followupdays,
- numberfollowupsallowed,numberfollowupssent,
- dateprinted,sourced,quantityreceiveddamaged,
- subscriptionfrom,subscriptionto
- "
- );
-# the other hash contains other actions that can't be done elsewhere. they are done
-# either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
-
-# The tabledata hash contains data that should be in the tables.
-# The uniquefieldrequired hash entry is used to determine which (if any) fields
-# must not exist in the table for this row to be inserted. If the
-# uniquefieldrequired entry is already in the table, the existing data is not
-# modified, unless the forceupdate hash entry is also set. Fields in the
-# anonymous "forceupdate" hash will be forced to be updated to the default
-# values given in the %tabledata hash.
-
-my %tabledata = (
- userflags => [
- {
- uniquefieldrequired => 'bit',
- bit => 0,
- flag => 'superlibrarian',
- flagdesc => 'Access to all librarian functions',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 1,
- flag => 'circulate',
- flagdesc => 'Circulate books',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 2,
- flag => 'catalogue',
- flagdesc => 'View Catalogue (Librarian Interface)',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 3,
- flag => 'parameters',
- flagdesc => 'Set Koha system paramters',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 4,
- flag => 'borrowers',
- flagdesc => 'Add or modify borrowers',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 5,
- flag => 'permissions',
- flagdesc => 'Set user permissions',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 6,
- flag => 'reserveforothers',
- flagdesc => 'Reserve books for patrons',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 7,
- flag => 'borrow',
- flagdesc => 'Borrow books',
- defaulton => 1
- },
- {
- uniquefieldrequired => 'bit',
- bit => 8,
- flag => 'reserveforself',
- flagdesc => 'Reserve books for self',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 9,
- flag => 'editcatalogue',
- flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
- defaulton => 0
- },
- {
- uniquefieldrequired => 'bit',
- bit => 10,
- flag => 'updatecharges',
- flagdesc => 'Update borrower charges',
- defaulton => 0
- },
- ],
- systempreferences => [
- {
- uniquefieldrequired => 'variable',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- variable => 'LibraryName',
- value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
- explanation => 'Library name as shown on main opac page',
- type => ''
-
- },
- {
- uniquefieldrequired => 'variable',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- variable => 'autoMemberNum',
- value => '1',
- explanation => 'Member number is auto-calculated',
- type => 'YesNo'
-
- },
- {
- uniquefieldrequired => 'variable',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1 },
- variable => 'acquisitions',
- value => 'normal',
- explanation =>
-'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
- type => 'Choice',
- options => 'simple|normal'
- },
- {
- uniquefieldrequired => 'variable',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1 },
- variable => 'dateformat',
- value => 'metric',
- explanation =>
- 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
- type => 'Choice',
- options => 'metric|us|iso'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'template',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'default',
- explanation => 'Preference order for intranet interface templates',
- type => 'Themes'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'autoBarcode',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'yes',
- explanation => 'Barcode is auto-calculated',
- type => 'YesNo'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'insecure',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'no',
- explanation =>
-'If YES, no auth at all is needed. Be careful if you set this to yes!',
- type => 'YesNo'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'authoritysep',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1 },
- value => '--',
- explanation =>
- 'the separator used in authority/thesaurus. Usually --',
- type => 'free',
- options => '10'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'opaclanguages',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'en',
- explanation => 'Set the preferred order for translations. The top language will be tried first.',
- type => 'Languages'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'opacthemes',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'css',
- explanation => 'Set the preferred order for themes. The top theme will be tried first.',
- type => 'Themes'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'timeout',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => '1200',
- explanation => 'Inactivity timeout for cookies authentication (in seconds)',
- type => 'Integer'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'marc',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'yes',
- explanation => 'Turn on MARC support',
- type => 'YesNo'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'marcflavour',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1},
- value => 'MARC21',
- explanation =>
- 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
- type => 'Choice',
- options => 'MARC21|UNIMARC'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'checkdigit',
- value => 'none',
- forceupdate => { 'explanation' => 1,
- 'type' => 1,
- 'options' => 1},
- explanation => 'Validity checks on membership number: none or "Katipo" style checks',
- type => 'Choice',
- options => 'none|katipo'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'maxoutstanding',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => '5',
- explanation =>
- 'maximum amount withstanding to be able make reserves ',
- type => 'Integer'
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'maxreserves',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => '5',
- explanation =>
- 'maximum number of reserves a member can make',
- type => 'Integer'
-
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'noissuescharge',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => '5',
- explanation =>
- 'maximum amount withstanding to be able to check out an item',
- type => 'Integer'
-
- },
- {
- uniquefieldrequired => 'variable',
- variable => 'KohaAdminEmailAddress',
- forceupdate => { 'explanation' => 1,
- 'type' => 1 },
- value => 'your.mail@here',
- explanation => 'the email address where borrowers modifs are sent',
- type => 'free'
- },
+$|=1; # flushes output
+
+my $DBversion = "3.00.00.000";
+# if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
+if (C4::Context->preference("Version") < TransformToNum($DBversion) ) {
+ # Tables to add if they don't exist
+ my %requiretables = (
+ action_logs => "(
+ `timestamp` TIMESTAMP NOT NULL ,
+ `user` INT( 11 ) NOT NULL default '0' ,
+ `module` TEXT default '',
+ `action` TEXT default '' ,
+ `object` INT(11) NULL ,
+ `info` TEXT default '' ,
+ PRIMARY KEY ( `timestamp` , `user` )
+ )",
+ letter => "(
+ module varchar(20) NOT NULL default '',
+ code varchar(20) NOT NULL default '',
+ name varchar(100) NOT NULL default '',
+ title varchar(200) NOT NULL default '',
+ content text,
+ PRIMARY KEY (module,code)
+ )",
+ alert =>"(
+ alertid int(11) NOT NULL auto_increment,
+ borrowernumber int(11) NOT NULL default '0',
+ type varchar(10) NOT NULL default '',
+ externalid varchar(20) NOT NULL default '',
+ PRIMARY KEY (alertid),
+ KEY borrowernumber (borrowernumber),
+ KEY type (type,externalid)
+ )",
+ opac_news => "(
+ `idnew` int(10) unsigned NOT NULL auto_increment,
+ `title` varchar(250) NOT NULL default '',
+ `new` text NOT NULL,
+ `lang` varchar(4) NOT NULL default '',
+ `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
+ PRIMARY KEY (`idnew`)
+ )",
+ repeatable_holidays => "(
+ `id` int(11) NOT NULL auto_increment,
+ `branchcode` varchar(4) NOT NULL default '',
+ `weekday` smallint(6) default NULL,
+ `day` smallint(6) default NULL,
+ `month` smallint(6) default NULL,
+ `title` varchar(50) NOT NULL default '',
+ `description` text NOT NULL,
+ PRIMARY KEY (`id`)
+ )",
+ special_holidays => "(
+ `id` int(11) NOT NULL auto_increment,
+ `branchcode` varchar(4) NOT NULL default '',
+ `day` smallint(6) NOT NULL default '0',
+ `month` smallint(6) NOT NULL default '0',
+ `year` smallint(6) NOT NULL default '0',
+ `isexception` smallint(1) NOT NULL default '1',
+ `title` varchar(50) NOT NULL default '',
+ `description` text NOT NULL,
+ PRIMARY KEY (`id`)
+ )",
+ overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
+ `categorycode` char(2) NOT NULL default '',
+ `delay1` int(4) default '0',
+ `letter1` varchar(20) default NULL,
+ `debarred1` char(1) default '0',
+ `delay2` int(4) default '0',
+ `debarred2` char(1) default '0',
+ `letter2` varchar(20) default NULL,
+ `delay3` int(4) default '0',
+ `letter3` varchar(20) default NULL,
+ `debarred3` int(1) default '0',
+ PRIMARY KEY (`branchcode`,`categorycode`)
+ )",
+ cities => "(`cityid` int auto_increment,
+ `city_name` char(100) NOT NULL,
+ `city_zipcode` char(20),
+ PRIMARY KEY (`cityid`)
+ )",
+ roadtype => "(`roadtypeid` int auto_increment,
+ `road_type` char(100) NOT NULL,
+ PRIMARY KEY (`roadtypeid`)
+ )",
+
+ labels => "(
+ labelid int(11) NOT NULL auto_increment,
+ itemnumber varchar(100) NOT NULL default '',
+ timestamp timestamp(14) NOT NULL,
+ PRIMARY KEY (labelid)
+ )",
+
+ labels_conf => "(
+ id int(4) NOT NULL auto_increment,
+ barcodetype char(100) default '',
+ title tinyint(1) default '0',
+ isbn tinyint(1) default '0',
+ itemtype tinyint(1) default '0',
+ barcode tinyint(1) default '0',
+ dewey tinyint(1) default '0',
+ class tinyint(1) default '0',
+ author tinyint(1) default '0',
+ papertype char(100) default '',
+ startrow int(2) default NULL,
+ PRIMARY KEY (id)
+ )",
+ reviews => "(
+ reviewid integer NOT NULL auto_increment,
+ borrowernumber integer,
+ biblionumber integer,
+ review text,
+ approved tinyint,
+ datereviewed datetime,
+ PRIMARY KEY (reviewid)
+ )",
+ subscriptionroutinglist=>"(
+ routingid integer NOT NULL auto_increment,
+ borrowernumber integer,
+ ranking integer,
+ subscriptionid integer,
+ PRIMARY KEY (routingid)
+ )",
+
+ notifys => "(
+ notify_id int(11) NOT NULL default '0',
+ `borrowernumber` int(11) NOT NULL default '0',
+ `itemnumber` int(11) NOT NULL default '0',
+ `notify_date` date NOT NULL default '0000-00-00',
+ `notify_send_date` date default NULL,
+ `notify_level` int(1) NOT NULL default '0',
+ `method` varchar(20) NOT NULL default ''
+ )",
+
+ charges => "(
+ `charge_id` varchar(5) NOT NULL default '',
+ `description` text NOT NULL,
+ `amount` decimal(28,6) NOT NULL default '0.000000',
+ `min` int(4) NOT NULL default '0',
+ `max` int(4) NOT NULL default '0',
+ `level` int(1) NOT NULL default '0',
+ PRIMARY KEY (`charge_id`)
+ )",
+ tags => "(
+ `entry` varchar(255) NOT NULL default '',
+ `weight` bigint(20) NOT NULL default '0',
+ PRIMARY KEY (`entry`)
+ )
+ ",
+ zebraqueue => "(
+ `id` int NOT NULL auto_increment,
+ `biblio_auth_number` int NOT NULL,
+ `operation` char(20) NOT NULL,
+ `server` char(20) NOT NULL ,
+ PRIMARY KEY (`id`)
+ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
+
+ );
+
+ my %requirefields = (
+ subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date NOT NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(12) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''},
+ itemtypes => { 'imageurl' => 'char(200) NULL'},
+ aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
+ aqbudget => { 'branchcode' => 'varchar(4) NULL'},
+ auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
+ auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => 'VARCHAR(45) NOT NULL', 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'},
+ marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
+ serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
+ statistics => { 'associatedborrower' => 'integer'},
+ z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
+ "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
+ "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
+ },
+ issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
+
+ # tablename => { 'field' => 'fieldtype' },
+ );
+
+ # Enter here the table to delete.
+ my @TableToDelete = qw(
+ additionalauthors
+ bibliosubject
+ bibliosubtitle
+ bibliothesaurus
+ );
+
+ my %uselessfields = (
+ # tablename => "field1,field2",
+ borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
+ deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
+ );
+ # the other hash contains other actions that can't be done elsewhere. they are done
+ # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
+
+ # The tabledata hash contains data that should be in the tables.
+ # The uniquefieldrequired hash entry is used to determine which (if any) fields
+ # must not exist in the table for this row to be inserted. If the
+ # uniquefieldrequired entry is already in the table, the existing data is not
+ # modified, unless the forceupdate hash entry is also set. Fields in the
+ # anonymous "forceupdate" hash will be forced to be updated to the default
+ # values given in the %tabledata hash.
+
+ my %tabledata = (
+ # tablename => [
+ # { uniquefielrequired => 'fieldname', # the primary key in the table
+ # fieldname => fieldvalue,
+ # fieldname2 => fieldvalue2,
+ # },
+ # ],
+ systempreferences => [
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'useDaysMode',
+ value => 'Calendar',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
+ type => 'Choice',
+ options => 'Calendar|Days'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'DebugLevel',
+ value => '0',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
+ type => 'Choice',
+ options => '0|1|2'
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'BorrowersTitles',
+ value => 'Mr|Mrs|Miss|Ms',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'List all Titles for borrowers',
+ type => 'free',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'BorrowerMandatoryField',
+ value => 'cardnumber|surname|address',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'List all mandatory fields for borrowers',
+ type => 'free',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'borrowerRelationship',
+ value => 'father|mother,grand-mother',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
+ type => 'free',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'ReservesMaxPickUpDelay',
+ value => '10',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Maximum delay to pick up a reserved document',
+ type => 'free',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'TransfersMaxDaysWarning',
+ value => '3',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Max delay before considering the transfer has potentialy a problem',
+ type => 'free',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'memberofinstitution',
+ value => '0',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Are your patrons members of institutions',
+ type => 'YesNo',
+ },