modules.contacts

48 """ 49 The implementation of the interface L{contacts.AbstractContactSynchronizationModule} for SQLite persistence backend 50 """

51 - def __init__( self, modulesString, config, configsection, folder, verbose=False, soft=False):

52 """ 53 Constructor 54 55 Super class constructor (L{contacts.AbstractContactSynchronizationModule.__init__}) is called. 56 Local variables are initialized. 57 The settings from the configuration file are loaded. 58 """ 59 contacts.AbstractContactSynchronizationModule.__init__(self, verbose, soft, modulesString, config, configsection, "SQLite") 60 self._dbpath = config.get(configsection,'database') 61 pisiprogress.getCallback().verbose('contact sqlite module loaded using file %s' % (self._dbpath)) 62 self._idMappingInternalGlobal = {} 63 self._idMappingGlobalInternal = {}

64

65 - def load(self):

66 """ 67 Loads all attributes for all contact entries from the SQLite database 68 69 For each entry a new L{contacts.contacts.Contact} instance is created and stored in the instance dictionary L{contacts.AbstractContactSynchronizationModule._allContacts}. 70 Several requests to the database are executed in order to get all the detailled information about phone numbers and addresses, etc. as well. 71 """ 72 pisiprogress.getCallback().verbose("SQLite: Loading") 73 database = sqlite3.connect(self._dbpath, isolation_level=None) 74 db_call = "select recid, firstname, middlename, lastname, company, title, department from contacts" 75 contactEntries = database.execute(db_call).fetchall() 76 77 pisiprogress.getCallback().progress.setProgress(20) # we guess that the actual query took up 20 % of the time - the remaining 80 % are taken by parsing the content ... 78 pisiprogress.getCallback().update('Loading') 79 i=0 80 for contactEntry in contactEntries: 81 atts = {} 82 recid = contactEntry[0] 83 atts['firstname'] = contactEntry[1] 84 atts['middlename'] = contactEntry[2] 85 atts['lastname'] = contactEntry[3] 86 atts['businessOrganisation'] = contactEntry[4] 87 atts['title'] = contactEntry[5] 88 atts['businessDepartment'] = contactEntry[6] 89 90 # fetch detail information as well 91 db_call = "select phone_number, phone_type from contactphonenumbers where recid = %s" %(recid) 92 phoneEntries = database.execute(db_call).fetchall() 93 for phoneEntry in phoneEntries: 94 type = int(phoneEntry[1]) 95 if type == QTOPIADB_PHONETYPE_MOBILE: 96 atts['mobile'] = phoneEntry[0] 97 elif type == QTOPIADB_PHONETYPE_HOME: 98 atts['phone'] = phoneEntry[0] 99 elif type == QTOPIADB_PHONETYPE_OFFICE: 100 atts['officePhone'] = phoneEntry[0] 101 elif type == QTOPIADB_PHONETYPE_FAX: 102 atts['fax'] = phoneEntry[0] 103 104 db_call = "select addr from emailaddresses where recid = %s" %(recid) 105 emailEntries = database.execute(db_call).fetchall() 106 for emailEntry in emailEntries: 107 atts['email'] = emailEntry[0] 108 109 db_call = "select street, city, state, zip, country, addresstype from contactaddresses where recid = %s" %(recid) 110 addressEntries = database.execute(db_call).fetchall() 111 for addressEntry in addressEntries: 112 type = int(addressEntry[5]) 113 if type == QTOPIADB_ADDRESSTYPE_PRIVATE: 114 atts['homeStreet'] = addressEntry[0] 115 atts['homeCity'] = addressEntry[1] 116 atts['homeState'] = addressEntry[2] 117 atts['homePostalCode'] = addressEntry[3] 118 atts['homeCountry'] = addressEntry[4] 119 if type == QTOPIADB_ADDRESSTYPE_BUSINESS: 120 atts['businessStreet'] = addressEntry[0] 121 atts['businessCity'] = addressEntry[1] 122 atts['businessState'] = addressEntry[2] 123 atts['businessPostalCode'] = addressEntry[3] 124 atts['businessCountry'] = addressEntry[4] 125 126 id = contacts.assembleID(atts) 127 c = contacts.Contact(id, atts) 128 self._allContacts[id] = c 129 130 self._idMappingGlobalInternal[id] = recid 131 self._idMappingInternalGlobal[recid] = id 132 i+=1 133 pisiprogress.getCallback().progress.setProgress(20 + ((i*80) / len(contactEntries))) 134 pisiprogress.getCallback().update('Loading') 135 database.close()

136

137 - def _quoteString(self, contact, st):

138 """ 139 Supporting function to get around problems with integrating 'None' values and KeyErrors if an attribute is not available 140 """ 141 try: 142 val = contact.attributes[st].replace("'", "''") # this is SQL standard; one apostrophe has to be quoted by another one 143 return "'" +val + "'" 144 except TypeError: 145 return "''" 146 except KeyError: 147 return "''"

148

149 - def _saveOperationAdd(self, id, database):

150 """ 151 Save all new entries to SQLite database 152 153 The information from all the attributes in the Contact entry are stored 1st in the major table and afterwards in all the related tables (addresses, phone numbers, etc.). Thereby the foreign keys are taken care of. 154 """ 155 contact = self.getContact(id) 156 # todo: What is this context thing about? We just put 1 here for now - all entries manually inserted in the phone have 1 as well. 157 st = "insert into contacts (firstname, middlename, lastname, default_email, default_phone, context, company, title, department) values (%s, %s, %s, %s, %s, 1, %s, %s, %s)" %( 158 self._quoteString(contact, 'firstname'), 159 self._quoteString(contact, 'middlename'), 160 self._quoteString(contact, 'lastname'), 161 self._quoteString(contact, 'email'), 162 self._quoteString(contact, 'mobile'), 163 self._quoteString(contact, 'businessOrganisation'), 164 self._quoteString(contact, 'title'), 165 self._quoteString(contact, 'businessDepartment')) 166 database.execute(st) 167 db_call = "select recid from contacts where firstname=%s and lastname=%s" %( 168 self._quoteString(contact, 'firstname'), 169 self._quoteString(contact, 'lastname') ) 170 recidEntry = database.execute(db_call).fetchall()[0] # here must only be a single entry (assuming that we have each combination of firstname and surname only once 171 recid = recidEntry[0] 172 173 try: 174 if contact.attributes['mobile'] != '' and contact.attributes['mobile'] != None: 175 st = "insert into contactphonenumbers (phone_number, recid, phone_type) values (%s, %d, %d)" %( 176 self._quoteString(contact, 'mobile'), 177 recid, 178 QTOPIADB_PHONETYPE_MOBILE) 179 database.execute(st) 180 except KeyError: 181 pass # that's fine - we haven't got this phone number here 182 183 try: 184 if contact.attributes['email'] != '' and contact.attributes['email'] != None: 185 st = "insert into emailaddresses (recid, addr) values (%d, %s)" %( 186 recid, 187 self._quoteString(contact, 'email')) 188 database.execute(st) 189 except KeyError: 190 pass # that's fine - we haven't got this phone number here 191 192 try: 193 if contact.attributes['phone'] != '' and contact.attributes['phone'] != None: 194 st = "insert into contactphonenumbers (phone_number, recid, phone_type) values (%s, %d, %d)" %( 195 self._quoteString(contact, 'phone'), 196 recid, 197 QTOPIADB_PHONETYPE_HOME) 198 database.execute(st) 199 except KeyError: 200 pass # that's fine - we haven't got this phone number here 201 202 try: 203 if contact.attributes['officePhone'] != '' and contact.attributes['officePhone'] != None: 204 st = "insert into contactphonenumbers (phone_number, recid, phone_type) values (%s, %d, %d)" %( 205 self._quoteString(contact, 'officePhone'), 206 recid, 207 QTOPIADB_PHONETYPE_OFFICE) 208 database.execute(st) 209 except KeyError: 210 pass # that's fine - we haven't got this phone number here 211 212 try: 213 if contact.attributes['fax'] != '' and contact.attributes['fax'] != None: 214 st = "insert into contactphonenumbers (phone_number, recid, phone_type) values (%s, %d, %d)" %( 215 self._quoteString(contact, 'fax'), 216 recid, 217 QTOPIADB_PHONETYPE_FAX) 218 database.execute(st) 219 except KeyError: 220 pass # that's fine - we haven't got this phone number here 221 222 # we assume, if somebody supplies information about an address, first the city (locality) name would be provided 223 # addresstype is set to 1 - the type for private addresses 224 try: 225 if contact.attributes['homeCity'] != '' and contact.attributes['homeCity'] != None: 226 st = "insert into contactaddresses (street, city, state, zip, country, addresstype, recid) values (%s, %s, %s, %s, %s, %d, %d)" %( 227 self._quoteString(contact, 'homeStreet'), 228 self._quoteString(contact, 'homeCity'), 229 self._quoteString(contact, 'homeState'), 230 self._quoteString(contact, 'homePostalCode'), 231 self._quoteString(contact, 'homeCountry'), 232 QTOPIADB_ADDRESSTYPE_PRIVATE, 233 recid) 234 database.execute(st) 235 except KeyError: 236 pass # that's fine - we haven't got this address here 237 238 # addresstype is set to 2 - the type for business addresses 239 try: 240 if contact.attributes['businessCity'] != '' and contact.attributes['businessCity'] != None: 241 st = "insert into contactaddresses (street, city, state, zip, country, addresstype, recid) values (%s, %s, %s, %s, %s, %d, %d)" %( 242 self._quoteString(contact, 'businessStreet'), 243 self._quoteString(contact, 'businessCity'), 244 self._quoteString(contact, 'businessState'), 245 self._quoteString(contact, 'businessPostalCode'), 246 self._quoteString(contact, 'businessCountry'), 247 QTOPIADB_ADDRESSTYPE_BUSINESS, 248 recid) 249 database.execute(st) 250 except KeyError: 251 pass # that's fine - we haven't got this address here

252

253 - def _saveOperationDelete(self, id, database):

254 """ 255 Finally deletes the contact entry identified by its ID in the database 256 257 Besides the deletion in the major table all traces in related tables (addresses phone numbers, etc.) are erased as well. 258 """ 259 recid = self._idMappingGlobalInternal[id] 260 st = "delete from contacts where recid=%s" %(recid) 261 database.execute(st) 262 st = "delete from emailaddresses where recid=%s" %(recid) 263 database.execute(st) 264 st = "delete from contactphonenumbers where recid=%s" %(recid) 265 database.execute(st) 266 st = "delete from contactaddresses where recid=%s" %(recid) 267 database.execute(st)

268

269 - def _saveOperationModify(self, id, database):

270 """ 271 Applies changes from an entry to the SQLite database 272 273 In order to keep things simple the old value is simply erased using the method L{_saveOperationDelete} and a new one is inserted (L{_saveOperationAdd}). 274 """ 275 self._saveOperationDelete(id, database) 276 self._saveOperationAdd(id, database)

277

278 - def saveModifications(self):

279 """ 280 Save whatever changes have come by 281 282 The history of actions for this data source is iterated. For each item in there the corresponding action is carried out on the item in question. 283 This function is just a dispatcher to one of the three functions L{_saveOperationAdd}, L{_saveOperationDelete} or L{_saveOperationModify}. 284 """ 285 pisiprogress.getCallback().verbose("SQLite module: I apply %d changes now" %(len(self._history))) 286 database = sqlite3.connect(self._dbpath, isolation_level=None) 287 #c = database.cursor() 288 i=0 289 for listItem in self._history: 290 action = listItem[0] 291 id = listItem[1] 292 if action == ACTIONID_ADD: 293 pisiprogress.getCallback().verbose( "\t\t<sqlite> adding %s" %(id)) 294 self._saveOperationAdd(id, database) 295 elif action == ACTIONID_DELETE: 296 pisiprogress.getCallback().verbose("\t\t<sqlite> deleting %s" %(id)) 297 self._saveOperationDelete(id, database) 298 elif action == ACTIONID_MODIFY: 299 pisiprogress.getCallback().verbose("\t\t<sqlite> replacing %s" %(id)) 300 self._saveOperationModify(id, database) 301 i+=1 302 pisiprogress.getCallback().progress.setProgress(i * 90 / len(self._history)) 303 pisiprogress.getCallback().update('Storing') 304 database.commit() 305 database.close() 306 pisiprogress.getCallback().progress.setProgress(100) 307 pisiprogress.getCallback().update('Storing')

Source Code for Module modules.contacts_sqlite3