Code workflow

[rrad0812]

Hi there
I thought I knew SQL but ... I am not a clear order of command execution, especially if there are several files.
I will keep for example on Todo app.

Can you explain in one general way "Code Workflow"
Radosav

[lovasoa]

Hello and welcome to SQLPage !

Did you have a look at the official website tutorial and the GitHub README ?

If you did and find something is missing or confusing, could you please point to it ? It's sometimes hard to see what information people need the most to get started, so your feedback is very welcome !

About the order of execution

1. Your user loads a page on your website, for instance https://example.com/todo.sql?todo_id=1
2. SQLPage reads and parses the file named todo.sql in your website root folder.
3. SQLPage executes the queries in the file one by one, in order, from the top of the file to the bottom
4. If one of the queries contains parameters, such as $todo_id, for instance, SQLPage will read request parameters and bind them to the parameters in the query. In this example, it will bind '1' to $todo_id.
5. SQLPage will not load or execute any other .sql file unless you explicitly ask it to.

We have a diagram illustrating the process in the "readme" file

About the Todo application

The source of the todo application is here: https://github.com/lovasoa/SQLpage/tree/main/examples/todo%20application

I will update its README to make it clearer exactly what files are involved and what each one does.

[rrad0812]

Good, this work as every other web.
Let's look at your Delete.SQL

delete from todos
where id = $todo_id and $confirm = 'yes'
returning
    'redirect' as component,
    '/' as link;

select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

select
    'alert' as component,
    'red' as color,
    'Confirm deletion' as title,
    'Are you sure you want to delete the following todo item ?

> ' || title as description_md,
    '?todo_id=' || $todo_id || '&confirm=yes' as link,
    'Delete' as link_text
from todos where id = $todo_id;`

What do that last line?

[lovasoa]

I added multiple comments to delete.sql

-- We find the todo item with the id given in the URL (/delete.sql?todo_id=1)
-- and we check that the URL also contains a 'confirm' parameter set to 'yes' (/delete.sql?todo_id=1&confirm=yes)
-- If both conditions are met, we delete the todo item from the database
-- and redirect the user to the home page.
delete from todos
where id = $todo_id and $confirm = 'yes'
returning -- returning will return one row if an item was deleted, and zero rows if no item was deleted
    'redirect' as component, -- if one item was deleted, we redirect the user to the home page, and skip the rest of the page
    '/' as link;

-- If we are here, it means that the delete statement above did not delete anything
-- because the confirm parameter was not set to 'yes'.

-- We display the same header as in other pages, by including the shell.sql file.
select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

-- When the page is initially loaded, it will contain a todo_id parameter
-- but no confirm parameter, so the delete statement above will not delete anything
-- and the 'redirect' component will not be returned.
-- In this case, we display a confirmation message to the user.
select
    'alert' as component, -- an alert is a message that is displayed to the user
    'red' as color,
    'Confirm deletion' as title,
    'Are you sure you want to delete the following todo item ?

> ' || title as description_md, -- we include the text of the todo item in the markdown confirmation message
    '?todo_id=' || $todo_id || '&confirm=yes' as link, -- When the user clicks on the 'Yes' button, the page will be reloaded with the confirm parameter set to 'yes', so that the delete statement above will delete the todo item
    'Delete' as link_text
from todos where id = $todo_id; -- finds the todo item with the id given in the URL

https://github.com/lovasoa/SQLpage/blob/main/examples/todo%20application/delete.sql

Let me know if it's clearer

[rrad0812]

Ok,
It is clear that you construct a link to delete records from the DB, but it is very unnatural to mix the SQL and interface commands.
This means that you previously pre-process SQL, remove parts related to the interface, and then send it to DB?
Do you miss a star after SELECT?

Thank you for very fast and extensive comment?
Radosav

[lovasoa]

No, nothing is removed or changed in the sql queries.

The detailed step by step explanation of the delete process is as follows:

• From the index.sql page, the user clicks the 'Delete' button on a todo item
• It loads the page /delete.sql?todo_id=7 (without the confirm=yes parameter)
  • the delete statement is sent to the database and executed. SQLPage has bound the values to URL query parameters, so we have
    • $todo_id bound to '7', and
    • $confirm bound to NULL (since there was no confirm parameter in the url)
  • the database evaluates the where id = $todo_id and $confirm = 'yes' condition to FALSE
  • so it deletes nothing, and returns nothing
  • SQLPage receives no row back from the database, it continues processing normally
  • it executes the select 'dynamic' ... query, which itself requires executing the shell.sql file. The result of this is a row that contains component=dynamic and properties={"component": "shell", "title": "My Todo App", ... }
  • it renders the page header with the application header and the top bar following the results of the query
  • it sends to the database the last query: select 'alert' as component, ... from todos where id = $todo_id it binds the parameters like before
    • $todo_id bound to '7'
  • the database returns a single row, containing component=alert, description_md=Are you sure [...] [the title of the todo item with id 7], ...
  • SQLPage returns the the alert component with its contents to the browser
• The user sees the confirmation alert and clicks the 'Delete' button
• The page is reloaded, this time with the URL /delete.sql?todo_id=7&confirm=yes
  • the delete statement is sent to the database and executed like last time. But this time SQLPage has bound the values to the new URL query parameters,
    • $todo_id bound to '7', (like before)
    • $confirm bound to 'yes' (since there is now a confirm parameter in the url)
  • the database evaluates the where id = $todo_id and $confirm = 'yes' condition to TRUE
  • so it deletes the todo item with id 7 and, as instructed by the returning clause, returns a single row containing component=redirect, link=/
  • SQLPage receives the row back from the database, and immediately returns sends a 302 redirect response to the browser, redirecting the user to the / page.
  • The following queries are not executed, as the page is redirected before they are processed.

I updated the readme of the todo application: https://github.com/lovasoa/SQLpage/tree/main/examples/todo%20application#deletesql

[lovasoa]

If you find this way of doing complicated, you could also have a separate page with only the delete statement, and link to that page instead of linking to the current page from the confirmation button, with '/perform_delete.sql?todo_id=' || $todo_id as link

[lovasoa]

I updated the TODO application README with some detailed explanations: https://github.com/lovasoa/SQLpage/tree/main/examples/todo%20application#todo-app-with-sqlpage

including the following diagram:

[rrad0812]

Thank you.
I have questions yet :)

The page is reloaded, this time with the URL
/delete.sql?todo_id=7&confirm=yes

1. Why this page is reloaded??? I don't see it in your code.
2. I still don't see the reason for the last line:
   from todos where id = $todo_id;

For the rest I would say it was clear to me!

Best all, Radosav

[lovasoa]

1. The page is reloaded because the user clicks on the link contained in the confirmation message. This is a standard web link, with an address that starts with '?', so it links to the page itself, with different url parameters.
2. the last line is just the continuation of the select statement started in the lines above. The 'from' part of the select is just written on a separate like for readability. We select from the todos table in order to be able to display the title of the todo item in the deletion warning message.

Overall, I suggest you download the example and play with it locally to better understand how it works.

[rrad0812]

OK. Thank you.
I think it is enough for now.
I downloaded all of examples, and TODO app was the first attempt in understanding SQLPage.

I actually try to find something like this for a long time. It seems to me that I have already tried Sklpage once, but I was done for over complicated. Now with your clarifications and work I will try to decide. I'll probably have to bother you again around the front, but it seems to me that I have seen JavaScript somewhere, so we will see.

Many thanks for the help, SQLPage alone doesn't make sense to praise. He works alone it for himself.

Greeting, Radosav.