Dynamic HTML content

Plugins

Chlipouni     5 months ago

Hi,

First of all, I want to thank you for making this amazing open source project.

I made a personal site with Oracle APEX, and I am trying to migrate it with Saltcorn and PostgreSQL.

I have a stored function in my PostgreSQL database that generates SVG code.
Is there a way to call this function from saltcorn ?  

For now, I did the following steps to be able to show the result of SVG in a saltcorn web page :

  • Add a new text column in the associated table
  • Generate and store the SVG code in this new column
  • Activate the HTML field type plugin
  • Set the field view as "unsafeNotEscaped

I would like to avoid storing the SVG code in a database column.
Do you think that using a calculated field that calls a PostgreSQL stored function (with the row id as a parameter) is something doable via a new plugin ?

Thanks



tomn      5 months ago

I have an idea for how to do this. It assumes you are already comfortable using psql or have some other way of running SQL commands (since you already have a stored procedure ... we are definitely not in nocode land anymore!)

To do this, you would have to run postgresql 12 or 13. Not sure how you were hosting, I think the DigitalOcean droplets are postgresql 12.

Create your table as normal as you probably already have with Saltcorn, adding all of the "normal" fields

Now log into your database with psql. We are trying to add a new generated column (https://www.postgresql.org/docs/12/ddl-generated-columns.html) to the existing table. There are various opinions on the Internet about whether this is possible, But this blog post has an alter table add generated column command: https://pgdash.io/blog/postgres-12-generated-columns.html. If it is not possible to add a generated column to an existing table, you can try to drop the table and create a new one with the same name, all the same fields, plus the new generated column. You probably want to make this not stored.

Finally, you need to insert a new row into the table _sc_fields. Have a look at the other row on the table, you will want to use the type = "HTML", set the table_id to the same as the other fields in that table (i.e. appropriate row in _sc_tables). Otherwise the column should look as normal as possible for Saltcorn, we are tricking Saltcorn into thinking that this is a completely normal column

As long as you do not use this new field in any Edit views, then it should work! You can write to the other fields, Saltcorn sdhould not put in any values for this new fake column if it is not a field in a form


Chlipouni      4 months ago

Hi tomn,

  Thanks a lot for the quick answer.

  In my mind, nocode is a good strategy for 95% of the use cases, but you should offer lowcode solutions for the 5% needs that cannot easily be achieved with nocode.
  One possible strategy is to use the power of the underlying database engine.

  In my specific case, I can't use a generated column, because my function is not immutable (it uses embedded sql queries).

  I managed to do it in the following way :

  • I created a database view that calls the plpgsql function to compute the complex column
  • I used the "Discover tables" feature of saltcorn to include this view (as a table)
  • I updated the field type as "HTML" in the "_sc_fields" table
  • I created a saltcorn view that uses this new table

  And the result is :

Complex view example

  For your information, once the view is discovered as a table, there is no way to update it in the saltcorn web pages.
  It should be a good strategy to allow the management of database views for read-only complex reports in saltcorn.

Keep up the great work !

Thanks



Sign up to post a reply