SF.net SVN: ledger-smb: [95] trunk/utils

[..hidden..]

Revision: 95
          http://svn.sourceforge.net/ledger-smb/?rev=95&view=rev
Author:   einhverfr
Date:     2006-09-14 23:15:24 -0700 (Thu, 14 Sep 2006)

Log Message:
-----------
Committing first draft of fixme utility for broken.  Will be putting it through more testing.

Added Paths:
-----------
    trunk/utils/fixme/
    trunk/utils/fixme/README
    trunk/utils/fixme/fixme.pgsql

Added: trunk/utils/fixme/README
===================================================================
--- trunk/utils/fixme/README	                        (rev 0)
+++ trunk/utils/fixme/README	2006-09-15 06:15:24 UTC (rev 95)
@@ -0,0 +1,9 @@
+README for fixme.pgsql
+
+This utility attempts to move all damaged data into one location so that it 
+can be sorted out.  It creates an account with the number of 1000000000 that 
+stores the broken transactions.
+
+It can be run from psql.
+
+IMPORTANT:  BACK UP YOUR DATABASE BEFORE TRYING THIS SCRIPT

Added: trunk/utils/fixme/fixme.pgsql
===================================================================
--- trunk/utils/fixme/fixme.pgsql	                        (rev 0)
+++ trunk/utils/fixme/fixme.pgsql	2006-09-15 06:15:24 UTC (rev 95)
@@ -0,0 +1,63 @@
+begin;
+
+-- handle NULL chart_id's
+insert into chart (accno, description, charttype) values ('1000000000', 'Broken SQL-Ledger Data', 'L');
+
+--handle duplicate transaction id's.
+CREATE TEMPORARY TABLE id_agregator (id INT, table_name text);
+
+insert into id_agregator (id, table_name) SELECT id, 'ar' from ar;
+insert into id_agregator (id, table_name) SELECT id, 'ap' from ap;
+insert into id_agregator (id, table_name) SELECT id, 'business' from business;
+insert into id_agregator (id, table_name) SELECT id, 'chart' from chart;
+insert into id_agregator (id, table_name) SELECT id, 'customer' from customer;
+insert into id_agregator (id, table_name) 
+	SELECT id, 'department' from department;
+insert into id_agregator (id, table_name) SELECT id, 'employee' from employee;
+insert into id_agregator (id, table_name) SELECT id, 'gl' from gl;
+insert into id_agregator (id, table_name) SELECT id, 'oe' from oe;
+insert into id_agregator (id, table_name) SELECT id, 'parts' from parts;
+insert into id_agregator (id, table_name) 
+	SELECT id, 'partsgroup' from partsgroup;
+insert into id_agregator (id, table_name) SELECT id, 'project' from project;
+insert into id_agregator (id, table_name) SELECT id, 'vendor' from vendor;
+insert into id_agregator (id, table_name) SELECT id, 'warehouse' from warehouse;
+
+CREATE TEMPORARY VIEW id_view1 AS
+SELECT id, count(*) AS num_rows FROM id_agregator 
+GROUP BY id HAVING num_rows > 1;
+
+select setval('id', select max(id) + 1 from id_agregator);
+
+create function fix_dupes() RETURNS OPAQUE AS
+' 
+DECLARE
+dupe_id id_agregator%ROWTYPE;
+BEGIN
+FOR dupe_id IN SELECT id FROM id_agregator 
+	WHERE id IN (SELECT id FROM id_view1)
+LOOP
+	EXECUTE ''UPDATE '' || dupe_id.table_name ||
+			SET id = nextval(''''id'''') WHERE
+		id = '' ||dupe_id.id;
+	UPDATE acc_trans SET trans_id = currval(''id'') WHERE
+		id = '' ||dupe_id.id;
+	INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES (
+		currval(''id''), '1', (
+			SELECT id FROM chart WHERE accno = ''1000000000''
+		)
+	);
+	INSERT INTO acc_trans (trans_id, amount, chart_id) VALUES (
+		currval(''id''), '-1', (
+			SELECT id FROM chart WHERE accno = ''1000000000''
+		)
+	);
+END;
+' LANGUAGE PLPGSQL;
+
+SELECT fix_dupes ();
+
+drop function fix_dupes();
+
+
+commit;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.