[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
sql/upgrade/1.2-1.4.sql patches
- Subject: sql/upgrade/1.2-1.4.sql patches
- From: ..hidden..
- Date: Tue, 26 Nov 2013 16:04:51 -0800
Here are a coupple of patches for 1.2-1.4.sql.
The file 1.2-1.4.sql-2fixes.patch fixes what I think are 2 bugs one is
missing () around a statement that contains an END and the other was a
problem i encountered with a new column barcode being in the makemodel
table.
The second patch 1.2-1.4.sql-turtles.patch is what I have had to do a
manual upgrade (so far) and is not submitted for the project. Its just a
reference hopefully to save someone manually upgrading 1.2 - 1.4 some
time.
One thing in there that may be of interest to someone upgrading or
desiring with duplicate partnumbers is that I looked into why
parts.partnumber had a unique constraint and found it was only an index to
speed up searching?
So in patch 1.2-1.4.sql-turtles.patch I drop the parts index and rebuild
it as a regular one not a unique one before parts are imported. I don't
think this is an issue however it would be good to confirm.
Why support duplicate parts? A user can have two parts with the same
partnumber using different parts groups or case so I did not feel like
renaming thousands of somebodies duplicate part numbers :)
Hope this helps
Cheers
Turtle
--- 1.2-1.4.sql 2013-09-30 15:17:47.345672947 -0700
+++ 1.2-1.4.sql-account__save-fix-makemodelfix.sql 2013-11-26 15:36:11.902475653 -0800
@@ -29,7 +29,7 @@
FROM lsmb12.chart WHERE charttype = 'H';
SELECT account__save(id, accno, description, category, gifi_accno, NULL, contra,
- CASE WHEN link like '%tax%' THEN true ELSE false END,
+ (CASE WHEN link like '%tax%' THEN true ELSE false END),
string_to_array(link,':'), false, false)
FROM lsmb12.chart
WHERE charttype = 'A';
@@ -398,7 +398,8 @@
-- needed to handle null values
UPDATE lsmb12.makemodel set model = '' where model is null;
-INSERT INTO makemodel
+--barcode will throw off SELECT * FROM makemodel
+INSERT INTO makemodel (parts_id, make, model)
SELECT * FROM lsmb12.makemodel;
INSERT INTO gifi--- 1.2-1.4.sql 2013-09-30 15:17:47.345672947 -0700
+++ 1.2-1.4.sql.new 2013-11-26 15:31:24.927371833 -0800
@@ -1,10 +1,39 @@
--Setup
+-- run this from your top level ledgersmb directory
+-- Set your defaults here for manual upgrading
+\set default_country '''us'''
+\set ar '''1200'''
+\set ap '''2100'''
--- When moved to an interface, these will all be specified and preprocessed.
-\set default_country '''<?lsmb default_country ?>'''
-\set ar '''<?lsmb default_ar ?>'''
-\set ap '''<?lsmb default_ap ?>'''
+BEGIN;
+--Existing data is safely being moved to lsmb12
+ALTER SCHEMA public RENAME TO lsmb12;
+CREATE SCHEMA public;
+
+CREATE EXTENSION pg_trgm;
+CREATE EXTENSION tsearch2;
+CREATE EXTENSION tablefunc;
+COMMIT;
+BEGIN;
+
+--\cd :lsmbdir changing directories does not work due to permissions I think
+
+-- Full module load should be part of upgrade wizard, at this stage.
+--these had to be done manually --turtle
+\i sql/Pg-database.sql
+\i sql/modules/Settings.sql --mispelled
+\i sql/modules/Location.sql
+\i sql/modules/Account.sql
+\i sql/modules/Payment.sql
+--Adding Company.sql fixes failure Person.sql failure below?
+\i sql/modules/Company.sql
+\i sql/modules/Person.sql
+\i sql/modules/Reconciliation.sql
+
+--do all the others need to get added?
+
+COMMIT;
BEGIN;
-- adding mapping info for import.
@@ -29,7 +58,7 @@
FROM lsmb12.chart WHERE charttype = 'H';
SELECT account__save(id, accno, description, category, gifi_accno, NULL, contra,
- CASE WHEN link like '%tax%' THEN true ELSE false END,
+ (CASE WHEN link like '%tax%' THEN true ELSE false END),
string_to_array(link,':'), false, false)
FROM lsmb12.chart
WHERE charttype = 'A';
@@ -376,20 +405,21 @@
FROM lsmb12.employee em
WHERE id IN (select min(id) from lsmb12.employee group by entity_id);
-
+COMMIT;
+BEGIN;
-- I would prefer stronger passwords here but the exposure is very short, since
-- the passwords time out after 24 hours anyway. These are not assumed to be
-- usable passwords. --CT
-
-SELECT admin__save_user(null, max(entity_id), login, random()::text, true)
- FROM lsmb12.employee
- WHERE login IN (select rolname FROM pg_roles)
- GROUP BY login;
-
-SELECT admin__save_user(null, max(entity_id), login, random()::text, false)
- FROM lsmb12.employee
- WHERE login NOT IN (select rolname FROM pg_roles)
- GROUP BY login;
+--do manually -T ?
+-- SELECT admin__save_user(null, max(entity_id), login, random()::text, true)
+-- FROM lsmb12.employee
+-- WHERE login IN (select rolname FROM pg_roles)
+-- GROUP BY login;
+--
+-- SELECT admin__save_user(null, max(entity_id), login, random()::text, false)
+-- FROM lsmb12.employee
+-- WHERE login NOT IN (select rolname FROM pg_roles)
+-- GROUP BY login;
@@ -398,17 +428,23 @@
-- needed to handle null values
UPDATE lsmb12.makemodel set model = '' where model is null;
-INSERT INTO makemodel
+--barcode will throw off SELECT * FROM makemodel
+INSERT INTO makemodel (parts_id, make, model)
SELECT * FROM lsmb12.makemodel;
INSERT INTO gifi
SELECT * FROM lsmb12.gifi;
+COMMIT;
+BEGIN;
+
UPDATE defaults
SET value = (select value from lsmb12.defaults src
WHERE src.setting_key = defaults.setting_key)
WHERE setting_key IN (select setting_key FROM lsmb12.defaults);
+COMMIT;
+BEGIN;
INSERT INTO parts (
id,
@@ -473,6 +509,8 @@
LEFT JOIN account income_accno ON incc.accno = income_accno.accno
LEFT JOIN account expense_accno ON expc.accno = expense_accno.accno;
+ --I somehow had broken assemblies by deleting parts and not poking the assembly table
+DELETE FROM lsmb12.assembly WHERE lsmb12.assembly.parts_id NOT IN (SELECT ID FROM lsmb12.parts) ;
INSERT INTO assembly SELECT * FROM lsmb12.assembly;
ALTER TABLE gl DISABLE TRIGGER gl_audit_trail;
@@ -562,6 +600,8 @@
JOIN lsmb12.chart ON chart.id = pt.chart_id
JOIN account a ON chart.accno = a.accno;
+COMMIT;
+BEGIN;
INSERT INTO business_unit_inv (entry_id, class_id, bu_id)
SELECT inv.id, 1, gl.department_id
FROM invoice inv
@@ -645,6 +685,8 @@
INSERT INTO warehouse SELECT * FROM lsmb12.warehouse;
+COMMIT;
+BEGIN;
INSERT INTO inventory(entity_id, warehouse_id, parts_id, trans_id,
orderitems_id, qty, shippingdate, entry_id)
SELECT e.entity_id, warehouse_id, parts_id, trans_id,
@@ -708,9 +750,9 @@
INSERT INTO business_unit_translation (trans_id, description, language_code)
SELECT trans_id + 1000, description, language_code
FROM lsmb12.translation where trans_id in (select id from lsmb12.project);
-
-SELECT setval('id', max(id)) FROM transactions;
-
+COMMIT;
+BEGIN;
+ SELECT setval('id', max(id)) FROM transactions;
SELECT setval('acc_trans_entry_id_seq', max(entry_id)) FROM acc_trans;
SELECT setval('partsvendor_entry_id_seq', max(entry_id)) FROM partsvendor;
SELECT setval('inventory_entry_id_seq', max(entry_id)) FROM inventory;