nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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

Find more posts tagged with

Comments

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.

kaynezhang

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

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

sujaypillai

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

andy1

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