1 files changed, 160 insertions, 0 deletions
diff --git a/src/db.lua b/src/db.lua
new file mode 100644
index 0000000..d606593
--- /dev/null
+++ b/src/db.lua
@@ -0,0 +1,160 @@
+--
+--  rhnop
+--
+--  Copyright (C) 2011-2015 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;
+-- GRANT select,insert,update,delete ON nop.master TO 'nopcollectd'@'localhost' IDENTIFIED BY '<password>';
+-- GRANT select,insert,update,delete ON nop.standby TO 'nopcollectd'@'localhost';
+-- GRANT select,insert,update ON nop.state TO 'nopsysstated'@'localhost' IDENTIFIED BY '<password>';
+-- GRANT select ON nop.* TO 'nop'@'localhost' IDENTIFIED BY '<password>';
+-- 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), ismusic BOOLEAN);
+-- 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), ismusic BOOLEAN);
+-- 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;
+
+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 ismusic = self.con:escape(data.ismusic)
+      
+      local cur, err = self.con:execute("REPLACE INTO " .. self.table .. " VALUES('" .. timestamp .. "', '" .. cart .. "', '" .. len .. "', '" .. showtitle .. "', '" .. title .. "', '" .. artist .."', '" .. album .. "', '" .. ismusic .. "')")
+      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