[tracker/tracker-0.6] Use single DB insert statements instead of insert and many updates
- From: Martyn James Russell <mr src gnome org>
- To: svn-commits-list gnome org
- Subject: [tracker/tracker-0.6] Use single DB insert statements instead of insert and many updates
- Date: Fri, 15 May 2009 05:01:50 -0400 (EDT)
commit 169fcacef91396ebe77e50a82241d5742ce53d39
Author: Carlos Garnacho <carlos lanedo com>
Date: Fri May 15 09:58:31 2009 +0100
Use single DB insert statements instead of insert and many updates
This seriously improves speed. My desktop, 7k items, 35s down to 28s.
This patch from Carlos was based on an intitial patch by Philip Van Hoof.
---
src/libtracker-data/tracker-data-update.c | 255 +++++++++++++++++++++++------
src/libtracker-data/tracker-data-update.h | 32 ++++-
src/libtracker-db/tracker-db-interface.h | 2 +-
src/tracker-indexer/tracker-indexer.c | 64 ++++++-
4 files changed, 293 insertions(+), 60 deletions(-)
diff --git a/src/libtracker-data/tracker-data-update.c b/src/libtracker-data/tracker-data-update.c
index e3f1fef..41c4b86 100644
--- a/src/libtracker-data/tracker-data-update.c
+++ b/src/libtracker-data/tracker-data-update.c
@@ -36,12 +36,17 @@
#include "tracker-data-query.h"
typedef struct {
+ TrackerDataUpdateMetadataContext *context;
TrackerService *service;
guint32 iid_value;
TrackerLanguage *language;
TrackerConfig *config;
} ForeachInMetadataInfo;
+typedef struct {
+ GPtrArray *columns;
+ GPtrArray *values;
+} InsertData;
guint32
tracker_data_update_get_new_service_id (TrackerDBInterface *iface)
@@ -90,12 +95,13 @@ tracker_data_update_get_new_service_id (TrackerDBInterface *iface)
}
gboolean
-tracker_data_update_create_service (TrackerService *service,
- guint32 service_id,
- const gchar *udi,
- const gchar *dirname,
- const gchar *basename,
- GHashTable *metadata)
+tracker_data_update_create_service (TrackerDataUpdateMetadataContext *context,
+ TrackerService *service,
+ guint32 service_id,
+ const gchar *udi,
+ const gchar *dirname,
+ const gchar *basename,
+ GHashTable *metadata)
{
TrackerDBInterface *iface;
TrackerDBResultSet *result_set;
@@ -134,19 +140,22 @@ tracker_data_update_create_service (TrackerService *service,
is_dir = g_file_test (path, G_FILE_TEST_IS_DIR);
is_symlink = g_file_test (path, G_FILE_TEST_IS_SYMLINK);
- tracker_db_interface_execute_procedure (iface, NULL, "CreateService",
- id_str,
- dirname,
- basename,
- service_type_id_str,
- is_dir ? "Folder" : g_hash_table_lookup (metadata, "File:Mime"),
- g_hash_table_lookup (metadata, "File:Size"),
- is_dir ? "1" : "0",
- is_symlink ? "1" : "0",
- "0", /* Offset */
- g_hash_table_lookup (metadata, "File:Modified"),
- volume_id_str, /* Aux ID */
- NULL);
+ /* Add data to the context */
+ tracker_data_update_metadata_context_add (context, "ID", id_str);
+ tracker_data_update_metadata_context_add (context, "Path", dirname);
+ tracker_data_update_metadata_context_add (context, "Name", basename);
+ tracker_data_update_metadata_context_add (context, "ServiceTypeID", service_type_id_str);
+ tracker_data_update_metadata_context_add (context, "Mime",
+ is_dir ? "Folder" : g_hash_table_lookup (metadata, "File:Mime"));
+ tracker_data_update_metadata_context_add (context, "Size",
+ g_hash_table_lookup (metadata, "File:Size"));
+ tracker_data_update_metadata_context_add (context, "IsDirectory",
+ is_dir ? "1" : "0");
+ tracker_data_update_metadata_context_add (context, "IsLink",
+ is_symlink ? "1" : "0");
+ tracker_data_update_metadata_context_add (context, "IndexTime",
+ g_hash_table_lookup (metadata, "File:Modified"));
+ tracker_data_update_metadata_context_add (context, "AuxilaryID", volume_id_str);
g_free (id_str);
g_free (service_type_id_str);
@@ -323,19 +332,21 @@ tracker_data_update_delete_all_metadata (TrackerService *service,
}
void
-tracker_data_update_set_metadata (TrackerService *service,
- guint32 service_id,
- TrackerField *field,
- const gchar *value,
- const gchar *parsed_value)
+tracker_data_update_set_metadata (TrackerDataUpdateMetadataContext *context,
+ TrackerService *service,
+ guint32 service_id,
+ TrackerField *field,
+ const gchar *value,
+ const gchar *parsed_value)
{
TrackerDBInterface *iface;
gint metadata_key;
gint collate_key;
gchar *id_str;
- if(!strlen(value))
+ if (tracker_is_empty_string (value)) {
return;
+ }
id_str = tracker_guint32_to_string (service_id);
@@ -390,39 +401,31 @@ tracker_data_update_set_metadata (TrackerService *service,
metadata_key = tracker_ontology_service_get_key_metadata (tracker_service_get_name (service),
tracker_field_get_name (field));
if (metadata_key > 0) {
- gchar *val;
+ gchar *column;
- val = tracker_escape_string (value);
-
- tracker_db_interface_execute_query (iface, NULL,
- "update Services set KeyMetadata%d = '%s' where id = %d",
- metadata_key,
- val,
- service_id);
- g_free (val);
+ column = g_strdup_printf ("KeyMetadata%d", metadata_key);
+ tracker_data_update_metadata_context_add (context, column, value);
+ g_free (column);
} else if (tracker_field_get_data_type (field) == TRACKER_FIELD_TYPE_DATE &&
(strcmp (tracker_field_get_name (field), "File:Modified") == 0)) {
/* Handle mtime */
- tracker_db_interface_execute_query (iface, NULL,
- "update Services set IndexTime = '%s' where ID = %d",
- value,
- service_id);
+ tracker_data_update_metadata_context_add (context, "IndexTime", value);
}
collate_key = tracker_ontology_service_get_key_collate (tracker_service_get_name (service),
tracker_field_get_name (field));
if (collate_key > 0) {
- gchar *val;
-
+ gchar *val, *collate_val, *column;
+
val = tracker_escape_string (value);
-
- tracker_db_interface_execute_query (iface, NULL,
- "update Services set KeyMetadataCollation%d = CollateKey('%s') where id = %d",
- collate_key,
- val,
- service_id);
+ collate_val = g_strdup_printf ("CollateKey('%s')", val);
+ column = g_strdup_printf ("KeyMetadataCollation%d", collate_key);
+
+ tracker_data_update_metadata_context_add (context, column, collate_val);
g_free (val);
+ g_free (collate_val);
+ g_free (column);
}
g_free (id_str);
@@ -654,7 +657,12 @@ set_metadata (TrackerField *field,
score);
}
- tracker_data_update_set_metadata (info->service, info->iid_value, field, value, parsed_value);
+ tracker_data_update_set_metadata (info->context,
+ info->service,
+ info->iid_value,
+ field,
+ value,
+ parsed_value);
g_free (parsed_value);
g_strfreev (arr);
@@ -697,6 +705,7 @@ tracker_data_update_replace_service (const gchar *udi,
const gchar *rdf_type,
GHashTable *metadata)
{
+ TrackerDataUpdateMetadataContext *context;
TrackerDBInterface *iface;
TrackerDBResultSet *result_set;
const gchar *modified;
@@ -753,11 +762,17 @@ tracker_data_update_replace_service (const gchar *udi,
set_metadata = TRUE;
}
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_UPDATE,
+ service, id);
+
g_object_unref (result_set);
} else {
id = tracker_data_update_get_new_service_id (iface);
- if (tracker_data_update_create_service (service, id,
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_INSERT,
+ service, id);
+
+ if (tracker_data_update_create_service (context, service, id,
udi,
dirname, basename,
metadata)) {
@@ -770,6 +785,7 @@ tracker_data_update_replace_service (const gchar *udi,
info = g_slice_new (ForeachInMetadataInfo);
+ info->context = context;
info->service = service;
info->iid_value = id;
@@ -783,6 +799,9 @@ tracker_data_update_replace_service (const gchar *udi,
g_slice_free (ForeachInMetadataInfo, info);
}
+ tracker_data_update_metadata_context_close (context);
+ tracker_data_update_metadata_context_free (context);
+
g_free (dirname);
g_free (basename);
g_free (escaped_path);
@@ -872,3 +891,143 @@ tracker_data_update_disable_all_volumes (void)
NULL);
}
+/* Metadata context */
+TrackerDataUpdateMetadataContext *
+tracker_data_update_metadata_context_new (TrackerDataUpdateMetadataContextType type,
+ TrackerService *service,
+ guint id)
+{
+ TrackerDataUpdateMetadataContext *context;
+
+ context = g_slice_new (TrackerDataUpdateMetadataContext);
+ context->type = type;
+ context->service = g_object_ref (service);
+ context->id = id;
+
+ context->data = g_hash_table_new_full (g_str_hash,
+ g_str_equal,
+ (GDestroyNotify) g_free,
+ (GDestroyNotify) g_free);
+ return context;
+}
+
+void
+tracker_data_update_metadata_context_add (TrackerDataUpdateMetadataContext *context,
+ const gchar *column,
+ const gchar *value)
+{
+ g_hash_table_replace (context->data,
+ g_strdup (column),
+ tracker_escape_string (value));
+}
+
+static void
+get_insert_data_foreach (gpointer key,
+ gpointer value,
+ gpointer user_data)
+{
+ InsertData *insert_data = (InsertData *) user_data;
+
+ g_ptr_array_add (insert_data->columns, key);
+ g_ptr_array_add (insert_data->values, value);
+}
+
+void
+tracker_data_update_metadata_context_close (TrackerDataUpdateMetadataContext *context)
+{
+ TrackerDBInterface *iface;
+ GError *error = NULL;
+ guint size;
+ gchar *sql;
+
+ size = g_hash_table_size (context->data);
+
+ if (size == 0) {
+ /* No changes */
+ return;
+ }
+
+ if (context->type == TRACKER_CONTEXT_TYPE_INSERT) {
+ gchar *id_str, *joined_columns, *joined_values;
+ InsertData insert_data;
+
+ /* Ensure we have an ID */
+ id_str = tracker_guint32_to_string (context->id);
+ tracker_data_update_metadata_context_add (context, "ID", id_str);
+ g_free (id_str);
+
+ /* Compose insert SQL query */
+ size = g_hash_table_size (context->data);
+ insert_data.columns = g_ptr_array_sized_new (size + 1);
+ insert_data.values = g_ptr_array_sized_new (size + 1);
+
+ g_hash_table_foreach (context->data,
+ get_insert_data_foreach,
+ &insert_data);
+
+ g_ptr_array_add (insert_data.columns, NULL);
+ g_ptr_array_add (insert_data.values, NULL);
+
+ joined_columns = g_strjoinv (",", (gchar **) insert_data.columns->pdata);
+ joined_values = g_strjoinv (",", (gchar **) insert_data.values->pdata);
+
+ sql = g_strdup_printf ("INSERT INTO Services (%s) VALUES (%s);",
+ joined_columns,
+ joined_values);
+
+ g_free (joined_columns);
+ g_free (joined_values);
+
+ g_ptr_array_free (insert_data.columns, TRUE);
+ g_ptr_array_free (insert_data.values, TRUE);
+ } else if (context->type == TRACKER_CONTEXT_TYPE_UPDATE) {
+ GString *update_query;
+ GHashTableIter iter;
+ gpointer key, value;
+ gboolean first = TRUE;
+
+ /* Compose update SQL query */
+ update_query = g_string_new ("UPDATE Services SET ");
+
+ g_hash_table_iter_init (&iter, context->data);
+
+ while (g_hash_table_iter_next (&iter, &key, &value)) {
+ const gchar *column = key;
+ const gchar *val = value;
+
+ if (!first) {
+ g_string_append (update_query, ", ");
+ }
+
+ g_string_append_printf (update_query,
+ "%s = '%s'",
+ column,
+ val);
+ first = FALSE;
+ }
+
+ g_string_append_printf (update_query, " WHERE ID = %d", context->id);
+
+ sql = g_string_free (update_query, FALSE);
+ } else {
+ g_assert_not_reached ();
+ }
+
+ iface = tracker_db_manager_get_db_interface_by_type (tracker_service_get_name (context->service),
+ TRACKER_DB_CONTENT_TYPE_METADATA);
+
+ tracker_db_interface_execute_query (iface, &error, sql, NULL);
+
+ if (error) {
+ g_warning ("%s", error->message);
+ g_error_free (error);
+ }
+}
+
+void
+tracker_data_update_metadata_context_free (TrackerDataUpdateMetadataContext *context)
+{
+ g_object_unref (context->service);
+ g_hash_table_unref (context->data);
+ g_slice_free (TrackerDataUpdateMetadataContext, context);
+}
diff --git a/src/libtracker-data/tracker-data-update.h b/src/libtracker-data/tracker-data-update.h
index 90cc12a..09783e3 100644
--- a/src/libtracker-data/tracker-data-update.h
+++ b/src/libtracker-data/tracker-data-update.h
@@ -32,10 +32,26 @@
G_BEGIN_DECLS
+typedef struct TrackerDataUpdateMetadataContext TrackerDataUpdateMetadataContext;
+typedef enum TrackerDataUpdateMetadataContextType TrackerDataUpdateMetadataContextType;
+
+enum TrackerDataUpdateMetadataContextType {
+ TRACKER_CONTEXT_TYPE_INSERT,
+ TRACKER_CONTEXT_TYPE_UPDATE
+};
+
+struct TrackerDataUpdateMetadataContext {
+ TrackerDataUpdateMetadataContextType type;
+ TrackerService *service;
+ guint id;
+ GHashTable *data;
+};
+
guint32 tracker_data_update_get_new_service_id (TrackerDBInterface *iface);
/* Services */
-gboolean tracker_data_update_create_service (TrackerService *service,
+gboolean tracker_data_update_create_service (TrackerDataUpdateMetadataContext *context,
+ TrackerService *service,
guint32 service_id,
const gchar *udi,
const gchar *dirname,
@@ -62,7 +78,8 @@ void tracker_data_update_delete_service_all (const gchar *rdf_type);
/* Metadata */
-void tracker_data_update_set_metadata (TrackerService *service,
+void tracker_data_update_set_metadata (TrackerDataUpdateMetadataContext *context,
+ TrackerService *service,
guint32 service_id,
TrackerField *field,
const gchar *value,
@@ -89,6 +106,17 @@ void tracker_data_update_disable_volume (const gchar *ud
void tracker_data_update_disable_all_volumes (void);
void tracker_data_update_reset_volume (guint32 volume_id);
+/* Metadata context */
+TrackerDataUpdateMetadataContext *
+ tracker_data_update_metadata_context_new (TrackerDataUpdateMetadataContextType type,
+ TrackerService *service,
+ guint id);
+void tracker_data_update_metadata_context_add (TrackerDataUpdateMetadataContext *context,
+ const gchar *column,
+ const gchar *value);
+void tracker_data_update_metadata_context_close (TrackerDataUpdateMetadataContext *context);
+void tracker_data_update_metadata_context_free (TrackerDataUpdateMetadataContext *context);
+
G_END_DECLS
#endif /* __TRACKER_DATA_UPDATE_H__ */
diff --git a/src/libtracker-db/tracker-db-interface.h b/src/libtracker-db/tracker-db-interface.h
index 71952fc..ca16469 100644
--- a/src/libtracker-db/tracker-db-interface.h
+++ b/src/libtracker-db/tracker-db-interface.h
@@ -70,7 +70,7 @@ struct TrackerDBInterfaceIface {
};
-#undef DISABLE_DEBUG
+#define DISABLE_DEBUG
#ifdef G_HAVE_ISO_VARARGS
# ifdef DISABLE_DEBUG
diff --git a/src/tracker-indexer/tracker-indexer.c b/src/tracker-indexer/tracker-indexer.c
index 0aec6bc..9d4d7ea 100644
--- a/src/tracker-indexer/tracker-indexer.c
+++ b/src/tracker-indexer/tracker-indexer.c
@@ -167,6 +167,7 @@ struct PathInfo {
};
struct MetadataForeachData {
+ TrackerDataUpdateMetadataContext *context;
TrackerLanguage *language;
TrackerConfig *config;
guint32 service_id;
@@ -1199,9 +1200,17 @@ index_metadata_item (TrackerField *field,
}
if (data->add) {
- tracker_data_update_set_metadata (data->service, data->id, field, (gchar *) value, parsed_value);
+ tracker_data_update_set_metadata (data->context,
+ data->service,
+ data->id,
+ field,
+ value,
+ parsed_value);
} else {
- tracker_data_update_delete_metadata (data->service, data->id, field, (gchar *) value);
+ tracker_data_update_delete_metadata (data->service,
+ data->id,
+ field,
+ value);
}
g_free (parsed_value);
@@ -1243,6 +1252,7 @@ index_metadata_foreach (TrackerField *field,
static void
index_metadata (TrackerIndexer *indexer,
+ TrackerDataUpdateMetadataContext *context,
guint32 id,
TrackerService *service,
TrackerModuleMetadata *metadata)
@@ -1252,6 +1262,7 @@ index_metadata (TrackerIndexer *indexer,
service_id = tracker_service_get_id (service);
+ data.context = context;
data.language = indexer->private->language;
data.config = indexer->private->config;
data.service_id = service_id;
@@ -1276,6 +1287,7 @@ unindex_metadata (TrackerIndexer *indexer,
service_id = tracker_service_get_id (service);
+ data.context = NULL;
data.language = indexer->private->language;
data.config = indexer->private->config;
data.service_id = service_id;
@@ -1667,6 +1679,7 @@ item_add_or_update (TrackerIndexer *indexer,
}
if (exists) {
+ TrackerDataUpdateMetadataContext *context;
TrackerDataMetadata *old_metadata_emb, *old_metadata_non_emb;
gchar *old_text;
@@ -1701,7 +1714,13 @@ item_add_or_update (TrackerIndexer *indexer,
remove_existing_non_emb_metadata,
old_metadata_non_emb);
- index_metadata (indexer, id, service, metadata);
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_UPDATE,
+ service, id);
+
+ index_metadata (indexer, context, id, service, metadata);
+
+ tracker_data_update_metadata_context_close (context);
+ tracker_data_update_metadata_context_free (context);
/* Take the old text -> the new one, calculate
* difference and add the words.
@@ -1727,6 +1746,7 @@ item_add_or_update (TrackerIndexer *indexer,
tracker_data_metadata_free (old_metadata_emb);
tracker_data_metadata_free (old_metadata_non_emb);
} else {
+ TrackerDataUpdateMetadataContext *context;
GHashTable *data;
const gchar *udi;
@@ -1739,14 +1759,21 @@ item_add_or_update (TrackerIndexer *indexer,
data = tracker_module_metadata_get_hash_table (metadata);
udi = tracker_hal_udi_get_for_path (indexer->private->hal, dirname);
- tracker_data_update_create_service (service,
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_INSERT,
+ service, id);
+
+ tracker_data_update_create_service (context,
+ service,
id,
udi,
dirname,
basename,
data);
- index_metadata (indexer, id, service, metadata);
+ index_metadata (indexer, context, id, service, metadata);
+
+ tracker_data_update_metadata_context_close (context);
+ tracker_data_update_metadata_context_free (context);
if (text) {
/* Save in the index */
@@ -1893,6 +1920,7 @@ update_moved_item_index (TrackerIndexer *indexer,
GFile *file,
GFile *source_file)
{
+ TrackerDataUpdateMetadataContext *context;
TrackerModuleMetadata *new_metadata;
gchar *path, *new_path, *new_name;
const gchar *ext;
@@ -1922,7 +1950,13 @@ update_moved_item_index (TrackerIndexer *indexer,
tracker_module_metadata_add_string (new_metadata, METADATA_FILE_EXT, ext);
}
- index_metadata (indexer, service_id, service, new_metadata);
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_UPDATE,
+ service, service_id);
+
+ index_metadata (indexer, context, service_id, service, new_metadata);
+
+ tracker_data_update_metadata_context_close (context);
+ tracker_data_update_metadata_context_free (context);
g_object_unref (new_metadata);
g_free (new_path);
@@ -2283,6 +2317,7 @@ handle_metadata_add (TrackerIndexer *indexer,
GStrv values,
GError **error)
{
+ TrackerDataUpdateMetadataContext *context;
TrackerService *service;
TrackerField *field;
guint service_id, i, j;
@@ -2377,6 +2412,9 @@ handle_metadata_add (TrackerIndexer *indexer,
set_values = g_new0 (gchar *, g_strv_length (values) + 1);
+ context = tracker_data_update_metadata_context_new (TRACKER_CONTEXT_TYPE_UPDATE,
+ service, service_id);
+
for (i = 0, j = 0; values[i] != NULL; i++) {
g_debug ("Setting metadata: service_type '%s' id '%d' field '%s' value '%s'",
tracker_service_get_name (service),
@@ -2384,15 +2422,23 @@ handle_metadata_add (TrackerIndexer *indexer,
tracker_field_get_name (field),
values[i]);
- if (tracker_field_get_multiple_values (field)
- && (tracker_string_in_string_list (values[i], old_contents) > -1) ) {
+ if (tracker_field_get_multiple_values (field) &&
+ tracker_string_in_string_list (values[i], old_contents) > -1) {
continue;
}
- tracker_data_update_set_metadata (service, service_id, field, values[i], NULL);
+ tracker_data_update_set_metadata (context,
+ service,
+ service_id,
+ field,
+ values[i],
+ NULL);
set_values [++j] = values[i];
}
+ tracker_data_update_metadata_context_close (context);
+ tracker_data_update_metadata_context_free (context);
+
joined = g_strjoinv (" ", set_values);
if (tracker_field_get_filtered (field)) {
index_text_no_parsing (indexer,
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
