On Dec 3, 2007 2:02 AM, Mark Clarke <[EMAIL PROTECTED]> wrote:
> Hi Chris,
>
> Thanks for the help. Jus to be sure it should be safe to run:
>
>
> delete from transactions left join ap on ap.id <http://ap.id> =
> transactions.id <http://transactions.id> where
> table_name = 'ap' and ap.id <http://ap.id> is null
>
> delete from transactions left join ar on ar.id <http://ar.id> =
> transactions.id <http://transactions.id> where
> table_name = 'ar' and ar.id <http://ar.id> is null
>
> delete from transactions left join gl on gl.id <http://gl.id> =
> transactions.id <http://transactions.id> where
> table_name = 'gl' and gl.id <http://gl.id> is null
What you are doing will not cause major issues. However, I would use a
different method.
On second, thought, there is a bug to be aware of. THis doesn't cause a
loss of accounting data integrity, but it can cause some other difficulties
in dealing with this specific table. What I would actually do is rebuild
these portions of your transaction table:
DELETE FROM transactions WHERE table_name IN ('ar', 'ap', 'gl');
INSERT INTO transactions (id, table_name)
SELECT id, 'ar' FROM ar
UNION
SELECT id, 'ap' FROM ap
UNION
SELECT id, 'gl' FROM gl
Just to note what is actually going on here: SQL-Ledger (which we forked
from) did not provide any tracking of unique id's between tables hence you
can get some weird join projection issues if the same id is used twice.
This used to happen when older versions of PostgreSQL (which we no longer
support) were used, and their backup/restore would fail to set the sequence
value properly. I haven't seen this for a long time, but it is possible
that it could be reset through other means, and bad import scripts could
cause problems as well.
LedgerSMB 1.2 attempted to correct this issue by using rules on associated
tables and replicating some information to this transactions table.
Unfortunately there was a bug in our approach (corrected in 1.3) which
causes the id sequence to be incremented again when the insert into
transactions occurs. In most cases, this is not a big issue since it still
guarantees the unique use of id's, but it has been fixed in 1.3 nonetheless.
Hope this helps,
Chris Travers
-------------------------------------------------------------------------
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell. From the desktop to the data center, Linux is going
mainstream. Let it simplify your IT future.
http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
_______________________________________________
Ledger-smb-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
