DB Lua Agent

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.

The DBLuaAgent is tied to the db action key.

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"/>
        </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.

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)

Invoking DBLuaAgent

A Lua Script can access the DBLuaAgent db action with code such as the following:

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

local handler = function (rest_args)
    local rows = db_api.dbi_select (nil, "SELECT name FROM table1 WHERE code = 1", nil)

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

return n2svcd.handler (handler)

This is standard Lua-style library usage. The n2/n2svcd/db.lua library is loaded with require "n2.n2svcd.db". Then methods are invoked on the returned library object.

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, then the first parameter is the “route”, or database key.

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

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 rows = db_api.dbi_select (nil, "SELECT name FROM table1 WHERE code = 1", nil)

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

.dbi_do

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

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 handler.

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

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 handler.

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

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 handler.
options Table A Lua object of options to pass to the Mongo DB operation handler.
projection Table A Lua object of projection values to pass to the Mongo DB operation handler.

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

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 handler.
options Table A Lua object of options to pass to the Mongo DB operation handler.
projection Table A Lua object of projection values to pass to the Mongo DB operation handler.

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

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 handler.

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

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 handler.
filter Table A Lua object of filter values to pass to the Mongo DB operation handler.
options Table A Lua object of option values to pass to the Mongo DB operation handler.

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

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 handler.
filter Table A Lua object of filter values to pass to the Mongo DB operation handler.

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

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 handler.
filter Table A Lua object of filter values to pass to the Mongo DB operation handler.

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

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 handler.

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

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 handler.

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

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 handler.

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