Read/write mysql database

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