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"/>
          </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 Element Location of the module for DBLuaAgent.
config Object Element Container for extended configuration for this Application instance.
.db_timeout_ms Positive Integer Attribute 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 rows = db_api.dbi_select (nil, "SELECT name FROM table1 WHERE code = 1", nil)

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.

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 List of Table An optional Array of Objects returned by the RETURNING clause (if any) in the SQL.

Example (DBI Update):

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

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

.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