[Kmymoney-devel] Review Request 119647: Database backend does not store row count in a table anymore

[Fernando Vilas]

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://git.reviewboard.kde.org/r/119647/#review64213
-----------------------------------------------------------

Ship it!


I like the direction you are going, and that someone else is taking a look at the database code.

I would recommend making the row counts into a view so you can use a JOIN rather than the long statement you created. This has the advantage of letting the DBMS cache the answer when any index changes. That probably has more of an effect before the change to index all the tables, but may be useful anyway.

- Fernando Vilas


On Aug. 7, 2014, 2:57 p.m., Christian David wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://git.reviewboard.kde.org/r/119647/
> -----------------------------------------------------------
> 
> (Updated Aug. 7, 2014, 2:57 p.m.)
> 
> 
> Review request for KMymoney.
> 
> 
> Repository: kmymoney
> 
> 
> Description
> -------
> 
> MyMoneyStorageSql stored the row count of several tables in the table
> kmmFileInfo. But this is error-prone and bad style.
> 
> Now this information is read from the database directly. Usually a database
> caches the row count of tables anyway. Also all tables have an index so even
> a `count(*)` is really fast. The result is still cached within
> MyMoneyStorageSQL as it did before.
> 
> 
> Diffs
> -----
> 
>   kmymoney/mymoney/storage/mymoneystoragesql.h 5e148756739fcbdc3b9ffb6e11751ea035209c2b 
>   kmymoney/mymoney/storage/mymoneystoragesql.cpp 6e7a0715842da5ccb6d40f5f4a512e3433196ce6 
> 
> Diff: https://git.reviewboard.kde.org/r/119647/diff/
> 
> 
> Testing
> -------
> 
> All test were done using SQLite. But all command I use are available in all other databases as well.
> 
> 1. Tested the used SQL query in a (huge) database using sqlite:
> 
> ```SQL
> SELECT (SELECT count(*) from kmmAccounts) AS accounts, (SELECT count(*) FROM kmmCurrencies) AS currencies, (SELECT count(*) FROM kmmPayees) AS payees, (SELECT count(*) from kmmTags) AS tags, (SELECT count(*) FROM kmmTransactions) AS transactions, (SELECT count(*) FROM kmmSplits) AS splits, (SELECT count(*) FROM kmmSecurities) AS securities, (SELECT count(*) FROM kmmCurrencies) AS currencies, (SELECT count(*) FROM kmmSchedules) AS schedules, (SELECT count(*) FROM kmmPrices) AS prices, (SELECT count(*) FROM kmmKeyValuePairs) AS kvps, (SELECT count(*) FROM kmmReportConfig) AS reports, (SELECT count(*) FROM kmmBudgetConfig) AS budgets UNION ALL SELECT accounts, currencies, payees, tags, transactions, splits, securities, currencies, schedules, prices, kvps, reports, budgets FROM kmmFileInfo;
> ```
> 
> Result:
> 
> 9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= the new query )
> 9|185|6330|0|99999|199998|0|185|0|22|10|0|0 ( <= what is stored in kmmFileInfo )
> 
> 2. Saved a .kmy file and into a database (to test the INSERT routine), inspected database by hand
> 
> 3. Changed something and inspected database by hand afterward.
> 
> 
> Thanks,
> 
> Christian David
> 
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/kmymoney-devel/attachments/20140810/4cd51e90/attachment.html>