SQL Log
Not logged in

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
182012-09-04T22:26:03.545Z 2012-09-04T22:26:03.692Zspatialite 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;
0no such column: c.nome_pro
192012-09-04T22:26:13.562Z 2012-09-04T22:26:13.737Zspatialite 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;
1success
202012-09-04T22:27:03.199Z 2012-09-04T22:27:03.370Zspatialite_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
1success


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