Artifact [e8d8d5aff2]
Not logged in

Artifact e8d8d5aff21f669d706cadee12ced1b6c7ce112a:

Wiki page [about Pause()] by sandro 2019-05-30 09:10:53.
D 2019-05-30T09:10:53.123
L about\sPause()
P aeed0b3a249320f0885cec86c0cfb97feb8c4675
U sandro
W 10242
<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 intended 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 instance 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 invisible 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 just 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>
<table cellpadding="6" cellspacing="6" bgcolor="#d0ffd0">
<tr><td>
<b>Note</b>: keeping <b>Pause()</b> disabled by default has an useful purpose.<br>
It's intended for allowing SQL developers to regularly insert as many <b>Pause()</b> breakpoints as required.
<ul>
<li>In all regular executions of the SQL Script they'll be simply ignored.</li>
<li>Only when a debugging session is effectively required they'll become effective.</li>
<li>So you'll always use the same SQL code, without any need to manually change it.</li>
</td></tr>
</table><br>
<hr>
<h1>A practical example of using Pause()</h1>
<verbatim>-- dropping the test table, just in case it already exists
DROP TABLE test;

-- creating the test 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, 'twelve');
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>
<h3>step #1</h3>
<verbatim>
$ spatialite pause.sqlite

SELECT EnablePause();
.read test_pause.sql
</verbatim>
We'll start first by invoking the <b>spatialite</b> CLI tool.<br>
Then we'll call <b>EnablePause()</b>, and finally we'll execute the SQL Script.
<h3>step #2</h3>
<verbatim>
***************  PAUSE  ***************
command for resuming execution is:
kill -SIGCONT 1234
</verbatim>
When the SQL Script executes the first <b>Pause()</b> it will be suspended, and a message line will be printed on the shell.<br>
The message will report the <b>kill</b> command required to resume execution. <b>Note</b>: 1234 will be actually replaced by the current <b>PID</b>, that is the unique identifier of the process running the SQL Script.
<br><br>
Now you'll be free to begin you debugging activities by starting a second connection to the same work database.<br>

<h3>step #3</h3>
<verbatim>
$ kill -SIGCONT 1234

*************** SIGCONT: resuming execution after PAUSE
</verbatim>
Once you've completed any required debug activity, you are simply required to execute the appropriate <b>kill</b> command for resuming the ordinary execution of the SQL Script.<br>
In this case too a confirmation message will be printed on the shell.
<h3>steps #4 and #5</h3>
The same sequence of events will repeat again when the second <b>Pause()</b> will be processed.<br>
This time, after resuming execution the SQL Script will finally stop.
<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>
<h3>step #1</h3>
<verbatim>
> spatialite pause.sqlite

SELECT EnablePause();
.read test_pause.sql
</verbatim>
We'll start first by invoking the <b>spatialite</b> CLI tool from <b>CMD.exe</b>.<br>
Then we'll call <b>EnablePause()</b>, and finally we'll execute the SQL Script.
<h3>step #2</h3>
<verbatim>
***************  PAUSE  ***************  Hit any key to continue
</verbatim>
When the SQL Script executes the first <b>Pause()</b> it will be suspended, and a message line will be printed on the shell.
<br><br>
Now you'll be free to begin you debugging activities by starting a second connection to the same work database.<br>

<h3>step #3</h3>
<verbatim>
<<key>>

*************** resuming execution after PAUSE
</verbatim>
Once you've completed any required debug activity, you are simply required to press any keyboard key for resuming the ordinary execution of the SQL Script.<br>
In this case too a confirmation message will be printed on the shell.
<h3>steps #4 and #5</h3>
The same sequence of events will repeat again when the second <b>Pause()</b> will be processed.<br>
This time, after resuming execution the SQL Script will finally stop.
<br><br>
<hr><br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.3.0-doc">back</a>
Z 58ea8286c263e3c7e7bed087741c567a