Renderer-specific statements

[3n-mb]

Have you considered new statement, like all SQL dbs have: SHOW in pg and mysql, DO in pg, so on?

How about

RENDER HTML _optional_tag_name_ WITH ...query executed in db...

or

RENDER HTML _html_form_tag_ WITH ...query executed in db... DO ...some action...

Those optional tag names can be web component, or template component, both in standard library and user added, or something else. With own statements SQLpage may have a greater space for future development.

Postgres' DO hints that for rendering form it can be about what form does, while in general it can be a spot to invoke embedded runtime to run some small function on inputs from db data. It can be Deno and WASI for whatever lambda-style functions user may wish to have.

Of course, a more structured and a more readable SQL with extension may need cleaner separation: no render-related anything goes to db server, reading code gives clean separation of what processes go here. MVP is fine with sequence that looks like

choisir
choisir
choisir

some of which will mean rendering, others will be actual data selects from db, all sprinkled with implicit closures of initial display elements, like browser used to (and may be do now) close tags that one forgot to close -- very web-y, not sql-y, though. It is amazing for MVP, but will this support one, say in opening data in those no-longer supported on-premise Jira tables in Postgres databases?

SQL is incredibly structured to the point of being simple and fast to both generate and parse. Don't add complex things with formulas. If small parser can't read it, give it to db to process or finally choke. But readability for a non-programmer, non-debugger, will improve.

[lovasoa]

Thank you for your thoughtful suggestions! I can see that you've given a lot of thought to possible enhancements and I appreciate your initiative.

Your ideas around enhancing SQL with HTML rendering capabilities are intriguing. However, I'd like to point out that SQLPage is designed to be a SQL-only website builder. Our goal is to keep the tool simple, easy to learn, fast, and efficient while adhering strictly to SQL syntax and capabilities, taking advantage of its flexibility and wide user base. Many SQLPage users know SQL without even knowing the concepts of rendering or html, and maintain large SQLPage applications.

If you haven't already, I'd strongly recommend you to try out SQLPage as it is currently designed. Try building a website using the existing functionality and you might be surprised at the flexibility and power that SQL alone can offer.

Your suggestions are certainly innovative and could make for a great extension to a project that includes HTML handling directly in SQL. However, incorporating them into SQLPage might deviate it from its original purpose and design.

Again, thank you for your suggestions. I encourage you to experiment with SQLPage and see how it works for you. Your feedback is always welcome.

[3n-mb]

Implementation wise,
since sqlpage is already using sqlparser = { version = "0.46.0", features = ["visitor"] }
may be add to sqlparser tokens and expected order for new render statements? Does that lib allow defined extensions, or it needs writing?

[lovasoa]

I'm sorry if my comment above was unclear: SQLPage will not deviate from the standard SQL syntax.

[3n-mb]

SQLPage contains an interesting step, that can be used elsewhere, packaged differently.
What if first web component gets data from one db, and another from another. Then SQLPage may want to extend, or, another app uses an SQLPage's interesting bit, doing something else with the result.

[3n-mb]

I have a question.
Is SQLPage trying to pretend that it generates pages with standard SQL syntax?
I don't think so. But select is effectively overloaded with the different meaning. We've seen pro's stepping away from overloading meaning of common operations (Java, C++).
Overloaded select is not processed by db. Or is it? And that was puzzling to me at least. Overloading creates confusion. And this puzzling is a friction.

Does Postgres commit an offense when they add SHOW? Not in practical sense.
Does Postgres SHOW statement follows all SQL style and feel? Yes. Very much so. And with already existing rust parser, with all language nuances that are in it, except for vocabulary, one can create clean SQL extension for this new purpose of turning tabulated form into something else.

[3n-mb]

Run a poll.
Prep some options that will make SQLPage's own life easier, and run a poll among users, may be users, and new people. Run a poll.
Again, I am not talking about today. SQLPage is amazing, in this iteration. And I appreciate an amount of details that are packaged into SQLPage, like db setup, migrations, auth, -- all packaged for current version, it is all perfect. Run a poll for future options.

[lovasoa]

I am still not sure you fully grasp the implications of being able to select a component and its options with a traditional select statement. I really encourage you to explore SQLPage for a while, maybe write a small app with it, before you suggest this kind of changes.

[3n-mb]

Let's take time and compare sql with overloaded select, and extensions that can repeat select if needed, but call it something else. Let's compare readability. No rush.

Let's take example https://github.com/lovasoa/SQLpage/tree/main/examples/splitwise

-- Simple form to create a shared expense account
SELECT 'form' as component,
    'New shared expense account' as title,
    'Create the shared expense account!' as validate;
SELECT 'Account Name' AS label,
    'shared_expense_name' AS name;

-- Insert the shared expense account posted by the form into the database
INSERT INTO expense_group(name)
SELECT :shared_expense_name
WHERE :shared_expense_name IS NOT NULL;

-- List of shared expense accounts
-- (we put it after the insertion because we want to see new accounts right away when they are created)
SELECT 'list' as component;
SELECT name AS title,
    'group.sql?id=' || id AS link
FROM expense_group;

and with extension to SQL vocabulary:

RENDER HTML form WITH
    title = 'New shared expense account',
    validate = 'Create the shared expense account!'
COLLECTING
    shared_expense_name FROM HTML input WITH label = 'Account Name'
DO
    INSERT INTO expense_group(name) VALUES (:shared_expense_name)
REFRESHING PAGE;

RENDER HTML list WITH
    SELECT name AS title, 'group.sql?id=' || id AS link
    FROM expense_group
    RENDERING INTO HTML item;

• Do commands with extended vocabulary need comments?
• If a person just looks at text in a file with path splitwise/index.sql without any further context, which variant will tell what it is?
• Did the first one clearly communicated that form button will reload this page?
• Where does SQL vocabulary helps to better see flow of data?
• Does title = 'New shared expense account' looks simpler than 'New shared expense account' as title and invokes UPDATE command with similar data flow dynamic: data is inserted into something.

What if we wanna page that has list refreshing via server events when some table gets updated? And there is no need to reload page.
I'll do it with extended SQL vocabulary:

RENDER HTML form WITH
    title = 'New shared expense account',
    validate = 'Create the shared expense account!'
COLLECTING
    shared_expense_name FROM HTML input WITH label = 'Account Name'
DO
    INSERT INTO expense_group(name) VALUES (:shared_expense_name)
TRIGGERING EVENT expenses_updated;

RENDER HTML list WITH
    SELECT name AS title, 'group.sql?id=' || id AS link
    FROM expense_group
    REFRESHED BY EVENT expenses_updated
    RENDERING INTO HTML item;

Let's take on the second file of the example app in the following comment.

[3n-mb]

Only now, while translating the second file, I start to understand that third line in

INSERT INTO expense_group(name)
SELECT :shared_expense_name
WHERE :shared_expense_name IS NOT NULL;

indicates that this is always called, and IS NOT NULL checks if it was a form action, not a more common page GET.

Secondly note, how pushing parser to produce explicit data flow info allows to build a more efficient html serving part of an overall server, cause explicit DO will naturally tie insert to POST. And your automatically generated routes sanitize and check input parameters, ensuring no nulls at that point, returning 400, I believe.

I thought, about WHERE as a way to exclude NULL values from form into database. Silly me, it is an abstraction leaking here. So, I take liberty to remove unnecessary WHERE in extended index.

[3n-mb]

Original splitwise/group.sql is

-- Write the name of the group in the title of the page
SELECT 'title' as component, name as contents FROM expense_group WHERE id = $id;


-- Handle the form to add a member to the group (we do it at the top of the page to see it right away)
INSERT INTO group_member(group_id, name)
SELECT $id, :new_member_name WHERE :new_member_name IS NOT NULL;

-- List of members of the group
SELECT 'list' as component, 'Members' as title;
SELECT name AS title FROM group_member WHERE group_id = $id;

-- Form to add a new member to the group
SELECT 'form' as component, 'Add a member to the group' as validate;
SELECT 'Member Name' AS 'label', 'new_member_name' AS name;

SELECT 'title' as component, 'Expenses' as contents

-- Form to add an expense
SELECT 'form' as component, 'Add an expense' as title, 'Add' as validate;
SELECT 'Description' AS name;
SELECT 'Amount' AS name, 'number' AS type;
SELECT 'Spent By' AS name, 'select' as type,
    json_group_array(json_object("label", name, "value", id)) as options
FROM group_member WHERE group_id = $id;

-- Insert the expense posted by the form into the database
INSERT INTO expense(spent_by, name, amount)
SELECT :"Spent By", :Description, :Amount WHERE :Amount IS NOT NULL;

-- List of expenses of the group
SELECT 'card' as component, 'Expenses' as title;
SELECT expense.name as title,
    'By ' || group_member.name || ', on ' || expense.date as description,
    expense.amount || ' €' as footer,
    CASE
        WHEN expense.amount > 100 THEN 'red'
        WHEN expense.amount > 50 THEN 'orange'
        ELSE 'blue'
    END AS color
FROM expense
    INNER JOIN group_member on expense.spent_by = group_member.id
WHERE group_member.group_id = $id;

-- Show the positive and negative debts of each member
SELECT 'chart' AS component, 'Debts by Person' AS title, 'bar' AS type, TRUE AS horizontal;
SELECT member_name AS label, is_owed AS value FROM individual_debts
WHERE group_id = $id ORDER BY is_owed DESC;

is a little longer, but we've developed most things in the first example. So, with extension to SQL vocabulary it may read like

RENDER HTML title WITH
    SELECT name AS contents FROM expense_group WHERE id = $id;

RENDER HTML list WITH
    title = 'Members',
    SELECT name AS title FROM group_member WHERE group_id = $id
    RENDERING INTO HTML item;

RENDER HTML form WITH
    validate = 'Add a member to the group'
COLLECTING
    new_member_name FROM HTML input WITH label = 'Member Name'
DO
    INSERT INTO group_member(group_id, name) VALUES ($id, :new_member_name)
REFRESHING PAGE;

RENDER HTML title WITH contents = 'Expenses';

RENDER HTML form WITH
    title = 'Add an expense',
    validate = 'Add'
COLLECTING
    'Description' FROM HTML input,
    'Amount' FROM HTML input WITH type = 'number',
    'Spent By' FROM HTML input WITH
        type = 'number',
        SELECT json_group_array(json_object("label", name, "value", id)) AS options
        FROM group_member WHERE group_id = $id
DO
    INSERT INTO expense(spent_by, name, amount) VALUES (:"Spent By", :Description, :Amount)
REFRESHING PAGE;

RENDER HTML cards_grid WITH
    title = 'Expenses',
    SELECT expense.name AS title,
        'By ' || group_member.name || ', on ' || expense.date as description,
        expense.amount || ' €' as footer,
        CASE
            WHEN expense.amount > 100 THEN 'red'
            WHEN expense.amount > 50 THEN 'orange'
            ELSE 'blue'
        END AS color
    FROM expense
        INNER JOIN group_member on expense.spent_by = group_member.id
    WHERE group_member.group_id = $id
    RENDERING INTO HTML card;

RENDER HTML chart WITH
    title = 'Debts by Person',
    type = 'bar',
    horizontal = TRUE,
    SELECT member_name AS label, is_owed AS value FROM individual_debts
    WHERE group_id = $id ORDER BY is_owed DESC;

[3n-mb]

I took some liberty in using card_grid and card for things inside, since manual says:

The "card" component
A grid where each element is a small card that displays a piece of data.

[3n-mb]

Without specific connection to DO for RENDER-ed form element, INSERT statements are just floating around. I even didn't immediately recognize that there were two of them in the second example.

[lovasoa]

RENDER HTML cards_grid WITH
    title = 'Expenses',
    SELECT expense.name AS title,
        'By ' || group_member.name || ', on ' || expense.date as description,
        expense.amount || ' €' as footer,
        CASE
            WHEN expense.amount > 100 THEN 'red'
            WHEN expense.amount > 50 THEN 'orange'
            ELSE 'blue'
        END AS color
    FROM expense
        INNER JOIN group_member on expense.spent_by = group_member.id
    WHERE group_member.group_id = $id
    RENDERING INTO HTML card;

I think you are touching on why full selects are actually needed here.

I understand your initial reaction, but SQLPage is and will stay sql only. it is not about creating a new language.

What I can suggest, once again, is that you actually try SQLPage in its current form. Then, if you really want to use a different syntax you can develop a develop a compiler from your custom language to SQL, document your language, write tutorials about how to use it, and support users who need help with it.

[3n-mb]

I am not just merely touching, I am using a full SELECT that is supposed to run in db, return one row, meaning name->value pairs that get picked for using in populating named template, like SQLPage is already doing.

Note, with SQL addition we have here full, complete, total SELECT, undisturbed by rendering, followed by RENDERING. This ordering of unencumbered self-contained functionality helps to bring up the most important part: flow of information.
And, isn't RENDERING after SELECT is very much in SQL style?

[3n-mb]

Now, let's come back to a phrase you keeping repeating:

I understand your initial reaction, but SQLPage is and will stay sql only. it is not about creating a new language.

Confusion due to overloading SELECT is what I personally experience. And I am a seasoned pro, which can be noticed from an exercise above.

Primary use case of SQLPage is (1st) once in a blue moon write a little helper app, then (2nd) never open the code but use the app for like five years. By that time world changes, and (3) something needs to change.
At point (3) person has to rebuild a mental model of the code. Code better be explicit and self documenting, and showing data flow to help reconstruction of mental model in human head.

It is the same scenario with macros in spreadsheets and offices in general.
I want to refer you to your own comment about VBA, somewhere in discussion, or site. What didn't you like about it?
But wait, even M$ now has, ... TypeScript, yes, TypeScript for writing macros.

I'll ask around, if a fleshed out extension to SQL is anywhere close to "creating a new language". I personally don't think so, but I am gonna run a poll.

[3n-mb]

For my taste, extended SQL captures data flow and thus core business logic, keeping it explicit.
sqlparser should probably be adjusted to parse new commands. And SQLPage server can use its output producing the same overall package. Well, may be with an addition of server events. By the way, how and are they used currently by SQLPage?

[3n-mb]

Just looked in "Loops?" discussion: #306 (reply in thread)
M$ has extended SQL. But, oh boy, it ain't pretty.

[3n-mb]

Let's also observe that DO extended syntax may

• have an optional IN DATABASE db1
• be followed by DO IN DATABASE dbN
  Naturally, all db's should be defined in the overall server to have respective connections.

[3n-mb]

Cursory look into dialect-related file in https://github.com/sqlparser-rs/sqlparser-rs/tree/main/src/dialect
suggests that dialects are separated by things like quotes and not vocabulary.

Cursory look into https://github.com/sqlparser-rs/sqlparser-rs/blob/main/src/ast/mod.rs
points to https://github.com/sqlparser-rs/sqlparser-rs/blob/main/src/ast/query.rs
where one can add all new possible queries.

sqlparser is a very nicely structured library.

[3n-mb]

I really think you are on a goldmine. And as with any mine, there is an initial soil digging phase.

[3n-mb]

May I point attention to how users of SQLPage already do work around of "implicitly closing tags".
Specific file is https://github.com/DSMejantel/Ecole_inclusive/blob/38b200f195fffec82e08260f1cd7195185eb7de5/aesh_edit.sql#L52
from line 52 down, note:

• on line 56 statement ends, but it is a statement that "opens form tag"
• lines 58 to 62 all have indentation that is not expected in SQL(!), but is required by human to somehow communicate structure, that all those rendering selects belong to somewhere else.
• lines 63 to 65 have even more indentation.

May be you should start to do the same in examples? But it has this nagging "why indentation after closing semicolumn ; ".

[3n-mb]

May I suggest that I am not the only one confused.
May I also suggest that those guys just don't have an option to look into Rust to correct the tool, instead of bending their own ways.

[3n-mb]

Rendering into list is a very common pattern, so, a little help can be extended from SQL extension to allow user first write data source select, concentrating on it, and tail it with rendering.
First example with events will change in list part to

RENDER HTML form WITH
    title = 'New shared expense account',
    validate = 'Create the shared expense account!'
COLLECTING
    shared_expense_name FROM HTML input WITH label = 'Account Name'
DO
    INSERT INTO expense_group(name) VALUES (:shared_expense_name)
TRIGGERING EVENT expenses_updated;

SELECT name AS title, 'group.sql?id=' || id AS link
FROM expense_group
REFRESHED BY EVENT expenses_updated
RENDERING INTO HTML CHILDREN item OF list;

Commands follow data flows' patterns:

• select data and display
• display place for collecting data, to do something with it.