How to list information about all Alfresco's files (Postgres SQL)?

Question

A file node representation in Alfresco persistent layer:

content is stored in ALFRESCO_HOME\alf_data\contentstore\ folder
metadata are stored in relational database (default: PostgreSQL)
information for text searching (Lucene) are stored in Solr's document database

Which Postgres tables are used to keep metadata of new uploaded file? How to list information about all Alfresco's files (Postgres SQL)?

The database schema is meant to be internal--you shouldn't hit it directly. — Jeff Potts
– Jeff Potts, Commented Dec 14, 2016 at 22:50
I know, but I need to export the list of ten millions Alfresco's files (name, path, size) to make sure that all files have been imported. — kinjelom
– kinjelom, Commented Dec 14, 2016 at 23:47

kinjelom · Accepted Answer · 2016-12-15 00:06:13Z

3

The tables that keep files metadata:

Listing information about all Alfresco's files - Postgres SQL:

SELECT 
  n.id, 
  npn.string_value as filename,
  cu.content_size,
  cu.content_url,   
  n.uuid, 
  n.audit_created
FROM alf_node as n
  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'))
  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'))
  join alf_content_data cd on (cd.id = npc.long_value)
  join alf_content_url cu on (cd.content_url_id = cu.id)
where 
  n.type_qname_id in (
    select id from alf_qname where local_name='content'
  )

edited Dec 15, 2016 at 0:06

answered Dec 14, 2016 at 23:54

kinjelom

6,5083 gold badges40 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to list information about all Alfresco's files (Postgres SQL)?

1 Answer 1

The tables that keep files metadata:

Listing information about all Alfresco's files - Postgres SQL:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The tables that keep files metadata:

Listing information about all Alfresco's files - Postgres SQL:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related