Relation between Document and Database

[shashi]

Hi All,

We want to export all documents from /contentstore and metadata from alf_content_url table.

As the table table (alf_content_url) stores only path of the files with *.bin format(eg- store://2021/1/29/12/31/17dfa825-fe2f-4cc1-90c0-f6b3b287445a.bin)

We can see also files and folder from Alfressco UI under Company Home.

So, where to find folder and file relations like which files belongs to which folder in table ot confiuration file?

Thanks in Advance!

Kind regards,

Shashi

[sufo · Accepted solution]

EDIT: added correct name for the document from ALF_NODE_PROPERTIES table.

You've asked for it

select a.child_node_name p2.string_value as content_name, p1.string_value as parent_name, u.content_url 
    from 
        alf_content_url u left join alf_content_data d on u.id=d.content_url_id 
        left join alf_node_properties p on d.id=p.long_value 
        left join alf_child_assoc a on a.child_node_id=p.node_id 
        left join alf_node_properties p1 on a.parent_node_id=p1.node_id 
        left join alf_node_properties p2 on a.child_node_id=p2.node_id
    where 
        p.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='content' and n.uri='http://www.alfresco.org/model/content/1.0'
        ) 
        and  
        p1.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
        )
        and
        p2.qname_id=( 
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0' 
        );

But you will get also thumbnails, so you have to filter the results further.

Document metadata is stored in ALF_NODE_PROPERTIES table. That table also contains NODE_ID, which is used in ALF_CHILD_ASSOC table that contains parent-child relations. ALF_NODE_PROPERTIES contains ID of ALF_CONTENT_DATA row and that contains CONTENT_URL_ID and that is ID in ALF_CONTENT_URL table.

This is only for the ideal scenario. If you have multifiling (one document in more folders), other associations defined or more content properties (custom model), things can get far more complicated.

[sufo · Accepted solution]

I love to learn new things so now I know how to do recursive selects in the DB (to build path using ALF_CHILD_ASSOC table)

This SQL code works on Oracle DB and filters out thumbnails and older versions of content (takes into account only workspace://SpacesStore):

create or replace function get_path (document_node_id in number) return varchar2 as
    document_path varchar2(32767);
begin
    with pth(parent_node_id, child_node_id, parent_name) as (
        select a.parent_node_id as parent_node_id, a.child_node_id as child_node_id, p1.string_value as parent_name from
            alf_child_assoc a left join alf_node_properties p1 on a.parent_node_id=p1.node_id 
        where 
            p1.qname_id=(
                select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
                where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
            )
            and
            a.child_node_id=document_node_id
        union all
        select a1.parent_node_id, a1.child_node_id, p2.string_value from 
            pth left join alf_child_assoc a1 on pth.parent_node_id=a1.child_node_id left join alf_node_properties p2 on a1.parent_node_id=p2.node_id
        where
            p2.qname_id=(
                select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
                where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
            )
    ) select listagg(parent_name, '/') within group(order by parent_node_id) into document_path from pth;
    return document_path;
end get_path;

select get_path(a.child_node_id) as content_path, p2.string_value as content_name, u.content_url 
    from 
        alf_content_url u left join alf_content_data d on u.id=d.content_url_id 
        left join alf_node_properties p on d.id=p.long_value 
        left join alf_child_assoc a on a.child_node_id=p.node_id 
        left join alf_node_properties p1 on a.parent_node_id=p1.node_id 
        left join alf_node_properties p2 on a.child_node_id=p2.node_id
        left join alf_node n on a.child_node_id=n.id
    where 
        n.store_id=(
            select id from alf_store where protocol='workspace' and identifier='SpacesStore'
        )
        and not
        n.type_qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='thumbnail' and n.uri='http://www.alfresco.org/model/content/1.0'
        )
        and
        p.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='content' and n.uri='http://www.alfresco.org/model/content/1.0'
        ) 
        and  
        p1.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
        )
        and
        p2.qname_id=( 
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0' 
        );

PostgreSQL version:

create or replace function get_path(document_node_id in bigint) returns text as $$
declare document_path text;
begin
    with recursive pth(parent_node_id, child_node_id, parent_name) as (
        select a.parent_node_id as parent_node_id, a.child_node_id as child_node_id, p1.string_value as parent_name from
            alf_child_assoc a left join alf_node_properties p1 on a.parent_node_id=p1.node_id 
        where 
            p1.qname_id=(
                select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
                where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
            )
            and
            a.child_node_id=document_node_id
        union all
        select a1.parent_node_id, a1.child_node_id, p2.string_value from 
            pth left join alf_child_assoc a1 on pth.parent_node_id=a1.child_node_id left join alf_node_properties p2 on a1.parent_node_id=p2.node_id
        where
            p2.qname_id=(
                select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
                where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
            )
    ) select string_agg(parent_name, '/' order by parent_node_id) into document_path from pth;
	return document_path;
end
$$ language plpgsql;

select get_path(a.child_node_id) as document_path, p2.string_value as content_name, u.content_url 
    from 
        alf_content_url u left join alf_content_data d on u.id=d.content_url_id 
        left join alf_node_properties p on d.id=p.long_value 
        left join alf_child_assoc a on a.child_node_id=p.node_id 
        left join alf_node_properties p1 on a.parent_node_id=p1.node_id 
        left join alf_node_properties p2 on a.child_node_id=p2.node_id
        left join alf_node n on a.child_node_id=n.id
    where 
        n.store_id=(
            select id from alf_store where protocol='workspace' and identifier='SpacesStore'
        )
        and not
        n.type_qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='thumbnail' and n.uri='http://www.alfresco.org/model/content/1.0'
        )
        and
        p.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='content' and n.uri='http://www.alfresco.org/model/content/1.0'
        ) 
        and  
        p1.qname_id=(
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0'
        )
        and
        p2.qname_id=( 
            select q.id from alf_qname q left join alf_namespace n on (q.ns_id=n.id) 
            where q.local_name='name' and n.uri='http://www.alfresco.org/model/content/1.0' 
        );

[EddieMay]

Hi @shashi 

Ah, yes 4.2.e! I'd recommend that you label the version of your alfresco when asking questions.

This blog post on Alfresco database queries refers to 4.2.c - there might be something there that is useful? Also, might there be an export extension - here are some.

Best wishes,

[shashi]

Thank you very much EddieMay!

Its realy a good link. I will work based on info provided. I hope I will get my outcomes!

Kind regards,

Shashi