/soc/2015/igor.gajowiak/chatlog: b77bc1210637: SQLite log optimi...

Igor Gajowiak igor.gajowiak at gmail.com
Sun Jul 19 08:07:11 EDT 2015


Changeset: b77bc1210637be15a44a034d4f4db7afc4e16bf7
Author:	 Igor Gajowiak <igor.gajowiak at gmail.com>
Date:	 2015-07-19 14:06 +0200
Branch:	 default
URL: https://hg.pidgin.im/soc/2015/igor.gajowiak/chatlog/rev/b77bc1210637

Description:

SQLite log optimization: reusing prepared queries

diffstat:

 libpurple/accounts.c              |    7 +-
 libpurple/conversation.c          |    9 +-
 libpurple/genericlog.h            |    3 +
 libpurple/message.c               |   15 +
 libpurple/message.h               |   22 +
 libpurple/plugins/log/logsqlite.c |  454 ++++++++++++++++++++++++++++++-------
 6 files changed, 411 insertions(+), 99 deletions(-)

diffs (truncated from 702 to 300 lines):

diff --git a/libpurple/accounts.c b/libpurple/accounts.c
--- a/libpurple/accounts.c
+++ b/libpurple/accounts.c
@@ -924,7 +924,7 @@ password_migration_cb(PurpleAccount *acc
 }
 
 static void
-show_unread_im_msgs(PurpleConnection *gc)
+show_unseen_im_msgs(PurpleConnection *gc)
 {
 	PurpleAccount *account = purple_connection_get_account(gc);
 
@@ -937,6 +937,9 @@ show_unread_im_msgs(PurpleConnection *gc
 	for(GList *it = unseen_msgs; it != NULL; it = it->next) {
 		PurpleMessage *msg = it->data;
 
+		purple_message_set_flags(msg, purple_message_get_flags(msg) |
+			PURPLE_MESSAGE_NO_LOG);
+
 		// Create or get a conversation
 		PurpleIMConversation *conv = purple_im_conversation_new(
 			account, purple_message_get_author(msg));
@@ -1036,7 +1039,7 @@ purple_accounts_init(void)
 	purple_signal_connect(conn_handle, "signed-on", handle,
 	                      PURPLE_CALLBACK(signed_on_cb), NULL);
 	purple_signal_connect(conn_handle, "signed-on", handle,
-						  PURPLE_CALLBACK(show_unread_im_msgs), NULL);
+						  PURPLE_CALLBACK(show_unseen_im_msgs), NULL);
 	purple_signal_connect(conn_handle, "signed-off", handle,
 	                      PURPLE_CALLBACK(signed_off_cb), NULL);
 	purple_signal_connect(conn_handle, "connection-error", handle,
diff --git a/libpurple/conversation.c b/libpurple/conversation.c
--- a/libpurple/conversation.c
+++ b/libpurple/conversation.c
@@ -622,6 +622,13 @@ void
 				purple_message_get_contents(pmsg));
 			log = log->next;
 		}
+
+		GError *error;
+		if (!purple_genericlog_logim(account, pmsg, &error)) {
+			purple_debug_error("conversation", "Logging failed: %s (code=%d)",
+				error->message, error->code);
+			g_error_free(error);
+		}
 	}
 
 	if (ops) {
@@ -633,8 +640,6 @@ void
 			ops->write_conv(conv, pmsg);
 	}
 
-	// TODO: add error handling?
-	purple_genericlog_logim(account, pmsg, NULL);
 	add_message_to_history(conv, pmsg);
 
 	purple_signal_emit(purple_conversations_get_handle(),
diff --git a/libpurple/genericlog.h b/libpurple/genericlog.h
--- a/libpurple/genericlog.h
+++ b/libpurple/genericlog.h
@@ -89,6 +89,9 @@ struct _PurpleGenericLogClass
 
 #define PURPLE_GENERICLOG_DEFAULT "genericlog-sqlitelog"
 
+#define PURPLE_GENERICLOG_PROPERTY_ID   "id"
+#define PURPLE_GENERICLOG_PROPERTY_NAME "name"
+
 /**************************************************************************/
 /* PurpleGenericLog intance methods                                       */
 /**************************************************************************/
diff --git a/libpurple/message.c b/libpurple/message.c
--- a/libpurple/message.c
+++ b/libpurple/message.c
@@ -63,6 +63,21 @@ static GHashTable *messages = NULL;
  ******************************************************************************/
 
 PurpleMessage *
+purple_message_new(const gchar *author, const gchar *author_alias,
+	const gchar *recipient, const gchar *contents, guint64 timestamp,
+	PurpleMessageFlags flags)
+{
+	return g_object_new(PURPLE_TYPE_MESSAGE,
+		"author", author,
+		"author-alias", author_alias,
+		"recipient", recipient,
+		"contents", contents,
+		"time", timestamp,
+		"flags", flags,
+		NULL);
+}
+
+PurpleMessage *
 purple_message_new_outgoing(const gchar *who, const gchar *contents,
 	PurpleMessageFlags flags)
 {
diff --git a/libpurple/message.h b/libpurple/message.h
--- a/libpurple/message.h
+++ b/libpurple/message.h
@@ -84,6 +84,28 @@ GType
 purple_message_get_type(void);
 
 /**
+ * purple_message_new:
+ * @author: Message's author.
+ * @author_alias: Author's alias.
+ * @recipient: Message's recipient.
+ * @contents: The contents of a message.
+ * @timestamp: The time of transmitting a message.
+ * @flags: The message flags.
+ *
+ * Deserializes a message.
+ *
+ * This function should be used when a complete
+ * message deserialization is needed. For sending or receiving a message
+ * see purple_message_new_outgoing, purple_message_new_incoming.
+ *
+ * Returns: the new #PurpleMessage.
+ */
+PurpleMessage *
+purple_message_new(const gchar *author, const gchar *author_alias,
+	const gchar *recipient, const gchar *contents, guint64 timestamp,
+	PurpleMessageFlags flags);
+
+/**
  * purple_message_new_outgoing:
  * @who: Message's recipient.
  * @contents: The contents of a message.
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
@@ -39,8 +39,160 @@
 
 #define SQLITELOG_MESSAGE_ID_ATTR "sqlitelog-msg-id"
 
+#define SQLITELOG_DEBUG_CATEGORY  "sqlite-log"
+
 static sqlite3 *sqlitelog_db_handle = NULL;
 
+/**************************************************************************/
+/* Prepared queries                                                       */
+/**************************************************************************/
+
+static sqlite3_stmt *get_accunt_id_q = NULL;
+
+static const char *get_accunt_id_q_str =
+	"SELECT Id FROM Accounts "
+	"WHERE Username = ?1 AND ProtocolId = ?2;";
+
+typedef enum
+{
+	GET_ACCOUNT_ID_QUERY_PARAM_USERNAME   = 1,
+	GET_ACCOUNT_ID_QUERY_PARAM_PROTOCOLID = 2,
+} GetAccountIdQueryParam;
+
+typedef enum
+{
+	GET_ACCOUNT_ID_QUERY_COL_ID = 0
+} GetAccountIdQueryCol;
+
+static sqlite3_stmt *insert_account_q = NULL;
+
+static const char *insert_account_q_str =
+	"INSERT INTO Accounts "
+	"(Username, ProtocolId) VALUES(?1, ?2);";
+
+typedef enum
+{
+	INSERT_ACCOUNT_QUERY_PARAM_USERNAME   = 1,
+	INSERT_ACCOUNT_QUERY_PARAM_PROTOCOLID = 2,
+} InsertAccountQueryParam;
+
+static sqlite3_stmt *insert_message_q = NULL;
+
+static const char *insert_message_q_str =
+	"INSERT INTO Messages (Author, AuthorAlias, Recipient, "
+	"Contents, MsgTime, Flags, Seen, AccountId) "
+	"VALUES(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8);";
+
+typedef enum
+{
+	INSERT_MESSAGE_QUERY_PARAM_AUTHOR      = 1,
+	INSERT_MESSAGE_QUERY_PARAM_AUTHORALIAS = 2,
+	INSERT_MESSAGE_QUERY_PARAM_RECIPIENT   = 3,
+	INSERT_MESSAGE_QUERY_PARAM_CONTENTS    = 4,
+	INSERT_MESSAGE_QUERY_PARAM_MSGTIME     = 5,
+	INSERT_MESSAGE_QUERY_PARAM_FLAGS       = 6,
+	INSERT_MESSAGE_QUERY_PARAM_SEEN        = 7,
+	INSERT_MESSAGE_QUERY_PARAM_ACCOUNTID   = 8,
+} InsertMessageQueryParam;
+
+static sqlite3_stmt *get_unseen_msgs_q = NULL;
+
+static const char *get_unseen_msgs_q_str =
+	"SELECT Messages.Id, Author, AuthorAlias, Recipient, Contents, "
+	"MsgTime, Flags, Seen "
+	"FROM Messages JOIN Accounts ON Accounts.Id = AccountId "
+	"WHERE Username = ?1 AND ProtocolId = ?2 AND Seen = 0 "
+	"ORDER BY Messages.Id DESC;";
+
+typedef enum
+{
+	GET_UNSEEN_MSGS_QUERY_PARAM_USERNAME   = 1,
+	GET_UNSEEN_MSGS_QUERY_PARAM_PROTOCOLID = 2,
+} GetUnseenMsgsQueryParam;
+
+typedef enum
+{
+	GET_UNSEEN_MSGS_QUERY_COL_ID          = 0,
+	GET_UNSEEN_MSGS_QUERY_COL_AUTHOR      = 1,
+	GET_UNSEEN_MSGS_QUERY_COL_AUTHORALIAS = 2,
+	GET_UNSEEN_MSGS_QUERY_COL_RECIPIENT   = 3,
+	GET_UNSEEN_MSGS_QUERY_COL_CONTENTS    = 4,
+	GET_UNSEEN_MSGS_QUERY_COL_MSGTIME     = 5,
+	GET_UNSEEN_MSGS_QUERY_COL_FLAGS       = 6,
+	GET_UNSEEN_MSGS_QUERY_COL_SEEN        = 7,
+} GetUnseenMsgsQueryCol;
+
+static sqlite3_stmt *mark_as_seen_q = NULL;
+
+static const char *mark_as_seen_q_str =
+	"UPDATE Messages SET Seen = 1 WHERE Id = ?1;";
+
+typedef enum
+{
+	MARK_AS_SEEN_QUERY_PARAM_ID = 1
+} MarkAsSeenQueryParam;
+
+static void
+sqlitelog_finalize_query(sqlite3_stmt **query)
+{
+	g_assert(query);
+
+	if (sqlite3_finalize(*query) != SQLITE_OK) {
+		purple_debug_error(SQLITELOG_DEBUG_CATEGORY,
+			"Failed to finalize query %p", *query);
+	}
+
+	*query = NULL;
+}
+
+static void
+sqlitelog_finalize_queries()
+{
+	sqlitelog_finalize_query(&get_accunt_id_q);
+	sqlitelog_finalize_query(&insert_account_q);
+	sqlitelog_finalize_query(&insert_message_q);
+	sqlitelog_finalize_query(&get_unseen_msgs_q);
+	sqlitelog_finalize_query(&mark_as_seen_q);
+}
+
+static gboolean
+sqlitelog_prepare_query(const char *query_str, sqlite3_stmt **query)
+{
+	g_assert(query_str);
+	g_assert(query);
+
+	sqlite3_stmt *handle;
+
+	int rc = sqlite3_prepare_v2(sqlitelog_db_handle,
+		query_str, -1, &handle, NULL);
+	if (rc != SQLITE_OK) {
+		purple_debug_error(SQLITELOG_DEBUG_CATEGORY,
+			"Failed to prepare query '%s', details: %s", query_str,
+			sqlite3_errmsg(sqlitelog_db_handle));
+
+		return FALSE;
+	}
+
+	*query = handle;
+	return TRUE;
+}
+
+static gboolean
+sqlitelog_prepare_queries()
+{
+	if (!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_message_q_str, &insert_message_q) ||
+		!sqlitelog_prepare_query(get_unseen_msgs_q_str, &get_unseen_msgs_q) ||
+		!sqlitelog_prepare_query(mark_as_seen_q_str, &mark_as_seen_q)) {
+
+		sqlitelog_finalize_queries();
+		return FALSE;
+	}
+
+	return TRUE;
+}
+
 static gboolean
 sqlitelog_db_file_exists(const gchar *db_path)
 {
@@ -62,7 +214,7 @@ sqlitelog_create_tables(sqlite3 *db_hand
 		"Id INTEGER PRIMARY KEY AUTOINCREMENT,"
 		"Author TEXT,"
 		"AuthorAlias TEXT,"
-		"Recipient TEXT NOT NULL,"
+		"Recipient TEXT,"
 		"Contents TEXT NOT NULL,"
 		"MsgTime INTEGER NOT NULL,"
 		"Flags INTEGER NOT NULL,"



More information about the Commits mailing list