How to list the names and size of all folders and files in alfresco? (Oracle 11g database)

Question

I need to fetch information from alfresco database :

• all folder name
• all file name
• size

Can someone give me the SQL query for that ?( if possible to get them in hierarchical order)

I am using oracle 11g

Thanks

Answer 1

CMIS query was proposed here.

But if you have to use SQL try this (more info):

SELECT 
  n.id as node_id,
  aq.local_name as node_type, 
  npn.string_value as node_name,
  ca.parent_node_id,
  cu.content_size,
  cu.content_url,   
  n.uuid, 
  n.audit_created
FROM alf_node as n
  left outer join alf_node_properties npn on 
     (npn.node_id=n.id and npn.actual_type_n=6 and npn.qname_id in 
       (select id from alf_qname where local_name='name'))
  left outer join alf_node_properties npc on 
     (npc.node_id=n.id and npc.actual_type_n=21 and npc.qname_id in 
       (select id from alf_qname where local_name='content'))
  left outer join alf_content_data cd on (cd.id = npc.long_value)
  left outer join alf_content_url cu on (cd.content_url_id = cu.id)
  left outer join alf_child_assoc ca on (ca.child_node_id=n.id)
  left outer join alf_qname aq on (n.type_qname_id=aq.id) 
where 
  aq.local_name in ('folder','content')

"The database schema is meant to be internal--you shouldn't hit it directly" - Jeff Potts

Answer 2

First of all ,Its not advisable to directly deal with database in alfresco, in that too when you want some basic information regarding node in alfresco.

There are Java API available in alfresco which you can use.All the APIs are available in below link.

http://docs.alfresco.com/5.1/concepts/dev-services.html?m=2

For your requirement you can use nodeService of alfresco.