This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm whether you accept or reject these cookies being set.

Read/write mysql database
#61
(20.05.2020, 07:38)admin Wrote: 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()

Thank you! works perfect! 

If I want to have a second table called "trends_definition" with columns (trend_id, name) ? It was interesting that if I modify or create new trends the table updates the information.

I created a new table with this structure:


Code:
CREATE TABLE `trends_definition` (`trend_id` int(11) NOT NULL,`name` varchar(50) NOT NULL,PRIMARY KEY (`trend_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I modified the your code to insert trend definitions in trends_definition table:


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

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

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)
  log (date)
 
  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
      log (values)
      query = 'INSERT INTO trends_data (trend_id, dt, value) VALUES ' .. table.concat(values, ',')
      dbcon:execute(query)
    end
  end

  -----------trends_definition--------------
 
  for _, item in ipairs(items) do
    id = item.id
    name = item.name
    query = 'INSERT INTO trends_definition (trend_id,name) VALUES (id,name)' 
    dbcon:execute(query)
  end 
 
  -------------------------------------------
 
  dbcon:close()
else
  log('mysql connection failed ' .. tostring(err))
end
  
env:close()


But the query doesn't works Huh
Reply
#62
Where can I get the necessary files for it to work in a Schneider electric spacelynk?
Reply


Forum Jump: