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.
[SOLVED] How to clean database?
Moderator: Renato
-
- New User
- Posts: 14
- Joined: Thu Oct 29, 2015 8:32 am
- Are you a spam bot?: No
[SOLVED] How to clean database?
Last edited by Cobra on Tue Dec 22, 2020 10:02 am, edited 1 time in total.
-
- 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?
Delete all transactions in current view is only options available for now.
Repeat it for every account.
Repeat it for every account.
-
- New User
- Posts: 14
- Joined: Thu Oct 29, 2015 8:32 am
- Are you a spam bot?: No
Re: How to clean database?
I did it and I moved all recurring operations to 2021, but then why do I have a budget for 2019?
-
- MVP User
- Posts: 682
- Joined: Mon Apr 25, 2011 7:36 pm
- Are you a spam bot?: No
- Location: near Zurich
Re: How to clean database?
I would use the DB Browser to delete the unwanted tables. But please be careful not to destroy the DB structure.
http://sqlitebrowser.org
http://sqlitebrowser.org
Renato Forum Administrator
-
- 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?
Any budget record are some data in BUDGETTABLE_V1 and BUDGETYEAR_V1 tables.
It may be deleted as well
It may be deleted as well
You do not have the required permissions to view the files attached to this post.
-
- 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?
There are Tools - Database - Database Debug menu item.
There may be executed SQL script like this (save it to a file with .mmdbg extension):
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.
-
- 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?
https://github.com/moneymanagerex/money ... ssues/2760
PS this report may be helpful for know account balances for any date
https://github.com/moneymanagerex/gener ... ntBalances
PS this report may be helpful for know account balances for any date
https://github.com/moneymanagerex/gener ... ntBalances
-
- New User
- Posts: 14
- Joined: Thu Oct 29, 2015 8:32 am
- Are you a spam bot?: No
Re: How to clean database?
OK, many thanks to all
-
- New User
- Posts: 2
- Joined: Tue Mar 28, 2023 3:53 pm
- Are you a spam bot?: No
Re: [SOLVED] How to clean database?
Thanks for the info,
How can we reset the Transaction ID counter?
Have a nice day
How can we reset the Transaction ID counter?
Have a nice day
-
- Developer
- Posts: 40
- Joined: Tue Nov 29, 2022 10:31 pm
- Are you a spam bot?: No
Re: [SOLVED] How to clean database?
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.
-
- MVP User
- Posts: 300
- Joined: Tue Mar 02, 2021 10:24 am
- Are you a spam bot?: No
Re: [SOLVED] How to clean database?
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.