How to retrieve content details without the API

[titusnachbauer]

Yes, I know, do not touch the database. I won't. Let me explain 🙂

I am a migration consultant and a client asked us to migrate data from an old Alfresco system. It is version Enterprise - v3.1.1 (229) from 2009. Now normally we would either crawl the frontend or use an API to access data like this, but we did not have access to both at the start of the project. So i went and read some documentation, trying to understand the inner workings of the database enough so that I can get some meaningful data out. And here is the good news: that was no problem in general. Thanks to helpful posts on this forum and pages like these: http://wiki.alfresco.com/wiki/Data_Dictionary_Guide

So last week I finally got access to the frontend and when I walked through that I found some missing data that I would like to add. There are a lot of files that have no modified date, modifier, creation date and so on, although their details page clearly shows them.

For these items ALF_NODE_PROPERTIES only has the name, title, icon and description. I would have expected the other fields to also be found here, based on what I saw in other items. Those items I found using this (admittedly creative) query:
SELECT n."assoc_id", n."node_id", Q.LOCAL_NAME AS "field", p.string_value AS "value"
FROM ALF_NODE_PROPERTIES p
INNER JOIN
    (SELECT na.source_node_id AS "node_id", na.id AS "assoc_id"
        FROM ALF_NODE_ASSOC na
        INNER JOIN ALF_QNAME q ON na.type_qname_id = q.id
        WHERE q.local_name = 'rootVersion'
        ) n
    ON (n."node_id" = p.node_id)
INNER JOIN ALF_QNAME q
    ON p.QNAME_ID = q.id)
UNION
(SELECT n."assoc_id", n."node_id", Q.LOCAL_NAME AS "field", p.string_value AS "value"
FROM ALF_NODE_PROPERTIES p
INNER JOIN
    (SELECT na.target_node_id AS "node_id", na.id AS "assoc_id"
        FROM ALF_NODE_ASSOC na
        INNER JOIN ALF_QNAME q ON na.type_qname_id = q.id
        WHERE q.local_name = 'rootVersion'
        ) n
    ON (n."node_id" = p.node_id)
INNER JOIN ALF_QNAME q
    ON p.QNAME_ID = q.id)";

Now this gives me a lot of usable information about documents, but as stated before, many items are just missing from this. There is no child node in ALF_CHILD_ASSOC and there is no other relations in ALF_NODE_ASSOC for these items. This being an enterprise system, the database is pretty extensive and it feels like I am almost there. So before I build the frontend crawler, debug all the issues with that and let it run all weekend, I would love to know where the information shown on the details pages is stored. Again, I only want to retrieve it, I won't store anything or mess up their database. Any hints will be much appreciated.

UPDATE:

Of course the ALF_NODE table contains the audit_created, audit_modifier, etc. columns, so that seems to give me the information I needed. Of course the big question now is: why do you store this in ALF_NODE_PROPERTIES for some assets? And what is the difference between the two, so what data is valid if there is a difference between ALF_NODE and ALF_NODE_PROPERTIES?

[mlagneaux]

Hi,

As you said, Auditable aspect properties (creation date, creator, modified date, modifier, …) are not manage like other properties. Other properties are stored in alf_node_properties table but auditable aspect properties are stored directly in alf_node table.

I don't why it is made like this but I confirm that if you want to get the auditable properties values of a node, you have to go in alf_node table.

I hope someone in Alfresco will let us know why auditable properties are stored in alf_node table.

[titusnachbauer]

AH, ok, that is a good one. So I guess that because you can configure your content model in the node table, you also store certain node properties there. My problem is mainly that I do see 900,000+ nodes, while there are 130,000 assets, but I cannot seem to find a connection between the nodes, other than what is in ALF_CHILD_ASSOC and ALF_NODE_ASSOC. So how are the properties related to nodes?

[mrogers]

The auditable aspect is a bit special since its tied into versioning.   In fact there's a bit of a debate whether it should be an aspect at all.    Anyway auditable values are either on the node table or properties.   Depending on whether someone has directly fiddled with what should be a system maintained property.  

Can you run the application?   Or is your system turned off?

If you can run the app then use an API.  Or export. Or something like the ftp server.   You should never try to second guess the database if you need help from the forums to do it.    Job done.    

[titusnachbauer]

The application is running, we have access to the user interface (frontend). I am not sure why the client did not give us access to the API yet.

Do you have a hint to how I can access the node browser or any API when I have the frontend URL? This is probably all basic Alfresco stuff, so forgive me for asking. It is a tough job to keep up with all the different CMS/DMS systems 😉 I did of course try to find it in the documentation, but I do not have a link to the administration console anywhere and also couldn't find any standard URL's that I could try.

[mrogers]

In 3.1 the node browser is part of "Alfresco Explorer" the "user interface".   Its only available to "admin"