---json
{
    "h1": "ComboStrap Analytics - How to query the analytics database",
    "page_id": "db5uh8pkizcn68hf8nyht"
}
---
====== ComboStrap - How to query the database ======


===== How-to =====
This page will show you how to query the [[database|database]] online.

===== Steps =====


==== Go to the admin page ====

Click on the ''Admin'' link to go to the [[doku>admin_window|Admin Window]]. ie ''https://yourdomain.com/?do=admin''

<tabs enclosed-tabs>
  <panel selected>
     <label>Strap Template</label>
With the [[release:deprecated:strap|ComboStrap Template]]:

{{docs:analytics:admin_combo.png?150|}} 
  </panel>
  <panel>
    <label>Dokuwiki Template</label>

With the [[doku>template:dokuwiki|Dokuwiki Template]]

{{::dokuwiki_admin_window_default_template.png|}}
    </panel>
</tabs>
==== Go to the Sqlite Interface ====

The [[doku>plugin:sqlite#admin_interface|SQLite interface]] is a webpage that permits to send statement to the ''SQLite'' database.

{{docs:analytics:sqlite_interface.png?150|}}


<note tip>
To access it directly, the URL is ''%%https://yourdomain.com/?do=admin&page=sqlite%%''
</note>

==== Choose the database ====

Choose the ''Combo'' [[database|database]].

{{docs:analytics:database_combo_sqlite_interface.png|}}

<note warning>
If you were users:
  * of the 404Manager, the database may be named ''404manager''
  * of the webcomponent plugin, the database may be named ''webcomponent''
</note>


The URL to access your database will look like the following URL

 ''%%https://yourdomain.com/?do=admin&page=sqlite&db=combo&version=sqlite3%%''



==== Enter your query and submit ====


Enter the analytical query and submit.

{{docs:analytics:sqlite_interface_query.png?500|}}



You can try the below query that returns statistics over ''words''.
<code sql>
select
	count(*) as page_count,
	round(word_count) as word_avg,
	sum(word_count) as word_count,
	max(word_count) as word_max,
	min(word_count) as word_min
from
	(
	select
		id,
		word_count
	from
		pages
	)
</code>


==== Advanced: Json Support  ====
If you want to query, the [[data|analytics json data]], you need to have sqlite with [[https://www.sqlite.org/json1.html|json support]].

This steps shows you how to validate that your SQLite database can query JSON.
  * The sqlite database version should then be greater or equal to SQLite 3.9.0 (2015-10-14).
<code sql>
select sqlite_version();
</code>
<file>
^ sqlite_version ^
| 3.28.0 |
</file>
   * The json module should be present when executing the below SQL.
<code sql>
PRAGMA compile_options; 
-- or
select * from pragma_compile_options() where compile_options = 'ENABLE_JSON1';
</code>
<file>
^ compile_options ^
| ENABLE_JSON1 |
</file>

If you have Json enable, you can extract all information in the analytics file.

For instance, the above query can be translated to this query with the [[data|json analytics column]]
<code sql>
select
	count(*) as page_count,
	round(word_count) as word_avg,
	sum(word_count) as word_count,
	max(word_count) as word_max,
	min(word_count) as word_min
from
	(
	select
		id,
		json_extract(analytics , '$.statistics.words') as word_count
	from
		pages
	)
</code>

<note tip>
If you don't have the [[https://www.sqlite.org/json1.html|JSON extension]] or can't install it, you can always download the [[database|database file]] and query it locally for instance with [[https://dbeaver.io/|dbeaver]]
</note>