How to distinguish different nodes of type "content".

[Muffex]

Hey there.

I've searched several threads regarding SQL statements to gather information on files, folders, etc. I'm well aware that the proper way would be to use the API or CMIS.

But for my case I have to rely on SQL.

When selecting the nodes (from the alf_node table) I filter on alf_qname.local_name = 'content'.
These content nodes, however, contain (uploaded) files, wiki-pages, blog-posts, discussion-topics and other nodes.

How can I properly distinguish these different content nodes? I.e. how to only select (uploaded) files? How to only select blog-posts? etc.

kind regards

[Muffex · Accepted solution]

Well, I finally figured "something" out.

I can retrieve most of the nodes that refer to "files". It's neither well optimized nor can I guarantee it will work.

But for my needs it is currently sufficient.

SELECT n.id AS nodeId, n.uuid AS fileId, n.audit_created AS created, n.audit_modified AS last_updated, anp.string_value AS name, n.audit_creator AS creator, CONCAT('http://alfresco.bas.uni-koblenz.de/share/page/document-details?nodeRef=',store.protocol,'://',store.identifier,'/',n.uuid) AS content
  FROM alf_node AS n
    JOIN alf_qname AS q on (n.type_qname_id = q.id AND q.local_name='content')
    JOIN alf_store AS store ON (store.id = n.store_id)
    JOIN alf_node_properties AS anp on (n.id = anp.node_id)
    LEFT JOIN alf_content_data AS acd ON (anp.long_value = acd.id)
    JOIN alf_child_assoc AS aca ON (aca.child_node_id = n.id)
    JOIN alf_node AS nP ON (aca.parent_node_id = nP.id)
    JOIN alf_qname AS aqn ON (np.type_qname_id = aqn.id)
    WHERE anp.qname_id IN 
      (SELECT id FROM alf_qname
        WHERE local_name='name' 
          AND ns_id IN 
            (SELECT ns2.id FROM alf_namespace AS ns2 WHERE uri LIKE '%content%'))
      AND n.audit_creator NOT LIKE 'System'
      AND aqn.local_name not like 'archiveUser'
      AND aqn.local_name not like 'person'
      AND anp.string_value like '%.%'
      AND anp.string_value NOT LIKE '%~%~%'
      AND anp.string_value NOT LIKE 'dashboard.xml'