How to get all files and folders names and size from alfresco database using SQL

[anujsharma]

Hi,

I need a SQL query to list all folder and file names with their size .

Can we do it using alfresco database or using opencmis workbench 

Thanks

[yogeshpj]

Yes it is possible using both database and CMIS query langues.

For database approach : you can probably play with following alfresco database tables and make query what you want.

alf_node_properties , alf_qname, alf_namespace, alf_content_url ,alf_content_data .

For CMIS query,  please refer the link  .

[anujsharma]

Thanks for such valuable info

If i take database approach :

Can you pls tell which table contains the names of the sites in the database? 

If i use apache cmis workbench:

How do i set the max hit to unlimited in the sql query editor....it shows only 100 results for a sql query

[yogeshpj]

• Can you pls tell which table contains the names of the sites in the database? 
  • Site name is also cm:name property on site node. So value should reside under alf_node_properties table.
• How do i set the max hit to unlimited in the sql query editor....it shows only 100 results for a sql query
  • First question is why do you want to get unlimited result ?
  • Anyway I am not sure from editor. But if you are using API, then probably you should have option to increase limit.

[anujsharma]

I have read on alfresco documentation that this can be achieved using rest api
But i don't know how to use it in alfresco.

If there is a tutorial available or sample code available, please give me the link.


Thanks

[kaynezhang]

You can try to search solr index directly,It could have better performance.

[sujaypillai]

Please refer this link where Morgan explains some useful DB queries. You may modify those as per your requirement.

[andy1]

Hi

This aggregation can be done using the public search API in 5.2.1 and later.

This is most likely over kill :-), but you can get the stats for all content that matches any query.

This will only include content you are allowed to see.

{
  "query": {
    "query": "name:*"
  },
    "stats": [
        {
            "field": "content.size",
              "label": "myStat",
            "min": true,
            "max": true,
            "stddev": true,
            "missing": true,
            "sum": true,
            "count": true,
            "sumOfSquares": true,
            "percentiles": ["1","12.5","25","50","75","99"],
            "distinctValues": true,
            "countDistinct": true,
            "cardinality": true,
            "cardinalityAccuracy": 0.1
      }
      ]
}

You can nest stats in other breakdowns - for example by site ....

{
   "query": {
      "query": "name:*"
   },
   "facetFields": {
      "facets": [
         {"field": "SITE", "label": "site"}
      ]
    },
    "stats": [
        {
            "field": "content.size",
              "label": "size",
            "min": true,
            "max": true,
            "stddev": true,
            "missing": true,
            "sum": true,
            "countValues": true,
            "sumOfSquares": true,
            "percentiles": ["1","12.5","25","50","75","99"],
            "distinctValues": false,
            "countDistinct": true,
            "cardinality": true,
            "cardinalityAccuracy": 0.1
      }
      ],
    "ranges":[
                {
                    "field": "created",
                    "start": "NOW/YEAR-5YEARS",
                    "end": "NOW/YEAR+1YEAR",
                    "gap": "+1YEAR",
                    "label": "created"
                }],
    "pivots" : [
        {
            "key": "site",
            "pivots": [
                {
                    "key": "size"
                }
                ]
        }
        ]
}

I need to get back to my blogging backlog!

Andy