Accessing the System Database with Lua Script

For the Internal System Database inmation uses the SQLite database engine. The functionality to access this database in Lua scripts is provided by the system’s adapted version of the LuaSQL library. For the documentation of the inmation specific LuaSQL library, see Using SQL in Lua Scripts.

The specific database handle for the System Database is provided by the syslib.getsystemdb() function. This handle only provides read-access. It’s not possible to manipulate the System Database directly.

Retrieving Property Information from the System Database

The following Lua script illustrates how static properties can be retrieved from the system database. In this simple example, the next ancestor Core is the only object for which the database is queried. For more complex use-cases, adjust the SQL query in the line marked with .

  1. Create a new Action Item object and give it a suitable name.

  2. Open the Script Editor for the Lua Script Body property

  3. Copy the following script into the Script Editor. See the comments in the code for an explanation of the individual steps of the script.

    --include json library for the conversion of the results
    local json = require("dkjson")
    
    --identify the current object
    local thisObj = syslib.getself()
    --identify the next ancestor Core;
    --this will be used as the target object for this example, for which the static properties will be retrieved
    local targetPath = syslib.getcorepath(thisObj)
    --get the object for the Core from the previous step
    local targetObj = syslib.getobject(targetPath)
    --get the numerical ID for this core; for the WHERE clause of the query
    local targetID = targetObj:numid()
    
    --get the read-only handle for the system database
    local sysDB = syslib.getsystemdb()
    
    --execute the query
    local cur,errMsg = sysDB:query("SELECT * FROM properties where objid = " .. targetID) (1)
    --in case of a query error, stop the script showing the error message in the Faceplate of the executing object
    if errMsg ~= nil then
        return errMsg
    end
    
    --create a new Lua table to store all the rows fetched by the query
    local properties = {}
    
    --fetch the initial row; rows will be indexed by field name
    local row = cur:fetch ({}, "a")
    while row do  -- loop over all fetched rows
      -- add current row to the 'properties' Lua table
    table.insert(properties, row)
      -- get the next row
      row = cur:fetch({}, "a")
    end
    
    --convert the'properties' table into JSON format and set as the Faceplate value of the executing object
    return json.encode(properties)
  4. Click OK to close the Script Editor …​

  5. …​ and click Create to create the new object and close the Create Action Item wizard.

  6. Select the newly created Action Item. The Faceplate in the Properties Panel will show the resulting table. As the resulting table has been converted into the JSON format, the JSON Viewer in the context menu can be used for a closer look at the retrieved property information.

For more information on the columns of the returned table, see the schema of the properties table.

Data Retrieval in JSON Format

In the previous example, the data was retrieved row by row, where each row was added to the Lua table called 'properties'. Before the final table was returned, it was converted into a JSON string.

Alternatively the aggregation of the rows as well as the conversion into the JSON format can be done in the database engine. For this, SQLite provides a number of JSON Functions and Operators. For data retrieval from the from the system database only the following two functions are relevant: json_object( 'label', value [,'label', value] …​ ), which converts the comma-separated list of labels and values into a JSON object, and json_group_array( value [, value] ), which aggregates a comma-separated list of values in a JSON array.

BLOB values can not be retrieved with JSON features of SQLite but cause errors.

In the following example, …​

  • The database engine converts all rows, which are to be returned for the query, into individual JSON objects .

  • All these objects are then aggregated into a JSON array by the database engine , …​

  • …​ before this array is fetched into the script’s 'properties' table .

  • Data of type BLOB is excluded from the query because the Core class has an internal property of this type which is not supported by the JSON features of SQLite .

--include json library for the conversion of the results
local json = require("dkjson")

--identify the current object
local thisObj = syslib.getself()
--identify the next ancestor Core;
--this will be used as the target object for this example, for which the static properties will be retrieved
local targetPath = syslib.getcorepath(thisObj)
--get the object for the Core from the previous step
local targetObj = syslib.getobject(targetPath)
--get the numerical ID for this core; for the WHERE clause of the query
local targetID = targetObj:numid()

--get the read-only handle for the system database
local sysDB = syslib.getsystemdb()

local query = [[
  SELECT
    json_group_array(  -- one array for the whole query      (2)
      json_object(     -- an individual object for each row  (1)
       'objid', objid,
       'path', path,
       'code', code,
       'value', value,
       'position', position,
       'modelcode', modelcode
      )
    )
  FROM
    properties
  WHERE
    objid = ]] .. targetID .. [[
    AND typeof(value) != "blob" --exclude BLOBs from fetched data (4)
  ;]]

-- execute the query
local cur,errMsg = sysDB:query(query)

--in case of a query error, stop the script showing the  error message in the Faceplate of the executing object
if errMsg ~= nil then
    return errMsg
end

-- create a new Lua table to store all the rows fetched by the query
local properties = cur:fetch() (3)

-- set as the Faceplate value of the executing object
return properties