D 2019-05-30T07:48:23.480
L about\sPause()
P 966c6569e451cbda50655dcfa0f860b2e60228ef
U sandro
W 6968
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a><hr><br>
<h1>Introduction</h1>
Many power users routinely use SpatiaLite for executing <b>very complex SQL Scripts</b>, may be largely based on <b>Stored Procedures</b>.<br>
Sometimes attempting to debug a complex SQL script may easily become a difficult and frustrating activity.<br><br>
In order to facilitate the debugging of SQL Scripts as much as possible, SpatiaLite (starting since <b>version 5.0.0</b>) supports a specific <b>Pause()</b> SQL function.<br>
This function is intented to be the conceptual equivalent of what a <a href="https://en.wikipedia.org/wiki/Breakpoint">breakpoint</a> is intended to be for ordinary debugger tools.<br><br>
Few basic concepts:
<ul>
<li>you, as the developer of the SQL Script, are free to call <b>Pause()</b> at some critical point of the Script (<i>where you suspect that nasty things have just happened</i>).</li>
<li>once <b>Pause()</b> is executed it suddenly stops any further execution of the calling process, that will indefinitely remain in a frozen state.</li>
<li>this gives you the opportunity to start a second hinstance of SpatiaLite to be used for inspecting any possible detail in the work database,<br>
And you'll be eventually free to correct on the fly some wrong value you'll notice somewhere.</li>
<li>when you've completed all your debug activities, you simply have to take the appropriate <b>continue action</b>,<br>
The SQL Script will then resume its normal execution exactly from the statement immediately following <b>Pause()</b></li>
<li><b><u>Note</u></b>: nothing forbids to call <b>Pause()</b> as many times as you can find useful from within the same SQL Script.</li>
</ul>
<br>
<table cellpadding="6" cellspacing="6" bgcolor="#ffffd0">
<tr><td>
<h3>Important notice: Pause() and SQL Transactions</h3>
<b><u>Recall</u></b>: all changes contained within a <b>pending</b> (<i>aka</i> <b>uncommitted</b>) SQL Transaction are strictly private.<br>
This practically means that all them will remain completely invisibile to any other connection accessing the same database.<br>
But a debugging session as previously defined necessarily requires using at least two different connections<br>
(<i>the one suspended by </i><b>Pause()</b><i> and the other used for inspecting the database</i>).<br><br>
<b><u>Short conclusion</u></b>: the only safe way for calling <b>Pause()</b> is from a point in the SQL code surely outside any pending Transaction.<br>
This will avoid any possible visibility issue and any locking conflict between different connections.<br>
Always remember to carefully check for this before defining a call to <b>Pause()</b>
</td><tr>
</table>
<h3>Auxiliary SQL functions</h3>
<verbatim>
SELECT IsPauseEnabled();
------------------------
0
SELECT EnablePause();
SELECT IsPauseEnabled();
------------------------
1
SELECT DisablePause()
SELECT IsPauseEnabled();
------------------------
0
</verbatim>
<ul>
<li>all connections will initially start by keeping <b>Pause()</b> disabled by default.<br>
And when Pause() is disabled it will be jus considered as an effectless <b>no-op</b></li>
<li>you must explicitly call <b>EnablePause()</b> in order to enable Pause() to effectively work.<br>
Once that Pause() is enabled this setting will persist until the end of the current connection life cycle.</li>
<li>you can eventually call <b>DisablePause()</b> to revert back to the default behaviour.</li>
<li>you can call <b>IsPauseEnabled()</b> in order to check if Pause() is currently enabled or not.</li>
</ul><br>
<hr>
<h1>A practical example of using Pause()</h1>
<verbatim>
-- creating a new table
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL);
-- starting a transaction
BEGIN;
-- inserting some rows into the above table
INSERT INTO test VALUES(NULL, 'one');
INSERT INTO test VALUES(NULL, 'two');
INSERT INTO test VALUES(NULL, 'three');
INSERT INTO test VALUES(NULL, 'four');
INSERT INTO test VALUES(NULL, 'five');
-- committing the pending transaction
COMMIT;
-- then pausing
SELECT Pause();
-- inserting more rows, then pausing again
BEGIN;
INSERT INTO test VALUES(NULL, 'six');
INSERT INTO test VALUES(NULL, 'seven');
INSERT INTO test VALUES(NULL, 'eight');
INSERT INTO test VALUES(NULL, 'nine');
INSERT INTO test VALUES(NULL, 'ten');
COMMIT;
SELECT Pause();
-- inserting the last block of rows, then exiting
BEGIN;
INSERT INTO test VALUES(NULL, 'eleven');
INSERT INTO test VALUES(NULL, 'twelfe');
INSERT INTO test VALUES(NULL, 'thirteen');
INSERT INTO test VALUES(NULL, 'fourteen');
INSERT INTO test VALUES(NULL, 'fifteen');
INSERT INTO test VALUES(NULL, 'sixteen');
INSERT INTO test VALUES(NULL, 'seventeen');
INSERT INTO test VALUES(NULL, 'eighteen');
INSERT INTO test VALUES(NULL, 'nineteen');
INSERT INTO test VALUES(NULL, 'twenty');
COMMIT;
-- end job - all done
</verbatim>
<br>
This is a very simple (and stupid) SQL Script, but it's enough to practically test how <b>Pause()</b> works.<br>
<b><u>Note</u></b>: the implementation of <b>Pause()</b> almost completely differs on Linux and Windows; please read the appropriate section.<br><br>
<hr>
<h3>Using Pause() on Linux</h3>
The Linux implementation is fully based on <a href="https://en.wikipedia.org/wiki/Signal_(IPC)#POSIX_signals">system signals</a>; the same approach applies to any Unix and Unix-like system, this including Mac OS X.<br>
More specifically it's based on <b>SIGSTOP</b> and <b>SIGCONT</b> signals:
<li><b>SIGSTOP</b> instructs the operating system to stop the calling process for later resumption.</li>
<li><b>SIGCONT</b> instructs the operating system to continue (restart) the process previously paused by the SIGSTOP.</li>
</ul>
<br><br>
<hr>
<h3>Using Pause() on Windows</h3>
The Windows implementation is based on the <a href="https://docs.microsoft.com/en-us/windows/console/readconsoleinput">ReadConsoleInput()</a> system API.<br><br>
<b><u>Note</u></b>: there is a striking difference from the Unix/Linux approach.
<ul>
<li>on Linux every process will always respect system signals such as SIGSTOP and SIGCONT, with no possible exception.<br>
And this indifferently applies to both CLI and GUI applications.</li>
<li>on Windows adopting an approach based on Console Input implies two relevant limitations:
<ul>
<li>only a process started from the <b>CMD.exe</b> command shell can have its own Console.<br>
But a process started by directly clicking the corresponding icon will never be connected to a Console, and consequently <b>Pause()</b> will never work.</li>
<li>And even in the case of a process started by <b>CMD.exe</b> an eventual redirection of the <b>Standard Input</b> will forbid a working <b>Pause()</b>.</li>
</ul></li>
</ul>
<br><br>
<hr><br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a>
Z 47132a70a4c40e87c7bbaca3bf222c63