Read/write mysql database

[admin]

There's no direct way of doing this. You can create a scheduled script that will push values to SQL database every minute or so. A better approach is to use a database that is meant for time-series data like InfluxDB.

[pentadom]

You can create a scheduled script that will push values to SQL database every minute or so.

I need to push values every 15 minutes to the database of almost 50 objects, but I'm not a programmer. If someone could help me would be great

[admin]

Can you provide table structure and what data do you want to add there apart from object values (group address, name, date/time etc)?

[pentadom]

Can you provide table structure and what data do you want to add there apart from object values  (group address, name, date/time etc)?

The structures could be like this:

- Table 1 "trending_definition" including [trending id, group address, object name, data type, date/time last update]
- Table 2 "trenging_values" including [value id, trending id, value, date/time]

I accept any advice or suggestion about the structure.

[admin]

Example script. Map table keys are trend ID from external database, values are group addresses to get values from. Don't forget to change access details in connect function.

Code:

require('luasql.mysql')

env = luasql.mysql()
dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)

if dbcon then
  map = {
    [1] = '1/1/1',
    [2] = '1/1/2',
  }

  for id, addr in pairs(map) do
    value = grp.getvalue(addr)
    dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())')  
    dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id)  
  end

  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end

env:close()

[pentadom]

Example script. Map table keys are trend ID from external database, values are group addresses to get values from. Don't forget to change access details in connect function.

Code:

require('luasql.mysql')

env = luasql.mysql()
dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)

if dbcon then
  map = {
    [1] = '1/1/1',
    [2] = '1/1/2',
  }

  for id, addr in pairs(map) do
    value = grp.getvalue(addr)
    dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())') 
    dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id) 
  end

  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end

env:close()

This code creates the tables (trends_values, trends_definition) automatically or I must create it manually in the database?

[admin]

You have to create tables manually and fill trends_definition table with data.

[Erwin van der Zwart]

Hi,

Here is a script i created a few years ago that checks for tables and if not exist it creates them, maybe this can be helpful for you (:

Code:

-- Check if MySQL connection is loaded and available otherwise perform DB connection loading
if not env or not dbcon then
 
  -- Setup MySQL connection
  require('luasql.mysql')
  env = luasql.mysql()
 
  -- Set database information and credentials, make sure remote access to MySQL server is enabled (see notes on bottom of this script)
  DB_NAME = 'WiserforKNX'
  USERNAME = 'root'
  PASSWORD = 'MyPassword'
  MYSQL_SERVER_IP = 'www.myserveraddress.com'  -- remote access host
  MYSQL_SERVER_PORT = 6033 -- remote access port (asymetric forwarding 6603 ext to 3306 internal to avoid internet MySQL known port scanning)
  TIMEOUT = 3 -- timeout to avoid blocking when MySQL server could not be reached (set timeout in seconds)
 
  -- Connect to MySQL database
  dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT, TIMEOUT)

  -- Error handler
  if err then
    log(err)
    env:close()
    env = nil
    alert('Connection to MySQL could not be established, system will try to restart connection')
    return
  end
 
  if dbcon then
    -- Set connected variable to true
    connected = true
    -- Set DB connection autocommit to true
    dbcon:setautocommit(true)
    -- Check if table for recording excist inside the database and holds data
    cursor = dbcon:execute('SHOW TABLES LIKE "objectdata"')
    -- Loop trough fetched data to see if rows are available
    while true do
      row = cursor:fetch({}, 'a')
      if row then
        -- do nothing
        log("Table already exist, resuming script")
        break
      else
        -- Drop DB table first before create new one
        dbcon:execute('DROP TABLE objectdata')
        -- Create DB table
        cursor = dbcon:execute('CREATE TABLE objectdata (ID int AUTO_INCREMENT, address varchar(255) NOT NULL, name varchar(255) NOT NULL, value varchar(255) NOT NULL, updatetime int NOT NULL, timestamp varchar(255) NOT NULL, PRIMARY KEY (ID))')
        log("Table for objectdata is created, resuming script")
        break
      end
    end
  else
    -- Exit script for new try
    return
  end
end

-- Init client for listening to all KNX events
if not client then
  require('genohm-scada.eibdgm')
 
  --handler for all event types
  eventhandler = function(event)
    objectinfo = grp.find(event.dst)

    -- fix for auto discovered objects without a name
    if objectinfo.name == nil then
      objectinfo.name = "unknown"
    end

    -- fix for new created objects without a having set value once
    if objectinfo.value == nil then
      objectinfo.value = "no value"
    else
        objectinfo.value = tostring(objectinfo.value)   
    end

    -- fix for time object
    if type(objectinfo.value) == "table" then
      objectinfo.value = string.format("%02d", objectinfo.value.hour) .. ":" .. string.format("%02d", objectinfo.value.minute) .. ":" ..  string.format("%02d", objectinfo.value.second) 
    end   

    -- fix for boolean object
    if type(objectinfo.value) == "boolean" then
        objectinfo.value = tostring(objectinfo.value)
    end
   
    -- calculate human readable timestamp
    timestamp = os.date("%Y-%m-%d %H:%M:%S", objectinfo.updatetime)
   
    -- write data to MySQL database
    cursor, err = dbcon:execute('INSERT INTO objectdata (address, name, value , updatetime, timestamp) VALUES ("' .. objectinfo.address .. '", "' .. objectinfo.name .. '", "' .. objectinfo.value .. '", ' .. objectinfo.updatetime .. ', "' .. timestamp .. '")')
   
    -- Get result of transaction
    result = dbcon:commit()
   
    -- Error handler
    if not result or err then
      if connected == true then
          alert('Connection to MySQL is broken or data is not processed correct, system will buffer data until connection is restored')
      end
      connected = false
    else
      if not connected then
          connected = true
      end
    end
  end
 
  -- Create client
  client = eibdgm:new()

  -- Set group communication handlers
  client:sethandler('groupread', eventhandler)
  client:sethandler('groupwrite', eventhandler)
  client:sethandler('groupresponse', eventhandler)
end

-- Next cycle
client:step()

BR,

Erwin

[pentadom]

Hi,

Here is a script i created a few years ago that checks for tables and if not exist it creates them, maybe this can be helpful for you (:

Code:

-- Check if MySQL connection is loaded and available otherwise perform DB connection loading
if not env or not dbcon then
 
  -- Setup MySQL connection
  require('luasql.mysql')
  env = luasql.mysql()
 
  -- Set database information and credentials, make sure remote access to MySQL server is enabled (see notes on bottom of this script)
  DB_NAME = 'WiserforKNX'
  USERNAME = 'root'
  PASSWORD = 'MyPassword'
  MYSQL_SERVER_IP = 'www.myserveraddress.com'  -- remote access host
  MYSQL_SERVER_PORT = 6033 -- remote access port (asymetric forwarding 6603 ext to 3306 internal to avoid internet MySQL known port scanning)
  TIMEOUT = 3 -- timeout to avoid blocking when MySQL server could not be reached (set timeout in seconds)
 
  -- Connect to MySQL database
  dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT, TIMEOUT)

  -- Error handler
  if err then
    log(err)
    env:close()
    env = nil
    alert('Connection to MySQL could not be established, system will try to restart connection')
    return
  end
 
  if dbcon then
    -- Set connected variable to true
    connected = true
    -- Set DB connection autocommit to true
    dbcon:setautocommit(true)
    -- Check if table for recording excist inside the database and holds data
    cursor = dbcon:execute('SHOW TABLES LIKE "objectdata"')
    -- Loop trough fetched data to see if rows are available
    while true do
      row = cursor:fetch({}, 'a')
      if row then
        -- do nothing
        log("Table already exist, resuming script")
        break
      else
        -- Drop DB table first before create new one
        dbcon:execute('DROP TABLE objectdata')
        -- Create DB table
        cursor = dbcon:execute('CREATE TABLE objectdata (ID int AUTO_INCREMENT, address varchar(255) NOT NULL, name varchar(255) NOT NULL, value varchar(255) NOT NULL, updatetime int NOT NULL, timestamp varchar(255) NOT NULL, PRIMARY KEY (ID))')
        log("Table for objectdata is created, resuming script")
        break
      end
    end
  else
    -- Exit script for new try
    return
  end
end

-- Init client for listening to all KNX events
if not client then
  require('genohm-scada.eibdgm')
 
  --handler for all event types
  eventhandler = function(event)
    objectinfo = grp.find(event.dst)

    -- fix for auto discovered objects without a name
    if objectinfo.name == nil then
      objectinfo.name = "unknown"
    end

    -- fix for new created objects without a having set value once
    if objectinfo.value == nil then
      objectinfo.value = "no value"
    else
        objectinfo.value = tostring(objectinfo.value)   
    end

    -- fix for time object
    if type(objectinfo.value) == "table" then
      objectinfo.value = string.format("%02d", objectinfo.value.hour) .. ":" .. string.format("%02d", objectinfo.value.minute) .. ":" ..  string.format("%02d", objectinfo.value.second) 
    end  

    -- fix for boolean object
    if type(objectinfo.value) == "boolean" then
        objectinfo.value = tostring(objectinfo.value)
    end
   
    -- calculate human readable timestamp
    timestamp = os.date("%Y-%m-%d %H:%M:%S", objectinfo.updatetime)
   
    -- write data to MySQL database
    cursor, err = dbcon:execute('INSERT INTO objectdata (address, name, value , updatetime, timestamp) VALUES ("' .. objectinfo.address .. '", "' .. objectinfo.name .. '", "' .. objectinfo.value .. '", ' .. objectinfo.updatetime .. ', "' .. timestamp .. '")')
   
    -- Get result of transaction
    result = dbcon:commit()
   
    -- Error handler
    if not result or err then
      if connected == true then
          alert('Connection to MySQL is broken or data is not processed correct, system will buffer data until connection is restored')
      end
      connected = false
    else
      if not connected then
          connected = true
      end
    end
  end
 
  -- Create client
  client = eibdgm:new()

  -- Set group communication handlers
  client:sethandler('groupread', eventhandler)
  client:sethandler('groupwrite', eventhandler)
  client:sethandler('groupresponse', eventhandler)
end

-- Next cycle
client:step()

BR,

Erwin

Dear Erwin,

How would be the code to create automatically the tables I need.

This is my code now:

Code:

require('luasql.mysql')

env = luasql.mysql()
dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)

if dbcon then
  map = {
    [1] = '1/1/1',
    [2] = '1/1/2',
  }

  for id, addr in pairs(map) do
    value = grp.getvalue(addr)
    dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())')  
    dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id)  
  end

  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end

env:close()

---

Example script. Map table keys are trend ID from external database, values are group addresses to get values from. Don't forget to change access details in connect function.

Code:

require('luasql.mysql')

env = luasql.mysql()
dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)

if dbcon then
  map = {
    [1] = '1/1/1',
    [2] = '1/1/2',
  }

  for id, addr in pairs(map) do
    value = grp.getvalue(addr)
    dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())') 
    dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id) 
  end

  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end

env:close()

I dont't want to modify the code every time I create a new object.

Is there any way to fill the map automatically when I create new objects in LM?

---

There's no direct way of doing this. You can create a scheduled script that will push values to SQL database every minute or so. A better approach is to use a database that is meant for time-series data like InfluxDB.

But if I push values directly from the objects and the connection with the database fails, this values will be lost. It's important to know that my database is in the cloud.

Can I push values from the trends. In this case I always have the values recorded locally in the LM and I do a backup once a day for example. 

[Erwin van der Zwart]

Hi,

Yes you can, you have seen the script for fetching the values from a trend, combine it with this script and modify the sql queries and you have what you need, it’s just a matter of creating a script with the samples you have..

BR,

Erwin

[pentadom]

Hi,

Yes you can, you have seen the script for fetching the values from a trend, combine it with this script and modify the sql queries and you have what you need, it’s just a matter of creating a script with the samples you have..

BR,

Erwin

Thanks Erwin, it's difficult for me because I'm not a programmer but I'll try 

I did a test fetching values from a trend with the next code:

Code:

require('trends')

name = 'testswitch'
dates = {}
dates['start'] = { year = 2020, month = 5, day = 17 }
dates['end'] = { year = 2020, month = 5, day = 18 }
-- resolution (in seconds) is optional
-- default trend resolution is used when not set
resolution = nil
res = trends.fetch(name, dates, resolution)

log(res)

but I have an error when I run script:

[Daniel]

Does this trend testswitch exist?

[admin]

Have you tried clicking Save before Run script?

[pentadom]

Does this trend testswitch exist?

Yes, here it is:

---

Have you tried clicking Save before Run script?

Yes. I tried "save" and "save and close"

I'm testing on the demo LM from openrb web. You can see all the configuration there if you want. The script called "SQL" is in the scheduled scripts.

[admin]

Some features are disabled on demo LM so this might not work. Do you have another LM to test this?

[pentadom]

Some features are disabled on demo LM so this might not work. Do you have another LM to test this?

No at the moment, I have it in my workplace. I'm confined because of the COVID-19 

I supposed then it's because of a disabled feature really?

[Daniel]

On my LM this script works fine.

[pentadom]

On my LM this script works fine.

Thank you so much for the information Daniel. I must test it on another LM.

[pentadom]

Hello,

I did some tests with the next code. I want to insert all the trending values in my SQL database. When I run the script all the code executes correctly without errors in the logs but in the database I don't see the new values.. 

Code:

require('trends')
require("luasql.mysql");

--Get a list of all trendings
names = db:getlist('SELECT name FROM trends')

log (names)

--Defines a period of the last day
dates = {}
dates['start'] = os.date('*t')
dates['start'].day = dates['start'].day - 1
dates['end'] = os.date('*t')


env = luasql.mysql();
--DATABASE CONNECTION -> (DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)
dbcon, err = env:connect('CONFIDENTIAL', 'admin','CONFIDENTIAL', 'lm-database.CONFIDENTIAL.us-east-1.rds.amazonaws.com', '3306');
log(dbcon, err)

if dbcon then

  -- Iterates all the trendings
  for _, name in ipairs(names) do
   
    -- Get all the trending values
    data = trends.fetch(name, dates)
   
    -- Iterates all the trending values
    for id, val in pairs(data) do

      -- Insert the trending values in the database.
      dbcon:execute('INSERT INTO trending-definition (id, value) VALUES (id,val)') 

    end

  end

else
  log('mysql connection failed ' .. tostring(err))
end
 
dbcon:close()
env:close()

I'm not a programmer and It's my first time doing a script with LUA so I apologize in advance for the posible errors.

[admin]

Use this table structure for data. trend_id will be the same id as in LM.

Code:

CREATE TABLE `trends_data` (
  `trend_id` int(11) NOT NULL,
  `dt` datetime NOT NULL,
  `value` double DEFAULT NULL,
  PRIMARY KEY (`trend_id`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Updated script:

Code:

require('trends')
require('luasql.mysql')

env = luasql.mysql()
--DATABASE CONNECTION -> (DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)
dbcon, err = env:connect('CONFIDENTIAL', 'admin', 'CONFIDENTIAL', 'lm-database.CONFIDENTIAL.us-east-1.rds.amazonaws.com', 3306)

if dbcon then
  items = db:getall('SELECT id, name FROM trends')
  trends.NaN = 'NULL'

  dates = {
    ['start'] = os.date('*t'),
    ['end'] = os.date('*t'),
  }

  dates.start.day = dates.start.day - 1
  date = string.format('%04d-%02d-%02d',
    dates.start.year, dates.start.month, dates.start.day)

  for _, item in ipairs(items) do
    id = item.id
    data = trends.fetch(item.name, dates)

    values = {}

    if data and #data > 0 then
      step = 1440 / #data -- in minutes

      for i, val in ipairs(data) do
        mins = (i - 1) * step
        datetime = string.format('%s %02d:%02d:00',
          date, math.floor(mins / 60), mins % 60)

        values[ #values + 1 ] = string.format('(%d,"%s",%s)',
          id, datetime, tostring(val))
      end
    end

    if #values > 0 then
      query = 'INSERT INTO trends_data (trend_id, dt, value) VALUES ' .. table.concat(values, ',')
      dbcon:execute(query)
    end
  end

  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end
  
env:close()