(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
