DB Lua Agent

Introduction

The DBLuaAgent is an asynchronous helper for Lua scripts running within the LogicApp. The DB Agent may be used by any Lua script, regardless of which Service initiated that script.

The DBLuaAgent communicates with one or more instances of the DBApp which is configured to communicate with an external database.

The DBLuaAgent and the DBApp support both DBI-style relational databases and MongoDB document-storage databases.

The DBLuaAgent communicates with the DBApp using the DB-… messages.

DB Agent methods are accessed via the “n2.n2svcd.db_agent” module:

local db_api = require "n2.n2svcd.db_agent"

Configuring DBLuaAgent

The DBLuaAgent is configured within a LogicApp.

<?xml version="1.0" encoding="utf-8"?>
<n2svcd>
  ...
  <applications>
    ...
    <application name="Logic" module="LogicApp">
      <include>
        <lib>../apps/logic/lib</lib>
      </include>
      <parameters>
        ...
        <parameter name="default_db_app_name" value="DB-App-PG1"/>
      </parameters>
      <config>
        <services>
          ...
        </services>
        <agents>
          <agent module="DBApp::DBLuaAgent" libs="../apps/db/lib">
            <config>
              <db_timeout_ms>500</db_timeout_ms>
            </config>
          </agent>
        </agents>
      </config>
    </application>
    ...
  </application>
  ...
</n2svcd>

Under normal installation, the following agent attributes apply:

Parameter Name Type XML Type Description
module DBApp::DBLuaAgent Attribute [Required] The module name containing the Lua Agent code.
libs ../apps/db/lib Attribute Location of the module for DBLuaAgent.
config Object Element Container for extended configuration for this Application instance.
db_timeout_ms Positive Integer Element How long the Lua script will wait for the DB Application to respond before abandoning the request.
Note that this period must include the time in the IPC transport layer, the time taken for the DB app to read the queue, any time spent waiting in the DB app for other preceding DB requests to complete, plus finally the time spent inside the actual DB method.
Hence this timeout value should be strictly longer than the sum of the db_request_expiry_ms and the action_timeout_ms DBApp parameters combined that are configured for the relevant DBApp.
(Default = 2000 milliseconds)

In addition, the DBLuaAgent must be configured with the name of the DBApp with which it will communicate. This is configured within the parameters of the containing LogicApp configuration.

Parameter Name Type XML Type Description
parameters Array Element Array of name = value Parameters for this Application instance.
.default_db_app_name String Attribute [Required] Default name for the DBApp with which DBLuaAgent will communicate, as per the Logic Application documentation.
.db_app_name_<route> String Attribute Use this format when DBLuaAgent will communicate with more than one DBApp, as per the Logic Application documentation.
(Default = DBLuaAgent uses only the default route/database)

The DBLuaAgent API

The DBLuaAgent API supports methods for both dbi and mongo databases. A DBApp application must be configured as either a dbi or a mongo, and the Lua script must use only the corresponding methods.

All methods may raise a Lua Error in the case of exception, including:

.dbi_select [Asynchronous]

The dbi_select method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
sql String The SQL SELECT string, including ? parameterized bind variables.
args Table A Lua list of values to substitute into the SQL binary parameters.

The dbi_select method returns a rows array (a Lua List).

Parameter Type Description
rows List List of returned row objects, one per row in the select query.

Example (DBI Select):

local n2svcd = require "n2.n2svcd"
local db_api = require "n2.n2svcd.db_agent"

local rest_args = ...

local args = {1}
local rows = db_api.dbi_select (nil, "SELECT name FROM table1 WHERE code = ?", args)

if (#rows == 0) then
  error ("No Rows Returned!")
end

return "Name for row with code = 1 is " .. rows[1].NAME

This example passes nil as the database identifier. This routes the database request to the default configured DBApp name. If more than one database is present, the route parameter specifies to which DBApp the request will be sent.

.dbi_do [Asynchronous]

The dbi_do method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
sql String The SQL INSERT/UPDATE/DELETE/EXECUTE string, including "?" parameterized bind variables.
args Table A Lua list of values to substitute into the SQL binary parameters, or a table of complex bind variables (see below for complex bind variable configuration).
options Table Additional options for dbi_do Options include:
  • returning - set to true if the dbi_do will return rows of data (e.g. using the PostgreSQL RETURNING keyword).

Passed args may be a list of bind variable values, or a table of bind variables with additional information. When passing complex bind variables, the passed table of args must be an key/value table. Keys define the bind variables (:var1 style) in the SQL statement, and the value can either be a simple value, or a table with the following options:

Parameter Type Description
value any basic type The value of the bind variable.
inout Integer Set to 1 for the bind variable to be "inout" when communicating with the DBI driver.
maxlen Integer Max buffer size in bytes of the bind variable. Default is 2000 bytes.
type Integer The type ID of the SQL type to bind the variable as. These may be driver dependent. E.g. see https://metacpan.org/pod/DBD::Oracle for the list of types available to the underlying Oracle DBD driver.

The dbi_do method returns a result object.

Parameter Type Description
result Table An object with the following attributes.
.nrows Integer The number of rows that were updated by the action.
.returned Table, or scalar value inout bind variables will returned as values in this table. If using the `RETURNING` keyword of PostgreSQL in the SQL statement, the first value of the returned data is returned as the value of returned

Example (DBI Update - simple bind variables):

The following shows how to perform a simple update with no returned data, and no complex bind variables:

    local args = { "My Princess", 343 }
    local result = db_api.dbi_do (nil, "UPDATE boats SET name = ? WHERE id = ?", args)

    if (result.nrows == 0) then
      error ("No Rows Updated!")
    end

Example (DBI Update - complex bind variables):

The following SQL shows how the Lua dbi_do API can be used to interact with an Oracle database with inout variables:

	local args = { 
		charging_engine_id = 1,
		acs_customer_id = 1001,
		currency_id = 13,
		be_acct_id = { inout = 1 },
		become_result = { inout = 1 }
	}

    local sql = "BEGIN \
      :become_result := ACS.becomeCustomerId (:acs_customer_id); \
      :be_acct_id := CCS_TOOLS.INSERT_CCS_ACCOUNT (:charging_engine_id, null, 0, 1, :currency_id, null); \
    END;"

    local result = db_api.dbi_do ("smf", sql, args)
    local nrows = result.nrows

    -- Insert failed.
    if (nrows < 1) then
        error "Account create inserted zero rows"
    end
    if (nrows > 1) then
        error "Account create inserted multiple rows"
    end

    -- Access the inout variable be_acct_id directly on the `returned` object
    return result.returned.be_acct_id

Example (DBI Update - returning data):

The following SQL shows how the Lua dbi_do API can be used to return data using the RETURNING keyword of PostgreSQL.

    local message = ...
    local sql = "INSERT INTO n2acd.migration_log (log_severity, log_message) VALUES (?, ?) RETURNING log_id"
    local result = db_api.dbi_do(nil, sql, { "warn", message }, { returning = true })
    local log_id = result.returned

.mongo_insert_one [Asynchronous]

The mongo_insert_one method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.

The mongo_insert_one method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.inserted Table An object with the following attributes.
.inserted_id String UUID of the record created.
.write_concern_errors Table A Lua list of potentital errors that occured when writing to the DB.
.write_errors Table A Lua list of errors that occured when writing to the DB.

Example (Mongo Insert One):

    local result = db_api.mongo_insert_one (nil, "boats" { name = "My Boat" })

    if (result.inserted == nil or result.inserted.inserted_id == nil) then
      error ("No Records Inserted!")
    end

.mongo_insert_many [Asynchronous]

The mongo_insert_many method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.

The mongo_insert_many method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.inserted Integer Any object with the following attributes.
.inserted_count Integer The number of records that were created by the action.
.inserted Table A Lua list of objects containing information for each inserted object.
.inserted_ids Table A Lua list of UUIDs for each record that was created.
.write_concern_errors Table A Lua list of potentital errors that occured when writing to the DB.
.write_errors Table A Lua list of errors that occured when writing to the DB.

Example (Mongo Insert Many):

    local result = db_api.insert_many (nil, "boats", { { name = "Boat 1"} , { name = "Boat 2" } })

    if (result.inserted == nil or result.i1nserted ~= 2) then
      error ("Failed to Create all Records!")
    end

.mongo_find [Asynchronous]

The mongo_find method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
filter Table A Lua object of filtering values to pass to the Mongo DB operation.
options Table A Lua object of options to pass to the Mongo DB operation.
projection Table A Lua object of projection values to pass to the Mongo DB operation.

The mongo_find method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.data Table A Lua list of objects that matched the provided search criteria.

Example (Mongo Find):

    local result = db_api.mongo_find (nil, "boats", { name = "My Boat" })

    if (result.data == nil or #result.data == 0) then
      error ("No Records Found!")
    end

.mongo_find_one [Asynchronous]

The mongo_find_one method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
filter Table A Lua object of filtering values to pass to the Mongo DB operation.
options Table A Lua object of options to pass to the Mongo DB operation.
projection Table A Lua object of projection values to pass to the Mongo DB operation.

The mongo_find_one method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.data Table A Lua object matching the first record to match the provided search criteria.

Example (Mongo Find One):

    local result = db_api.mongo_find_one (nil, "boats" { name = "My Boat" })

    if (result.data == nil) then
      error ("No Record Found!")
    end

.mongo_aggregate [Asynchronous]

The mongo_aggregate method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of pipeline values to pass to the Mongo DB operation.

The mongo_aggregate method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.data Table A Lua list of objects that matched the provided search pipeline.

Example (Mongo Aggregate):

    local aggregatePipeline = {
        {
            ["$lookup"] = {
                from           = "boat_types"
                , localField   = "boat_type"
                , foreignField = "boat_type_id"
                , as           = "boat_types"
            }
        }
    }
    local result = db_api.mongo_aggregate (nil, "boats", aggregatePipeline)

    if (result.data == nil or #result.data == 0) then
      error ("No Records Found!")
    end

.mongo_find_one_and_update [Asynchronous]

The mongo_find_one_and_update method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.
filter Table A Lua object of filter values to pass to the Mongo DB operation.
options Table A Lua object of option values to pass to the Mongo DB operation.

The mongo_find_one_and_update method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.updated Table A response object matching the provided options configuration.

Example (Mongo Find one and Update):

    local result = db_api.mongo_find_one_and_update (nil, "boats" { ['$set'] = { name = "My Updated Boat" } }, { name = "My Boat" },  { returnDocument = "after" })
    local updatedBost = result.updated

.mongo_update_one [Asynchronous]

The mongo_update_one method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.
filter Table A Lua object of filter values to pass to the Mongo DB operation.

The mongo_update_one method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.updated Table An object with the following attributes.
.matched_count Integer The number records that matched the provided filter criteria.
.modified_count Integer The number of records that were updated by the provided object definition.
.upserted_id String The identifier of the inserted document if an upsert took place. If no upsert took place, it returns nil

Example (Mongo Update One):

    local result = db_api.mongo_update_one (nil, "boats" { ['$set'] = { name = "My Updated Boat" } }, { name = "My Boat" })

    if (result.updated == nil or result.updated.modified_count == 0) then
      error ("No Rows Updated!")
    end

.mongo_update_many [Asynchronous]

The mongo_update_many method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.
filter Table A Lua object of filter values to pass to the Mongo DB operation.

The mongo_update_many method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.updated Table An object with the following attributes.
.matched_count Integer The number records that matched the provided filter criteria.
.modified_count Integer The number of records that were updated by the provided object definition.
.upserted_id String The identifier of the inserted document if an upsert took place. If no upsert took place, it returns nil

Example (Mongo Update Many):

    local result = db_api.mongo_update_many (nil, "boats" { ['$set'] = { name = "My Updated Boat" } }, { name = "My Boat" })

    if (result.updated == nil or result.updated.modified_count == 0) then
      error ("No Rows Updated!")
    end

.mongo_delete_one [Asynchronous]

The mongo_delete_one method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.

The mongo_delete_one method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
deleted Table An object with the following attributes.
deleted_count Integer A count of the amount of records deleted by the operation.

Example (Mongo Delete One):

    local result = db_api.mongo_delete_one (nil, "boats", { name = "My Boat" })

    if (result.deleted == nil or result.deleted.deleted_count == 0) then
      error ("No Rows Deleted!")
    end

.mongo_delete_many [Asynchronous]

The mongo_delete_many method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.

The mongo_delete_many method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
deleted Table An object with the following attributes.
deleted_count Integer A count of the amount of records deleted by the operation.

Example (Mongo Delete Many):

    local result = db_api.mongo_delete_many (nil, "boats", { name = "My Boat" })

    if (result.deleted == nil or result.deleted.deleted_count == 0) then
        error ("No Rows Deleted!")
    end

.mongo_count [Asynchronous]

The mongo_count method takes the following parameters.

Parameter Type Description
route String The identifier of the database to access, or nil for the default.
collection String The name spaced Mongo DB collection to perform the operation on.
object Table A Lua object of values to pass to the Mongo DB operation.

The mongo_count method returns the following result.

Parameter Type Description
result Table An object with the following attributes.
.count Integer The number of records that match the provided object definition.

Example (Mongo Count):

    local result = db_api.mongo_count (nil, "boats", { name = "My Boat" })

    if (result.count == nil or result.count == 0) then
      error ("No Records Found!")
    end