Ask Reuben

QAUD – Query Add Update Delete

What is QAUD?

Can I write generic code to maintain a table?

QAUD is an acronym for Query Add Update Delete.  I am not sure where I first heard the term or where it came from, but it relates to the common pattern you see in Informix-4gl programs of

MENU "
    COMMAND "Query" 
      -- code that leads to a CONSTRUCT and then to a SELECT
   COMMAND "Add"
      -- code that leads to an INPUT then a INSERT
   COMMAND "Update"
      -- code that leads to an INPUT then an UPDATE
   COMMAND "Delete"
      -- code that leads to a DELETE, perhaps after a Yes/No confirmation dialog

typically this pattern will then have

   COMMAND "First"
      -- code that leads to a FETCH FIRST using the cursor prepared by the Query
   COMMAND "Previous"
      -- code that leads to a FETCH PREVIOUS using the cursor prepared by the Query
   COMMAND "Next"
      -- code that leads to a FETCH NEXT using the cursor prepared by the Query
   COMMAND "Last"
      -- code that leads to a FETCH LAST using the cursor prepared by the Query

and finishing with a

   COMMAND "Exit"
      -- code that leads to the user exiting the program

The characteristic of all these COMMAND entries is that they all start with a different letter so the keys Q, A, U, D, F, P, N, L, E can be used by the user to signify their action with one keystroke in a TUI environment,

Variations of the above might use different words and keystrokes if they were unique.  So I for Insert instead of A for Add, R for Remove instead of D for Delete etc.

Additional functionality might have been added with a suitable keystrokes if a unique word/key could be found i.e

COMMAND "Browse"
   -- code that leads to a DISPLAY ARRAY of the cursor prepared by the Query
COMMAND "Go"
   -- code that leads to a dialog to navigate to a certain record
COMMAND "Xport"
   -- code that leads to an UNLOAD
COMMAND "Import"
   -- code that leads to a bulk import using a LOAD or perhaps an INPUT ARRAY
COMMAND "Report"
   -- code that leads to a printout
COMMAND 'Other"
   -- code that leads to a sub-menu with more options

With a transformation from Informix-4gl to Genero you might enhance this code by …

  • replacing the COMMAND with ON ACTION
  • adding the single-key accelerator to the ActionDefault entry for the entry
  • adding Toolbar and TopMenu entries  that map to these actions.

Your program might have gone from looking like…

… and been transformed to now look like …

What you might find is that in your system you have over 100 programs that have this pattern that are used to maintain a single table.  This pattern might have even been expanded to maintain two or more tables that have a master-detail relationship.  Say there was a 100 of them, you might look at them and think I have 100 programs all with …

  • effectively the same MENU statement
  • effectively the same INPUT, CONSTRUCT dialogs
  • effectively the same INSERT, UPDATE, DELETE database statements
  • similar forms
  • the only thing different is table and column names and the business rules that dictate if you can see a value, edit a value, what the default value is, is the field valid etc.

… you might think to yourself, how can I code this QAUD pattern smarter?   Similar to what fgl_zoom does in allowing you to code BUTTONEDIT lookups smarter.

As I write this during the 4.00 EAP program I think there is some new functionality in the 4.00 that will allow you to code this QAUD pattern smarter and this is why I have chosen to post this Ask-Reuben article now.

We have already in our toolbox

  • Dynamic Dialogs that will allow you to code the dialog statements (INPUT, CONSTRUCT, DISPLAY ARRAY, INPUT ARRAY) generically once
  • base.Sqlhandle that will allow you to code the INSERT, UPDATE, DELETE statements generically once
  • DICTIONARY that allows you to define as associate array of elements
  • IMPORT FGL that allows you to reference modules instead of linking
  • Function References that allow you to reference dynamically and invoke a function in a call or expression

We can see some of these in use with the dbbrowser demo that is in FGLDIR/demo/dbbrowser from Genero 3.00 on.  dbbrowser is like a QAUD program only it displays an array rather than a single record and uses row modification triggers for the add, update, delete of a business row.  If you want to see something interesting with dbbrowser, create the following program…

IMPORT FGL dbbrowser

MAIN
    OPTIONS FIELD ORDER FORM
    OPTIONS INPUT WRAP
    TRY
        CONNECT TO "stores.dbs+driver='sqlite'"
    CATCH
        EXIT PROGRAM 1
    END TRY
    CALL dbbrowser.browseTable("state")  -- change this parameter to point to any of the other database tables, 
END MAIN

Hopefully you can see and verify that this program allows you to query, view any of your database tables and the only thing you have to change is the table name passed as a parameter to dbbrowser.browseTable().

What dbbrowser does not have is any business rules or logic.  Part of the reason for that is that if you have something like …

DEFINE state_type RECORD LIKE state.*

FUNCTION valid_state(rec state_type) RETURNS BOOLEAN, STRING

… if you attempt to create a solution that passes the validation function to dbbrowser via function references i.e.

CALL dbbrowser.browserTable("state", FUNCTION valid_state)

… you are hamstrung by the fact that

“In order to assign a function reference to a variable, the variable must have been defined with a function type that matches the referenced function: The function parameter names and types, as well as the return types must be the same. If the signatures of the function type and function reference do not match, the compiler produces the error -6631.”

(see Important tip here https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_Functions_FUNCTION_selector.html).

FUNCTION valid_state(rec RECORD LIKE state.*) … has different arguments than FUNCTION valid_customer(rec RECORD LIKE customer.*) … etc

What Genero 4.00  introduces is reflection.  This allows us to effectively have a pointer to a record.   You can then pass this pointer to the function.  Using this you can define a function type similar to

TYPE valid_function_type FUNCTION (rv reflect.Value) RETURNS (BOOLEAN, STRING)

You can implement the validation function to take as input the reflection parameter and the first thing it should do is assign the reflection to a variable, and then you can code your validation function as you normally would.

FUNCTION valid_state(rv reflect.Value) RETURNS (BOOLEAN, STRING)
    DEFINE state_rec RECORD LIKE state.*

   CALL rv.assignToVariable(state_rec)
    ...
END FUNCTION

Your generic code can then do something simplistic like …

CALL  mydbbrowser.browserTable("state", FUNCTION valid_state)

You’ll want more than one function for your business rules so you may end up with something like …

TYPE my_business_rules_type RECORD
    field_validation_function DICTIONARY OF valid_field_function_type ,
    field_visible_function DICTIONARY OF field_visible_function_type,
    field_enabled_function DICTIONARY OF field_enabled_function_type
...
END RECORD

… and then your unique code to maintain a particular table is something like …

DEFINE state_business_rules mydbbrowser.my_business_rules_type 

LET state_business_rules.field_validation_function["code"] = FUNCTION valid_state_code
LET state_business_rules.field_validation_function["sname"] = FUNCTION valid_state_sname
...
CALL mydbbrowser.browserTable("state", state_business_rules)
...
FUNCTION valid_state_code(rv reflect.Value)
...
END FUNCTION

FUNCTION valid_state_name(rv reflect.Value)
...
END FUNCTION

… that is the only unique code is the business logic.  Those who maintain a code base that was derived from FourGen’s code generator may recognise that these unique business rules are what they code in triggers and extensions.  You may also recognise similarities to the recent developments with Business Application Modelling and Code Events.

I hope I have whetted some appetites, aroused some interest, that there might be something in the 4.00 Early Access Program that may allow you to write generic code for the QAUD pattern, rather than repeating code, and if you have not already, please register for the Early Access Program and see in what directions you can take this new functionality.