Wiki page
[SQL Log] by
sandro
2014-08-27 12:34:43.
D 2014-08-27T12:34:43.536
L SQL\sLog
P d585e9391d2072ed843a5a38b4b49cea3d8126a8
U sandro
W 4050
<h2>SQL Statements Log as implemented in version 4.0.0</h2>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0-doc">main page</a>
<h3>The "<i>sql_statements_log</i>" table</h3>
This new table will be automatically created in any new DB primed using <i>libspatialite</i> version 4.0.0, and the intended scope is supporting the new <b>SQL Log</b> facility.<br><br>
<i>libspatialite</i> version 4.0.0 now includes two APIs directly supporting the SQL Log: <i><b>gaiaInsertIntoSqlLog()</b></i> and <i><b>gaiaUpdateSqlLog()</b></i>.<br>
Both <b>spatialite_gui</b> and <b>spatialite CLI</b> tools now fully support the SQL Log facility; and rather obviously any third party tool is welcome in exploiting this cool new feature.<br><br>
The following example explains how all this practically works.<br><br>
<table width="100%" bgcolor="#f4f4f4" border="1" cellspacing="4" cellpadding="4"><tr>
<td bgcolor="#e8e8e8" align="center"><b>id</b></td>
<td bgcolor="#e8e8e8" align="center"><b>time_start</b></td>
<td bgcolor="#e8e8e8" align="center"><b>time_end</b></td>
<td bgcolor="#e8e8e8" align="center"><b>user_agent</b></td>
<td bgcolor="#e8e8e8" align="center"><b>sql_statement</b></td>
<td bgcolor="#e8e8e8" align="center"><b>success</b></td>
<td bgcolor="#e8e8e8" align="center"><b>error_cause</b></td></tr>
<tr><td align="right">18</td><td>2012-09-04T22:26:03.545Z</td> <td>2012-09-04T22:26:03.692Z</td><td>spatialite CLI</td><td>
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 10<br>
ORDER BY c.nome_pro, c.nome_com;</td><td align="right">0</td><td>no such column: c.nome_pro</td></tr>
<tr><td align="right">19</td><td>2012-09-04T22:26:13.562Z</td> <td>2012-09-04T22:26:13.737Z</td><td>spatialite CLI</td><td>
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 10<br>
ORDER BY p.nome_pro, c.nome_com;</td><td align="right">1</td><td>success</td></tr>
<tr><td align="right">20</td><td>2012-09-04T22:27:03.199Z</td> <td>2012-09-04T22:27:03.370Z</td><td>spatialite_gui</td><td>
SELECT r.nome_reg, p.nome_pro, c.nome_com<br>
FROM com2011 AS c<br>
JOIN prov2011 AS p ON (c.cod_pro = p.cod_pro)<br>
JOIN reg2011 AS r ON (c.cod_reg = r.cod_reg)<br>
WHERE c.cod_reg = 9<br>
ORDER BY c.nome_com DESC</td><td align="right">1</td><td>success</td></tr>
</table><br>
<ul>
<li>the <b>id</b> column is the <u>event ID</u> (<i>Primary Key</i>, uniquely identifying each single SQL Statement into the Log).</li>
<li>the <b>time_start</b> and <b>time_end</b> columns contaits the <i>timestamps</i> measured immediately before and after executing the SQL statement itself.</li>
<li>the <b>user_agent</b> column identify the tool being used to executed the SQL statement.</li>
<li>the <b>sql_statement</b> column contains the SQL statement body.</li>
<li>the <b>success</b> column (<i>boolean</i>) tells if the statement execution was successful or not.</li>
<li>the <b>error_cause</b> will contain the <u>error message</u> eventually returned by SQLite.</li>
</ul><br>
You can learn more about the SQL Log as actually implemented by the <b>spatialite_gui</b> and <b>spatialite CLI</b> tools by reading this <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=tools-4.0">Wiki page</a>
<h4>Strictly related (and really useful) SQL Query</h4>
In order to directly get the execution timings (expressed in <i>milliseconds</i>) you simply have to use and SQL query like the following one:
<table bgcolor="#e0e0e0" cellspacing="8" cellpadding="4"><tr>
<td>SELECT id, JulianDay(time_end) - JulianDay(time_start) AS millis, sql_statement<br>
FROM sql_statements_log<br>
WHERE success = 1;
</td></tr></table><br>
<hr><br>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0-doc">main page</a>
Z a596746af55b946e535ed92b1809fc55