Questions on the `SET` command

[ppom0]

Hi! After trying to put a table-valued result in a SET variable, I think I understood that a SET variable can only store scalars, at least on SQLite:

Here's a minimal example with SQLite:

-- index.sql
DROP TABLE IF EXISTS list;
CREATE TEMP TABLE list (id INTEGER);
INSERT INTO list VALUES (1),(2),(3),(4);

SET all = (SELECT id FROM list);

SELECT 'debug' AS component, $all AS all;
-- shows only `1`

Is it a current limitation, or is it expected?

Is SET a standard SQLite command? I can't find anything on it on the internet.

And I guess there is some specific code on this command in src/webserver/database/sql.rs but I'm not quite sure.

The only doc about this currently is in https://sql-page.com/your-first-sql-website/. I feel like a documentation page "Extensions to SQL" would belong next to the "SQLPage functions" page, especially if there are other extensions than SET and the functions. I can contribute to the doc.

What do you think about this? Did I go in the good direction or am I far from reality? Thanks!

[lovasoa]

SQLPage variables can only store scalar strings

Is it a current limitation, or is it expected?

It is expected. SET creates a SQLPage variable, that can only contain a string (or null). Variables have to be used as sql prepared statement parameters anyways; it wouldn't be really useful to be able to store a table if it couldn't be used afterwards anyways. Temporary table-valued results are best stored in the database as temp tables, no need to transfer them back and forth between sqlpage and the database.

When you need to store more advanced data structures inside sqlpage, you can convert them to json first. You can store and use a list of ids like this:

drop table if exists list;
create temp table list as select 1 as id union select 2 union select 3 union select 4;

-- store even numbers from the list as a json array in the $all variable
set all = (select json_group_array(id) from list where id % 2 = 0);

-- select only numbers present in the json array stored in 'all'
select 'list' as component;
select id as title
from list
where id in (select value from json_each($all));

Documenting SQLPage's SQL language extensions

I feel like a documentation page "Extensions to SQL" would belong next to the "SQLPage functions" page, especially if there are other extensions than SET and the functions. I can contribute to the doc.

That would be great, thank you very much !

It would indeed be very useful to systematically document how we parse and transform the sql files before passing them to the database.

Below is a good starting point, which we could reformulate to be a little more pedagogical. Could you open a pull request creating a new file in https://github.com/sqlpage/SQLPage/tree/main/examples/official-site/your-first-sql-website ?

sql language extensions

• we accept both $-prefixed and :-prefixed statement parameters in source queries, whatever the original database's sql dialect for statement parameters is.
  • $ variables reference URL parameters, and can be overwritten by SET statements
  • : variables reference POST data from forms
  • the source sql is transformed so that the query passed to the database uses its true sql statement parameters syntax, and casts all variables to strings in the query, to let the database query optimizer know only strings (or nulls) will be passed.
• all function calls starting with sqlpage. are transformed to prepared statement parameters. sqlpage analyses the structure of the source query, and will call the function :
  • before executing the source query if the function does not process results coming from the database

    • select * from blog where slug = sqlpage.path()
      

    • gets transformed to

    • select * from blog where slug = CAST(?1 AS TEXT)
      

    • (on sqlite, which uses ?N for bound parameters natively)
  • after executing the source query if the function processes results coming from the database

    • select sqlpage.read_file_as_text(blog_post_file) as title from blog;
      

    • gets transformed to

    • select blog_post_file as title from blog;
      

• set statements are transformed to select queries, and their result is stored in a $-variable

  • set post_id = coalesce($post_id, 0);
    

  • gets transformed to

  • select coalesce(CAST(?1 AS TEXT), 0)
    

  • then only the first column of the first result row from the query results is read, and stored in the variable

[ppom0]

Hi! I currently don't have enough time to write it but I hope I'll have a first version in 2 weeks 🙂