# Copyright 2004,2005 Pierre Martineau <pmartino@users.sourceforge.net>
# This file is part of Bibus, a bibliographic database that can
# work together with OpenOffice.org to generate bibliographic indexes.
#
# Bibus 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.
#
# Bibus 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 Bibus; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA.
#
"""Base class for db connection.
__init__
selectDatabase
__encode
__listEncode
get_insert_id
getDbInfo
duplicateIdentifier
getGrants
must be overrided
You must also define Error and IntegrityError
"""
#
import wx
import time
import BIB
SUBSTR = 'SUBSTR'	# SUBSTRING keyword. 'SUBSTR' in SQLite and MySQL >= 4.1; 'SUBSTRING' in MySQL all versions
TABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE = "","","","","","" # set in derived classes

class dbBib(object):
	def __init__(self,parent=None,paramstyle='pyformat'):
		self.tableRef = ''
		self.tableKey = ''
		self.tableLink = ''
		self.tableQuery = ''
		self.tableModif = ''
		self.tableFile = ''
		self.parent=parent
		self.dbConnection = None	# database connection
		# parameter for DB API2 = "%s" for 'format' and 'pyformat'; "?" for "qmark"
		if paramstyle in ('pyformat','format'):
			self.param = "%s"
		elif paramstyle == 'qmark':
			self.param = "?"
		else:
			print "Error, the dbBibBase can only handle paramstyle == 'format,'pyformat' or 'qmark' but not %s"%paramstyle

#
# This function format the Identifier
# You can overload it if you want to change the default behaviour
	def newIdentifier(self,record):
		"""Return a Identifier. Identifier = AuthorYear where Author is the first author name.
		Spaces are also replaced by _"""
		author = record[BIB.BIBLIOGRAPHIC_FIELDS['Author']]
		if not author: author = 'Anonymous'				# put anonymous for the name => more readable
		return author.split(',')[0].replace(' ','_')+record[BIB.BIBLIOGRAPHIC_FIELDS['Year']]	# FirstAuthorYear
#
# Database specific funtions
#
#
	def get_insert_id(self):
		"""Return the last inserted auto_incremented id"""
		return None

	def getDbInfo(self,key=''):
		"""Return a tuple that represent the database connection. For mysql we use  (host,db,table).
		For another database it could be a file name or ... something that identify the database + connection"""
		return (None,)


	def createDatabase(self,db_name,tableref,tablekey,tablelink,tablequery,tablemodif,tablefile):
		return

	def duplicateIdentifier(self,e):
		return True

	def getGrants(self):
		"""Return a string
		True if temp table may be created with INSERT,DELETE,SELECT
		'rw' if bibref is INSERT,DELETE,SELECT,UPDATE ; 'r' if INSERT ; '' else
		'rw' if bibrefKey is INSERT,DELETE,SELECT,UPDATE or 'r' or ''
		'rw' if bibrefLink is INSERT,DELETE,SELECT,UPDATE or 'r' or ''
		'rw' if bibquery is INSERT,DELETE,SELECT,UPDATE or 'r' or ''
		return:
			'rw' if True,'rw','rw','rw','rw'
			'ro' if Any,'r','rw','rw','rw'
			'rk' if Any,'r','r','r','r'
			'rr' else (normally we need at least 'r' on bibref)	# rr = ReadRestricted
		"""
		return 'rw'

# the following classes should be ok for any database compatible with API 2.0

	def __writeRef(self,ref):
		"""Error if Identifier already exists => we have to solve the duplicate"""
		# we first test for duplicate references according to settings
		if BIB.DUPLICATES_TEST:
			if BIB.DUPLICATES_CASE:
				tmpstr = """select Id from %s where """% self.tableRef + ' and '.join( '%s = %s'%(field,self.param) for field in BIB.DUPLICATES )
			else:
				tmpstr = """select Id from %s where """% self.tableRef + ' and '.join( 'lower(%s) = lower(%s)'%(field,self.param) for field in BIB.DUPLICATES )
			self.dbCursor.execute( tmpstr, tuple([ref[BIB.BIBLIOGRAPHIC_FIELDS[field]] for field in BIB.DUPLICATES]) )
			self.dbConnection.commit()
			ref_id = self.dbCursor.fetchone()
			if ref_id:								# we found a duplicate. We use the first one.
				if not BIB.DUPLICATES_KEEP_OLD:		# we update the old ref with the new values, except Id,Identifier
					self.modifyRef(BIB.BIB_FIELDS[2:],ref[2:],ref_id[0])
				return ref_id[0]				# we return the id of the duplicate
		# The reference is not in the db already => we save it
		try:
			tmpstr = """INSERT INTO %s VALUES (""" % self.tableRef
			self.dbCursor.execute(tmpstr + (len(ref)*('%s,'%self.param))[:-1] + ")", ref)
			self.dbConnection.commit()
			ref_id = self.get_insert_id()
			self.updateCreator( ref_id )
			return ref_id
		#except IntegrityError,errorType:	# does not work with pysqlite2 because duplicates don't raise IntegrityError but an Error
		except Error,errorType:
			if self.duplicateIdentifier(errorType):
				storedIdentifier = ref[1]
				ref[1] = None
				ref_id = self.__writeRef(ref)
				#ref_id = self.get_insert_id()
				self.modifyRef(('Identifier',),(storedIdentifier,),ref_id)
				return ref_id
			else:
				self.showError("dbBibBase.__writeRef " + `errorType.args`)
				return None
		#except Error,errorType:
		#	print Error
		#	print errorType
		#	self.showError(`errorType.args`)
		#	return None

	def writeRef(self,ref):
		ref = list(ref)
		if not ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']]:
			ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']] = self.newIdentifier(ref)	# Format a new Identifier if needed
		return self.__writeRef(ref)

	def __write(self,table,ref):
		"""Automatically take care of duplicates"""
		try:
			tmpstr = """REPLACE INTO %s VALUES (""" % (table)
			self.dbCursor.execute(tmpstr + (len(ref)*('%s,'%self.param))[:-1] + ")", ref)
			self.dbConnection.commit()
		except Error,errorType:
			self.showError("dbBibBase.__write " + `errorType.args`)

	def writeRefOnline(self,ref):
		ref = list(ref)
		if not ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']]:
			ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']] = self.newIdentifier(ref)	# Format a new Identifier if needed
		self.__write(BIB.TMP_ONLINE,ref)

	def writeRefImport(self,ref):
		ref = list(ref)
		if not ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']]:
			ref[BIB.BIBLIOGRAPHIC_FIELDS['Identifier']] = self.newIdentifier(ref)	# Format a new Identifier if needed
		self.__write(BIB.TMP_IMPORT,ref)

	def writeKey(self,user,ref):
		"""Write a new Key. key_id is autoincremented
		ref = (parentID,key_name)"""
		#self.__write(self.tableKey,(self.user,NULL)+ref)	# does not work anymore with pysqlite2 because we cannot get the Id with lastrowid
		# as it was the case with pysqlite1.x
		# we must thus use an INSERT instead of a REPLACE
		# ('user','key_Id','parent','key_name')
		try:
			self.dbCursor.execute( """INSERT INTO %s VALUES (%s,%s,%s,%s)""" % \
									(self.tableKey,self.param,self.param,self.param,self.param) , \
									(user,None)+ref )
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBibBase. writeKey: ' + `errorType.args`)

	def writeLink(self,link):
		"""link = tuple = (key_id,ref_id)"""
		self.__write(self.tableLink,link)

	def writeNewQuery(self,user,query_name,query):
		"""query = search_string"""
		self.__write(self.tableQuery,(None,user,query_name,query))

	def delLink(self,key_id,ref_id):
		tmpstr = """DELETE FROM %s WHERE %s=%s AND %s=%s""" % (self.tableLink,'key_id',self.param,'ref_id',self.param)
		try:
			self.dbCursor.execute(tmpstr,(key_id,ref_id))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.delLink: '+`errorType.args`)

	def __deleteKey(self,user,key_id):
		"""Delete the key key_id and all the links containing key_id"""
		try:
			self.dbCursor.execute("""DELETE FROM %s WHERE key_id = %s"""%(self.tableLink,self.param),(key_id,))
			self.dbCursor.execute("""DELETE FROM %s WHERE key_id = %s AND user = %s"""%(self.tableKey,self.param,self.param),(key_id,user))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError(u'dbBib.deleteKey:' + `errorType.args`)

	def __deleteKeyChildren(self,user,key_id):
		"""Recursively delete the children of key_id and all the children's links"""
		for name,child_id in self.getKeyChildren(user,key_id):
			self.__deleteKeyChildren(user,child_id)
			self.__deleteKey(user,child_id)

	def deleteKey(self,user,key_id):
		"""Recursively delete the key with key_id, its children and all the links"""
		self.__deleteKeyChildren(user,key_id)	# delete children, etc..
		self.__deleteKey(user,key_id)			# delete the key and its links

	def __getAllRef(self,table,collist,order,how=BIB.LIST_HOW,short=False):
		try:
			if not short:
				self.dbCursor.execute("""SELECT %s FROM %s ORDER BY %s %s"""% (','.join(('Id',)+collist),table,order,how))	# column Id added for SetData of refList
			else:
				# SUBSTR requires MySQL 4.1.1; For previous versions we should use SUBSTRING
				self.dbCursor.execute("""SELECT %s FROM %s ORDER BY %s %s"""% (','.join(['Id']+["%s(%s,1,%s)"%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),table,order,how))	# column Id added for SetData of refList
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.__getAllRef " + `errorType.args`)
			return None

	def getAllDatabase(self,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return all the refs including the Trash"""
		return self.__getAllRef(self.tableRef,collist,order,how,short)

	def getAllRefOnline(self,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		return self.__getAllRef(BIB.TMP_ONLINE,collist,order,how,short)

	def getAllRefImport(self,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		return self.__getAllRef(BIB.TMP_IMPORT,collist,order,how,short)
		
	def getAllRef(self,user,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return all the references tagged by user, ie all the refs except Trash"""
		try:
			if short:
					self.dbCursor.execute("""SELECT %s FROM %s WHERE Id IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user=%s) ORDER BY %s %s"""\
										%(','.join(['Id']+["%s(%s,1,%s)"%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),\
										self.tableRef, self.tableLink, self.tableKey,self.param,order,how),\
										(user,))
			else:
					self.dbCursor.execute("""SELECT %s FROM %s WHERE Id IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user=%s) ORDER BY %s %s"""\
										%(','.join(('Id',)+collist),\
										self.tableRef, self.tableLink, self.tableKey,self.param,order,how),\
										(user,))
			return self.dbCursor.fetchall()					
		except Error,errorType:
			self.showError("dbBibBase.getAllRef " + `errorType.args`)
			return None
		
	def getTrashRef(self,user,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return all the refs non tagged by the current user => Trash"""
		try:
			if short:
					self.dbCursor.execute("""SELECT %s FROM %s WHERE Id NOT IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user=%s) ORDER BY %s %s"""\
										%(','.join(['Id']+["%s(%s,1,%s)"%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),\
										self.tableRef, self.tableLink, self.tableKey,self.param,order,how),\
										(user,))
			else:
					self.dbCursor.execute("""SELECT %s FROM %s WHERE Id NOT IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user=%s) ORDER BY %s %s"""\
										%(','.join(('Id',)+collist),\
										self.tableRef, self.tableLink, self.tableKey,self.param,order,how),\
										(user,))
			return self.dbCursor.fetchall()					
		except Error,errorType:
			self.showError("dbBibBase.getTrashRef " + `errorType.args`)
			return None	

	def __deleteTmpRef(self,table):
		try:
			self.dbCursor.execute("""DELETE FROM %s"""%table)
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.__deleteTmpRef' + `errorType.args`)

	def deleteImport(self):
		"""Delete all the ref in BIB.TMP_IMPORT table"""
		self.__deleteTmpRef(BIB.TMP_IMPORT)

	def deleteOnline(self):
		"""Delete all the ref in BIB.TMP_ONLINE table"""
		self.__deleteTmpRef(BIB.TMP_ONLINE)

	def __getRef(self,table,ref_id,collist,idcol='Id'):
		"""Return the reference corresponding to ref_id for column idcol"""
		try:
			tmpstr = """SELECT %s FROM %s WHERE %s=%s""" % (','.join(collist),table,idcol,self.param)
			self.dbCursor.execute(tmpstr, (ref_id,))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.__getRef " + `errorType.args`)
			return None

	def getRefFromIdentifier(self,identifier,collist=BIB.BIB_PRINT_FIELD):
		"""Return the reference corresponding to identifier in database
		This is used for the connection with OOo"""
		return self.__getRef(self.tableRef,identifier,collist,'Identifier')

	def getRef(self,ref_id,collist=BIB.BIB_PRINT_FIELD):
		"""Return the reference corresponding to ref_id in database"""
		return self.__getRef(self.tableRef,ref_id,collist)

	def getRefImport(self,ref_id,collist=BIB.BIB_PRINT_FIELD):
		"""Return the reference corresponding to ref_id in TMP_IMPORT"""
		return self.__getRef(BIB.TMP_IMPORT,ref_id,collist)

	def getRefOnline(self,ref_id,collist=BIB.BIB_PRINT_FIELD):
		"""Return the reference corresponding to ref_id in TMP_ONLINE"""
		return self.__getRef(BIB.TMP_ONLINE,ref_id,collist)

	def getRefKey(self,key_id,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the key with key_id"""
		try:
			if not short:
				tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s ORDER BY %s %s""" %('Id,' + ','.join(collist),self.tableRef,self.tableLink,'Id','ref_id','key_id',self.param,order,how)
			else:
				tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s ORDER BY %s %s""" %(','.join(['Id'] + ['%s(%s,1,%s)'%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),self.tableRef,self.tableLink,'Id','ref_id','key_id',self.param,order,how)
			self.dbCursor.execute(tmpstr, (key_id,))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getRefKey " + `errorType.args`)
			return None

	def getRefKeySearch(self,key_id,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the key with key_id and the search 'searchStr'"""
		searchStr = searchStr.replace('%','%%')	# protect against substitution
		try:
			if not short:
				tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s AND %s ORDER BY %s %s""" %(','.join(('Id',)+ collist),self.tableRef,self.tableLink,'Id','ref_id','key_id',self.param,searchStr,order,how)
			else:
				tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s AND %s ORDER BY %s %s""" %(','.join(['Id']+ ['%s(%s,1,%s)'%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),self.tableRef,self.tableLink,'Id','ref_id','key_id',self.param,searchStr,order,how)
			#print tmpstr
			self.dbCursor.execute(tmpstr.encode(BIB.ENCODING,BIB.ENC_ERRORS), (key_id,))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getRefKeySearch " + `errorType.args`)
			return None

	def __getAllRefSearch(self,searchStr,table,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the search 'searchStr' from table"""
		try:
			#print searchStr.encode('latin1')
			if not short:
				tmpstr = u"""SELECT %s FROM %s WHERE %s ORDER BY %s %s""" %(','.join(('Id',)+ collist),table,searchStr,order,how)
			else:
				tmpstr = u"""SELECT %s FROM %s WHERE %s ORDER BY %s %s""" %(','.join(['Id']+ ['%s(%s,1,%s)' %(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),table,searchStr,order,how)
			self.dbCursor.execute(tmpstr.encode(BIB.ENCODING,BIB.ENC_ERRORS))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getAllRefSearch: " + `errorType.args`)
			return None

	def getAllDatabaseRefSearch(self,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the search 'searchStr'"""
		return self.__getAllRefSearch(searchStr,self.tableRef,collist,order,how,short)

	def getOnlineRefSearch(self,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		return self.__getAllRefSearch(searchStr,BIB.TMP_ONLINE,collist,order,how,short)
			
	def getImportRefSearch(self,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		return self.__getAllRefSearch(searchStr,BIB.TMP_IMPORT,collist,order,how,short)	

	def getAllRefSearch(self,user,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the search 'searchStr' from all the refs belonging to current user"""
		try:
			if not short:
				tmpstr = """SELECT %s FROM %s WHERE Id IN \
							(SELECT t1.ref_Id FROM %s AS t1 \
							JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
							WHERE user="%s") AND %s ORDER BY %s %s"""\
							%(','.join(('Id',)+ collist),\
							self.tableRef, self.tableLink, self.tableKey,user,searchStr,order,how)
			else:
				tmpstr = """SELECT %s FROM %s WHERE Id IN \
							(SELECT t1.ref_Id FROM %s AS t1 \
							JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
							WHERE user="%s") AND %s ORDER BY %s %s"""\
							%(','.join(['Id']+["%s(%s,1,%s)"%(SUBSTR,i,BIB.LIST_SHORT) for i in collist]),\
							self.tableRef, self.tableLink, self.tableKey,user,searchStr,order,how)
			self.dbCursor.execute(tmpstr.encode(BIB.ENCODING,BIB.ENC_ERRORS))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getAllRefSearch: " + `errorType.args`)
			return None
			
	def getTrashRefSearch(self,user,searchStr,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the search 'searchStr' from all the refs in the Trash of current user"""
		try:
			if short:
					tmpstr = """SELECT %s FROM %s WHERE Id NOT IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user="%s") AND %s ORDER BY %s %s"""\
										%(','.join(['Id']+["%s(%s,1,%s)"%(SUBSTR,i,BIB.LIST_SHORT)\
										    for i in collist]),\
										self.tableRef, self.tableLink, self.tableKey,user,searchStr,order,how)
			else:
					tmpstr = """SELECT %s FROM %s WHERE Id NOT IN \
										(SELECT t1.ref_Id FROM %s AS t1 \
										JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
										WHERE user="%s") AND %s ORDER BY %s %s"""\
										%(','.join(('Id',)+collist),\
										self.tableRef, self.tableLink, self.tableKey,user,searchStr,order,how)
			self.dbCursor.execute(tmpstr.encode(BIB.ENCODING,BIB.ENC_ERRORS))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getTrashRefSearch: " + `errorType.args`)
			return None

	def getRefFromIdentifiers(self,Identifiers,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW):
		"""Return the references corresponding to the list of Identifiers"""
		if not Identifiers: return ()
		try:
			query = \
			u"""SELECT %s FROM %s WHERE """ %('Id,' + ','.join(collist),self.tableRef) \
			+ "OR".join((u"""Identifier=%%s""",)*len(Identifiers)) \
			+ u"""WHERE %s ORDER BY %s %s""" %(order,how)
			self.dbCursor.execute(query,Identifiers)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getRefFromIdentifiers: " + `errorType.args`)
			return None

	def getQueryRef(self,user,query_id,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW,short=False):
		"""Return the references corresponding to the query with id query_id in table bibquery"""
		try:
			self.dbCursor.execute("""SELECT query FROM %s WHERE query_id = %s""" %(self.tableQuery,query_id))
			searchstr = self.dbCursor.fetchall()[0][0]
			return self.getAllRefSearch(user,searchstr,collist,order,how,short)
		except Error,errorType:
			self.showError("dbBibBase.getQueryRef " + `errorType.args`)
			return None

	def __getQuery(self,col,query_id):
		try:
			tmpstr = """SELECT %s FROM %s WHERE query_id = %s""" % (col,self.tableQuery,self.param)
			self.dbCursor.execute(tmpstr, (query_id,))
			return self.dbCursor.fetchall()[0][0]
		except Error,errorType:
			self.showError('dbBib.__getQuery: '+`errorType.args`)

	def getQueryName(self,query_id):
		"""Return the name of query query_id"""
		return self.__getQuery('name',query_id)

	def getQuery(self,query_id):
		"""Return the query of query query_id"""
		return self.__getQuery('query',query_id)

	def getQueries(self,user,restrict=''):
		"""Return list of (name,key_id) of children of Key ID_QUERY_ROOT from table self.tableQuery
		restricted to name LIKE %restrict% """
		try:
			tmpstr = """SELECT %s,%s FROM %s WHERE user=%s AND name LIKE %s""" % ('name','query_id',self.tableQuery,self.param,self.param)
			self.dbCursor.execute(tmpstr, (user,'%'+restrict+'%'))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getQueries " + `errorType.args`)
			return None

	def __modifyQuery(self,col,query_id,value):
		"""Change the column col value of the query with query_id"""
		try:
			tmpstr = """UPDATE %s SET %s = %s where query_id = %s""" % (self.tableQuery,col,self.param,self.param)
			self.dbCursor.execute(tmpstr, (value,query_id))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.__modifyQuery: '+`errorType.args`)

	def renameQuery(self,query_id,newName):
		"""Change the name of the current query"""
		self.__modifyQuery('name',query_id,newName)

	def setQuery(self,query_id,query):
		"""Set a new 'query' in the current query"""
		self.__modifyQuery('query',query_id,query)

	def deleteQuery(self,query_id):
		"""Delete the query with id query_id"""
		try:
			self.dbCursor.execute("""DELETE FROM %s WHERE query_id=%s"""%(self.tableQuery,self.param),(query_id,))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.deleteQuery: '+`errorType.args`)

	def modifyRef(self,collist,valuelist,ref_id):
		valuelist = list(valuelist)
		collist = list(collist)
		try:
			if not valuelist[collist.index('Identifier')]:								# Identifier is ''
				ref = list( self.getRef(ref_id,BIB.BIB_FIELDS)[0] )						# get the ref from the database
				for pos in xrange(len(collist)):
					ref[BIB.BIBLIOGRAPHIC_FIELDS[collist[pos]]] = valuelist[pos]		# update the fields
				valuelist[collist.index('Identifier')] = self.newIdentifier(ref)	# Format a new Identifier if needed
		except ValueError:
			pass			# Identifier is not in the changed Fields => we don't have to check for empty Identifier
		#
		try:
			tmpstr = """UPDATE %s SET """ % self.tableRef + ','.join( ["%s = %s"%(c,self.param) for c in collist] )
			valuelist.append(ref_id)
			#print tmpstr + """ WHERE Id=%s"""%self.param
			self.dbCursor.execute(tmpstr + """ WHERE Id=%s"""%self.param , tuple(valuelist) )
			self.dbConnection.commit()
			self.updateLastModif( ref_id )
		#except IntegrityError,errorType: # This does not work with pysqlite2		# duplicate Identifier
		except Error,errorType:									# duplicate Identifier
			if self.duplicateIdentifier(errorType):				# possible only if we redefined the Identifier => is in collist
				valuelist.pop()									# remove the ref_id we added before
				valuelist[collist.index('Identifier')] = BIB.SEP_DUP.join( (valuelist[collist.index('Identifier')],repr(ref_id)) )
				self.modifyRef(collist,valuelist,ref_id)
			else:
				self.showError("dbBibBase.modifyRef " + `errorType.args`)

	def getKeyChildren(self,user,key_id,restrict=''):
		"""Return list of (key_name,key_id) of children of Key with id key_id
		where name LIKE restrict"""
		try:
			tmpstr = """SELECT %s,%s FROM %s WHERE user=%s AND parent=%s AND key_name LIKE %s ORDER BY key_name""" % ('key_name','key_id',self.tableKey,self.param,self.param,self.param)
			self.dbCursor.execute(tmpstr, (user,key_id,'%'+restrict+'%'))
			#print "getKeyLike",self.dbCursor.fetchall()
			#print self.dbCursor.fetchall()
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError("dbBibBase.getKeyChildren " + `errorType.args`)
			return None

	def getKeyParent(self,user,key_id):
		"""Return key_id of the parent of Key with id key_id"""
		try:
			tmpstr = """SELECT %s FROM %s WHERE user=%s AND %s=%s""" % ('parent',self.tableKey,self.param,'key_Id',self.param)
			self.dbCursor.execute(tmpstr, (user,key_id))
			#print "getKeyLike",self.dbCursor.fetchall()
			return self.dbCursor.fetchall()[0][0]
		except Error,errorType:
			self.showError('dbBib.getKeyParent: '+`errorType.args`)
			return None

	def getTupleKeyParent(self,key_id):
		"""Return key_id,name of the parent of Key with id key_id"""
		try:
			tmpstr = """SELECT t1.parent,t2.key_name from %s as t1 LEFT JOIN %s as t2 ON t1.parent = t2.key_Id WHERE t1.key_Id = %s""" %(self.tableKey,self.tableKey,self.param)
			self.dbCursor.execute(tmpstr, (key_id,))
			return self.dbCursor.fetchone()
		except Error,errorType:
			self.showError('dbBib.getTupleKeyParent: '+`errorType.args`)
			return None

	def getKeyPath(self,user,key_id):
		"""Return a tuple of lists [references,child1,child2,...,name(key_id)],[id_ref,id_child1,...,key_id]
		in the keytree of the user"""
		tmp = [self.getKeyName(user,key_id)]
		tmpId = [key_id]
		parent,name = self.getTupleKeyParent(key_id)
		while parent:
			tmp.append(name)
			tmpId.append(parent)
			parent,name = self.getTupleKeyParent(parent)
		tmp.reverse()
		tmpId.reverse()
		return tmp[1:],tmpId[1:]

	def getKeyId(self,parent,key_name):
		"""return the Id of the key with parent and key_name"""
		try:
			tmpstr = """SELECT %s FROM %s WHERE %s=%s AND %s=%s AND %s=%s""" % ('key_id',self.tableKey,'user',self.param,'parent',self.param,'key_name',self.param)
			self.dbCursor.execute(tmpstr, (self.user,parent,key_name))
			return self.dbCursor.fetchall()[0][0]
		except Error,errorType:
			self.showError('dbBib.getKeyId: '+`errorType.args`)

	def getKeyName(self,user,key_id):
		"""return the name of the key"""
		try:
			self.dbCursor.execute("select key_name from %s where key_id=%s AND user=%s"%(self.tableKey,self.param,self.param), (key_id,user))
			return self.dbCursor.fetchall()[0][0]
		except Error,errorType:
			self.showError('dbBib.getKeyName: '+`errorType.args`)

	def __modifyKey(self,user,col,value,key_id):
		try:
			tmpstr = """UPDATE %s SET %s=%s WHERE %s=%s and %s=%s""" % (self.tableKey,col,self.param,'user',self.param,'key_id',self.param)
			self.dbCursor.execute(tmpstr , (value,user,key_id))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.__modifyKey: '+`errorType.args`)

	def modifyKeyName(self,user,name,key_id):
		self.__modifyKey(user,'key_name',name,key_id)

	def modifyKeyParent(self,user,parent,key_id):
		self.__modifyKey(user,'parent',parent,key_id)

	def keyExist(self,user,parent_id,key_name):
		"""Return true if a key with the same name exist"""
		try:
			tmpstr = """SELECT %s FROM %s WHERE %s=%s AND %s=%s AND %s=%s""" % ('key_id',self.tableKey,'user',self.param,'key_name',self.param,'parent',self.param)
			self.dbCursor.execute(tmpstr , (user,key_name,parent_id))
			return bool(self.dbCursor.fetchall())
		except Error,errorType:
			self.showError('dbBib.keyExist: '+`errorType.args`)

	def getKeys(self,ref_id):
		"""Return list of key_id associated with ref_id for the current user"""
		try:
			self.dbCursor.execute("""SELECT t2.key_id FROM %s AS t1,%s AS t2 WHERE t1.key_id = t2.key_id AND t1.ref_id=%s AND t2.user=%s"""%(self.tableLink,self.tableKey,self.param,self.param) , (ref_id,self.user))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getKeys: '+`errorType.args`)

	def getRoot(self,user=''):
		"""Return the key_id of root"""
		try:
			tmpstr = """SELECT %s FROM %s WHERE %s=%s AND parent IS NULL""" % ('key_id',self.tableKey,'user',self.param)
			self.dbCursor.execute(tmpstr , (user,))
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getRoot: '+`errorType.args`)

	def check_db(self):
		""" Check that all the fields are indeed present in the selected database and print an error on failure"""
		fields = self.getFields(self.tableRef)
		if self.getGrants() in ('rw','ro'):
			fields2 = self.getFields(self.tableKey)
			fields3 = self.getFields(self.tableLink)
			fields4 = self.getFields(self.tableModif)
			fields5 = self.getFields(self.tableQuery)
			fields6 = self.getFields(self.tableFile)
			#
			for i in BIB.BIB_FIELDS:
				if i not in fields: break
			else:
				for j in BIB.BIB_KEYS:
					if j not in fields2: break
				else:
					for k in BIB.BIB_LINKS:
						if k not in fields3: break
					else:
						for l in BIB.BIB_MODIF:
							if l not in fields4: break
						else: 
							for m in BIB.BIB_QUERY:
								if m not in fields5: break
							else:
								for n in BIB.BIB_FILE:
									if n not in fields6: break
								else:
									return True
				self.showError(_("Some needed fields are absent from the selected database/tables.\nContinue at your own risk"))
				# Can SQlite 1.3/1.4 to 1.5 database conversion be invoked from elsewhere than the FirstStart Wizard?
				# e.g. here, or as a separate utility
				return False
		else:	# grants == 'rr'
			for i in BIB.BIB_FIELDS:
				if i not in fields:
					self.showError(_("Some needed fields are absent from the selected database/tables.\nContinue at your own risk"))
					# Can SQlite 1.3/1.4 to 1.5 database conversion be invoked from elsewhere than the FirstStart Wizard?
					# e.g. here, or as a separate utility
					return False
			return True

# the following routines are for sqlite export
	def getAllKeys(self):
		"""return all the keys from the table bibrefKey"""
		try:
			self.dbCursor.execute("""SELECT * FROM %s"""%self.tableKey)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.bibrefKey: '+`errorType.args`)
			
	def dumpKey(self,key):
		self.__write(self.tableKey,key)		

	def getAllLinks(self):
		"""return all the keys from the table bibrefLink"""
		try:
			self.dbCursor.execute("""SELECT * FROM %s"""%self.tableLink)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getAllLinks: '+`errorType.args`)

	def getAllQueries(self):
		"""return all the keys from the table bibrefLink"""
		try:
			self.dbCursor.execute("""SELECT * FROM %s"""%self.tableQuery)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getAllQueries: '+`errorType.args`)			
			
	def getAllModifs(self):
		"""return all the modifs from the table table_modif"""
		try:
			self.dbCursor.execute("""SELECT * FROM %s"""%self.tableModif)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getAllModifs: '+`errorType.args`)
			
	def getAllFiles(self):
		"""return all the files from the table table_file"""
		try:
			self.dbCursor.execute("""SELECT * FROM %s"""%self.tableFile)
			return self.dbCursor.fetchall()
		except Error,errorType:
			self.showError('dbBib.getAllFiles: '+`errorType.args`)
			
	def dumpQuery(self,query):
		self.__write(self.tableQuery,query)

# Modifications
	def setCreator(self,ref_id,creator,dateCreator,modif,dateModif):
		try:
			tmpstr = """INSERT INTO %s VALUES (%s,%s,%s,%s,%s)"""%(self.tableModif,self.param,self.param,self.param,self.param,self.param)
			self.dbCursor.execute(tmpstr, (ref_id,creator,dateCreator,modif,dateModif))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError("dbBibBase.setCreator " + `errorType.args`)
			
	def resetCreator(self,ref_id,creator,dateCreator,modif,dateModif):
		"""replace creator etc.. with new values. Used for exporting with no modif of modification table""" 
		try:
			tmpstr = """UPDATE %s SET creator=%s,date=%s,user_modif = %s,date_modif = %s WHERE ref_Id = %s"""%(self.tableModif,self.param,self.param,self.param,self.param,self.param)
			self.dbCursor.execute(tmpstr, (creator,dateCreator,modif,dateModif,ref_id))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError("dbBibBase.resetCreator " + `errorType.args`)	
			
	def updateCreator(self,ref_id):
		t = time.time()
		self.setCreator(ref_id,self.user,t,self.user,t)
		
	def dataCreatorExist(self,ref_id):
		self.dbCursor.execute( """SELECT ref_Id from %s where ref_Id=%s"""%(self.tableModif,self.param), (ref_id,) )
		return self.dbCursor.fetchall() != []

	def updateLastModif(self,ref_id):
		if self.dataCreatorExist(ref_id):
			try:
				tmpstr = """UPDATE %s SET user_modif = %s,date_modif = %s WHERE ref_Id = %s"""%(self.tableModif,self.param,self.param,self.param)
				self.dbCursor.execute(tmpstr, (self.user,time.time(),ref_id))
				self.dbConnection.commit()
			except Error,errorType:
				self.showError("dbBibBase.updateLastModif " + `errorType.args`)
		else:
			self.setCreator(ref_id,_("Unknown"),0,self.user,time.time())

	def getModifs(self,ref_id):
		try:
			tmpstr = """SELECT creator,date,user_modif,date_modif from %s WHERE ref_Id=%s"""%(self.tableModif,self.param)
			self.dbCursor.execute(tmpstr, (ref_id,))
			return self.dbCursor.fetchall()[0]
		except:
			return (_("Unknown"),0,_("Unknown"),0)
			
# Fulltext files. Table self.tableFile
	def getFiles(self,ref_Id):
		try:
			self.dbCursor.execute("""SELECT path FROM %s WHERE ref_Id = %s""" %(self.tableFile,self.param), (ref_Id,))
			tmp = self.dbCursor.fetchall()
			if tmp:
				return tuple([i[0] for i in tmp])
			else:
				return ()
		except Error,errorType:
			self.showError("dbBibBase.getFiles " + `errorType.args`)
			
	def setFiles(self,ref_Id,refs):
		try:
			self.dbCursor.execute("""DELETE FROM %s WHERE ref_Id = %s""" %(self.tableFile,self.param), (ref_Id,))
			values = tuple([(ref_Id,ref) for ref in refs])
			self.dbCursor.executemany("""INSERT INTO %s VALUES (%s,%s)""" %(self.tableFile,self.param,self.param),values)
			self.dbConnection.commit()
		except Error,errorType:
			self.showError("dbBibBase.setFiles " + `errorType.args`)

# Database cleanup
	def emptyTrash(self):
		"""We delete all the ref which are not tagged by any user"""
		try:
			# We delete first from table_modif
			#self.dbCursor.execute("""DELETE FROM %s WHERE ref_Id IN (SELECT Id FROM %s LEFT JOIN %s ON Id=ref_Id WHERE ref_Id IS NULL)"""\
			#						%(self.tableModif,self.tableRef,self.tableLink))
			self.dbCursor.execute("""DELETE FROM %s WHERE ref_Id NOT IN \
								(SELECT t1.ref_Id FROM %s AS t1 \
								JOIN %s AS t2 ON t1.key_Id=t2.key_Id)"""\
								%(self.tableModif, self.tableLink, self.tableKey))
			# Then the references
			#self.dbCursor.execute("""DELETE FROM %s WHERE Id IN (SELECT Id FROM %s LEFT JOIN %s ON Id=ref_Id WHERE ref_Id IS NULL)"""\
			#						%(self.tableRef,self.tableRef,self.tableLink))				
			self.dbCursor.execute("""DELETE FROM %s WHERE Id NOT IN \
								(SELECT t1.ref_Id FROM %s AS t1 \
								JOIN %s AS t2 ON t1.key_Id=t2.key_Id)"""\
								%(self.tableRef, self.tableLink, self.tableKey))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.emptyTrash' + `errorType.args`)
			
	def checkDatabase(self):
		error = False
		# Checking that all the tables are present: 
		tables = self.getTables()
		tablenames = {	self.tableRef:TABLE_REF,\
						self.tableKey:TABLE_KEY,\
						self.tableLink:TABLE_LINK,\
						self.tableQuery:TABLE_QUERY,\
						self.tableModif:TABLE_MODIF,\
						self.tableFile:TABLE_FILE}
		for table in tablenames.keys():
			if table not in tables:
				error = True
				ret = wx.MessageBox(_("""Table "%(name)s" is absent from the database.\nShould I fix it?""")%{'name':table},\
									_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
				if ret == wx.YES:
					self.dbCursor.execute("""create table %s %s""" % (table,tablenames[table]))
		# Checking that all the fields
		if not self.check_db():
			self.showError( _("""Sorry, I can't fix this error."""))
			error = True
			return
		# Checking for NULL Identifiers in bibref
		self.dbCursor.execute("""SELECT Id FROM %s WHERE Identifier IS NULL"""%self.tableRef)
		ids = self.dbCursor.fetchone()
		if ids:
			error = True
			ret = wx.MessageBox(_("The database contains references with NULL Identifiers\nShould I delete them ?"),_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
			if ret == wx.YES:
				self.dbCursor.execute("""DELETE FROM %s WHERE Identifier IS NULL"""%self.tableRef)
				self.dbConnection.commit()
		# Checking for Links with non-existent references
		self.dbCursor.execute("""SELECT key_Id,ref_Id FROM %s LEFT JOIN %s ON Id=ref_Id WHERE Id IS NULL"""\
								%(self.tableLink,self.tableRef))
		ids = self.dbCursor.fetchall()
		if ids:
			error = True
			ret = wx.MessageBox(_("The database contains wrong links\nShould I delete them ?"),_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
			if ret == wx.YES:
				# We first delete where key or ref is NULL
				self.dbCursor.execute("""DELETE FROM %s WHERE key_Id IS NULL OR ref_Id IS NULL"""%self.tableLink)
				for key_Id,ref_Id in ids:
					# We could use a single DELETE with MySQL but not with SQLite
					# Since there are only few links to remove, it should not be a problem
					self.dbCursor.execute("""DELETE FROM %s WHERE key_Id=%s AND ref_Id=%s"""\
											%(self.tableLink,self.param,self.param),\
											(key_Id,ref_Id))
				self.dbConnection.commit()
		# Checking for Links with non-existent keys
		self.dbCursor.execute("""SELECT t1.key_Id,ref_Id FROM %s AS t1 \
								LEFT JOIN %s AS t2 ON t1.key_Id=t2.key_Id \
								WHERE t2.key_Id IS NULL"""
								%(self.tableLink,self.tableKey))
		ids = self.dbCursor.fetchall()
		if ids:
			error = True
			ret = wx.MessageBox(_("The database contains wrong links\nShould I delete them ?"),_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
			if ret == wx.YES:
				# We first delete where key or ref is NULL
				self.dbCursor.execute("""DELETE FROM %s WHERE key_Id IS NULL OR ref_Id IS NULL"""%self.tableLink)
				for key_Id,ref_Id in ids:
					self.dbCursor.execute("""DELETE FROM %s WHERE key_Id=%s AND ref_Id=%s"""\
											%(self.tableLink,self.param,self.param),\
											(key_Id,ref_Id))
			self.dbConnection.commit()
		# Checking for Modif of non-existent references
		self.dbCursor.execute("""SELECT ref_Id FROM %s LEFT JOIN %s \
								ON ref_Id=Id WHERE Id IS NULL"""\
								%(self.tableModif,self.tableRef))
		ids = self.dbCursor.fetchone()
		if ids:
			error = True
			ret = wx.MessageBox(_("The database contains wrong links in table_modif.\nShould I delete them ?"),_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
			if ret == wx.YES:
				self.clean_tableModif()
		# Checking for File links to non-existent references
		self.dbCursor.execute("""SELECT ref_Id FROM %s LEFT JOIN %s \
								ON ref_Id=Id WHERE Id IS NULL"""\
								%(self.tableFile,self.tableRef))
		ids = self.dbCursor.fetchone()
		if ids:
			error = True
			ret = wx.MessageBox(_("The database contains Fulltext references pointing to non-existent references.\nShould I delete them ?"),_("Database error"),wx.YES_NO|wx.ICON_ERROR,self.parent)
			if ret == wx.YES:
				self.clean_tableFile()
		# No error
		if not error:
			wx.MessageBox(_("Your database is a valid bibus database."),_("Database check"), wx.OK | wx.ICON_INFORMATION, self.parent)
#

	def showError(self,message=''):
		dlg=wx.MessageDialog(self.parent, message, caption = _("Database Error"), style = wx.OK | wx.CENTRE, pos = wx.DefaultPosition)
		try:
			dlg.ShowModal()
		finally:
			dlg.Destroy()