Multiple versions point to same url

paul_price — Thu, 14 Feb 2013 17:59:07 GMT

I found the following SQL somewhere on this site several months ago. It works great for docs that have a single version. However, if there are multiple revisions of a doc, all versions of the doc point to the same content_url. Anyone want to take a shot at improving the SQL so that it will also work

Re: Multiple versions point to same url

smicyk — Thu, 21 Feb 2013 14:21:14 GMT

Hi Paul,

as far as remember if you don't update content on object in each version, system point to the same location (this is the same file).

Best Regards

Re: Multiple versions point to same url

paul_price — Tue, 12 Mar 2013 16:51:09 GMT

After a lot of head scratching, I realized that my external database with NODE IDs just contained the series id, not the version id (my bad). So I had to build a more complex query to get a good mapping to each version of a file. Here is what I ended up with. It's even more convoluted than the first (sorry).


SELECT  node.id AS version_id ,
        nodeSeries.id AS series_id ,
        propFileName.string_value AS name ,
        propVersion.string_value AS version ,
        url.content_url ,
        node.uuid AS version_uuid ,
        nodeSeries.uuid AS series_uuid
FROM    alf_node AS node
        INNER JOIN alf_node_properties AS propSeries ON node.id = propSeries.node_id
        INNER JOIN alf_node_properties AS propVersion ON node.id = propVersion.node_id
        INNER JOIN alf_node_properties AS propFileName ON node.id = propFileName.node_id
        INNER JOIN alf_node_properties AS propContent ON node.id = propContent.node_id
        INNER JOIN alf_qname AS qnameFrozen ON qnameFrozen.id = propSeries.qname_id
        INNER JOIN alf_qname AS qnameVerLabel ON qnameVerLabel.id = propVersion.qname_id
        INNER JOIN alf_qname AS qnameLocalName ON qnameLocalName.id = propFileName.qname_id
        INNER JOIN alf_qname AS qnameContent ON qnameContent.id = propContent.qname_id
                                                AND qnameLocalName.ns_id = qnameContent.id
        INNER JOIN alf_namespace AS namespaceVersion ON qnameVerLabel.ns_id = namespaceVersion.id
        INNER JOIN alf_namespace AS namespaceContent ON qnameContent.ns_id = namespaceContent.id
        INNER JOIN alf_content_data AS ContentData ON propContent.long_value = ContentData.id
        INNER JOIN alf_content_url AS url ON ContentData.content_url_id = url.id
        INNER JOIN alf_node AS nodeSeries ON propSeries.long_value = nodeSeries.id
WHERE   ( qnameFrozen.local_name = 'frozenNodeDbId' )
        AND ( qnameVerLabel.local_name = 'versionLabel' )
        AND ( qnameLocalName.local_name = 'name' )
        AND ( qnameContent.local_name = 'content' )
        AND ( namespaceContent.uri = 'http://www.alfresco.org/model/content/1.0' )
        AND ( namespaceVersion.uri = 'http://www.alfresco.org/model/versionstore/2.0' )
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

From the result set I can now map a nodi id + version number to a file location (URL).

I'd like to thank the folks at Zia Consulting (Boulder, CO) for their assistance on this one.

topic Re: Multiple versions point to same url in Alfresco Archive

Multiple versions point to same url

Re: Multiple versions point to same url

Re: Multiple versions point to same url