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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
--
-- 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"
-- for destination database
-- CREATE DATABASE nop CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- USE nop
-- CREATE TABLE IF NOT EXISTS master (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'));
-- ALTER TABLE master CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- CREATE TABLE IF NOT EXISTS standby (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'));
-- ALTER TABLE standby CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- CREATE TABLE IF NOT EXISTS state (timestamp BIGINT UNSIGNED PRIMARY KEY NOT NULL, state VARCHAR(32));
-- ALTER TABLE state CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- GRANT select,insert,update,delete ON nop.master TO 'nopsyncd'@'localhost' IDENTIFIED BY '<password>';
-- GRANT select,insert,update,delete ON nop.standby TO 'nopsyncd'@'localhost';
-- GRANT select,insert,update ON nop.state TO 'nopsysstated'@'localhost' IDENTIFIED BY '<password>';
-- GRANT select ON nop.* TO 'nopro'@'localhost' IDENTIFIED BY '<password>';
local db = {}
function db.init(db, user, pwd, host, port, table)
local mydb = {}
mydb.table = table
local err
mydb.env, err = luasql.mysql()
if mydb.env == nil then
return nil, err
end
mydb.con, err = mydb.env:connect(db, user, pwd, host, port)
if mydb.con == nil then
return nil, err
end
local ret, err = mydb.con:execute("SET CHARACTER SET utf8")
if ret == nil then
return nil, err
end
ret, err = mydb.con:setautocommit(true)
if ret == nil then
return nil, err
end
function mydb:getLastEntry()
local cur, err = self.con:execute("SELECT MAX(timestamp) FROM " .. self.table)
if cur == nil then
return nil, err
end
local timestamp = cur:fetch()
if timestamp == nil then timestamp = 0 end
return timestamp
end
function mydb:findMissingEntries(lasttimestamp)
local lastts = self.con:escape(lasttimestamp)
return self.con:execute("SELECT * FROM " .. self.table .. " WHERE timestamp > " .. lastts)
end
function mydb:getNextMissingEntry(cur)
local data = {}
data = cur:fetch(data, "a")
if data == nil then
return nil, "that's all folks"
end
return data
end
function mydb:getEntry(timestamp)
local ts = self.con:escape(timestamp)
local cur, err = self.con:execute("SELECT * FROM " .. self.table .. " WHERE timestamp = " .. ts)
if cur == nil then
return nil, err
end
local data = {}
data = cur:fetch(data, "a")
if data == nil then
return nil, "nothing found"
end
return data
end
function mydb:addEntry(data)
local timestamp = self.con:escape(data.timestamp)
local cart = self.con:escape(data.cart)
local len = self.con:escape(data.len)
local showtitle = self.con:escape(data.showtitle)
local title = self.con:escape(data.title)
local artist = self.con:escape(data.artist)
local album = self.con:escape(data.album)
local carttype = self.con:escape(data.carttype)
local cur, err = self.con:execute("REPLACE INTO " .. self.table .. " VALUES('" .. timestamp .. "', '" .. cart .. "', '" .. len .. "', '" .. showtitle .. "', '" .. title .. "', '" .. artist .."', '" .. album .. "', '" .. carttype .. "')")
if cur == nil then
return nil, err
end
return true
end
function mydb:getLastSysState()
local cur, err = self.con:execute("SELECT state FROM " .. self.table .. " WHERE timestamp = (SELECT MAX(timestamp) FROM " .. self.table .. ")")
if cur == nil then
return nil, err
end
local state = cur:fetch()
if state == nil then state = "unknown" end
return state
end
function mydb:updateSysState(timestamp, state)
local t = self.con:escape(timestamp)
local s = self.con:escape(state)
local cur, err = self.con:execute("INSERT INTO " .. self.table .. " VALUES('" .. t .. "', '" .. s .. "')")
if cur == nil then
return nil, err
end
return true
end
function mydb:close()
if self.con then
self.con:close()
end
if self.env then
self.env:close()
end
end
return mydb
end
return db
|
