topic SQL statement to get list of document names in Alfresco Archive

SQL statement to get list of document names

somersst — Fri, 16 Apr 2010 20:36:59 GMT

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 welco

Re: SQL statement to get list of document names

hsohaib — Fri, 16 Apr 2010 22:36:14 GMT

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
‍‍‍‍‍

Re: SQL statement to get list of document names

somersst — Sat, 17 Apr 2010 06:44:55 GMT

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

Re: SQL statement to get list of document names

hsohaib — Sat, 17 Apr 2010 08:56:37 GMT

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.

Re: SQL statement to get list of document names

somersst — Sat, 17 Apr 2010 09:26:08 GMT

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

Re: SQL statement to get list of document names

hsohaib — Sat, 17 Apr 2010 09:41:22 GMT

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.

Re: SQL statement to get list of document names

somersst — Sat, 17 Apr 2010 12:26:19 GMT

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.

Re: SQL statement to get list of document names

eclose0923 — Thu, 29 Apr 2010 16:16:51 GMT

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'