1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
--
-- rhnop
--
-- Copyright (C) 2011-2016 Christian Pointner <equinox@helsinki.at>
--
-- This file is part of rhnop.
--
-- rhnop is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- any later version.
--
-- rhnop is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with rhnop. If not, see <http://www.gnu.org/licenses/>.
--
luasql = require "luasql.mysql"
-- CREATE DATABASE rhnop CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- GRANT select,insert,update ON rhnop.* TO 'nopfetchd' IDENTIFIED BY '<password>';
-- GRANT select ON rhnop.* TO 'nopsyncd' IDENTIFIED BY '<password>';
-- USE rhnop
-- CREATE TABLE IF NOT EXISTS now (timestamp BIGINT UNSIGNED PRIMARY KEY NOT NULL, cart INT NOT NULL, len INT, showtitle VARCHAR(255), title VARCHAR(255), artist VARCHAR(255), album VARCHAR(255), carttype ENUM('show','pool','jingle'), output INT NOT NULL);
-- ALTER TABLE now CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
local playlog = {}
function playlog:init(cnf)
local err
self.env, err = luasql.mysql()
if self.env == nil then
return nil, err
end
self.con, err = self.env:connect(cnf.playlog_db, cnf.playlog_user, cnf.playlog_pwd, cnf.playlog_host, cnf.playlog_port)
if self.con == nil then
return nil, err
end
local ret, err = self.con:execute("SET CHARACTER SET utf8")
if ret == nil then
return nil, err
end
ret, err = self.con:setautocommit(true)
if ret == nil then
return nil, err
end
return true
end
function playlog:getLastCart(output, timestamp)
local cur, err = self.con:execute("SELECT cart FROM now WHERE timestamp = " .. timestamp .. " and output = " .. output)
if cur == nil then
return nil, err
end
local cart = cur:fetch()
if cart == nil then cart = 0 end
return tonumber(cart)
end
function playlog:getLastCarts()
local cur, err = self.con:execute("select output,max(timestamp) as timestamp from now GROUP BY output")
if cur == nil then
return nil, err
end
local carts = {}
while true do
local data = {}
data = cur:fetch(data, "a")
if data == nil then break end
carts[data.output] = self:getLastCart(data.output, data.timestamp)
end
cur:close()
return carts
end
function playlog:insertNow(timestamp, cart, len, showtitle, title, artist, album, carttype, output)
cart = tonumber(cart)
len = tonumber(len)
output = tonumber(output)
local cur, err = self.con:execute("INSERT into now VALUES(" .. self.con:escape(timestamp) .. ", " .. cart .. ", " .. len .. ", '" .. self.con:escape(showtitle) .. "', '" .. self.con:escape(title) .. "', '" .. self.con:escape(artist) .."', '" .. self.con:escape(album) .. "', '" .. self.con:escape(carttype) .. "', " .. output .. ")")
if cur == nil then
return nil, err
end
return true
end
function playlog:close()
if self.con then
self.con:close()
end
if self.env then
self.env:close()
end
end
return playlog
|
