Table of Contents

ComboStrap - How to query the database

How-to

This page will show you how to query the database online.

Steps

Go to the admin page

Click on the Admin link to go to the Admin Window. ie https://yourdomain.com/?do=admin

Go to the Sqlite Interface

The SQLite interface is a webpage that permits to send statement to the SQLite database.

Sqlite Interface

To access it directly, the URL is https://yourdomain.com/?do=admin&page=sqlite

Choose the database

Choose the Combo database.

Database Combo Sqlite Interface

If you were users:

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.

Sqlite Interface Query

You can try the below query that returns statistics over words.

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
	)

Advanced: Json Support

If you want to query, the analytics json data, you need to have sqlite with json support.

This steps shows you how to validate that your SQLite database can query JSON.

select sqlite_version();
^ sqlite_version ^
| 3.28.0 |

PRAGMA compile_options; 
-- or
select * from pragma_compile_options() where compile_options = 'ENABLE_JSON1';
^ compile_options ^
| ENABLE_JSON1 |

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 json analytics column

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
	)

If you don't have the JSON extension or can't install it, you can always download the database file and query it locally for instance with dbeaver