Update of "about Pause()"
Not logged in

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

Overview

Artifact ID: aeed0b3a249320f0885cec86c0cfb97feb8c4675
Page Name:about Pause()
Date: 2019-05-30 07:48:23
Original User: sandro
Parent: 966c6569e451cbda50655dcfa0f860b2e60228ef
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 intented 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 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


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:


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.



back