Read/write mysql database

[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()