Field validation in rodakino RAD

1. Download and install the new rodakino version

You find the new version here: rodakino_0_1_1.tgz

Also look here for  rodakino RAD download and installation . Be sure to install rodakino_0_1_1.tgz, not rodakino_0_1_0.tgz !!!

 

Goal: Developers should be able to add field validation functions to a rodakino database window.

Use cases are:

* Local field validation (am I a number, date, check for mandatory field, ...)

* Validate a field against a database table (e.g. foreign key or part of foreign key)

* Adjust the field content after change (add or remove leading zeros, ...)

* fill in other fields in the row, depending on the content of the changed field.

 

2. Create two database tables

For the tutorial to work, we need two more database tables:

create table COUNTRIES (
    COUNTRY_ABBREV varchar(10),
    COUNTRY_NAME varchar(70),
    constraint PK_COUNTRIES primary key (COUNTRY_ABBREV)
);

 

create table ZIPCODES_ADV (                                   /* advanced zip codes  */
    COUNTRY_ABBREV varchar(10),
    ZIPCODE varchar(10),
    CITY varchar(50),
    constraint PK_ZIPCODES_ADV primary key (COUNTRY_ABBREV,ZIPCODE,CITY)
);

 

alter table ZIPCODES_ADV add constraint FK01_ZIPCODES_ADV
  foreign key (COUNTRY_ABBREV) references COUNTRIES(COUNTRY_ABBREV);

 

grant select,insert,update,delete on COUNTRIES to test;
grant select,insert,update,delete on ZIPCODES_ADV to test;

 

3. Create menu items for Countries and advanced Zipcodes

-- insert menu item for rdbw_countries
insert into RODA_MENUS( MENUID,MENU_PROMPT,MENU_CLASS,OBJECT2CALL,PARENT_MENUID,SORT_ORDER,DELETED)
    values('countries','countries','W','rw_countries','mymenu',1001,'N');

insert into MENU_PROFILE_ITEMS(MENU_PROFILE,MENUID,READONLY)
  values('TEST','countries','N');

 

-- insert menu item for rdbw_zipcodes_adv
insert into RODA_MENUS( MENUID,MENU_PROMPT,MENU_CLASS,OBJECT2CALL,PARENT_MENUID,SORT_ORDER,DELETED)
    values('zipcodes_adv','Advanced Zipcodes','W','rw_zipcodes_adv','mymenu',1002,'N');

insert into MENU_PROFILE_ITEMS(MENU_PROFILE,MENUID,READONLY)
  values('TEST','zipcodes_adv','N');

 

4. Create two new rodakino database windows

Now we create two new rodakino database windows,

one for table COUNTRIES, one for rable ZIPCODES_ADV (i.e. advanced zipcodes):

 

First, we add the two files for the new rdbw_countries into ~/rodakino/app/roda_dbwindows:

You find the files here: rdbw_countries.hbs and rdbw_countries_css.hbs

 

Second, we add the two files for the new rdbw_zipcodes_adv into ~/rodakino/app/roda_dbwindows:

You find the files here:  rdbw_zipcodes_adv.hbs and rdbw_zipcodes_adv_css.hbs

 

5. Create two new rodakino windows

create two new directories named rw_countries and rw_zipcodes_adv in ~/rodakino/app/roda_windows.

 

First, we create the rodakino window for COUNTRIES:

Add new file index.js in rw_countries:

module.exports = {
  init: require('./init')
}

Add new file init.js in rw_countries:

const passport = require('passport')

function initUser (app) {
  app.get('/rw_countries', passport.authenticationMiddleware(), (req, res) => {
    res.render('roda_windows/rw_countries/rw_countries', {
      // dont use the default layout
      layout: false,
      id: req.params.id
    })
  })
}

module.exports = initUser

 

Add new file rw_countries.hbs in rw_countries:

<script type="text/javascript">
  // create instance of master dbwindow
  var rdbw_master = $("#rdbw_countries").roda_dbwindow({
    sql: "select COUNTRY_ABBREV,COUNTRY_NAME from COUNTRIES " +
         "%WHERE_CLAUSE% order by 1",
    insSql: "insert into COUNTRIES values('%COUNTRY_ABBREV%','%COUNTRY_NAME%')",
    updSql: "update COUNTRIES " +
              "set COUNTRY_ABBREV = '%COUNTRY_ABBREV%'," +
              "COUNTRY_NAME = '%COUNTRY_NAME%' " +
              "where COUNTRY_ABBREV = '%_COUNTRY_ABBREV%' ",  // %_ ... means original value of db-field
    delSql: "delete from COUNTRIES " +
              "where COUNTRY_ABBREV = '%_COUNTRY_ABBREV%' ", // %_ ... means original value of db-field
    validateField: function(rowFields,actField,actRow,checkField,callback) {
      // validate fields
      // rowFields: {fieldname: fieldvalue, ...}
      // actField: Name of field to validate
      // actRow: actual roda_row of rdbw, can be used to set field values
      // checkField: field that is checked and gains focus back, if error
      // callback: callback to set focus back

      // everything is OK, no error
      return null;
    }
  });

  function onDivLoad(myName) {
    // function is called on ajax success after load
    console.log('rw_countries.onDivLoad.1 myName: '+ myName);

    // i am the active roda_window. !! necessary for roda_menubar to work !!
    act_roda_window = myName;

    // resize the dialog
    $("#"+myName).roda_window("option","height",550);
    $("#"+myName).roda_window("option","width",660);

    // set master roda_dbwindow
    $("#"+myName).roda_window("option","rdbw_master","rdbw_countries");
    // call retrieve for master roda_dbwindow
    rdbw_master.roda_dbwindow("retrieve",true);
  }

</script>

<!-- HTML -->
<h4>Countries</h4>

<!-- import Code of Master roda_dbwindow -->
{{> roda_dbwindows/rdbw_countries }}

 

Second, we create the rodakino window for ZIPCODES_ADV:

Add new file index.js in rw_zipcodes_adv:

module.exports = {
  init: require('./init')
}

Add new file init.js in rw_zipcodes_adv:

const passport = require('passport')

function initUser (app) {
  app.get('/rw_zipcodes_adv', passport.authenticationMiddleware(), (req, res) => {
    res.render('roda_windows/rw_zipcodes_adv/rw_zipcodes_adv', {
      // dont use the default layout
      layout: false,
      id: req.params.id
    })
  })
}

module.exports = initUser

 

Add new file rw_countries.hbs in rw_zipcodes_adv:

<script type="text/javascript">
  // create instance of master dbwindow
  var rdbw_master = $("#rdbw_zipcodes_adv").roda_dbwindow({
    sql: "select Z.COUNTRY_ABBREV,C.COUNTRY_NAME," +
         "Z.ZIPCODE,Z.CITY from ZIPCODES_ADV Z " +
         "inner join COUNTRIES C on Z.COUNTRY_ABBREV = C.COUNTRY_ABBREV " +
         "%WHERE_CLAUSE% order by 1,3",
    insSql: "insert into ZIPCODES_ADV values('%COUNTRY_ABBREV%','%ZIPCODE%','%CITY%')",
    updSql: "update ZIPCODES_ADV " +
              "set COUNTRY_ABBREV = '%COUNTRY_ABBREV%'," +
              "ZIPCODE = '%ZIPCODE%'," +
              "CITY = '%CITY%' " +
              "where COUNTRY_ABBREV = '%_COUNTRY_ABBREV%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' " +
              "and CITY = '%_CITY%'",
    delSql: "delete from ZIPCODES_ADV " +
              "where COUNTRY_ABBREV = '%_COUNTRY_ABBREV%' " + // %_ ... means original value of db-field
              "and ZIPCODE = '%_ZIPCODE%' " +
              "and CITY = '%_CITY%'",
    validateField: function(rowFields,actField,actRow,checkField,callback) {
      // validate fields
      // rowFields: {fieldname: fieldvalue, ...}
      // actField: Name of field to validate
      // actRow: actual roda_row of rdbw, can be used to set field values
      // checkField: field that is checked and gains focus back, if error
      // callback: callback to set focus back
      if (actField == "COUNTRY_ABBREV") {
        var country_abbrev = rowFields[actField];
        if (country_abbrev.length <= 0) {
          callback(checkField,"Country Abbrev. must be filled in!");
        } else {
          // check if foreign key exists in db
          mySql = "select COUNTRY_NAME from COUNTRIES " +
                  " where COUNTRY_ABBREV = '" + country_abbrev +"'";
          $.ajax({
            type: "POST",
            dataType: "json",
            contentType: "application/json; charset=utf-8",
            url: "selectdb",
            data: JSON.stringify({sql: mySql}),
            success: function(data){
              var found = false;
              var country_name = '';
              for (var index in data.res.rows){
                found = true;
                country_name = data.res.rows[index]['country_name'];
                console.log("validateField.1 country_name: " + country_name);
                break;
              };
              // no foreign key match found
              if (!found) {
                callback(checkField,"This country does'nt exist - retry.");
              } else {
                // fill found country_name in roda_field
                actRow.find("[name='COUNTRY_NAME']").val(country_name);
                // eventually remove some fields, which are'nt matching the new country?
                // ....
              };
            }
          });
        }      
      } else if (actField == "ZIPCODE") {
        if (rowFields[actField].length <= 0) {
          callback(checkField,"Zipcode must be filled in!");
        }
      } else if (actField == "CITY") {
        if (rowFields[actField].length <= 0) {
          callback(checkField,"City must be filled in!");
        }
      };
      // everything is OK, no error
      return null;
    }
  });

  function onDivLoad(myName) {
    // function is called on ajax success after load
    console.log('rw_zipcodes_adv.onDivLoad.1 myName: '+ myName);

    // i am the active roda_window. !! necessary for roda_menubar to work !!
    act_roda_window = myName;

    // resize the dialog
    $("#"+myName).roda_window("option","height",550);
    $("#"+myName).roda_window("option","width",660);

    // set master roda_dbwindow
    $("#"+myName).roda_window("option","rdbw_master","rdbw_zipcodes_adv");
    // call retrieve for master roda_dbwindow
    rdbw_master.roda_dbwindow("retrieve",true);
  }

</script>

<!-- HTML -->
<h4>Advanced Zipcodes</h4>

<!-- import Code of Master roda_dbwindow -->
{{> roda_dbwindows/rdbw_zipcodes_adv }}

 

What's new here:

We have a new function validateField.

1. It checks the three fields COUNTRY_ABBREV, ZIPCODE and CITY, if they are not empty.

2. It also checks (via ajax and SQL) the content of field COUNTRY_ABBREV, if such a country-abbreviation exists in table COUNTRIES.

3. If a country was found, then the country name is filled into the the current record.

4. The callback function is used to display error messages.