AnsweredAssumed Answered

Best Practice for cross-querying Alfresco editorial content

Question asked by cyril on Nov 21, 2007
I've a architecture/design question I'd like guidance/recommendation for:
As our Alfresco-based project progresses we start running into the issues cross-querying WCMS editorial content with decoration data in our RDBMS, e.g. "Top rated article about France".
Articles are XML documents in Alfresco repository which xs:anyURI links to France, which is another document of type Destination.
User ratings are managed in our RDBMS keyed by document path.
This type of query spans over the 2 data stores with 2 different query languages and is a tough problem to solve…
Is there any best practices on how to best handle this type of requirement?

We thought of ETLing metadata extractor properties from the Alfresco runtime schema into our own database for querying purposes. We found the table avm_node_properties_new to be interesting as it seems to have a mapping between each document (node_id) and metadata-extractor properties from our namespace. The qname column is not indexed + we'd need to normalize the serializable_value columns, so we thought we could ETL this table into one of ours to lookup node_ids by qname/value pairs.
We have a couple of issues though going down that path:
1) is node_id reliable to track documents persistently? I.e. does it survive versioning? Should we use the property dbid instead?
2) how can we lookup a document path by node_id/dbid?

I'd appreciate any recommendation on how this topic.
Thank you,