1	/***************************************************************************
2	* Copyright (C) 2005-2014 by the Quassel Project *
3	* devel@quassel-irc.org *
4	* *
5	* This program is free software; you can redistribute it and/or modify *
6	* it under the terms of the GNU General Public License as published by *
7	* the Free Software Foundation; either version 2 of the License, or *
8	* (at your option) version 3. *
9	* *
10	* This program is distributed in the hope that it will be useful, *
11	* but WITHOUT ANY WARRANTY; without even the implied warranty of *
12	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
13	* GNU General Public License for more details. *
14	* *
15	* You should have received a copy of the GNU General Public License *
16	* along with this program; if not, write to the *
17	* Free Software Foundation, Inc., *
18	* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. *
19	***************************************************************************/
20
21	#include "sqlitestorage.h"
22
23	#include <QtSql>
24
25	#include "logger.h"
26	#include "network.h"
27	#include "quassel.h"
28
29	int SqliteStorage::_maxRetryCount = 150;
30
31	SqliteStorage::SqliteStorage(QObject *parent)
32	: AbstractSqlStorage(parent)
33	{
34	}
35
36
37	SqliteStorage::~SqliteStorage()
38	{
39	}
40
41
42	bool SqliteStorage::isAvailable() const
43	{
44	if (!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
45	return true;
46	}
47
48
49	QString SqliteStorage::displayName() const
50	{
51	// We identify the backend to use for the monolithic core by its displayname.
52	// so only change this string if you _really_ have to and make sure the core
53	// setup for the mono client still works ;)
54	return QString("SQLite");
55	}
56
57
58	QString SqliteStorage::description() const
59	{
60	return tr("SQLite is a file-based database engine that does not require any setup. It is suitable for small and medium-sized "
61	"databases that do not require access via network. Use SQLite if your Quassel Core should store its data on the same machine "
62	"it is running on, and if you only expect a few users to use your core.");
63	}
64
65
66	int SqliteStorage::installedSchemaVersion()
67	{
68	// only used when there is a singlethread (during startup)
69	// so we don't need locking here
70	QSqlQuery query = logDb().exec("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
71	if (query.first())
72	return query.value(0).toInt();
73
74	// maybe it's really old... (schema version 0)
75	query = logDb().exec("SELECT MAX(version) FROM coreinfo");
76	if (query.first())
77	return query.value(0).toInt();
78
79	return AbstractSqlStorage::installedSchemaVersion();
80	}
81
82
83	bool SqliteStorage::updateSchemaVersion(int newVersion)
84	{
85	// only used when there is a singlethread (during startup)
86	// so we don't need locking here
87	QSqlQuery query(logDb());
88	query.prepare("UPDATE coreinfo SET value = :version WHERE key = 'schemaversion'");
89	query.bindValue(":version", newVersion);
90	query.exec();
91
92	bool success = true;
93	if (query.lastError().isValid()) {
94	qCritical() << "SqliteStorage::updateSchemaVersion(int): Updating schema version failed!";
95	success = false;
96	}
97	return success;
98	}
99
100
101	bool SqliteStorage::setupSchemaVersion(int version)
102	{
103	// only used when there is a singlethread (during startup)
104	// so we don't need locking here
105	QSqlQuery query(logDb());
106	query.prepare("INSERT INTO coreinfo (key, value) VALUES ('schemaversion', :version)");
107	query.bindValue(":version", version);
108	query.exec();
109
110	bool success = true;
111	if (query.lastError().isValid()) {
112	qCritical() << "SqliteStorage::setupSchemaVersion(int): Updating schema version failed!";
113	success = false;
114	}
115	return success;
116	}
117
118
119	UserId SqliteStorage::addUser(const QString &user, const QString &password)
120	{
121	QSqlDatabase db = logDb();
122	UserId uid;
123
124	db.transaction();
125	// this scope ensures that the query is freed in sqlite before we call unlock()
126	// this ensures that our thread doesn't hold a internal after unlock is called
127	// (see sqlites doc on implicit locking for details)
128	{
129	QSqlQuery query(db);
130	query.prepare(queryString("insert_quasseluser"));
131	query.bindValue(":username", user);
132	query.bindValue(":password", cryptedPassword(password));
133	lockForWrite();
134	safeExec(query);
135	if (query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
136	db.rollback();
137	}
138	else {
139	uid = query.lastInsertId().toInt();
140	db.commit();
141	}
142	}
143	unlock();
144
145	if (uid.isValid())
146	emit userAdded(uid, user);
147	return uid;
148	}
149
150
151	bool SqliteStorage::updateUser(UserId user, const QString &password)
152	{
153	QSqlDatabase db = logDb();
154	bool success = false;
155
156	db.transaction();
157	{
158	QSqlQuery query(db);
159	query.prepare(queryString("update_userpassword"));
160	query.bindValue(":userid", user.toInt());
161	query.bindValue(":password", cryptedPassword(password));
162	lockForWrite();
163	safeExec(query);
164	success = query.numRowsAffected() != 0;
165	db.commit();
166	}
167	unlock();
168	return success;
169	}
170
171
172	void SqliteStorage::renameUser(UserId user, const QString &newName)
173	{
174	QSqlDatabase db = logDb();
175	db.transaction();
176	{
177	QSqlQuery query(db);
178	query.prepare(queryString("update_username"));
179	query.bindValue(":userid", user.toInt());
180	query.bindValue(":username", newName);
181	lockForWrite();
182	safeExec(query);
183	db.commit();
184	}
185	unlock();
186	emit userRenamed(user, newName);
187	}
188
189
190	UserId SqliteStorage::validateUser(const QString &user, const QString &password)
191	{
192	UserId userId;
193
194	{
195	QSqlQuery query(logDb());
196	query.prepare(queryString("select_authuser"));
197	query.bindValue(":username", user);
198	query.bindValue(":password", cryptedPassword(password));
199
200	lockForRead();
201	safeExec(query);
202
203	if (query.first()) {
204	userId = query.value(0).toInt();
205	}
206	}
207	unlock();
208
209	return userId;
210	}
211
212
213	UserId SqliteStorage::getUserId(const QString &username)
214	{
215	UserId userId;
216
217	{
218	QSqlQuery query(logDb());
219	query.prepare(queryString("select_userid"));
220	query.bindValue(":username", username);
221
222	lockForRead();
223	safeExec(query);
224
225	if (query.first()) {
226	userId = query.value(0).toInt();
227	}
228	}
229	unlock();
230
231	return userId;
232	}
233
234
235	UserId SqliteStorage::internalUser()
236	{
237	UserId userId;
238
239	{
240	QSqlQuery query(logDb());
241	query.prepare(queryString("select_internaluser"));
242	lockForRead();
243	safeExec(query);
244
245	if (query.first()) {
246	userId = query.value(0).toInt();
247	}
248	}
249	unlock();
250
251	return userId;
252	}
253
254
255	void SqliteStorage::delUser(UserId user)
256	{
257	QSqlDatabase db = logDb();
258	db.transaction();
259
260	lockForWrite();
261	{
262	QSqlQuery query(db);
263	query.prepare(queryString("delete_backlog_by_uid"));
264	query.bindValue(":userid", user.toInt());
265	safeExec(query);
266
267	query.prepare(queryString("delete_buffers_by_uid"));
268	query.bindValue(":userid", user.toInt());
269	safeExec(query);
270
271	query.prepare(queryString("delete_networks_by_uid"));
272	query.bindValue(":userid", user.toInt());
273	safeExec(query);
274
275	query.prepare(queryString("delete_quasseluser"));
276	query.bindValue(":userid", user.toInt());
277	safeExec(query);
278	// I hate the lack of foreign keys and on delete cascade... :(
279	db.commit();
280	}
281	unlock();
282
283	emit userRemoved(user);
284	}
285
286
287	void SqliteStorage::setUserSetting(UserId userId, const QString &settingName, const QVariant &data)
288	{
289	QByteArray rawData;
290	QDataStream out(&rawData, QIODevice::WriteOnly);
291	out.setVersion(QDataStream::Qt_4_2);
292	out << data;
293
294	QSqlDatabase db = logDb();
295	db.transaction();
296	{
297	QSqlQuery query(db);
298	query.prepare(queryString("insert_user_setting"));
299	query.bindValue(":userid", userId.toInt());
300	query.bindValue(":settingname", settingName);
301	query.bindValue(":settingvalue", rawData);
302	lockForWrite();
303	safeExec(query);
304
305	if (query.lastError().isValid()) {
306	QSqlQuery updateQuery(db);
307	updateQuery.prepare(queryString("update_user_setting"));
308	updateQuery.bindValue(":userid", userId.toInt());
309	updateQuery.bindValue(":settingname", settingName);
310	updateQuery.bindValue(":settingvalue", rawData);
311	safeExec(updateQuery);
312	}
313	db.commit();
314	}
315	unlock();
316	}
317
318
319	QVariant SqliteStorage::getUserSetting(UserId userId, const QString &settingName, const QVariant &defaultData)
320	{
321	QVariant data = defaultData;
322	{
323	QSqlQuery query(logDb());
324	query.prepare(queryString("select_user_setting"));
325	query.bindValue(":userid", userId.toInt());
326	query.bindValue(":settingname", settingName);
327	lockForRead();
328	safeExec(query);
329
330	if (query.first()) {
331	QByteArray rawData = query.value(0).toByteArray();
332	QDataStream in(&rawData, QIODevice::ReadOnly);
333	in.setVersion(QDataStream::Qt_4_2);
334	in >> data;
335	}
336	}
337	unlock();
338	return data;
339	}
340
341
342	IdentityId SqliteStorage::createIdentity(UserId user, CoreIdentity &identity)
343	{
344	IdentityId identityId;
345
346	QSqlDatabase db = logDb();
347	db.transaction();
348
349	{
350	QSqlQuery query(db);
351	query.prepare(queryString("insert_identity"));
352	query.bindValue(":userid", user.toInt());
353	query.bindValue(":identityname", identity.identityName());
354	query.bindValue(":realname", identity.realName());
355	query.bindValue(":awaynick", identity.awayNick());
356	query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
357	query.bindValue(":awayreason", identity.awayReason());
358	query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
359	query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
360	query.bindValue(":autoawaytime", identity.autoAwayTime());
361	query.bindValue(":autoawayreason", identity.autoAwayReason());
362	query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
363	query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
364	query.bindValue(":detachawayreason", identity.detachAwayReason());
365	query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
366	query.bindValue(":ident", identity.ident());
367	query.bindValue(":kickreason", identity.kickReason());
368	query.bindValue(":partreason", identity.partReason());
369	query.bindValue(":quitreason", identity.quitReason());
370	#ifdef HAVE_SSL
371	query.bindValue(":sslcert", identity.sslCert().toPem());
372	query.bindValue(":sslkey", identity.sslKey().toPem());
373	#else
374	query.bindValue(":sslcert", QByteArray());
375	query.bindValue(":sslkey", QByteArray());
376	#endif
377
378	lockForWrite();
379	safeExec(query);
380
381	identityId = query.lastInsertId().toInt();
382	if (!identityId.isValid()) {
383	watchQuery(query);
384	}
385	else {
386	QSqlQuery deleteNickQuery(db);
387	deleteNickQuery.prepare(queryString("delete_nicks"));
388	deleteNickQuery.bindValue(":identityid", identityId.toInt());
389	safeExec(deleteNickQuery);
390
391	QSqlQuery insertNickQuery(db);
392	insertNickQuery.prepare(queryString("insert_nick"));
393	foreach(QString nick, identity.nicks()) {
394	insertNickQuery.bindValue(":identityid", identityId.toInt());
395	insertNickQuery.bindValue(":nick", nick);
396	safeExec(insertNickQuery);
397	}
398	}
399	db.commit();
400	}
401	unlock();
402	identity.setId(identityId);
403	return identityId;
404	}
405
406
407	bool SqliteStorage::updateIdentity(UserId user, const CoreIdentity &identity)
408	{
409	QSqlDatabase db = logDb();
410	bool error = false;
411	db.transaction();
412
413	{
414	QSqlQuery checkQuery(db);
415	checkQuery.prepare(queryString("select_checkidentity"));
416	checkQuery.bindValue(":identityid", identity.id().toInt());
417	checkQuery.bindValue(":userid", user.toInt());
418	lockForRead();
419	safeExec(checkQuery);
420
421	// there should be exactly one identity for the given id and user
422	error = (!checkQuery.first() || checkQuery.value(0).toInt() != 1);
423	}
424	if (error) {
425	unlock();
426	return false;
427	}
428
429	{
430	QSqlQuery query(db);
431	query.prepare(queryString("update_identity"));
432	query.bindValue(":identityname", identity.identityName());
433	query.bindValue(":realname", identity.realName());
434	query.bindValue(":awaynick", identity.awayNick());
435	query.bindValue(":awaynickenabled", identity.awayNickEnabled() ? 1 : 0);
436	query.bindValue(":awayreason", identity.awayReason());
437	query.bindValue(":awayreasonenabled", identity.awayReasonEnabled() ? 1 : 0);
438	query.bindValue(":autoawayenabled", identity.awayReasonEnabled() ? 1 : 0);
439	query.bindValue(":autoawaytime", identity.autoAwayTime());
440	query.bindValue(":autoawayreason", identity.autoAwayReason());
441	query.bindValue(":autoawayreasonenabled", identity.autoAwayReasonEnabled() ? 1 : 0);
442	query.bindValue(":detachawayenabled", identity.detachAwayEnabled() ? 1 : 0);
443	query.bindValue(":detachawayreason", identity.detachAwayReason());
444	query.bindValue(":detachawayreasonenabled", identity.detachAwayReasonEnabled() ? 1 : 0);
445	query.bindValue(":ident", identity.ident());
446	query.bindValue(":kickreason", identity.kickReason());
447	query.bindValue(":partreason", identity.partReason());
448	query.bindValue(":quitreason", identity.quitReason());
449	#ifdef HAVE_SSL
450	query.bindValue(":sslcert", identity.sslCert().toPem());
451	query.bindValue(":sslkey", identity.sslKey().toPem());
452	#else
453	query.bindValue(":sslcert", QByteArray());
454	query.bindValue(":sslkey", QByteArray());
455	#endif
456	query.bindValue(":identityid", identity.id().toInt());
457	safeExec(query);
458	watchQuery(query);
459
460	QSqlQuery deleteNickQuery(db);
461	deleteNickQuery.prepare(queryString("delete_nicks"));
462	deleteNickQuery.bindValue(":identityid", identity.id().toInt());
463	safeExec(deleteNickQuery);
464	watchQuery(deleteNickQuery);
465
466	QSqlQuery insertNickQuery(db);
467	insertNickQuery.prepare(queryString("insert_nick"));
468	foreach(QString nick, identity.nicks()) {
469	insertNickQuery.bindValue(":identityid", identity.id().toInt());
470	insertNickQuery.bindValue(":nick", nick);
471	safeExec(insertNickQuery);
472	watchQuery(insertNickQuery);
473	}
474	db.commit();
475	}
476	unlock();
477	return true;
478	}
479
480
481	void SqliteStorage::removeIdentity(UserId user, IdentityId identityId)
482	{
483	QSqlDatabase db = logDb();
484	db.transaction();
485
486	bool error = false;
487	{
488	QSqlQuery checkQuery(db);
489	checkQuery.prepare(queryString("select_checkidentity"));
490	checkQuery.bindValue(":identityid", identityId.toInt());
491	checkQuery.bindValue(":userid", user.toInt());
492	lockForRead();
493	safeExec(checkQuery);
494
495	// there should be exactly one identity for the given id and user
496	error = (!checkQuery.first() || checkQuery.value(0).toInt() != 1);
497	}
498	if (error) {
499	unlock();
500	return;
501	}
502
503	{
504	QSqlQuery deleteNickQuery(db);
505	deleteNickQuery.prepare(queryString("delete_nicks"));
506	deleteNickQuery.bindValue(":identityid", identityId.toInt());
507	safeExec(deleteNickQuery);
508
509	QSqlQuery deleteIdentityQuery(db);
510	deleteIdentityQuery.prepare(queryString("delete_identity"));
511	deleteIdentityQuery.bindValue(":identityid", identityId.toInt());
512	deleteIdentityQuery.bindValue(":userid", user.toInt());
513	safeExec(deleteIdentityQuery);
514	db.commit();
515	}
516	unlock();
517	}
518
519
520	QList<CoreIdentity> SqliteStorage::identities(UserId user)
521	{
522	QList<CoreIdentity> identities;
523	QSqlDatabase db = logDb();
524	db.transaction();
525
526	{
527	QSqlQuery query(db);
528	query.prepare(queryString("select_identities"));
529	query.bindValue(":userid", user.toInt());
530
531	QSqlQuery nickQuery(db);
532	nickQuery.prepare(queryString("select_nicks"));
533
534	lockForRead();
535	safeExec(query);
536
537	while (query.next()) {
538	CoreIdentity identity(IdentityId(query.value(0).toInt()));
539
540	identity.setIdentityName(query.value(1).toString());
541	identity.setRealName(query.value(2).toString());
542	identity.setAwayNick(query.value(3).toString());
543	identity.setAwayNickEnabled(!!query.value(4).toInt());
544	identity.setAwayReason(query.value(5).toString());
545	identity.setAwayReasonEnabled(!!query.value(6).toInt());
546	identity.setAutoAwayEnabled(!!query.value(7).toInt());
547	identity.setAutoAwayTime(query.value(8).toInt());
548	identity.setAutoAwayReason(query.value(9).toString());
549	identity.setAutoAwayReasonEnabled(!!query.value(10).toInt());
550	identity.setDetachAwayEnabled(!!query.value(11).toInt());
551	identity.setDetachAwayReason(query.value(12).toString());
552	identity.setDetachAwayReasonEnabled(!!query.value(13).toInt());
553	identity.setIdent(query.value(14).toString());
554	identity.setKickReason(query.value(15).toString());
555	identity.setPartReason(query.value(16).toString());
556	identity.setQuitReason(query.value(17).toString());
557	#ifdef HAVE_SSL
558	identity.setSslCert(query.value(18).toByteArray());
559	identity.setSslKey(query.value(19).toByteArray());
560	#endif
561
562	nickQuery.bindValue(":identityid", identity.id().toInt());
563	QList<QString> nicks;
564	safeExec(nickQuery);
565	watchQuery(nickQuery);
566	while (nickQuery.next()) {
567	nicks << nickQuery.value(0).toString();
568	}
569	identity.setNicks(nicks);
570	identities << identity;
571	}
572	db.commit();
573	}
574	unlock();
575	return identities;
576	}
577
578
579	NetworkId SqliteStorage::createNetwork(UserId user, const NetworkInfo &info)
580	{
581	NetworkId networkId;
582
583	QSqlDatabase db = logDb();
584	db.transaction();
585
586	bool error = false;
587	{
588	QSqlQuery query(db);
589	query.prepare(queryString("insert_network"));
590	query.bindValue(":userid", user.toInt());
591	bindNetworkInfo(query, info);
592	lockForWrite();
593	safeExec(query);
594	if (!watchQuery(query)) {
595	db.rollback();
596	error = true;
597	}
598	else {
599	networkId = query.lastInsertId().toInt();
600	}
601	}
602	if (error) {
603	unlock();
604	return NetworkId();
605	}
606
607	{
608	QSqlQuery insertServersQuery(db);
609	insertServersQuery.prepare(queryString("insert_server"));
610	foreach(Network::Server server, info.serverList) {
611	insertServersQuery.bindValue(":userid", user.toInt());
612	insertServersQuery.bindValue(":networkid", networkId.toInt());
613	bindServerInfo(insertServersQuery, server);
614	safeExec(insertServersQuery);
615	if (!watchQuery(insertServersQuery)) {
616	db.rollback();
617	error = true;
618	break;
619	}
620	}
621	if (!error)
622	db.commit();
623	}
624	unlock();
625	if (error)
626	return NetworkId();
627	else
628	return networkId;
629	}
630
631
632	void SqliteStorage::bindNetworkInfo(QSqlQuery &query, const NetworkInfo &info)
633	{
634	query.bindValue(":networkname", info.networkName);
635	query.bindValue(":identityid", info.identity.toInt());
636	query.bindValue(":encodingcodec", QString(info.codecForEncoding));
637	query.bindValue(":decodingcodec", QString(info.codecForDecoding));
638	query.bindValue(":servercodec", QString(info.codecForServer));
639	query.bindValue(":userandomserver", info.useRandomServer ? 1 : 0);
640	query.bindValue(":perform", info.perform.join("\n"));
641	query.bindValue(":useautoidentify", info.useAutoIdentify ? 1 : 0);
642	query.bindValue(":autoidentifyservice", info.autoIdentifyService);
643	query.bindValue(":autoidentifypassword", info.autoIdentifyPassword);
644	query.bindValue(":usesasl", info.useSasl ? 1 : 0);
645	query.bindValue(":saslaccount", info.saslAccount);
646	query.bindValue(":saslpassword", info.saslPassword);
647	query.bindValue(":useautoreconnect", info.useAutoReconnect ? 1 : 0);
648	query.bindValue(":autoreconnectinterval", info.autoReconnectInterval);
649	query.bindValue(":autoreconnectretries", info.autoReconnectRetries);
650	query.bindValue(":unlimitedconnectretries", info.unlimitedReconnectRetries ? 1 : 0);
651	query.bindValue(":rejoinchannels", info.rejoinChannels ? 1 : 0);
652	if (info.networkId.isValid())
653	query.bindValue(":networkid", info.networkId.toInt());
654	}
655
656
657	void SqliteStorage::bindServerInfo(QSqlQuery &query, const Network::Server &server)
658	{
659	query.bindValue(":hostname", server.host);
660	query.bindValue(":port", server.port);
661	query.bindValue(":password", server.password);
662	query.bindValue(":ssl", server.useSsl ? 1 : 0);
663	query.bindValue(":sslversion", server.sslVersion);
664	query.bindValue(":useproxy", server.useProxy ? 1 : 0);
665	query.bindValue(":proxytype", server.proxyType);
666	query.bindValue(":proxyhost", server.proxyHost);
667	query.bindValue(":proxyport", server.proxyPort);
668	query.bindValue(":proxyuser", server.proxyUser);
669	query.bindValue(":proxypass", server.proxyPass);
670	}
671
672
673	bool SqliteStorage::updateNetwork(UserId user, const NetworkInfo &info)
674	{
675	QSqlDatabase db = logDb();
676	bool error = false;
677	db.transaction();
678
679	{
680	QSqlQuery updateQuery(db);
681	updateQuery.prepare(queryString("update_network"));
682	updateQuery.bindValue(":userid", user.toInt());
683	bindNetworkInfo(updateQuery, info);
684
685	lockForWrite();
686	safeExec(updateQuery);
687	if (!watchQuery(updateQuery) || updateQuery.numRowsAffected() != 1) {
688	error = true;
689	db.rollback();
690	}
691	}
692	if (error) {
693	unlock();
694	return false;
695	}
696
697	{
698	QSqlQuery dropServersQuery(db);
699	dropServersQuery.prepare("DELETE FROM ircserver WHERE networkid = :networkid");
700	dropServersQuery.bindValue(":networkid", info.networkId.toInt());
701	safeExec(dropServersQuery);
702	if (!watchQuery(dropServersQuery)) {
703	error = true;
704	db.rollback();
705	}
706	}
707	if (error) {
708	unlock();
709	return false;
710	}
711
712	{
713	QSqlQuery insertServersQuery(db);
714	insertServersQuery.prepare(queryString("insert_server"));
715	foreach(Network::Server server, info.serverList) {
716	insertServersQuery.bindValue(":userid", user.toInt());
717	insertServersQuery.bindValue(":networkid", info.networkId.toInt());
718	bindServerInfo(insertServersQuery, server);
719	safeExec(insertServersQuery);
720	if (!watchQuery(insertServersQuery)) {
721	error = true;
722	db.rollback();
723	break;
724	}
725	}
726	}
727
728	db.commit();
729	unlock();
730	return !error;
731	}
732
733
734	bool SqliteStorage::removeNetwork(UserId user, const NetworkId &networkId)
735	{
736	QSqlDatabase db = logDb();
737	bool error = false;
738	db.transaction();
739
740	{
741	QSqlQuery deleteNetworkQuery(db);
742	deleteNetworkQuery.prepare(queryString("delete_network"));
743	deleteNetworkQuery.bindValue(":networkid", networkId.toInt());
744	deleteNetworkQuery.bindValue(":userid", user.toInt());
745	lockForWrite();
746	safeExec(deleteNetworkQuery);
747	if (!watchQuery(deleteNetworkQuery) || deleteNetworkQuery.numRowsAffected() != 1) {
748	error = true;
749	db.rollback();
750	}
751	}
752	if (error) {
753	unlock();
754	return false;
755	}
756
757	{
758	QSqlQuery deleteBacklogQuery(db);
759	deleteBacklogQuery.prepare(queryString("delete_backlog_for_network"));
760	deleteBacklogQuery.bindValue(":networkid", networkId.toInt());
761	safeExec(deleteBacklogQuery);
762	if (!watchQuery(deleteBacklogQuery)) {
763	db.rollback();
764	error = true;
765	}
766	}
767	if (error) {
768	unlock();
769	return false;
770	}
771
772	{
773	QSqlQuery deleteBuffersQuery(db);
774	deleteBuffersQuery.prepare(queryString("delete_buffers_for_network"));
775	deleteBuffersQuery.bindValue(":networkid", networkId.toInt());
776	safeExec(deleteBuffersQuery);
777	if (!watchQuery(deleteBuffersQuery)) {
778	db.rollback();
779	error = true;
780	}
781	}
782	if (error) {
783	unlock();
784	return false;
785	}
786
787	{
788	QSqlQuery deleteServersQuery(db);
789	deleteServersQuery.prepare(queryString("delete_ircservers_for_network"));
790	deleteServersQuery.bindValue(":networkid", networkId.toInt());
791	safeExec(deleteServersQuery);
792	if (!watchQuery(deleteServersQuery)) {
793	db.rollback();
794	error = true;
795	}
796	}
797	if (error) {
798	unlock();
799	return false;
800	}
801
802	db.commit();
803	unlock();
804	return true;
805	}
806
807
808	QList<NetworkInfo> SqliteStorage::networks(UserId user)
809	{
810	QList<NetworkInfo> nets;
811
812	QSqlDatabase db = logDb();
813	db.transaction();
814
815	{
816	QSqlQuery networksQuery(db);
817	networksQuery.prepare(queryString("select_networks_for_user"));
818	networksQuery.bindValue(":userid", user.toInt());
819
820	QSqlQuery serversQuery(db);
821	serversQuery.prepare(queryString("select_servers_for_network"));
822
823	lockForRead();
824	safeExec(networksQuery);
825	if (watchQuery(networksQuery)) {
826	while (networksQuery.next()) {
827	NetworkInfo net;
828	net.networkId = networksQuery.value(0).toInt();
829	net.networkName = networksQuery.value(1).toString();
830	net.identity = networksQuery.value(2).toInt();
831	net.codecForServer = networksQuery.value(3).toString().toLatin1();
832	net.codecForEncoding = networksQuery.value(4).toString().toLatin1();
833	net.codecForDecoding = networksQuery.value(5).toString().toLatin1();
834	net.useRandomServer = networksQuery.value(6).toInt() == 1 ? true : false;
835	net.perform = networksQuery.value(7).toString().split("\n");
836	net.useAutoIdentify = networksQuery.value(8).toInt() == 1 ? true : false;
837	net.autoIdentifyService = networksQuery.value(9).toString();
838	net.autoIdentifyPassword = networksQuery.value(10).toString();
839	net.useAutoReconnect = networksQuery.value(11).toInt() == 1 ? true : false;
840	net.autoReconnectInterval = networksQuery.value(12).toUInt();
841	net.autoReconnectRetries = networksQuery.value(13).toInt();
842	net.unlimitedReconnectRetries = networksQuery.value(14).toInt() == 1 ? true : false;
843	net.rejoinChannels = networksQuery.value(15).toInt() == 1 ? true : false;
844	net.useSasl = networksQuery.value(16).toInt() == 1 ? true : false;
845	net.saslAccount = networksQuery.value(17).toString();
846	net.saslPassword = networksQuery.value(18).toString();
847
848	serversQuery.bindValue(":networkid", net.networkId.toInt());
849	safeExec(serversQuery);
850	if (!watchQuery(serversQuery)) {
851	nets.clear();
852	break;
853	}
854	else {
855	Network::ServerList servers;
856	while (serversQuery.next()) {
857	Network::Server server;
858	server.host = serversQuery.value(0).toString();
859	server.port = serversQuery.value(1).toUInt();
860	server.password = serversQuery.value(2).toString();
861	server.useSsl = serversQuery.value(3).toInt() == 1 ? true : false;
862	server.sslVersion = serversQuery.value(4).toInt();
863	server.useProxy = serversQuery.value(5).toInt() == 1 ? true : false;
864	server.proxyType = serversQuery.value(6).toInt();
865	server.proxyHost = serversQuery.value(7).toString();
866	server.proxyPort = serversQuery.value(8).toUInt();
867	server.proxyUser = serversQuery.value(9).toString();
868	server.proxyPass = serversQuery.value(10).toString();
869	servers << server;
870	}
871	net.serverList = servers;
872	nets << net;
873	}
874	}
875	}
876	}
877	db.commit();
878	unlock();
879	return nets;
880	}
881
882
883	QList<NetworkId> SqliteStorage::connectedNetworks(UserId user)
884	{
885	QList<NetworkId> connectedNets;
886
887	QSqlDatabase db = logDb();
888	db.transaction();
889
890	{
891	QSqlQuery query(db);
892	query.prepare(queryString("select_connected_networks"));
893	query.bindValue(":userid", user.toInt());
894	lockForRead();
895	safeExec(query);
896	watchQuery(query);
897
898	while (query.next()) {
899	connectedNets << query.value(0).toInt();
900	}
901	db.commit();
902	}
903	unlock();
904	return connectedNets;
905	}
906
907
908	void SqliteStorage::setNetworkConnected(UserId user, const NetworkId &networkId, bool isConnected)
909	{
910	QSqlDatabase db = logDb();
911	db.transaction();
912
913	{
914	QSqlQuery query(db);
915	query.prepare(queryString("update_network_connected"));
916	query.bindValue(":userid", user.toInt());
917	query.bindValue(":networkid", networkId.toInt());
918	query.bindValue(":connected", isConnected ? 1 : 0);
919
920	lockForWrite();
921	safeExec(query);
922	watchQuery(query);
923	db.commit();
924	}
925	unlock();
926	}
927
928
929	QHash<QString, QString> SqliteStorage::persistentChannels(UserId user, const NetworkId &networkId)
930	{
931	QHash<QString, QString> persistentChans;
932
933	QSqlDatabase db = logDb();
934	db.transaction();
935	{
936	QSqlQuery query(db);
937	query.prepare(queryString("select_persistent_channels"));
938	query.bindValue(":userid", user.toInt());
939	query.bindValue(":networkid", networkId.toInt());
940
941	lockForRead();
942	safeExec(query);
943	watchQuery(query);
944	while (query.next()) {
945	persistentChans[query.value(0).toString()] = query.value(1).toString();
946	}
947	}
948	unlock();
949	return persistentChans;
950	}
951
952
953	void SqliteStorage::setChannelPersistent(UserId user, const NetworkId &networkId, const QString &channel, bool isJoined)
954	{
955	QSqlDatabase db = logDb();
956	db.transaction();
957
958	{
959	QSqlQuery query(db);
960	query.prepare(queryString("update_buffer_persistent_channel"));
961	query.bindValue(":userid", user.toInt());
962	query.bindValue(":networkId", networkId.toInt());
963	query.bindValue(":buffercname", channel.toLower());
964	query.bindValue(":joined", isJoined ? 1 : 0);
965
966	lockForWrite();
967	safeExec(query);
968	watchQuery(query);
969	db.commit();
970	}
971	unlock();
972	}
973
974
975	void SqliteStorage::setPersistentChannelKey(UserId user, const NetworkId &networkId, const QString &channel, const QString &key)
976	{
977	QSqlDatabase db = logDb();
978	db.transaction();
979
980	{
981	QSqlQuery query(db);
982	query.prepare(queryString("update_buffer_set_channel_key"));
983	query.bindValue(":userid", user.toInt());
984	query.bindValue(":networkId", networkId.toInt());
985	query.bindValue(":buffercname", channel.toLower());
986	query.bindValue(":key", key);
987
988	lockForWrite();
989	safeExec(query);
990	watchQuery(query);
991	db.commit();
992	}
993	unlock();
994	}
995
996
997	QString SqliteStorage::awayMessage(UserId user, NetworkId networkId)
998	{
999	QSqlDatabase db = logDb();
1000	db.transaction();
1001
1002	QString awayMsg;
1003	{
1004	QSqlQuery query(db);
1005	query.prepare(queryString("select_network_awaymsg"));
1006	query.bindValue(":userid", user.toInt());
1007	query.bindValue(":networkid", networkId.toInt());
1008
1009	lockForRead();
1010	safeExec(query);
1011	watchQuery(query);
1012	if (query.first())
1013	awayMsg = query.value(0).toString();
1014	db.commit();
1015	}
1016	unlock();
1017
1018	return awayMsg;
1019	}
1020
1021
1022	void SqliteStorage::setAwayMessage(UserId user, NetworkId networkId, const QString &awayMsg)
1023	{
1024	QSqlDatabase db = logDb();
1025	db.transaction();
1026
1027	{
1028	QSqlQuery query(db);
1029	query.prepare(queryString("update_network_set_awaymsg"));
1030	query.bindValue(":userid", user.toInt());
1031	query.bindValue(":networkid", networkId.toInt());
1032	query.bindValue(":awaymsg", awayMsg);
1033
1034	lockForWrite();
1035	safeExec(query);
1036	watchQuery(query);
1037	db.commit();
1038	}
1039	unlock();
1040	}
1041
1042
1043	QString SqliteStorage::userModes(UserId user, NetworkId networkId)
1044	{
1045	QSqlDatabase db = logDb();
1046	db.transaction();
1047
1048	QString modes;
1049	{
1050	QSqlQuery query(db);
1051	query.prepare(queryString("select_network_usermode"));
1052	query.bindValue(":userid", user.toInt());
1053	query.bindValue(":networkid", networkId.toInt());
1054
1055	lockForRead();
1056	safeExec(query);
1057	watchQuery(query);
1058	if (query.first())
1059	modes = query.value(0).toString();
1060	db.commit();
1061	}
1062	unlock();
1063
1064	return modes;
1065	}
1066
1067
1068	void SqliteStorage::setUserModes(UserId user, NetworkId networkId, const QString &userModes)
1069	{
1070	QSqlDatabase db = logDb();
1071	db.transaction();
1072
1073	{
1074	QSqlQuery query(db);
1075	query.prepare(queryString("update_network_set_usermode"));
1076	query.bindValue(":userid", user.toInt());
1077	query.bindValue(":networkid", networkId.toInt());
1078	query.bindValue(":usermode", userModes);
1079
1080	lockForWrite();
1081	safeExec(query);
1082	watchQuery(query);
1083	db.commit();
1084	}
1085	unlock();
1086	}
1087
1088
1089	BufferInfo SqliteStorage::bufferInfo(UserId user, const NetworkId &networkId, BufferInfo::Type type, const QString &buffer, bool create)
1090	{
1091	QSqlDatabase db = logDb();
1092	db.transaction();
1093
1094	BufferInfo bufferInfo;
1095	{
1096	QSqlQuery query(db);
1097	query.prepare(queryString("select_bufferByName"));
1098	query.bindValue(":networkid", networkId.toInt());
1099	query.bindValue(":userid", user.toInt());
1100	query.bindValue(":buffercname", buffer.toLower());
1101
1102	lockForRead();
1103	safeExec(query);
1104
1105	if (query.first()) {
1106	bufferInfo = BufferInfo(query.value(0).toInt(), networkId, (BufferInfo::Type)query.value(1).toInt(), 0, buffer);
1107	if (query.next()) {
1108	qCritical() << "SqliteStorage::getBufferInfo(): received more then one Buffer!";
1109	qCritical() << " Query:" << query.lastQuery();
1110	qCritical() << " bound Values:";
1111	QList<QVariant> list = query.boundValues().values();
1112	for (int i = 0; i < list.size(); ++i)
1113	qCritical() << i << ":" << list.at(i).toString().toLatin1().data();
1114	Q_ASSERT(false);
1115	}
1116	}
1117	else if (create) {
1118	// let's create the buffer
1119	QSqlQuery createQuery(db);
1120	createQuery.prepare(queryString("insert_buffer"));
1121	createQuery.bindValue(":userid", user.toInt());
1122	createQuery.bindValue(":networkid", networkId.toInt());
1123	createQuery.bindValue(":buffertype", (int)type);
1124	createQuery.bindValue(":buffername", buffer);
1125	createQuery.bindValue(":buffercname", buffer.toLower());
1126	createQuery.bindValue(":joined", type & BufferInfo::ChannelBuffer ? 1 : 0);
1127
1128	unlock();
1129	lockForWrite();
1130	safeExec(createQuery);
1131	watchQuery(createQuery);
1132	bufferInfo = BufferInfo(createQuery.lastInsertId().toInt(), networkId, type, 0, buffer);
1133	}
1134	}
1135	db.commit();
1136	unlock();
1137	return bufferInfo;
1138	}
1139
1140
1141	BufferInfo SqliteStorage::getBufferInfo(UserId user, const BufferId &bufferId)
1142	{
1143	QSqlDatabase db = logDb();
1144	db.transaction();
1145
1146	BufferInfo bufferInfo;
1147	{
1148	QSqlQuery query(db);
1149	query.prepare(queryString("select_buffer_by_id"));
1150	query.bindValue(":userid", user.toInt());
1151	query.bindValue(":bufferid", bufferId.toInt());
1152
1153	lockForRead();
1154	safeExec(query);
1155
1156	if (watchQuery(query) && query.first()) {
1157	bufferInfo = BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), 0, query.value(4).toString());
1158	Q_ASSERT(!query.next());
1159	}
1160	db.commit();
1161	}
1162	unlock();
1163	return bufferInfo;
1164	}
1165
1166
1167	QList<BufferInfo> SqliteStorage::requestBuffers(UserId user)
1168	{
1169	QList<BufferInfo> bufferlist;
1170
1171	QSqlDatabase db = logDb();
1172	db.transaction();
1173
1174	{
1175	QSqlQuery query(db);
1176	query.prepare(queryString("select_buffers"));
1177	query.bindValue(":userid", user.toInt());
1178
1179	lockForRead();
1180	safeExec(query);
1181	watchQuery(query);
1182	while (query.next()) {
1183	bufferlist << BufferInfo(query.value(0).toInt(), query.value(1).toInt(), (BufferInfo::Type)query.value(2).toInt(), query.value(3).toInt(), query.value(4).toString());
1184	}
1185	db.commit();
1186	}
1187	unlock();
1188
1189	return bufferlist;
1190	}
1191
1192
1193	QList<BufferId> SqliteStorage::requestBufferIdsForNetwork(UserId user, NetworkId networkId)
1194	{
1195	QList<BufferId> bufferList;
1196
1197	QSqlDatabase db = logDb();
1198	db.transaction();
1199
1200	{
1201	QSqlQuery query(db);
1202	query.prepare(queryString("select_buffers_for_network"));
1203	query.bindValue(":networkid", networkId.toInt());
1204	query.bindValue(":userid", user.toInt());
1205
1206	lockForRead();
1207	safeExec(query);
1208	watchQuery(query);
1209	while (query.next()) {
1210	bufferList << BufferId(query.value(0).toInt());
1211	}
1212	db.commit();
1213	}
1214	unlock();
1215
1216	return bufferList;
1217	}
1218
1219
1220	bool SqliteStorage::removeBuffer(const UserId &user, const BufferId &bufferId)
1221	{
1222	QSqlDatabase db = logDb();
1223	db.transaction();
1224
1225	bool error = false;
1226	{
1227	QSqlQuery delBufferQuery(db);
1228	delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1229	delBufferQuery.bindValue(":bufferid", bufferId.toInt());
1230	delBufferQuery.bindValue(":userid", user.toInt());
1231
1232	lockForWrite();
1233	safeExec(delBufferQuery);
1234
1235	error = (!watchQuery(delBufferQuery) || delBufferQuery.numRowsAffected() != 1);
1236	}
1237
1238	if (error) {
1239	db.rollback();
1240	unlock();
1241	return false;
1242	}
1243
1244	{
1245	QSqlQuery delBacklogQuery(db);
1246	delBacklogQuery.prepare(queryString("delete_backlog_for_buffer"));
1247	delBacklogQuery.bindValue(":bufferid", bufferId.toInt());
1248
1249	safeExec(delBacklogQuery);
1250	error = !watchQuery(delBacklogQuery);
1251	}
1252
1253	if (error) {
1254	db.rollback();
1255	}
1256	else {
1257	db.commit();
1258	}
1259	unlock();
1260	return !error;
1261	}
1262
1263
1264	bool SqliteStorage::renameBuffer(const UserId &user, const BufferId &bufferId, const QString &newName)
1265	{
1266	QSqlDatabase db = logDb();
1267	db.transaction();
1268
1269	bool error = false;
1270	{
1271	QSqlQuery query(db);
1272	query.prepare(queryString("update_buffer_name"));
1273	query.bindValue(":buffername", newName);
1274	query.bindValue(":buffercname", newName.toLower());
1275	query.bindValue(":bufferid", bufferId.toInt());
1276	query.bindValue(":userid", user.toInt());
1277
1278	lockForWrite();
1279	safeExec(query);
1280
1281	error = query.lastError().isValid();
1282	// unexepcted error occured (19 == constraint violation)
1283	if (error && query.lastError().number() != 19) {
1284	watchQuery(query);
1285	}
1286	else {
1287	error |= (query.numRowsAffected() != 1);
1288	}
1289	}
1290	if (error) {
1291	db.rollback();
1292	}
1293	else {
1294	db.commit();
1295	}
1296	unlock();
1297	return !error;
1298	}
1299
1300
1301	bool SqliteStorage::mergeBuffersPermanently(const UserId &user, const BufferId &bufferId1, const BufferId &bufferId2)
1302	{
1303	QSqlDatabase db = logDb();
1304	db.transaction();
1305
1306	bool error = false;
1307	{
1308	QSqlQuery checkQuery(db);
1309	checkQuery.prepare(queryString("select_buffers_for_merge"));
1310	checkQuery.bindValue(":oldbufferid", bufferId2.toInt());
1311	checkQuery.bindValue(":newbufferid", bufferId1.toInt());
1312	checkQuery.bindValue(":userid", user.toInt());
1313
1314	lockForRead();
1315	safeExec(checkQuery);
1316	error = (!checkQuery.first() || checkQuery.value(0).toInt() != 2);
1317	}
1318	if (error) {
1319	db.rollback();
1320	unlock();
1321	return false;
1322	}
1323
1324	{
1325	QSqlQuery query(db);
1326	query.prepare(queryString("update_backlog_bufferid"));
1327	query.bindValue(":oldbufferid", bufferId2.toInt());
1328	query.bindValue(":newbufferid", bufferId1.toInt());
1329	safeExec(query);
1330	error = !watchQuery(query);
1331	}
1332	if (error) {
1333	db.rollback();
1334	unlock();
1335	return false;
1336	}
1337
1338	{
1339	QSqlQuery delBufferQuery(db);
1340	delBufferQuery.prepare(queryString("delete_buffer_for_bufferid"));
1341	delBufferQuery.bindValue(":bufferid", bufferId2.toInt());
1342	delBufferQuery.bindValue(":userid", user.toInt());
1343	safeExec(delBufferQuery);
1344	error = !watchQuery(delBufferQuery);
1345	}
1346
1347	if (error) {
1348	db.rollback();
1349	}
1350	else {
1351	db.commit();
1352	}
1353	unlock();
1354	return !error;
1355	}
1356
1357
1358	void SqliteStorage::setBufferLastSeenMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1359	{
1360	QSqlDatabase db = logDb();
1361	db.transaction();
1362
1363	{
1364	QSqlQuery query(db);
1365	query.prepare(queryString("update_buffer_lastseen"));
1366	query.bindValue(":userid", user.toInt());
1367	query.bindValue(":bufferid", bufferId.toInt());
1368	query.bindValue(":lastseenmsgid", msgId.toInt());
1369
1370	lockForWrite();
1371	safeExec(query);
1372	watchQuery(query);
1373	}
1374	db.commit();
1375	unlock();
1376	}
1377
1378
1379	QHash<BufferId, MsgId> SqliteStorage::bufferLastSeenMsgIds(UserId user)
1380	{
1381	QHash<BufferId, MsgId> lastSeenHash;
1382
1383	QSqlDatabase db = logDb();
1384	db.transaction();
1385
1386	bool error = false;
1387	{
1388	QSqlQuery query(db);
1389	query.prepare(queryString("select_buffer_lastseen_messages"));
1390	query.bindValue(":userid", user.toInt());
1391
1392	lockForRead();
1393	safeExec(query);
1394	error = !watchQuery(query);
1395	if (!error) {
1396	while (query.next()) {
1397	lastSeenHash[query.value(0).toInt()] = query.value(1).toInt();
1398	}
1399	}
1400	}
1401
1402	db.commit();
1403	unlock();
1404	return lastSeenHash;
1405	}
1406
1407
1408	void SqliteStorage::setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId)
1409	{
1410	QSqlDatabase db = logDb();
1411	db.transaction();
1412
1413	{
1414	QSqlQuery query(db);
1415	query.prepare(queryString("update_buffer_markerlinemsgid"));
1416	query.bindValue(":userid", user.toInt());
1417	query.bindValue(":bufferid", bufferId.toInt());
1418	query.bindValue(":markerlinemsgid", msgId.toInt());
1419
1420	lockForWrite();
1421	safeExec(query);
1422	watchQuery(query);
1423	}
1424	db.commit();
1425	unlock();
1426	}
1427
1428
1429	QHash<BufferId, MsgId> SqliteStorage::bufferMarkerLineMsgIds(UserId user)
1430	{
1431	QHash<BufferId, MsgId> markerLineHash;
1432
1433	QSqlDatabase db = logDb();
1434	db.transaction();
1435
1436	bool error = false;
1437	{
1438	QSqlQuery query(db);
1439	query.prepare(queryString("select_buffer_markerlinemsgids"));
1440	query.bindValue(":userid", user.toInt());
1441
1442	lockForRead();
1443	safeExec(query);
1444	error = !watchQuery(query);
1445	if (!error) {
1446	while (query.next()) {
1447	markerLineHash[query.value(0).toInt()] = query.value(1).toInt();
1448	}
1449	}
1450	}
1451
1452	db.commit();
1453	unlock();
1454	return markerLineHash;
1455	}
1456
1457
1458	bool SqliteStorage::logMessage(Message &msg)
1459	{
1460	QSqlDatabase db = logDb();
1461	db.transaction();
1462
1463	bool error = false;
1464	{
1465	QSqlQuery logMessageQuery(db);
1466	logMessageQuery.prepare(queryString("insert_message"));
1467
1468	logMessageQuery.bindValue(":time", msg.timestamp().toTime_t());
1469	logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt());
1470	logMessageQuery.bindValue(":type", msg.type());
1471	logMessageQuery.bindValue(":flags", (int)msg.flags());
1472	logMessageQuery.bindValue(":sender", msg.sender());
1473	logMessageQuery.bindValue(":message", msg.contents());
1474
1475	lockForWrite();
1476	safeExec(logMessageQuery);
1477
1478	if (logMessageQuery.lastError().isValid()) {
1479	// constraint violation - must be NOT NULL constraint - probably the sender is missing...
1480	if (logMessageQuery.lastError().number() == 19) {
1481	QSqlQuery addSenderQuery(db);
1482	addSenderQuery.prepare(queryString("insert_sender"));
1483	addSenderQuery.bindValue(":sender", msg.sender());
1484	safeExec(addSenderQuery);
1485	safeExec(logMessageQuery);
1486	error = !watchQuery(logMessageQuery);
1487	}
1488	else {
1489	watchQuery(logMessageQuery);
1490	}
1491	}
1492	if (!error) {
1493	MsgId msgId = logMessageQuery.lastInsertId().toInt();
1494	if (msgId.isValid()) {
1495	msg.setMsgId(msgId);
1496	}
1497	else {
1498	error = true;
1499	}
1500	}
1501	}
1502
1503	if (error) {
1504	db.rollback();
1505	}
1506	else {
1507	db.commit();
1508	}
1509
1510	unlock();
1511	return !error;
1512	}
1513
1514
1515	bool SqliteStorage::logMessages(MessageList &msgs)
1516	{
1517	QSqlDatabase db = logDb();
1518	db.transaction();
1519
1520	{
1521	QSet<QString> senders;
1522	QSqlQuery addSenderQuery(db);
1523	addSenderQuery.prepare(queryString("insert_sender"));
1524	lockForWrite();
1525	for (int i = 0; i < msgs.count(); i++) {
1526	const QString &sender = msgs.at(i).sender();
1527	if (senders.contains(sender))
1528	continue;
1529	senders << sender;
1530
1531	addSenderQuery.bindValue(":sender", sender);
1532	safeExec(addSenderQuery);
1533	}
1534	}
1535
1536	bool error = false;
1537	{
1538	QSqlQuery logMessageQuery(db);
1539	logMessageQuery.prepare(queryString("insert_message"));
1540	for (int i = 0; i < msgs.count(); i++) {
1541	Message &msg = msgs[i];
1542
1543	logMessageQuery.bindValue(":time", msg.timestamp().toTime_t());
1544	logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt());
1545	logMessageQuery.bindValue(":type", msg.type());
1546	logMessageQuery.bindValue(":flags", (int)msg.flags());
1547	logMessageQuery.bindValue(":sender", msg.sender());
1548	logMessageQuery.bindValue(":message", msg.contents());
1549
1550	safeExec(logMessageQuery);
1551	if (!watchQuery(logMessageQuery)) {
1552	error = true;
1553	break;
1554	}
1555	else {
1556	msg.setMsgId(logMessageQuery.lastInsertId().toInt());
1557	}
1558	}
1559	}
1560
1561	if (error) {
1562	db.rollback();
1563	unlock();
1564	// we had a rollback in the db so we need to reset all msgIds
1565	for (int i = 0; i < msgs.count(); i++) {
1566	msgs[i].setMsgId(MsgId());
1567	}
1568	}
1569	else {
1570	db.commit();
1571	unlock();
1572	}
1573	return !error;
1574	}
1575
1576
1577	QList<Message> SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId first, MsgId last, int limit)
1578	{
1579	QList<Message> messagelist;
1580
1581	QSqlDatabase db = logDb();
1582	db.transaction();
1583
1584	bool error = false;
1585	BufferInfo bufferInfo;
1586	{
1587	// code dupication from getBufferInfo:
1588	// this is due to the impossibility of nesting transactions and recursive locking
1589	QSqlQuery bufferInfoQuery(db);
1590	bufferInfoQuery.prepare(queryString("select_buffer_by_id"));
1591	bufferInfoQuery.bindValue(":userid", user.toInt());
1592	bufferInfoQuery.bindValue(":bufferid", bufferId.toInt());
1593
1594	lockForRead();
1595	safeExec(bufferInfoQuery);
1596	error = !watchQuery(bufferInfoQuery) || !bufferInfoQuery.first();
1597	if (!error) {
1598	bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), bufferInfoQuery.value(1).toInt(), (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), 0, bufferInfoQuery.value(4).toString());
1599	error = !bufferInfo.isValid();
1600	}
1601	}
1602	if (error) {
1603	db.rollback();
1604	unlock();
1605	return messagelist;
1606	}
1607
1608	{
1609	QSqlQuery query(db);
1610	if (last == -1 && first == -1) {
1611	query.prepare(queryString("select_messagesNewestK"));
1612	}
1613	else if (last == -1) {
1614	query.prepare(queryString("select_messagesNewerThan"));
1615	query.bindValue(":firstmsg", first.toInt());
1616	}
1617	else {
1618	query.prepare(queryString("select_messages"));
1619	query.bindValue(":lastmsg", last.toInt());
1620	query.bindValue(":firstmsg", first.toInt());
1621	}
1622	query.bindValue(":bufferid", bufferId.toInt());
1623	query.bindValue(":limit", limit);
1624
1625	safeExec(query);
1626	watchQuery(query);
1627
1628	while (query.next()) {
1629	Message msg(QDateTime::fromTime_t(query.value(1).toInt()),
1630	bufferInfo,
1631	(Message::Type)query.value(2).toUInt(),
1632	query.value(5).toString(),
1633	query.value(4).toString(),
1634	(Message::Flags)query.value(3).toUInt());
1635	msg.setMsgId(query.value(0).toInt());
1636	messagelist << msg;
1637	}
1638	}
1639	db.commit();
1640	unlock();
1641
1642	return messagelist;
1643	}
1644
1645
1646	QList<Message> SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId last, int limit)
1647	{
1648	QList<Message> messagelist;
1649
1650	QSqlDatabase db = logDb();
1651	db.transaction();
1652
1653	QHash<BufferId, BufferInfo> bufferInfoHash;
1654	{
1655	QSqlQuery bufferInfoQuery(db);
1656	bufferInfoQuery.prepare(queryString("select_buffers"));
1657	bufferInfoQuery.bindValue(":userid", user.toInt());
1658
1659	lockForRead();
1660	safeExec(bufferInfoQuery);
1661	watchQuery(bufferInfoQuery);
1662	while (bufferInfoQuery.next()) {
1663	BufferInfo bufferInfo = BufferInfo(bufferInfoQuery.value(0).toInt(), bufferInfoQuery.value(1).toInt(), (BufferInfo::Type)bufferInfoQuery.value(2).toInt(), bufferInfoQuery.value(3).toInt(), bufferInfoQuery.value(4).toString());
1664	bufferInfoHash[bufferInfo.bufferId()] = bufferInfo;
1665	}
1666
1667	QSqlQuery query(db);
1668	if (last == -1) {
1669	query.prepare(queryString("select_messagesAllNew"));
1670	}
1671	else {
1672	query.prepare(queryString("select_messagesAll"));
1673	query.bindValue(":lastmsg", last.toInt());
1674	}
1675	query.bindValue(":userid", user.toInt());
1676	query.bindValue(":firstmsg", first.toInt());
1677	query.bindValue(":limit", limit);
1678	safeExec(query);
1679
1680	watchQuery(query);
1681
1682	while (query.next()) {
1683	Message msg(QDateTime::fromTime_t(query.value(2).toInt()),
1684	bufferInfoHash[query.value(1).toInt()],
1685	(Message::Type)query.value(3).toUInt(),
1686	query.value(6).toString(),
1687	query.value(5).toString(),
1688	(Message::Flags)query.value(4).toUInt());
1689	msg.setMsgId(query.value(0).toInt());
1690	messagelist << msg;
1691	}
1692	}
1693	db.commit();
1694	unlock();
1695	return messagelist;
1696	}
1697
1698
1699	QString SqliteStorage::backlogFile()
1700	{
1701	return Quassel::configDirPath() + "quassel-storage.sqlite";
1702	}
1703
1704
1705	bool SqliteStorage::safeExec(QSqlQuery &query, int retryCount)
1706	{
1707	query.exec();
1708
1709	if (!query.lastError().isValid())
1710	return true;
1711
1712	switch (query.lastError().number()) {
1713	case 5: // SQLITE_BUSY 5 /* The database file is locked */
1714	case 6: // SQLITE_LOCKED 6 /* A table in the database is locked */
1715	if (retryCount < _maxRetryCount)
1716	return safeExec(query, retryCount + 1);
1717	default:
1718	return false;
1719	}
1720	}
1721
1722
1723	// ========================================
1724	// SqliteMigration
1725	// ========================================
1726	SqliteMigrationReader::SqliteMigrationReader()
1727	: SqliteStorage(),
1728	_maxId(0)
1729	{
1730	}
1731
1732
1733	void SqliteMigrationReader::setMaxId(MigrationObject mo)
1734	{
1735	QString queryString;
1736	switch (mo) {
1737	case Sender:
1738	queryString = "SELECT max(senderid) FROM sender";
1739	break;
1740	case Backlog:
1741	queryString = "SELECT max(messageid) FROM backlog";
1742	break;
1743	default:
1744	_maxId = 0;
1745	return;
1746	}
1747	QSqlQuery query = logDb().exec(queryString);
1748	query.first();
1749	_maxId = query.value(0).toInt();
1750	}
1751
1752
1753	bool SqliteMigrationReader::prepareQuery(MigrationObject mo)
1754	{
1755	setMaxId(mo);
1756
1757	switch (mo) {
1758	case QuasselUser:
1759	newQuery(queryString("migrate_read_quasseluser"), logDb());
1760	break;
1761	case Identity:
1762	newQuery(queryString("migrate_read_identity"), logDb());
1763	break;
1764	case IdentityNick:
1765	newQuery(queryString("migrate_read_identity_nick"), logDb());
1766	break;
1767	case Network:
1768	newQuery(queryString("migrate_read_network"), logDb());
1769	break;
1770	case Buffer:
1771	newQuery(queryString("migrate_read_buffer"), logDb());
1772	break;
1773	case Sender:
1774	newQuery(queryString("migrate_read_sender"), logDb());
1775	bindValue(0, 0);
1776	bindValue(1, stepSize());
1777	break;
1778	case Backlog:
1779	newQuery(queryString("migrate_read_backlog"), logDb());
1780	bindValue(0, 0);
1781	bindValue(1, stepSize());
1782	break;
1783	case IrcServer:
1784	newQuery(queryString("migrate_read_ircserver"), logDb());
1785	break;
1786	case UserSetting:
1787	newQuery(queryString("migrate_read_usersetting"), logDb());
1788	break;
1789	}
1790	return exec();
1791	}
1792
1793
1794	bool SqliteMigrationReader::readMo(QuasselUserMO &user)
1795	{
1796	if (!next())
1797	return false;
1798
1799	user.id = value(0).toInt();
1800	user.username = value(1).toString();
1801	user.password = value(2).toString();
1802	return true;
1803	}
1804
1805
1806	bool SqliteMigrationReader::readMo(IdentityMO &identity)
1807	{
1808	if (!next())
1809	return false;
1810
1811	identity.id = value(0).toInt();
1812	identity.userid = value(1).toInt();
1813	identity.identityname = value(2).toString();
1814	identity.realname = value(3).toString();
1815	identity.awayNick = value(4).toString();
1816	identity.awayNickEnabled = value(5).toInt() == 1 ? true : false;
1817	identity.awayReason = value(6).toString();
1818	identity.awayReasonEnabled = value(7).toInt() == 1 ? true : false;
1819	identity.autoAwayEnabled = value(8).toInt() == 1 ? true : false;
1820	identity.autoAwayTime = value(9).toInt();
1821	identity.autoAwayReason = value(10).toString();
1822	identity.autoAwayReasonEnabled = value(11).toInt() == 1 ? true : false;
1823	identity.detachAwayEnabled = value(12).toInt() == 1 ? true : false;
1824	identity.detachAwayReason = value(13).toString();
1825	identity.detchAwayReasonEnabled = value(14).toInt() == 1 ? true : false;
1826	identity.ident = value(15).toString();
1827	identity.kickReason = value(16).toString();
1828	identity.partReason = value(17).toString();
1829	identity.quitReason = value(18).toString();
1830	identity.sslCert = value(19).toByteArray();
1831	identity.sslKey = value(20).toByteArray();
1832	return true;
1833	}
1834
1835
1836	bool SqliteMigrationReader::readMo(IdentityNickMO &identityNick)
1837	{
1838	if (!next())
1839	return false;
1840
1841	identityNick.nickid = value(0).toInt();
1842	identityNick.identityId = value(1).toInt();
1843	identityNick.nick = value(2).toString();
1844	return true;
1845	}
1846
1847
1848	bool SqliteMigrationReader::readMo(NetworkMO &network)
1849	{
1850	if (!next())
1851	return false;
1852
1853	network.networkid = value(0).toInt();
1854	network.userid = value(1).toInt();
1855	network.networkname = value(2).toString();
1856	network.identityid = value(3).toInt();
1857	network.encodingcodec = value(4).toString();
1858	network.decodingcodec = value(5).toString();
1859	network.servercodec = value(6).toString();
1860	network.userandomserver = value(7).toInt() == 1 ? true : false;
1861	network.perform = value(8).toString();
1862	network.useautoidentify = value(9).toInt() == 1 ? true : false;
1863	network.autoidentifyservice = value(10).toString();
1864	network.autoidentifypassword = value(11).toString();
1865	network.useautoreconnect = value(12).toInt() == 1 ? true : false;
1866	network.autoreconnectinterval = value(13).toInt();
1867	network.autoreconnectretries = value(14).toInt();
1868	network.unlimitedconnectretries = value(15).toInt() == 1 ? true : false;
1869	network.rejoinchannels = value(16).toInt() == 1 ? true : false;
1870	network.connected = value(17).toInt() == 1 ? true : false;
1871	network.usermode = value(18).toString();
1872	network.awaymessage = value(19).toString();
1873	network.attachperform = value(20).toString();
1874	network.detachperform = value(21).toString();
1875	network.usesasl = value(22).toInt() == 1 ? true : false;
1876	network.saslaccount = value(23).toString();
1877	network.saslpassword = value(24).toString();
1878	return true;
1879	}
1880
1881
1882	bool SqliteMigrationReader::readMo(BufferMO &buffer)
1883	{
1884	if (!next())
1885	return false;
1886
1887	buffer.bufferid = value(0).toInt();
1888	buffer.userid = value(1).toInt();
1889	buffer.groupid = value(2).toInt();
1890	buffer.networkid = value(3).toInt();
1891	buffer.buffername = value(4).toString();
1892	buffer.buffercname = value(5).toString();
1893	buffer.buffertype = value(6).toInt();
1894	buffer.lastseenmsgid = value(7).toInt();
1895	buffer.markerlinemsgid = value(8).toInt();
1896	buffer.key = value(9).toString();
1897	buffer.joined = value(10).toInt() == 1 ? true : false;
1898	return true;
1899	}
1900
1901
1902	bool SqliteMigrationReader::readMo(SenderMO &sender)
1903	{
1904	int skipSteps = 0;
1905	while (!next()) {
1906	if (sender.senderId < _maxId) {
1907	bindValue(0, sender.senderId + (skipSteps * stepSize()));
1908	bindValue(1, sender.senderId + ((skipSteps + 1) * stepSize()));
1909	skipSteps++;
1910	if (!exec())
1911	return false;
1912	}
1913	else {
1914	return false;
1915	}
1916	}
1917
1918	sender.senderId = value(0).toInt();
1919	sender.sender = value(1).toString();
1920	return true;
1921	}
1922
1923
1924	bool SqliteMigrationReader::readMo(BacklogMO &backlog)
1925	{
1926	int skipSteps = 0;
1927	while (!next()) {
1928	if (backlog.messageid < _maxId) {
1929	bindValue(0, backlog.messageid.toInt() + (skipSteps * stepSize()));
1930	bindValue(1, backlog.messageid.toInt() + ((skipSteps + 1) * stepSize()));
1931	skipSteps++;
1932	if (!exec())
1933	return false;
1934	}
1935	else {
1936	return false;
1937	}
1938	}
1939
1940	backlog.messageid = value(0).toInt();
1941	backlog.time = QDateTime::fromTime_t(value(1).toInt()).toUTC();
1942	backlog.bufferid = value(2).toInt();
1943	backlog.type = value(3).toInt();
1944	backlog.flags = value(4).toInt();
1945	backlog.senderid = value(5).toInt();
1946	backlog.message = value(6).toString();
1947	return true;
1948	}
1949
1950
1951	bool SqliteMigrationReader::readMo(IrcServerMO &ircserver)
1952	{
1953	if (!next())
1954	return false;
1955
1956	ircserver.serverid = value(0).toInt();
1957	ircserver.userid = value(1).toInt();
1958	ircserver.networkid = value(2).toInt();
1959	ircserver.hostname = value(3).toString();
1960	ircserver.port = value(4).toInt();
1961	ircserver.password = value(5).toString();
1962	ircserver.ssl = value(6).toInt() == 1 ? true : false;
1963	ircserver.sslversion = value(7).toInt();
1964	ircserver.useproxy = value(8).toInt() == 1 ? true : false;
1965	ircserver.proxytype = value(9).toInt();
1966	ircserver.proxyhost = value(10).toString();
1967	ircserver.proxyport = value(11).toInt();
1968	ircserver.proxyuser = value(12).toString();
1969	ircserver.proxypass = value(13).toString();
1970	return true;
1971	}
1972
1973
1974	bool SqliteMigrationReader::readMo(UserSettingMO &userSetting)
1975	{
1976	if (!next())
1977	return false;
1978
1979	userSetting.userid = value(0).toInt();
1980	userSetting.settingname = value(1).toString();
1981	userSetting.settingvalue = value(2).toByteArray();
1982
1983	return true;
1984	}
1985

---

Generated on 2014-Aug-04 from project quassel revision 0.11-pre-104-g12e650f
Powered by Code Browser 1.7