Back to 5.0.0-doc main page

Introduction

Many power users routinely use SpatiaLite for executing very complex SQL Scripts, may be largely based on Stored Procedures.
Sometimes attempting to debug a complex SQL script may easily become a difficult and frustrating activity.

In order to facilitate the debugging of SQL Scripts as much as possible, SpatiaLite (starting since version 5.0.0) supports a specific Pause() SQL function.
This function is intended to be the conceptual equivalent of what a breakpoint is intended to be for ordinary debugger tools.

Few basic concepts:

you, as the developer of the SQL Script, are free to call Pause() at some critical point of the Script (where you suspect that nasty things have just happened).
once Pause() is executed it suddenly stops any further execution of the calling process, that will indefinitely remain in a frozen state.
this gives you the opportunity to start a second instance of SpatiaLite to be used for inspecting any possible detail in the work database,
And you'll be eventually free to correct on the fly some wrong value you'll notice somewhere.
when you've completed all your debug activities, you simply have to take the appropriate continue action,
The SQL Script will then resume its normal execution exactly from the statement immediately following Pause()
Note: nothing forbids to call Pause() as many times as you can find useful from within the same SQL Script.

Important notice: Pause() and SQL Transactions

Recall: all changes contained within a pending (aka uncommitted) SQL Transaction are strictly private.
This practically means that all them will remain completely invisible to any other connection accessing the same database.
But a debugging session as previously defined necessarily requires using at least two different connections
(the one suspended by Pause() and the other used for inspecting the database).

Short conclusion: the only safe way for calling Pause() is from a point in the SQL code surely outside any pending Transaction.
This will avoid any possible visibility issue and any locking conflict between different connections.
Always remember to carefully check for this before defining a call to Pause()

Auxiliary SQL functions

SELECT IsPauseEnabled();
------------------------
0

SELECT EnablePause();
SELECT IsPauseEnabled();
------------------------
1

SELECT DisablePause()
SELECT IsPauseEnabled();
------------------------
0

all connections will initially start by keeping Pause() disabled by default.
And when Pause() is disabled it will be just considered as an effectless no-op
you must explicitly call EnablePause() in order to enable Pause() to effectively work.
Once that Pause() is enabled this setting will persist until the end of the current connection life cycle.
you can eventually call DisablePause() to revert back to the default behaviour.
you can call IsPauseEnabled() in order to check if Pause() is currently enabled or not.

Note: keeping Pause() disabled by default has an useful purpose.
It's intended for allowing SQL developers to regularly insert as many Pause() breakpoints as required.

In all regular executions of the SQL Script they'll be simply ignored.
Only when a debugging session is effectively required they'll become effective.
So you'll always use the same SQL code, without any need to manually change it.

A practical example of using Pause()

-- 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

This is a very simple (and stupid) SQL Script, but it's enough to practically test how Pause() works.
Note: the implementation of Pause() almost completely differs on Linux and Windows; please read the appropriate section.

Using Pause() on Linux

The Linux implementation is fully based on system signals; the same approach applies to any Unix and Unix-like system, this including Mac OS X.
More specifically it's based on SIGSTOP and SIGCONT signals:

SIGSTOP instructs the operating system to stop the calling process for later resumption.
SIGCONT instructs the operating system to continue (restart) the process previously paused by the SIGSTOP.

step #1

$ spatialite pause.sqlite

SELECT EnablePause();
.read test_pause.sql

We'll start first by invoking the spatialite CLI tool.
Then we'll call EnablePause(), and finally we'll execute the SQL Script.

step #2

***************  PAUSE  ***************
command for resuming execution is:
kill -SIGCONT 1234

When the SQL Script executes the first Pause() it will be suspended, and a message line will be printed on the shell.
The message will report the kill command required to resume execution. Note: 1234 will be actually replaced by the current PID, that is the unique identifier of the process running the SQL Script.

Now you'll be free to begin you debugging activities by starting a second connection to the same work database.

step #3

$ kill -SIGCONT 1234

*************** SIGCONT: resuming execution after PAUSE

Once you've completed any required debug activity, you are simply required to execute the appropriate kill command for resuming the ordinary execution of the SQL Script.
In this case too a confirmation message will be printed on the shell.

steps #4 and #5

The same sequence of events will repeat again when the second Pause() will be processed.
This time, after resuming execution the SQL Script will finally stop.

Using Pause() on Windows

The Windows implementation is based on the ReadConsoleInput() system API.

Note: there is a striking difference from the Unix/Linux approach.

on Linux every process will always respect system signals such as SIGSTOP and SIGCONT, with no possible exception.
And this indifferently applies to both CLI and GUI applications.
on Windows adopting an approach based on Console Input implies two relevant limitations:
- only a process started from the CMD.exe command shell can have its own Console.
  But a process started by directly clicking the corresponding icon will never be connected to a Console, and consequently Pause() will never work.
- And even in the case of a process started by CMD.exe an eventual redirection of the Standard Input will forbid a working Pause().

step #1

> spatialite pause.sqlite

SELECT EnablePause();
.read test_pause.sql

We'll start first by invoking the spatialite CLI tool from CMD.exe.
Then we'll call EnablePause(), and finally we'll execute the SQL Script.

step #2

***************  PAUSE  ***************  Hit any key to continue

When the SQL Script executes the first Pause() it will be suspended, and a message line will be printed on the shell.

Now you'll be free to begin you debugging activities by starting a second connection to the same work database.

step #3

<<key>>

*************** resuming execution after PAUSE

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.
In this case too a confirmation message will be printed on the shell.

steps #4 and #5

The same sequence of events will repeat again when the second Pause() will be processed.
This time, after resuming execution the SQL Script will finally stop.

Back to 5.0.0-doc main page