Execute SQL statements local/remote

[Aesth]

I like the approach used in SQLPage, but I don't understand one thing that I'd like to clarify. As far as I understand, SQLPage has an internal parser that processes statements and decides which ones should be executed by the application itself and which ones will be passed on to an external database server. If you don't mind, could you at least briefly explain how this decision is made? I saw a note that if the statement appears static(?) then the query is executed by the application itself, and at least one condition for this was the absence of a from clause in the statement. But then the question arises: how complex can a statement executed by the application be? That is, what is the acceptable syntax that SQLPage can understand and execute without an external server? On the other hand, there may well be operators that don't have a from clause but must be executed on the server, such as "select @@Version" for MS SQL. What's really interesting is the line that separates responsibilities and how strict it is, or whether SQLPage can understand in a single operator what it should execute itself and what it should send to the database server, for example, something like "select (select type_name from type_list where id = 1) as component"
PS: Sorry, Google translation.

[lovasoa]

Hello and welcome to SQLPage !

Short answer: the notion of static simple select (select statements that are not sent to the database) is very restrictive. No arithmetic, no expressions, no function calls, no database variable references. Only single quoted string literals, literal numbers, booleans, literal NULLs, and sqlpage $variables qualify.

Examples:

• static simple select, not sent to the database
  • select 'form' as component, 'handle_form.sql' as action;
  • select 'text' as component, $alert_message as contents;
• sent to the database
  • select 'from' as component, 'handle_form.sql?id=' || $id as action;
  • select 'text' as component, $alert_message as contents where $should_alert;

I updated the corresponding documentation page: https://sql-page.com/extensions-to-sql

[Aesth]

Thank you so much, that makes things much clearer. One more thing, there's this code SET post_id = COALESCE($post_id, 0);
From one of your examples, I understood that it's executed by SQLPage itself. But COALESCE isn't in the list of executable functions. Does this mean SQLPage can evaluate expressions other than simple SELECT statements, like CASE ... END , without a reference to the database server? And if so, is there a list of similar expressions that are allowed in locally executed statements?
P.S. Sorry for being so pedantic.

[lovasoa]

No, SET post_id = COALESCE($post_id, 0); is executed by the database.

SQLPage sends SELECT COALESCE(CAST(? AS TEXT), 0); to the database, binds the value of $post_id to the statement, and stores the result back in the $post_id variable.

[Aesth]

Okay, so SQLPage doesn't execute any other expressions or functions declared in the description locally. No UPPER, LOWER, TRIM, or anything similar? Only a simple SELECT using literals and native functions is executed locally. Am I correct?

[lovasoa]

yes

[Aesth]

Thank you very much !