Update of "about Pause()"
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: e8d8d5aff21f669d706cadee12ced1b6c7ce112a
Page Name:about Pause()
Date: 2019-05-30 09:10:53
Original User: sandro
Parent: aeed0b3a249320f0885cec86c0cfb97feb8c4675
Content

back



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:

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

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:

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.

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