upgrade db 1.2 -> 1.3 experiences

[herman vierendeels <..hidden..>]

Hello,

-- I had to do following extra-steps before being able to upgrade db
without errors:

dangling acc_trans  ,  invoice

 delete from lsmb12.acc_trans where trans_id in (SELECT trans_id FROM
lsmb12.acc_trans where trans_id not in (select id from lsmb12.ap union
select id from lsmb12.ar union select id from lsmb12.gl) group by
trans_id);

 delete from lsmb12.invoice where trans_id in (SELECT trans_id FROM
lsmb12.invoice where trans_id not in (select id from lsmb12.ap union
select id from lsmb12.ar union select id from lsmb12.gl) group by
trans_id);

Is this something we should check in sql/upgrade/1.2-pre-upgrade-checks.sql ?


-- i had to do following extra-step before being able to work with upgraded db:

update parts p set expense_accno_id=(select c.id from chart
c,lsmb12.chart lc where c.accno=lc.accno and
lc.id=p.expense_accno_id);

Should we not adapt import of parts in  e.g. sql/upgrade/1.2-1.3-manual.sql

--INSERT INTO parts SELECT * FROM lsmb12.parts;

insert into parts  select
id,partnumber,description,unit,listprice,sellprice,lastcost,priceupdate,weight,onhand,notes,makemodel,assembly,alternate,rop,inventory_accno_id,income_accno_id,
--expense_accno_id,
(select c.id from chart c,lsmb12.chart lc where c.accno=lc.accno and
lc.id=l12parts.expense_accno_id),
bin,obsolete,bom,image,drawing,microfiche,partsgroup_id,project_id,avgcost
person
from lsmb12.parts l12parts

idem for inventory_accno_id or income_accno_id  ??

otherwise, i get error:
INSERT INTO acc_trans (trans_id, chart_id, amount,
transdate, project_id, invoice_id)
VALUES (?, ?, ?, ?, ?, ?)
ERROR: insert or update on table "acc_trans" violates foreign key
constraint "acc_trans_chart_id_fkey"
DETAIL: Key (chart_id)=(10841) is not present in table "account".


 Salutations,

Herman Vierendeels