[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
hacking on scripts/setup.pl this weekend
- Subject: hacking on scripts/setup.pl this weekend
- From: Hugh Esco <..hidden..>
- Date: Mon, 3 Sep 2012 15:52:46 -0400
I want to do some work with the API,
but first I have to complete an upgrade
of my own company's books from 1.2 to 1.3.
I've been working my way through the scripts/setup.pl.
I already have a patch to automate the resolution of
duplicate keys which previously required user intervention.
I'm curious to know who I might send that to for consideration.
There is a copy below.
I am a bit stuck though not knowing the author's intent with this script.
Installing 1.3.22, downloaded from sourceforge yesterday . . .
I see where my old data is moved aside as an lsmb12 schema;
and where the functions and the 1.3.22 ddl are pre-processed and run.
But I have found nothing in the script which seems interested
in importing my legacy data from the lsmb12 schema to the new
public schema.
Nor any design documents which would explain the thinking behind
this tool.
I encountered issues in the person__save function defined in:
sql/modules/Person.sql
I'm advising that the setup.pl form which asks for a default country,
use that to set a row in the public.defaults table,
which can then be queried by the person__save function
to provide a default in the case of an undefined country_id.
With some guidance, like load order, DDL changes which ought
to inform the effort and such, I'd be willing to take a crack
at writing the code for scripts/setup.pl to automate the process
for the next poor sap who faces this upgrade path.
I'd love to hear from anyone familiar with how this script
is put together on how they'd advise I proceed.
See below for preliminary patch. I promised that I would
note the source of the change in the invoice numbers
in a private notes field, but that change has not been written yet,
and is not reflected below.
Any guidance would be appreciated. As I normally read this list
in digest, please consider bcc:'ing me directly in your response.
Thanks,
--
Hugh Esco
skype: hresco3_ ; 678-921-8186 x21
http://www.CampaignFoundations.com/
Providing Application Hosting, Telephony,
Custom Development and Consulting Services
to Green Candidates, Green Parties and
the non profits working for a just and sustainable future.
if( $insurance->rationing() ) { $people->die(); }
if( isa_ok($self,'Troy::Davis') =~ m/^ok/) { $people->are_whole(); }
--------------------------------------------------
diff --git a/scripts/setup.pl b/scripts/setup.pl
index 05d476b..3bb4de7 100644
--- a/scripts/setup.pl
+++ b/scripts/setup.pl
@@ -371,61 +371,85 @@ sub upgrade{
my $locale = $request->{_locale};
my @pre_upgrade_checks = (
- {query => "select id, customernumber, name, address1, city, state, zipcode
- from customer where customernumber in (SELECT customernumber from customer
- GROUP BY customernumber
- HAVING count(*) > 1)",
+ {query => "SELECT id, customernumber, name, address1, city, state, zipcode
+ FROM customer WHERE customernumber IN(
+ SELECT customernumber
+ FROM customer
+ GROUP BY customernumber
+ HAVING count(*) > 1)",
+ update => 'UPDATE customer SET customernumber = ? WHERE id = ?',
name => $locale->text('Unique Customernumber'),
cols => ['customernumber', 'name', 'address1', 'city', 'state', 'zip'],
edit => 'customernumber',
table => 'customer'},
{query => "SELECT id, vendornumber, name, address1, city, state, zipcode
- FROM vendor WHERE vendornumber IN
- (SELECT vendornumber from vendor
- GROUP BY vendornumber
- HAVING count(*) > 1)",
+ FROM vendor WHERE vendornumber IN(
+ SELECT vendornumber from vendor
+ GROUP BY vendornumber
+ HAVING count(*) > 1)",
+ update => 'UPDATE vendor SET vendornumber = ? WHERE id = ?',
name => $locale->text('Unique Vendornumber'),
cols => ['vendornumber', 'name', 'address1', 'city', 'state', 'zip'],
edit => 'vendornumber',
table => 'vendor'},
- {query => 'SELECT * FROM employee where employeenumber IS NULL',
+ {query => 'SELECT * FROM employee WHERE employeenumber IS NULL',
+ update => 'UPDATE employee SET employeenumber = ? WHERE id = ?',
name => $locale->text('No null employeenumber'),
cols => ['login', 'name', 'employeenumber'],
edit => 'employeenumber',
table => 'employee'},
{query => 'SELECT * FROM employee
- WHERE employeenumber IN
- (SELECT employeenumber FROM employee
- GROUP BY employeenumber
- HAVING count(*) > 1)',
+ WHERE employeenumber IN(
+ SELECT employeenumber
+ FROM employee
+ GROUP BY employeenumber
+ HAVING count(*) > 1)',
+ update => 'UPDATE employee SET employeenumber = ? WHERE id = ?',
name => $locale->text('Duplicte employee numbers'),
cols => ['login', 'name', 'employeenumber'],
edit => 'employeenumber',
table => 'employee'},
- {query => "select * from parts where obsolete is not true
- and partnumber in
- (select partnumber from parts
- WHERE obsolete is not true
- group by partnumber having count(*) > 1)",
+ {query => "SELECT * FROM parts
+ WHERE obsolete IS NOT true
+ AND partnumber IN(
+ SELECT partnumber
+ FROM parts
+ WHERE obsolete IS NOT true
+ GROUP BY partnumber HAVING COUNT(*) > 1)",
+ update => 'UPDATE parts SET partnumber = ? WHERE id = ?',
name => $locale->text('Unique nonobsolete partnumbers'),
cols => ['partnumber', 'description', 'sellprice'],
edit => 'partnumber',
table => 'parts'},
- {query => 'SELECT * from ar where invnumber in (
- select invnumber from ar
- group by invnumber having count(*) > 1)',
+ {query => 'SELECT * FROM ar WHERE invnumber IN(
+ SELECT invnumber FROM ar
+ GROUP BY invnumber HAVING COUNT(*) > 1)',
+ update => 'UPDATE ar SET invnumber = ? WHERE id = ?',
name => $locale->text('Unique AR Invoice numbers'),
cols => ['invnumber', 'transdate', 'amount', 'netamount', 'paid'],
edit => 'invnumber',
table => 'ar'},
);
+ my @failed_checks;
for my $check (@pre_upgrade_checks){
my $sth = $request->{dbh}->prepare($check->{query});
+ my $sth_update = $request->{dbh}->prepare($check->{update});
+ $sth->execute();
+ if ($sth->rows > 0){ # Check fail --CT
+ _repair_duplicate_keys($request, $check, $sth, $sth_update);
+ $sth->execute();
+ if ($sth->rows > 0){ # Check is still failing
+ push @failed_checks, $check;
+ }
+ }
+ }
+ for my $check (@failed_checks){
+ my $sth = $request->{dbh}->prepare($check->{query});
$sth->execute();
if ($sth->rows > 0){ # Check failed --CT
_failed_check($request, $check, $sth);
@@ -441,6 +465,22 @@ sub upgrade{
}
+sub _repair_duplicate_keys {
+ my ($request, $check, $sth, $sth_update) = @_;
+ my $count = 1;
+ while (my $row = $sth->fetchrow_hashref('NAME_lc')){
+ my $new_key;
+ if( $row->{$check->{edit}} ){
+ $new_key = $row->{$check->{edit}} . '-' . $count;
+ } else {
+ $new_key = $count;
+ }
+ $sth_update->execute($new_key,$row->{id});
+ $count++;
+ }
+ return;
+}
+
sub _failed_check{
my ($request, $check, $sth) = @_;
my $template = LedgerSMB::Template->new(