sqlpage.run_sql is just great 👏

[matthewlarkin]

Hey SQLPagers,

Just came by to show my appreciation for the new sqlpage.run_sql function for it's help with reusability making my code more readable. In particular, I've enjoyed using it lately to set global variables and filters.

Global variables

Set global variables at the top to help you write more readable code, obscuring complexity in separate SQL files.

-- file: /index.sql

set user = sqlpage.run_sql('api/return/user.sql')->>0;

select 'title' as component,
  'Hello ' || $user->>'first_name' as contents;

-- file: /api/return/user.sql

set user_session = (select key from sessions where key = sqlpage.cookie('user_session') and expires > datetime('now'));
set user_id = (select user from user_sessions where session = $user_session);

select
	$user_session as session,
	cast(id as integer) as id,
	cast(is_admin as integer) as admin,
	cast(is_super_admin as integer) as super_admin,
	email as email,
	first_name as first_name,
	last_name as last_name
from users where id = $user_id;

Subsequent .run_sql functions and of course the rest of the current sql file now have access to $user. 🙌

Filters

The example above assumes we have a logged in user. So we can easily use a filter to redirect non-logged-in guests since .run_sql inherits our $user variable 💪

-- file: /index.sql
set user = sqlpage.run_sql('api/return/user.sql')->>0;
select 'dynamic' as component, sqlpage.run_sql('api/filters/logged-in.sql') as properties;

-- Now we know we're dealing with a real user
select 'title' as component,
  'Hello ' || $user->>'first_name' as contents;

-- file: /api/filters/logged-in.sql
select 'redirect' as component,
  '/login.sql' where $user->>'id' is null;

---

I'd love to see how others are making use of the .run_sql function!

Great job @lovasoa!

[lovasoa]

Thanks, @matthewlarkin! It's refreshing to read a post like yours, sharing knowledge and appreciation. Typically, people only speak up when they have an issue, so thanks for being an exception!

@DSMejantel, I think you've got a similar user management system in place, so I figure you might find these tips useful 😉

[matthewlarkin]

You know, @lovasoa, I do like the inherting nature of variables when working with sqlpage.run_sql() call. That is, variables are not pulled out from the included file, but rather, the included files inherit variables set from the outside (the files calling .run_sql()).

I think that's organic for most cases and doesn't cause issues if you code carefully, but I wonder if you've considered a way to "export" variables set within .run_sql() files?

I do have this issue where I'm reusing this code on all pages that require login (a lot of pages):

set account = sqlpage.run_sql('return/account.sql')->>0;
select 'dynamic' as component, sqlpage.run_sql('filters/admin.sql') as properties;
select 'dynamic' as component, sqlpage.run_sql('ui/shell.sql') as properties;

I think the only thing holding me back from just one line of code...

select 'dynamic' as component, sqlpage.run_sql('user-init.sql');`

...is the fact that I need to make use of $account downstream ..which requires its setting on on each page.

I wonder, would it be exceptionally tricky to optionally allow exporting of certain variables set from within a .run_sql() call? Or perhaps a way to set a truly global variable from any set declaration as they come?

[lovasoa]

I guess we could reuse MySQL's SET GLOBAL syntax...

[axelmoe]

Thank you very much for these great examples. Such contributions would be something for HowTo's in the documentation
KR Axel

[lovasoa]

I do agree !

@axelmoe , would you be ready to open a small pull request to the dynamic component documentation and the run_sql documentation ?

[axelmoe]

Hi @lovasoa, never done before but I 'll check and learn whats to do and to support this great project

[lovasoa]

Great ! If you need help, don't hesitate to ask ! How did you discover SQLPage ? What are you building with it ?

[axelmoe]

HI @lovasoa ,
I came across SQLPAGE about 4 months ago and have been following its development with great interest ever since. At the moment I am trying to improve my SQL knowledge and am studying examples and working with the documentation. In the next few months I would like to modernize some smaller apps that I wrote years ago as a classic desktop app with SQLPAGE.