SQL statement to get list of document names

[somersst]

I'm trying to create a SQL-statement which gives me the following information :
Filename(as uploaded)   Content-URL-Filename (bin as stored by Alfresco)

I know I need to join tables like alf_content_url & ald_node_properties, but I don't find the right key to link them.

Any help would be most welcome

[hsohaib]

they can be joined using  :

WHERE Statement

alf_locale.id=alf_node_properties.locale_id AND
alf_content_data.content_locale_id = alf_locale.id AND
alf_content_url.id = alf_content_data.content_url_id
‍‍‍‍‍

[somersst]

Hi,

already many thanks for your response. I think that the result is not OK. I created the following SQL :
SELECT alf_content_url.content_url,  alf_node_properties.string_value FROM alf_content_url, alf_content_data, alf_locale, alf_node_properties
where
alf_locale.id=alf_node_properties.locale_id AND
alf_content_data.content_locale_id = alf_locale.id AND
alf_content_url.id = alf_content_data.content_url_id


This results into the following :
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Loonbrieven Cegeka 2008'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', ''
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Oefeningen eerste semester 5de leerjaar van Frans'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Oefeningen Jeff Frans 5de leerjaar'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', ''

As you can see there are different documents, but they have the same url.

Greetings,

Stefaan Somers

[hsohaib]

This results into the following :
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Loonbrieven Cegeka 2008'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', ''
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Oefeningen eerste semester 5de leerjaar van Frans'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', 'Oefeningen Jeff Frans 5de leerjaar'
'store://2010/4/16/14/3/7ef9c727-0b58-4c54-8e46-24f5293605df.bin', ''

As you can see there are different documents, but they have the same url.

Greetings,

Stefaan Somers

the query is correct, the two table are joined correctly, as for the results I guess that's not unusual, its normal for multiple documents to belong to the same store, meaning there is no direct url to the document, it must be extracted from the .bin.
"alf_content_url" does contain the urls of stores, not document, (you could say the .bin in this case is the same as a .zip, contains multiple files) .

To clearify more, when i execute this query :

select count(*) from alf_content_url;
‍‍‍

the result is less than  60, but i have over 2000 documents in alfresco, meaning it couldn't possibly contain theirs urls.

[somersst]

Thanks again for answering.
So what you mean is that there is no one-to-one relation between the BIN-file and the file you uploaded

[hsohaib]

Thanks again for answering.
So what you mean is that there is no one-to-one relation between the BIN-file and the file you uploaded

yeh, it's a one-to-many, if you need the download link you can construct like this :

http://domain:port/alfresco/download/attach/workspace/SpacesStore/0000-0000-0000-0000/myfile.pdf
‍‍‍

replace 0000-0000-0000-0000 by the noderef.

[somersst]

The strange thing is the fact that I could open the bin-file when I uploaded a document.
For example I upload test.doc
I check on my computer which bin file was last uploaded.  I can open this bin file with Word without any problem

The reason why I want this kind of list, is that when my alfresco is corrupt, Istill can get at the BIN-files for opening them. But then I need to know which bin-file is which document.

[eclose0923]

This will do it for you 🙂

select    docid.string_value, url.content_url
        from
            alf_node_properties np           
            inner join alf_content_data  acd on np.long_value = acd.id
            inner join alf_content_url url on acd.content_url_id=url.id
            INNER JOIN alf_node_properties docid on docid.node_id=np.node_id
        where
            np.actual_type_n = 3 and    np.persisted_type_n = 3 and docid.string_value='509a52de-8606-40dc-93a9-b770bc9db62c'