(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
