How I migrated from sql-ledger 2.8

[Donald Baud <..hidden..>]

I would like to share the notes I kept when I migrated from sql-ledger 2.8.7 to ledgersmb 1.2.7

The following is far from complete, I only migrated the tables that contained information.  
This is just a starting for others who are stuck with sql-ledger 2.8.x
Do not run those commands if you don't understand what they do.  

You might want to wait for ledgersmb 1.3.x which supposedly will have an automated migration path from sql-ledger 2.8
======================

# I first installed a vanilla ledgersmb.  The database name I used was my-ledgersmb.  Once installed, I took a backup with pg_dump and restored the backup as a separate schema in the sql-ledger database.
Storing the ledgersmb in a separate Schema in the sql-ledger database is necessary because I need to do cross-schema queries.

pg_dump --schema=public my-ledgersmb > eraseme.sql
perl -p -i -e 's=my-ledgersmb=sql-ledger=g' eraseme.sql
perl -p -i -e 's=public=myledgersmb=g' eraseme.sql
echo 'drop schema "myledgersmb" cascade' | psql sql-ledger
echo 'CREATE SCHEMA "myledgersmb"' | psql sql-ledger 
cat eraseme.sql | psql sql-ledger
rm eraseme.sql
# ##################

# delete the content of each table and fill it with the content of sql-ledger data

delete from myledgersmb.chart;
insert into myledgersmb.chart (id, accno, description, charttype, category, link, gifi_accno, contra) 
  select id, accno, description, charttype, category, link, gifi_accno, contra from public.chart;

delete from myledgersmb.gifi;
insert into myledgersmb.gifi (accno,description)
   select accno, description from public.gifi;

delete from myledgersmb.tax;
insert into myledgersmb.tax (chart_id, rate, taxnumber, validto, pass, taxmodule_id)
  select chart_id, rate, taxnumber, validto, 0, 1 from public.tax where validto is null;


delete from myledgersmb.acc_trans ;
insert into myledgersmb.acc_trans (trans_id, chart_id, amount, transdate,source, cleared, fx_transaction, project_id, memo, invoice_id)
  select trans_id, chart_id, amount, transdate, source, approved, fx_transaction, project_id, memo, id from public.acc_trans 

delete from myledgersmb.transactions where table_name = 'ap';
delete from myledgersmb.ap ;
insert into myledgersmb.ap (id, invnumber, transdate, vendor_id,taxincluded, amount, netamount, paid, datepaid, duedate,invoice,ordnumber,curr,notes,employee_id,till,quonumber,intnotes,department_id,shipvia,language_code,ponumber,shippingpoint,terms)
  select id, invnumber, transdate, vendor_id,taxincluded, amount, netamount, paid, datepaid, duedate,invoice,ordnumber,curr,notes,employee_id,till,quonumber,intnotes,department_id,shipvia,language_code,ponumber,shippingpoint,terms from public.ap 
  ;

delete from myledgersmb.transactions where table_name = 'ar';
delete from myledgersmb.ar ;
insert into myledgersmb.ar (id, invnumber, transdate, customer_id,taxincluded, amount, netamount, paid, datepaid, duedate,invoice,shippingpoint, terms, notes, curr, ordnumber, employee_id,till,quonumber,intnotes,department_id,shipvia,language_code,ponumber)
  select id, invnumber, transdate, customer_id,taxincluded, amount, netamount, paid, datepaid, duedate,invoice,shippingpoint, terms, notes, curr, ordnumber, employee_id,till,quonumber,intnotes,department_id,shipvia,language_code,ponumber from public.ar 
  ;

delete from myledgersmb.assembly;
insert into myledgersmb.assembly ( id,  parts_id,  qty,  bom,  adj )
  select id,  parts_id,  qty,  bom,  adj from public.assembly;

# #####################

# table customer was a bit tricky because the info was split between sql-ledger.customer and sql-ledger.address

delete from myledgersmb.transactions where table_name = 'customer';
delete from myledgersmb.customer;

insert into myledgersmb.customer (  id,  name,  address1, address2,  city,  state,  zipcode ,  country ,  contact,  phone ,fax,email ,  notes ,  discount ,  taxincluded , creditlimit ,  terms , customernumber ,  cc ,  bcc ,  business_id ,  taxnumber ,  sic_code,  iban  ,  bic  ,  employee_id ,  language_code ,  pricegroup_id ,  curr,  startdate ,  enddate  )
select customer.id, name, address1, address2, city, state,zipcode, country, contact,phone, fax, email, notes, cast(discount as numeric) , taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code, iban, bic, employee_id, language_code, pricegroup_id, curr, startdate,enddate
  from public.address, public.customer
  where address.trans_id = customer.id
  ;


delete from myledgersmb.customertax;

insert into myledgersmb.customertax (customer_id, chart_id)
select myledgersmbcustomer.id, customertax.chart_id
from public.customer , public.customertax , myledgersmb.customer myledgersmbcustomer
where customer.id = customertax.customer_id
  and customer.name = myledgersmbcustomer.name
;


# #####################


# table defaults 
update myledgersmb.defaults set value = publicdefaults.fldvalue
from public.defaults publicdefaults
where 
  publicdefaults.fldname = defaults.setting_key
and
  (
      publicdefaults.fldname = 'customernumber' 
  or publicdefaults.fldname = 'employeenumber' 
  or publicdefaults.fldname = 'expense_accno_id' 
  or publicdefaults.fldname = 'fxgain_accno_id' 
  or publicdefaults.fldname = 'fxloss_accno_id' 
  or publicdefaults.fldname = 'glnumber' 
  or publicdefaults.fldname = 'income_accno_id' 
  or publicdefaults.fldname = 'inventory_accno_id' 
  or publicdefaults.fldname = 'sinumber' 
  or publicdefaults.fldname = 'vendornumber' 
  or publicdefaults.fldname = 'vinumber' 
  )
  ;
# ###################

# ###################
delete from myledgersmb.employee;
insert into myledgersmb.employee (id,  "login",  name ,  address1 ,  address2 ,  city ,  state ,  zipcode ,  country ,  workphone ,  homephone ,  startdate ,  enddate ,  notes ,  "role" ,  sales ,  email ,  ssn ,  iban ,  bic ,  managerid ,  employeenumber ,  dob )
select id,  "login",  name ,  address1 ,  address2 ,  city ,  state ,  zipcode ,  country ,  workphone ,  homephone ,  startdate ,  enddate ,  notes ,  "role" ,  sales ,  email ,  ssn ,  iban ,  bic ,  managerid ,  employeenumber ,  dob from public.employee
;

delete from myledgersmb.gl;
insert into myledgersmb.gl (id, reference, description, transdate, employee_id, notes, department_id  )
select id,  reference, description, transdate, employee_id, notes, department_id  from public.gl
;

delete from myledgersmb.invoice;
insert into myledgersmb.invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, project_id, deliverydate,serialnumber, notes  )
  select id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, project_id, deliverydate,serialnumber, itemnotes   from public.invoice
;

delete from myledgersmb.language;
insert into myledgersmb.language (code, description )
  select code, description  from public.language
;


delete from myledgersmb.parts;
insert into myledgersmb.parts ( id,partnumber,description,unit,listprice,sellprice,lastcost,priceupdate,weight,onhand,notes,makemodel,assembly,alternate,rop,inventory_accno_id,income_accno_id,expense_accno_id,bin,obsolete,bom,image,drawing,microfiche,partsgroup_id,project_id,avgcost )
  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,bin,obsolete,bom,image,drawing,microfiche,partsgroup_id,project_id,avgcost from public.parts
;

delete from myledgersmb.partsgroup;
insert into myledgersmb.partsgroup ( id, partsgroup)
  select id, partsgroup from public.partsgroup
;

delete from myledgersmb.partstax;
insert into myledgersmb.partstax ( parts_id, chart_id)
  select parts_id, chart_id from public.partstax
;

delete from myledgersmb.status;
insert into myledgersmb.status ( trans_id, formname, printed, emailed, spoolfile)
  select trans_id, formname, printed, emailed, spoolfile from public.status
;

delete from myledgersmb.transactions where table_name = 'vendor';
delete from myledgersmb.vendor;
insert into myledgersmb.vendor ( id, name, address1, address2, city, state, zipcode, country, contact, phone, fax, email,notes, terms, taxincluded, vendornumber, cc, bcc, gifi_accno, business_id, taxnumber, sic_code, discount, creditlimit, iban, bic,employee_id, language_code,pricegroup_id,curr,startdate,enddate)
  select vendor.id, name, address1, address2, city, state, zipcode, country, contact, phone, fax, email,notes, terms, taxincluded, vendornumber, cc, bcc, gifi_accno, business_id, taxnumber, sic_code, discount, creditlimit, iban, bic,employee_id, language_code,pricegroup_id,curr,startdate,enddate
   from public.vendor, public.address
   where vendor.id = address.trans_id
;

# I then moved the Schema back from sql-ledger's database into the ledgersmb database
pg_dump --schema=myledgersmb sql-ledger > eraseme.sql
perl -p -i -e 's=sql-ledger=my-ledgersmb=g' eraseme.sql
perl -p -i -e 's=myledgersmb=public=g' eraseme.sql
echo 'DROP SCHEMA public cascade' | psql my-ledgersmb
echo 'CREATE SCHEMA "public"' | psql my-ledgersmb 
cat eraseme.sql | psql my-ledgersmb








       
____________________________________________________________________________________
Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online.
http://smallbusiness.yahoo.com/webhosting