SCRIPT

The SCRIPT statement allows queries to include an inline script.   An inline script can do anything.  

 

SCRIPT <script> ENGINE '<engine>' [[ <text> ]]

 

Inline scripts provide a way to use script functions in a single self-contained query component.

 

Example: Add a computed field that uses an inline script to set insert date for each record:

 

ALTER TABLE t (

  ADD insertdate DATETIME

    WITH

[[

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

]]

    AS [[ currentdate() ]]

);

 

After running the above query, existing records in the table will get the current datetime.   If we wait a minute or so to allow the datetime to visibly change and then insert a new record,  either manually or using INSERT, it will get the new, current datetime value.

 

Example

We begin by creating a table:

 

CREATE TABLE data (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  value NVARCHAR,

  logger INT32

    WITH

[[

SCRIPT [inline] ENGINE 'c#'

[[

 

class Script

{

static Manifold.Context Manifold;

static int Log(string table, long id, string value)

{

  Manifold.Application.Log(string.Format("{0}: record {1} = {2}",

    table, id, value));

  return 0; // ignored

}

}

 

]]

FUNCTION logvalue(@table NVARCHAR, @id INT64, @value NVARCHAR) INT32

  AS SCRIPT INLINE [inline] ENTRY 'Script.Log';

 

]]

    AS [[ logvalue('[data]', mfd_id, value) ]]

);

 

The table has a computed field which logs changes to other fields.

 

We do two inserts and an update:

 

INSERT INTO data (value) VALUES ('abc');

INSERT INTO data (value) VALUES ('def');

UPDATE data SET value=value & 'x' WHERE value='def';

 

We see changes logged to the log:

 

#

++ [data]: record 1 = abc

++ [data]: record 2 = def

++ [data]: record 2 = defx

 

The above lines log the first INSERT, the second INSERT and the UPDATE, respectively.

 

Such techniques must be used with care, because inserting a million records will just flood the log. The log will survive, but inserts will be noticeably slower than usual and, more importantly, we  will not be able to see anything in the log apart from the automated messages generated by the computed field.  A better approach for logging might be to have a counter to log only the first ten messages, or perhaps to log no more than one message per minute, which could be done by saving the date and time of the last logged message, using a static variable and a critical section, or similar means to  avoid flooding the log.

 

Notes

Anything goes -  At the present time, there are no restrictions on what an inline script is permitted within the WITH clause.   Depending on the evolution of how inline scripts are used in real life it could be that some restrictions will be introduced in future builds.

 

Execution of Inline Scripts - Part of an inline script will be executed by the system during the set up when the expression is loaded.  That includes everything in the WITH clause, for example.  We  can first define an inline script and then call its functions using VALUE @... = <calling a script function> or, for example, TABLE CALL <calling a script function that returns a table>.   When a table needs to compute the value of a computed field or perform a check on a constraint, those parts will run synchronously as needed.

 

Avoid User Interface - It is not a good idea to display any user interface from within an inline script, in either execution phase as mentioned above.   It is way too easy to get too many windows popping up at what might sometimes seem to be random moments.  Logging is perhaps fine as a debugging measure, but only as a debugging measure; otherwise logging also can quickly become way too chatty.

 

Use .NET, not COM - Use .NET languages within inline scripts.  COM languages are currently not supported, since COM languages typically use objects with thread affinity.   Special means must be added to handle thread affinity since without those special means evaluating the expression might fail.   Until such means can be added, in future builds, COM languages cannot be used within inline scripts.

 

See Also

Queries

 

Command Window

 

Query Builder

 

SQL Statements

 

EXECUTE

 

GROUP BY

 

SPLIT

 

TABLE

 

UNION / EXCEPT / INTERSECT

 

VALUE

 

VALUES