[SOLVED] How to clean database?

General discussion on all other topics under the sun.

Moderator: Renato

Cobra
New MMEX User
Posts: 14
Joined: Thu Oct 29, 2015 8:32 am
Are you a spam bot?: No
Contact:

[SOLVED] How to clean database?

Post by Cobra »

How can I clean up the database from all records, balances, historical budgets, leaving only the names of categories, accounts, customers, etc.
I want to use it as a "template" to start from scratch, but without having to create all the settings yet.
Last edited by Cobra on Tue Dec 22, 2020 10:02 am, edited 1 time in total.
Thanks in advance for any advice and help.

Where I'm working
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: How to clean database?

Post by Nikolay »

Delete all transactions in current view is only options available for now.
Repeat it for every account.
Cobra
New MMEX User
Posts: 14
Joined: Thu Oct 29, 2015 8:32 am
Are you a spam bot?: No
Contact:

Re: How to clean database?

Post by Cobra »

I did it and I moved all recurring operations to 2021, but then why do I have a budget for 2019?
Thanks in advance for any advice and help.

Where I'm working
User avatar
Renato
MVP MMEX User
Posts: 655
Joined: Mon Apr 25, 2011 7:36 pm
Are you a spam bot?: No
Location: near Zurich

Re: How to clean database?

Post by Renato »

I would use the DB Browser to delete the unwanted tables. But please be careful not to destroy the DB structure.
http://sqlitebrowser.org
Renato Forum Administrator
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: How to clean database?

Post by Nikolay »

Any budget record are some data in BUDGETTABLE_V1 and BUDGETYEAR_V1 tables.

It may be deleted as well
budget.png
budget.png (84.41 KiB) Viewed 3204 times
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: How to clean database?

Post by Nikolay »

There are Tools - Database - Database Debug menu item.
There may be executed SQL script like this (save it to a file with .mmdbg extension):

Code: Select all

-- MMEX Debug SQL - Update --

delete from BUDGETTABLE_V1 
where BUDGETYEARID in 
  (select BUDGETYEARID from BUDGETYEAR_V1 where BUDGETYEARNAME like '2019%');

delete from BUDGETYEAR_V1 where BUDGETYEARNAME like '2019%';

delete from CUSTOMFIELDDATA_V1 where REFID in
(select TRANSID from CHECKINGACCOUNT_V1 where strftime('%Y', TRANSDATE)='2019');

DELETE from ATTACHMENT_V1 WHERE REFID in 
(select TRANSID from CHECKINGACCOUNT_V1 where strftime('%Y', TRANSDATE)='2019');

delete from SPLITTRANSACTIONS_V1 where TRANSID in
(select TRANSID from CHECKINGACCOUNT_V1 where strftime('%Y', TRANSDATE)='2019');

DELETE from CHECKINGACCOUNT_V1 where strftime('%Y', TRANSDATE)='2019';

Last edited by Nikolay on Tue Dec 22, 2020 8:11 am, edited 1 time in total.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: How to clean database?

Post by Nikolay »

Cobra
New MMEX User
Posts: 14
Joined: Thu Oct 29, 2015 8:32 am
Are you a spam bot?: No
Contact:

Re: How to clean database?

Post by Cobra »

OK, many thanks to all
Thanks in advance for any advice and help.

Where I'm working
skassama
New MMEX User
Posts: 2
Joined: Tue Mar 28, 2023 3:53 pm
Are you a spam bot?: No

Re: [SOLVED] How to clean database?

Post by skassama »

Thanks for the info,

How can we reset the Transaction ID counter?

Have a nice day
nstein
MMEX Developer
Posts: 39
Joined: Tue Nov 29, 2022 10:31 pm
Are you a spam bot?: No

Re: [SOLVED] How to clean database?

Post by nstein »

The next Transaction ID is always max(TRANSID) + 1, and deleting a transaction automatically frees that ID for use. To reset the Transaction ID counter to 1 you would have to delete all transactions.
Post Reply