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
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
Ledger-smb-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
