# 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.
#
# Part of the Wizard code has been copied from the example "Simple Wizard" included with
# wxPython

from sqlite3 import Error,IntegrityError,connect,version_info,paramstyle					# python >= 2.5
#
import getpass, os
import dbBibBase
dbBibBase.SUBSTR = 'SUBSTR'
#
import wx
import wx.wizard
import BIB

# Database fields definition
# tables are now defines in files
# pysqlite1_tables.py ; pysqlite2_tables.py
# depending on python-sqlite version
TMPTABLE_REF=""
TABLE_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 triplet must be unique
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
TABLE_KEY=""
# This table if for queries storage
TABLE_QUERY=""
# table containing modifications
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
		self.filen = BIB.SQLiteFile
		# the conversion from unicode to str below is because of a bug in python-sqlite
		# that does not accept unicode filenames
		# see http://trac.edgewall.org/changeset/6223
		if type(self.filen) == unicode: self.filen = self.filen.encode('utf-8')
		self.user = BIB.SQLiteUSER
		#
		try:
			global TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			if version_info < (2,0,0):
				self.dbConnection = connect(self.filen,encoding=BIB.ENCODING)
				from pysqlite1_tables import TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			else:
				self.dbConnection = connect(self.filen )
				from pysqlite2_tables import TABLE_REF, TMPTABLE_REF, TABLE_LINK, TABLE_KEY, TABLE_QUERY, TABLE_MODIF, TABLE_FILE
			self.dbCursor=self.dbConnection.cursor()
			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.SQLiteFile):
		self.filen=db
		self.user = BIB.SQLiteUSER
		try:
			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.dbConnection.commit()
		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("select name from sqlite_master where type='table'")
			return [table[0] for table in self.dbCursor.fetchall()]
		except Error,errorType:
			self.showError(`errorType.args`)
			return []

	def getFields(self,table):
		"""Return a list of field names"""
		try:
			self.table=table
			self.dbCursor.execute('pragma table_info(%s)' % (self.table))
			self.dbConnection.commit()
			return map(lambda x: x[1],self.dbCursor.fetchall())
		except Error,errorType:
			self.showError(`errorType.args`)

	def get_insert_id(self):
		"""Return the last inserted auto_incremented id"""
		try:
			return self.dbCursor.lastrowid
		except AttributeError:				# for pysqlite-1.1 ?
			return self.dbConnection.db.sqlite_last_insert_rowid()

	def getDbInfo(self,key=''):
		"""Return a tuple that represent the database connection. Here  (file,dbTable).
		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.filen, table)

	def getDbDescription(self):
		"""Return a string that describes the connection in order to put it in the Frame title"""
		return u"%s using SQLite" % self.filen

	def createDatabase(self,db=BIB.SQLiteFile,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 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.dbConnection.commit()
			self.filen=db
			self.tableRef=tableref
			self.tableKey=tablekey
			self.tableLink=tablelink
			self.tableQuery = tablequery
			self.tableModif = tablemodif
			self.tableFile = tablefile
		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 = ('column Identifier is not unique',)"""
		return e.args == ('column Identifier is not unique',)

	def getGrants(self):
		"""Return a string
		'rw' if we can read and write to the database file
		'rk' if we can just read the database # r=read db ; k = read keytree
		"""
		if os.access( BIB.SQLiteFile, os.R_OK | os.W_OK ):
			return 'rw'
		else:
			return 'rk'
			
# Database cleanup	
	def clean_tableModif(self):
		self.dbCursor.execute("""DELETE FROM %s WHERE ref_Id IN \
									(SELECT ref_Id FROM %s LEFT JOIN %s ON ref_Id=Id WHERE Id IS NULL)"""
									%(self.tableModif,self.tableModif,self.tableRef))
		self.dbConnection.commit()

	def clean_tableFile(self):
		self.dbCursor.execute("""DELETE FROM %s WHERE ref_Id IN \
									(SELECT ref_Id FROM %s LEFT JOIN %s ON ref_Id=Id WHERE Id IS NULL)"""
									%(self.tableFile,self.tableFile,self.tableRef))
		self.dbConnection.commit()

#----------------------------------------------------------------------
class TitledPage(wx.wizard.WizardPageSimple):
	def __init__(self, parent, title):
		wx.wizard.WizardPageSimple.__init__(self, parent)
		self.sizer = self.__makePageTitle(title)

	def __makePageTitle(self, title):
		sizer = wx.BoxSizer(wx.VERTICAL)
		self.SetSizer(sizer)
		title = wx.StaticText(self, -1, title)
		title.SetFont(wx.Font(18, wx.SWISS, wx.NORMAL, wx.BOLD))
		sizer.Add(title, 0, wx.ALIGN_CENTRE|wx.ALL, 5)
		sizer.Add(wx.StaticLine(self, -1), 0, wx.EXPAND|wx.ALL, 5)
		return sizer

#----------------------------------------------------------------------

class dbWizard(wx.wizard.Wizard):
	"""This is the Frame that open when Menu connect is selected"""
	def __init__(self,parent):
	# Create the wizard and the pages
		self.ID_wiz = wx.NewId()
		self.parent = parent
		self.db = None
		# save original BIB values
		self.Original_SQLiteUSER = BIB.SQLiteUSER
		self.Original_SQLiteFile = BIB.SQLiteFile
		#
		wx.wizard.Wizard.__init__(self,parent, self.ID_wiz, _("Database choice"))
		self.Show(1)
		page1 = TitledPage(self, _("%s connection parameters")%BIB.DB_TYPE)
		self.page1 = page1
		# page1 Layout
		page1.sizer.Add(wx.StaticText(page1, -1, u""))
		page1.sizer1 = wx.FlexGridSizer(2,2,5,5)
		page1.sizer1.AddGrowableCol(1)
		self.user=wx.TextCtrl(page1,-1,BIB.SQLiteUSER)
		self.filen=wx.TextCtrl(page1,-1,BIB.SQLiteFile)
		self.db_file = wx.Button(page1,-1,_("File name ..."))
		page1.sizer1.Add(wx.StaticText(page1, -1, _("UserName")),wx.ALIGN_CENTER_VERTICAL,wx.ALIGN_RIGHT)
		page1.sizer1.Add(self.user, 0, wx.EXPAND)
		page1.sizer1.Add(self.db_file,0,wx.ALIGN_RIGHT)
		page1.sizer1.Add(self.filen, 0, wx.EXPAND)
		page1.sizer.Add(page1.sizer1, 0, wx.EXPAND)
		self.FitToPage(page1)
		self.Layout()

		wx.wizard.EVT_WIZARD_CANCEL(self, self.ID_wiz, self.OnWizCancel)
		wx.EVT_BUTTON(self, self.db_file.GetId(), self.OnFileChoice)
		wx.wizard.EVT_WIZARD_FINISHED(self, self.ID_wiz, self.OnWizFinished)


	def OnWizFinished(self, evt):
		# we are leaving the wizard by clickink the Finish button
		# we first check that the connection is working then we return the dbBib connection
		# before leaving
		BIB.SQLiteFile = self.filen.GetValue()
		BIB.SQLiteUSER = self.user.GetValue()
		### print 'File check...'
		# TO DO: not only check existence but also check if proper Bibus database
		if not os.path.exists(BIB.SQLiteFile):
			# File does not exist...		
			# we restore original values
			BIB.SQLiteUSER = self.Original_SQLiteUSER
			BIB.SQLiteFile = self.Original_SQLiteFile
			# rollback UI... 
			self.filen.SetValue(self.Original_SQLiteFile)
			self.user.SetValue(self.Original_SQLiteUSER)
			# no db selected
			self.db = None
			return
		# ok, file exists, but TO DO: check if it is a SQLite database (in general)
		# the following part pretends already that it is a SQLite database that is open
		# it just checks if the necessary fields are there
		try:
			if self.db != None: self.db.close()
			self.db = dbBib(self.parent)
			self.db.selectDatabase(BIB.SQLiteFile)
			self.db.tableRef = BIB.DB_TABLE_REF
			self.db.tableKey = BIB.DB_TABLE_KEY
			self.db.tableLink = BIB.DB_TABLE_LINK
			self.db.tableQuery = BIB.DB_TABLE_QUERY
			self.db.tableModif = BIB.DB_TABLE_MODIF
			self.db.tableFile = BIB.DB_TABLE_FILE
			self.db.check_db()	# pop an error if some fields are missing
		except (AttributeError, TypeError):
			# ... some fields are missing... the file is not a Bibus database
			# we restore original values
			BIB.SQLiteUSER = self.Original_SQLiteUSER
			BIB.SQLiteFile = self.Original_SQLiteFile
			# rollback UI... 
			self.filen.SetValue(self.Original_SQLiteFile)
			self.user.SetValue(self.Original_SQLiteUSER)
			# no db selected
			self.db = None

	def OnWizCancel(self, evt):
		# we restore original values
		BIB.SQLiteUSER = self.Original_SQLiteUSER
		BIB.SQLiteFile = self.Original_SQLiteFile
		# no db selected
		self.db = None

	def OnFileChoice(self,event):
		filename = wx.FileSelector(_('SQlite database file'))
		self.filen.SetValue(filename)

	def getDbSelected(self):
		"""Return db. None if no selected db. Clear password for security"""
		BIB.SQLiteUSER = self.user.GetValue()
		BIB.SQLiteFile = self.filen.GetValue()
		if self.db != None and BIB.DB_STARTUP == 0: # we store the current database
			BIB.CONFIG.writeConfig(True)
		return self.db