Source: server/routes/fields.js

  1. /**
  2.  *
  3.  * Field Router
  4.  */
  6. const Router = require("express-promise-router");
  8. const { getState } = require("@saltcorn/data/db/state");
  9. const { renderForm } = require("@saltcorn/markup");
  10. const Field = require("@saltcorn/data/models/field");
  11. const Table = require("@saltcorn/data/models/table");
  12. const Form = require("@saltcorn/data/models/form");
  13. const Workflow = require("@saltcorn/data/models/workflow");
  14. const User = require("@saltcorn/data/models/user");
  15. const {
  16.   expressionValidator,
  17.   get_async_expression_function,
  18.   get_expression_function,
  19. } = require("@saltcorn/data/models/expression");
  20. const db = require("@saltcorn/data/db");
  22. const { setTenant, isAdmin, error_catcher } = require("./utils.js");
  23. const expressionBlurb = require("../markup/expression_blurb");
  24. const { readState } = require("@saltcorn/data/plugin-helper");
  25. const { wizardCardTitle } = require("../markup/forms.js");
  26. const router = new Router();
  27. module.exports = router;
  29. const fieldForm = async (req, fkey_opts, existing_names, id, hasData) => {
  30.   let isPrimary = false;
  31.   let primaryTypes = Object.entries(getState().types)
  32.     .filter(([k, v]) => v.primaryKey)
  33.     .map(([k, v]) => k);
  34.   if (id) {
  35.     const field = await Field.findOne({ id });
  36.     if (field) {
  37.       isPrimary = !!field.primary_key;
  38.     }
  39.   }
  40.   return new Form({
  41.     action: "/field",
  42.     validator: (vs) => {
  43.       if (vs.calculated && vs.type == "File")
  44.         return req.__("Calculated fields cannot have File type");
  45.       if (vs.calculated && vs.type.startsWith("Key to"))
  46.         return req.__("Calculated fields cannot have Key type");
  47.     },
  48.     fields: [
  49.       new Field({
  50.         label: req.__("Label"),
  51.         name: "label",
  52.         sublabel: req.__("Name of the field"),
  53.         input_type: "text",
  54.         validator(s) {
  55.           if (!s || s === "") return req.__("Missing label");
  56.           if (!id && existing_names.includes(Field.labelToName(s)))
  57.             return req.__("Column %s already exists", s);
  58.         },
  59.       }),
  60.       // description
  61.       new Field({
  62.         label: req.__("Description"),
  63.         name: "description",
  64.         sublabel: req.__("Description allows to give more information about field"),
  65.         input_type: "text",
  66.       }),
  67.       new Field({
  68.         label: req.__("Type"),
  69.         name: "type",
  70.         sublabel:
  71.             req.__("The type determines the kind of data that can be stored in the field"),
  72.         input_type: "select",
  73.         options: isPrimary
  74.           ? primaryTypes
  75.           : getState().type_names.concat(fkey_opts || []),
  76.         disabled:
  77.           !!id &&
  78.           !getState().getConfig("development_mode", false) &&
  79.           (hasData || db.isSQLite),
  80.       }),
  81.       new Field({
  82.         label: req.__("Calculated"),
  83.         name: "calculated",
  84.         sublabel: req.__("Calculated from other fields with a formula"),
  85.         type: "Bool",
  86.         disabled: !!id,
  87.       }),
  88.       new Field({
  89.         label: req.__("Required"),
  90.         name: "required",
  91.         type: "Bool",
  92.         sublabel: req.__("There must be a value in every row"),
  93.         disabled: !!id && db.isSQLite,
  94.         showIf: { calculated: false },
  95.       }),
  96.       new Field({
  97.         label: req.__("Unique"),
  98.         name: "is_unique",
  99.         sublabel: req.__("Different rows must have different values for this field"),
  100.         showIf: { calculated: false },
  101.         type: "Bool",
  102.       }),
  104.       new Field({
  105.         label: req.__("Stored"),
  106.         name: "stored",
  107.         type: "Bool",
  108.         disabled: !!id,
  109.         showIf: { calculated: true },
  110.       }),
  111.     ],
  112.   });
  113. };
  114. const calcFieldType = (ctxType) =>
  115.   ctxType.startsWith("Key to")
  116.     ? { type: "Key", reftable_name: ctxType.replace("Key to ", "") }
  117.     : { type: ctxType };
  119. const translateAttributes = (attrs, req) =>
  120.   Array.isArray(attrs)
  121.     ? attrs.map((attr) => translateAttribute(attr, req))
  122.     : attrs;
  124. const translateAttribute = (attr, req) => {
  125.   const res = { ...attr };
  126.   if (res.sublabel) res.sublabel = req.__(res.sublabel);
  127.   return res;
  128. };
  129. const fieldFlow = (req) =>
  130.   new Workflow({
  131.     action: "/field",
  132.     onDone: async (context) => {
  133.       const thetype = getState().types[context.type];
  134.       var attributes = context.attributes || {};
  135.       attributes.default = context.default;
  136.       attributes.summary_field = context.summary_field;
  137.       const {
  138.         table_id,
  139.         name,
  140.         label,
  141.         required,
  142.         is_unique,
  143.         calculated,
  144.         expression,
  145.         stored,
  146.         description,
  147.       } = context;
  148.       const { reftable_name, type } = calcFieldType(context.type);
  149.       const fldRow = {
  150.         table_id,
  151.         name,
  152.         label,
  153.         type,
  154.         required,
  155.         is_unique,
  156.         reftable_name,
  157.         attributes,
  158.         calculated,
  159.         expression,
  160.         stored,
  161.         description,
  162.       };
  163.       if (fldRow.calculated) {
  164.         fldRow.is_unique = false;
  165.         fldRow.required = false;
  166.       }
  167.       if (context.id) {
  168.         const field = await Field.findOne({ id: context.id });
  169.         try {
  170.           await field.update(fldRow);
  171.         } catch (e) {
  172.           return {
  173.             redirect: `/table/${context.table_id}`,
  174.             flash: ["error", e.message],
  175.           };
  176.         }
  177.       } else {
  178.         try {
  179.           await Field.create(fldRow);
  180.         } catch (e) {
  181.           return {
  182.             redirect: `/table/${context.table_id}`,
  183.             flash: ["error", e.message],
  184.           };
  185.         }
  186.       }
  188.       return {
  189.         redirect: `/table/${context.table_id}`,
  190.         flash: [
  191.           "success",
  192.           context.id
  193.             ? req.__("Field %s saved", label)
  194.             : req.__("Field %s created", label),
  195.         ],
  196.       };
  197.     },
  198.     steps: [
  199.       {
  200.         name: req.__("Basic properties"),
  201.         form: async (context) => {
  202.           const tables = await Table.find({});
  203.           const table = tables.find((t) => t.id === context.table_id);
  204.           const nrows = await table.countRows({});
  205.           const existing_fields = await table.getFields();
  206.           const existingNames = existing_fields.map((f) => f.name);
  207.           const fkey_opts = ["File", ...tables.map((t) => `Key to ${t.name}`)];
  208.           const form = await fieldForm(
  209.             req,
  210.             fkey_opts,
  211.             existingNames,
  212.             context.id,
  213.             nrows > 0
  214.           );
  215.           if (context.type === "Key" && context.reftable_name) {
  216.             form.values.type = `Key to ${context.reftable_name}`;
  217.           }
  218.           return form;
  219.         },
  220.       },
  221.       {
  222.         name: req.__("Attributes"),
  223.         contextField: "attributes",
  224.         onlyWhen: (context) => {
  225.           if (context.calculated) return false;
  226.           if (context.type === "File") return true;
  227.           if (new Field(context).is_fkey) return false;
  228.           const type = getState().types[context.type];
  229.           return type.attributes && type.attributes.length > 0;
  230.         },
  231.         form: async (context) => {
  232.           if (context.type === "File") {
  233.             const roles = await User.get_roles();
  234.             return new Form({
  235.               fields: [
  236.                 {
  237.                   name: "min_role_read",
  238.                   label: req.__("Role required to access added files"),
  239.                   sublabel: req.__(
  240.                     "The user uploading the file has access irrespective of their role"
  241.                   ),
  242.                   input_type: "select",
  243.                   options: roles.map((r) => ({ value: r.id, label: r.role })),
  244.                 },
  245.               ],
  246.             });
  247.           } else {
  248.             const type = getState().types[context.type];
  249.             return new Form({
  250.               validator(vs) {
  251.                 if (type.validate_attributes) {
  252.                   const res = type.validate_attributes(vs);
  253.                   if (!res) return req.__("Invalid attributes");
  254.                 }
  255.               },
  256.               fields: translateAttributes(type.attributes, req),
  257.             });
  258.           }
  259.         },
  260.       },
  261.       {
  262.         name: req.__("Expression"),
  263.         onlyWhen: (context) => context.calculated,
  264.         form: async (context) => {
  265.           const table = await Table.findOne({ id: context.table_id });
  266.           const fields = await table.getFields();
  267.           return new Form({
  268.             blurb: expressionBlurb(context.type, context.stored, fields, req),
  269.             fields: [
  270.               new Field({
  271.                 name: "expression",
  272.                 label: req.__("Formula"),
  273.                 // todo sublabel
  274.                 type: "String",
  275.                 validator: expressionValidator,
  276.               }),
  277.               new Field({
  278.                 name: "test_btn",
  279.                 label: req.__("Test"),
  280.                 // todo sublabel
  281.                 input_type: "custom_html",
  282.                 attributes: {
  283.                   html: `<button type="button" id="test_formula_btn" onclick="test_formula('${
  284.                     table.name
  285.                   }', ${JSON.stringify(
  286.                     context.stored
  287.                   )})" class="btn btn-outline-secondary">${req.__(
  288.                     "Test"
  289.                   )}</button>
  290.                   <div id="test_formula_output"></div>`,
  291.                 },
  292.               }),
  293.             ],
  294.           });
  295.         },
  296.       },
  297.       {
  298.         name: req.__("Summary"),
  299.         onlyWhen: (context) =>
  300.           context.type !== "File" && new Field(context).is_fkey,
  301.         form: async (context) => {
  302.           const fld = new Field(context);
  303.           const table = await Table.findOne({ name: fld.reftable_name });
  304.           const fields = await table.getFields();
  305.           const orderedFields = [
  306.             ...fields.filter((f) => !f.primary_key),
  307.             ...fields.filter((f) => f.primary_key),
  308.           ];
  309.           const keyfields = orderedFields
  310.             .filter((f) => !f.calculated || f.stored)
  311.             .map((f) => ({
  312.               value: f.name,
  313.               label: f.label,
  314.             }));
  315.           return new Form({
  316.             fields: [
  317.               new Field({
  318.                 name: "summary_field",
  319.                 label: req.__("Summary field"),
  320.                 input_type: "select",
  321.                 options: keyfields,
  322.               }),
  323.             ],
  324.           });
  325.         },
  326.       },
  327.       {
  328.         name: req.__("Default"),
  329.         onlyWhen: async (context) => {
  330.           if (!context.required || context.id || context.calculated)
  331.             return false;
  332.           const table = await Table.findOne({ id: context.table_id });
  333.           const nrows = await table.countRows();
  334.           return nrows > 0;
  335.         },
  336.         form: async (context) => {
  337.           const formfield = new Field({
  338.             name: "default",
  339.             label: req.__("Default"),
  340.             // todo sublabel
  341.             type: context.type,
  342.             required: true,
  343.             attributes: {
  344.               summary_field: context.summary_field,
  345.               ...(context.attributes || {}),
  346.             },
  347.           });
  348.           await formfield.fill_fkey_options();
  349.           return new Form({
  350.             blurb: req.__(
  351.               "A default value is required when adding required fields to nonempty tables"
  352.             ),
  353.             fields: [formfield],
  354.           });
  355.         },
  356.       },
  357.     ],
  358.   });
  359. router.get(
  360.   "/:id",
  361.   setTenant,
  362.   isAdmin,
  363.   error_catcher(async (req, res) => {
  364.     const { id } = req.params;
  365.     const field = await Field.findOne({ id });
  366.     const table = await Table.findOne({ id: field.table_id });
  367.     const wf = fieldFlow(req);
  368.     const wfres = await wf.run(
  369.       {
  370.         ...field.toJson,
  371.         ...field.attributes,
  372.       },
  373.       req
  374.     );
  375.     res.sendWrap(req.__(`Edit field`), {
  376.       above: [
  377.         {
  378.           type: "breadcrumbs",
  379.           crumbs: [
  380.             { text: req.__("Tables"), href: "/table" },
  381.             { href: `/table/${table.id}`, text: table.name },
  382.             { text: req.__(`Edit %s field`, field.label) },
  383.             { workflow: wf, step: wfres },
  384.           ],
  385.         },
  386.         {
  387.           type: "card",
  388.           title: wizardCardTitle(field.label, wf, wfres),
  389.           contents: renderForm(wfres.renderForm, req.csrfToken()),
  390.         },
  391.       ],
  392.     });
  393.   })
  394. );
  396. router.get(
  397.   "/new/:table_id",
  398.   setTenant,
  399.   isAdmin,
  400.   error_catcher(async (req, res) => {
  401.     const { table_id } = req.params;
  402.     const table = await Table.findOne({ id: table_id });
  403.     const wf = fieldFlow(req);
  404.     const wfres = await wf.run({ table_id: +table_id }, req);
  405.     res.sendWrap(req.__(`New field`), {
  406.       above: [
  407.         {
  408.           type: "breadcrumbs",
  409.           crumbs: [
  410.             { text: req.__("Tables"), href: "/table" },
  411.             { href: `/table/${table.id}`, text: table.name },
  412.             { text: req.__(`Add field`) },
  413.             //{ text: wfres.stepName },
  414.             { workflow: wf, step: wfres },
  415.           ],
  416.         },
  417.         {
  418.           type: "card",
  419.           title: wizardCardTitle(req.__(`New field`), wf, wfres),
  420.           contents: renderForm(wfres.renderForm, req.csrfToken()),
  421.         },
  422.       ],
  423.     });
  424.   })
  425. );
  427. router.post(
  428.   "/delete/:id",
  429.   setTenant,
  430.   isAdmin,
  431.   error_catcher(async (req, res) => {
  432.     const { id } = req.params;
  433.     const f = await Field.findOne({ id });
  434.     const table_id = f.table_id;
  436.     await f.delete();
  437.     req.flash("success", req.__(`Field %s deleted`, f.label));
  438.     res.redirect(`/table/${table_id}`);
  439.   })
  440. );
  442. router.post(
  443.   "/",
  444.   setTenant,
  445.   isAdmin,
  446.   error_catcher(async (req, res) => {
  447.     const wf = fieldFlow(req);
  448.     const wfres = await wf.run(req.body, req);
  449.     if (wfres.renderForm) {
  450.       const table = await Table.findOne({ id: wfres.context.table_id });
  451.       res.sendWrap(req.__(`Field attributes`), {
  452.         above: [
  453.           {
  454.             type: "breadcrumbs",
  455.             crumbs: [
  456.               { text: req.__("Tables"), href: "/table" },
  457.               { href: `/table/${table.id}`, text: table.name },
  458.               {
  459.                 text: req.__(
  460.                   `Edit %s field`,
  461.                   wfres.context.label || req.__("new")
  462.                 ),
  463.               },
  464.               { workflow: wf, step: wfres },
  465.             ],
  466.           },
  467.           {
  468.             type: "card",
  469.             title: wizardCardTitle(
  470.               wfres.context.label || req.__("New field"),
  471.               wf,
  472.               wfres
  473.             ),
  474.             contents: renderForm(wfres.renderForm, req.csrfToken()),
  475.           },
  476.         ],
  477.       });
  478.     } else {
  479.       if (wfres.flash) req.flash(...wfres.flash);
  480.       res.redirect(wfres.redirect);
  481.     }
  482.   })
  483. );
  485. router.post(
  486.   "/test-formula",
  487.   setTenant,
  488.   isAdmin,
  489.   error_catcher(async (req, res) => {
  490.     const { formula, tablename, stored } = req.body;
  491.     const table = await Table.findOne({ name: tablename });
  492.     const fields = await table.getFields();
  493.     const rows = await table.getRows({}, { orderBy: "RANDOM()", limit: 1 });
  494.     if (rows.length < 1) return "No rows in table";
  495.     let result;
  496.     try {
  497.       if (stored) {
  498.         const f = get_async_expression_function(formula, fields);
  499.         result = await f(rows[0]);
  500.       } else {
  501.         const f = get_expression_function(formula, fields);
  502.         result = f(rows[0]);
  503.       }
  504.       res.send(
  505.         `Result of running on row with id=${
  506.           rows[0].id
  507.         } is: <pre>${JSON.stringify(result)}</pre>`
  508.       );
  509.     } catch (e) {
  510.       return res.send(
  511.         `Error on running on row with id=${rows[0].id}: ${e.message}`
  512.       );
  513.     }
  514.   })
  515. );
  516. router.post(
  517.   "/show-calculated/:tableName/:fieldName/:fieldview",
  518.   setTenant,
  519.   isAdmin,
  520.   error_catcher(async (req, res) => {
  521.     const { tableName, fieldName, fieldview } = req.params;
  522.     const table = await Table.findOne({ name: tableName });
  523.     const fields = await table.getFields();
  524.     const field = fields.find((f) => f.name === fieldName);
  525.     const formula = field.expression;
  526.     const row = { ...req.body };
  527.     readState(row, fields);
  528.     let result;
  529.     try {
  530.       if (field.stored) {
  531.         const f = get_async_expression_function(formula, fields);
  532.         result = await f(row);
  533.       } else {
  534.         const f = get_expression_function(formula, fields);
  535.         result = f(row);
  536.       }
  537.       const fv = field.type.fieldviews[fieldview];
  538.       res.send(fv.run(result));
  539.     } catch (e) {
  540.       return res.status(400).send(`Error: ${e.message}`);
  541.     }
  542.   })
  543. );
  545. router.post(
  546.   "/preview/:tableName/:fieldName/:fieldview",
  547.   setTenant,
  548.   isAdmin,
  549.   error_catcher(async (req, res) => {
  550.     const { tableName, fieldName, fieldview } = req.params;
  551.     const table = await Table.findOne({ name: tableName });
  552.     const fields = await table.getFields();
  553.     let field, row, value;
  554.     if (fieldName.includes(".")) {
  555.       const [refNm, targetNm] = fieldName.split(".");
  556.       const ref = fields.find((f) => f.name === refNm);
  557.       const reftable = await Table.findOne({ name: ref.reftable_name });
  558.       const reffields = await reftable.getFields();
  559.       field = reffields.find((f) => f.name === targetNm);
  560.       row = await reftable.getRow({});
  561.       value = row && row[targetNm];
  562.     } else {
  563.       field = fields.find((f) => f.name === fieldName);
  564.       row = await table.getRow({});
  565.       value = row && row[fieldName];
  566.     }
  568.     const configuration = req.body.configuration;
  569.     if (!field) {
  570.       res.send("");
  571.       return;
  572.     }
  573.     const fieldviews =
  574.       field.type === "Key"
  575.         ? getState().keyFieldviews
  576.         : field.type === "File"
  577.         ? getState().fileviews
  578.         : field.type.fieldviews;
  579.     if (!field.type || !fieldviews) {
  580.       res.send("");
  581.       return;
  582.     }
  583.     const fv = fieldviews[fieldview];
  584.     if (!fv && field.type === "Key" && fieldview === "select")
  585.       res.send("<select disabled></select>");
  586.     else if (!fv) res.send("");
  587.     else if (fv.isEdit)
  588.       res.send(
  589.         fv.run(
  590.           field.name,
  591.           undefined,
  592.           { disabled: true, ...configuration },
  593.           "",
  594.           false,
  595.           field
  596.         )
  597.       );
  598.     else if (field.type === "File") {
  599.       res.send(fv.run(value, "filename.ext"));
  600.     } else res.send(fv.run(value, req, configuration));
  601.   })
  602. );
  604. router.post(
  605.   "/preview/:tableName/:fieldName/",
  606.   setTenant,
  607.   isAdmin,
  608.   error_catcher(async (req, res) => {
  609.     res.send("");
  610.   })
  611. );