Source: server/routes/fields.js

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