SQL file inclusion

[gourk]

Hi,

I have really often the same begin in my sql file: session managment, check GET/POST inputs, shell, backward navigation, ...
When I need to make a change un theses parts, I have to go through all my files. I can find/replace of course but I can do mistake doing that.

I wonder if you has already considered to implement file inclusion. By that, I mean allowing a SQL file to call another zone like a directive "#include /check_usersession.sql"

Thanks for your work so far, sqlcode is amazing. 🚀

[lovasoa]

Hi !
I understand the pain. This issue has been raised before here: #20
The current recommended solution is to simply store the common boilerplate directly in the database, and then start your files with

select * from headers;
select * from redirect_if_user_is_not_connected(sqlpage.cookie('session'));
select * from shell;

Since it's the second time this comes up, I'll see how I can update the documentation and examples to make the pattern easier to find.

And if this is still not enough, then I may add a sqlpage.include_file('...') function, but I'd rather avoid using anything that is not standard SQL, like adding #include...

[demogit-code]

select * from redirect_if_user_is_not_connected(sqlpage.cookie('session'));

i didn't find this one elsewhere, redirect_if_user_is_not_connected is a function then?
what would it look like more or less?

or would it make more sense to have a table redirect(if,then,to):

• if: reason for redirect
• then: action
• to: redirect url

not sure how to set it up

[lovasoa]

Hello ! No, redirect_if_user_is_not_connected is not an existing sqlpage function. Reading my own message above, I realize it was not clear that this is just a suggested pattern.

I was talking about writing your own redirect_if_user_is_not_connected table-valued function, that takes a string and returns a table with a component column set to redirect. Something like that:

In the database schema :

create table user_sessions(user_id int, session_token text);

-- postgres example
create or replace function redirect_if_user_is_not_connected(session_str in text)
  returns table(component text, link text)
  language sql
as $body$
  select 'redirect'::text as component, 'login.sql'::text as link
    where not exists (select 1 from user_sessions where session_token = session_str)
$body$;

and then, in your sql files:

select * from redirect_if_user_is_not_connected(sqlpage.cookie('session'));

try it online: http://sqlfiddle.com/#!17/0c257/3

[demogit-code]

tnx, the if-then table approach sounded nice allowing for more rules, maybe even role dependent, but i guess then where would have to be (dynamic) sql inside the if column..;

whereas with a function you would not need that

[demogit-code]

in general i think this table approach only works for simple setups,

i mean using a select * from shells/headers/..;

if we take the shell from the documentation example, it has for example 2 javascript columns

• https://sql.ophir.dev/documentation.sql?component=shell#component

(so a select * from shells would not be able to show that)

whereas column names inside a table shell should be unique,
so you would have to explicitly rename all the columns in the calling query.

(maybe using arrays?)

[lovasoa]

Yes, duplicated column names are just a shortcut to generate arrays in sqlpage with a less cumbersome syntax.

[demogit-code]

this seems to work with text like

SELECT 
    'shell' AS component, 
    ARRAY['title1','title2'] AS title

but not with json?

SELECT 
    'shell' AS component, 
    ARRAY['title1','title2'] AS title
    ARRAY[json('{"link":"../d1/index.sql","title":"d1"}'),json('{"link":"../d2/index.sql","title":"d2"}')] as menu_item

[lovasoa]

The way you are mixing pg arrays and json objects is unusual, but it should work, I'll look into it.

In the meantime, if I were you, I would just use json everywhere, for arrays as well as for objects.

SELECT 
    'shell' AS component, 
    json('[
         {"link":"../d1/index.sql","title":"d1"},
         {"link":"../d2/index.sql","title":"d2"}
    ]') as menu_item

[demogit-code]

a reason for this construct could be: having a shell table with some array columns,
so that the whole shell menu could be generated from the content of table with
the select * from shell;

using json for the dropdown,
but maybe array for the horizontal dimension of the menu

eg

SELECT 
    'shell' AS component, 
    'title' as title,
    ARRAY['t1 ','t2 ', 'json object for dropdown'] as menu_item

or all json objects,
this seems to render, but all menu_items seem to point to the same link,
which makes it pointless :)

[lovasoa]

You can do

SELECT 
    'shell' AS component, 
    'title' as title,
    json('[ "t1", "t2", { ... } ]') as menu_item

[lovasoa]

I am closing this as the functionality is now supported with sqlpage.run_sql