SQLite Stored Procedures

Here are some ideas on the implementation of stored procedures on SQLite, that maybe could be used to implement smart contracts.

SET result = SELECT ...

The main part of this proposal is to store the result of SELECT commands into a variable.

The reason to use this format instead of SELECT ... INTO variable is due to many wrappers that parse the SQL command before sending it to SQLite to know if it just writes to the db or it returns a value. Parsing just the first word is easier for those wrappers, and maybe even for us to implement it.

This variable will then contain a table, because the result of a SELECT statement is always a table, even when the statement returns a single value. eg:

SELECT count(*) FROM sales

It will return a table with 1 column and 1 row.

So when executing this command:

SET result = SELECT count(*) FROM sales

The variable result will contain a table (by default). Even not being so intuitive.

To make it easier to return a single value (when required) we could use one of these options:

Declaring the variable before it is used, using a type. eg:
```
 DECLARE result INTEGER
 SET result = SELECT count(*) FROM sales
```
In this case, if the variable is already declared the engine will try to convert the result to the expected format, retrieving the result from the table in the cases it has a single value.
Retrieving the value from the returned table on a second statement:
```
 SET result = SELECT count(*) FROM sales
 value = result[0][0]
```
Putting the variable inside double parenthesis, the first to indicate "get the first row" and the second to indicate "get the first column"
```
 SET ((result)) = SELECT count(*) FROM sales
```

Simplifying and requesting only a single parenthesis. eg:

 SET (name, email) = SELECT name, email FROM customers WHERE id = 123

When returning many values to different variables (when a comma is present) we don't need parenthesis
```
 SET name, email = SELECT name, email FROM customers WHERE id = 123
```
This can be implemented together with option 1.
It could use an "smart" approach: when the result is a single row, retrieve it from the table. Otherwise return a table.
```
 SET name = SELECT name FROM customers WHERE id = 123
```

Returned rows

Behavior must be defined for the following cases:

The result has 0 rows
The result has 1 row
The result has > 1 row

Retrieving values from a result set

When we do not cast the result from a SELECT statement, our variable will contain a result table

SET person = SELECT * FROM customers WHERE id = 123

To read the values from this result set we could use a syntax like in Javascript:

person[0].name
person[0].email

Or like Python:

person[0]['name']
person[0]['email']

But unhappily SQLite does not support parsing these formats.

So what can be done for now is to implement a function to read these values, that could be used in SQL commands as SELECT, UPDATE, etc.

get(person, 0, 'name')
get(person, 0, 'email')

Or, if the result is a single row like in this statement:

SET (person) = SELECT * FROM customers WHERE id = 123

We can read the values using:

get(person, 'name')
get(person, 'email')

Another option is to convert the result into a virtual table and retrieve the result as:

SELECT name FROM person
SELECT email FROM person

It is easy to implement and similar to PostgreSQL:

name FROM person
email FROM person

So we can have expressions like:

IF (SELECT name FROM person) = "John" THEN

Or

IF get(person,'name') = "John" THEN

This is not so good as person.name but it works for now.

Note 1: The result could also be interpreted as a list of values instead of a dictionary. In this case the access to the values could be done only by position

Note 2: Maybe we do not need to implement returning resultsets with many rows. They could be handled in FOR EACH statements

Expressions

The implementation of the SET command is central because it can be used also for evaluating expressions and storing the result on variables.

The line bellow:

a = b + 1

Could be implemented as:

SET a = SELECT b + 1

The part at right of the equal sign is a plain SQL command. We just added the SET to store the result on a variable. So it would be easier to implement.

The SELECT just needs to read stored variables.

If statement

It can run the if statement and store a flag internally (on the procedure or transaction).

The flag informs whether the next statements should be executed or ignored, until it reaches an ELSE or END IF statement.

Notice that if statements can be nested... so it must have nested flags storing the state/result from the last if statement.

Evaluating the result

It can convert this:

IF result > 10 THEN ...

Into this:

IF SELECT result > 10 THEN ...

Adding the SELECT statement to parse the expression and return the result.

The user could also use the SELECT statement explictly.

Variable implementation

The values could be stored as sqlite3_value structure that currently supports:

INTEGER
REAL / FLOAT
TEXT
BLOB
NULL

We should add an additional type:

TABLE

That would store the result of SELECT statements when returning tables.

We can check how SQLite implements virtual tables and row values.

bigint or big decimal support

SQLite support integers as int64 (I guess no uint64) and double precision floating point numbers. There is no native support for big decimal numbers (as far as I know).

We could solve that by storing them as strings and using a loadable extension containing functions to deal with the big decimal numbers.

So only the contracts that need it will use it. Other contracts will use simple native numeric arithmetic.

Executing a stored procedure

Either using position based arguments:

EXEC[UTE] transfer(11, 22, 50.0)

Or using named arguments:

EXEC[UTE] transfer(from_id=11, to_id=22, amount=50.0)

It may be also possible to call these procedures as user defined functions by adding them to the functions list together with a flag marking them as stored procedures, so the engine can fire the interpreter instead of firing the callback.

In this case the procedures declared as functions could be called by the expression evaluator:

SELECT fibonacci(5)

Or even inside of another stored procedure:

SET result = SELECT fibonacci(5)

That can also be called with just:

SET result = fibonacci(5)

Testing a stored procedure

It would be good to test the procedure for errors before saving it.

We could do this by starting a transaction, running the procedure and rolling back the transaction.

In this way it would not affect the database.

As it can have code/statements inside control flows, it should have different test cases for reaching all the possible paths.

The testing could include inserting data on the db before running the procedure and reading data after it was run, all before the transaction is rolled back.

Drafts

Transferring balance between accounts

Draft 1 (hard to implement or not possible)

CREATE PROCEDURE transfer (from_id, to_id, amount)
BEGIN TRANSACTION;
SET result = SELECT * FROM balances WHERE id = from_id;
IF result.count < 1 THEN
    ROLLBACK
    FAIL "The source account was not found"
END IF;
IF result[0].amount < amount THEN      -- or result[0]['amount']
    ROLLBACK
    FAIL "Not enough funds"
END IF;
DECLARE found INTEGER;
SET found = SELECT count(*) FROM balances WHERE id = to_id;
IF found <> 1 THEN
    FAIL "The destination account was not found"
END IF;
UPDATE balances SET balance = balance - amount WHERE id = from_id;
UPDATE balances SET balance = balance + amount WHERE id = to_id;
COMMIT;     -- maybe not needed
END PROCEDURE

Draft 2 (feasible)

CREATE PROCEDURE transfer (from_id, to_id, amount)
BEGIN
SET result = SELECT * FROM balances WHERE id = from_id;
IF num_rows(result) < 1 THEN
    FAIL "The source account was not found"
END IF;
IF get(result,0,'amount') < amount THEN
    FAIL "Not enough funds"
END IF;
DECLARE found INTEGER;
SET found = SELECT count(*) FROM balances WHERE id = to_id;
IF found <> 1 THEN
    FAIL "The destination account was not found"
END IF;
UPDATE balances SET balance = balance - amount WHERE id = from_id;
UPDATE balances SET balance = balance + amount WHERE id = to_id;
END PROCEDURE

Draft 3 (feasible)

CREATE PROCEDURE transfer (from_id, to_id, amount)
BEGIN
SET (result) = SELECT * FROM balances WHERE id = from_id;  -- () returns a dict, or NULL if num_rows!=1
IF result IS NULL THEN
    FAIL "The source account was not found"
END IF;
IF result.amount < amount THEN             -- or get(result,'amount') < amount
    FAIL "Not enough funds"
END IF;
DECLARE found INTEGER;
SET found = SELECT count(*) FROM balances WHERE id = to_id;
IF found <> 1 THEN
    FAIL "The destination account was not found"
END IF;
UPDATE balances SET balance = balance - amount WHERE id = from_id;
UPDATE balances SET balance = balance + amount WHERE id = to_id;
END PROCEDURE

Draft 4 (easier to implement)

CREATE PROCEDURE transfer (from_id, to_id, amount)
BEGIN
DECLARE balance REAL;
SET balance = SELECT balance FROM balances WHERE id = from_id;
IF balance = NULL THEN
    FAIL "The source account was not found"
END IF;
IF balance < amount THEN
    FAIL "Not enough funds"
END IF;
DECLARE found INTEGER;
SET found = SELECT count(*) FROM balances WHERE id = to_id;
IF found <> 1 THEN
    FAIL "The destination account was not found"
END IF;
UPDATE balances SET balance = balance - amount WHERE id = from_id;
UPDATE balances SET balance = balance + amount WHERE id = to_id;
END PROCEDURE

Draft 4b (using decorated variables)

CREATE PROCEDURE transfer (@from_id, @to_id, @amount)
BEGIN
DECLARE @balance REAL;
SET @balance = SELECT balance FROM balances WHERE id = @from_id;
IF @balance IS NULL THEN
    FAIL "The source account was not found"
END IF;
IF @balance < @amount THEN
    FAIL "Not enough funds"
END IF;
DECLARE @found INTEGER;
SET @found = SELECT count(*) FROM balances WHERE id = @to_id;
IF @found <> 1 THEN
    FAIL "The destination account was not found"
END IF;
UPDATE balances SET balance = balance - @amount WHERE id = @from_id;
UPDATE balances SET balance = balance + @amount WHERE id = @to_id;
END PROCEDURE

or

UPDATE balances SET balance = balance - amount WHERE id = from_id AND balance >= amount;
IF changes() < 1 THEN ...

Recursive call

Fibonacci number at position n:

CREATE PROCEDURE fib (n INT) RETURNS INT
BEGIN
    DECLARE n1 INT;
    DECLARE n2 INT;

    IF (n < 1) THEN
        RETURN 0;
    ELSEIF (n = 1) THEN
        RETURN 1;
    ELSE
        SET n1 = fib(n - 1);
        SET n2 = fib(n - 2);
        RETURN n1 + n2;
    END IF;
END

Checking the result type

SET result = SELECT price FROM products WHERE idGroup = 10
IF is_table(result) THEN ...

Iterating on the results

FOR EACH row IN result
    ...
END FOR

or

FOR EACH row IN SELECT * FROM sales WHERE ...
    ...
END FOR

or

FOR EACH fruit IN ("orange", "apple", "grape")    -- maybe this list can be implemented as a `row value`
    ...
END FOR

For loop

Incrementing:

FOR i = 1 TO 10 DO
    ...
END FOR

Decrementing:

FOR i = 10 TO 1 STEP -1 DO
    ...
END FOR

While loop

WHILE ... DO
    ...
END WHILE

or

WHILE ... LOOP
    ...
END LOOP

Provide feedback

Saved searches

Use saved searches to filter your results more quickly