Silent sqlpage.persist_uploaded_file() option?

[matthewlarkin]

Hi @lovasoa,

Wow, great new feature: persist_uploaded_file()!

It seems an ideal use case is gallery stuffing (where you're just stuffing some files somewhere, storing the file paths in the database) but not quite right for updating image fields. For instance, if we have an update-profile form:

update-profile-form.sql

select 'form' as component,
  'update-profile.sql' as action;

  select
    'id' as name,
    'hidden' as type;

  select
    'first_name' as name;

  select
    'image' as name,
    'file' as type,
    'image/png, image/jpeg' as accept;

update-profile.sql

set image_path = sqlpage.persist_uploaded_file('image');
update profiles set first_name = :first_name where id = :id;
update profiles set image = image where $image_path is not null and id = :id;

This works nicely the first time, but if we don't supply an image (say we only want to update the name), we get a SQLPage error along the lines of "No uploaded image". Perhaps there is there a way to avoid running the sqlpage.persist_uploaded_file() function if no file is posted?

I know we want to avoid turning SQLPage into a programming language 😄 so maybe the function can instead return silent if no file is provided, as with sqlpage.uploaded_file_path() does. Related, I believe sqlpage.uploaded_file_mime_type() also fails if nothing is provided.

[lovasoa]

You can redirect the user before the call to persist_uploaded_file using the redirect component if there was no uploaded file.

[lovasoa]

select 'redirect' as component, '/failure' as link where sqlpage.uploaded_file_path('file') is null;
set response = sqlpage.persist_uploaded_file('file');
select 'redirect' as component, '/success' as link;

[matthewlarkin]

Hmm... @lovasoa, I'm still getting redirected to '/failure' here.

When I do

select 'debug' as component,
	sqlpage.uploaded_file_path('file') as file_path;

-- select 'redirect' as component, '/failure' as link where sqlpage.uploaded_file_path('file') is null;
-- set response = sqlpage.persist_uploaded_file('file');
-- select 'redirect' as component, '/success' as link;

I get

{"component":"debug","file_path":"/var/folders/sr/m6xhhlls7fbcv7gpjr3h6zmh0000gp/T/.tmpt94b1h"}

Maybe an adjustement to pseudofunctions is required? I issued a pull request.

// . . .
  StmtParam::UploadedFilePath(x) => {
    request
      .uploaded_files
      .get(x)
      .and_then(|x| {
        if x.file.exists() {
          x.file.path().to_str()
        } else {
          None
        }
      })
      .map(Cow::Borrowed),
  }
// . . .

[matthewlarkin]

Ahhh.... I think I see what's going on. It seems the temporary upload path is being prepared ahead of time as a blank file, prior to any actual uploading, which is confusing the sqlpage functions downstream.

This still returns 'true' even if no file was posted:

upload.sql:

set file_exists = sqlpage.exec('./sqlpage/sh/file_exists', sqlpage.uploaded_file_path('file'));

select 'text' as component, $file_exists as contents;

-- select 'redirect' as component, '/failure' as link where sqlpage.uploaded_file_path('file') is null;
-- set response = sqlpage.persist_uploaded_file('file');
-- select 'redirect' as component, '/success' as link;

Where file_exists contains:

#!/bin/bash

test -f "$1" && echo "true" || echo "false"

However, when I change file_exists to this:

#!/bin/bash

[ ! -s "$1" ] && echo "File is empty or does not exist" || echo "File is not empty"

...we get "File is empty or does not exist" (which is what we're after 💪)

---

@lovasoa, do you know where this upload path prep might be taking place or ideas to return null in the sql.

[lovasoa]

Oh, I see, you are talking about the case when the user did submit the form that contained a file upload field, but left it blank. Not the case of the user loading the URL of the form target directly, or submitting another form that did not contain a file input.

By default, the browser will send an empty file, with a mime type of application/octet-stream.

You can use that to trigger the redirection:

select 'redirect' as component, '/upload_form.sql' as link
where sqlpage.uploaded_file_mime_type('file') NOT LIKE 'image/%';

I'll add this to the official "image gallery" example.

[matthewlarkin]

Oh, that's great Ophir! Thank you for pointing that out.

SQLPage is such an incredible tool, and has really gotten me thinking more database-first! Thank you for your work on this. Looking forward to sharing a few projects I'm cooking up with it soon 🙂

[matthewlarkin]

You know, @lovasoa, I do still wonder if having a fail silently option would be helpful here with uploads, particularly when handling multiple uploads.

For instance, say we have an update form that contains two image files (logo and banner), and we're happy with the logo but we want to update the banner. Right now, we have to call sqlpage.redirect() prior to any attempt to upload a null value to avoid crashing. So it would seem we're limited to one upload file per request (as we'll inevitably run into a redirect if any of the values are null).

I do wish there were a way to:

update globals set value = sqlpage.persist_uploaded_file('logo') where [...] and :logo is not null;
-- or
update globals set value = sqlpage.persist_uploaded_file('banner') where [...] and sqlpage.uploaded_file_mime_type('banner') LIKE 'image/%';
-- or
update globals set value = coalesce(sqlpage.persist_uploaded_file('banner', 'fail-silently'), (select [...] )) where [...];

Currently, I don't see see an elegant way to handle this in one file (I would love to, given the wonderful new sqlpage.request_method() function 🤩).

---

The only way I can think to remedy this is to perhaps have our form endpoint make curl requests to another endpoint specifically set up to handle image uploads, one at a time. I haven't tried that yet, but that seems like overkill ...or maybe useful across many projects if I can nail the design.

I wonder if you've run into this yourself or have other thoughts?

--

Thanks as always! 🙏

[lovasoa]

Thinking about it a little more... Shouldn't sqlpage.persist_uploaded_file just return NULL when there is no file?

[matthewlarkin]

Yes! That would make a one file setup very straightforward 🙌

-- POST request

[...]

update users set
    profile = coalesce(sqlpage.persist_uploaded_file('logo'), logo),
    banner = coalesce(sqlpage.persist_uploaded_file('banner'), banner),
    [...]
    where id = $id and sqlpage.request_method() = 'POST';

-- GET request

select 'shell' as component,
    [...];

[...]

[lovasoa]

@matthewlarkin , the problem is finally fixed :)

Change available on main, will be in 0.23

[lovasoa]

You can try it already in lovasoa/sqlpage:main on docker