Passing Information via GET Parameters

[pchemguy]

Web applications routinely use URL GET parameters to pass information between different application parts. For example, when an unauthenticated user clicks the Login button, the browser redirects to the login form. Depending on specific circumstances, the application usually would want to redirect back to the original page after the authentication process is completed. A common approach to pass the desired return path to the login form is via a GET parameter. In SQLPage, one could do something like this:

'/login.sql?path=' || sqlpage.path() AS link

Other common examples of using GET parameters include querying various databases and search engines and passing certain context information between application components. In such cases, it might also be necessary to relay other GET parameters when redirecting.

SQLPage provides the sqlpage.variables('GET') function returning a JSON array containing current GET parameters. This JSON array also contains any locally set variables via the SET statement, which, generally, do not need to be passed around indiscriminately. As far as I can tell, SQLPage does not provide a readily available means to distinguish the two kinds of variables. One possible approach to separate the two is via a naming convention. I usually prefix local variable names with underscores. For instance, path information may be stored in a local variable for further use:

SET $_curpath = sqlpage.path();

which can later be used to set a redirect or action target:

'/login.sql?path=' || $_curpath AS link

If this "underscore" convention is adopted, the code inside an SQLPage script may retrieve GET parameters:

SET $_get_vars = (
    SELECT json_group_object("key", "value")
    FROM json_each(sqlpage.variables('GET'))
    WHERE "key" NOT LIKE '\_%' ESCAPE '\'
);

This code may be executed at the top of a page to "prepare" the environment. Given a URL containing ?lang=en&theme=fancy&hide_language_names=1&authenticated=0, $_get_vars will be set to

{"theme":"fancy","hide_language_names":"1","authenticated":"0","lang":"en"}

Suppose we plan to populate language submenu links that enable switching to alternative locales. Given a table with a list of available languages:

CREATE TABLE locales (
    code      TEXT PRIMARY KEY COLLATE NOCASE,
    language  TEXT COLLATE NOCASE NOT NULL UNIQUE
);

INSERT INTO locales VALUES
    ('en', 'English'), ('fr', 'French'), ('de', 'German'), ('sp', 'Spanish');

we could have index.sql with the following minimalistic code:

-- Sets up the demo
SELECT
    'redirect' AS component,
    '?authenticated=0&hide_language_names=1&lang=en&theme=fancy' AS link
WHERE length(sqlpage.variables('GET')) <= 2;

-- Retrieves URL GET parameters, filter out SET variables
--
-- The FROM clause uses the json_each() function to convert the GET parameters into a table,
-- the WHERE clause filters out local variables based on the adopted naming convention, and
-- the SELECT clause uses json_group_object to repack GET parameters into an array.
SET $_get_vars = (
    SELECT json_group_object("key", "value")
    FROM  json_each(sqlpage.variables('GET'))
    WHERE "key" NOT LIKE '~_%' ESCAPE '~'
);

SET $_menu_lang = (
    WITH
        -- Uses json_patch to updated GET parameters
        get_json AS (
            SELECT
                language AS title,
                json_patch($_get_vars, json_object('lang', code)) AS var
            FROM locales
        ),
        -- Unpacks JSON array and converts into a GET URL string
        get_urls AS (
            SELECT
                title,
                '?' || group_concat("key" || '=' || value, '&' ORDER BY "key") AS "get_suffix"
            FROM get_json, json_each(var)
            GROUP BY title
        )
    -- Constructs a basic definition of a submenu
    SELECT
        json_group_array(json_object('title', title, 'link', get_suffix) ORDER BY title) AS menu
    FROM
        get_urls
);

SELECT
    'shell'    AS component,
    'SQLPage'  AS title,
    'database' AS icon,
    '/'        AS link,
    -- Defines previously constructed menu
    json_object('title', 'Language', 'submenu', json($_menu_lang)) AS menu_item;


SELECT
    'text' AS component,
    replace(replace(json_pretty($_menu_lang), ' ', '&nbsp;'), x'0A', x'20200A') AS contents_md;

[lovasoa]

Thanks for sharing !

Depending on your requirements, you can also use cookies to persist user preferences such as language or theme between page loads. This avoids having to reconstruct complex urls in all links.

[pchemguy]

Definitely! I have another piece, which uses cookies for session management following some of the other examples. And the idea of using cookies for other purposes is in the queue as well.