1

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

user3331349
  • 49
  • 1
  • 7

2 Answers2

2

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

Community
  • 1
  • 1
kinjelom
  • 6,105
  • 3
  • 35
  • 61
1

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.

Krutik Jayswal
  • 3,165
  • 1
  • 15
  • 38
  • Hi Krutik , Thanks for your prompt reply. I have never worked on java api for alfresco.Can you suggest some examples to get started. And is it impossible to get the same data using 'SELECT' database query ? – user3331349 Dec 27 '16 at 19:14
  • 1
    You should try first implementing javascript webscripts then try implementing java baked webscript. In java baked webscript you will be able to use Java api. – Krutik Jayswal Dec 27 '16 at 19:48
  • Krutik..... Can you please help me in designing a sql query for the same – user3331349 Dec 29 '16 at 18:31