SQL Statements Log as implemented in version 4.0.0

back

The "sql_statements_log" table

This new table will be automatically created in any new DB primed using libspatialite version 4.0.0, and the intended scope is supporting the new SQL Log facility.

libspatialite version 4.0.0 now includes two APIs directly supporting the SQL Log: gaiaInsertIntoSqlLog() and gaiaUpdateSqlLog().
Both spatialite_gui and spatialite CLI tools now fully support the SQL Log facility; and rather obviously any third party tool is welcome in exploiting this cool new feature.

The following example explains how all this practically works.

id	time_start	time_end	user_agent	sql_statement	success	error_cause
18	2012-09-04T22:26:03.545Z	2012-09-04T22:26:03.692Z	spatialite CLI	SELECT r.nome_reg, p.nome_pro, c.nome_com FROM com2011 AS c JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro) JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg) WHERE c.cod_reg = 10 ORDER BY c.nome_pro, c.nome_com;	0	no such column: c.nome_pro
19	2012-09-04T22:26:13.562Z	2012-09-04T22:26:13.737Z	spatialite CLI	SELECT r.nome_reg, p.nome_pro, c.nome_com FROM com2011 AS c JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro) JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg) WHERE c.cod_reg = 10 ORDER BY p.nome_pro, c.nome_com;	1	success
20	2012-09-04T22:27:03.199Z	2012-09-04T22:27:03.370Z	spatialite_gui	SELECT r.nome_reg, p.nome_pro, c.nome_com FROM com2011 AS c JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro) JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg) WHERE c.cod_reg = 9 ORDER BY c.nome_com DESC	1	success

• the id column is the event ID (Primary Key, uniquely identifying each single SQL Statement into the Log).
• the time_start and time_end columns contaits the timestamps measured immediately before and after executing the SQL statement itself.
• the user_agent column identify the tool being used to executed the SQL statement.
• the sql_statement column contains the SQL statement body.
• the success column (boolean) tells if the statement execution was successful or not.
• the error_cause will contain the error message eventually returned by SQLite.

You can learn more about the SQL Log as actually implemented by the spatialite_gui and spatialite CLI tools by reading this Wiki page

Strictly related (and really useful) SQL Query

In order to directly get the execution timings (expressed in milliseconds) you simply have to use and SQL query like the following one:

SELECT id, JulianDay(time_end) - JulianDay(time_start) AS millis, sql_statement FROM sql_statements_log WHERE success = 1;

---

back