index.js

const db = require("@saltcorn/data/db");
const Form = require("@saltcorn/data/models/form");
const Field = require("@saltcorn/data/models/field");
const Table = require("@saltcorn/data/models/table");
const FieldRepeat = require("@saltcorn/data/models/fieldrepeat");
const Workflow = require("@saltcorn/data/models/workflow");
const { eval_expression } = require("@saltcorn/data/models/expression");
const { interpolate } = require("@saltcorn/data/utils");
const {
  text,
  div,
  h5,
  style,
  a,
  script,
  pre,
  domReady,
  i,
  text_attr,
} = require("@saltcorn/markup/tags");
const { mkTable } = require("@saltcorn/markup");
const { readState } = require("@saltcorn/data/plugin-helper");
const { features } = require("@saltcorn/data/db/state");
const Handlebars = require("handlebars");

const configuration_workflow = () =>
  new Workflow({
    steps: [
      {
        name: "views",
        form: async (context) => {
          return new Form({
            fields: [
              {
                name: "sql",
                label: "SQL",
                input_type: "code",
                attributes: { mode: "text/x-sql" },
                sublabel:
                  "Refer to state parameters in the order below with <code>$1</code>, <code>$2</code> etc",
              },
              {
                name: "state_parameters",
                label: "State parameters",
                sublabel:
                  "Comma separated list of state variables from URL querystring to use as SQL query parameters. User variables can be used as <code>user.id</code> etc",
                type: "String",
              },
              {
                name: "output_type",
                label: "Output type",
                type: "String",
                required: true,
                attributes: {
                  options: ["Table", "JSON", "HTML", "HTML with handlebars"],
                },
              },
              {
                name: "html_code",
                label: "HTML Code",
                input_type: "code",
                attributes: { mode: "text/html" },
                showIf: { output_type: "HTML" },
                sublabel:
                  "Use interpolations (<code>{{ }}</code>) to access query result in he <code>rows</code> variable. Example: <code>&lt;script&gt;const rows = {{ JSON.stringify(rows) }}&lt;/script&gt;</code>",
              },
              {
                name: "html_code",
                label: "HTML Code",
                input_type: "code",
                attributes: { mode: "text/html" },
                showIf: { output_type: "HTML with handlebars" },
                sublabel:
                  "Use handlebars to access query result in the <code>rows</code> variable. Example: <code>{{#each rows}}&lt;h1&gt;{{this.name}}&lt;/h1&gt;{{/each}}</code>",
              },
            ],
          });
        },
      },
    ],
  });

const get_state_fields = () => [];

const run = async (
  table_id,
  viewname,
  { sql, output_type, state_parameters, html_code },
  state,
  { req },
) => {
  const is_sqlite = db.isSQLite;

  const phValues = [];
  (state_parameters || "")
    .split(",")
    .filter((s) => s)
    .forEach((sp0) => {
      const sp = sp0.trim();
      if (sp.startsWith("user.")) {
        phValues.push(eval_expression(sp, {}, req.user));
      } else if (typeof state[sp] === "undefined") phValues.push(null);
      else phValues.push(state[sp]);
    });
  let qres, client;
  try {
    client = is_sqlite ? db : await db.getClient();
    await client.query(`BEGIN;`);
    if (!is_sqlite) {
      await client.query(`SET LOCAL search_path TO "${db.getTenantSchema()}";`);
      await client.query(
        `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;`,
      );
    }
    qres = await client.query(sql, phValues);
  } finally {
    await client.query(`ROLLBACK;`);
    if (!is_sqlite) client.release(true);
  }
  switch (output_type) {
    case "HTML":
      return interpolate(
        html_code,
        { rows: qres.rows },
        req.user,
        `HTML code interpolation in view ${viewname}`,
      );
    //return template();
    case "HTML with handlebars":
      const template = Handlebars.compile(html_code || "");
      return template({ rows: qres.rows });
    case "JSON":
      return `<pre>${JSON.stringify(qres.rows, null, 2)}</pre>`;

    default: //Table
      return mkTable(
        qres.fields.map((field) => ({ label: field.name, key: field.name })),
        qres.rows,
      );
  }
};

module.exports = {
  sc_plugin_api_version: 1,
  plugin_name: "sql",
  actions: require("./action.js"),
  table_providers: require("./table-provider.js"),
  exchange: {
    agent_skills: [require("./agent-skill.js")],
  },
  functions: {
    sqlQuery: {
      run: async (query, parameters) => {
        const is_sqlite = db.isSQLite;

        const client = is_sqlite ? db : await db.getClient();
        db.sql_log("BEGIN;");
        await client.query(`BEGIN;`);
        if (!is_sqlite) {
          db.sql_log(`SET LOCAL search_path TO "${db.getTenantSchema()}";`);
          await client.query(
            `SET LOCAL search_path TO "${db.getTenantSchema()}";`,
          );
          db.sql_log(`SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;`);
          await client.query(
            `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;`,
          );
        }
        db.sql_log(query, parameters || []);
        const qres = await client.query(query, parameters || []);
        db.sql_log("ROLLBACK;");
        await client.query(`ROLLBACK;`);
        if (!is_sqlite) client.release();
        return qres;
      },
      isAsync: true,
      description: "Run an SQL query",
      returns: "JSON",
      tsreturns: "Promise<{rows: Array<{ [key: string]: unknown }>}>",
      arguments: [
        { name: "sql_query", type: "String", required: true },
        { name: "parameters", type: "JSON", tstype: "any[]" },
      ],
    },
  },
  viewtemplates: [
    {
      name: "SQLView",
      display_state_form: false,
      tableless: true,
      copilot_generate_view_prompt: async ({ table }) => {
        const tableLines = [];
        const tables = await Table.find({});
        tables.forEach((table) => {
          const fieldLines = table.fields.map(
            (f) =>
              `  * ${f.name} with type: ${f.pretty_type.replace(
                "Key to",
                "ForeignKey referencing",
              )}.${f.description ? ` ${f.description}` : ""}`,
          );
          tableLines.push(
            `${table.name}${
              table.description ? `: ${table.description}.` : "."
            } Contains the following fields:\n${fieldLines.join("\n")}`,
          );
        });
        return `The database already contains the following tables: 

    ${tableLines.join("\n\n")}

    You can reference these tables when generating SQL`;
      },
      get_state_fields,
      configuration_workflow,
      run,
    },
    require("./terminal"),
  ],
};