|Think about Loose Coupling|
Caching DB rows transparently and with SQL searchby bliako (Prior)
|on Jul 04, 2020 at 11:13 UTC||Need Help??|
bliako has asked for the wisdom of the Perl Monks concerning the following question:
Esteemed Monks, I need some advice on DB caching,
A typical web-app, let's say using Mojolicious and DBIx::Class, has a lot of semi-static data stored in the DB. For example the list of registered users, the set of allowed tags, phone numbers of associates. This data does not change often (perhaps once a week/month) so why do I fetch it every time a user looks up another user's phone. (forget at the moment issues of privacy - all users are allowed to see all other users details). Another example is a business phone directory with 100 entries. Fairly static and very much in use. This data is not so big in size so I want to keep it in the memory of my app and serve this instead of fetching it from db each time a webpage with a dropdown menu of all phones is loaded.
So, I thought on starting up the app, create a helper and store these semi-static data (fetched once and only from DB). Then serve this and never bother the DB again. That's fine but how about providing SQL-style search for this data? Because sometimes I will get requests from the client (e.g. when it loads a user's profile page) of /user/list?id=xyz in addition to /usr/list?id=all and also /usr/list?name=*abc*. These are all implemented as SQL searches in DBIx::Class and work fine but they are not cached.
This is what I would like (and this is the 21st century and I want to have it): either tell DBIx::Class that specific table should be cached unconditionally until I say so. This transparency will be ideal for using existing code. OR, less preferred, store the static data in Perl and via a module have SQL searches on it, as if it was residing in a DB.
thanks, bw bliako
EDIT: I have asked a similar question at https://webchat.freenode.net/#mojo (which was part of a more general question)