Enable RLA via SQL interface

To begin using SQL to access your data we need to record some key information about the file/table. We will store this information in a javascript file called a DbDefn module.

For instance, you have a file/table in your database named "product". You would:

Enable DbDefn support within the configuration.
Name the Module File name --> product.DbDefn.js
1. The module file name must end with .DbDefn.js
2. The first part of the name "product" is how your pjs program(s) will reference it.
Describing the table:
1. dbObject - required - Set this to the actual database object name, can be the name of a table or a view.. (this does not need to match the file name).
2. useSQL - optional - Set this to true to enable all PJS modules to access this file using an SQL interface. This is only required is you are using an IBM i database. All other databases force this to true.
3. uniqueKey - optional - If the table/view does not have a primary (unique) keys defined then this is required. If this is a SQL view then this is required. If set, this will be used to correctly read 1 record at a time in a unique order.
  1. field - the database column name
  2. ascending: true or false if the column should be sorted in ascending order, default is true
4. dbSchema - optional - Set this to the actual database library/schema. You would normally not need to use this.
5. format - optional - When converting RPG programs to Profoundjs programs this is required. This is the record format name of the IBM i table.
6. overrides - optional - this is where you can override specific fields types and values
  1. There are several reasons where you may need this. Just to name a few:
    1. The database does not support storage of that type of field (such as with Oracle and a Time type field)
    2. The database you choose to stores dates as number or string, but you want the UI to show them as true dates
    3. The database you choose to stores 2 separate fields for Date and Time, but you want to put them together as a single Date and Time field
    4. The database does not allow storage with that same definition (such as char 1024, some database may require you to make that varying)
    5. The database does not support storing of packed value, but the converted module is coded with packed modules
7. dbConnection: An optional string can be specified that refers to a database connection name. If this property is not specified, the default database connection is used.

Below is showing an example of a couple different ways on how you can use these overrides.

Field named "prcatid" that is stored in the database as character, but will be referenced within the pjs programs as decimal.
Field name "prtime" that is stored in the database as character, but will be referenced with the pjs programs as time.

module.exports = {
  useSQL: true,
  dbObject: "product",


  overrides: {
    prcatid: {
      type: "decimal",
      length:5,
      decimals:0,
      dbTransforms: {
        toDb: function(value) { return value.toString().padStart(5,"0") },
        fromDb: function(value) { return Number(value) },
      }
    },
    prtime: {
      type: "time",
      dbTransforms: {
        toDb: profound.utils.timeToCharTransform,
        fromDb: profound.utils.charToTimeTransform
      }
    },
    prdesc: {
       varying: false
    }
  }
}

Once this module file is created, all pjs programs that do a pjs.defineTable(...) will then use and SQL interface to access the data.

If you are using an IBM i database, you can set this useSQL to false. This will cause your pjs modules to go back to using the standard IBMi record level file operations
If you delete this DbDefn module file, which will revert back to using the standard IBMi record level file operation commands
This DbDefn module file is required in order to use any of the RLA APIs for any non IBMi database.

Here is a simple pjs program that reads all the records of a table. It is just like any other pjs module using RLA. You do not need to change any of your logic to switch from standard RLA function to the new RLA via SQL functions.

function products() {
    pjs.defineTable("product", { read: true, keyed: true });
    product.positionTo(0);

    var data = [];
    var record = product.fetch();
    while (!pjs.endOfData()) {
        data.push(record);
        record = product.fetch();
    }
   
    console.log(JSON.stringify(data,null,2));
}

exports.run = products;