News

October 2009 : a new release (bibus 1.5.0)

  • OOo3.1 compatibility
  • Infinite number of file/http links
  • Clever capitalization (lord of war => Lord of War ...)
  • Better handling of journal abbreviations
  • Import/Export filters can be added easily (plugin)
  • journal abbreviations and shortcuts can be easily edited (simple csv file that can be edited in OOo/excel or a text editor)
  • Lot of bug fixes
  • Requires python2.5 (not a problem anymore under Windows since OOo3.1 includes 2.6)
  • Requires wxpython-2.8

Considering a column as numeric to sort the database

From Bibus

Jump to: navigation, search

It is sometimes convenient to have a field of numeric in a library. For example, in my library the field "Custom2" contains an integer corresponding to the article position in my desk... Unfortunately bibus fields are all considered as text (except "Id" and "Year" I think) so that when I sort my library by "Custom2" I get:

1;10;2;3;4;5;6;7;8;9

instead of 1;2;3;4;5;6;7;8;9;10

Till bibus 1.3

A solution consists in modifying the function "getRefKey" and "__getAllRef" in dbBibBase.py:

 def getRefKey(self,key_id,collist=BIB.LIST_DISPLAY,order=BIB.LIST_ORDER,how=BIB.LIST_HOW):
   """Return the references corresponding to the key with key_id"""
   try:
     if order=="Custom2":
       #sort as a numeric
       tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s ORDER BY %s+0 %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""" %('Id,' + ','.join(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 __getAllRef(self,table,collist,order,how=BIB.LIST_HOW):
   try:
     if order=="Custom2":
       #sort as a numeric
       self.dbCursor.execute("""SELECT %s FROM %s ORDER BY %s +0 %s"""% (','.join(('Id',)+collist),table,order,how))  # column Id added for SetData of refList
     else:
       self.dbCursor.execute("""SELECT %s FROM %s ORDER BY %s %s"""% (','.join(('Id',)+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


Since bibus 1.4

you have to modify functions "getRefKey" and "getAllRef" (not "__getAllRef") in dbBibBase.py as follows (to sort Custom2 as a numeric field):


	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:
				if order=="Custom2":
					tmpstr = """SELECT %s FROM %s as t1,%s as t2 WHERE t1.%s=t2.%s AND t2.%s=%s ORDER BY %s+0 %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)
				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 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:
					# JOIN could be better for optimization but it is very slow with python-SQLite
					# Subqueries are much faster. I will test later with new python-sqlite versions
					#self.dbCursor.execute("""SELECT DISTINCT %s FROM %s JOIN %s AS t1 ON Id=t1.ref_Id \
					#					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,))
					if order=="Custom2":
						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+0 %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']+["%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 DISTINCT %s FROM %s JOIN %s AS t1 ON Id=t1.ref_Id \
					#					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,))
					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
Personal tools