Does SQLPage support running CTEs and storing their output for display?

[shah]

Hi I'm trying to run this in an SQLite database to prepare a single markdown page for my SQLite database but I do not think I'm using the right SQLPage syntax for CTEs.

WITH TableInfo AS (
  SELECT
    m.tbl_name AS table_name,
    CASE WHEN c.pk THEN '*' ELSE '' END AS is_primary_key,
    c.name AS column_name,
    c."type" AS column_type,
    CASE WHEN c."notnull" THEN '*' ELSE '' END AS not_null,
    COALESCE(c.dflt_value, '') AS default_value,
    COALESCE((SELECT pfkl.'table' || '.' || pfkl.'to' FROM pragma_foreign_key_list(m.tbl_name) AS pfkl WHERE pfkl.'from' = c.name), '') as fk_refs,
    ROW_NUMBER() OVER (PARTITION BY m.tbl_name ORDER BY c.cid) AS row_num
  FROM sqlite_master m JOIN pragma_table_info(m.tbl_name) c ON 1=1
  WHERE m.type = 'table'
  ORDER BY table_name, row_num
),
Views AS (
  SELECT '## Views ' AS markdown_output
  UNION ALL
  SELECT '| View | Column | Type |' AS markdown_output
  UNION ALL
  SELECT '| ---- | ------ |----- |' AS markdown_output
  UNION ALL
  SELECT '| ' || tbl_name || ' | ' || c.name || ' | ' || c."type" || ' | '
  FROM
    sqlite_master m,
    pragma_table_info(m.tbl_name) c
  WHERE
    m.type = 'view'
),
Indexes AS (
  SELECT '## Indexes' AS markdown_output
  UNION ALL
  SELECT '| Table | Index | Columns |' AS markdown_output
  UNION ALL
  SELECT '| ----- | ----- | ------- |' AS markdown_output
  UNION ALL
  SELECT '| ' ||  m.name || ' | ' || il.name || ' | ' || group_concat(ii.name, ', ') || ' |' AS markdown_output
  FROM sqlite_master as m,
    pragma_index_list(m.name) AS il,
    pragma_index_info(il.name) AS ii
  WHERE
    m.type = 'table'
  GROUP BY
    m.name,
    il.name
)
SELECT
  markdown_output AS info_schema_markdown
FROM
  (
    SELECT '## Tables' AS markdown_output
    UNION ALL
    SELECT
      CASE WHEN ti.row_num = 1 THEN '
### `' || ti.table_name || '` Table
| PK | Column | Type | Req? | Default | References |
| -- | ------ | ---- | ---- | ------- | ---------- |
' ||
        '| ' || is_primary_key || ' | ' || ti.column_name || ' | ' || ti.column_type || ' | ' || ti.not_null || ' | ' || ti.default_value || ' | ' || ti.fk_refs || ' |'
      ELSE
        '| ' || is_primary_key || ' | ' || ti.column_name || ' | ' || ti.column_type || ' | ' || ti.not_null || ' | ' || ti.default_value || ' | ' || ti.fk_refs || ' |'
      END
    FROM TableInfo ti
    UNION ALL SELECT ''
    UNION ALL SELECT * FROM	Views
    UNION ALL SELECT ''
    UNION ALL SELECT * FROM Indexes
);

-- :info_schema_markdown should be defined in the above query
SELECT 'text' as component,
       'Information Schema' as title,
       :info_schema_markdown as contents_md

The above CTE works directly in SQLite but is this possible in the latest SQLPage?
Thanks for the great work!

[lovasoa]

The :name syntax is used to refer to POST variables and is useful for handling form results. It does not refer to the previous query, and is not what you need here. You want to select 'text' as component directly in your large query, and make it return a single row which contains all your markdown, including newline characters, instead of one line per row (you can use group_concat).

[lovasoa]

Here is a fixed version:

WITH TableInfo AS (
  SELECT
    m.tbl_name AS table_name,
    CASE WHEN c.pk THEN '*' ELSE '' END AS is_primary_key,
    c.name AS column_name,
    c."type" AS column_type,
    CASE WHEN c."notnull" THEN '*' ELSE '' END AS not_null,
    COALESCE(c.dflt_value, '') AS default_value,
    COALESCE((SELECT pfkl."table" || '.' || pfkl."to" FROM pragma_foreign_key_list(m.tbl_name) AS pfkl WHERE pfkl."from" = c.name), '') as fk_refs,
    ROW_NUMBER() OVER (PARTITION BY m.tbl_name ORDER BY c.cid) AS row_num
  FROM sqlite_master m JOIN pragma_table_info(m.tbl_name) c ON 1=1
  WHERE m.type = 'table'
  ORDER BY table_name, row_num
),
Views AS (
  SELECT '## Views ' AS markdown_output
  UNION ALL
  SELECT '| View | Column | Type |' AS markdown_output
  UNION ALL
  SELECT '| ---- | ------ |----- |' AS markdown_output
  UNION ALL
  SELECT '| ' || tbl_name || ' | ' || c.name || ' | ' || c."type" || ' | '
  FROM
    sqlite_master m,
    pragma_table_info(m.tbl_name) c
  WHERE
    m.type = 'view'
),
Indexes AS (
  SELECT '## Indexes' AS markdown_output
  UNION ALL
  SELECT '| Table | Index | Columns |' AS markdown_output
  UNION ALL
  SELECT '| ----- | ----- | ------- |' AS markdown_output
  UNION ALL
  SELECT '| ' ||  m.name || ' | ' || il.name || ' | ' || group_concat(ii.name, ', ') || ' |' AS markdown_output
  FROM sqlite_master as m,
    pragma_index_list(m.name) AS il,
    pragma_index_info(il.name) AS ii
  WHERE
    m.type = 'table'
  GROUP BY
    m.name,
    il.name
)
SELECT
    'text' as component,
    'Information Schema' as title,
    group_concat(markdown_output, '
') AS contents_md
FROM
  (
    SELECT '## Tables' AS markdown_output
    UNION ALL
    SELECT
      CASE WHEN ti.row_num = 1 THEN '
### `' || ti.table_name || '` Table
| PK | Column | Type | Req? | Default | References |
| -- | ------ | ---- | ---- | ------- | ---------- |
' ||
        '| ' || is_primary_key || ' | ' || ti.column_name || ' | ' || ti.column_type || ' | ' || ti.not_null || ' | ' || ti.default_value || ' | ' || ti.fk_refs || ' |'
      ELSE
        '| ' || is_primary_key || ' | ' || ti.column_name || ' | ' || ti.column_type || ' | ' || ti.not_null || ' | ' || ti.default_value || ' | ' || ti.fk_refs || ' |'
      END
    FROM TableInfo ti
    UNION ALL SELECT ''
    UNION ALL SELECT * FROM	Views
    UNION ALL SELECT ''
    UNION ALL SELECT * FROM Indexes
);

and the result

[lovasoa]

I double-quoted (instead of single-quoting) column names because of apache/datafusion-sqlparser-rs#1020 (standard SQL only supports double quotes for identifiers).

[lovasoa]

The parser update just went out, and single-quoted column names are now supported, they will be part of SQLPage v0.15

[lovasoa]

Out of curiosity: what is it that you are building ? If I may suggest, other components would probably look better to render this list of tables, and the code would be shorter and cleaner too, because you wouldn't have to do all of this string concatenation to generate valid markdown.

[shah]

Excellent answer @lovasoa and great to see you got it to work! I love SQLpage :-)

My use case is double-sided need:

1. Allow running the existing CTE through SQL and generating a *.md file (e.g. sqlite3 my.db "select ... markdown" > my-db.md).
2. Using the same output in SQLPage to display via a web page

Your idea of using components is perfect for the web page display but would not let me save the *.md file for storage in Git.
Another alternative is to add some CLI functionality to SQLPage to allow saving generated SQLPage output into files similar to the need I have with the first use case above.

Keep up the great work!

[lovasoa]

And since your initial query was about storing a query output and reusing it: You don't really need it here, but it is possible. The limitation is that the only thing that you can store is text (not structured rows).

SET my_variable = (SELECT group_concat(my_lines, x'0a') from my_table); -- You can have an arbitrarily complex sql query here, but it must return a single string;
SELECT 'text' as component, $my_variable AS contents_md;