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

Igor Gajowiak igor.gajowiak at gmail.com
Sun Jul 26 16:24:26 EDT 2015


Changeset: 8099f786d1b780757ed5bcabe94ad60d5d874a8a
Author:	 Igor Gajowiak <igor.gajowiak at gmail.com>
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,



More information about the Commits mailing list