/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