Question about the modal component

[monsieurmerle]

I have discovered the 'modal' component, and have started to try using it to make my webapp visually less dense. I have then tried to embed froms.

They do display correctly, but when I try to send a query through the form, it doesn't actually go through. It sort of makes sense that it doesn't work as if I had opened the form in its corresponding page. I have copied the code almost straight from the website documentation and have done minimal alterations for visuals.

I don't know how to actually send the query through the modal component, any guidance with that ?

Any help is welcome

[lovasoa]

Hi ! Can you give a clean minimal reproduction of your issue ?

[monsieurmerle]

So, I had a homepage with a button that sent me to a second page which has the form on it. I have used the modal component to embed the whole form page directly into the homepage. When I fill the form and press the validate button, the embedded form updates but the table is not updated nor filled with the new entry.

[lovasoa]

Can you make a minimal reproducible example of your issue and post the code here ? Not your own application, just the smallest possible imaginable sqlpage application that reproduces your issue. That will make it easier to help you.

https://stackoverflow.com/help/minimal-reproducible-example

[monsieurmerle]

This is my attempt at a minimal reproducible example. It should be enough to demonstrate the issue.

I have used the code component in the GitHub text editor. I hope I did not do it wrong; I am very new to this, and GitHub scares me.

The first file is the page containing the form, _repro_1:

CREATE TABLE IF NOT EXISTS clients_company(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    next_maint DATE,
    created_at DATE DEFAULT CURRENT_DATE
);
SELECT 'form' AS component,
    'multipart/form-data' AS enctype;
SELECT "username" AS name;
SELECT "date_m" AS name,
    'TIMESTAMP' AS type;
INSERT INTO clients_company(username)
SELECT :username
WHERE :username IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM clients_company
      WHERE username = :username
  );
UPDATE clients_company
SET next_maint = :date_m
WHERE username = :username
  AND :date_m IS NOT NULL;
SELECT 'table' AS component;
SELECT
    username AS Utilisateur,
    next_maint AS Date_maintenance,
    created_at AS Date_de_creation
FROM clients_company;

This form works by itself. You enter the name of the user whose maintenance date you want to modify, along with the new date, and it updates the list below. The page needs to be refreshed for the update to be displayed.

In my actual code, both the table creation and the user creation are on separate dedicated pages. I moved them into this page to make the example functional.

The second file is another page named _repro_2, reduced to only the embed component for this example:

SELECT
    'modal' AS component,
    'embed_maintenance' AS id,
    'repro_1.sql?_sqlpage_embed' AS embed;
SELECT
    'button' AS component;
SELECT
    '#embed_maintenance' AS link;

To test this, first go to _repro_1 and try the form. It works: it either creates a new entry or updates an existing one, depending on whether the username already exists.

Then try the _repro_2 code. It does display the _repro_1 form inside the embedded view, but it does not actually update or create entries in the clients_company table.

I hope this is enough to clearly explain the issue.

[lovasoa]

Thank you for the reproduction, this makes it very clear what you are talking about.

So, this is what is happening:

repro_1 does not have any action property in its form component, so, by default, it submits the form to the current page. When you are visiting the page from repro_1, then the form is submitted to repro_1, where it is handled, it all works.

When you embed repro_1 inside repro_2, then the current page is repro_2, so it submits the form to repro_2, which does not handle it at all.

One cleaner way to do it would be:

repro_1.sql

CREATE TABLE IF NOT EXISTS clients_company(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    next_maint DATE,
    created_at DATE DEFAULT CURRENT_DATE
);
create unique index if not exists clients_company_username
on clients_company(username); 

SELECT 'form' AS component,
    '/repro_handler' as action,
    'multipart/form-data' AS enctype;
SELECT 'username' AS name;
SELECT 'next_maint' AS name, 'date' AS type;

 

SELECT 'table' AS component;
SELECT
    username AS Utilisateur,
    next_maint AS Date_maintenance,
    created_at AS Date_de_creation
FROM clients_company;

repro_handler.sql

INSERT INTO clients_company(username, next_maint)
values (:username, :next_maint)
on conflict (username)
do update set next_maint = :next_maint;

select
 'redirect' as component,
 'repro_2#embed_maintenance' as link;

note

I also cleaned up your single date per username constraint.

The important idea is that the database should enforce the rules that must always be true.

In this case, there should be only one maintenance date per username belongs in the schema because it is a data invariant. This kind of invariants should be enforced using a UNIQUE constraint, unique index, or primary key. Only form-handling concerns belong in the sql data insertion logic. For instance, here, is the current user allowed to make the given update would be correctly handled in repro_handler.sql.

Doing this in SQL has a few advantages:

• it works no matter where the data comes from: this form, another page, an import script, or a future API;
• it avoids race conditions where two requests both check that a username does not exist, then both insert it;
• it lets SQLite use ON CONFLICT, which is simpler faster and safer than manually doing INSERT then UPDATE;
• it documents the data model directly in the database.

Some good resources:

• SQLite constraints: UNIQUE, NOT NULL, CHECK, PRIMARY KEY, and FOREIGN KEY https://www.sqlite.org/lang_createtable.html#constraints
• SQLite ON CONFLICT / UPSERT : https://www.sqlite.org/lang_upsert.html
• SQLite foreign keys, for when your data starts referencing other tables: https://www.sqlite.org/foreignkeys.html
• PostgreSQL documentation on constraints, even if you are using SQLite. The explanations of data integrity are very clear and apply conceptually to SQL databases in general: https://www.postgresql.org/docs/current/ddl-constraints.html
• Use the index, Luke! https://use-the-index-luke.com/

[monsieurmerle]

Thank you,
I have tried with the code you give for the repro example and it works. Now begins the integration back into the main code, hoping it works quickly and I don't mess it up even more ! (backups shall be made beforehand)

[monsieurmerle]

Unfortunately I have tried implemeting the fixes you gave me, but I am facing a new issue trying to make it work. It turns out my clients_company table has other NOT NULL attributes (mainly a password stored as hashed and unhashed) and when I try to use your handler code, it gives me this error message :

`In "maint_handler.sql": The following error occurred while executing an SQL statement:
error returned from database: (code: 1299) NOT NULL constraint failed: clients_company.password_unhashed

The SQL statement sent by SQLPage was:
INSERT INTO clients_company (username, next_maint) VALUES (CAST(?1 AS TEXT), CAST(?2 AS TEXT)) ON CONFLICT(username) DO UPDATE SET next_maint = CAST(?2 AS TEXT);
maint_handler.sql: lines 1 to 4`

It so happens that the table definition and embed components are on the same page, while the form is on another. If need be I will try to modify the previous minimal reproduction to explain it.
Here is the maint_handler code :

`INSERT INTO clients_company(username, next_maint)
values (:username, :next_maint)
on conflict (username)
do update set next_maint = :next_maint;

select
 'redirect' as component,
 'company_admin#embed_maint' as link;`

My guess is that for some reason the program thinks that I have entered a new username and I want to create a new user, which it can't due to the lack of password linked to the user.

[lovasoa]

If this form is not supposed to insert new lines in your table, then you should not have an INSERT at all, just an UPDATE. And the form field should probably be a dropdown, then, not a freeform text field.

And as a side note, you should not store cleartext passwords for your users at all.

[monsieurmerle]

I will try to implement the dropdown part, I hadn't found it yet in the documentation.

As for the cleartext password, I know it should not be done, however both my supervisor and the company director are aware of this and do not consider it an issue as no sensible information is stored in the database (both about us and about the customers). I guess I can get rid of the need for it in the further versions of the app, I do like having the possibility to check if the password change works in the prototype stage for now.