nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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

Find more posts tagged with

Comments

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'