Navigation :

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:

Invalid input parameters supplied by Lua script.
Unexpected results structure returned from DBApp.
Processing error occurred at DBApp.
Timeout occurred at DBApp.

.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