#
# Extensible User Folder
# 
# Postgres Property Source for exUserFolder
#
# (C) Copyright 2000,2001 The Internet (Aust) Pty Ltd
# ACN: 082 081 472  ABN: 83 082 081 472
# All Rights Reserved
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.
#
# Author: Andrew Milton <akm@theinternet.com.au>
# $Id: pgPropSource.py,v 1.23 2003/03/11 04:53:26 akm Exp $
from Globals import HTMLFile, MessageDialog, INSTANCE_HOME,Acquisition

from OFS.Folder import Folder

from ZODB.PersistentMapping import PersistentMapping
from Products.ZSQLMethods.SQL import SQL

from Products.exUserFolder.exUserFolder import exUserFolder
from Products.exUserFolder.Plugins import PluginRegister

from Persistence import Persistent

manage_addPropSourceForm=HTMLFile('manage_addpgPropSourceForm', globals())

import cPickle


def manage_addpgPropSource(self, REQUEST):
	""" Add a Postgres Prop Source """

	connection=REQUEST['pgprop_connection']
	o = pgPropSource(connection)
	self._setObject('pgPropSource', o, None, None, 0)
	o = getattr(self, 'pgPropSource')

	# Allow Prop Source to setup default users...
	if hasattr(o, 'postInitialisation'):
		o.postInitialisation(REQUEST)
	self.currentPropSource=o

manage_addpgPropSourceForm=HTMLFile('manage_addpgPropSourceForm', globals())
manage_editpgPropSourceForm=HTMLFile('manage_editpgPropSourceForm', globals())

#
# Very simple thing
#
class pgPropSource(Folder):
	""" Store User Data in a Postgres Database """

	meta_type='Property Source'
	title='Postgresql Properties'
	icon ='misc_/exUserFolder/exUserFolderPlugin.gif'
	manage_tabs=Acquisition.Acquired
	manage_editForm=manage_editpgPropSourceForm


	#
	# Reduce this if it is too long
	# Increase it if it's too short
	# This isn't a tunable right now.
	#
	PROPERTY_CACHE_TIME=10.0

	
	def __init__(self, connection):
		self.id='pgPropSource'
		self.connection=connection
		self.addSQLQueries()
		self._v_dict=None
		self.loadDict()

	def loadDict(self):
		if not hasattr(self, '_v_dict'):
			self._v_dict=None
		if not self._v_dict:
			self._v_dict={}
			
		
	def manage_editPropSource(self, REQUEST):
		""" Add a Postgres Prop Source """
		self.connection=REQUEST['Acmisprop_connection']
		self.delSQLQueries()
		self.addSQLQueries() # Re-add queries with new connection
		
	def hasProperty(self, key):
		self.loadDict()
		self.loadUserProperty(username=self.name, key = key)

		if not self._v_dict.has_key(self.name):
			return 0
		
		return self._v_dict[self.name].has_key(key)

	def delProperty(self, key):
		self.delUserProperty(key=key, username=self.name)

	def delUserProperty(self, key, username):
		self.loadDict()		
		self.sqlDelProperty(username=username, key=key)
		self.loadUserProperties(username=self.name)
		try:
			del self._v_dict[username][key]
		except:
			pass

	def flushTempProperties(self):
		self.loadDict()		
		if self._v_dict.has_key(self.name):
			del self._v_dict[self.name]
		self.sqlDelTempProperties(username=self.name)
		self.loadUserProperties(username=self.name)
		
	def setProperty(self, key, value):		
		self.setUserProperty(key=key, value=value, username=self.name)

	def setTempProperty(self, key, value):
		self.setUserProperty(key=key, value=value, username=self.name, temp=1)
		
	def setUserProperty(self, key, username, value, temp=0):	
		self.loadDict()	
		if not self._v_dict.has_key(username):
			self._v_dict[username]={}

		self.loadUserProperty(username, key)

		if self._v_dict[username].has_key(key):
			done = 0
			tries = 0
			while not done:
				try:
					self.sqlUpdateProperty(username=username, key=key, value=cPickle.dumps(value))
					done = 1
				except ConfictError:
					tries = tries + 1
					if tries > 10:
						# Give up
						done = 1

		else:
			self.sqlInsertProperty(username=username, key=key, value=cPickle.dumps(value), temp=temp)

		self._v_dict[username][key]={}
		self._v_dict[username][key]['data']=value
		self._v_dict[username][key]['lastLoad']=time.time()

		self._v_dict[username][key]['lastLoad']=0.0

	def getUserProperty(self, key, username, default=None):
		# Load Properties once we start asking for them...
		self.loadDict()		
		self.loadUserProperty(username=username, key=key)
			
		if self._v_dict[username].has_key(key):
			return self._v_dict[username][key]['data']
		return default
		
	def getProperty(self, key, default=None):
		return self.getUserProperty(key=key, username=self.name,
									default=default)
	
	def loadProperties(self):
		self.loadUserProperties(username=self.name)

	def loadUserProperty(self, username, key):
		self.loadDict()
		if not self._v_dict.has_key(username):
			self._v_dict[username]={}
		
		now = time.time()
		if self._v_dict[username].has_key(key):
			if now - self._v_dict[username][key]['lastLoad'] < self.PROPERTY_CACHE_TIME:
				return
		for p in self.sqlLoadProperty(username=username, key=key):
			self._v_dict[username][key]={}
			self._v_dict[username][key]['data']=None
			self._v_dict[username][key]['lastLoad']=now			
			try:
				self._v_dict[username][key]['data']=cPickle.loads(sqlattr(p, 'value'))
			except:
				self._v_dict[username][key]['data']=sqlattr(p, 'value')
			return

		# There wasn't one that existed in the db
		# Therefore any existing one we might have must be out of date
		# and should be deleted.
		try:
			del self._v_dict[username][key]
		except:
			pass

	def loadUserProperties(self, username):
		self.loadDict()
		now = time.time()		
		self._v_dict[username]={}
		for p in self.sqlLoadProperties(username=username):
			key = sqlattr(p, 'key')
			self._v_dict[username][key]={}
			try:
				self._v_dict[username][key]['data']=cPickle.loads(sqlattr(p, 'value'))
			except:
				self._v_dict[username][key]['data']=sqlattr(p, 'value')	

			self._v_dict[username][key]['lastLoad']=now
		
	def listProperties(self):
		self.listUserProperties(username=self.name)
		
	def listUserProperties(self, username):
		self.loadDict()
		self.loadUserProperties(username=username)		

		if self._v_dict.has_key(username):
			return self._v_dict[username].keys()
		return []
	
	def createUser(self, username, REQUEST):
		for k in REQUEST.keys():
			if k[:5]=='user_':
				key=k[5:]
				value=REQUEST[k]
				self.sqlInsertProperty(username=username, key=key, value=value, temp=0)

	def deleteUsers(self, userids):
		self.loadDict()		
		for username in userids:
			self.sqlDeleteUser(username=username)
			if self._v_dict.has_key(username):
				del self._v_dict[username]

	def updateUser(self, username, REQUEST):
		self.loadUserProperties(username)
		for k in REQUEST.keys():
			if k[:5]=='user_':
				key=k[5:]
				value=REQUEST[k]
				self._v_dict[username][key]={}
				self._v_dict[username][key]['data']=value
				self._v_dict[username][key]['lastLoad']=time.time()
				self.sqlUpdateProperty(username=username, key=key, value=value)


	def delSQLQueries(self):
		sqllist=self.objectIds('Z SQL Method')
		self.manage_delObjects(ids=sqllist)
		
	def addSQLQueries(self):
		if not hasattr(self, 'sqlLoadProperties'):
			sqlLoadProperties=SQL(
				'sqlLoadProperties',
				'Load Properties for One User',
				self.connection,
				'username',
				_sqlLoadProperties)

			self._setObject('sqlLoadProperties', sqlLoadProperties)

		if not hasattr(self, 'sqlLoadProperty'):
			sqlLoadProperty=SQL(
				'sqlLoadProperty',
				'Load One Property for One User',
				self.connection,
				'username key',
				_sqlLoadProperty)

			self._setObject('sqlLoadProperty', sqlLoadProperty)			

		if not hasattr(self, 'sqlUpdateProperty'):
			sqlUpdateProperty=SQL(
				'sqlUpdateProperty',
				'Update One Property',
				self.connection,
				'key value username',
				_sqlUpdateProperty)

			self._setObject('sqlUpdateProperty', sqlUpdateProperty)

		if not hasattr(self, 'sqlInsertProperty'):
			sqlInsertProperty=SQL(
				'sqlInsertProperty',
				'Insert a New Property',
				self.connection,
				'key value username temp:int',
				_sqlInsertProperty)

			self._setObject('sqlInsertProperty', sqlInsertProperty)

		if not hasattr(self, 'sqlDeleteUser'):
			sqlDeleteUser=SQL(
				'sqlDeleteUser',
				'Delete a Users properties',
				self.connection,
				'username',
				_sqlDeleteUser)

			self._setObject('sqlDeleteUser', sqlDeleteUser)

		if not hasattr(self, 'sqlDelProperty'):
			sqlDelProperty=SQL(
				'sqlDelProperty',
				'Delete a Property',
				self.connection,
				'username key',
				_sqlDelProperty)

			self._setObject('sqlDelProperty', sqlDelProperty)

		if not hasattr(self, 'sqlDelTempProperties'):
			sqlDelTempProperties=SQL(
				'sqlDelTempProperties',
				'Delete all temp properties for a user',
				self.connection,
				'username',
				_sqlDelTempProperties)

			self._setObject('sqlDelTempProperties', sqlDelTempProperties)

	def __setstate__(self, state):
		Persistent.__setstate__(self, state)
		self.addSQLQueries()

	def postInitialisation(self, REQUEST):
		pass

pgPropReg=PluginRegister('pgPropSource', 'Postgresql Properties Source',
						 pgPropSource, manage_addpgPropSourceForm,
						 manage_addpgPropSource,
						 manage_editpgPropSourceForm)

exUserFolder.propSources['pgPropSource']=pgPropReg

from string import upper, lower
import Missing
mt=type(Missing.Value)

def typeconv(val):
    if type(val)==mt:
        return ''
    return val

def sqlattr(ob, attr):
    name=attr
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    attr=upper(attr)
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    attr=lower(attr)
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    raise NameError, name

_sqlInsertProperty='''
INSERT INTO UserProperties (key, username, value, istemporary)
VALUES(<dtml-sqlvar key type=string>,
       <dtml-sqlvar username type=string>,
	   <dtml-sqlvar value type=string>,
	   <dtml-sqlvar temp type=int>)
'''

_sqlUpdateProperty='''
UPDATE UserProperties
SET value=<dtml-sqlvar value type=string>
WHERE key=<dtml-sqlvar key type=string>
AND username=<dtml-sqlvar username type=string>
'''

_sqlLoadProperties='''
SELECT *
FROM UserProperties
WHERE username=<dtml-sqlvar username type=string>
'''

_sqlDeleteUser='''
DELETE FROM UserProperties
WHERE username=<dtml-sqlvar username type=string>
'''

_sqlDelProperty='''
DELETE FROM UserProperties
WHERE username=<dtml-sqlvar username type=string>
AND key=<dtml-sqlvar key type=string>
'''

_sqlDelTempProperties='''
DELETE FROM UserProperties
WHERE username=<dtml-sqlvar username type=string>
AND isTemporary=1
'''

_sqlLoadProperty='''
SELECT *
FROM UserProperties
WHERE username=<dtml-sqlvar username type=string>
AND key=<dtml-sqlvar key type=string>
'''