Re: Call for testing experimental patch

[Tony Fraser <..hidden..>]

On Mon, 2006-09-11 at 22:49 -0700, Chris Travers wrote:
> This patch will hopefully get included with the next version but I
> want to put it through some more vetting first.  I am testing it on my
> production machines.
> 
> What it does is install a shadow table with a primary key index which
> ensures that transaciton id's are unique across tables.  I see no
> concerns about integrity of production data.
> 
> Ideas and feedback are welcome.

OK, I said I would put some more thought into this and I finally have. I
know it's maybe a bit late but better late then never, right?

I've attached a SQL script that will make what I think are the right
changes to handle this. It makes changes to the schema that would
require changes in a lot of the .pm files. Really, I'm pretty sure they
would be relatively small changes though. I checked that the attached
script executes cleanly against SQL Ledger schema version 2.6.12. It
would of course work in conjunction with the acc_trans.chart_id
REFERENCES chart.id change that I'm pretty sure has already been made to
the LedgerSMB schema. ie.:

ALTER TABLE chart ADD PRIMARY KEY (id);
ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart;

I think the changes in this patch bring the schema closer to the
accounting reality that it is modelling. I can't think of what the
id_tracker/transactions table would be representing. All that I see
missing is the Ledger -> Sub Ledger relation between GL -> AR and GL ->
AP.

-- 
Tony Fraser
..hidden..
Sybaspace Internet Solutions                        System Administrator
phone: (250) 246-5368                                fax: (250) 246-5398

--
ALTER TABLE gl ADD PRIMARY KEY (id);
ALTER TABLE gl ADD COLUMN ledger text;
ALTER TABLE gl ADD COLUMN module text;
UPDATE gl SET ledger='gl', module='gl';
ALTER TABLE gl ALTER COLUMN ledger SET NOT NULL;
ALTER TABLE gl ALTER COLUMN module SET NOT NULL;
--
--
--
INSERT INTO gl (id, reference, description, transdate, employee_id, notes, department_id, ledger, module)
	SELECT
		id, invnumber, 'AR Transaction ' || invnumber, transdate, employee_id, notes, department_id, 'ar',
		CASE
		  WHEN invoice=true AND till IS NULL THEN 'is'
		  WHEN invoice=true AND till IS NOT NULL THEN 'ps'
		  ELSE 'ar'
		END
	FROM
		ar;
--
ALTER TABLE ar DROP COLUMN invnumber;
ALTER TABLE ar DROP COLUMN transdate;
ALTER TABLE ar DROP COLUMN notes;
ALTER TABLE ar DROP COLUMN department_id;
--
ALTER TABLE ar ADD PRIMARY KEY (id);
ALTER TABLE ar ADD FOREIGN KEY (id) REFERENCES gl;
--
--
--
INSERT INTO gl (id, reference, description, transdate, employee_id, notes, department_id, ledger, module)
	SELECT
		id, invnumber, 'AP Transaction ' || invnumber, transdate, employee_id, notes, department_id, 'ap',
		CASE
		  WHEN invoice=true AND till IS NULL THEN 'ir'
		  WHEN invoice=true AND till IS NOT NULL THEN 'ps'
		  ELSE 'ap'
		END

	FROM
		ap;
--
ALTER TABLE ap DROP COLUMN invnumber;
ALTER TABLE ap DROP COLUMN transdate;
ALTER TABLE ap DROP COLUMN notes;
ALTER TABLE ap DROP COLUMN department_id;
--
ALTER TABLE ap ADD PRIMARY KEY (id);
ALTER TABLE ap ADD FOREIGN KEY (id) REFERENCES gl;
--
--
--
ALTER TABLE acc_trans ADD FOREIGN KEY (trans_id) REFERENCES gl;
--