[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[6546] branches/1.3/sql/upgrade/sl2.8-1.3.sql
- Subject: SF.net SVN: ledger-smb:[6546] branches/1.3/sql/upgrade/sl2.8-1.3.sql
- From: ..hidden..
- Date: Sun, 19 Jan 2014 11:04:57 +0000
Revision: 6546
http://sourceforge.net/p/ledger-smb/code/6546
Author: ehuelsmann
Date: 2014-01-19 11:04:56 +0000 (Sun, 19 Jan 2014)
Log Message:
-----------
Backport of SL28 migration script.
Modified Paths:
--------------
branches/1.3/sql/upgrade/sl2.8-1.3.sql
Modified: branches/1.3/sql/upgrade/sl2.8-1.3.sql
===================================================================
--- branches/1.3/sql/upgrade/sl2.8-1.3.sql 2014-01-19 09:57:53 UTC (rev 6545)
+++ branches/1.3/sql/upgrade/sl2.8-1.3.sql 2014-01-19 11:04:56 UTC (rev 6546)
@@ -60,13 +60,17 @@
INSERT INTO entity_credit_account
(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id,
cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, vendornumber, business_id, creditlimit, aa.id,
- a.id, startdate, enddate, threshold, 1
-FROM sl28.vendor
-LEFT JOIN sl28.chart c on payment_accno_id = c.id
-LEFT JOIN account a on c.accno = a.accno
- JOIN account aa ON aa.accno = :ap
- WHERE entity_id IS NOT NULL;
+SELECT entity_id, vendornumber, business_id, creditlimit,
+ (select id
+ from account
+ where accno = coalesce((select accno from sl28.chart
+ where id = arap_accno_id) ,:ap)),
+ (select id
+ from account
+ where accno = (select accno from sl28.chart
+ where id = payment_accno_id)),
+ startdate, enddate, threshold, 1
+FROM sl28.vendor WHERE entity_id IS NOT NULL;
UPDATE sl28.vendor SET credit_id =
(SELECT id FROM entity_credit_account e
@@ -76,13 +80,17 @@
INSERT INTO entity_credit_account
(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id,
cash_account_id, startdate, enddate, threshold, entity_class)
-SELECT entity_id, customernumber, business_id, creditlimit, aa.id,
- a.id, startdate, enddate, threshold, 2
-FROM sl28.customer
-LEFT JOIN sl28.chart c on payment_accno_id = c.id
-LEFT JOIN account a on c.accno = a.accno
- JOIN account aa ON aa.accno = :ar
- WHERE entity_id IS NOT NULL;
+SELECT entity_id, customernumber, business_id, creditlimit,
+ (select id
+ from account
+ where accno = coalesce((select accno from sl28.chart
+ where id = arap_accno_id) ,:ar)),
+ (select id
+ from account
+ where accno = (select accno from sl28.chart
+ where id = payment_accno_id)),
+ startdate, enddate, threshold, 2
+FROM sl28.customer WHERE entity_id IS NOT NULL;
UPDATE sl28.customer SET credit_id =
(SELECT id FROM entity_credit_account e
@@ -256,8 +264,8 @@
lower(trim(both ' ' from c.short_name)) = lower( trim(both ' ' from oa.shiptocountry))
JOIN (select credit_id, id from sl28.vendor
union
- select credit_id, id from sl28.customer) ov ON oa.trans_id = ov.id
-JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
+ select credit_id, id from sl28.customer) v ON oa.trans_id = v.id
+JOIN entity_credit_account eca ON (v.credit_id = eca.id)
GROUP BY eca.id;
INSERT INTO eca_note(note_class, ref_key, note, vector)
@@ -312,6 +320,11 @@
FROM sl28.employee em;
+
+-- must rebuild this table due to changes since 1.2
+
+INSERT INTO partsgroup (id, partsgroup) SELECT id, partsgroup FROM sl28.partsgroup;
+
insert into parts
(id, partnumber, description, unit, listprice,
sellprice, lastcost, priceupdate, weight, onhand,
@@ -324,30 +337,42 @@
notes, makemodel, assembly, alternate, rop,
bin, obsolete, bom, image, drawing,
microfiche, partsgroup_id, project_id, avgcost,
- inv.id, inc.id, exp.id
- FROM sl28.parts p
- LEFT JOIN sl28.chart cinv ON inventory_accno_id = cinv.id
- LEFT JOIN account inv ON cinv.accno = inv.accno
- LEFT JOIN sl28.chart cinc ON income_accno_id = cinc.id
- LEFT JOIN account inc ON cinc.accno = inc.accno
- LEFT JOIN sl28.chart cexp ON expense_accno_id = cexp.id
- LEFT JOIN account exp ON cexp.accno = exp.accno;
+ (select id
+ from public.account
+ where accno = (select accno from sl28.chart
+ where id = inventory_accno_id)),
+ (select id
+ from public.account
+ where accno = (select accno from sl28.chart
+ where id = income_accno_id)),
+ (select id
+ from public.account
+ where accno = (select accno from sl28.chart
+ where id = expense_accno_id))
+ FROM sl28.parts p;
+INSERT INTO makemodel (parts_id, make, model)
+SELECT parts_id, make, model FROM sl28.makemodel;
--- must rebuild this table due to changes since 1.2
-
-INSERT INTO makemodel
-SELECT * FROM sl28.makemodel;
-
INSERT INTO gifi
SELECT * FROM sl28.gifi;
+/* TODO -- can't be solved this easily: a freshly created defaults
+table contains 30 keys, one after having saved the System->Defaults
+screen contains 58. Also, there are account IDs here, which should
+be migrated using queries, not just copied over.
+
+To watch out for: keys which are semantically the same, but have
+different names
+
UPDATE defaults
SET value = (select fldvalue from sl28.defaults src
WHERE src.fldname = defaults.setting_key)
- WHERE setting_key IN (select fldname FROM sl28.defaults);
+ WHERE setting_key IN (select fldvalue FROM sl28.defaults
+ where );
+*/
--INSERT INTO assembly SELECT * FROM sl28.assembly;
@@ -408,16 +433,25 @@
ALTER TABLE ap ENABLE TRIGGER ap_audit_trail;
+ALTER TABLE sl28.acc_trans ADD COLUMN entry_id integer;
+
+update sl28.acc_trans
+ set entry_id = nextval('acc_trans_entry_id_seq');
+
INSERT INTO acc_trans
-(trans_id, chart_id, amount, transdate, source, cleared, fx_transaction,
+(entry_id, trans_id, chart_id, amount, transdate, source, cleared, fx_transaction,
project_id, memo, approved, cleared_on, reconciled_on,
voucher_id)
-SELECT trans_id, a.id, amount, transdate, source,
+SELECT entry_id, trans_id,
+ (select id
+ from account
+ where accno = (select accno
+ from sl28.chart
+ where chart.id = acc_trans.chart_id)),
+ amount, transdate, source,
CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
project_id, memo, approved, cleared, null, vr_id
- FROM sl28.acc_trans ac
- JOIN sl28.chart c on c.id = ac.chart_id
- join account a on c.accno= a.accno;
+ FROM sl28.acc_trans ac;
INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
@@ -440,20 +474,19 @@
JOIN sl28.chart c ON (t.chart_id = c.id)
JOIN account a ON (a.accno = c.accno);
-INSERT INTO customertax (customer_id, chart_id)
- SELECT c.credit_id, a.id
- FROM sl28.customertax pt
- JOIN sl28.customer c ON (pt.customer_id = c.id)
- JOIN sl28.chart ON chart.id = pt.chart_id
- JOIN account a ON chart.accno = a.accno;
+INSERT INTO eca_tax (credit_id, chart_id)
+ SELECT credit_id, (select id from account
+ where accno = (select accno from sl28.chart sc
+ where sc.id = ct.chart_id))
+ FROM sl28.customertax ct
+ UNION
+ SELECT credit_id, (select id from account
+ where accno = (select accno from sl28.chart sc
+ where sc.id = vt.chart_id))
+ FROM sl28.vendortax vt;
-INSERT INTO vendortax (vendor_id, chart_id)
- SELECT c.credit_id, a.id
- FROM sl28.vendortax pt
- JOIN sl28.vendor c ON (pt.vendor_id = c.id)
- JOIN sl28.chart ON chart.id = pt.chart_id
- JOIN account a ON chart.accno = a.accno;
+
INSERT
INTO oe(id, ordnumber, transdate, amount, netamount, reqdate, taxincluded,
shippingpoint, notes, curr, person_id, closed, quotation, quonumber,
@@ -538,10 +571,11 @@
INSERT INTO user_preference(id)
SELECT id from users;
-INSERT INTO recurring(id, reference, startdate, nextdate, enddate, repeat,
- unit, howmany, payment)
- SELECT id, reference, startdate, nextdate, enddate, repeat,
- unit, howmany, payment
+INSERT INTO recurring(id, reference, startdate, nextdate, enddate,
+ recurring_interval, howmany, payment)
+ SELECT id, reference, startdate, nextdate, enddate,
+ (repeat || ' ' || unit)::interval,
+ howmany, payment
FROM sl28.recurring;
INSERT INTO recurringemail SELECT * FROM sl28.recurringemail;
@@ -619,15 +653,13 @@
SELECT setval('menu_node_id_seq', max(id)) FROM menu_node;
SELECT setval('menu_attribute_id_seq', max(id)) FROM menu_attribute;
SELECT setval('menu_acl_id_seq', max(id)) FROM menu_acl;
- SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
+-- SELECT setval('pending_job_id_seq', max(id)) FROM pending_job;
SELECT setval('new_shipto_id_seq', max(id)) FROM new_shipto;
SELECT setval('payment_id_seq', max(id)) FROM payment;
SELECT setval('cr_report_id_seq', max(id)) FROM cr_report;
SELECT setval('cr_report_line_id_seq', max(id)) FROM cr_report_line;
-UPDATE defaults SET value = '1.3.0' WHERE setting_key = 'version';
-
COMMIT;
--TODO: Translation migratiion. Partsgroups?
-- TODO: User/password Migration
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits