Read/write mysql database

[pentadom]

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