GNOME.org
 

[almanah] Use iterators for large database queries

commit fc00ec9948692fffb966eaaa75bfb6d073932f5b
Author: Philip Withnall <philip tecnocode co uk>
Date:   Sat May 8 21:13:33 2010 +0100

    Use iterators for large database queries
    
    Use iterators for almanah_storage_manager_get_entries() and
    almanah_storage_manager_search_entries(), rather than allocating all the
    results at once into a list. This saves memory and could be used to make the
    interface more responsive for long-running operations.

 src/import-export-dialog.c |   23 ++--
 src/search-dialog.c        |   20 ++--
 src/storage-manager.c      |  272 +++++++++++++++++++++++++++++---------------
 src/storage-manager.h      |   15 ++-
 4 files changed, 214 insertions(+), 116 deletions(-)
---
diff --git a/src/import-export-dialog.c b/src/import-export-dialog.c
index ef98d62..b962207 100644
--- a/src/import-export-dialog.c
+++ b/src/import-export-dialog.c
@@ -422,8 +422,9 @@ static gboolean
 export_text_files (AlmanahImportExportDialog *self, GError **error)
 {
 	AlmanahImportExportDialogPrivate *priv = self->priv;
+	AlmanahStorageManagerIter iter;
 	GFile *folder;
-	GSList *entries;
+	AlmanahEntry *entry;
 	GtkTextBuffer *buffer;
 	gboolean success = FALSE;
 	GError *child_error = NULL;
@@ -435,11 +436,9 @@ export_text_files (AlmanahImportExportDialog *self, GError **error)
 	/* Build a text buffer to use when getting all the entries */
 	buffer = gtk_text_buffer_new (NULL);
 
-	/* Get the list of entries */
-	entries = almanah_storage_manager_get_entries (almanah->storage_manager);
-
-	for (; entries != NULL; entries = g_slist_delete_link (entries, entries)) {
-		AlmanahEntry *entry = ALMANAH_ENTRY (entries->data);
+	/* Iterate through the entries */
+	almanah_storage_manager_iter_init (&iter);
+	while ((entry = almanah_storage_manager_get_entries (almanah->storage_manager, &iter)) != NULL) {
 		GDate date;
 		gchar *filename, *content;
 		GFile *file;
@@ -499,8 +498,10 @@ import_database (AlmanahImportExportDialog *self, AlmanahImportResultsDialog *re
 	GFileInfo *file_info;
 	gchar *path;
 	const gchar *display_name;
-	GSList *entries, *i, *definitions;
+	GSList *i, *definitions;
+	AlmanahEntry *entry;
 	AlmanahStorageManager *database;
+	AlmanahStorageManagerIter iter;
 	AlmanahImportExportDialogPrivate *priv = self->priv;
 
 	/* Get the database file to import */
@@ -523,13 +524,12 @@ import_database (AlmanahImportExportDialog *self, AlmanahImportResultsDialog *re
 		return FALSE;
 	}
 
-	/* Query for every entry */
-	entries = almanah_storage_manager_get_entries (database);
-	for (i = entries; i != NULL; i = i->next) {
+	/* Iterate through every entry */
+	almanah_storage_manager_iter_init (&iter);
+	while ((entry = almanah_storage_manager_get_entries (database, &iter)) != NULL) {
 		GDate date;
 		gchar *message = NULL;
 		AlmanahImportStatus status;
-		AlmanahEntry *entry = ALMANAH_ENTRY (i->data);
 
 		almanah_entry_get_date (entry, &date);
 
@@ -539,7 +539,6 @@ import_database (AlmanahImportExportDialog *self, AlmanahImportResultsDialog *re
 
 		g_object_unref (entry);
 	}
-	g_slist_free (entries);
 
 	/* Query for every definition */
 	definitions = almanah_storage_manager_get_definitions (database);
diff --git a/src/search-dialog.c b/src/search-dialog.c
index ddcd6db..2cc8fbe 100644
--- a/src/search-dialog.c
+++ b/src/search-dialog.c
@@ -140,26 +140,28 @@ sd_response_cb (GtkDialog *dialog, gint response_id, AlmanahSearchDialog *search
 void
 sd_search_button_clicked_cb (GtkButton *self, AlmanahSearchDialog *search_dialog)
 {
-	GSList *results, *i;
-	GtkTreeIter iter;
+	AlmanahEntry *entry;
+	AlmanahStorageManagerIter iter;
 	AlmanahSearchDialogPrivate *priv = search_dialog->priv;
+	const gchar *search_string = gtk_entry_get_text (priv->sd_search_entry);
 
+	/* Clear the results store of previous search results first */
 	gtk_list_store_clear (search_dialog->priv->sd_results_store);
-	results = almanah_storage_manager_search_entries (almanah->storage_manager, gtk_entry_get_text (priv->sd_search_entry));
 
-	for (i = results; i != NULL; i = i->next) {
-		AlmanahEntry *entry;
+	/* Search over all entries */
+	almanah_storage_manager_iter_init (&iter);
+	while ((entry = almanah_storage_manager_search_entries (almanah->storage_manager, search_string, &iter)) != NULL) {
 		GDate date;
 		gchar formatted_date[100];
+		GtkTreeIter tree_iter;
 
-		entry = ALMANAH_ENTRY (i->data);
 		almanah_entry_get_date (entry, &date);
 
 		/* Translators: This is a strftime()-format string for the dates displayed in search results. */
 		g_date_strftime (formatted_date, sizeof (formatted_date), _("%A, %e %B %Y"), &date);
 
-		gtk_list_store_append (priv->sd_results_store, &iter);
-		gtk_list_store_set (priv->sd_results_store, &iter,
+		gtk_list_store_append (priv->sd_results_store, &tree_iter);
+		gtk_list_store_set (priv->sd_results_store, &tree_iter,
 		                    0, g_date_get_day (&date),
 		                    1, g_date_get_month (&date),
 		                    2, g_date_get_year (&date),
@@ -169,8 +171,6 @@ sd_search_button_clicked_cb (GtkButton *self, AlmanahSearchDialog *search_dialog
 
 		g_object_unref (entry);
 	}
-
-	g_slist_free (results);
 }
 
 static void
diff --git a/src/storage-manager.c b/src/storage-manager.c
index e38f2e2..7085f9a 100644
--- a/src/storage-manager.c
+++ b/src/storage-manager.c
@@ -861,144 +861,232 @@ almanah_storage_manager_set_entry (AlmanahStorageManager *self, AlmanahEntry *en
 }
 
 /**
+ * almanah_storage_manager_iter_init:
+ * @iter: an #AlmanahStorageManagerIter to initialise
+ *
+ * Initialises the given iterator so it can be used by #AlmanahStorageManager functions. Typically, initialisers are allocated on the stack, so need
+ * explicitly initialising before being passed to functions such as almanah_storage_manager_get_entries().
+ *
+ * Since: 0.8.0
+ **/
+void
+almanah_storage_manager_iter_init (AlmanahStorageManagerIter *iter)
+{
+	g_return_if_fail (iter != NULL);
+
+	iter->statement = NULL;
+	iter->user_data = NULL;
+	iter->finished = FALSE;
+}
+
+typedef struct {
+	GtkTextBuffer *text_buffer;
+	gchar *search_string;
+} SearchData;
+
+/**
  * almanah_storage_manager_search_entries:
  * @self: an #AlmanahStorageManager
  * @search_string: string for which to search in entry content
- *
+ * TODO
  * Searches for @search_string in the content in entries in the database, and returns the results. If there are no results, %NULL will be returned.
  *
  * The results are returned in descending date order.
  *
  * Return value: a #GSList of #AlmanahEntry<!-- -->s, or %NULL; unref elements with g_object_unref(); free list with g_slist_free()
  **/
-GSList *
-almanah_storage_manager_search_entries (AlmanahStorageManager *self, const gchar *search_string)
+AlmanahEntry *
+almanah_storage_manager_search_entries (AlmanahStorageManager *self, const gchar *search_string, AlmanahStorageManagerIter *iter)
 {
 	sqlite3_stmt *statement;
 	GtkTextBuffer *text_buffer;
-	GSList *matches = NULL;
 
-	/* Prepare the statement. Query in ascending date order, and then reverse the results by prepending them to the list as we build it,
-	 * rather than appending them. */
-	if (sqlite3_prepare_v2 (self->priv->connection,
-	                        "SELECT content, day, month, year, is_important, edited_day, edited_month, edited_year FROM entries "
-	                        "ORDER BY year ASC, month ASC, day ASC", -1,
-	                        &statement, NULL) != SQLITE_OK) {
+	g_return_val_if_fail (ALMANAH_IS_STORAGE_MANAGER (self), NULL);
+	g_return_val_if_fail (iter != NULL, NULL);
+	g_return_val_if_fail (iter->statement != NULL || search_string != NULL, NULL);
+	g_return_val_if_fail (iter->statement == NULL || iter->user_data != NULL, NULL);
+
+	if (iter->finished == TRUE)
 		return NULL;
+
+	if (iter->statement == NULL) {
+		SearchData *data;
+
+		/* Prepare the statement. */
+		if (sqlite3_prepare_v2 (self->priv->connection,
+			                "SELECT content, day, month, year, is_important, edited_day, edited_month, edited_year FROM entries "
+			                "ORDER BY year DESC, month DESC, day DESC", -1,
+			                (sqlite3_stmt**) &(iter->statement), NULL) != SQLITE_OK) {
+			return NULL;
+		}
+
+		/* Set up persistent data for the operation */
+		data = g_slice_new (SearchData);
+		data->text_buffer = gtk_text_buffer_new (NULL);
+		data->search_string = g_strdup (search_string);
+		iter->user_data = data;
 	}
 
-	text_buffer = gtk_text_buffer_new (NULL);
+	statement = iter->statement;
+	text_buffer = ((SearchData*) iter->user_data)->text_buffer;
+	search_string = ((SearchData*) iter->user_data)->search_string;
 
 	/* Execute the statement */
-	while (sqlite3_step (statement) == SQLITE_ROW) {
-		AlmanahEntry *entry;
-		GDate date, last_edited;
-		GtkTextIter iter;
-
-		g_date_set_dmy (&date, sqlite3_column_int (statement, 1), sqlite3_column_int (statement, 2), sqlite3_column_int (statement, 3));
-		entry = almanah_entry_new (&date);
-		almanah_entry_set_data (entry, sqlite3_column_blob (statement, 0), sqlite3_column_bytes (statement, 0));
-		almanah_entry_set_is_important (entry, (sqlite3_column_int (statement, 4) == 1) ? TRUE : FALSE);
-
-		if (g_date_valid_dmy (sqlite3_column_int (statement, 2),
-		                      sqlite3_column_int (statement, 3),
-		                      sqlite3_column_int (statement, 4)) == TRUE) {
-			g_date_set_dmy (&last_edited,
-				        sqlite3_column_int (statement, 5),
-				        sqlite3_column_int (statement, 6),
-				        sqlite3_column_int (statement, 7));
-			almanah_entry_set_last_edited (entry, &last_edited);
-		}
+	switch (sqlite3_step (statement)) {
+		case SQLITE_ROW: {
+			AlmanahEntry *entry;
+			GDate date, last_edited;
+			GtkTextIter text_iter;
+
+			g_date_set_dmy (&date,
+			                sqlite3_column_int (statement, 1),
+			                sqlite3_column_int (statement, 2),
+			                sqlite3_column_int (statement, 3));
+
+			entry = almanah_entry_new (&date);
+			almanah_entry_set_data (entry, sqlite3_column_blob (statement, 0), sqlite3_column_bytes (statement, 0));
+			almanah_entry_set_is_important (entry, (sqlite3_column_int (statement, 4) == 1) ? TRUE : FALSE);
+
+			if (g_date_valid_dmy (sqlite3_column_int (statement, 2),
+			                      sqlite3_column_int (statement, 3),
+			                      sqlite3_column_int (statement, 4)) == TRUE) {
+				g_date_set_dmy (&last_edited,
+				                sqlite3_column_int (statement, 5),
+				                sqlite3_column_int (statement, 6),
+				                sqlite3_column_int (statement, 7));
+				almanah_entry_set_last_edited (entry, &last_edited);
+			}
+
+			/* Deserialise the entry into our buffer */
+			gtk_text_buffer_set_text (text_buffer, "", 0);
+			if (almanah_entry_get_content (entry, text_buffer, TRUE, NULL) == FALSE) {
+				/* Error: return the next entry instead */
+				g_object_unref (entry);
+				g_warning (_("Error deserializing entry into buffer while searching."));
+				return almanah_storage_manager_search_entries (self, NULL, iter);
+			}
 
-		/* Deserialise the entry into our buffer */
-		gtk_text_buffer_set_text (text_buffer, "", 0);
-		if (almanah_entry_get_content (entry, text_buffer, TRUE, NULL) == FALSE) {
+			/* Perform the search */
+			gtk_text_buffer_get_start_iter (text_buffer, &text_iter);
+			if (gtk_text_iter_forward_search (&text_iter, search_string, GTK_TEXT_SEARCH_VISIBLE_ONLY | GTK_TEXT_SEARCH_TEXT_ONLY,
+			                                  NULL, NULL, NULL) == TRUE) {
+				/* A match was found! */
+				return entry;
+			}
+
+			/* Free stuff up and return the next match instead */
 			g_object_unref (entry);
-			g_warning (_("Error deserializing entry into buffer while searching."));
-			continue;
+			return almanah_storage_manager_search_entries (self, NULL, iter);
 		}
-
-		/* Perform the search */
-		gtk_text_buffer_get_start_iter (text_buffer, &iter);
-		if (gtk_text_iter_forward_search (&iter, search_string, GTK_TEXT_SEARCH_VISIBLE_ONLY | GTK_TEXT_SEARCH_TEXT_ONLY,
-		                                  NULL, NULL, NULL) == TRUE) {
-			/* A match was found */
-			matches = g_slist_prepend (matches, g_object_ref (entry));
+		case SQLITE_DONE:
+		case SQLITE_ERROR:
+		case SQLITE_BUSY:
+		case SQLITE_LOCKED:
+		case SQLITE_NOMEM:
+		case SQLITE_IOERR:
+		case SQLITE_CORRUPT:
+		default: {
+			/* Clean up the iter and return */
+			sqlite3_finalize (statement);
+			iter->statement = NULL;
+			g_object_unref (((SearchData*) iter->user_data)->text_buffer);
+			g_free (((SearchData*) iter->user_data)->search_string);
+			g_slice_free (SearchData, iter->user_data);
+			iter->user_data = NULL;
+			iter->finished = TRUE;
+
+			return NULL;
 		}
-
-		/* Free stuff up and continue */
-		g_object_unref (entry);
 	}
 
-	sqlite3_finalize (statement);
-	g_object_unref (text_buffer);
-
-	return matches;
+	g_assert_not_reached ();
 }
 
 /**
  * almanah_storage_manager_get_entries:
  * @self: an #AlmanahStorageManager
+ * @iter: an #AlmanahStorageManagerIter to keep track of the query
  *
- * Returns a list of all #AlmanahEntry<!-- -->s in the database.
+ * Iterates through every single #AlmanahEntry in the database using the given #AlmanahStorageManagerIter. @iter should be initialised with
+ * almanah_storage_manager_iter_init() and passed to almanah_storage_manager_get_entries(). This will then return an #AlmanahEntry every time it's
+ * called with the same @iter until it reaches the end of the result set, when it will return %NULL. It will also finish and return %NULL on error.
  *
- * Return value: a #GSList of #AlmanahEntry<!-- -->s, or %NULL; unref elements with g_object_unref(); free list with g_slist_free()
+ * Calling functions must get every result from the result set (i.e. not stop calling almanah_storage_manager_get_entries() until it returns %NULL).
+ *
+ * Return value: an #AlmanahEntry, or %NULL; unref with g_object_unref()
  **/
-GSList *
-almanah_storage_manager_get_entries (AlmanahStorageManager *self)
+AlmanahEntry *
+almanah_storage_manager_get_entries (AlmanahStorageManager *self, AlmanahStorageManagerIter *iter)
 {
-	GSList *entries = NULL;
-	int result;
 	sqlite3_stmt *statement;
 
+	g_return_val_if_fail (ALMANAH_IS_STORAGE_MANAGER (self), NULL);
+	g_return_val_if_fail (iter != NULL, NULL);
+
 	/* Just as with almanah_storage_manager_get_entry(), it's necessary to avoid our nice SQLite interface here. It's probably more efficient to
 	 * avoid it anyway. */
 
-	/* Prepare the statement */
-	if (sqlite3_prepare_v2 (self->priv->connection,
-	                        "SELECT content, is_important, day, month, year, edited_day, edited_month, edited_year FROM entries", -1,
-	                        &statement, NULL) != SQLITE_OK) {
+	if (iter->finished == TRUE)
 		return NULL;
-	}
 
-	/* Execute the statement */
-	while ((result = sqlite3_step (statement)) == SQLITE_ROW) {
-		GDate date, last_edited;
-		AlmanahEntry *entry;
-
-		g_date_set_dmy (&date,
-		                sqlite3_column_int (statement, 2),
-		                sqlite3_column_int (statement, 3),
-		                sqlite3_column_int (statement, 4));
-
-		/* Get the data */
-		entry = almanah_entry_new (&date);
-		almanah_entry_set_data (entry, sqlite3_column_blob (statement, 0), sqlite3_column_bytes (statement, 0));
-		almanah_entry_set_is_important (entry, (sqlite3_column_int (statement, 1) == 1) ? TRUE : FALSE);
-
-		if (g_date_valid_dmy (sqlite3_column_int (statement, 2),
-		                      sqlite3_column_int (statement, 3),
-		                      sqlite3_column_int (statement, 4)) == TRUE) {
-			g_date_set_dmy (&last_edited,
-				        sqlite3_column_int (statement, 5),
-				        sqlite3_column_int (statement, 6),
-				        sqlite3_column_int (statement, 7));
-			almanah_entry_set_last_edited (entry, &last_edited);
+	if (iter->statement == NULL) {
+		/* Prepare the statement */
+		if (sqlite3_prepare_v2 (self->priv->connection,
+		                        "SELECT content, is_important, day, month, year, edited_day, edited_month, edited_year FROM entries", -1,
+		                        (sqlite3_stmt**) &(iter->statement), NULL) != SQLITE_OK) {
+			return NULL;
 		}
-
-		entries = g_slist_prepend (entries, entry);
 	}
 
-	sqlite3_finalize (statement);
+	statement = iter->statement;
 
-	/* Check for errors */
-	if (result != SQLITE_DONE) {
-		g_slist_foreach (entries, (GFunc) g_object_unref, NULL);
-		g_slist_free (entries);
-		return NULL;
+	/* Execute the statement */
+	switch (sqlite3_step (statement)) {
+		case SQLITE_ROW: {
+			GDate date, last_edited;
+			AlmanahEntry *entry;
+
+			/* Process the row */
+			g_date_set_dmy (&date,
+			                sqlite3_column_int (statement, 2),
+			                sqlite3_column_int (statement, 3),
+			                sqlite3_column_int (statement, 4));
+
+			/* Get the data */
+			entry = almanah_entry_new (&date);
+			almanah_entry_set_data (entry, sqlite3_column_blob (statement, 0), sqlite3_column_bytes (statement, 0));
+			almanah_entry_set_is_important (entry, (sqlite3_column_int (statement, 1) == 1) ? TRUE : FALSE);
+
+			if (g_date_valid_dmy (sqlite3_column_int (statement, 2),
+			                      sqlite3_column_int (statement, 3),
+			                      sqlite3_column_int (statement, 4)) == TRUE) {
+				g_date_set_dmy (&last_edited,
+				                sqlite3_column_int (statement, 5),
+				                sqlite3_column_int (statement, 6),
+				                sqlite3_column_int (statement, 7));
+				almanah_entry_set_last_edited (entry, &last_edited);
+			}
+
+			return entry;
+		}
+		case SQLITE_DONE:
+		case SQLITE_ERROR:
+		case SQLITE_BUSY:
+		case SQLITE_LOCKED:
+		case SQLITE_NOMEM:
+		case SQLITE_IOERR:
+		case SQLITE_CORRUPT:
+		default: {
+			/* Clean up the iter and return */
+			sqlite3_finalize (statement);
+			iter->statement = NULL;
+			iter->finished = TRUE;
+
+			return NULL;
+		}
 	}
 
-	return g_slist_reverse (entries);
+	g_assert_not_reached ();
 }
 
 /* NOTE: Free results with g_free. Return value is 0-based. */
diff --git a/src/storage-manager.h b/src/storage-manager.h
index ad09d08..324c0c5 100644
--- a/src/storage-manager.h
+++ b/src/storage-manager.h
@@ -58,6 +58,13 @@ typedef enum {
 	ALMANAH_STORAGE_MANAGER_ERROR_BAD_VERSION
 } AlmanahStorageManagerError;
 
+typedef struct {
+	/*< private >*/
+	gpointer /*sqlite3_stmt **/ statement;
+	gboolean finished; /* TRUE if the query is finished and the iter has been cleaned up */
+	gpointer user_data; /* to be used by #AlmanahStorageManager functions which need to associate data with a statement */
+} AlmanahStorageManagerIter;
+
 typedef gint (*AlmanahQueryCallback) (gpointer user_data, gint columns, gchar **data, gchar **column_names);
 
 typedef struct {
@@ -82,8 +89,12 @@ gboolean almanah_storage_manager_get_statistics (AlmanahStorageManager *self, gu
 gboolean almanah_storage_manager_entry_exists (AlmanahStorageManager *self, GDate *date);
 AlmanahEntry *almanah_storage_manager_get_entry (AlmanahStorageManager *self, GDate *date);
 gboolean almanah_storage_manager_set_entry (AlmanahStorageManager *self, AlmanahEntry *entry);
-GSList *almanah_storage_manager_search_entries (AlmanahStorageManager *self, const gchar *search_string) G_GNUC_MALLOC G_GNUC_WARN_UNUSED_RESULT;
-GSList *almanah_storage_manager_get_entries (AlmanahStorageManager *self) G_GNUC_MALLOC G_GNUC_WARN_UNUSED_RESULT;
+
+void almanah_storage_manager_iter_init (AlmanahStorageManagerIter *iter);
+AlmanahEntry *almanah_storage_manager_search_entries (AlmanahStorageManager *self, const gchar *search_string,
+                                                      AlmanahStorageManagerIter *iter) G_GNUC_MALLOC G_GNUC_WARN_UNUSED_RESULT;
+AlmanahEntry *almanah_storage_manager_get_entries (AlmanahStorageManager *self,
+                                                   AlmanahStorageManagerIter *iter) G_GNUC_MALLOC G_GNUC_WARN_UNUSED_RESULT;
 
 gboolean *almanah_storage_manager_get_month_marked_days (AlmanahStorageManager *self, GDateYear year, GDateMonth month, guint *num_days);
 gboolean *almanah_storage_manager_get_month_important_days (AlmanahStorageManager *self, GDateYear year, GDateMonth month, guint *num_days);
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]