D 2019-05-30T07:48:23.480 L about\sPause() P 966c6569e451cbda50655dcfa0f860b2e60228ef U sandro W 6968 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 intented 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 hinstance 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 invisibile 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 jus 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.

A practical example of using Pause()

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

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().

back Z 47132a70a4c40e87c7bbaca3bf222c63