# - Would also be a good idea to offer to do a backup at this time...
# NOTE: If you do something more than once in here, make it table driven.
-
use strict;
# CPAN modules
use DBI;
-
+use Getopt::Long;
# Koha modules
-use C4::Context("/etc/koha.conf.tmp");
- # FIXME - /etc/koha.conf might not exist, so shouldn't use
- # C4::Context.
+use C4::Context;
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.
-my $debug=0;
+my $debug = 0;
my (
- $sth, $sti,
- $query,
- %existingtables, # tables already in database
- %types,
- $table,
- $column,
- $type, $null, $key, $default, $extra,
- $prefitem, # preference item in systempreferences table
+ $sth, $sti,
+ $query,
+ %existingtables, # tables already in database
+ %types,
+ $table,
+ $column,
+ $type, $null, $key, $default, $extra,
+ $prefitem, # preference item in systempreferences table
);
+my $silent;
+GetOptions(
+ 's' =>\$silent
+ );
my $dbh = C4::Context->dbh;
+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 '',
- 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 =>"( file varchar(80) NOT NULL default '',
- isbn varchar(10) NOT NULL default '',
- marc text NOT NULL,
- PRIMARY KEY (isbn)
- )",
- authorised_values => "(id int(11) NOT NULL auto_increment,
- category char(10) NOT NULL default '',
- authorised_value char(80) NOT NULL default '',
- PRIMARY KEY (id),
- KEY name (category)
- )",
- );
+my %requiretables = (
+ categorytable => "(categorycode char(5) NOT NULL default '',
+ description text default '',
+ itemtypecodes text default '',
+ PRIMARY KEY (categorycode)
+ )",
+ subcategorytable => "(subcategorycode char(5) NOT NULL default '',
+ description text default '',
+ itemtypecodes text default '',
+ PRIMARY KEY (subcategorycode)
+ )",
+ mediatypetable => "(mediatypecode char(5) NOT NULL default '',
+ description text default '',
+ itemtypecodes text default '',
+ PRIMARY KEY (mediatypecode)
+ )",
+ action_logs => "(
+ `timestamp` TIMESTAMP NOT NULL ,
+ `user` INT( 11 ) NOT NULL ,
+ `module` TEXT default '',
+ `action` TEXT default '' ,
+ `object` INT(11) default '' ,
+ `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)
+ )",
+);
-my %requirefields=(
- biblio=>{ 'abstract' => 'text' },
- deletedbiblio=>{ 'abstract' => '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)'},
- aqorders=>{'budgetdate' => 'date'},
- #added so that reference items are not available for reserves...
- itemtypes=>{'notforloan' => 'smallint(6)'},
- systempreferences =>{'explanation' => 'char(80)'},
+my %requirefields = (
+ subscription => { 'letter' => 'char(20) NULL'},
+# tablename => { 'field' => 'fieldtype' },
);
-my %dropable_table=(
- classification =>'classification',
- multipart =>'multipart',
- multivolume =>'multivolume',
- newitems =>'newitems',
- procedures =>'procedures',
- publisher =>'publisher',
- searchstats =>'searchstats',
- serialissues =>'serialissues',
- );
+my %dropable_table = (
+# tablename => 'tablename',
+);
+my %uselessfields = (
+# tablename => "field1,field2",
+ );
+# 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.
-
-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 },
- ],
+# 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 => 'autoMemberNum', value => '1', explanation => '1 or else. If 1, Barcode is auto-calculated' },
- { uniquefieldrequired => 'variable', variable => 'acquisitions', value => 'simple', explanation => 'normal or simple : will use acquisition system found in directory acqui.simple or acquisition' },
- { uniquefieldrequired => 'variable', variable => 'dateformat', value => 'metric', explanation => 'metric or us' },
- { uniquefieldrequired => 'variable', variable => 'template', value => "default', explanation => 'template default name' },
- { uniquefieldrequired => 'variable', variable => 'autoBarcode', value => '1', explanation => '1 or else. If 1, Barcode is auto-calculated' },
- { uniquefieldrequired => 'variable', variable => 'insecure', value => 'NO', explanation => 'if YES, no auth at all is needed. Be careful if you set this to yes !' },
- { uniquefieldrequired => 'variable', variable => 'authoritysep', value => '--', explanation => 'the separator used in authority/thesaurus. Usually --' },
- { uniquefieldrequired => 'variable', variable => 'opaclanguages', value => 'en', explanation => 'languages' },
- { uniquefieldrequired => 'variable', variable => 'opacthemes', value => 'default', explanation => 'theme' },
- { uniquefieldrequired => 'variable', variable => 'timeout', value => '12000000', explanation => 'login timeout' },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'Activate_Log',
+ value => 'On',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Turn Log Actions on DB On an Off',
+ type => 'YesNo',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'IndependantBranches',
+ value => 0,
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Turn Branch independancy management On an Off',
+ type => 'YesNo',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'ReturnBeforeExpiry',
+ value => 'Off',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
+ type => 'YesNo',
+ },
],
);
-
-my %fielddefinitions=(
-printers => [
- { field => 'printername', type => 'char(40)', null => '', key => 'PRI', default => '' },
- ],
-aqbookfund => [
- { field => 'bookfundid', type => 'char(5)', null => '', key => 'PRI', default => '' },
- ],
-z3950servers => [
- { field => 'id', type => 'int', null => '', key => 'PRI', default => '', extra => 'auto_increment' },
- ],
+my %fielddefinitions = (
+# fieldname => [
+# { field => 'fieldname',
+# type => 'fieldtype',
+# null => '',
+# key => '',
+# default => ''
+# },
+# ],
);
-
-
#-------------------
# Initialize
# Start checking
# Get version of MySQL database engine.
-my $mysqlversion=`mysqld --version`;
-$mysqlversion=~/Ver (\S*) /;
-$mysqlversion=$1;
-if ($mysqlversion ge '3.23') {
- print "Could convert to MyISAM database tables...\n";
+my $mysqlversion = `mysqld --version`;
+$mysqlversion =~ /Ver (\S*) /;
+$mysqlversion = $1;
+if ( $mysqlversion ge '3.23' ) {
+ print "Could convert to MyISAM database tables...\n" unless $silent;
}
#---------------------------------
# Tables
# Collect all tables into a list
-$sth=$dbh->prepare("show tables");
+$sth = $dbh->prepare("show tables");
$sth->execute;
-while (my ($table) = $sth->fetchrow) {
- $existingtables{$table}=1;
+while ( my ($table) = $sth->fetchrow ) {
+ $existingtables{$table} = 1;
}
+
# Now add any missing tables
foreach $table ( keys %requiretables ) {
- print "Checking $table table...\n" if $debug;
- unless ($existingtables{$table} ) {
- print "Adding $table table...\n";
- my $sth=$dbh->prepare(
- "create table $table $requiretables{$table}" );
- $sth->execute;
- if ($sth->err) {
- print "Error : $sth->errstr \n";
- $sth->finish;
- } # if error
- } # unless exists
-} # foreach
+ unless ( $existingtables{$table} ) {
+ print "Adding $table table...\n" unless $silent;
+ my $sth = $dbh->prepare("create table $table $requiretables{$table}");
+ $sth->execute;
+ if ( $sth->err ) {
+ print "Error : $sth->errstr \n";
+ $sth->finish;
+ } # if error
+ } # unless exists
+} # foreach
# now drop useless tables
-foreach $table ( keys %dropable_table) {
- print "Dropping unused tables...\n" if $debug;
- if ($existingtables{$table} ) {
+foreach $table ( keys %dropable_table ) {
+ if ( $existingtables{$table} ) {
+ print "Dropping unused table $table\n" if $debug and not $silent;
$dbh->do("drop table $table");
- if ($dbh->err) {
- print "Error : $dbh->errstr \n";
+ if ( $dbh->err ) {
+ print "Error : $dbh->errstr \n";
}
}
}
-unless ($existingtables{'z3950servers'}) {
- print "Adding z3950servers table...\n";
- my $sti=$dbh->prepare("create table z3950servers (
- host char(255),
- port int,
- db char(255),
- userid char(255),
- password char(255),
- name text,
- id int,
- checked smallint,
- rank int)");
- $sti->execute;
- $sti=$dbh->prepare("insert into z3950servers
- values ('z3950.loc.gov',
- 7090,
- 'voyager',
- '', '',
- 'Library of Congress',
- 1, 1, 1)");
- $sti->execute;
-}
#---------------------------------
# Columns
foreach $table ( keys %requirefields ) {
- print "Check table $table\n" if $debug;
- $sth=$dbh->prepare("show columns from $table");
- $sth->execute();
- undef %types;
- while ( ($column, $type, $null, $key, $default, $extra)
- = $sth->fetchrow) {
- $types{$column}=$type;
- } # while
- foreach $column ( keys %{ $requirefields{$table} } ) {
- print " Check column $column\n" if $debug;
- if ( ! $types{$column} ) {
- # column doesn't exist
- print "Adding $column field to $table table...\n";
- $query="alter table $table
- add column $column " . $requirefields{$table}->{$column} ;
- print "Execute: $query\n" if $debug;
- my $sti=$dbh->prepare($query);
- $sti->execute;
- if ($sti->err) {
- print "**Error : $sti->errstr \n";
- $sti->finish;
- } # if error
- } # if column
- } # foreach column
-} # foreach table
-
-foreach $table ( keys %fielddefinitions ) {
- print "Check table $table\n" if $debug;
- $sth=$dbh->prepare("show columns from $table");
+ print "Check table $table\n" if $debug and not $silent;
+ $sth = $dbh->prepare("show columns from $table");
$sth->execute();
- my $definitions;
- while ( ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $definitions->{$column}->{type}=$type;
- $definitions->{$column}->{null}=$null;
- $definitions->{$column}->{key}=$key;
- $definitions->{$column}->{default}=$default;
- $definitions->{$column}->{extra}=$extra;
- } # while
- my $fieldrow=$fielddefinitions{$table};
- foreach my $row ( @$fieldrow ) {
- my $field = $row->{field};
- my $type = $row->{type};
- my $null = $row->{null};
- my $key = $row->{key};
- my $default = $row->{default};
- my $extra = $row->{extra};
- my $def=$definitions->{$field};
- unless ($type eq $def->{type} && $null eq $def->{null} && $key eq $def->{key} && $default eq $def->{default} && $extra eq $def->{extra}) {
- if ($null eq '') {
- $null='NOT NULL';
- }
- if ($key eq 'PRI') {
- $key ='PRIMARY KEY';
- }
- unless ($extra eq 'auto_increment') {
- $extra='';
- }
- my $sth=$dbh->prepare("alter table $table change $field $field $type $null $key $extra default ?");
- $sth->execute($default);
- print " Alter $field in $table\n";
- }
- }
-}
-
-# Get list of columns from items table
-my %itemtypes;
+ undef %types;
+ while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $types{$column} = $type;
+ } # while
+ foreach $column ( keys %{ $requirefields{$table} } ) {
+ print " Check column $column [$types{$column}]\n" if $debug and not $silent;
+ if ( !$types{$column} ) {
+
+ # column doesn't exist
+ print "Adding $column field to $table table...\n" unless $silent;
+ $query = "alter table $table
+ add column $column " . $requirefields{$table}->{$column};
+ print "Execute: $query\n" if $debug;
+ my $sti = $dbh->prepare($query);
+ $sti->execute;
+ if ( $sti->err ) {
+ print "**Error : $sti->errstr \n";
+ $sti->finish;
+ } # if error
+ } # if column
+ } # foreach column
+} # foreach table
-$sth=$dbh->prepare("show columns from items");
-$sth->execute;
-while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $itemtypes{$column}=$type;
-}
-
-unless ($itemtypes{'barcode'} eq 'varchar(20)') {
- $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
- my $oldlength=$1;
- if ($oldlength<20) {
- print "Setting maximum barcode length to 20 (was $oldlength).\n";
- my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
- $sti->execute;
+foreach $table ( keys %fielddefinitions ) {
+ print "Check table $table\n" if $debug;
+ $sth = $dbh->prepare("show columns from $table");
+ $sth->execute();
+ my $definitions;
+ while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $definitions->{$column}->{type} = $type;
+ $definitions->{$column}->{null} = $null;
+ $definitions->{$column}->{key} = $key;
+ $definitions->{$column}->{default} = $default;
+ $definitions->{$column}->{extra} = $extra;
+ } # while
+ my $fieldrow = $fielddefinitions{$table};
+ foreach my $row (@$fieldrow) {
+ my $field = $row->{field};
+ my $type = $row->{type};
+ my $null = $row->{null};
+ my $key = $row->{key};
+ my $default = $row->{default};
+ $default="''" unless $default;
+ my $extra = $row->{extra};
+ my $def = $definitions->{$field};
+ unless ( $type eq $def->{type}
+ && $null eq $def->{null}
+ && $key eq $def->{key}
+ && $default eq $def->{default}
+ && $extra eq $def->{extra} )
+ {
+
+ if ( $null eq '' ) {
+ $null = 'NOT NULL';
+ }
+ if ( $key eq 'PRI' ) {
+ $key = 'PRIMARY KEY';
+ }
+ unless ( $extra eq 'auto_increment' ) {
+ $extra = '';
+ }
+ # if it's a new column use "add", if it's an old one, use "change".
+ my $action;
+ if ($definitions->{$field}->{type}) {
+ $action="change $field"
+ } else {
+ $action="add";
+ }
+# if it's a primary key, drop the previous pk, before altering the table
+ my $sth;
+ if ($key ne 'PRIMARY KEY') {
+ $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
+ } else {
+ $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
+ }
+ $sth->execute($default);
+ print " Alter $field in $table\n" unless $silent;
+ }
}
}
-# extending the timestamp in branchtransfers...
-my %branchtransfers;
-
-$sth=$dbh->prepare("show columns from branchtransfers");
-$sth->execute;
-while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $branchtransfers{$column}=$type;
-}
-
-unless ($branchtransfers{'datesent'} eq 'datetime') {
- print "Setting type of datesent in branchtransfers to datetime.\n";
- my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
- $sti->execute;
-}
-
-unless ($branchtransfers{'datearrived'} eq 'datetime') {
- print "Setting type of datearrived in branchtransfers to datetime.\n";
- my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
- $sti->execute;
-}
-
-# changing the branchcategories table around...
-my %branchcategories;
-
-$sth=$dbh->prepare("show columns from branchcategories");
-$sth->execute;
-while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
- $branchcategories{$column}=$type;
-}
-
-unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
- print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
- my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
- $sti->execute;
- $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
- $sti->execute;
-}
-
-unless ($branchcategories{'categoryname'} eq 'text') {
- print "Changing branchcode in branchcategories to categoryname text.\n";
- my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
- $sth->execute;
-}
-
-unless ($branchcategories{'codedescription'} eq 'text') {
- print "Replacing branchholding in branchcategories with codedescription text.\n";
- my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
- $sth->execute;
-}
-
# Populate tables with required data
-
-foreach my $table (keys %tabledata) {
- print "Checking for data required in table $table...\n";
- my $tablerows=$tabledata{$table};
+foreach my $table ( keys %tabledata ) {
+ print "Checking for data required in table $table...\n" unless $silent;
+ my $tablerows = $tabledata{$table};
foreach my $row (@$tablerows) {
- my $uniquefieldrequired=$row->{uniquefieldrequired};
- my $uniquevalue=$row->{$uniquefieldrequired};
- my $sth=$dbh->prepare("select $uniquefieldrequired from $table where $uniquefieldrequired=?");
- $sth->execute($uniquevalue);
- unless ($sth->rows) {
- print "Adding row to $table: ";
- my @values;
- my $fieldlist;
- my $placeholders;
- foreach my $field (keys %$row) {
- (next) if ($field eq 'uniquefieldrequired');
- my $value=$row->{$field};
- push @values, $value;
- print " $field => $value";
- $fieldlist.="$field,";
- $placeholders.="?,";
+ my $uniquefieldrequired = $row->{uniquefieldrequired};
+ my $uniquevalue = $row->{$uniquefieldrequired};
+ my $forceupdate = $row->{forceupdate};
+ my $sth =
+ $dbh->prepare(
+"select $uniquefieldrequired from $table where $uniquefieldrequired=?"
+ );
+ $sth->execute($uniquevalue);
+ if ($sth->rows) {
+ foreach my $field (keys %$forceupdate) {
+ if ($forceupdate->{$field}) {
+ my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
+ $sth->execute($row->{$field}, $uniquevalue);
+ }
}
- print "\n";
- $fieldlist=~s/,$//;
- $placeholders=~s/,$//;
- my $sth=$dbh->prepare("insert into $table ($fieldlist) values ($placeholders)");
- $sth->execute(@values);
- }
+ } else {
+ print "Adding row to $table: " unless $silent;
+ my @values;
+ my $fieldlist;
+ my $placeholders;
+ foreach my $field ( keys %$row ) {
+ next if $field eq 'uniquefieldrequired';
+ next if $field eq 'forceupdate';
+ my $value = $row->{$field};
+ push @values, $value;
+ print " $field => $value" unless $silent;
+ $fieldlist .= "$field,";
+ $placeholders .= "?,";
+ }
+ print "\n" unless $silent;
+ $fieldlist =~ s/,$//;
+ $placeholders =~ s/,$//;
+ my $sth =
+ $dbh->prepare(
+ "insert into $table ($fieldlist) values ($placeholders)");
+ $sth->execute(@values);
+ }
}
}
+# at last, remove useless fields
+foreach $table ( keys %uselessfields ) {
+ my @fields = split /,/,$uselessfields{$table};
+ my $fields;
+ my $exists;
+ foreach my $fieldtodrop (@fields) {
+ $fieldtodrop =~ s/\t//g;
+ $fieldtodrop =~ s/\n//g;
+ $exists =0;
+ $sth = $dbh->prepare("show columns from $table");
+ $sth->execute;
+ while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $exists =1 if ($column eq $fieldtodrop);
+ }
+ if ($exists) {
+ print "deleting $fieldtodrop field in $table...\n" unless $silent;
+ my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
+ $sth->execute;
+ }
+ }
+} # foreach
+
+
$sth->finish;
exit;
# $Log$
-# Revision 1.29 2003/01/06 11:14:11 tipaul
-# last bugfixes before 1.3.3 : systempref table correctly filled
-#
-# Revision 1.28 2002/12/10 13:27:47 tipaul
-# bugfixes (davide mails in koha-dev)
-#
-# Revision 1.27 2002/11/26 15:04:54 tipaul
-# road to 1.3.2. Updating db structure during installation
-#
-# Revision 1.26 2002/11/12 17:42:40 tonnesen
-# Merged some features over from rel-1-2, including primary key checking.
-#
-# Revision 1.25 2002/11/12 16:44:38 tipaul
-# road to 1.3.2 :
-# * many bugfixes
-# * adding value_builder : you can map a subfield in the marc_subfield_structure to a sub stored in "value_builder" directory. In this directory you can create screen used to build values with any method. In this commit is a 1st draft of the builder for 100$a unimarc french subfield, which is composed of 35 digits, with 12 differents values (only the 4th first are provided for instance)
-#
-# Revision 1.24 2002/10/30 14:00:23 arensb
-# (bug fix): Fixed typo.
-#
-# Revision 1.23 2002/10/25 10:55:46 tipaul
-# Road to 1.3.2
-# * bugfixes and improvements
-# * manage mandatory MARC subfields
-# * new table : authorised_values. this table contains categories and authorised values for the category. On MARC management, you can map a subfield to a authorised_values category. If you do this, the subfield can only be filled with a authorised_value of the selected category.
-# this submit contains everything needed :
-# * updatedatabase
-# * admin screens
-# * "links" management
-# * creation of a html-list if a subfield is mapped to an authorised value.
-#
-# Note this is different from authorities support, which will come soon.
-# The authorised_values is supposed to contains a "small" number of authorised values for a category (less than 50-100). If you enter more authorised values than this, it should be hard to find what you want in a BIG list...
-#
-# Revision 1.22 2002/10/15 10:08:19 tipaul
-# fixme corrected, re-indent and adding the marc_breeding table (see commit of marcimport.pl for more explanations about breeding)
+# Revision 1.119 2005/08/04 16:07:58 tipaul
+# Synch really broke this script...
#
-# Revision 1.21 2002/10/14 11:48:59 tipaul
-# bugfix
+# Revision 1.118 2005/08/04 16:02:55 tipaul
+# oops... error in synch between 2.2 and head
#
-# Revision 1.20 2002/10/10 04:49:41 arensb
-# Added some FIXME comments.
+# Revision 1.117 2005/08/04 14:24:39 tipaul
+# synch'ing 2.2 and head
#
-# Revision 1.19 2002/10/05 10:17:17 arensb
-# Merged with arensb-context branch: use C4::Context->dbh instead of
-# &C4Connect, and generally prefer C4::Context over C4::Database.
+# Revision 1.116 2005/08/04 08:55:54 tipaul
+# Letters / alert system, continuing...
#
-# Revision 1.18.2.2 2002/10/05 06:18:43 arensb
-# Added a whole mess of FIXME comments.
+# * adding a package Letters.pm, that manages Letters & alerts.
+# * adding feature : it's now possible to define a "letter" for any subscription created. If a letter is defined, users in OPAC can put an alert on the subscription. When an issue is marked "arrived", all users in the alert will recieve a mail (as defined in the "letter"). This last part (= send the mail) is not yet developped. (Should be done this week)
+# * adding feature : it's now possible to "put to an alert" in OPAC, for any serial subscription. The alert is stored in a new table, called alert. An alert can be put only if the librarian has activated them in subscription (and they activate it just by choosing a "letter" to sent to borrowers on new issues)
+# * adding feature : librarian can see in borrower detail which alerts they have put, and a user can see in opac-detail which alert they have put too.
#
-# Revision 1.18.2.1 2002/10/04 02:46:00 arensb
-# Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
-# C4Connect.
+# Note that the system should be generic enough to manage any type of alert.
+# I plan to extend it soon to virtual shelves : a borrower will be able to put an alert on a virtual shelf, to be warned when something is changed in the virtual shelf (mail being sent once a day by cron, or manually by the shelf owner. Anyway, a mail won't be sent on every change, users would be spammed by Koha ;-) )
#
-# Revision 1.18 2002/09/24 13:50:55 tipaul
-# long WAS the road to 1.3.0...
-# coming VERY SOON NOW...
-# modifying installer and buildrelease to update the DB
+# Revision 1.115 2005/08/02 16:15:34 tipaul
+# adding 2 fields to letter system :
+# * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
+# * title, that will be used as mail subject.
#
-# Revision 1.17 2002/09/24 12:57:35 tipaul
-# long WAS the road to 1.3.0...
-# coming VERY SOON NOW...
-# modifying installer and buildrelease to update the DB
+# Revision 1.114 2005/07/28 15:10:13 tipaul
+# Introducing new "Letters" system : Letters will be used everytime you want to sent something to someone (through mail or paper). For example, sending a mail for overdues use letter that you can put as parameters. Sending a mail to a borrower when a suggestion is validated uses a letter too.
+# the letter table contains 3 fields :
+# * code => the code of the letter
+# * name => the complete name of the letter
+# * content => the complete text. It's a TEXT field type, so has no limits.
#
-# Revision 1.16 2002/07/31 02:34:27 finlayt
+# My next goal now is to work on point 2-I "serial issue alert"
+# With this feature, in serials, a user can subscribe the "issue alert". For every issue arrived/missing, a mail is sent to all subscribers of this list. The mail warns the user that the issue is arrive or missing. Will be in head.
+# (see mail on koha-devel, 2005/04/07)
#
-# added "notforloan" field to the itemtypes table.
+# The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
#
-# Revision 1.15 2002/07/20 22:30:06 rangi
-# Making sure fix makes it into the main branch as well
-# Fix for bug 69
+# Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
#
-# Revision 1.14 2002/07/08 16:20:26 tonnesen
-# Added sessionqueries table and password/userid fields to borrowers table
+# Revision 1.113 2005/07/28 08:38:41 tipaul
+# For instance, the return date does not rely on the borrower expiration date. A systempref will be added in Koha, to modify return date calculation schema :
+# * ReturnBeforeExpiry = yes => return date can't be after expiry date
+# * ReturnBeforeExpiry = no => return date can be after expiry date
#
-# Revision 1.13 2002/07/04 18:05:36 tonnesen
-# bug fix
+# Revision 1.112 2005/07/26 08:19:47 hdl
+# Adding IndependantBranches System preference variable in order to manage Branch independancy.
#
-# Revision 1.12 2002/07/04 16:41:06 tonnesen
-# Merged changes from rel-1-2. Abstracted table structure changes by alan.
+# Revision 1.111 2005/07/25 15:35:38 tipaul
+# we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
+# So, the updatedatabase script can highly be cleaned (90% removed).
+# Let's play with the new Koha DB structure now ;-)
#
projects / koha.git / blobdiff