"""
	This module provides:
		A dbPool for each database defined by simple names.
		Automatically re-connects bad connections.
		Provides access to connections, cursors or recordsets.
		Automatic rollback when connections are created and re-used.
		Records that support:
			Traditional list lookup like PyDB-API: record[0].
			Case insensitive attribute lookup: record.firstName.
			Case insensitive dictionary lookup: record['firstName'].
			Automatic conversion of timestamps to mx.DateTime.DateTime objects.
			
		
	Jeff Johnson, August 2001.
	Please send bug fixes to jeff@jeffjohnson.net.
	
	I wrote this for PostgreSQL 7.1.x and pgdb.py but it should work
	with any python database module that supports python DB-API 2.

	Example:

		class SitePage(Page,DataSourcePool):
			dataSources = {
				'myDsn':    {'dbModule':pgdb, 'maxConnections':2, 'connectString':'host:database:username:password'},
				}
			
			def writeContent(self):
				recordset = self.recordset('myDsn', 'select firstName, lastName from account')
				for record in recordset:
					self.writeln('account name: %(firstName)s %(lastName)s<br>' % record

"""

import threading
from UserList import UserList
import mx.DateTime
from MiscUtils.DBPool import DBPool

dbPools = {}

_threadLock = threading.Lock()

class Database:
	"""
		I don't want to pre-allocate dbPools that may not apply to 
		the current application so I need to create them as needed.
		I wish DBPool would create connections as needed.
		Maybe in the future it'll have that option
	"""
	
	# The subclass needs to define the datasources...
	dataSources = {}
	#	'DSN':    {'dbModule':pgdb, 'maxConnections':2, 'connectString':'host:database:username:password'},
	
	def connection(self,dsn):
		global dbPools
		_threadLock.acquire()
		try:
			dbPool = dbPools.get(dsn,None)
			if dbPool == None:
				ds = self.dataSources.get(dsn,None)
				if ds == None:
					raise AttributeError, "dsn undefined: %s" % dsn
				try:
					dbPool = DBPool(ds['dbModule'], ds['maxConnections'], ds['connectString'])
					dbPools[dsn] = dbPool
				except:
					raise
		finally:
			_threadLock.release()
		return dbPool.getConnection()
	
	def cursor(self,dsn):
		""" Python DB-API 2 starts a transaction which most of the time is unwanted.
			Let's rollback here to get out of transaction mode and reset any old connections.
			Also, use the rollback to check for dead connections and try to reconnect.
		"""
		attempts = 0
		while 1:
			attempts = attempts + 1
			conn = self.connection(dsn)
			try:
				cursor = conn.cursor()
				cursor.execute("rollback")
				return cursor
			except AttributeError:
				raise
			except:
				# The connection might be dead, attempt restart once.
				if attempts > 1:
					raise
				else:
					global dbPools
					dbPools[dsn] = None
	
	def recordset(self,dsn,sql):
		cursor = self.cursor(dsn)
		cursor.execute(sql)
		return Recordset(cursor)
	
class Recordset(UserList):
	""" 
	This will create a list of Record objects
	"""

	def __init__(self,cursor):
		""" Call cursor.execute(sql) before creating the Recordset.
			See pgrecordset in PostgresMixin.
		"""
		UserList.__init__(self)
		# if this isn't a select query, fetchall fails..
		try:
			self._description = cursor.description
			for row in cursor.fetchall():
				self.data.append(self.record(row))
		except:
			self._description = None

	def description(self):
		return self._description

	def fields(self):
		fields = []
		for colDesc in self._description:
			fields.append(colDesc[0])
		return fields

	def types(self):
		types = []
		for colDesc in self._description:
			types.append(colDesc[1])
		return types

	def record(self,row):
		record = Record()
		i = 0
		for name, type_code, display_size, internal_size, precision, scale, null_ok in self._description:
			value = row[i]
			if type_code == 'timestamp' and value is not None:
				value = mx.DateTime.ISO.ParseDateTime(value)
			setattr(record, name, value)
			i = i + 1
		return record


class Record:
	def __getattr__(self,name):
		""" Postgres changes all field names to lowercase, we want to be able to use "row.userName" instead of "row.username". """
		try:
			return self.__dict__[name.lower()]
		except:
			raise AttributeError, name