[ledgersmb-users] Re: Replacement of tokens in SQL files

Hi Pete,

> > I'm trying to upgrade an ancient LSMB (1.2.26) to a slightly less
> > ancient one (1.3.47) and the upgrade is not going smoothly, as you might
> > imagine.
> >

> Is there anything we (the project) can help you with that would allow you
> to pick a newer version?

The upgrade to 1.3.x is just a stepping stone to reach a supported
version. I am going to have to go through a couple of iterations of
upgrading LSMB then upgrading postgresql to inch my way up the ladder.

If there's a better way, do please suggest it!

Well, I'm short on time this weekend, but I've wanted to support migrations from 1.2 to the current versions (1.6 / 1.7) out of the box. Not knowing how many people are still on 1.2, at the time, it looked like a dumb goal to give a high priority. However, if there are (many) others on this mailing list on 1.2 as well, I'll be happy to work on that goal in the short term!

> The problem stems from this line:
> https://github.com/ledgersmb/LedgerSMB/blob/1.3/LedgerSMB/Database.pm#L674
>
> There, the regex should have a 'g' at the end: s/.../.../g. With this "g",
> all occurrances in the string will be replaced instead of only the first
> one.

That's it! [...] Thank you.

No problem :-) You're welcome.

However, there are still a tonne of SQL errors, some of which are no
doubt benign and a lot of which are just because the current transaction
has been aborted. I do see a few foreign key constraint violations which
look a bit concerning. The first such is this:

2020-04-17 11:06:52 BST ERROR: insert or update on table "menu_acl" violates foreign key constraint "menu_acl_node_id_fkey"
2020-04-17 11:06:52 BST DETAIL: Key (node_id)=(134) is not present in table "menu_node".
2020-04-17 11:06:52 BST STATEMENT: INSERT INTO menu_acl (node_id, acl_type, role_name)
values (134, 'allow', 'lsmb_testbooks__backup');

... which might be fine. There is indeed no 134 in menu_node - there's
nothing between 132 (Yearend) and 136 (Chart of Accounts) - but maybe I
just removed whatever that was years ago.

The problem with the database scripts as we had them during the 1.3 release is that they aren't incremental. They'd simply be run on each upgrade again and again while at the same time Pg-database.sql as *also* updated. This causes lots of warnings and errors for which it is hard to estimate if they are problematic or not. Exactly because of that, we have used a different schema-upgrade strategy since 1.5, which tracks which updates have been applied to the schema. Pg-database.sql should be immutable. (Although, in all honesty, I *have* removed some elements from it, to reduce confusion, taking possible-non-existence into account in the schema-upgrade.)

It is followed by the slightly more worrying-looking

2020-04-17 11:06:52 BST ERROR: insert or update on table "account_link" violates foreign key constraint "account_link_description_fkey"
2020-04-17 11:06:52 BST DETAIL: Key (description)=(CT_tax) is not present in table "account_link_description".
2020-04-17 11:06:52 BST CONTEXT: SQL statement "INSERT INTO account_link (account_id, description) VALUES ( $1 , $2 )"
PL/pgSQL function "account_save" line 60 at SQL statement
2020-04-17 11:06:52 BST STATEMENT: SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra,
CASE WHEN link like '%tax%' THEN true ELSE false END,
string_to_array(link,':'))
FROM lsmb12.chart
WHERE charttype = 'A';

There is no CT_* of any sort in account_link_description. Is this something
necessary?

I didn't know the answer to this: I hadn't seen CT_* before today. However, I looked up the 1.2 code and searched for it. It's not in the code base. It's in a few chart-of-accounts definition files, but that's all. I'm thinking your data has a longer history than LedgerSMB 1.2 and that these come from that line of history. Am I right?

Regards,

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.