00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021 #include <qsqldatabase.h>
00022 #include <qsqlcursor.h>
00023
00024 #include <kdebug.h>
00025 #include <kglobal.h>
00026 #include <klineedit.h>
00027 #include <klocale.h>
00028
00029 #include "resourcesql.h"
00030 #include "resourcesqlconfig.h"
00031
00032 using namespace KABC;
00033
00034 extern "C"
00035 {
00036 ResourceConfigWidget *config_widget( QWidget *parent ) {
00037 KGlobal::locale()->insertCatalogue("kabc_sql");
00038 return new ResourceSqlConfig( parent, "ResourceSqlConfig" );
00039 }
00040
00041 Resource *resource( AddressBook *ab, const KConfig *config ) {
00042 KGlobal::locale()->insertCatalogue("kabc_sql");
00043 return new ResourceSql( ab, config );
00044 }
00045 }
00046
00047 ResourceSql::ResourceSql( AddressBook *ab, const KConfig *config )
00048 : Resource( ab ), mDb( 0 )
00049 {
00050 QString user, password, db, host;
00051
00052 user = config->readEntry( "SqlUser" );
00053 password = cryptStr( config->readEntry( "SqlPassword " ) );
00054 db = config->readEntry( "SqlName" );
00055 host = config->readEntry( "SqlHost" );
00056
00057 init( user, password, db, host );
00058 }
00059
00060 ResourceSql::ResourceSql( AddressBook *ab, const QString &user,
00061 const QString &password, const QString &db, const QString &host )
00062 : Resource( ab ), mDb( 0 )
00063 {
00064 init( user, password, db, host );
00065 }
00066
00067 void ResourceSql::init( const QString &user, const QString &password,
00068 const QString &db, const QString &host )
00069 {
00070 mUser = user;
00071 mPassword = password;
00072 mDbName = db;
00073 mHost = host;
00074 }
00075
00076 Ticket *ResourceSql::requestSaveTicket()
00077 {
00078 if ( !addressBook() ) {
00079 kdDebug(5700) << "no addressbook" << endl;
00080 return 0;
00081 }
00082
00083 return createTicket( this );
00084 }
00085
00086 bool ResourceSql::open()
00087 {
00088 QStringList drivers = QSqlDatabase::drivers();
00089 for ( QStringList::Iterator it = drivers.begin(); it != drivers.end(); ++it ) {
00090 kdDebug(5700) << "Driver: " << (*it) << endl;
00091 }
00092
00093 mDb = QSqlDatabase::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 QSqlQuery 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 QString 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
00153 {
00154 QSqlQuery 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
00162 {
00163 QSqlQuery 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
00171 {
00172 QSqlQuery 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
00192 {
00193 QSqlQuery categoriesQuery( "select category from kaddressbook_categories "
00194 "where addressId = '" + addrId + "'" );
00195 while ( categoriesQuery.next() )
00196 addr.insertCategory( categoriesQuery.value( 0 ).toString() );
00197 }
00198
00199
00200 {
00201 QSqlQuery 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
00218 QSqlQuery query( "select addressId from kaddressbook_main_" + mUser );
00219
00220 while ( query.next() ) {
00221 QString addrId = query.value( 0 ).toString();
00222 QSqlQuery 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
00234 AddressBook::Iterator it;
00235 for ( it = addressBook()->begin(); it != addressBook()->end(); ++it ) {
00236 if ( (*it).resource() != this && (*it).resource() != 0 )
00237 continue;
00238
00239 QString 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 QString::number( (*it).timeZone().offset() ) + "','" +
00253 QString::number( (*it).geo().latitude() ) + "','" +
00254 QString::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
00266 {
00267 QStringList emails = (*it).emails();
00268 QStringList::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 QString::number(preferred) + "')");
00275 preferred = false;
00276 }
00277 }
00278
00279
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 QString::number( (*it).type() ) + "')");
00288 }
00289 }
00290
00291
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 QString::number( (*it).type() ) + "')");
00307 }
00308 }
00309
00310
00311 {
00312 QStringList categories = (*it).categories();
00313 QStringList::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
00321 {
00322 QStringList list = (*it).customs();
00323 QStringList::ConstIterator it;
00324 for( it = list.begin(); it != list.end(); ++it ) {
00325 int dashPos = (*it).find( '-' );
00326 int colonPos = (*it).find( ':' );
00327 QString app = (*it).left( dashPos );
00328 QString name = (*it).mid( dashPos + 1, colonPos - dashPos - 1 );
00329 QString 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 QString ResourceSql::identifier() const
00341 {
00342 return mHost + "_" + mDbName;
00343 }