Hi List, sometimes there are doubled appointments in SOGo. I don't know how and why, but sometimes it still happens.
I've written a little script which checks a users tables for events which have the same uid or same begin/end date+time and the same title. Duplicates gets removed. Regards Michael ############################################################### #!/usr/bin/env python # coding: utf-8 # Python script to remove doubled events in SOGo # in PostgreSQL Databases only # Written by Michael Heide <m...@os-cillation.de> # os-cillation GmbH - http://www.os-cillation.de/ # Licensed via PSF, see http://docs.python.org/license.html # !!! this script might cause DAMAGE to your computer !!! # !!! it comes with absolutely NO WARRANTY !!! # !!! use it at your own risk !!! import sys import psycopg2 import datetime ############################################################ #Code from http://code.activestate.com/recipes/134892/ # #Licensed via PSF, see http://docs.python.org/license.html # ############################################################ class _Getch: """Gets a single character from standard input. Does not echo to the screen.""" def __init__(self): try: self.impl = _GetchWindows() except ImportError: self.impl = _GetchUnix() def __call__(self): return self.impl() class _GetchUnix: def __init__(self): import tty, sys def __call__(self): import sys, tty, termios fd = sys.stdin.fileno() old_settings = termios.tcgetattr(fd) try: tty.setraw(sys.stdin.fileno()) ch = sys.stdin.read(1) finally: termios.tcsetattr(fd, termios.TCSADRAIN, old_settings) return ch class _GetchWindows: def __init__(self): import msvcrt def __call__(self): import msvcrt return msvcrt.getch() ########################################################## # End activestate.com recipes # ########################################################## def usage(): print "usage... 1 or 2 parameter: c_path2 (Name) [and c_foldername (Folder)]" print "(remember: you have to use quotation marks if there are whitespaces in a single parameter)" print sys.exit() def deletefromtables(dry): notdeleted=0 deleted=0 for duplist in duplicates: firstdate=0 first=0 print for dup in duplist: cursor.execute("SELECT c_creationdate FROM %s WHERE c_name=%%s;" % (datatable), (dup,)) if cursor.rowcount==0: print "Warning: there is no c_name %s in %s where it is in %s." % (dup,datatable,quicktable) break if cursor.rowcount>1: print "Error: c_name (%s) no primary key in %s?" % (dup,datatable) print sys.exit() row = cursor.fetchone() if dry: print "Date %s: %s" % (datetime.datetime.fromtimestamp(row[0]).strftime('%Y-%m-%d %H:%M:%S'),dup) if row[0]<firstdate or firstdate==0: firstdate=row[0] first=dup for dup in duplist: if dup!=first: #print "Deleting c_name %s from %s" % (dup,datatable) #print "Deleting c_name %s from %s" % (dup,quicktable) if dry: print "DELETE FROM %s WHERE c_name=%%s" % (datatable) % (dup) print "DELETE FROM %s WHERE c_name=%%s" % (quicktable) % (dup) notdeleted=notdeleted+2; else: cursor.execute("DELETE FROM %s WHERE c_name=%%s" % (datatable), (dup,)) if cursor.rowcount==1: print "successfully deleted %s from %s" % (dup,datatable) deleted=deleted+1; else: print "Error while deleting %s from %s: %d rows affected" % (dup,datatable,cursor.rowcount) notdeleted=notdeleted+1; cursor.execute("DELETE FROM %s WHERE c_name=%%s" % (quicktable), (dup,)) if cursor.rowcount==1: print "successfully deleted %s from %s" % (dup,quicktable) deleted=deleted+1; else: print "Error while deleting %s from %s: %d rows affected" % (dup,quicktable,cursor.rowcount) notdeleted=notdeleted+1; else: if dry: print "Preserving first duplicate %s (c_name) in %s and %s" % (dup,datatable, quicktable) return (deleted, notdeleted) ############ MAIN ############# def main(): global cursor global duplicates global datatable global quicktable if len(sys.argv) != 2 and len(sys.argv) != 3: usage() getch = _Getch() ###### connect to the database and search tables conn = psycopg2.connect("dbname=sogo user=sogo password=sogo") cursor = conn.cursor() folder = "" row = [] if len(sys.argv) == 2: cursor.execute("SELECT c_location, c_quick_location, c_foldername FROM sogo_folder_info WHERE c_path2=%s and c_folder_type='Appointment';",(sys.argv[1],)) if cursor.rowcount > 1: print "Valid folder Names: " for row in cursor.fetchall(): print row[2] print "For this user you have to add a c_foldername parameter." print "(remember: you have to use quotation marks if there are whitespaces in a single parameter)" print sys.exit() else: row = cursor.fetchone() folder = row[2] else: folder = sys.argv[2] cursor.execute("SELECT c_location, c_quick_location, c_foldername FROM sogo_folder_info WHERE c_path2=%s and c_folder_type='Appointment' and c_foldername=%s;",(sys.argv[1],folder)) row = cursor.fetchone() folder = row[2] if cursor.rowcount == 0: print "rowcount is 0: no such user?" print sys.exit() if cursor.rowcount != 1: print "rowcount is not 1: %d! Stopping..." % (cursor.rowcount) print sys.exit() print "\n\nDeleting duplicate entries for folder %s of user %s " % (folder, sys.argv[1]) #row=cursor.fetchone() if len(row) != 3: print "Result row is not a 3-tuple: %d! Stopping..." % (len(row)) print sys.exit() #print row #print "datatable:", row[0][row[0].rfind("/")+1:len(row[0])] datatable=row[0][row[0].rfind("/")+1:len(row[0])] quicktable=row[1][row[1].rfind("/")+1:len(row[1])] print "Folder: ", row[2] print "Datatable:", datatable, "/ Quicktable:", quicktable ###### we've connected to the db and found our tables print "Seems ok? (y/n) ", answer = getch() print "answer: ", answer #answer = sys.stdin.read(1) if answer != 'y': print "answer not \"y\" -> stopping on user request..." print sys.exit() duplicates = [] nondupcounter = 0 dupcounter = 0 ######## find duplicates via startdate, enddate and title cursor.execute("SELECT c_name, c_startdate, c_enddate, c_title FROM %s" % (quicktable)) rows = [] row = cursor.fetchone() while row != None: rows.append(row) row = cursor.fetchone() rows.sort(key=lambda col: col[1:4]) # if sortet all dups are side by side rowold = ("","","") lastdup = False # last was duplicate currdups = [] # current duplicates for row in rows: if row[1:4]==rowold[1:4]: # and row[0]!=rowlastdup: if len(currdups)==0: currdups.append(rowold[0]) currdups.append(row[0]) lastdup = True dupcounter = dupcounter+1 else: if lastdup: lastdup = False duplicates.append(currdups) currdups = [] nondupcounter = nondupcounter+1 rowold = row ######## find duplicates via c_uid cursor.execute("SELECT c_name, c_uid FROM %s" % (quicktable)) rows = [] row = cursor.fetchone() while row != None: rows.append(row) row = cursor.fetchone() rows.sort(key=lambda col: col[1]) # if sortet all dups are side by side rowold = ("","") lastdup = False # last was duplicate currdups = [] # current duplicates for row in rows: if row[1]==rowold[1]: # and row[0]!=rowlastdup: if len(currdups)==0: currdups.append(rowold[0]) currdups.append(row[0]) lastdup = True dupcounter = dupcounter+1 else: if lastdup: lastdup = False duplicates.append(currdups) currdups = [] nondupcounter = nondupcounter+1 rowold = row print duplicates print "rows: ", len(rows) print "duplicates: ", dupcounter print "added deduplicated: ", dupcounter print # at this pount we have a list (duplicates) of lists, where the inner lists # are c_names of duplicated events # (events where starttime, endtime and title are the same) result = deletefromtables(True) print print "Deleted: %d, not deleted: %d" % result print "Dry run done. Seems ok? Sure to really delete those entries? (y/n) ", answer = getch() print "answer: ", answer if answer != 'y': print "answer not \"y\" -> stopping on user request..." print sys.exit() result = deletefromtables(False) print print "Deleted: %d, not deleted: %d" % result print "Okay... all DELETE events are sent and the transaction state is pending." print " Are you sure you want to commit that? (y/n) ", answer = getch() print "answer: ", answer if answer != 'y': print "answer not \"y\" -> stopping on user request..." print sys.exit() conn.commit() print # Standard boilerplate to call the main() function to begin # the program. if __name__ == '__main__': main() ############################################################### -- ---------------------------------------------------------- os-cillation GmbH ---------------------------------------------------------- Softwareentwicklung, Auftragsprogrammierung, IT-Consulting iPhone/iPad, Linux, Windows, Web, Android, Embedded-System ---------------------------------------------------------- GF: Oliver Schweissgut, Dipl.-Ing. technische Informatik T: +49 271 31368-0 F: +49 271 31368-18 Hohler Weg 75, 57072 Siegen, Germany USt-ID: DE276692229, HRB 9478, AG Siegen ---------------------------------------------------------- -- users@sogo.nu https://inverse.ca/sogo/lists
