SQL identifier from string

[pchemguy]

It would be nice to have a function sqlpage.sql_id(<str_object_name>) returning an SQL identifier (str_object_name - string representation of schema/table/column names). so that I could do something like this:

SET $table = 'sqlite_master';
SET $schema = 'main';
SET $col = 'name';

WITH
    data AS (
        SELECT sqlpage.sql_id($schema).sqlpage.sql_id($table).sqlpage.sql_id($col)
        FROM sqlpage.sql_id($table).sqlpage.sql_id($col)
        WHERE length(sqlpage.sql_id($schema).sqlpage.sql_id($table).sqlpage.sql_id($col)) > 3
        ORDER BY sqlpage.sql_id($schema).sqlpage.sql_id($table).sqlpage.sql_id($col);
    )
SELECT 'table' AS component ...

In practice, I might read certain object identifiers from a database metadata table or pass them as GET/POST parameters, so that they can be included in template queries.

[lovasoa]

Hello ! Can you tell us more about how your data is structured and what you are ultimately trying to achieve ?

[pchemguy]

It's a work in-progress. below is the current schema.

Among other things, I want to be able to generate a list of tables (say, query sqlite_master) and have a table component template taking table name and generating a page with table data, e.g.

SET $item_form = 'currencies_item.sql';
SELECT
    id,
    name,
    to_rub,
    '[![](/icons/outline/edit.svg)]('  || $item_form || '?id=' || id || ') ' ||
    '[![](/icons/outline/trash.svg)](' || $item_form || '?id=' || id || '&action=DELETE)' AS actions
FROM currencies
ORDER BY id;

Above, I hardcoded column and table names, and I would need to have separate table-view files for each table. With something like

SET $item_form = 'currencies_item.sql';
SELECT *
    '[![](/icons/outline/edit.svg)]('  || $item_form || '?id=' || id || ') ' ||
    '[![](/icons/outline/trash.svg)](' || $item_form || '?id=' || id || '&action=DELETE)' AS actions
FROM sqlpage.sql_id($table_name)
ORDER BY id;

placed in table-view.sql, I could have links like table-view.sql?table_name=currencies, This way, I could have one template generating a basic table view for any table. A dynamic list can then be generated with something like

SELECT
    'table-view.sql?table_name=' || tbl_name
FROM sqlite_master
WHERE type = 'table';

and using the result to populate a dynamic drop-down list.

With the ability of dynamic client-side updating and AJAX requests, the two pieces can be placed in the same file, so that at the top of the page a query to sqlite_master generates a dynamic drop-down list of tables, and its current value is then used to show the table contents. With present facilities and sqlpage.sql_id(), I could place the dynamically generated table list in a form at the top of a page and update displayed table data below after the form is submitted with redirect to itself.

Schema

CREATE TABLE "assemblies" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "classes" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "brands" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE,
    "url"    TEXT COLLATE NOCASE
);

CREATE TABLE "vendors" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE,
    "url"    TEXT COLLATE NOCASE
);

CREATE TABLE "connector_types" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "connector_styles" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "connector_genders" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "connector_kinds" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "cable_kinds" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE
);

CREATE TABLE "connectors" (
    "id"         INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"       TEXT COLLATE NOCASE NOT NULL UNIQUE GENERATED ALWAYS AS (
                     coalesce("style" || '/', '') ||
                     "kind_1" || coalesce(' ' || "gender_1", '') ||
                     iif("cable_kind" IS NOT NULL, '/' || "cable_kind", '') ||
                     iif("kind_2" IS NOT NULL, '/' || "kind_2" ||
                         coalesce(' ' || "gender_2", ''), '') ||
                     coalesce(', ' || "cable_len" || ' m', '')
                     
                 ) VIRTUAL,
    "style"      TEXT COLLATE NOCASE
                 CONSTRAINT "fk_connectors_connector_styles"
                 REFERENCES "connector_styles"("name"),
    "kind_1"     TEXT COLLATE NOCASE NOT NULL
                 CONSTRAINT "fk_connectors_connector_kinds_1"
                 REFERENCES "connector_kinds"("name"),
    "gender_1"   TEXT COLLATE NOCASE
                 CHECK(iif("gender_1" IS NOT NULL, "kind_1" IS NOT NULL, TRUE))
                 CONSTRAINT "fk_connectors_connector_genders_1"
                 REFERENCES "connector_genders"("name"),
    "kind_2"     TEXT COLLATE NOCASE
                 CONSTRAINT "fk_connectors_connector_kinds_2"
                 REFERENCES "connector_kinds"("name"),
    "gender_2"   TEXT COLLATE NOCASE
                 CHECK(iif("gender_2" IS NOT NULL, "kind_2" IS NOT NULL, TRUE))
                 CONSTRAINT "fk_connectors_connector_genders_2"
                 REFERENCES "connector_genders"("name"),
    "cable_kind" TEXT COLLATE NOCASE
                 CHECK(iif("cable_kind" IS NOT NULL, "style" = 'Cable', TRUE))
                 CONSTRAINT "fk_connectors_cable_kinds"
                 REFERENCES "cable_kinds"("name"),
    "cable_len"  NUMERIC
                 CHECK(iif("cable_len" IS NOT NULL, "cable_kind" IS NOT NULL, TRUE))
);

CREATE TABLE "currencies" (
    "id"     INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"   TEXT COLLATE NOCASE NOT NULL UNIQUE,
    "to_rub" REAL NOT NULL
);

CREATE TABLE "parts" (
    "id"        INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"      TEXT COLLATE NOCASE NOT NULL,
    "part_no"   TEXT COLLATE NOCASE,
    "sku"       TEXT COLLATE NOCASE,
    "class"     TEXT COLLATE NOCASE NOT NULL
                CONSTRAINT "fk_parts_classes" REFERENCES "classes"("name"),
    "brand"     TEXT COLLATE NOCASE NOT NULL DEFAULT ''
                CONSTRAINT "fk_parts_brands" REFERENCES "brands"("name"),
    "vendor"    TEXT COLLATE NOCASE NOT NULL
                CONSTRAINT "fk_vendors_brands" REFERENCES "vendors"("name"),
    "connector" TEXT COLLATE NOCASE
                CONSTRAINT "fk_parts_connectors" REFERENCES "connectors"("name"),
    "url"       TEXT COLLATE NOCASE NOT NULL,
    "price"     NUMERIC,
    "currency"  TEXT COLLATE NOCASE NOT NULL
                CONSTRAINT "fk_parts_currencies" REFERENCES "currencies"("name"),
    "extra"     TEXT COLLATE NOCASE,
    CONSTRAINT uq_name_vendor UNIQUE("name", "brand", "vendor")
);

CREATE TABLE "purchase_orders" (
    "id"             INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"           TEXT COLLATE NOCASE NOT NULL UNIQUE,
    "reference"      TEXT COLLATE NOCASE NOT NULL UNIQUE,
    "vendor"         TEXT COLLATE NOCASE NOT NULL
                     CONSTRAINT "fk_purchase_orders_brands"
                     REFERENCES "vendors"("name"),
    "invoice_date"   TEXT COLLATE NOCASE,
    "invoice_number" TEXT COLLATE NOCASE,
    "order_date"     TEXT COLLATE NOCASE,
    "order_number"   TEXT COLLATE NOCASE,
    "order_status"   TEXT COLLATE NOCASE CHECK("order_status"
                     IN ('placed', 'paid', 'shipped', 'delivered', 'closed')),
    UNIQUE("vendor", "invoice_number"),
    UNIQUE("vendor", "order_number"),
    UNIQUE("name", "vendor")
);


CREATE TABLE "boms" (
    "id"       INTEGER PRIMARY KEY AUTOINCREMENT,
    "assembly" TEXT COLLATE NOCASE NOT NULL
               CONSTRAINT "fk_boms_assemblies" REFERENCES "assemblies"("name"),
    "name"     TEXT COLLATE NOCASE NOT NULL,
    "brand"    TEXT COLLATE NOCASE NOT NULL DEFAULT '',
    "vendor"   TEXT COLLATE NOCASE NOT NULL,
    "quantity" INTEGER,
    "po_name"  TEXT COLLATE NOCASE,
    CONSTRAINT "fk_boms_parts" FOREIGN KEY("name", "brand", "vendor")
        REFERENCES "parts"("name", "brand", "vendor"),
    CONSTRAINT "fk_boms_purchase_orders" FOREIGN KEY("po_name")
        REFERENCES "purchase_orders"("name") ON UPDATE CASCADE
);

[lovasoa]

I understand your approach. Personally, I would handle it differently. The risk with treating table names as dynamic data is that it can add complexity and limit scalability over time.

In your scenario, since you have similar structures for different types of connectors, I would suggest having a single table to store all connector attribute values. Each connector can link to multiple connector attributes, and you can dynamically query and edit attribute types. You can use unique indices to maintain invariants.

By doing this, you can simplify your database structure and queries. Instead of generating a large number of table names, you would query the "connector_attributes" table based on certain criteria, such as connector type, gender, style, etc.

This approach would make your schema more streamlined and easier to manage. You can still achieve dynamic behavior by querying based on attributes stored in the database, rather than dynamically constructing SQL statements.

SQLPage compiles your SQL queries to prepared statements as they are, without any potentially dangerous sql string manipulation. This is a guarantee of both performance and security, so I'm not a huge fan of breaking that invariant.

[pchemguy]

I am not sure I understand you correctly. The table "connectors" essentially stores all attributes. There are five additional tables defining individual attributes to which "connectors" establishes foreign keys. This structure is dictated by normalization considerations (though the present schema is intentionally partly denormalized and foreign keys reference actual values, rather than the id/pk columns). The contents of individual attribute tables is not fixed, so it would be impractical to convert those to CHECK constraints.

Anyways, I understand why dynamic SQL generation should usually be avoided, that is fine, so it is OK to close this idea.