bibus 1.5.2-4 / usr / share / bibus / dbBibMySQL.py

# 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.
#
from MySQLdb import Error,IntegrityError,connect,version_info,paramstyle 	#
import MySQLdb.converters,dbBibBase
dbBibBase.SUBSTR = 'SUBSTRING'
#
import getpass, os.path
import BIB
#
from dbWizard import dbWizard

# Database fields definition
# tables are now defines in files
# mysql_tables.py ; mysql_41_tables.py
# depending on mysqlversion
TABLE_REF = ""
TMPTABLE_REF = ""
# This table maps ref to keys. A ref can be mapped to several keys.
# ref_Id = unique Id of bibref table
# key_id= unique Id of bibrefKey table
# the couple must be unique
# BIB_LINKS=('key_Id','ref_Id')
TABLE_LINK=""
# This table describes the tree of keys
# user = user name. To know to which user the key belongs to.
# key_id = unique Id of bibrefKey table
# parent = key_id of parent key. NULL = root
# key_name = key name
# BIB_KEYS=('user','key_Id','parent','key_name')
TABLE_KEY=""
# This table if for queries storage
# BIB_QUERY=('query_id','user','name','query')
TABLE_QUERY=""
# storage of modification
#  ref_Id int(10) unsigned NOT NULL default '0',
#  creator varchar(255) NOT NULL default '',
#  date double NOT NULL default 0,
#  user_modif varchar(255) NOT NULL default '',
#  date_modif double NOT NULL default 0,
#  UNIQUE ref_Id (ref_Id)
TABLE_MODIF = ""
# table with links to fulltext files (url)
TABLE_FILE = ""

class dbBib(dbBibBase.dbBib):
	def __init__(self,parent=None):
		dbBibBase.dbBib.__init__(self,parent,paramstyle)
		dbBibBase.Error = Error		# needed in dbBibBase for Error in db connection
		dbBibBase.IntegrityError = IntegrityError # needed in dbBibBase for Error in db connection
		if not BIB.CONFIG.userDefined('mysql') and BIB.USER == '':
			try:
				BIB.USER = getpass.getuser()	# We try to get a default value if not already defined.
			except:
				BIB.USER = ''
		self.user=BIB.USER
		self.passwd=BIB.PASSWORD
		self.db=BIB.DB_NAME
		self.host=BIB.HOST
		self.port=BIB.PORT
		self.unix_socket=BIB.SOCKET
		#
		try:
			# CPAO -> 23/05/05
			# unicode parameter changed for use_unicode and encoding not needed with MySQLdb 1.2.0
			if version_info[0:2]<(1,2):
				mc=MySQLdb.converters.conversions.copy()	# conversion dictionary
				self.dbConnection = connect(user=self.user,passwd=self.passwd,db=self.db,host=self.host,port=self.port,unix_socket=self.unix_socket,conv=mc,unicode=BIB.ENCODING)
				mc[unicode] = lambda x,d: MySQLdb.converters.string_literal(x.encode(BIB.ENCODING,BIB.ENC_ERRORS),d)	# encode unicode string in utf-8
				# conversion not needed anymore with MySQLdb 1.0.0
				if version_info[0]<1:
					mc[constants.FIELD_TYPE.BLOB] = mc[constants.FIELD_TYPE.CHAR]		# decode TEXT fields from utf-8 (default is only CHAR et VARCHAR)
					mc[constants.FIELD_TYPE.MEDIUM_BLOB] = mc[constants.FIELD_TYPE.CHAR]	# decode TEXT fields from utf-8 (default is only CHAR et VARCHAR)
					mc[constants.FIELD_TYPE.LONG_BLOB] = mc[constants.FIELD_TYPE.CHAR]	# decode TEXT fields from utf-8 (default is only CHAR et VARCHAR)
			else:
				self.dbConnection = connect(read_default_file=os.path.join(BIB.SOURCEDIR,'MySQL_Bibus.ini'),user=self.user,passwd=self.passwd,db=self.db,host=self.host,port=self.port,unix_socket=self.unix_socket,use_unicode=(BIB.ENCODING=='utf-8'))
			self.dbCursor=self.dbConnection.cursor()
			self.dbCursor.execute("SELECT VERSION()")
			self.mysqlversion = self.dbCursor.fetchone()[0].split('.')[:2]
			# we import the correct table definition depending on mysql version
			global TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			if self.mysqlversion >= ['4','1']:
				from mysql_41_tables import TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			else:
				from mysql_tables import TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			#
			dbBibBase.TABLE_REF, dbBibBase.TABLE_LINK, dbBibBase.TABLE_KEY, dbBibBase.TABLE_QUERY, dbBibBase.TABLE_MODIF, dbBibBase.TABLE_FILE = \
			TABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
		except Error,errorType:
			self.showError(`errorType.args`)

	def selectDatabase(self,db=BIB.DB_NAME):
		self.db=db
		try:
			self.dbCursor.execute("use %s"%db)
			if self.getGrants() == 'rw':	# we have the grants to temporary table creation
				self.dbCursor.execute("CREATE TEMPORARY TABLE %s %s" % (BIB.TMP_ONLINE,TMPTABLE_REF))
				self.dbCursor.execute("CREATE TEMPORARY TABLE %s %s" % (BIB.TMP_IMPORT,TMPTABLE_REF))
			self.host = self.dbConnection.get_host_info().split()[0].lower()
			self.passwd = None # for security clear the password when done
		except Error,errorType:
		    self.showError(`errorType.args`)

#
# MySQL specific funtions
#
#
	def getDatabases(self):
		try:
			self.dbCursor.execute('show databases')
			return self.dbCursor.fetchall()
		except Error,errorType:
		    self.showError(`errorType.args`)


	def selectTable(self,tableref=BIB.DB_TABLE_REF,tablekey=BIB.DB_TABLE_KEY,tablelink=BIB.DB_TABLE_LINK,tablequery=BIB.DB_TABLE_QUERY,tablemodif=BIB.DB_TABLE_MODIF,tablefile=BIB.DB_TABLE_FILE):
		self.tableRef = tableref
		self.tableKey = tablekey
		self.tableLink = tablelink
		self.tableQuery = tablequery
		self.tableModif = tablemodif
		self.tableFile = tablefile

	def getTables(self):
		try:
			self.dbCursor.execute('show tables')
			return [table[0] for table in self.dbCursor.fetchall()]
		except Error,errorType:
			self.showError(`errorType.args`)
			return []

	def getFields(self,table):
		try:
			self.table=table
			self.dbCursor.execute('describe %s' % (self.table))
			return map(lambda x: x[0],self.dbCursor.fetchall())
		except Error,errorType:
			self.showError(`errorType.args`)

	def get_insert_id(self):
		"""Return the last inserted auto_incremented id"""
		return self.dbCursor.lastrowid

	def getDbInfo(self,key=''):
		"""Return a tuple that represent the database connection. Here  (host,db,table).
		For another database it could be a file name or ... something that identify the database + connection"""
		if key == u'Online':
			table = BIB.TMP_ONLINE
		elif key == u'Import':
			table = BIB.TMP_IMPORT
		else:
			table = self.tableRef
		return (self.host, self.db, table)

	def getDbDescription(self):
		"""Return a string that describes the connection in order to put it in the Frame title"""
		return u"%s@%s using MySQL" % (self.db,self.host)


	def createDatabase(self,db=BIB.DB_NAME,tableref=BIB.DB_TABLE_REF,tablekey=BIB.DB_TABLE_KEY,tablelink=BIB.DB_TABLE_LINK,tablequery=BIB.DB_TABLE_QUERY,tablemodif=BIB.DB_TABLE_MODIF,tablefile=BIB.DB_TABLE_FILE):
		try:
			self.dbCursor.execute("""create database %s""" % (db))
			self.dbCursor.execute("""use %s""" % (db))
			self.dbCursor.execute("""create table %s %s""" % (tableref,TABLE_REF))
			self.dbCursor.execute("""create table %s %s""" % (tablekey,TABLE_KEY))
			self.dbCursor.execute("""create table %s %s""" % (tablelink,TABLE_LINK))
			self.dbCursor.execute("""create table %s %s""" % (tablequery,TABLE_QUERY))
			self.dbCursor.execute("""create table %s %s""" % (tablemodif,TABLE_MODIF))
			self.dbCursor.execute("""create table %s %s""" % (tablefile,TABLE_FILE))
			self.db=db
			self.tableRef=tableref
			self.tableKey=tablekey
			self.tableLink=tablelink
			self.tableQuery = tablequery
			self.tableModif = tablemodif
			self.tableFile = tablefile
			self.selectDatabase(self.db)
		except Error,errorType:
			#print `errorType.args`
			self.showError(`errorType.args`)

	def duplicateIdentifier(self,e):
		"""Return True if the error correspond to a duplicate Identifier
		e.args = (1062, "Duplicate entry 'Clement1991#8' for key 2") """
		try:
			return ( e.args[1].split()[:2] == ['Duplicate', 'entry'] ) and ( e.args[1].split()[3:] == ['for', 'key', '2'] )
		except:
			return False

	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
		"""
		ret=[]
		if self.mysqlversion >= ['4','1']:
			self.dbCursor.execute('SHOW GRANTS FOR current_user()')
		else:
			self.dbCursor.execute('SELECT current_user()')
			user = self.dbCursor.fetchone()[0]
			userp = user.split('@')
			user =  '\'' + userp[0] + '\'' + '@' + '\'' + userp[1] + '\''
			# getting grants and store them in a dico
			self.dbCursor.execute('SHOW GRANTS FOR %s' %user)
		grants = {}
		grant = self.dbCursor.fetchone()
		while grant:
			table = grant[0].split('ON')[1].split('TO')[0].strip()
			grants[table] = [p.strip() for p in  grant[0].split('ON')[0][5:].split(',')]
			grant = self.dbCursor.fetchone()
		# getting privileges for temporary tables
		tmp = []
		for key in ( '*.*', '`%s`.*'%BIB.DB_NAME ):
			try:
				tmp.extend( grants[key] )
			except KeyError:
				pass
		if ( ('CREATE TEMPORARY TABLES' in tmp) and ('INSERT' in tmp) and ('DELETE' in tmp) and ('SELECT' in tmp) ) or ('ALL' in tmp) or ('ALL PRIVILEGES' in tmp):
			ret.append(True)
		else:
			ret.append(False)
		# fusion of the grants at the global + db + table levels for table
		for table in (self.tableRef, self.tableKey, self.tableLink, self.tableQuery, self.tableModif, self.tableFile):
			tmp = []
			for key in '*.*','`%s`.*'%BIB.DB_NAME,'`%s`.`%s`'%(BIB.DB_NAME,table):
				try:
					tmp.extend( grants[key] )
				except KeyError:
					pass
			if ( ('INSERT' in tmp) and ('DELETE' in tmp) and ('SELECT' in tmp) and ('UPDATE' in tmp) ) or ('ALL' in tmp) or ('ALL PRIVILEGES' in tmp):
				ret.append('rw')
			elif ('SELECT' in tmp) or ('ALL' in tmp) or ('ALL PRIVILEGES' in tmp):
				ret.append('r')
			else:
				ret.append('')
		#
		if ret == [True,'rw','rw','rw','rw','rw','rw']:
			return 'rw'	# normal read-write user
		elif ret[1:] == ['r','rw','rw','rw','rw','rw']:
			return 'ro' # normal read-only user. Can create its own keytree but cannot add references in db
		elif ret[1:] == ['r','r','r','r','r','r']:
			return 'rk' # user able to read-only keytree. For collaborative work with a read-only user.
		else:
			return 'rr' # very restricted user. Can only see a flat list of references

# 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))
			# idem from table file
			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.tableFile,self.tableRef,self.tableLink))
			# Then the references
			self.dbCursor.execute("""DELETE %s FROM %s LEFT JOIN %s ON Id=ref_Id WHERE ref_Id IS NULL"""\
									%(self.tableRef,self.tableRef,self.tableLink))
			self.dbConnection.commit()
		except Error,errorType:
			self.showError('dbBib.emptyTrash' + `errorType.args`)
			
	def clean_tableModif(self):
		self.dbCursor.execute("""DELETE %s FROM %s LEFT JOIN %s ON ref_Id=Id WHERE Id IS NULL"""
										%(self.tableModif,self.tableModif,self.tableRef))
					
	def clean_tableFile(self):
		self.dbCursor.execute("""DELETE %s FROM %s LEFT JOIN %s ON ref_Id=Id WHERE Id IS NULL"""
										%(self.tableFile,self.tableFile,self.tableRef))