Multiple versions point to same url

[paul_price]

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 with versioned docs?

SELECT  n.uuid ,
        u.content_url
FROM    alf_qname AS q
        INNER JOIN alf_namespace AS ns ON q.ns_id = ns.id
        INNER JOIN alf_node AS n
        INNER JOIN alf_node_properties AS p ON n.id = p.node_id ON q.id = p.qname_id
        INNER JOIN alf_content_data AS d ON p.long_value = d.id
        INNER JOIN alf_content_url AS u ON d.content_url_id = u.id
WHERE   ( q.local_name = 'content' )
        AND ( ns.uri = 'http://www.alfresco.org/model/content/1.0' )
ORDER BY u.content_url


MORE INFO:

In looking at my database, it appears that the ALF_CONTENT_DATA table maps all the versions of a doc to the same CONTENT_URL_ID.

It should be noted that this data was initially uploaded into Alfresco using the CMIS interface in Alfresco 3.4.1. I have done an "in place" upgrade to 4.1.1.3. So it is entirely possible that the problem originated in the older version of Alfresco.

Adding some additional tables to the SQL might make the problem a little easier to see.

SELECT  node.uuid AS [NodeUuid(Search)] ,
        prop.node_id AS PropNodeId ,
        contentdata.id AS CDid ,
        contentdata.content_url_id ,
        node.version AS NodeVer ,
        url.content_url AS [ContentUrl(Returned)] ,
        contentdata.version
FROM    alf_qname AS qname
        INNER JOIN alf_namespace AS namespace ON qname.ns_id = namespace.id
        INNER JOIN alf_node AS node
        INNER JOIN alf_node_properties AS prop ON node.id = prop.node_id ON qname.id = prop.qname_id
        INNER JOIN alf_content_data AS contentdata ON prop.long_value = contentdata.id
        INNER JOIN alf_content_url AS url ON contentdata.content_url_id = url.id
WHERE   ( qname.local_name = 'content' )
        AND ( namespace.uri = 'http://www.alfresco.org/model/content/1.0' )
ORDER BY url.content_url

Also, my intention for this nasty query is to build a map from alf_node.uuid to the alf_content_url.content_url. I will use this map to migrate old docs into a new system using the bulk loader. I need to add new meta data to the migrated docs that is currently stored in a separate database that uses the alf_nade.uuid as its key.

REVISED SOLVED CODE:
<code>
SELECT  node.id AS version_id ,
        nodeSeries.id AS series_id ,
        propFileName.string_value AS file_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
        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' )
        AND ( qnameLocalName.ns_id = namespaceContent.id )
</CODE>

[smicyk]

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

[paul_price]

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.