ScriptingTcl

Paweł Salawa edited this page Jan 15, 2018 · 1 revision

Description

This plugin provides support for writting SQLiteStudio scripts in Tcl.

The plugin executes script at interpreter's global scope.

Scripts are pre-compiled and cached in memory, so executing same script multiple times benefits from it. Cache size is for 5 scripts at the time

Using external Tcl packages or modules is not possible, because Tcl interpreters are not initialized with "init.tcl". It works like this on purpose - the full initialization of interpreter is a very costly operation, while plugin tends to create and delete interepreters a lot. Maybe there will be an extra command to perform full initialization of the interpreter in the future, but nothing like that is available as for now.

Script input arguments

Input arguments are passed to the script through argc and argv Tcl global variables, just like the script was executed from system command line with those arguments passed in.

Extra commands

The db command

Every script has access to the db command, which gives the script access to the database, that the script is executed for. This applies for example if the script is executed as implementation of Custom SQL function. If the function is not executed in any database context, the db command will raise an error.

Using db command from within custom SQL function implemented with Tcl, or custom collation (and so on...) should never do any INSERTs, UPDATEs, or DELETEs on any table that the query (being executed) might be operating on, or the command might fail with a "table locked" error, or something similar.

Syntax

db eval ?sql?

Executes given sql on the database and returns results as a list, each cell as one element. This syntax is compatible with eval command from official SQLite Tcl interface. Example result: {row1_col1 row1_col2 row1_col3 row2_col1 row2_col2 row2_col3}

db eval ?sql? arrayName code

Executes given sql on the database and Iterates through results row by row and executes given Tcl code. For each row the arrayName array is defined with keys equal to names of columns in the row, including one special key "*" which keeps list of all other keys available. Returns empty string. This syntax is compatible with eval command from official SQLite Tcl interface.

db rows ?sql?

This is almost the same as eval, except query results are grouped, so each query results row is a single element in the command results list and each element of that list is a single column in the row. This is different from the eval, cause eval has all columns from all rows concatenated as a single, flat list. Example result:

db onecolumn ?sql?

This is the same as onecolumn from SQLite's official Tcl API. It executes query and returns only the first column of the first row as a result.

In all variants of db command you can use parameter placeholders (:name, @name, $name) in the SQL statement and if respective variables are available in the Tcl interpreter, SQLiteStudio will bind them to placeholders. Just like the SQLite's Tcl API does.