#!/usr/bin/python2.5
"""
Copyright (C) 2008 Norman Messtorff <normes@normes.org>

This program 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 2
of the License, or (at your option) any later version.

This program 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 this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
"""

import os
from pysqlite2 import dbapi2 as sqlite
from Configuration import Configuration


class Database(Configuration):
	"""Provide all needed DB methodes"""
	connected = False
	__valuebuffer = [ ]
	
	def __init__(self):
		"""Verify if stated DB filename is already existing, otherwise create a new one"""
		Configuration.__init__(self, "server")
		if not os.path.exists(self.config["database"]["sqlite_filename"]):
			self.__create()
		self.debug(4, "Class Database initialized, DB: " + self.config["database"]["sqlite_filename"])
	
	
	def __del__(self):
		self.close()
	
	
	def __create(self):
		"""Create tables"""
		self.debug(3, "Database not existing, creating...")
		self.connect()
		# TODO: UNIQUE? new pkgstatus table?
		
		""" General Node informations """
		self.__exec('CREATE TABLE `nodes` (`nodeid` INTEGER PRIMARY KEY, `hostname` TEXT, `password` TEXT, `active` BOOL, `last_upd` DATE, `last_ip` TEXT, `archid` INT, `distid` INT, `dist_ver` TEXT);')
		""" Sources defined on this node """
		self.__exec('CREATE TABLE `node_sources` (`nodeid` INT, `sourceid` INT);')
		""" Packages known on this node """
		self.__exec('CREATE TABLE `node_packages` (`nodeid` INT, `pkgid` INT, `status` TEXT);')
		
		""" index tables """
		self.__exec('CREATE TABLE `distributions` (`distid` INTEGER PRIMARY KEY, `name` TEXT, `version`, TEXT);')
		self.__exec('CREATE TABLE `components` (`compid` INTEGER PRIMARY KEY, `name` TEXT);')
		self.__exec('CREATE TABLE `architectures` (`archid` INTEGER PRIMARY KEY, `name` TEXT);')
		
		""" Sources index """
		self.__exec('CREATE TABLE `sources` (`sourceid` INTEGER PRIMARY KEY, `distid` INT, `compid` INT, `archid` INT);')
		
		"""Packages"""
		self.__exec('CREATE TABLE `packages` (`pkgid` INTEGER PRIMARY KEY, `sourceid` INT, `pkgname` VARCHAR(65), `installsize` INT, `version` VARCHAR(50) );')
		
		""" Session management """
		self.__exec('CREATE TABLE `sessions` (`sessionid` INTEGER PRIMARY KEY, `nodeid` INT, `timeout` DATETIME);')
		
		""" optional... """
		self.__exec('CREATE INDEX `packages_id_pkgname` on `packages` (`pkgid`, `pkgname`); ')
		return self.close()
	
	
	
	"""
	Core methodes....
	==============
	"""
	def __commit(self):
		"""Force a DB commit"""
		if self.connected:
			return self.__sql_con.commit()
		else:
			self.debug(1, "No active DB Connection!")
			return False
	
	
	def __exec(self, string):
		"""Execute SQL statement"""
		if self.connected:
			return self.__sql_cur.execute(string)
		else:
			self.debug(1, "No active DB Connection!")
			return False
	
	
	def __fetchall(self):
		"""Call and return the results of fetchall()"""
		if self.connected:
			return self.__sql_cur.fetchall()
		else:
			self.debug(1, "No active DB Connection!")
			return False
	
	
	def connect(self):
		"""Connect to SQLite DB"""
		self.__sql_con=sqlite.connect(self.config["database"]["sqlite_filename"])
		self.__sql_cur = self.__sql_con.cursor()
		self.connected = True
		self.debug(4," -- connected to SQLite Database '"+ self.config["database"]["sqlite_filename"] +"'.")
		return 0
	
	
	def count(self, table, column, condition=""):
		"""Count with or without any condition things from the DB"""
		if condition != "":
			self.__exec('SELECT %s from COUNT(`%s`) where ( %s );' % (column, table, condition))
		else:
			self.__exec('SELECT %s from COUNT(`%s`);' % (column, table))
		return self.__fetchall()[0][0]
	
	
	def close(self):
		"""Close SQLite DB connection"""
		# TODO: check sessiontable...
		try:
			ret=self.__sql_con.close()
			self.__sql_con = None
			self.__sql_cur = None
			self.debug(4, " -- SQLite disconnected")
			self.connected = False
		except:
			self.debug(1,  "Unable to close DB connection")
		return ret
	
	
	def delete(self, table, condition, commit=False):
		"""Delete in given table with specified condition, optionally force a commit"""
		ret = self.__exec('DELETE from `%s` where ( %s );' % (table, condition))
		if commit:
			self.__commit()
		return ret
	
	
	def insert(self, table, column, value, commit=False):
		"""Insert into given table and column specified values, optionally force a commit"""
		c = ""
		if type(column) == type( "teststring" ):
			c = "`%s`" % column
		else:
			for item in column:
				if c != "":
					c = "%s, `%s`" % (c, item)
				else:
					c = "`%s`" % item
		
		v = ""
		if type(value) == type( "teststring" ):
			v = '"%s"' % value
		else:
			for item in value:
				if v != "":
					v = '%s, "%s"' % (v, item)
				else:
					v = '"%s"' % item
		
		ret = self.__exec('INSERT INTO `%s` ( %s ) VALUES ( %s );' % (table, c, v))
		if commit:
			self.__commit()
		return ret
	
	
	def select(self, table, column, condition=""):
		"""Select with or without any condition from the DB"""
		if condition != "":
			self.__exec('SELECT %s from `%s` where ( %s );' % (column, table, condition))
		else:
			self.__exec('SELECT %s from `%s`;' % (column, table))
		return self.__fetchall()
	
	
	def update(self, table, column, value, condition, commit=False):
		"""Update in given table and column specified values, optionally force a commit"""
		ret = self.__exec('UPDATE `%s` SET `%s` = %s WHERE ( %s );' % (table, column, value, condition))
		if commit:
			self.__commit()
		return ret
	
	
	"""
	The "usefull" methodes...
	================
	"""
	def check_sessiontable(self):
		"""Session maintenance"""
		self.delete("sessions", "timeout < DATETIME('now')", commit=True)
		return 0
	
	
	def delete_session(self, sessionid):
		"""Remove unused sessions"""
		if self.select("sessions", "sessionid", "sessionid=%s" % sessionid):
			self.delete("sessions", "sessionid=%s" % sessionid, commit=True)
			return True
		return False
	
	
	def get_all_sourceids(self, distname, distver, compname, archname):
		distid = self.get_distid(distname, distver)
		compid = self.get_compid(compname)
		archid = self.get_archid(archname)
		sourceid = self.get_sourceid(distid, compid, archid)
		return (sourceid, distid, compid, archid)
	
	
	def get_archid(self, archname):
		"""Returns archid from given archname"""
		ret = self.select("architectures", "archid", 'name="%s"' % archname)
		if ret:
			return ret[0][0]
		else:
			self.insert("architectures", "name", archname, commit=True)
			ret = self.select("architectures", "archid", 'name="%s"' % archname)
			return ret[0][0]
	
	
	def get_compid(self, compname):
		"""Returns compid from given compname"""
		ret = self.select("components", "compid", 'name="%s"' % compname)
		if ret:
			return[0][0]
		else:
			self.insert("components", "name", compname, commit=True)
			ret = self.select("components", "compid", 'name="%s"' % compname)
			return ret[0][0]
	
	
	def get_distid(self, distname, distver):
		"""Returns distid from given distname and distver"""
		ret = self.select("distributions", "distid", 'name="%s" and version="%s"' % (distname, distver))
		if ret:
			return ret[0][0]
		else:
			self.insert("distributions", ("name", "version"), (distname, distver), commit=True)
			ret = self.select("distributions", "distid", 'name="%s" and version="%s"' % (distname, distver))
			return ret[0][0]
	
	
	def get_node_pkgid_status(self, nodeid):
		ret = self.select("node_packages", "pkgid, status", 'nodeid="%s"' % nodeid)
		if ret:
			return ret[0]
		else:
			return False
	
	
	def get_node_sourceids(self, nodeid):
		ret = self.select("node_sources", "sourceid", 'nodeid="%s"' % nodeid)
		if ret:
			return ret[0]
		else:
			return False
	
	
	def get_nodeid(self, hostname):
		"""Returns nodeid from given hostname"""
		ret = self.select("nodes", "nodeid", 'hostname="%s"' % hostname )
		if ret:
			return ret[0][0]
		else:
			return False
	
	
	def get_package(self, pkgid):
		ret = self.select("packages", "sourceid, pkgname, installsize, version", 'pkgid="%s"' % pkgid)
		if ret:
			return[0]
		else:
			return False
	
	
	def get_pkgid(self, sourceid, pkgname, installsize, version):
		if installsize == "none":
			installsize ="0"
		ret = self.select("packages", "pkgid", 'sourceid="%s" AND pkgname="%s" AND installsize="%s" AND version="%s"' % (sourceid, pkgname, installsize, version))
		if ret:
			return[0][0]
		else:
			return False
	
	
	
	def get_sessionid(self, nodeid):
		ret = self.select("sessions", "sessionid", 'nodeid="%s"' % nodeid)
		if ret:
			return[0][0]
		else:
			return False
	
	
	def get_sourceid(self, distid, compid, archid):
		ret = self.select("sources", "sourceid", 'distid="%s" AND compid="%s" AND archid="%s"' % (distid, compid,  archid))
		if ret:
			return ret[0][0]
		else:
			self.insert("sources", ("distid", "compid", "archid"), (distid, compid,  archid), commit=True)
			ret = self.select("sources", "sourceid", 'distid="%s" AND compid="%s" AND archid="%s"' % (distid, compid,  archid))
			return ret[0][0]
	
	
	def insert_node(self, hostname, password, active, last_ip, arch, dist, dist_ver):
		"""Add a new node, returns the nodeid and a new password"""
		if not self.get_nodeid(hostname):
			archid = get_archid(arch)
			distid = get_distid(dist, dist_ver)
			self.insert("nodes", "hostname,password,active,last_upd,last_ip,archid,distid", '%s, %s, %s, %s, %s, %s' %(hostname, password, active, last_ip, archid, distid), commit=True)
			return self.get_nodeid(hostname)
		else:
			return False
	
	
	def insert_node_pkgs(self, nodeid, values):
		#SELECT pkgname FROM packages WHERE arch=(SELECT arch FROM architecture WHERE archid=1);
		self.__sql_cur.executemany("INSERT INTO `node_packages` (nodeid, pkgid, status) VALUES (%s, ?, ?)" % nodeid, values)
		return self.__commit()
	
	
	def insert_pkg(self, sourceid, pkgname, installsize, version):
		if installsize == "none":
			installsize = "0"
		return self.insert("packages", "sourceid, pkgname, installsize, version", "%s, %s, %s, %s" % (sourceid, pkgname, installsize, version))
	
	
	def insert_sessionid(self, nodeid):
		if self.insert("sessions", ("nodeid", "timeout"), (nodeid, "DATETIME('now', '+%s minutes')" % self.config["database"]["session_timeout"]), commit=True):
			return self.get_sessionid(nodeid)
		else:
			return False
	
	
	def insert_source_pkgs(self, sourceid):
		"""Import component packages into database"""
		#TODO: check if packages already existing
		self.__sql_cur.executemany("INSERT INTO `packages` (sourceid, pkgname, installsize, version) VALUES (%s, ?, ?, ?);" % sourceid, self.__valuebuffer)
		return self.__commit()
	
	
	def update_session(self, nodeid):
		"""Create and update sessions"""
		sessionid = self.get_sessionid(nodeid)
		if sessionid:
			self.update("sessions", "timeout", 'DATETIME("NOW")', "sessionid=%s" % sessionid)
			return sessionid
		else:
			self.insert_sessionid(nodeid)
			return self.get_sessionid(nodeid)
	
	
	def valuebuffer(self):
		return self.__valuebuffer
	
	
	def valuebuffer_append(self, item):
		return self.__valuebuffer.append( item )
	
	
	def valuebuffer_exec(self, sql):
		self.__sql_cur.executemany(sql, self.__valuebuffer)
		return self.__commit()
	
	
	def valuebuffer_init(self):
		self.__valuebuffer = [ ]
		return True
	
	
	"""
	Sandbox methodes...
	"""
	def __joined_select(self, table, column, condition):
		""" 
		sqlite> select nodes.hostname,architectures.name from nodes inner join architectures on nodes.archid = architectures.archid where architectures.name == "testarch2";
		"""
		self.select("SELECT %s from %s INNER JOIN %s;" % (column, table, condition))
		return self.__fetchall()
	
	
	def insert_pkg(self, nodeid, pkg):
		"""Insert new package informations
		nodeid, package, status, installsize, version"""
		if pkg[2] == "none":
			pkg[2] ="0"
		#if packages.pkgname == pkg[0] (.. weitere attribute...):
		
		pkgid = self.__joined_select("packages", "packages.pkgname", "nodes on nodes.archid = packages.archid WHERE architectures.archid ==%s")
		
		if self.select("packages", "package", "nodeid=%s AND package='%s'" % (nodeid, pkg[0])):
			# TODO: update_multi(), dictionary?
			return self.__exec('UPDATE `packages` SET `status` = "%s", `installsize` = "%s", `version` = "%s" WHERE ( `nodeid` = %s AND `package` = "%s" );' % (pkg[1], pkg[2], pkg[3], nodeid, pkg[0]) )
		else:
			return self.insert("packages", ("nodeid", "package", "status", "installsize", "version"), (nodeid, "%s" % pkg[0], "%s" % pkg[1], "%s" % pkg[2], "%s" % pkg[3]) )
	
	
	def delete_pkg(self, nodeid, package):
		"""Delete package informations"""
		return self.delete("packages", "nodeid=%s AND package='%s'" % (nodeid, package))