Bill and Todd's Awesome Crud

This library contains custom hooks to make it easier to use WHI's query and crud rest endpoints defined in the REST_SQL_QUERY and REST_CRUD_ENDPOINTS tables. It also includes UI components to build dynamic tables and forms using the state and controller functions returned from those hooks.

Documentation

• Getting Started
• Usage Overview
• useQueryApi
• useCrudApi
• Column properties
• CrudTable component
• QueryTable component

Examples

• Drop Downs (e.g. select/options)
• URL parameters
• Expandable rows
• Table tips/Examples
  • Use display:none to hide a column but keep it in searches
• Forms tips/Example
  • Improving the look of your forms
  • Generating id values for inserts
  • Columns where the DB generates values (e.g. sids, insert timestamps)
  • Custom field validation with Yup
  • Force input to be uppercase
  • Custom actions
• Using sub components for custom UI

Get Started

Install whireact and its dependancies...

npm i whireact
npm i axiosprimereact primeicons bootstrap react-transition-group date-fns formik yup

Then add these imports to your index.js if they dont already exist

import "bootstrap/dist/css/bootstrap.min.css"
import "primereact/resources/themes/md-light-indigo/theme.css"
import "primereact/resources/primereact.min.css"
import "primeicons/primeicons.css"

import "whireact/dist/css/whireact.css"

Overview

This library has code to make it easier to fetch data from our endpoints stored in the REST_SQL_QUERY and REST_CRUD_ENDPOINTS tables and UI components that build dynamic tables and forms using the state and functions returned from those hooks.

Custom hooks

• useQueryApi - fetch data from a rest_sql_query endpoint
• useCrudApi - fetch data from a rest_crud_endpoints endpoint and return functions that make it easier to do crud operations for that endpoint.

Both of these take a url and return an array with two values, a state object and a controller object. The state object holds the data returned from the url and various other state values (e.g. isLoading, etc...). The controller returns utility functions needed by the components for manipulating the data.

e.g.

const [qcatState, qcatCtrl] = useCrudApi(`https://devccc.whi.org/api/crud/F2_CAT`)

UI components

• QueryTable
• CrudTable

These components take the state and controller values from the custom hook and an optional columns array that describes the columns to display and return a dynamic Primereact datatable component that has sorting, filtering, paging and csv download capabilities. The CrudTable component also creates a dynamic Formik form for insert and update.

e.g. code to build a CRUD screen from the fact categories table (F2_CAT endpoint)...

import React from "react"
import { useCrudApi, CrudTable } from "whireact"

export function Categories() {
   const [qcatState, qcatCtrl] = useCrudApi(`https://devccc.whi.org/api/crud/F2_CAT`)

   const columns = [
      { name: "QCAT_ID", label: "id"},
      { name: "QCAT_NAME", label: "Name" },
      { name: "QCAT_SHORT_NAME", label: "Short name" }
   ]

   return <CrudTable columns={columns} state={qcatState} controller={qcatCtrl} />
}

This code returns a screen that looks like this...

Custom hooks

useQueryApi

Fetches data from a rest_sql_queries endpoint

Usage example

const [dtypState, dtypCtrl] = useQueryApi(`https://devccc.whi.org/api/query/F2_DATA_TYPES`)

Parameters

• url - must be a valid endpoint as defined in the REST_SQL_QUERIES table. This url can contain parameters e.g. "...MYURL?parentId=5"
• options - optional. An object of callback functions to allow data manipulation before and after crud operations. Should rarely have to use these.
  • processFetchedData - function that runs right after a fetch runs. It takes the fetched data as a parameter and returns the modified data object. Use this if you need to manipulate the raw data in some way.
  • handleError - function to replace default error handling
  • initialFilters - initial value for server side filters, see below about server side filtering.

Returns an array with two objects

• state - holds the various state variables related to fetching data

  • isLoading - data fetch is running
  • data - the data fetched from the url
  • isError - true if an error occurred during data fetch
  • defaultColumns - array of columns from metadata returned from url

• controller - Utility functions to update the data array appropriately.

  • setUrl - Change the url. Setting this will refetch the data.
    This is usually used in a filtering scenario. e.g. called in a useEffect to refetch when a user changes the value of a url parameter value.
  • refreshData - refetches data
  • setGlobalFilter - Sets the text value used for datatable globalfilter property.
  • setServerSideFilters - an array of objects to be passed to the server to do server side column filtering

useCrudApi

A custom hook to do CRUD operations for endpoints defined in REST_CRUD_ENDPOINTS.

In addition to the capabilities of useQueryApi, this encapsulates the common state variables and functions needed in crud screens. It fetches data from the url passed in and returns the functions to do crud rest calls and refresh the state data array appropriately after the crud operations complete successfully.

This is often used with the CrudTable component, but can be used with your own custom UI code.

Usage example

const [dtypState, dtypCtrl] = useCrudApi(`${CRUDURL}/F2_DTYP`)

Properties

• url - must refer to a row in the REST_CRUD_ENDPOINTS table. This url can contain parameters e.g. "...MYURL?parentId=5"
• options - optional. An object of callback functions to allow data manipulation before and after crud operations. Should rarely have to use these.
  • processFetchedData - function that runs right after a fetch runs. It takes the fetched data as a parameter and returns the modified data object. Use this if you need to manipulate the raw data in some way.
  • handleError - function to replace default error handling
  • initialFilters - initial value for server side filters, see below about server side filtering.
  • preProcessEditItem - function to modify the editItem object before insert or update. gets passed editItem and returns the modified editItem
  • preInsert - async function to execute before the insert (POST) has fired. Can be used for validation, and will suspend insert completion if an error is thrown
  • preUpdate - async function to execute before the update (PUT) has fired. Can be used for validation, and will suspend update completion if an error is thrown
  • preDelete - async function to execute before the delete (DELETE) has fired. Can be used for validation, and will suspend update completion if an error is thrown
  • postInsert - async function to execute after the insert (POST) has fired, but before the new item is added to the data array. Gets passed the return value from the insert and returns the object to put into the data array
  • postUpdate - async function to execute after the update (PUT) has fired, but before the data is updated, gets passed the return value from the edit and returns the object to put into the data array
  • dbDelete - async function to replace the normal db delete operation. e.g. if the object has children that need to be deleted, you can use this to replace the normal delete with a rest call that fires a db procedure to delete the row and its children.

Return - an array with two objects

• state - holds the various state variables related to fetching data and crud operations

  • isLoading - data fetch is running
  • data - the data fetched from the url
  • isError - true if an error occurred during data fetch
  • editItem - the object to be edited in the form. undefined if no item is currently being edited.
  • insertMode - boolean, true if the form will call doInsert instead of doUpdate when submitting.
  • defaultColumns - array of columns from metadata returned from url

• controller - Functions to execute the crud operations and update the data array appropriately. Usually this entire object is passed to CrudTable. If you are building a custom UI would you need to call these individually.

  • setUrl - Change the CRUD url. Setting this will refetch the data. This is usually used in a filtering scenario. e.g. called in a useEffect when a user changes the value of a url parameter value.
  • refreshData - refetches data
  • setGlobalFilter - Sets the text value used for datatable globalfilter property.
  • setServerSideFilters - an array of objects to be passed to the server to do server side column filtering
  • doInsert - Does an insert (POST) and then adds the new row (the return value from the insert operation) to the top of the data array
  • doUpdate - Does an update (PUT) and then replaces the corresponding row in data with the value returned from the update operation
  • doDelete - Does a delete (DELETE) and removes the row from the data array.
  • setEditItem - Set the item that should appear in the edit form
  • setNewItem - Sets insertMode to true and sets editItem to the value passed in.
  • cancelEdit - sets editItem to undefined and resets insertMode to false

Notes

• You can have several useCrudAPI calls pointing to different urls in the same screen.
• you dont have to use the crud capabilities, you can use this to just fetch data, although useQueryApi is designed for this usage.

UI Components

Column Array

The UI components require an array of column data to control how the columns are displayed

Column Properties

• label - required, label to use for table column and form input field

• name - name of column in the data array. Can be null if this is a table column of calculated data (e.g. uses the body table parameter. table: {body: ()={}}).

• columnType - The type of column to use in the table and form. if null, assumes a text field. (except when options is specified, see below). Can be one of...

  • date - converts Javascript Date values to a string format.
    (MM/dd/yyyy is the default, you can use the format property to specify a different date format)
  • check - checkbox, defaults to Y/N as values (use checkValues property to specify other values)
  • Y/N - shortcut for a checkbox with Y and N values saved to the database
  • 1/0 - shortcut for a checkbox with 1 and 0 values saved to the database
  • textArea
  • autocomplete
  • number - input will enforce that value is a number
  • money - displays as data formatted as money
  • select - select item, no need to set this manually, set automatically when the options parameter is set
  • custom - a custom input component is passed in using the input.customComponent prop

• options - an array of option values for a select column

  • Each element in the array must contain the keys "value" and "label". Other key-value pairs can exist in array
  • If this is defined, columnType will be assigned in code to "select", you dont need to specify columnType
  • The value displayed in the datatable will be the label value that corresponds to column value, i.e. it does the lookup
  • if the required property is not set to true, a blank row {value:"", label:""} will be added to the top of the options array if one doesnt already exist.

• table - optional. Either a boolean false or an object holding properties for the DataTable Column

  • Set this to false if column should not appear in the DataTable, e.g. table: false
  • Otherwise this is an object of properties values that will be sent to the PrimeReact DataTable Column component. You can use any property mentioned in the PrimeReact Column documentation
    e.g. use body the body parameter to customize the column contents.

    table: { body: row => {row.COL.toUpperCase()}, className="blue" }.

• input - optional. Either a boolean false or an object holding properties for the Formik Field component

  • set this to false if column should not appear in the form, e.g. input: false
  • Can be an object with properties to send to the Formik Field element, e.g. input: { className: "me-2" }
  • Special properties - Any property appropriate for a formik Field object is valid. however, there are some special properties used by the dynamic form code
    • required - set to true if field is required (used by default yup schema validation)
    • default - the default value. Used for inserts, see example below for code using this field to set a calculated id value for new rows.
    • yup - yup rule for column.(overrides default yup rule for that column) e.g.

     input: { yup: yup.string().required('First Name is required')
       .max(20, 'First Name: Max 20 chars'), }

    • invisible - boolean - used for columns that need to be in the insert/update statements but are not entered by the user, e.g. a db generated id number.
    • noUpdate - boolean - if true, column will not show up for edit, but will appear for insert, e.g. for ids or names that shouldn't be updated.
    • fieldSet - string - all fields with the same fieldSet value will be grouped in a fieldset box
    • newLine - boolean - force a new line before displaying input
    • style - for properties you want to affect the Edit Form

     input: { style: { width: "25%", textAlign: "center" } }

• checkValues - Optional. For "columnType: "check"" columns, this array defines the values matching checked and not checked e.g. ["Y","N"] (checked/true value is first in array)

• format - for columnType: "date" fields the date format to use, defaults to MM/dd/yyyy (uses datefns date formats)

CrudTable component

This component creates a Primereact Datatable and a Formik form for data entry. This has the following features by default, but you can use properties to modify or turn off some of these features

• One column in the datatable for each row in columns array (unless table=false)
• Action column with edit and delete buttons
• New button in header to create new records
• Double clicking on a row also starts an edit
• Column sorting turned on by default
• Global filtering
• Server side column filtering (the drop down next to the search input box)
• Csv downloads
• Dynamic form with an input item for every row in the columns array (unless input=false). Form appears in a dialog box by default, but inplace editing (i.e. edit in the datatable row) can be turned on with the inplace property
• can add Yup validation for fields

Properties

Commonly used properties

• state - the state variable from useCrudApi. required
• controller - the controller from useCrudApi. required
• columns - the array describing the columns to add to the table and form. required
• heading - heading to use for the table and form
• rows - turns on paging if number of rows is greater than this property, defaults to null
• yupSchema - yupSchema to validate edits

Other properties to customize UI and behavior

• csvFileName - the filename to use for csv files, defaults to the heading with spaces changed to underscores
• className - class name(s) to apply to the div surrounding the dataTable and form
• style - style to apply to the div surrounding the dataTable and form
• noNew - if true, no insert button will be created
• noEdit - If true, no edit button will be created
• noDelete - If true, no delete button will be created
• noCSV - if true no CSV button will be created
• noFilter - if true no global filter input will be created
• inplace - if true, will display input fields in the table row rather than as a form in a dialog.
• getDialogHeader = function to generate the heading for the edit dialog. defaults to state => (state.insertMode ? "Create new item " :`Edit ${heading ? heading : ""}`).
• defaultNewItem - an optional object to use for inserts. default is an object with "" for all values that do not have a default specified.
• customAction - a function that returns a component that will be added to the buttons in the actions column of the DataTable. Gets passed in the current row
• customHeader - optional code to replace entire header
• headerLeftSection - optional code to replace left section of header
• headerRightSection - optional custom code to replace right section of header
• onRowDoubleClick - optional code to execute on double click
• dialogProps - props to pass to dialog form
• tableProps - props to pass to table. Can be any
  • rowExpansionTemplate - see primereact datatable docs, if specified this turns on other code to make the row expansion work, e.g. the expansion column and a rowExpansionToggle trigger

QueryTable component

Similar to CrudTable without the crud abilities

Code Examples

Simple Usage

const [qcatState, qcatCtrl] = useCrudApi(`${CRUDURL}/F2_CAT`)
if (qcatState.isLoading) return "Loading..."

const columns = [
   { name: "QCAT_ID", label: "id"},
   { name: "QCAT_NAME", label: "Name" },
   { name: "QCAT_SHORT_NAME", label: "Short name" }
]

return <CrudTable columns={columns} state={qcatState} controller={qcatCtrl} />\

Screen with lookup fields (options) and generating id values for inserts

This screen will create a crud table and screen for datasources

• it calls useCrudApi to query audience rows and uses those as option values for the DSRC_AUD_ID field. The table will use the options array to display the AUD_NAME value that corresponds to the DSRC_AUD_ID value.
• a default value is set for the id column based on a function that calculates the next id value.

const [dsrcState, dsrcCtrl] = useCrudApi((`${CRUDURL}/F2_DATASOURCES`)
const [audienceState, audienceCtrl] = useCrudApi((`${CRUDURL}/F2_AUD`)  //not using crud capabilities, just querying.

if (dsrcState.isLoading) return "Loading..."

//add value and label fields required by options parameter
const audiences = audienceState.data.map(row => ({...row, value: row.AUD_ID, label: row.AUD_NAME }))

const maxId = getNextId(dsrcState.data, "DSRC_ID")

const columns = [
   {name: "DSRC_ID", label: "id", input: { default: maxId }},
   {name: "DSRC_NAME", label: "Name"},
   {name: "DSRC_AUD_ID", label: "Audience", options: audiences}
]

return <CrudTable state={dsrcState} controller={dsrcCtrl} columns={columns} />

url parameters

Assume that we are passed in an audience value and only want to see datasources for that value. You can pass parameters on the url like so...

const [dsrcState, dsrcCtrl] = useCrudApi((`${CRUDURL}/F2_DATASOURCES?audId=${props.audId}`)

Dealing with columns where the DB generates the values (sids, insert timestamps, etc...)

For values generated by the database and dont need to be in the insert or update statements (e.g. insert_timestamp) you can just set input: false

      {
         name: "XXX_INSERT_TIMESTAMP",
         input: false
      },

the invisible parameter if true, will not display the field in the form, but will include the column in the insert and update statements.

For Client side code generated values (e.g. fk columns from a parent table or calculated id values), This is handy with the default parameter.

input: {invisible: true, default: <setvalue> }

if values are generated by db, use the invisible prop to make them not display in the form, but still get the column included in the insert/update statements

input: {invisible: true }

Tips to improve the look of your forms

• Change the size of the form fields to match your data. e.g. input: { style: { width: "3em" }}
• Set the row and col properties for textarea columns. e.g. input: {rows: 5, cols: 100}
• Use fieldSet to group related input fields. e.g. input: { fieldSet: "Publish to" } on all related fields
• Use newLine property to force next inputs onto a new line. e.g. input: { newLine: true}
• use dialogProps to set custom width for small forms

Force input to be uppercase

use the html onInput attribute to do this...

      {
         name: "DB_HOST_PORT_SID",
         label: "Host:Port:Sid",
         input: {
            onInput: e => (e.target.value = e.target.value.toUpperCase()),
         }
      },

Custom Yup validation

use the input yup property to define custom validation. See the Yup library documentation for more detail. Note some basic yup validation is already generated by the system, this replaces that validation

{
         name: "DB_HOST_PORT_SID",
         label: "Host:Port:Sid",
         input: {
            default: "SNAPSHOT:1521:XXX",
            yup: yup
               .string()
               .required()
               .matches(/\w+:\d+:\w+/, "invalid value for Host:Port:Sid")
         }
      },

 input: {
    yup: yup.string().required().max(3, "Max of 3 characters allowed")
 }

min max for number

yup.number().min(min).max(max).required(),

Custom actions

Here's an example from the fact query screen.

I'm adding a "copy" button to custom actions that takes makes a new object from the data of the current row and calls setNewItem with that object to allow creating a new query from a copy of an old one.

customAction={row => (
<button onClick={() => qryCtrl.setNewItem(newQryFrom(row))} title="Copy">
<FaRegCopy />
</button>
)}

Expandable rows

Pass a function to rowExpansionTemplate to define a detail component that displays when the user clicks on an expansion icon. e.g.

rowExpansionTemplate= {row => <MyDetailComponent rowData={row}>}

Using sub components for custom UI

if you need a more custom solution you can use some of the subcomponents that make up CrudTable. or just use useCrudApi with your own UI code.

<DynamicHeader> - has the heading, global filter input, new and csv buttons
<DynamicTable> - creates a dynamic table based on column info
<FormDialog> - wraps a dialog around <DynamicForm>
<DynamicForm> - builds a dynamic form based on column info. Displayed when state.editItem is not null

for example if you want to replace the datatable with your own custom code to display the data, but still want the crud operations and the filtering and csv functionality, You could do something like...

*Note I did not include the properties you would need to pass to the subcomponents

const [dsrcState, dsrcCtrl] = useCrudApi((`${CRUDURL}/F2_DATASOURCES`)

return (
<>
   <DynamicHeader heading="Columns" columns={dcolCols} state={dcolState} controller={dcolCtrl} />
   {dcolState.data.map(row => (
      <div key={row.DCOL_ID}>
         {row.DCOL_ORDER} {row.DCOL_COLUMN_NAME}
         <button onClick={() => dcolCtrl.setEditItem(row)}> Edit </button>
         <button onClick={() => dcolCtrl.doDelete(row)}> Delete </button>
      </div>
   ))}
   {dcolState.editItem && <FormDialog state={dcolState} controller={dcolCtrl} columns={dcolCols} />}
</>
)

Utility Functions

getNextId(array, column)

returns the max value+1 for a numeric column in the given array. Use this to get default values for spid columns