python-dhm 0.6-3build1 / usr / share / pyshared / dhm / sql / dict.py

# sqldict.py
#
# Copyright 2003 Wichert Akkerman <wichert@deephackmode.org>
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of version 2 of the GNU General Public License as
# published by the Free Software Foundation.
#
# 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., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
# Calculate shared library dependencies

"""Basic SQL dictionary interface

Often you want to treat a SQL table as a dictionary. This module implements
the SQLTable class which makes it easy to do that. It expects to deal with
tables with unqiue keys (combined keys are supported) which is used to index
the dictionary.
"""

__docformat__	= "epytext en"

import copy, UserDict


class SQLLookup(UserDict.UserDict):
	"""SQL lookup table.

	This class implements a trivial lookup table that uses a SQL table
	as data source. It loads the content of the table during construction,
	so only use this for situations where you do frequent lookups or the
	table size is small.

	It makes a few assumptions on the SQL table:

	 - it contains only two columns: a key and a value column
	 - duplicate key entries are not detected. It is highly
	 recommended to use a unique constraint on the key column.

	@cvar   table: SQL table which contains our data
	@type   table: string
	"""

	def __init__(self, dbc, table, **kwargs):
		"""Constructor.

		Initializing of SQLLookup is done using a Connection
		class and specifying the table containing the data.

		@param   dbc: database connection
		@type    dbc: sqlwrap.Connection class
		@param table: SQL table with data
		@type  table: string
		"""
		UserDict.UserDict.__init__(self)

		self.table=table
		self.dbc=dbc

		self._Read()
	

	def _Read(self):
		res=self.dbc.query("SELECT * FROM %s;" % self.table);
		for data in res:
			(key,value)=data[0:2]
			self.data[key]=value
	

class DictIterator:
	def __init__(self, db):
		(cmd,values)=db._genwhere()
		self.cursor=db.dbc.execute("SELECT DISTINCT %s FROM %s %s;" %
				(",".join(db.keycols), db.table, cmd),
				values, "format")


	def __del__(self):
		self.cursor.close()


	def __iter__(self):
		return self


	def next(self):
		res=self.cursor.fetchone()
		if res==None:
			raise StopIteration

		if len(res)==1:
			return res[0]
		else:
			return tuple(res)


class SQLDict:
	"""SQL dictionary

	This class provides a dictionary interface to a SQL table. This is an
	abstract class; to use it derive your own class and make a three simple
	changes:
	
	 1. set the 'table' class variable to the name of the SQL table
	 containing the data
	 2. set the 'keycols' class variable to the list of the key columns
	 3. implement the __getitem__ method

	@cvar   table: SQL table which contains our data
	@type   table: string
	@cvar keycols: Columns which make up the unique key
	@type keycols: string
	"""
	table		= None
	keycols		= []

	def __init__(self, dbc, table=None):
		"""Constructor.

		@param   dbc: database connection
		@type    dbc: sqlwrap.Connection class
		@param table: SQL table with data
		@type  table: string
		"""
		if table:
			self.table=table

		self.dbc=dbc
	

	def _GetKeys(self, args, kwargs={}):
		"""Build a key dictionary

		Build a dictionary containing the keys based on both
		normal and keyword arguments.
		"""
		if isinstance(args[0], tuple):
			args=args[0]
		assert(len(args)<=len(self.keycols))

		found={}
		for i in range(len(args)):
			found[self.keycols[i]]=args[i]
		for (key,value) in kwargs.items():
			if not key in self.keycols:
				raise KeyError, "Illegal key"
			found[key]=value

		if len(found)!=len(self.keycols):
			raise KeyError, "Not all keys supplied"

		return found


	def _genwhere(self, keys=None):
		"""Generate data for a WHERE clause to filter for a keyset.

		This function generates data which can be used to generate
		a WHERE clause to uniquely identify this object in a table. It
		returns a tuple containing a string with the SQL command and a 
		tuple with the data values. This can be fed to the execute
		method for a database connection using the format paramstyle.

		@return: (command,values) tuple
		"""

		if not keys:
			return ("", (()))

		data=keys.items()
		keys=map(lambda x: x[0], data)
		values=tuple(map(lambda x: x[1], data))
		return ("WHERE " + (" AND ".join(map(lambda x: x+"=%s", keys))),
				values)


	def __getitem__(self, key):
		raise NotImplementedError


	def __setitem__(self, key, item):
		raise NotImplementedError


	def __len__(self):
		(cmd,values)=self._genwhere()
		return int(self.dbc.query(
			"SELECT COUNT(*) FROM %s %s;" % (self.table, cmd),
			values, "format")[0][0])


	def __iter__(self):
		return DictIterator(self)


	def __contains__(self, key):
		return self.has_key(key)


	def has_key(self, *arg, **kwargs):
		(cmd,values)=self._genwhere(self._GetKeys(arg, kwargs))
		res=self.dbc.query(
			"SELECT COUNT(*) FROM %s %s;" % 
			(self.table, cmd), values, "format")
		return res[0][0]>0
		

	def keys(self):
		(cmd,values)=self._genwhere()
		keys=self.dbc.query("SELECT DISTINCT %s FROM %s %s;" %
				(",".join(self.keycols), self.table, cmd),
				values, "format")

		if len(self.keycols)>1:
			return keys
		else:
			return map(lambda x: x[0], keys)
	

	def values(self):
		keys=self.keys()
		return map(lambda x,s=self: s[x], keys)


	def clear(self):
		(cmd,values)=self._genwhere()
		self.dbc.execute("DELETE FROM %s %s;" % (self.table, cmd),
				values, "format")
	

	def __delitem__(self, *arg, **kwargs):
		(cmd,values)=self._genwhere(self._GetKeys(arg, kwargs))
		self.dbc.execute("DELETE FROM %s %s;" %
			(self.table, cmd), values, "format")


class SQLFilterDict(SQLDict):
	"""SQL filtering dictionary

	This class works exactly like SQLDict but adds the ability to
	only work on a subset of rows in a SQL table 
	This class provides a dictionary interface to a SQL table. This is an
	abstract class; to use it derive your own class and make a three simple
	changes:
	
	 1. set the 'table' class variable to the name of the SQL table
	 containing the data
	 2. set the 'keycols' class variable to the list of the key columns
	 3. implement the __getitem__ method

	@cvar   table: SQL table which contains our data
	@type   table: string
	@cvar keycols: Columns which make up the unique key
	@type keycols: string
	"""
	def __init__(self, dbc, table=None, **kwargs):
		SQLDict.__init__(self, dbc=dbc, table=table)

		self.filter=copy.copy(kwargs)


	def _genwhere(self, keys={}):
		"""Generate data for a WHERE clause to filter for a keyset.

		This function generates data which can be used to generate
		a WHERE clause to uniquely identify this object in a table. It
		returns a tuple containing a string with the SQL command and a 
		tuple with the data values. This can be fed to the execute
		method for a database connection using the format paramstyle.

		@return: (command,values) tuple
		"""

		if not (keys or self.filter):
			return ("", (()))

		columns=copy.copy(self.filter.keys())
		values=copy.copy(self.filter.values())
		columns.extend(keys.keys())
		values.extend(keys.values())

		return (("WHERE " + " AND ".join(map(lambda x: x+"=%s", columns))),
				tuple(values))