todo application

Todo app with SQLPage

This is a simple todo app implemented with SQLPage. It uses a SQLite database to store the todo items. (See the PostgreSQL version)

It is meant as an illustrative example of how to use SQLPage to create a simple CRUD application.

Structure

index.sql

This is the main file of the application. It will be loaded when the user visits the root of the application (http://localhost:8080/ when running this example locally).

In order, it uses:

• the dynamic component to load the shell.sql file that will be used at the top of every page in the application to create a consistent layout and top bar.
• the list component to display the list of todo items.
• the button component to create a button that will redirect the user to the todo_form.sql page to create a new todo item when clicked.

todo_form.sql

This file is used to create a new todo item or edit an existing one.

It uses:

1. the redirect component to redirect the user back to the index.sql page after the form is submitted.
2. the dynamic component to load shell.sql to create a consistent layout and top bar.
3. the form component to create a form with fields for the title and description of the todo item.

The order of the components is important, as the redirect component cannot be used after the page has been displayed. It is called first to ensure that the user is redirected immediately after submitting the form. It is guarded by a WHERE :todo_id IS NOT NULL clause to ensure that it only redirects when the form was submitted, not when the page is initially loaded by the user in their browser.

delete.sql

This file is used to delete a todo item.

It contains a delete statement guarded by a WHERE $confirm = 'yes' clause. So, the delete is not executed when the page is initially loaded, but only when the user clicks the "Yes" button, which contains a link pointing to the same page with the confirm=yes query parameter.

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.

shell.sql

This file is not meant to be accessed directly by the user (it would display an empty page with only the top bar).

But it is included from all the other pages to call the shell component with the exact same parameters on every page.

It is included everywhere using the dynamic component and the sqlpage.run_sql function.

Running the example

To run the example, simply download the latest SQLPage release and run it from the root folder of the example.

SQLPage features used

This example is meant to illustrate many of the common features of SQLPage.

Components

• list
• button
• form
• redirect
• shell
• timeline
• dynamic

Functions

• sqlpage.run_sql