Page 1 of 1
[SOLVED] How to clean database?
Posted: Mon Dec 21, 2020 7:25 pm
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.
Re: How to clean database?
Posted: Mon Dec 21, 2020 7:38 pm
by Nikolay
Delete all transactions in current view is only options available for now.
Repeat it for every account.
Re: How to clean database?
Posted: Mon Dec 21, 2020 8:21 pm
by Cobra
I did it and I moved all recurring operations to 2021, but then why do I have a budget for 2019?
Re: How to clean database?
Posted: Mon Dec 21, 2020 10:06 pm
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
Re: How to clean database?
Posted: Tue Dec 22, 2020 6:29 am
by Nikolay
Any budget record are some data in BUDGETTABLE_V1 and BUDGETYEAR_V1 tables.
It may be deleted as well
budget.png
Re: How to clean database?
Posted: Tue Dec 22, 2020 6:36 am
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';
Re: How to clean database?
Posted: Tue Dec 22, 2020 7:52 am
by Nikolay
Re: How to clean database?
Posted: Tue Dec 22, 2020 10:02 am
by Cobra
OK, many thanks to all
Re: [SOLVED] How to clean database?
Posted: Tue Mar 28, 2023 8:32 pm
by skassama
Thanks for the info,
How can we reset the Transaction ID counter?
Have a nice day
Re: [SOLVED] How to clean database?
Posted: Mon Apr 10, 2023 8:21 pm
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.
Re: [SOLVED] How to clean database?
Posted: Tue Apr 11, 2023 7:44 am
by MartinArmstrong
skassama wrote: ↑Tue Mar 28, 2023 8:32 pm
How can we reset the Transaction ID counter?
Could you say
why you wish to reset it please?
The transaction ID just happens to be an integer that starts at 1 in a new database and increases as transactions are added across
all accounts. However, it's not really a "counter". Just regard it as a reference identifier.