/soc/2015/igor.gajowiak/chatlog: 8099f786d1b7: Implemented SQL o...

Igor Gajowiak <[email protected]>
Newsgroups gmane.comp.gnome.gaim.cvs
Message-ID <[email protected]> 
Changeset: 8099f786d1b780757ed5bcabe94ad60d5d874a8a
Author:	 Igor Gajowiak <[email protected]>
Date:	 2015-07-26 22:24 +0200
Branch:	 default
URL: https://hg.pidgin.im/soc/2015/igor.gajowiak/chatlog/rev/8099f786d1b7

Description:

Implemented SQL optimizations.

diffstat:

 libpurple/plugins/log/logsqlite.c |  142 ++++++++++++++++++++++++++++++++-----
 1 files changed, 121 insertions(+), 21 deletions(-)

diffs (235 lines):

diff --git a/libpurple/plugins/log/logsqlite.c b/libpurple/plugins/log/logsqlite.c
--- a/libpurple/plugins/log/logsqlite.c
+++ b/libpurple/plugins/log/logsqlite.c
@@ -47,6 +47,21 @@ static sqlite3 *sqlitelog_db_handle = NU
 /* Prepared queries                                                       */
 /**************************************************************************/
 
+static sqlite3_stmt *begin_transation_q = NULL;
+
+static const char *begin_transation_q_str =
+	"BEGIN TRANSACTION;";
+
+static sqlite3_stmt *commit_transation_q = NULL;
+
+static const char *commit_transation_q_str =
+	"COMMIT TRANSACTION;";
+
+static sqlite3_stmt *rollback_transation_q = NULL;
+
+static const char *rollback_transation_q_str =
+	"ROLLBACK TRANSACTION;";
+
 static sqlite3_stmt *get_accunt_id_q = NULL;
 
 static const char *get_accunt_id_q_str =
@@ -76,6 +91,17 @@ typedef enum
 	INSERT_ACCOUNT_QUERY_PARAM_PROTOCOLID = 2,
 } InsertAccountQueryParam;
 
+static sqlite3_stmt *insert_buddy_q = NULL;
+
+static const char *insert_buddy_q_str =
+	"INSERT OR IGNORE INTO Buddies (Name, AccountId) VALUES(?1, ?2);";
+
+typedef enum
+{
+	INSERT_BUDDY_QUERY_PARAM_NAME      = 1,
+	INSERT_BUDDY_QUERY_PARAM_ACCOUNTID = 2
+} InsertBuddyQueryParam;
+
 static sqlite3_stmt *insert_message_q = NULL;
 
 static const char *insert_message_q_str =
@@ -181,21 +207,14 @@ typedef enum
 static sqlite3_stmt *get_all_buddies_q = NULL;
 
 static const char *get_all_buddies_q_str =
-	"SELECT Username, ProtocolId, Author Buddy FROM "
-	"Messages JOIN Accounts on Accounts.Id = AccountId "
-	"WHERE Send = 0 "
-	"GROUP BY Username, ProtocolId, Buddy "
-	"UNION "
-	"SELECT Username, ProtocolId, Recipient Buddy FROM "
-	"Messages JOIN Accounts on Accounts.Id = AccountId "
-	"WHERE Send = 1 "
-	"GROUP BY Username, ProtocolId, Buddy;";
+	"SELECT Username, ProtocolId, Name FROM "
+	"Accounts JOIN Buddies ON Accounts.Id = Buddies.AccountId";
 
 typedef enum
 {
-	GET_ALL_BUDDIES_QUERY_COL_USERNAME = 0,
-	GET_ALL_BUDDIES_QUERY_COL_PROTOCOL = 1,
-	GET_ALL_BUDDIES_QUERY_COL_BUDDY    = 2,
+	GET_ALL_BUDDIES_QUERY_COL_USERNAME   = 0,
+	GET_ALL_BUDDIES_QUERY_COL_PROTOCOLID = 1,
+	GET_ALL_BUDDIES_QUERY_COL_BUDDY      = 2,
 } GetAllBuddiesQueryCol;
 
 static void
@@ -214,8 +233,12 @@ sqlitelog_finalize_query(sqlite3_stmt **
 static void
 sqlitelog_finalize_queries()
 {
+	sqlitelog_finalize_query(&begin_transation_q);
+	sqlitelog_finalize_query(&commit_transation_q);
+	sqlitelog_finalize_query(&rollback_transation_q);
 	sqlitelog_finalize_query(&get_accunt_id_q);
 	sqlitelog_finalize_query(&insert_account_q);
+	sqlitelog_finalize_query(&insert_buddy_q);
 	sqlitelog_finalize_query(&insert_message_q);
 	sqlitelog_finalize_query(&get_unseen_msgs_q);
 	sqlitelog_finalize_query(&get_all_msgs_q);
@@ -250,8 +273,14 @@ sqlitelog_prepare_query(const char *quer
 static gboolean
 sqlitelog_prepare_queries()
 {
-	if (!sqlitelog_prepare_query(get_accunt_id_q_str, &get_accunt_id_q) ||
+	if (!sqlitelog_prepare_query(begin_transation_q_str, &begin_transation_q) ||
+		!sqlitelog_prepare_query(commit_transation_q_str,
+			&commit_transation_q) ||
+		!sqlitelog_prepare_query(rollback_transation_q_str,
+			&rollback_transation_q) ||
+		!sqlitelog_prepare_query(get_accunt_id_q_str, &get_accunt_id_q) ||
 	    !sqlitelog_prepare_query(insert_account_q_str, &insert_account_q) ||
+	    !sqlitelog_prepare_query(insert_buddy_q_str, &insert_buddy_q) ||
 		!sqlitelog_prepare_query(insert_message_q_str, &insert_message_q) ||
 		!sqlitelog_prepare_query(get_unseen_msgs_q_str, &get_unseen_msgs_q) ||
 		!sqlitelog_prepare_query(get_all_msgs_q_str, &get_all_msgs_q) ||
@@ -283,7 +312,20 @@ sqlitelog_create_tables(sqlite3 *db_hand
 		"Username TEXT NOT NULL,"
 		"ProtocolId TEXT NOT NULL); "
 
-		"CREATE UNIQUE INDEX AccountsIndex On Accounts(Username, ProtocolId); "
+		/* For fetching account ID */
+		"CREATE UNIQUE INDEX AccountsIndex ON Accounts(Username, ProtocolId); "
+
+		/* 
+		 * This table exists only for performance reasons,
+		 * because grouping by Author/Recipient does a linear
+		 * scan on Messages.
+		 */
+		"CREATE TABLE Buddies("
+		"Id INTEGER PRIMARY KEY AUTOINCREMENT, "
+		"Name TEXT NOT NULL, "
+		"AccountId INTEGER NOT NULL, "
+		"FOREIGN KEY(AccountId) REFERENCES Accounts(Id), "
+		"UNIQUE(Name, AccountId)); "
 
 		"CREATE TABLE Messages("
 		"Id INTEGER PRIMARY KEY AUTOINCREMENT,"
@@ -298,15 +340,23 @@ sqlitelog_create_tables(sqlite3 *db_hand
 		"AccountId INTEGER NOT NULL,"
 		"FOREIGN KEY(AccountId) REFERENCES Accounts(Id)); "
 
-		"CREATE INDEX AuthorIndex ON Messages (Author); "
-		"CREATE INDEX RecipientIndex ON Messages (Recipient); "
-		"CREATE INDEX SeenIndex ON Messages (Seen); "
-		"CREATE INDEX SendIndex ON Messages (Send); "
-		"CREATE INDEX MsgTimeIndex ON Messages (MsgTime); "
+		/* For fetching unseen messages */
+		"CREATE INDEX SeenIndex ON Messages (Seen, AccountId, MsgTime); "
+
+		/* For fetching all and older messages */
+		"CREATE INDEX AuthorIndex ON Messages (Author, AccountId, MsgTime); "
+		"CREATE INDEX RecipientIndex ON Messages (Recipient, AccountId, "
+		"MsgTime); "
 
 		"CREATE TRIGGER AccountDeleted AFTER DELETE ON Accounts "
 		"BEGIN "
+		"DELETE FROM Buddies WHERE AccountId = OLD.Id; "
 		"DELETE FROM Messages WHERE AccountId = OLD.Id; "
+		"END; "
+
+		"CREATE TRIGGER BuddyDeleted AFTER DELETE ON Buddies "
+		"BEGIN "
+		"DELETE FROM Messages WHERE Author = OLD.Name OR Recipient = OLD.Name; "
 		"END; ";
 
 	return sqlite3_exec(db_handle, query, NULL, NULL, NULL);
@@ -419,6 +469,30 @@ sqlitelog_insert_account(const PurpleAcc
 }
 
 static gboolean
+sqlitelog_insert_buddy(unsigned account_id, const char *name)
+{
+	g_assert(account_id != SQLITELOG_DB_ID_NONE);
+	g_assert(name);
+
+	g_assert(insert_buddy_q);
+
+	if (sqlite3_bind_text(insert_buddy_q, INSERT_BUDDY_QUERY_PARAM_NAME,
+			name, -1, SQLITE_STATIC) != SQLITE_OK ||
+		sqlite3_bind_int64(insert_buddy_q, INSERT_BUDDY_QUERY_PARAM_ACCOUNTID,
+			account_id) != SQLITE_OK) {
+		return FALSE;
+	}
+
+	int rc = sqlite3_step(insert_buddy_q);
+	sqlite3_reset(insert_buddy_q);
+
+	if (rc != SQLITE_DONE)
+		return FALSE;
+
+	return TRUE;
+}
+
+static gboolean
 sqlitelog_insert_message(PurpleAccount *account, unsigned account_id,
 	const PurpleMessage *msg, unsigned *id)
 {
@@ -710,7 +784,7 @@ sqlitelog_get_all_buddies_impl(GList **r
 		const char *username = (const char*) sqlite3_column_text(
 			get_all_buddies_q, GET_ALL_BUDDIES_QUERY_COL_USERNAME);
 		const char *protocol = (const char*) sqlite3_column_text(
-			get_all_buddies_q, GET_ALL_BUDDIES_QUERY_COL_PROTOCOL);
+			get_all_buddies_q, GET_ALL_BUDDIES_QUERY_COL_PROTOCOLID);
 		const char *buddy = (const char*) sqlite3_column_text(
 			get_all_buddies_q, GET_ALL_BUDDIES_QUERY_COL_BUDDY);
 
@@ -800,17 +874,43 @@ sqlitelog_log_im(PurpleAccount *account,
 		return FALSE;
 	}
 
+	const char *buddy_name =
+		(purple_message_get_flags(message) & PURPLE_MESSAGE_SEND) ?
+		purple_message_get_recipient(message) :
+		purple_message_get_author(message);
+
+	if (!buddy_name)
+		return FALSE;
+
+	buddy_name = purple_normalize(account, buddy_name);
+
 	unsigned account_id;
 	if (!sqlitelog_get_account_id(account, &account_id))
 		return FALSE;
 
+	/* Begin transaction */
+	if (sqlite3_step(begin_transation_q) != SQLITE_DONE)
+		return FALSE;
+
 	if (account_id == SQLITELOG_DB_ID_NONE &&
 		!sqlitelog_insert_account(account, &account_id)) {
+			sqlite3_step(rollback_transation_q);
 			return FALSE;
 	}
 
+	if (!sqlitelog_insert_buddy(account_id, buddy_name)) {
+		sqlite3_step(rollback_transation_q);
+		return FALSE;
+	}
+
 	unsigned message_id;
-	if (!sqlitelog_insert_message(account, account_id, message, &message_id))
+	if (!sqlitelog_insert_message(account, account_id, message, &message_id)) {
+		sqlite3_step(rollback_transation_q);
+		return FALSE;
+	}
+
+	/* Commit transation */
+	if (sqlite3_step(commit_transation_q) != SQLITE_DONE)
 		return FALSE;
 
 	g_object_set_data(G_OBJECT(message), SQLITELOG_MESSAGE_ID_ATTR,

_______________________________________________
Commits mailing list
[email protected]
https://pidgin.im/cgi-bin/mailman/listinfo/commits
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.