use C4::Context;
use MARC::Record;
-use MARC::File::XML;
-
+use MARC::File::XML ( BinaryEncoding => 'utf8' );
+
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.
`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)
+ )",
+
);
my %requirefields = (
subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'},
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 UNSIGNED ZEROFILL', 'kohafield' => 'VARCHAR(45) NOT NULL', 'linkid' => 'TINYINT(1) NOT NULL UNSIGNED', 'isurl' => 'TINYINT(1) UNSIGNED'},
+ statistics => { 'associatedborrower' => 'integer'},
# tablename => { 'field' => 'fieldtype' },
);
my %uselessfields = (
# tablename => "field1,field2",
+ borrowers => "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)
explanation => 'Enable/Disable password change in OPAC (disable it when using LDAP auth)',
type => 'YesNo',
},
+ {
+ 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 => 'borrowerMandatoryField',
+ value => 'zipcode|surname',
+ 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',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'ReadingHistory',
+ value => '0',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Allow reading record info retrievable from issues and oldissues tables',
+ type => 'YesNo',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'IssuingInProcess',
+ value => '0',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
+ type => 'YesNo',
+ },
+ {
+ uniquefieldrequired => 'variable',
+ variable => 'AutomaticItemReturn',
+ value => '1',
+ forceupdate => { 'explanation' => 1,
+ 'type' => 1},
+ explanation => 'This Variable allow or not to return automaticly to his homebranch',
+ type => 'YesNo',
+ },
],
);
extra => '',
},
],
+ borrowers => [
+ { field => 'B_email',
+ type => 'text',
+ null => 'NULL',
+ after => 'B_zipcode',
+ },
+ {
+ field => 'streetnumber', # street number (hidden if streettable table is empty)
+ type => 'char(10)',
+ null => 'NULL',
+ after => 'initials',
+ },
+ {
+ field => 'streettype', # street table, list builded from a system table
+ type => 'char(50)',
+ null => 'NULL',
+ after => 'streetnumber',
+ },
+ {
+ field => 'B_streetnumber', # street number (hidden if streettable table is empty)
+ type => 'char(10)',
+ null => 'NULL',
+ after => 'fax',
+ },
+ {
+ field => 'B_streettype', # street table, list builded from a system table
+ type => 'char(50)',
+ null => 'NULL',
+ after => 'B_streetnumber',
+ },
+ {
+ field => 'phonepro',
+ type => 'text',
+ null => 'NULL',
+ after => 'fax',
+ },
+ {
+ field => 'address2', # complement address
+ type => 'text',
+ null => 'NULL',
+ after => 'address',
+ },
+ {
+ field => 'emailpro',
+ type => 'text',
+ null => 'NULL',
+ after => 'fax',
+ },
+ {
+ field => 'contactfirstname', # contact's firstname
+ type => 'text',
+ null => 'NULL',
+ after => 'contactname',
+ },
+ {
+ field => 'contacttitle', # contact's title
+ type => 'text',
+ null => 'NULL',
+ after => 'contactfirstname',
+ },
+ ],
+
+ branches => [
+ {
+ field => 'branchip',
+ type => 'varchar(15)',
+ null => 'NULL',
+ key => '',
+ default => '',
+ extra => '',
+ },
+ {
+ field => 'branchprinter',
+ type => 'varchar(100)',
+ null => 'NULL',
+ key => '',
+ default => '',
+ extra => '',
+ },
+ ],
+ categories => [
+ {
+ field => 'category_type',
+ type => 'char(1)',
+ null => 'NOT NULL',
+ key => '',
+ default => 'A',
+ extra => '',
+ },
+ ],
+ reserves => [
+ {
+ field => 'waitingdate',
+ type => 'date',
+ null => 'NULL',
+ key => '',
+ default => '',
+ extra => '',
+ },
+ ],
);
my %indexes = (
# FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
);
+
+# column changes
+my %column_change = (
+ # table
+ borrowers => [
+ {
+ from => 'emailaddress',
+ to => 'email',
+ after => 'city',
+ },
+ {
+ from => 'streetaddress',
+ to => 'address',
+ after => 'initials',
+ },
+ {
+ from => 'faxnumber',
+ to => 'fax',
+ after => 'phone',
+ },
+ {
+ from => 'textmessaging',
+ to => 'opacnote',
+ after => 'userid',
+ },
+ {
+ from => 'altnotes',
+ to => 'contactnote',
+ after => 'opacnote',
+ },
+ {
+ from => 'physstreet',
+ to => 'B_address',
+ after => 'fax',
+ },
+ {
+ from => 'streetcity',
+ to => 'B_city',
+ after => 'B_address',
+ },
+ {
+ from => 'phoneday',
+ to => 'mobile',
+ after => 'phone',
+ },
+ {
+ from => 'zipcode',
+ to => 'zipcode',
+ after => 'city',
+ },
+ {
+ from => 'homezipcode',
+ to => 'B_zipcode',
+ after => 'B_city',
+ },
+ {
+ from => 'altphone',
+ to => 'B_phone',
+ after => 'B_zipcode',
+ },
+ {
+ from => 'expiry',
+ to => 'dateexpiry',
+ after => 'dateenrolled',
+ },
+ {
+ from => 'guarantor',
+ to => 'guarantorid',
+ after => 'contactname',
+ },
+ {
+ from => 'textmessaging',
+ to => 'opacnotes',
+ after => 'flags',
+ },
+ {
+ from => 'altnotes',
+ to => 'contactnotes',
+ after => 'opacnotes',
+ },
+ {
+ from => 'altrelationship',
+ to => 'relationship',
+ after => 'borrowernotes',
+ },
+ ],
+ );
+
+foreach my $table (keys %column_change) {
+ $sth = $dbh->prepare("show columns from $table");
+ $sth->execute();
+ undef %types;
+ while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
+ {
+ $types{$column}->{type} ="$type";
+ $types{$column}->{null} = "$null";
+ $types{$column}->{key} = "$key";
+ $types{$column}->{default} = "$default";
+ $types{$column}->{extra} = "$extra";
+ } # while
+ my $tablerows = $column_change{$table};
+ foreach my $row ( @$tablerows ) {
+ if ($types{$row->{from}}->{type}) {
+ print "altering $table $row->{from} to $row->{to}\n";
+ # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
+# alter table `borrowers` change `faxnumber` `fax` type text null after phone
+ my $sql =
+ "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
+ ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
+ ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
+ "$types{$row->{from}}->{extra} after $row->{after} ";
+# print "$sql";
+ $dbh->do($sql);
+ }
+ }
+}
+
#-------------------
# Initialize
# $default="''" unless $default;
my $extra = $row->{extra};
my $def = $definitions->{$field};
+ my $after = ($row->{after}?" after ".$row->{after}:"");
unless ( $type eq $def->{type}
&& $null eq $def->{null}
# 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 ?");
+ $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ? $after");
} else {
- $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
+ $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ? $after");
}
$sth->execute($default);
- print " Alter $field in $table\n" unless $silent;
+ print " alter or create $field in $table\n" unless $silent;
}
}
}
-
# Populate tables with required data
# changing marc field type
$dbh->do('ALTER TABLE `biblioitems` CHANGE `marc` `marc` BLOB NULL DEFAULT NULL ');
# adding marc xml, just for convenience
- $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT NOT NULL');
+ $dbh->do('ALTER TABLE `biblioitems` ADD `marcxml` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ');
# moving data from marc_subfield_value to biblio
$sth = $dbh->prepare('select bibid,biblionumber from marc_biblio');
$sth->execute;
my $totaldone=0;
while (my ($bibid,$biblionumber) = $sth->fetchrow) {
my $record = MARCgetbiblio($dbh,$bibid);
- $sth_update->execute($record->as_usmarc(),$record->as_xml(),$biblionumber);
+ #Force UTF-8 in record leader
+ $record->encoding('UTF-8');
+ print $record->as_formatted if ($biblionumber==3902);
+ $sth_update->execute($record->as_usmarc(),$record->as_xml_record(),$biblionumber);
$totaldone++;
print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
}
print "\rdone\n";
}
-# MOVE all tables TO UTF-8 and innoDB
-$sth = $dbh->prepare("show table status");
-$sth->execute;
-while ( my $table = $sth->fetchrow_hashref ) {
- if ($table->{Engine} ne 'InnoDB') {
-# $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
- print "moving $table->{Name} to InnoDB\n";
- }
- unless ($table->{Collation} =~ /^utf8/) {
- #$dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
-# $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
- # FIXME : maybe a ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 would be better, def char set seems to work fine. If any problem encountered, let's try with convert !
- print "moving $table->{Name} to utf8\n";
- } else {
- }
-}
# at last, remove useless fields
foreach $table ( keys %uselessfields ) {
} # foreach
+# MOVE all tables TO UTF-8 and innoDB
+$sth = $dbh->prepare("show table status");
+$sth->execute;
+while ( my $table = $sth->fetchrow_hashref ) {
+# if ($table->{Engine} ne 'InnoDB') {
+# $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
+# print "moving $table->{Name} to InnoDB\n";
+# }
+ unless ($table->{Collation} =~ /^utf8/) {
+ $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
+ $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
+ # FIXME : maybe a ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 would be better, def char set seems to work fine. If any problem encountered, let's try with convert !
+ print "moving $table->{Name} to utf8\n";
+ } else {
+ }
+}
+
$sth->finish;
#
# are still here, but uses other tables
# (the ones that are filled by updatedatabase !)
#
+
sub MARCgetbiblio {
# Returns MARC::Record of the biblio passed in parameter.
exit;
# $Log$
+# Revision 1.144 2006/06/08 15:36:31 alaurin
+# Add a new system preference 'AutomaticItemReturn' :
+#
+# if this prefence is switched on: the document returned in another library than homebranch, the system automaticly transfer the document to his homebranch (with notification for librarian in returns.tmpl) .
+#
+# switch off : the document stay in the holdingbranch ...
+#
+# correcting bugs :
+# - comment C4::acquisition (not using in request.pl).
+# - correcting date in request.pl
+# -add the new call of function getbranches in request.pl
+#
+# Revision 1.143 2006/06/07 02:02:47 bob_lyon
+# merging katipo changes...
+#
+# adding new preference IssuingInProcess
+#
+# Revision 1.142 2006/06/06 23:42:46 bob_lyon
+# Merging Katipo changes...
+#
+# Adding new system pref where one can still retrieve a correct reading
+# record history if one has moved older data from issues to oldissues table
+# to speed up issues speed
+#
+# Revision 1.141 2006/06/01 03:18:11 rangi
+# Adding a new column to the statistics table
+#
+# Revision 1.140 2006/05/22 22:40:45 rangi
+# Adding new systempreference allowing for the library to add borrowers to institutions (rest homes, parishes, schools, classes etc).
+#
+# Revision 1.139 2006/05/19 19:31:29 tgarip1957
+# Added new fields to auth_header and auth_subfield_table to allow ZEBRA use of authorities and new MARC framework like structure.
+# Authority tables are modified to be compatible with new MARC frameworks. This change is part of Authority Linking & Zebra authorities. Requires change in Mysql database. It will break head unless all changes regarding this is implemented. This warning will take place on all commits regarding this
+#
+# Revision 1.138 2006/05/19 16:51:44 alaurin
+# update database for :
+# - new feature ip and printer management
+# adding two fields in branches table (branchip,branchprinter)
+#
+# - waiting date : adding one field in reserves table(waiting date) to calculate the Maximum delay to pick up a reserved document when it's available
+#
+# new system preference :
+# - ReservesMaxPickUpDelay : Maximum delay to pick up a reserved document
+# TransfersMaxDaysWarning : Max delay before considering the transfer as potentialy a problem
+#
+# Revision 1.137 2006/04/18 09:36:36 plg
+# bug fixed: typo fixed in labels and labels_conf tables creation query.
+#
+# Revision 1.136 2006/04/17 21:55:33 sushi
+# Added 'labels' and 'labels_conf' tables, for spine lable tool.
+#
+# Revision 1.135 2006/04/15 02:37:03 tgarip1957
+# Marc record should be set to UTF-8 in leader.Force it.
+# XML should be with<record> wrappers
+#
+# Revision 1.134 2006/04/14 09:37:29 tipaul
+# improvements from SAN Ouest Provence :
+# * introducing a category_type into categories. It can be A (adult), C (children), P (Professionnal), I (institution/organisation).
+# * each category_type has it's own forms to create members.
+# * the borrowers table has been heavily modified (many fields changed), to get something more logic & readable
+# * reintroducing guarantor/guanrantee system that is now independant from hardcoded C/A for categories
+# * updating templates to fit template rules
+#
+# (see mail feb, 17 on koha-devel "new features for borrowers" for more details)
+#
+# Revision 1.133 2006/04/13 08:36:42 plg
+# new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on
+# the system preference prefered date format.
+#
+# improvement: book fund list and budget list screen redesigned. Filters on
+# each field. Columns are not sortable yet. Using DHTML Calendar to fill date
+# fields instead of manual filling. Pagination system. From the book fund
+# list, you can reach the budget list, filtered on a book fund, or not. A
+# budget can be added only from book fund list screen.
+#
+# bug fixed: branchcode was missing in table aqbudget.
+#
+# bug fixed: when setting a branchcode to a book fund, all associated budgets
+# move to this branchcode.
+#
+# modification: when adding/modifying budget/fund, MySQL specific "REPLACE..."
+# statements replaced by standard SQL compliant statement.
+#
+# bug fixed: when adding/modifying a budget, if the book fund is associated to
+# a branch, the branch selection is disabled and set to the book fund branch.
+#
+# Revision 1.132 2006/04/06 12:37:05 hdl
+# Bugfixing : aqbookfund needed a field.
+#
+# Revision 1.131 2006/03/03 17:02:22 tipaul
+# commit for holidays and news management.
+# (some forgotten files)
+#
# Revision 1.130 2006/03/03 16:35:21 tipaul
# commit for holidays and news management.
#