21 #include <tqsqldatabase.h>
22 #include <tqsqlcursor.h>
26 #include <klineedit.h>
29 #include "resourcesql.h"
30 #include "resourcesqlconfig.h"
36 KDE_EXPORT
void *init_kabc_sql()
38 return new KRES::PluginFactory<ResourceSql,ResourceSqlConfig>();
43 : Resource( ab ), mDb( 0 )
45 TQString user, password, db, host;
48 password = cryptStr( config->
readEntry(
"SqlPassword " ) );
52 init( user, password, db, host );
55 ResourceSql::ResourceSql(
AddressBook *ab,
const TQString &user,
56 const TQString &password,
const TQString &db,
const TQString &host )
57 : Resource( ab ), mDb( 0 )
59 init( user, password, db, host );
62 void ResourceSql::init(
const TQString &user,
const TQString &password,
63 const TQString &db,
const TQString &host )
71 Ticket *ResourceSql::requestSaveTicket()
73 if ( !addressBook() ) {
78 return createTicket(
this );
81 bool ResourceSql::open()
83 TQStringList drivers = TQSqlDatabase::drivers();
84 for ( TQStringList::Iterator it = drivers.begin(); it != drivers.end(); ++it ) {
88 mDb = TQSqlDatabase::addDatabase(
"QMYSQL3" );
91 kdDebug(5700) <<
"Error. Unable to connect to database." <<
endl;
95 mDb->setDatabaseName( mDbName );
96 mDb->setUserName( mUser );
97 mDb->setPassword( mPassword );
98 mDb->setHostName( mHost );
100 if ( !mDb->open() ) {
101 kdDebug(5700) <<
"Error. Unable to open database '" << mDbName <<
"'." <<
endl;
108 void ResourceSql::close()
113 bool ResourceSql::load()
115 TQSqlQuery query(
"select addressId, name, familyName, givenName, "
116 "additionalName, prefix, suffix, nickname, birthday, "
117 "mailer, timezone, geo_latitude, geo_longitude, title, "
118 "role, organization, note, productId, revision, "
119 "sortString, url from kaddressbook_main_" + mUser );
121 while ( query.next() ) {
122 TQString addrId = query.value(0).toString();
127 addr.setName( query.value(1).toString() );
128 addr.setFamilyName( query.value(2).toString() );
129 addr.setGivenName( query.value(3).toString() );
130 addr.setAdditionalName( query.value(4).toString() );
131 addr.setPrefix( query.value(5).toString() );
132 addr.setSuffix( query.value(6).toString() );
133 addr.setNickName( query.value(7).toString() );
134 addr.setBirthday( query.value(8).toDateTime() );
135 addr.setMailer( query.value(9).toString() );
136 addr.setTimeZone(
TimeZone( query.value(10).toInt() ) );
137 addr.setGeo(
Geo( query.value(11).toDouble(), query.value(12).toDouble() ) );
138 addr.setTitle( query.value(13).toString() );
139 addr.setRole( query.value(14).toString() );
140 addr.setOrganization( query.value(15).toString() );
141 addr.setNote( query.value(16).toString() );
142 addr.setProductId( query.value(17).toString() );
143 addr.setRevision( query.value(18).toDateTime() );
144 addr.setSortString( query.value(19).toString() );
145 addr.setUrl( query.value(20).toString() );
149 TQSqlQuery emailsQuery(
"select email, preferred from kaddressbook_emails "
150 "where addressId = '" + addrId +
"'" );
151 while ( emailsQuery.next() )
152 addr.
insertEmail( emailsQuery.value( 0 ).toString(),
153 emailsQuery.value( 1 ).toInt() );
158 TQSqlQuery phonesQuery(
"select number, type from kaddressbook_phones "
159 "where addressId = '" + addrId +
"'" );
160 while ( phonesQuery.next() )
162 phonesQuery.value( 1 ).toInt() ) );
167 TQSqlQuery addressesQuery(
"select postOfficeBox, extended, street, "
168 "locality, region, postalCode, country, label, type "
169 "from kaddressbook_addresses where addressId = '" + addrId +
"'" );
170 while ( addressesQuery.next() ) {
173 a.
setExtended( addressesQuery.value(1).toString() );
174 a.
setStreet( addressesQuery.value(2).toString() );
175 a.
setLocality( addressesQuery.value(3).toString() );
176 a.
setRegion( addressesQuery.value(4).toString() );
178 a.
setCountry( addressesQuery.value(6).toString() );
179 a.
setLabel( addressesQuery.value(7).toString() );
180 a.
setType( addressesQuery.value(8).toInt() );
188 TQSqlQuery categoriesQuery(
"select category from kaddressbook_categories "
189 "where addressId = '" + addrId +
"'" );
190 while ( categoriesQuery.next() )
196 TQSqlQuery customsQuery(
"select app, name, value from kaddressbook_customs "
197 "where addressId = '" + addrId +
"'" );
198 while ( customsQuery.next() )
200 customsQuery.value( 1 ).toString(),
201 customsQuery.value( 2 ).toString());
204 addressBook()->insertAddressee( addr );
210 bool ResourceSql::save(
Ticket * )
213 TQSqlQuery query(
"select addressId from kaddressbook_main_" + mUser );
215 while ( query.next() ) {
216 TQString addrId = query.value( 0 ).toString();
219 q.exec(
"DELETE FROM kaddressbook_emails WHERE addressId = '" + addrId +
"'" );
220 q.exec(
"DELETE FROM kaddressbook_phones WHERE addressId = '" + addrId +
"'" );
221 q.exec(
"DELETE FROM kaddressbook_addresses WHERE addressId = '" + addrId +
"'" );
222 q.exec(
"DELETE FROM kaddressbook_categories WHERE addressId = '" + addrId +
"'" );
223 q.exec(
"DELETE FROM kaddressbook_customs WHERE addressId = '" + addrId +
"'" );
225 q.exec(
"DELETE FROM kaddressbook_main_" + mUser +
" WHERE addressId = '" + addrId +
"'" );
230 for ( it = addressBook()->begin(); it != addressBook()->end(); ++it ) {
231 if ( (*it).resource() !=
this && (*it).resource() != 0 )
234 TQString uid = (*it).uid();
236 query.exec(
"INSERT INTO kaddressbook_main_" + mUser +
" VALUES ('" +
237 (*it).uid() +
"','" +
238 (*it).name() +
"','" +
239 (*it).familyName() +
"','" +
240 (*it).givenName() +
"','" +
241 (*it).additionalName() +
"','" +
242 (*it).prefix() +
"','" +
243 (*it).suffix() +
"','" +
244 (*it).nickName() +
"','" +
245 (*it).birthday().toString( Qt::ISODate ) +
"','" +
246 (*it).mailer() +
"','" +
247 TQString::number( (*it).timeZone().offset() ) +
"','" +
248 TQString::number( (*it).geo().latitude() ) +
"','" +
249 TQString::number( (*it).geo().longitude() ) +
"','" +
250 (*it).title() +
"','" +
251 (*it).role() +
"','" +
252 (*it).organization() +
"','" +
253 (*it).note() +
"','" +
254 (*it).productId() +
"','" +
255 (*it).revision().toString( Qt::ISODate ) +
"','" +
256 (*it).sortString() +
"','" +
257 (*it).url().url() +
"')"
262 TQStringList emails = (*it).emails();
263 TQStringList::ConstIterator it;
264 bool preferred =
true;
265 for( it = emails.begin(); it != emails.end(); ++it ) {
266 query.exec(
"INSERT INTO kaddressbook_emails VALUES ('" +
269 TQString::number(preferred) +
"')");
276 PhoneNumber::List phoneNumberList = (*it).phoneNumbers();
277 PhoneNumber::List::ConstIterator it;
278 for( it = phoneNumberList.begin(); it != phoneNumberList.end(); ++it ) {
279 query.exec(
"INSERT INTO kaddressbook_phones VALUES ('" +
281 (*it).number() +
"','" +
282 TQString::number( (*it).type() ) +
"')");
289 Address::List::ConstIterator it;
290 for( it = addressList.begin(); it != addressList.end(); ++it ) {
291 query.exec(
"INSERT INTO kaddressbook_addresses VALUES ('" +
293 (*it).postOfficeBox() +
"','" +
294 (*it).extended() +
"','" +
295 (*it).street() +
"','" +
296 (*it).locality() +
"','" +
297 (*it).region() +
"','" +
298 (*it).postalCode() +
"','" +
299 (*it).country() +
"','" +
300 (*it).label() +
"','" +
301 TQString::number( (*it).type() ) +
"')");
307 TQStringList categories = (*it).categories();
308 TQStringList::ConstIterator it;
309 for( it = categories.begin(); it != categories.end(); ++it )
310 query.exec(
"INSERT INTO kaddressbook_categories VALUES ('" +
317 TQStringList list = (*it).customs();
318 TQStringList::ConstIterator it;
319 for( it = list.begin(); it != list.end(); ++it ) {
320 int dashPos = (*it).find(
'-' );
321 int colonPos = (*it).find(
':' );
322 TQString app = (*it).left( dashPos );
323 TQString
name = (*it).mid( dashPos + 1, colonPos - dashPos - 1 );
324 TQString value = (*it).right( (*it).length() - colonPos - 1 );
326 query.exec(
"INSERT INTO kaddressbook_categories VALUES ('" +
327 uid +
"','" + app +
"','" + name +
"','" + value +
"')");
335 TQString ResourceSql::identifier()
const
337 return mHost +
"_" + mDbName;