added initial db.lua

1 files changed, 111 insertions, 0 deletions

diff --git a/rhnop-client/db.lua b/rhnop-client/db.lua
new file mode 100644
index 0000000..0d6f591
--- /dev/null
+++ b/rhnop-client/db.lua
@@ -0,0 +1,111 @@
+--
+--  rhnop
+--
+--  Copyright (C) 2011 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/>.
+--
+
+require "luasql.mysql"
+
+-- CREATE DATABASE rhnop
+-- GRANT select,insert,update ON rhnop.* TO 'nopsyncd' IDENTIFIED BY '<password>';
+-- GRANT select ON rhnop.* TO 'nopcollectd' 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), ismusic BOOLEAN);
+
+conf = require "conf"
+
+local db = {}
+
+function db:init(db, user, pwd, host, port, table)
+   self.table = table
+
+   local err
+
+   self.env, err = luasql.mysql()
+   if self.env == nil then
+      return nil, err
+   end
+
+   self.con, err = self.env:connect(db, user, pwd, host, port)
+   if self.con == nil then
+      return nil, err
+   end
+
+   local ret, err = self.con:setautocommit(true)
+   if ret == nil then
+      return nil, err
+   end
+
+   return true
+end
+
+function db:getLastCart()
+   local cur, err = self.con:execute("SELECT cart FROM now WHERE timestamp = (SELECT MAX(timestamp) FROM now)")
+   if cur == nil then
+      return nil, err
+   end
+
+   local cart = cur:fetch()
+   if cart == nil then cart = 0 end
+   return cart
+end
+
+function db: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 db:insertEntry(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("INSERT into " .. self.table .. " VALUES('" .. timestamp .. "', '" .. cart .. "', '" .. len .. "', '" .. showtitle .. "', '" .. title .. "', '" .. artist .."', '" .. album .. "', '" .. ismusic "')")
+   if cur == nil then
+      return nil, err
+   end
+
+   return true
+end
+
+function db:close()
+   if self.con then
+      self.con:close()
+   end
+
+   if self.env then
+      self.env:close()
+   end
+end
+
+return db