From 757a1cf2eaf6b6355847d99f6a075fb9c5eb41bc Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 23 Oct 2012 14:48:41 -0400 Subject: [PATCH] Bug 8956 - Split serials enumeration data into separate fields - Part 1 - Add fields to DB Content-Type: text/plain; charset="utf-8" --- installer/data/mysql/updatedatabase.pl | 71 ++++++++++++++++++++++++++++++++ 1 files changed, 71 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 4759034..4801476 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6019,6 +6019,77 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.09.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("ALTER TABLE serial ADD published_end_date DATE NULL DEFAULT NULL AFTER publisheddate"); + $dbh->do(" + ALTER TABLE serial ADD serialseq_x VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq, + ADD serialseq_y VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x, + ADD serialseq_z VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y + "); + $dbh->do(" + ALTER TABLE subscription ADD serialseq_x_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER numberingmethod, + ADD serialseq_y_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_x_label, + ADD serialseq_z_label VARCHAR( 100 ) NULL DEFAULT NULL AFTER serialseq_y_label + "); + + my $trim = sub { + my $string = shift; + $string =~ s/^\s+//; + $string =~ s/\s+$//; + return $string; + }; + + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare("SELECT * FROM subscription ORDER BY numberingmethod ASC"); + $sth->execute(); + while ( my $subscription = $sth->fetchrow_hashref() ) { + ## parse out the labels and save them + my $nm = $subscription->{'numberingmethod'}; + print "NUMBERING METHOD: '$nm'\n"; + my @labels = split(/\{[XYZ]\}[, ]?/, $nm); + @labels = map( $trim->( $_ ), @labels ); + my ( $x_label, $y_label, $z_label ) = @labels; + + $dbh->do( + "UPDATE subscription SET serialseq_x_label = ?, serialseq_y_label = ?, serialseq_z_label = ? WHERE subscriptionid = ?", + undef, + ( $x_label, $y_label, $z_label, $subscription->{'subscriptionid'} ) + ); + + ## parse of the data and save + my @splits = split(/\{[XYZ]\}/, $nm); + foreach my $s ( @splits ) { + $s = quotemeta( $s ); + } + + my $sth2 = $dbh->prepare("SELECT * FROM serial WHERE subscriptionid = ?"); + $sth2->execute( $subscription->{'subscriptionid'} ); + while( my $serial = $sth2->fetchrow_hashref() ) { + my $serialseq = $serial->{'serialseq'}; + my ( $x, $y, $z ); + ## We cannot split on multiple values at once, + ## so let's replace each of those values with __SPLIT__ + if ( @splits ) { + map( $serialseq =~ s/$_/__SPLIT__/ , @splits ); + ( undef, $x, $y, $z ) = split(/__SPLIT__/, $serialseq); + } else { ## Nothing to split on means the only thing in serialseq is {X} + $x = $serialseq; + } + + $dbh->do( + "UPDATE serial SET serialseq_x = ?, serialseq_y = ?, serialseq_z = ? WHERE serialid = ?", + undef, + ( $x, $y, $z, $serial->{'serialid'} ) + ); + } + } + + print "Upgrade to $DBversion done ( Split serials enumeration data into separate fields )\n"; + SetVersion ($DBversion); + +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.2.5