topic How to get all files and folders names and size from alfresco database using SQL in Alfresco Archive

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

anujsharma — Tue, 27 Dec 2016 18:13:15 GMT

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

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

yogeshpj — Tue, 27 Dec 2016 20:09:04 GMT

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 .

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

anujsharma — Tue, 27 Dec 2016 21:12:14 GMT

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

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

yogeshpj — Wed, 28 Dec 2016 15:21:35 GMT

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.

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

kaynezhang — Wed, 28 Dec 2016 15:56:07 GMT

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

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

anujsharma — Wed, 28 Dec 2016 16:22:07 GMT

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

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

sujaypillai — Fri, 06 Jan 2017 07:39:17 GMT

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

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

andy1 — Thu, 07 Sep 2017 09:45:33 GMT

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