Here is an extended example on how to use the view from part 1. Note i’m used extend output \x which will display the tables vertically instead of horizontally on the output so there is not a scroll bar side to side.

\x on
SELECT
	view_recurse_alf_folders.qname_localname as folder_name,
	alf_child_assoc.qname_localname as file_name,
	alf_mimetype.mimetype_str as mimetype,
	alf_content_url.content_size,
	alf_node.audit_creator,
	alf_node.audit_created,
	alf_node.audit_modifier,
	alf_node.audit_modified,
	view_recurse_alf_folders.local_name as folder_type,
	alf_qname.local_name as file_type,
	alf_child_assoc.parent_node_id,
	alf_child_assoc.child_node_id
FROM
	alf_child_assoc,
	alf_qname,
	alf_node,
	alf_content_data,
	alf_content_url,
	alf_node_properties,
	alf_mimetype,
	view_recurse_alf_folders
WHERE
	alf_content_url.id = alf_content_data.content_url_id
	and alf_node_properties.long_value = alf_content_data.id
	and alf_node.id = alf_node_properties.node_id
	and alf_mimetype.id = alf_content_data.content_mimetype_id
	and alf_node.type_qname_id = alf_qname.id
	and alf_node.id = alf_child_assoc.child_node_id
	and view_recurse_alf_folders.child_node_id  = alf_child_assoc.parent_node_id
	and alf_qname.local_name = 'content' 
	and view_recurse_alf_folders.qname_localname like 'company_home/sites/samples/documentLibrary/generic%'
ORDER BY view_recurse_alf_folders.qname_localname;

Here it is in extended output. As you can see the query returns…

  • The Folder Path
  • The File Name
  • The File’s Mime Time like Excel, JPEG, etc..
  • The files size (in bytes), if you need it in MB just /1024/1024, and consider rounding if you want clean output.
  • Creator/Modifier with dates incase you need to group by user or timeframe
  • I also included the types of the child and parent objects incase you wanted to search for system objects, and their id numbers to easily tie to other database tables.

Basically this is most of the code necessary to perform a majority of the types of reports necessary out of the file system.

The one major shortcoming of my code (which will be resolved soon), is it does not account for versions, so all these numbers are for the current version, ignoring previous version of files.

The addition of version information to this will most likely be part 3.

Sample output in extended format for easy viewing that can obviously be aggregated with count() or sum().

-[ RECORD 1 ]--+----------------------------------------------------------------
folder_name    | company_home/sites/samples/documentLibrary/generic
file_name      | Excel Workbook.xlsx
mimetype       | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
content_size   | 12989
audit_creator  | admin
audit_created  | 2016-12-21T02:38:13.281Z
audit_modifier | admin
audit_modified | 2016-12-21T02:38:13.281Z
folder_type    | folder
file_type      | content
parent_node_id | 892
child_node_id  | 923
-[ RECORD 2 ]--+----------------------------------------------------------------
folder_name    | company_home/sites/samples/documentLibrary/generic
file_name      | Chrysanthemum.jpg
mimetype       | image/jpeg
content_size   | 879394
audit_creator  | admin
audit_created  | 2016-12-21T02:38:13.000Z
audit_modifier | admin
audit_modified | 2016-12-21T02:38:13.000Z
folder_type    | folder
file_type      | content
parent_node_id | 892
child_node_id  | 917

Developed on Alfresco 5.1.1 with PostgreSQL 9.4.4