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
-
stackoverflow.com/a/41154244/1679903kinjelom– kinjelom2016-12-29 11:07:41 +00:00Commented Dec 29, 2016 at 11:07
-
@imagine - by column How to identify which is folder and which is file................. And what column defines their association?user3331349– user33313492016-12-29 18:19:14 +00:00Commented Dec 29, 2016 at 18:19
Add a comment
|
2 Answers
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
Comments
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.
3 Comments
user3331349
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 ?
Krutik Jayswal
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.
user3331349
Krutik..... Can you please help me in designing a sql query for the same