Structuring code: using standalone SET and local variables in SQLpage

[pchemguy]

I am a new user of SQLpage and am still trying to figure out optimal ways of doing things using SQLite as my primary db engine (so the examples below will need to be adjusted to different engines).

I accidentally discovered the standalone "SET" statement (not available in plain SQLite/SQL), which can be used to define local variables, for example:

SET $sqlite_version = (
    SELECT sqlite_version()
);

sets $sqlite_version to a string containing the version of the integrated SQLite library. This variable can then be used in the code.

When the query used to set a variable returns multiple rows/columns, the variable takes the value of the first column in the first row. In such a case, JSON can help pass around non-scalar values, for example:

SET $function_list = (
    SELECT json_group_array(DISTINCT name ORDER BY name)
    FROM pragma_function_list()
);

sets $function_list to a JSON array containing a sorted list of available SQL functions.

A more practical example is to place the following code close to the beginning of the module:

-- =============================================================================
-- =============================== Module vars =================================
-- =============================================================================

SET $curpath = '&path=' || sqlpage.path();

Then $curpath can be appended, for example, to relative redirect links, so that the target of redirects could redirect back after processing (I may show code examples in a separate post).

[lovasoa]

That's a good explanation.

I think we should strive to improve the learning path; https://sql.ophir.dev/your-first-sql-website/ doesn't do a great job at helping people discover more than the the basics.

@pchemguy , would you be interested in contributing to the documentation ? I have seen your advanced SQLite tutorial and you seem to have a talent for explaining technical concepts in an approachable manner. I think we could make SQLPage's documentation much better together.

A good place to start would be opening a pull request targeting https://github.com/lovasoa/SQLpage/blob/main/examples/official-site/your-first-sql-website/tutorial.md

[pchemguy]

Ok, I will think about it. I mean I am open to it, I just cannot tell how much time I could contribute. I like the project, so I will try to make some time. And thank you for your comments about my tutorial.