created_at updated_at to help with cache invalidation

[petesiss]

Do you think there is any scope for adding created_at and updated_at to the schema to help with cache invalidation?

Im not sure whether this is something to go in here, jackalope, or even cmf, but one way or another it would be immensely helpful to be able to find out when a specific subset of nodes was last updated.

Any thoughts? I was going to fall back on SHOW TABLE STATUS LIKE 'tablename' to get the last update time as a crude solution, but for InnoDB unfortunately the update time is always Null. There is a ticket open for it to be added but it has been open since 2005...

[dbu]

you mean create and update timestamps for node modification? sounds reasonable.

probably the best would be to leverage http://www.day.com/specs/jcr/2.0/3_Repository_Model.html#3.7.11.7%20mix:created and mix:lastModified (oh, and maybe we need something in jackalope doctrine-dbal to support those mixins correctly)

i would be ok to map those fields in our standard documents and declare they have these mixins.

what exactly do you mean by subset? this is not working in the sense of the linux file system tree, its always on individual nodes, changing a child node does not change the lastModified of the parent (unless you rename the child, that is)

[petesiss]

Hmm... well I did mean create and update timestamps, but was thinking more on the transport level, for sql db row modification rather than for a constructed node.

So determine what is updated without spending time fully building all the objects in jackalope in order to check, as once you've built them to check you've already done all the work.

How about implementing the mixins you linked to, which seem cool, but also in the transport we could pull them out in to separate fields as well as having them buried in the xml?

By subset, I meant for example it would be great to be able to query MAX(updated_at) for all rows with a path LIKE 'this/current/path%' and so know whether we can return a 302 for the child menu fragment or whether we have to let KnpMenu do its thing.

Doing that in sql would be relatively fast. Trying to do it by constructing each node first to get the relevant property would for that use case be fairly pointless.

But then in any case are we allowed to add additional utility methods like this to the transport that might not be part of supporting the spec?

Just thinking out loud really.

[dbu]

thinking out loud is good for brainstorming :-)

i think having those columns in the db makes sense. then we could pull them into the xml if the respective mixins are set (i would rather not duplicate the info into the xml blob). having a cache invalidation concept that allows for implementation specific helpers that can take shortcuts sounds like a good idea to me (the whole cache thing is conceptually messy but meant to speed up things).

maybe @lsmith77 can point to where the right hooks would be for cache invalidation resp. re-validation checks?

i would be totally fine with a PR that just adds the columns and the behaviour in transport to update them (unless that works automatically - have some recollections mysql does stuff but some of it not as you would expect it to...) and leave the support for the mixins as a later TODO. (we just would have to make sure that lastModified follows the semantics of the mixin lastModified)

[lsmith77]

hmm not sure .. there is also the etag support in jcr

[petesiss]

Sounds good. I'll do a PR then to add the fields.

I think we should populate them ourselves rather than leave the db to do it. It is easy enough with MySQL but postgres and sqlite seem completely different, needing to use a trigger and stored proc.

Thats only based on some quick research though. I have little experience of using postgres or sqlite aside from running the test suite. So let me know if Im wrong on that.

Also potentially the timestamp to be used might be set elsewhere as a node property when the node is first instantiated, and so we would need to set those specific values, not whatever the backend thinks the time is.

I am thinking if we combine this with the idea of moving the cache up to Jackalope, then we could potentially have a system where a Jackalope Cache can cache the node objects themselves, and the cache can do quick checks to the transport to validate the cached nodes by just comparing the last update (or etag?) before it allows them to be sent to the object manager.

We could also potentially improve cache invalidation if the transport returned uuids or paths of all modified nodes after each sync / update.

[dbu]

probably etag is really the thing to use, as its specifically designed for cache invalidation handling. though updated_at could be nice too if for example you want to i.e. build a cronjob that invalidates cache entries by looking at updated nodes rather than checking each cached data bit. i am no caching expert - if there are none that tell us how to do, i suggest you try something and do a PR and we see. could get a rather large PR though, affecting core jackalope and the transports. probably we want to add something like an EtagTransportInterface or CachingTransportInterface to make the features optional for the transport.

[lsmith77]

btw I just did a first few steps towards SonataCacheBundle integration with PHPCR: https://github.com/sonata-project/SonataCacheBundle/pull/24

here as well we could use etag support

[petesiss]

I did have a read through the spec page dbu linked, and it seems the eTag is specifically for binary props:

The mix:etag mixin type defines a standardized identity validator for BINARY properties

Does that mean we shouldn't use it in the way we're discussing here? Or does it not matter?

I'll have a look at SonataCacheBundle. I've made a start on a cache for Jackalope, implemented in a simlar way to the DBAL cache. Im keen to see what impact not recreating the nodes has on performance.

[lsmith77]

SonataCacheBundle can help with providing different kinds of cache backends (esi, ssi, apc, memcache, js async loading etc). Its fairly well integrated into SonataBlockBundle and it seems to be possible to make it work with SymfonyCmfBlockBundle fairly easily. The only thing we would have to do there is implement the getCacheKeys method as well as setting cache headers on responses generated from the Block services based on the Block configuration.

Now I am not sure if @rande has also integrated this with other Bundles, like KnpMenuBundle. But it should be possible to do this without too much effort.

The other bit it does is help with invalidation, through a set of listeners. This part I did implement, but I didnt test it yet, also because I dont understand how it works exactly just yet. But afaik the idea is that the cache keys contain the id's of nodes and via the listeners writes can trigger automatic invalidation.

[rande]

There is not no need to cache the menu generation directly - of course I am considering that the menu is generating inside a template handled by a block service.

@lsmith77 I need to fix a bug in the CacheBundle, the Recorder stack is wrong.

[dbu]

regarding mix:lastModified, be aware that its up to the implementation to decide if this is managed by the implementation or in user land. jackrabbit decides to leave it to the user

[dbu]

is this still relevant? i tend to close it.

[lsmith77]

well there are two topics here .. one is mix:lastModified and the other is improving the invalidation logic in the CachedClient.

[dbu]

mix:lastModified is now handled, there is an option to jackalope whether the date should be updated on every change automatically.