#
# Extensible User Folder
# 
# MySQL Property Source for exUserFolder
#
# (C) Copyright 2000,2001 The Internet (Aust) Pty Ltd
# 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: Clint Brubakken <cabrubak@acm.org> adapted from pgPropSource by Andrew Milton <akm@theinternet.com.au>
# $Id: mysqlPropSource.py,v 1.2 2002/08/23 14:05:53 mattbehrens 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_addmysqlPropSourceForm', globals())

import cPickle


def manage_addmysqlPropSource(self, REQUEST):
	""" Add a MySQL Prop Source """

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

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

manage_addmysqlPropSourceForm=HTMLFile('manage_addmysqlPropSourceForm', globals())
manage_editmysqlPropSourceForm=HTMLFile('manage_editmysqlPropSourceForm', globals())

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

	meta_type='Property Source'
	title='MySQL Properties'
	icon ='misc_/exUserFolder/exUserFolderPlugin.gif'
	manage_tabs=Acquisition.Acquired
	manage_editForm=manage_editmysqlPropSourceForm
	dict=PersistentMapping()
	
	def __init__(self, connection):
		self.id='mysqlPropSource'
		self.connection=connection
		self.addSQLQueries()

	def manage_editPropSource(self, REQUEST):
		""" Add a MySQL Prop Source """
		self.connection=REQUEST['mysqlprop_connection']
		self.delSQLQueries()
		self.addSQLQueries() # Re-add queries with new connection
		
	def hasProperty(self, key):
		if not self.dict.has_key(self.name):
			self.loadUserProperties(username=self.name)

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

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

	def delUserProperty(self, key, username):
		self.sqlDelProperty(username=username, key=key)
		if not self.dict.has_key(self.name):
			self.loadUserProperties(username=self.name)
		else:
			try:
				del self.dict[username][key]
				self._p_changed = 1
			except:
				pass

	def flushTempProperties(self):
		if self.dict.has_key(self.name):
			del self.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):
		if not self.dict.has_key(username):
			self.loadUserProperties(username=username)

		if self.dict[username].has_key(key):
			self.sqlUpdateProperty(username=username, key=key, value=cPickle.dumps(value))
		else:
			self.sqlInsertProperty(username=username, key=key, value=cPickle.dumps(value), temp=temp)
		self.dict[username][key]=value
		self._p_changed=1

	def getUserProperty(self, key, username, default=None):
		# Load Properties once we start asking for them...
		if not self.dict.has_key(username):
			self.loadUserProperties(username=username)
			
		if self.dict[username].has_key(key):
			return self.dict[username][key]
		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 loadUserProperties(self, username):
		self.dict[username]={}
		for p in self.sqlLoadProperties(username=username):
			try:
				self.dict[username][sqlattr(p, 'key')]=cPickle.loads(sqlattr(p, 'value'))
			except:
				self.dict[username][sqlattr(p, 'key')]=sqlattr(p, 'value')
		self._p_changed=1

	def listProperties(self):
		self.listUserProperties(username=self.name)
		
	def listUserProperties(self, username):
		if not self.dict.has_key(username):
			self.loadUserProperties(username=username)
		if self.dict.has_key(username):
			return self.dict[username].keys()
		return None
	
	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):
		for username in userids:
			self.sqlDeleteUser(username=username)
			if self.dict.has_key(username):
				del self.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.dict[username][key]=value
				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, '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

mysqlPropReg=PluginRegister('mysqlPropSource', 'MySQL Properties Source',
						 mysqlPropSource, manage_addmysqlPropSourceForm,
						 manage_addmysqlPropSource,
						 manage_editmysqlPropSourceForm)

exUserFolder.propSources['mysqlPropSource']=mysqlPropReg

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 (prop_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 prop_key=<dtml-sqlvar key type=string>
AND username=<dtml-sqlvar username type=string>
'''

_sqlLoadProperties='''
SELECT username, prop_key as 'key', value, istemporary
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 prop_key=<dtml-sqlvar key type=string>
'''

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