SQL Script

ArcadeDB allows execution of arbitrary scripts written in Javascript or any scripting language installed in the JVM.

ArcadeDB supports a minimal SQL engine to allow a batch of commands, called "SQL Script" (sqlscript). Batches of commands are useful when you have to execute multiple things at the server side and avoiding the network roundtrip for each command.

The following list highlights the differences between sql and sqlscript:

  • sqlscript allows multiple statements, sql is limited to a single statement.

  • A sqlscript batch is automatically transaction, a sql query or command by itself is not.

  • sqlscript supports additional control flow constructs (i.e. loops and conditionals, see below).

Given that sqlscript always induces transactions, the transaction atomicity prerequisite of "all-or-nothing" applies to every sqlscript script.

SQL Script supports all the ArcadeDB SQL Commands, plus the following:

  • BEGIN [isolation <isolation-level>], where <isolation-level> can be READ_COMMITTED, REPEATABLE_READ. By default is READ_COMMITTED

  • LOCK <lock-type> <id>[,<id>]*, where <lock-type> has to be TYPE or BUCKET followed by the respective types' or buckets' identifiers <id>

  • COMMIT [retry <retry>], where:

    • <retry> is the number of retries in case of concurrent modification exception

  • ROLLBACK

  • LET <variable> = <SQL>, to assign the result of a SQL command to a variable. To reuse the variable prefix it with the dollar sign $.

  • SET GLOBAL <variable> = <BOOLEAN|STRING|NUMBER|LIST|MAP>, to assign a transient global variable. To reuse the variable prefix it with the dollar sign $.

LOCK explicitly locks a type or bucket during a transaction until it is committed.
A SET GLOBAL-declared variable exists as long as the associated database is not closed or the server shut down.
A LET can be used as an ephemeral view.
LET-declared variables have dynamic scope.

  • IF(<condition>){ <statement>; [<statement>;]* }. Look at conditional execution.

  • WHILE(<condition>){ <statement>; [<statement>;]* }. Look at loops.

  • FOREACH(<variable> IN <expression>){ <statement>; [<statement>;]* }. Look at loops.

  • SLEEP <ms>, put the batch in wait for <ms> milliseconds.

  • BREAK Look at loops.

  • RETURN <value>, where value can be:

    • any value. Example: return 3

    • any variable with $ as prefix. Example: return $a

    • arrays (HTTP protocol only, see below). Example: return [ $a, $b ]

    • maps (HTTP protocol only, see below). Example: return { 'first' : $a, 'second' : $b }

    • a query. Example: return (SELECT FROM Foo)

An empty result can be returned via RETURN [] in SQLscript and also SQL.
to return arrays and maps (eg. Java or Node.js driver) it’s strongly recommended using a RETURN SELECT, eg. 
RETURN (SELECT $a as first, $b as second)

This will work on any protocol and driver.

Dynamic typing

Types in statements like SELECT, INSERT, CREATE, etc. can be provided as variables:

LET mytype = 'vec';
LET r = SELECT FROM $mytype WHERE num > 0;
RETURN $r

this includes edge types in edge creation:

LET myedge = 'edg';
LET r = CREATE EDGE $myedge FROM #1:0 TO #1:1;
RETURN $r

It is required that statements using a variable type are inside a LET-assignment.

Optimistic transaction

Example to create a new vertex in a Transactions and attach it to an existent vertex by creating a new edge between them. If a concurrent modification occurs, repeat the transaction up to 100 times:

begin;
let account = create vertex Account set name = 'Luke';
let city = select from City where name = 'London';
let e = create edge Lives from $account to $city;
commit retry 100;
return $e;

Note the usage of $account and $city in further SQL commands.

Conditional execution

SQL Batch provides IF constructor to allow conditional execution. The syntax is

IF( <sql-predicate> ){
  <statement>;
  <statement>;
  ...
}
There has to be a linebreak after IF( <sql-predicate> ){ and } has to follow a linebreak.

<sql-predicate> is any valid SQL predicate (any condition that can be used in a WHERE clause):

IF( $a.size() > 0 ) {
  ROLLBACK;
}

Loops

SQL Batch provides two different loop blocks: FOREACH and WHILE.

FOREACH

Loops on all the items of a collection and, for each of them, executes a set of SQL statements. The expression argument should return an array, and can be a literal array, variable, or result of a sub-query.

The syntax is:

FOREACH( <variable> IN <expression> ){
   <statement>;
   <statement>;
   ...
}

Example

FOREACH( $i IN [1, 2, 3] ){
  INSERT INTO Foo SET value = $i;
}

WHILE

Loops while a condition is true.

The syntax is:

WHILE( <condition> ){
   <statement>;
   <statement>;
   ...
}

Example

LET $i = 0;
WHILE ($i < 10){
  INSERT INTO Foo SET value = $i;
  LET $i = $i + 1;
}

BREAK

FOREACH and WHILE loops can be conditionally interrupted using BREAK.

FOREACH( $i IN [1, 2, 3] ){
  IF( $i >2 ){
    BREAK;
  }
  CONSOLE.log $i;
}

Example that computes the sum of amounts for customers until the amount is negative.

LET total = 0;
FOREACH( $record IN (select from CustomerAccount) ){
  IF( record.amount < 0 ) {
    BREAK;
  }
  LET total = $total + record.amount;
}
RETURN $total;