Problems upgrading from sql-ledger 1.6.6

[Mads Kiilerich <..hidden..>]

Hi,

I'm a *ledger* newbie, so I post this long story to get some feedback 
before posting bug-reports or trying to fix problems already solved...


I'm trying to upgrade a very old sql-ledger 1.6.6 to 
ledger-smb-1.1.1d.tar.gz

I have read README.sql-ledger and run SL2LS.pl. (The wording in the 
README could be improved; there seems to be some redudancy or confusion 
in the references to SL2LS.pl...)


When logging in to the system to get the database upgraded I get

   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] DBD::Pg::st
   execute failed: ERROR: relation "employee" does not exist, referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] DBD::Pg::st
   fetchrow_array failed: no statement executing, referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] DBD::Pg::db
   selectrow_array failed: ERROR: column "employeenumber" does not
   exist, referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] DBD::Pg::db do
   failed: ERROR: column "employeenumber" of relation "defaults" does
   not exist, referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] Can't call
   method "dberror" on an undefined value at LedgerSMB/Form.pm line
   2640., referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] Compilation
   failed in require at /account/login.pl line 104., referer: ...
   [Sat Oct 14 17:07:14 2006] [error] [client x.x.x.x] Premature end of
   script headers: login.pl, referer: ...


The employee relation is introduced in Pg-upgrade-1.8.4-1.8.5.sql. But 
the code in User.pm indicates that that the error isn't critical. Fair 
enough but nice to get fixed...


The dberror bug seems to be a typo:

--- LedgerSMB/Form.pm.org 2006-10-14 23:29:54.000000000 +0200
+++ LedgerSMB/Form.pm 2006-10-14 23:30:15.000000000 +0200
@@ -2637,7 +2637,7 @@
$query = qq|UPDATE defaults
SET $fld = '$dbvar'|;

- $dbh->do($query) || $form->dberror($query);
+ $dbh->do($query) || $self->dberror($query);

if ($closedb) {
$dbh->commit;


I tried again with that fix, and now I got the following on the screen:

   Error!
   UPDATE defaults
   SET employeenumber = '1'
   ERROR: column "employeenumber" of relation "defaults" does not exist


It seems like the code setting defaults can't handle that the relation 
is missing. As a temporary hack I commented the code out:

--- LedgerSMB/User.pm.org 2006-10-15 00:05:15.000000000 +0200
+++ LedgerSMB/User.pm 2006-10-14 23:44:43.000000000 +0200
@@ -140,13 +140,13 @@
$sth->finish;

if (! $id) {
- my ($employeenumber) = $form->update_defaults(\%myconfig, 
"employeenumber", $dbh);
+# my ($employeenumber) = $form->update_defaults(\%myconfig, 
"employeenumber", $dbh);

- $query = qq|INSERT INTO employee (login, employeenumber, name, workphone,
- role)
- VALUES ('$login', '$employeenumber', '$myconfig{name}',
- '$myconfig{tel}', '$myconfig{role}')|;
- $dbh->do($query);
+# $query = qq|INSERT INTO employee (login, employeenumber, name, workphone,
+# role)
+# VALUES ('$login', '$employeenumber', '$myconfig{name}',
+# '$myconfig{tel}', '$myconfig{role}')|;
+# $dbh->do($query);
}
$dbh->disconnect;

@@ -851,10 +851,10 @@

} else {

- my ($employeenumber) = Form::update_defaults("", \%$self, 
"employeenumber", $dbh);
- $query = qq|INSERT INTO employee (login, employeenumber, name, 
workphone, role, email, sales)
- VALUES ('$login', '$employeenumber', '$self->{name}',
- '$self->{tel}', '$self->{role}', '$self->{email}', '1')|;
+# my ($employeenumber) = Form::update_defaults("", \%$self, 
"employeenumber", $dbh);
+# $query = qq|INSERT INTO employee (login, employeenumber, name, 
workphone, role, email, sales)
+# VALUES ('$login', '$employeenumber', '$self->{name}',
+# '$self->{tel}', '$self->{role}', '$self->{email}', '1')|;
}

$dbh->do($query);


I tried again and got:

   Upgrading to Version 1.1.1c ...
   Error!
   update defaults set fxgain_accno_id = (select id from chart where
   chart.accno = temp.accno)
   ERROR: missing FROM-clause entry in subquery for table "temp"


The problem is seen for the first time in Pg-upgrade-1.6.0-1.8.0.sql 
where "temp.accno" seems to be a shortcut for "(select accno from temp)" 
- but it doesn't work.

Google gave me 
http://www.mail-archive.com/..hidden../msg02375.html, and 
adding "add_missing_from=true" to postgresql.conf fixed THAT problem.

(Btw: It would be very nice to be told on the error page which script it 
was that failed...)
(Btw: After having unlocked in admin.pl the admin is shown a loginbox 
again for no reason...)


Finally I got the "oids" problem mentioned in the sql-ledger faq. It 
would be nice to mention/fix it in ledger-smb too. I "fixed" that by 
adding "default_with_oids = on" to postgresql.conf.


FINALLY the upgrade succeded!  :-)


BUT... menu.pl and some other files was previously a symlink to am.pl. 
The upgrade instructions says "copy the files from the tarball over your 
existing SQL-Ledger directory". The result will be files with the wrong 
content. What is the best way to upgrade?


/Mads