[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Cleaning database part 1

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

Regards

Mark

Chris Travers wrote:
On Dec 2, 2007 11:14 PM, Mark Clarke <..hidden.. <mailto:..hidden..>> wrote: 

    Hi all,

    I was reading a bug that reports problems with ar/ap/gl entry
    deletion or
    repostings. (
    http://sourceforge.net/tracker/index.php?func=detail&aid=1819483&group_id=175965&atid=875350
    <http://sourceforge.net/tracker/index.php?func=detail&aid=1819483&group_id=175965&atid=875350>)
    The fix was to add the following to the database:

    I am using the latest 1.2.9'

    CREATE RULE gl_track_d AS ON DELETE TO gl DO ALSO DELETE FROM
    transactions
    WHERE id = old.id <http://old.id> AND table_name = 'gl';

    CREATE OR REPLACE RULE ar_track_d AS
       ON DELETE TO ar DO  DELETE FROM transactions
     WHERE transactions.id <http://transactions.id> = old.id
    <http://old.id> AND transactions.table_name = 'ar'

    CREATE OR REPLACE RULE ap_track_d AS
       ON DELETE TO ap DO  DELETE FROM transactions
     WHERE transactions.id <http://transactions.id> = old.id
    <http://old.id> AND transactions.table_name = 'ap'

    To clean up the existing database can I delete from transactions
    table all
    entries that satisfy these searches?


    select * 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

    select * 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

    select * 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


Those should work.  1.3 will be addressing the problem differently.



    Regards

    Mark

    ===========================================
    Cyber Connect - More than just broadband
    http://www.CyberConnect.co.za - Vodacom 3G R99/month

    Cyber Designs - Put your business on the net
    http://www.CyberDesigns.co.za

    Jumping Bean - Your Java and Linux Experts
    http://www.JumpingBean.co.za

    Tel: 011-781 80 14
    Fax: 011-781 80 15
    ===========================================
    Disclaimer

    Any views or opinions presented in this email are solely those of
    the author and do not necessarily represent those of the company.
    Employees of Cyber Designs are expressly required not to make
    defamatory statements and not to infringe or authorize any
    infringement of copyright or any other legal right by email
    communications. Any such communication is contrary to company
    policy and outside the scope of the employment of the individual
    concerned. The company will not accept any liability in respect of
    such communication, and the employee responsible will be
    personally liable for any damages or other liability arising.

    -------------------------------------------------------------------------
    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
    ..hidden..
    <mailto:..hidden..>
    https://lists.sourceforge.net/lists/listinfo/ledger-smb-users


------------------------------------------------------------------------

-------------------------------------------------------------------------
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
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users


===========================================
Cyber Connect - More than just broadband
http://www.CyberConnect.co.za - Vodacom 3G R99/month

Cyber Designs - Put your business on the net
http://www.CyberDesigns.co.za

Jumping Bean - Your Java and Linux Experts
http://www.JumpingBean.co.za

Tel: 011-781 80 14
Fax: 011-781 80 15
===========================================
Disclaimer

Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Employees of Cyber Designs are expressly required not to make defamatory statements and not to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is contrary to company policy and outside the scope of the employment of the individual concerned. The company will not accept any liability in respect of such communication, and the employee responsible will be personally liable for any damages or other liability arising.