resourcesql.cpp
00001 /* 00002 This file is part of libtdeabc. 00003 Copyright (c) 2002 Tobias Koenig <tokoe@kde.org> 00004 00005 This library is free software; you can redistribute it and/or 00006 modify it under the terms of the GNU Library General Public 00007 License as published by the Free Software Foundation; either 00008 version 2 of the License, or (at your option) any later version. 00009 00010 This library is distributed in the hope that it will be useful, 00011 but WITHOUT ANY WARRANTY; without even the implied warranty of 00012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 00013 Library General Public License for more details. 00014 00015 You should have received a copy of the GNU Library General Public License 00016 along with this library; see the file COPYING.LIB. If not, write to 00017 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 00018 Boston, MA 02110-1301, USA. 00019 */ 00020 00021 #include <tqsqldatabase.h> 00022 #include <tqsqlcursor.h> 00023 00024 #include <kdebug.h> 00025 #include <tdeglobal.h> 00026 #include <klineedit.h> 00027 #include <tdelocale.h> 00028 00029 #include "resourcesql.h" 00030 #include "resourcesqlconfig.h" 00031 00032 using namespace TDEABC; 00033 00034 extern "C" 00035 { 00036 KDE_EXPORT void *init_tdeabc_sql() 00037 { 00038 return new KRES::PluginFactory<ResourceSql,ResourceSqlConfig>(); 00039 } 00040 } 00041 00042 ResourceSql::ResourceSql( AddressBook *ab, const TDEConfig *config ) 00043 : Resource( ab ), mDb( 0 ) 00044 { 00045 TQString user, password, db, host; 00046 00047 user = config->readEntry( "SqlUser" ); 00048 password = cryptStr( config->readEntry( "SqlPassword " ) ); 00049 db = config->readEntry( "SqlName" ); 00050 host = config->readEntry( "SqlHost" ); 00051 00052 init( user, password, db, host ); 00053 } 00054 00055 ResourceSql::ResourceSql( AddressBook *ab, const TQString &user, 00056 const TQString &password, const TQString &db, const TQString &host ) 00057 : Resource( ab ), mDb( 0 ) 00058 { 00059 init( user, password, db, host ); 00060 } 00061 00062 void ResourceSql::init( const TQString &user, const TQString &password, 00063 const TQString &db, const TQString &host ) 00064 { 00065 mUser = user; 00066 mPassword = password; 00067 mDbName = db; 00068 mHost = host; 00069 } 00070 00071 Ticket *ResourceSql::requestSaveTicket() 00072 { 00073 if ( !addressBook() ) { 00074 kdDebug(5700) << "no addressbook" << endl; 00075 return 0; 00076 } 00077 00078 return createTicket( this ); 00079 } 00080 00081 void ResourceSql::releaseSaveTicket( Ticket *ticket ) 00082 { 00083 delete ticket; 00084 } 00085 00086 bool ResourceSql::open() 00087 { 00088 TQStringList drivers = TQSqlDatabase::drivers(); 00089 for ( TQStringList::Iterator it = drivers.begin(); it != drivers.end(); ++it ) { 00090 kdDebug(5700) << "Driver: " << (*it) << endl; 00091 } 00092 00093 mDb = TQSqlDatabase::addDatabase( "QMYSQL3" ); 00094 00095 if ( !mDb ) { 00096 kdDebug(5700) << "Error. Unable to connect to database." << endl; 00097 return false; 00098 } 00099 00100 mDb->setDatabaseName( mDbName ); 00101 mDb->setUserName( mUser ); 00102 mDb->setPassword( mPassword ); 00103 mDb->setHostName( mHost ); 00104 00105 if ( !mDb->open() ) { 00106 kdDebug(5700) << "Error. Unable to open database '" << mDbName << "'." << endl; 00107 return false; 00108 } 00109 00110 return true; 00111 } 00112 00113 void ResourceSql::close() 00114 { 00115 mDb->close(); 00116 } 00117 00118 bool ResourceSql::load() 00119 { 00120 TQSqlQuery query( "select addressId, name, familyName, givenName, " 00121 "additionalName, prefix, suffix, nickname, birthday, " 00122 "mailer, timezone, geo_latitude, geo_longitude, title, " 00123 "role, organization, note, productId, revision, " 00124 "sortString, url from kaddressbook_main_" + mUser ); 00125 00126 while ( query.next() ) { 00127 TQString addrId = query.value(0).toString(); 00128 00129 Addressee addr; 00130 addr.setResource( this ); 00131 addr.setUid( addrId ); 00132 addr.setName( query.value(1).toString() ); 00133 addr.setFamilyName( query.value(2).toString() ); 00134 addr.setGivenName( query.value(3).toString() ); 00135 addr.setAdditionalName( query.value(4).toString() ); 00136 addr.setPrefix( query.value(5).toString() ); 00137 addr.setSuffix( query.value(6).toString() ); 00138 addr.setNickName( query.value(7).toString() ); 00139 addr.setBirthday( query.value(8).toDateTime() ); 00140 addr.setMailer( query.value(9).toString() ); 00141 addr.setTimeZone( TimeZone( query.value(10).toInt() ) ); 00142 addr.setGeo( Geo( query.value(11).toDouble(), query.value(12).toDouble() ) ); 00143 addr.setTitle( query.value(13).toString() ); 00144 addr.setRole( query.value(14).toString() ); 00145 addr.setOrganization( query.value(15).toString() ); 00146 addr.setNote( query.value(16).toString() ); 00147 addr.setProductId( query.value(17).toString() ); 00148 addr.setRevision( query.value(18).toDateTime() ); 00149 addr.setSortString( query.value(19).toString() ); 00150 addr.setUrl( query.value(20).toString() ); 00151 00152 // emails 00153 { 00154 TQSqlQuery emailsQuery( "select email, preferred from kaddressbook_emails " 00155 "where addressId = '" + addrId + "'" ); 00156 while ( emailsQuery.next() ) 00157 addr.insertEmail( emailsQuery.value( 0 ).toString(), 00158 emailsQuery.value( 1 ).toInt() ); 00159 } 00160 00161 // phones 00162 { 00163 TQSqlQuery phonesQuery( "select number, type from kaddressbook_phones " 00164 "where addressId = '" + addrId + "'" ); 00165 while ( phonesQuery.next() ) 00166 addr.insertPhoneNumber( PhoneNumber( phonesQuery.value( 0 ).toString(), 00167 phonesQuery.value( 1 ).toInt() ) ); 00168 } 00169 00170 // addresses 00171 { 00172 TQSqlQuery addressesQuery( "select postOfficeBox, extended, street, " 00173 "locality, region, postalCode, country, label, type " 00174 "from kaddressbook_addresses where addressId = '" + addrId + "'" ); 00175 while ( addressesQuery.next() ) { 00176 Address a; 00177 a.setPostOfficeBox( addressesQuery.value(0).toString() ); 00178 a.setExtended( addressesQuery.value(1).toString() ); 00179 a.setStreet( addressesQuery.value(2).toString() ); 00180 a.setLocality( addressesQuery.value(3).toString() ); 00181 a.setRegion( addressesQuery.value(4).toString() ); 00182 a.setPostalCode( addressesQuery.value(5).toString() ); 00183 a.setCountry( addressesQuery.value(6).toString() ); 00184 a.setLabel( addressesQuery.value(7).toString() ); 00185 a.setType( addressesQuery.value(8).toInt() ); 00186 00187 addr.insertAddress( a ); 00188 } 00189 } 00190 00191 // categories 00192 { 00193 TQSqlQuery categoriesQuery( "select category from kaddressbook_categories " 00194 "where addressId = '" + addrId + "'" ); 00195 while ( categoriesQuery.next() ) 00196 addr.insertCategory( categoriesQuery.value( 0 ).toString() ); 00197 } 00198 00199 // customs 00200 { 00201 TQSqlQuery customsQuery( "select app, name, value from kaddressbook_customs " 00202 "where addressId = '" + addrId + "'" ); 00203 while ( customsQuery.next() ) 00204 addr.insertCustom( customsQuery.value( 0 ).toString(), 00205 customsQuery.value( 1 ).toString(), 00206 customsQuery.value( 2 ).toString()); 00207 } 00208 00209 addressBook()->insertAddressee( addr ); 00210 } 00211 00212 return true; 00213 } 00214 00215 bool ResourceSql::save( Ticket * ) 00216 { 00217 // we have to delete all entries for this user and reinsert them 00218 TQSqlQuery query( "select addressId from kaddressbook_main_" + mUser ); 00219 00220 while ( query.next() ) { 00221 TQString addrId = query.value( 0 ).toString(); 00222 TQSqlQuery q; 00223 00224 q.exec( "DELETE FROM kaddressbook_emails WHERE addressId = '" + addrId + "'" ); 00225 q.exec( "DELETE FROM kaddressbook_phones WHERE addressId = '" + addrId + "'" ); 00226 q.exec( "DELETE FROM kaddressbook_addresses WHERE addressId = '" + addrId + "'" ); 00227 q.exec( "DELETE FROM kaddressbook_categories WHERE addressId = '" + addrId + "'" ); 00228 q.exec( "DELETE FROM kaddressbook_customs WHERE addressId = '" + addrId + "'" ); 00229 00230 q.exec( "DELETE FROM kaddressbook_main_" + mUser + " WHERE addressId = '" + addrId + "'" ); 00231 } 00232 00233 // let's start... 00234 AddressBook::Iterator it; 00235 for ( it = addressBook()->begin(); it != addressBook()->end(); ++it ) { 00236 if ( (*it).resource() != this && (*it).resource() != 0 ) // save only my and new entries 00237 continue; 00238 00239 TQString uid = (*it).uid(); 00240 00241 query.exec( "INSERT INTO kaddressbook_main_" + mUser + " VALUES ('" + 00242 (*it).uid() + "','" + 00243 (*it).name() + "','" + 00244 (*it).familyName() + "','" + 00245 (*it).givenName() + "','" + 00246 (*it).additionalName() + "','" + 00247 (*it).prefix() + "','" + 00248 (*it).suffix() + "','" + 00249 (*it).nickName() + "','" + 00250 (*it).birthday().toString( Qt::ISODate ) + "','" + 00251 (*it).mailer() + "','" + 00252 TQString::number( (*it).timeZone().offset() ) + "','" + 00253 TQString::number( (*it).geo().latitude() ) + "','" + 00254 TQString::number( (*it).geo().longitude() ) + "','" + 00255 (*it).title() + "','" + 00256 (*it).role() + "','" + 00257 (*it).organization() + "','" + 00258 (*it).note() + "','" + 00259 (*it).productId() + "','" + 00260 (*it).revision().toString( Qt::ISODate ) + "','" + 00261 (*it).sortString() + "','" + 00262 (*it).url().url() + "')" 00263 ); 00264 00265 // emails 00266 { 00267 TQStringList emails = (*it).emails(); 00268 TQStringList::ConstIterator it; 00269 bool preferred = true; 00270 for( it = emails.begin(); it != emails.end(); ++it ) { 00271 query.exec("INSERT INTO kaddressbook_emails VALUES ('" + 00272 uid + "','" + 00273 (*it) + "','" + 00274 TQString::number(preferred) + "')"); 00275 preferred = false; 00276 } 00277 } 00278 00279 // phonenumbers 00280 { 00281 PhoneNumber::List phoneNumberList = (*it).phoneNumbers(); 00282 PhoneNumber::List::ConstIterator it; 00283 for( it = phoneNumberList.begin(); it != phoneNumberList.end(); ++it ) { 00284 query.exec("INSERT INTO kaddressbook_phones VALUES ('" + 00285 uid + "','" + 00286 (*it).number() + "','" + 00287 TQString::number( (*it).type() ) + "')"); 00288 } 00289 } 00290 00291 // postal addresses 00292 { 00293 Address::List addressList = (*it).addresses(); 00294 Address::List::ConstIterator it; 00295 for( it = addressList.begin(); it != addressList.end(); ++it ) { 00296 query.exec("INSERT INTO kaddressbook_addresses VALUES ('" + 00297 uid + "','" + 00298 (*it).postOfficeBox() + "','" + 00299 (*it).extended() + "','" + 00300 (*it).street() + "','" + 00301 (*it).locality() + "','" + 00302 (*it).region() + "','" + 00303 (*it).postalCode() + "','" + 00304 (*it).country() + "','" + 00305 (*it).label() + "','" + 00306 TQString::number( (*it).type() ) + "')"); 00307 } 00308 } 00309 00310 // categories 00311 { 00312 TQStringList categories = (*it).categories(); 00313 TQStringList::ConstIterator it; 00314 for( it = categories.begin(); it != categories.end(); ++it ) 00315 query.exec("INSERT INTO kaddressbook_categories VALUES ('" + 00316 uid + "','" + 00317 (*it) + "')"); 00318 } 00319 00320 // customs 00321 { 00322 TQStringList list = (*it).customs(); 00323 TQStringList::ConstIterator it; 00324 for( it = list.begin(); it != list.end(); ++it ) { 00325 int dashPos = (*it).find( '-' ); 00326 int colonPos = (*it).find( ':' ); 00327 TQString app = (*it).left( dashPos ); 00328 TQString name = (*it).mid( dashPos + 1, colonPos - dashPos - 1 ); 00329 TQString value = (*it).right( (*it).length() - colonPos - 1 ); 00330 00331 query.exec("INSERT INTO kaddressbook_categories VALUES ('" + 00332 uid + "','" + app + "','" + name + "','" + value + "')"); 00333 } 00334 } 00335 } 00336 00337 return true; 00338 } 00339 00340 TQString ResourceSql::identifier() const 00341 { 00342 return mHost + "_" + mDbName; 00343 }