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

Cleaning database part 1



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) 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 AND table_name = 'gl';

CREATE OR REPLACE RULE ar_track_d AS
   ON DELETE TO ar DO  DELETE FROM transactions
 WHERE transactions.id = 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 = 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 = transactions.id where
table_name = 'ap' and ap.id is null

select * from transactions left join ar on ar.id = transactions.id where
table_name = 'ar' and ar.id is null

select * from transactions left join gl on gl.id = transactions.id where
table_name = 'gl' and gl.id is null

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.