Query caching on table level

[eluser]Volder[/eluser]
Hello.

I am developing a site based on CI. And there are a lot of dynamic content which is received through database queries.

Now I would like to make it use caching.
I have checked both possible cache modules natively present in CI:
- Web page caching
- Database Caching Class

but both of them are not what I'm looking for.

The first is storing cache in dimensions of controllers - which is not helping me as same queries are done in a lot of different controllers (e.g. tag cloud, last comments panel etc). Also the parametrization there is in minutes - how long the page be kept in cached state. And for example, if the user is publishing a new topic - he won't see it immediately published, but only after the cache expire.

The second caching solution provided - database queries - is not acceptable for me because:
the cache files stored are again for controllers and functions, but I need it depending on database tables. Because otherwise It's just a headache to understand what should expire when one table is updated.

How I see the perfect scenario:

the cache is stored for queries and expires when any DML (insert, update, delete) is done on any table inside query.

So the cache files to be stored in separate folders which are generated as names of all tables inside the query:
table1+table2+...+tableN and the names of the files are for example the hash of the query text.

And when any data changed in table2, for example, all the cache files which used this table - have to be marked as expired (=deleted).

I have searched a lot and read a lot in the forum and only one solution I found more or less acceptable is the Improved Query Caching. The code files and more description are here.

What are the advantages of this for me (although I have not yet tested this):
- it gives almost what I wanted - the caching technique for tables.

What are the negative points:
- as I used almost everywhere binding queries (not active record) - I need to mark table names to cache before every query, and mark what cached tables to delete after every modification of data (as it is marked - there is no automatic query parsing done);
- the second thing - as we can not change database classes - I need to rewrite the core of the CI, so I would need to do it after every renewal of the CI version. As the code was provided almost a year ago and CodeIgniter has improved several versions from that time - probably something is already missing there.


So can anybody give me an advice of how to organize database query caching with CI? I'm sure someone has faced similar requirments.
Any help is appreciated as I am trying to understand in which direction to move now.