<commit_before>---
layout: post
title:  "Some useful Alfresco queries"
date:   2015-05-01 16:23:45
comments: true
categories:
description: I would like to share some of SQL queries for Alfresco database which could be useful.
tags: 
- alfresco
- database
---

## Why you may use SQL queries

The proper way to get information about your repo would be to create a Java or javascript webscript. To get info about documents you'll need to use [CMIS](http://docs.alfresco.com/4.2/concepts/intrans-metadata-query.html) query language in it. But sometimes more convenient way is to use SQL. For example in the project I'm working on we use [AppDynamics](https://www.appdynamics.com/) for monitoring. For one of the dashboards we would like to have a document counter and for AppDynamics it's more convenient to get a result from database rather than response from an Alfresco webscipt. So here we go!

First we need to connect to Alfresco database. You can refer to this post to do it: [Connect to alfresco database]({{ site.url }}/2015/02/13/connect-to-alfresco-db/).

## Total number of documents in repository

This query will return number of nodes which have _cm:content_ type:

{% highlight sql%}
select count(*) as cm_content_nodes
from alf_node nd, alf_qname qn, alf_namespace ns
where qn.ns_id = ns.id
  and nd.type_qname_id = qn.id
  and ns.uri = 'http://www.alfresco.org/model/content/1.0'
  and qn.local_name = 'content';
{% endhighlight sql%}

To get number of folders (which have _cm:folder_ type) replace `content` by `folder`. To see all available types run following queries:

{% highlight sql%}
select * from alf_namespace; -- namespaces
select * from alf_qname;     -- qnames
{% endhighlight sql%}

For custom types change qname and namespace.

## Document name - creator - date
<commit_msg>fix link to other post<commit_after>---
layout: post
title:  "Some useful Alfresco queries"
date:   2015-05-01 16:23:45
comments: true
categories:
description: I would like to share some of SQL queries for Alfresco database which could be useful.
tags: 
- alfresco
- database
---

## Why you may use SQL queries

The proper way to get information about your repo would be to create a Java or javascript webscript. To get info about documents you'll need to use [CMIS](http://docs.alfresco.com/4.2/concepts/intrans-metadata-query.html) query language in it. But sometimes more convenient way is to use SQL. For example in the project I'm working on we use [AppDynamics](https://www.appdynamics.com/) for monitoring. For one of the dashboards we would like to have a document counter and for AppDynamics it's more convenient to get a result from database rather than response from an Alfresco webscipt. So here we go!

First we need to connect to Alfresco database. You can refer to this post to do it: [Connect to alfresco database]({{ site.url }}/2015/02/connect-to-alfresco-db/).

## Total number of documents in repository

This query will return number of nodes which have _cm:content_ type:

{% highlight sql%}
select count(*) as cm_content_nodes
from alf_node nd, alf_qname qn, alf_namespace ns
where qn.ns_id = ns.id
  and nd.type_qname_id = qn.id
  and ns.uri = 'http://www.alfresco.org/model/content/1.0'
  and qn.local_name = 'content';
{% endhighlight sql%}

To get number of folders (which have _cm:folder_ type) replace `content` by `folder`. To see all available types run following queries:

{% highlight sql%}
select * from alf_namespace; -- namespaces
select * from alf_qname;     -- qnames
{% endhighlight sql%}

For custom types change qname and namespace.

## Document name - creator - date

{'fraction_non_alphanumeric': 0.08439402324294411, 'fraction_numerical': 0.01383508577753182, 'mean_word_length': 4.134943181818182, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '524336', 'n_tokens_mistral': 1126, 'n_tokens_neox': 1076, 'n_words': 486}