tracker r3132 - in trunk: . data/db data/services src/libtracker-common src/libtracker-data src/libtracker-db

From: mottela svn gnome org
To: svn-commits-list gnome org
Subject: tracker r3132 - in trunk: . data/db data/services src/libtracker-common src/libtracker-data src/libtracker-db
Date: Wed, 25 Mar 2009 11:00:28 +0000 (UTC)
Author: mottela
Date: Wed Mar 25 11:00:28 2009
New Revision: 3132
URL: http://svn.gnome.org/viewvc/tracker?rev=3132&view=rev

Log:
Performance improvements to the queries, added key collations to Services-table

Modified:
   trunk/ChangeLog
   trunk/data/db/sqlite-service.sql
   trunk/data/services/default.service
   trunk/src/libtracker-common/tracker-ontology.c
   trunk/src/libtracker-common/tracker-ontology.h
   trunk/src/libtracker-data/tracker-data-schema.c
   trunk/src/libtracker-data/tracker-data-search.c
   trunk/src/libtracker-data/tracker-data-update.c
   trunk/src/libtracker-data/tracker-field-data.c
   trunk/src/libtracker-data/tracker-field-data.h
   trunk/src/libtracker-data/tracker-rdf-query.c
   trunk/src/libtracker-db/tracker-db-manager.c

Modified: trunk/data/db/sqlite-service.sql
==============================================================================
--- trunk/data/db/sqlite-service.sql	(original)
+++ trunk/data/db/sqlite-service.sql	Wed Mar 25 11:00:28 2009
@@ -23,6 +23,12 @@
 	KeyMetadata10		Text,
 	KeyMetadata11		Text,
 
+	KeyMetadataCollation1   Text,
+	KeyMetadataCollation2   Text,
+	KeyMetadataCollation3   Text,
+	KeyMetadataCollation4   Text,
+	KeyMetadataCollation5   Text,
+
 	Icon			Text,
 	CanWrite		Integer default 1,
 	CanExecute		Integer default 1,
@@ -39,7 +45,19 @@
     	unique (Path, Name)
 );
 
-CREATE INDEX ServiceTypeIDIndex1 ON Services (ServiceTypeID);
+CREATE INDEX ServiceTypeIDIndex ON Services (ServiceTypeID);
+
+/* It would seem that sqlite is unable to use split indices for GROUP or ORDER, thus we end up
+   with this scheme where AuxilaryID is dropped from the index and ServiceType requires additional logic */
+CREATE INDEX ServicesCompoundIndex1 ON Services (ServiceTypeID, KeyMetadataCollation1, KeyMetadataCollation2);
+CREATE INDEX ServicesCompoundIndex2 ON Services (ServiceTypeID, KeyMetadataCollation2);
+CREATE INDEX ServicesCompoundIndex3 ON Services (ServiceTypeID, KeyMetadataCollation3);
+CREATE INDEX ServicesCompoundIndex4 ON Services (ServiceTypeID, KeyMetadataCollation4);
+CREATE INDEX ServicesCompoundIndex5 ON Services (ServiceTypeID, KeyMetadataCollation5);
+CREATE INDEX ServicesCompoundIndex6 ON Services (ServiceTypeID, KeyMetadata6);
+CREATE INDEX ServicesCompoundIndex7 ON Services (ServiceTypeID, KeyMetadata7);
+CREATE INDEX ServicesCompoundIndex8 ON Services (ServiceTypeID, KeyMetadata8);
+CREATE INDEX ServicesCompoundIndexAux ON Services (ServiceTypeID, AuxilaryID);
 
 /* child service relationships for a specific group/struct metadata */
 CREATE TABLE ChildServices
@@ -62,7 +80,7 @@
 	MetaDataCollation	Text
 );
 
-CREATE INDEX ServiceMetaDataCompoundIndex4 ON ServiceMetaData (ServiceID, MetaDataID, MetaDataDisplay, MetaDataCollation);
+CREATE INDEX ServiceMetaDataCompoundIndex ON ServiceMetaData (ServiceID, MetaDataID, MetaDataDisplay, MetaDataCollation);
 
 /* metadata for all keyword types - keywords are db indexed for fast searching - they are also not processed like other metadata. */
 CREATE TABLE  ServiceKeywordMetaData 

Modified: trunk/data/services/default.service
==============================================================================
--- trunk/data/services/default.service	(original)
+++ trunk/data/services/default.service	Wed Mar 25 11:00:28 2009
@@ -76,8 +76,8 @@
 KeyMetadata5=Image:Location
 KeyMetadata6=Image:Date
 KeyMetadata7=Image:Rating
-KeyMetadata8=Image:Orientation
-KeyMetadata9=Image:HasKeywords
+KeyMetadata8=Image:HasKeywords
+KeyMetadata9=Image:Orientation
 KeyMetadata10=Image:Software
 TabularMetadata=File:Name;Image:Height;Image:Width;Image:Date;File:Modified;Image:Creator;Image:Software;
 TileMetadata=Image:Title;Image:Height;Image:Width;Image:Date;Image:Creator;Image:Software;Image:Comments;
@@ -98,15 +98,15 @@
 UIVisible=true
 UIView=tabular
 Icon=audio-x-generic
-KeyMetadata1=Audio:Genre
-KeyMetadata2=Audio:Artist
-KeyMetadata3=Audio:Album
+KeyMetadata1=Audio:Artist
+KeyMetadata2=Audio:Album
+KeyMetadata3=Audio:Genre
 KeyMetadata4=Audio:Title
-KeyMetadata5=Audio:Bitrate
+KeyMetadata5=Audio:PlayCount
 KeyMetadata6=Audio:ReleaseDate
 KeyMetadata7=Audio:Duration
 KeyMetadata8=Audio:TrackNo
-KeyMetadata9=Audio:PlayCount
+KeyMetadata9=Audio:BitRate
 KeyMetadata10=Audio:DateAdded
 KeyMetadata11=Audio:LastPlay
 TabularMetadata=Audio:Title;Audio:Artist;Audio:Album;Audio:Genre;Audio:Duration;Audio:ReleaseDate;

Modified: trunk/src/libtracker-common/tracker-ontology.c
==============================================================================
--- trunk/src/libtracker-common/tracker-ontology.c	(original)
+++ trunk/src/libtracker-common/tracker-ontology.c	Wed Mar 25 11:00:28 2009
@@ -622,6 +622,20 @@
 	return 0;
 }
 
+gint
+tracker_ontology_service_get_key_collate (const gchar *service_str,
+					  const gchar *meta_name)
+{
+	gint		i;
+
+	g_return_val_if_fail (service_str != NULL, 0);
+	g_return_val_if_fail (meta_name != NULL, 0);
+
+	i = tracker_ontology_service_get_key_metadata (service_str, meta_name);
+
+	return ((i < 6) ? i : 0);
+}
+
 gboolean
 tracker_ontology_service_get_show_directories (const gchar *service_str)
 {

Modified: trunk/src/libtracker-common/tracker-ontology.h
==============================================================================
--- trunk/src/libtracker-common/tracker-ontology.h	(original)
+++ trunk/src/libtracker-common/tracker-ontology.h	Wed Mar 25 11:00:28 2009
@@ -62,6 +62,8 @@
 gboolean	tracker_ontology_service_has_text		(const gchar	*service_str);
 gint		tracker_ontology_service_get_key_metadata	(const gchar	*service_str,
 								 const gchar	*meta_name);
+gint		tracker_ontology_service_get_key_collate	(const gchar	*service_str,
+								 const gchar	*meta_name);
 gboolean	tracker_ontology_service_get_show_directories	(const gchar	*service_str);
 gboolean	tracker_ontology_service_get_show_files		(const gchar	*service_str);
 const gchar *	tracker_ontology_get_field_name_by_id 	        (gint id);

Modified: trunk/src/libtracker-data/tracker-data-schema.c
==============================================================================
--- trunk/src/libtracker-data/tracker-data-schema.c	(original)
+++ trunk/src/libtracker-data/tracker-data-schema.c	Wed Mar 25 11:00:28 2009
@@ -174,6 +174,7 @@
 		gchar	    *this_field_name;
 		gchar	    *where_field;
 		gchar       *order_field;
+		gint         key_collate;
 
 		field_data = g_object_new (TRACKER_TYPE_FIELD_DATA,
 					   "is-select", is_select,
@@ -227,16 +228,35 @@
 
 		tracker_field_data_set_where_field (field_data, where_field);
 
-		if ((tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_DOUBLE) ||
-		    (tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_INDEX)  ||
-		    (tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_STRING)) {
-			order_field = g_strdup_printf ("M%d.MetaDataCollation", field_count);
+		key_collate = tracker_ontology_service_get_key_metadata (service, field_name);
+
+		if (key_collate > 0 && key_collate < 6) {
+			gchar *str;
+
+			str = g_strdup_printf (" S.KeyMetadataCollation%d", key_collate);
+			tracker_field_data_set_order_field (field_data, str);
+			tracker_field_data_set_needs_collate (field_data, FALSE);
+			g_free (str);
+		} else if (key_collate > 5 && key_collate < 9) {
+			gchar *str;
+			
+			str = g_strdup_printf (" S.KeyMetadata%d", key_collate);
+			tracker_field_data_set_order_field (field_data, str);
+			tracker_field_data_set_needs_collate (field_data, FALSE);
+			g_free (str);
 		} else {
-			order_field = g_strdup_printf ("M%d.MetaDataValue", field_count);
+			if ((tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_DOUBLE) ||
+			    (tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_INDEX)  ||
+			    (tracker_field_get_data_type (def) == TRACKER_FIELD_TYPE_STRING)) {
+				order_field = g_strdup_printf ("M%d.MetaDataCollation", field_count);
+			} else {
+				order_field = g_strdup_printf ("M%d.MetaDataValue", field_count);
+			}
+			tracker_field_data_set_needs_collate (field_data, TRUE);
+			tracker_field_data_set_order_field (field_data, order_field);
+			g_free (order_field);
 		}
 		
-		tracker_field_data_set_order_field (field_data, order_field);
-		
 		tracker_field_data_set_needs_null (field_data, FALSE);
 		g_free (where_field);
 		g_free (alias);

Modified: trunk/src/libtracker-data/tracker-data-search.c
==============================================================================
--- trunk/src/libtracker-data/tracker-data-search.c	(original)
+++ trunk/src/libtracker-data/tracker-data-search.c	Wed Mar 25 11:00:28 2009
@@ -651,7 +651,8 @@
 				     GSList	       **fields,
 				     const gchar        *field_name,
 				     gboolean		 is_select,
-				     gboolean		 is_condition)
+				     gboolean		 is_condition,
+				     gboolean            is_order)
 {
 	TrackerFieldData *field_data;
 	gboolean	  field_exists;
@@ -813,15 +814,14 @@
 	sql_select = g_string_new ("SELECT DISTINCT ");
 	sql_from   = g_string_new ("\nFROM Services AS S ");
 	sql_where  = g_string_new ("\nWHERE ");
-	sql_order  = g_string_new ("\nORDER BY ");
+	sql_order  = g_string_new ("");
 	sql_group  = g_string_new ("\nGROUP BY ");
 
 
 	for (i = 0; i < g_strv_length (fields); i++) {
 		TrackerFieldData *fd;
 
-		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, fields[i], FALSE, FALSE);
-		tracker_field_data_set_needs_join (fd, TRUE);
+		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, fields[i], TRUE, FALSE, TRUE);
 
 		if (!fd) {
 			g_string_free (sql_select, TRUE);
@@ -838,15 +838,18 @@
 
 		if (i) {
 			g_string_append_printf (sql_select, ",");
-			g_string_append_printf (sql_order, ",");
 			g_string_append_printf (sql_group, ",");
 		}
 
 		g_string_append_printf (sql_select, "%s", tracker_field_data_get_select_field (fd));
-		g_string_append_printf (sql_order, " %s %s",
-					tracker_field_data_get_order_field (fd),
-					order_desc ? "DESC" : "ASC" );
-		g_string_append_printf (sql_group, "%s", tracker_field_data_get_select_field (fd));
+		if (order_desc) {
+			if (i) {
+				g_string_append_printf (sql_order, ",");
+			}
+			g_string_append_printf (sql_order, "\nORDER BY %s DESC ",
+						tracker_field_data_get_order_field (fd));
+		}
+		g_string_append_printf (sql_group, "%s", tracker_field_data_get_order_field (fd));
 
 	}
 
@@ -854,7 +857,7 @@
 		TrackerFieldData *fd;
 		TrackerFieldType  data_type;
 
-		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, concat_field, TRUE, FALSE);
+		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, concat_field, TRUE, FALSE, FALSE);
 
 		if (!fd) {
 			g_string_free (sql_select, TRUE);
@@ -891,7 +894,8 @@
 		TrackerFieldData *fd;
 
 		if (strcmp (count_field, "*")) {
-			fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, count_field, TRUE, FALSE);
+			fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, count_field,
+								  TRUE, FALSE, FALSE);
 			
 			if (!fd) {
 				g_string_free (sql_select, TRUE);
@@ -916,7 +920,7 @@
 		TrackerFieldData *fd;
 		TrackerFieldType  data_type;
 
-		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, sum_field, TRUE, FALSE);
+		fd = tracker_metadata_add_metadata_field (iface, service_type, &field_list, sum_field, TRUE, FALSE, FALSE);
 
 		if (!fd) {
 			g_string_free (sql_select, TRUE);
@@ -1041,7 +1045,7 @@
 	sql_from   = g_string_new ("\nFROM Services AS S ");
 	sql_where  = g_string_new ("\nWHERE ");
 
-	fd = tracker_metadata_add_metadata_field (iface, service_type, &fields, field, TRUE, FALSE);
+	fd = tracker_metadata_add_metadata_field (iface, service_type, &fields, field, TRUE, FALSE, FALSE);
 
 	if (!fd) {
 		g_string_free (sql_select, TRUE);
@@ -1151,7 +1155,7 @@
 	sql_where  = g_string_new ("\nWHERE ");
 
 	if (strcmp (field, "*")) {
-		fd = tracker_metadata_add_metadata_field (iface, service_type, &fields, field, TRUE, FALSE);
+		fd = tracker_metadata_add_metadata_field (iface, service_type, &fields, field, TRUE, FALSE, FALSE);
 		
 		if (!fd) {
 			g_string_free (sql_select, TRUE);

Modified: trunk/src/libtracker-data/tracker-data-update.c
==============================================================================
--- trunk/src/libtracker-data/tracker-data-update.c	(original)
+++ trunk/src/libtracker-data/tracker-data-update.c	Wed Mar 25 11:00:28 2009
@@ -327,6 +327,7 @@
 {
 	TrackerDBInterface *iface;
 	gint metadata_key;
+	gint collate_key;
 	gchar *id_str;
 
 	if(!strlen(value))
@@ -404,6 +405,22 @@
 						    service_id);
 	}
 
+	collate_key = tracker_ontology_service_get_key_collate (tracker_service_get_name (service),
+								tracker_field_get_name (field));
+	if (collate_key > 0) {
+		gchar *val;
+		
+		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);
+
+		g_free (val);
+	}
+
 	g_free (id_str);
 }
 

Modified: trunk/src/libtracker-data/tracker-field-data.c
==============================================================================
--- trunk/src/libtracker-data/tracker-field-data.c	(original)
+++ trunk/src/libtracker-data/tracker-field-data.c	Wed Mar 25 11:00:28 2009
@@ -46,7 +46,9 @@
 	gboolean	  multiple_values;
 	gboolean	  is_select;
 	gboolean	  is_condition;
+	gboolean          is_order;
 	gboolean	  needs_join;
+	gboolean          needs_collate;
 	gboolean          needs_null;
 };
 
@@ -73,7 +75,9 @@
 	PROP_MULTIPLE_VALUES,
 	PROP_IS_SELECT,
 	PROP_IS_CONDITION,
+	PROP_IS_ORDER,
 	PROP_NEEDS_JOIN,
+	PROP_NEEDS_COLLATE,
 	PROP_NEEDS_NULL
 };
 
@@ -167,6 +171,13 @@
 							       FALSE,
 							       G_PARAM_READWRITE));
 	g_object_class_install_property (object_class,
+					 PROP_IS_ORDER,
+					 g_param_spec_boolean ("is-order",
+							       "Is order",
+							       "Is order",
+							       FALSE,
+							       G_PARAM_READWRITE));
+	g_object_class_install_property (object_class,
 					 PROP_NEEDS_JOIN,
 					 g_param_spec_boolean ("needs-join",
 							       "Needs join",
@@ -174,7 +185,14 @@
 							       FALSE,
 							       G_PARAM_READWRITE));
 	g_object_class_install_property (object_class,
-					 PROP_NEEDS_JOIN,
+					 PROP_NEEDS_COLLATE,
+					 g_param_spec_boolean ("needs-collate",
+							       "Needs collate",
+							       "Needs collate",
+							       FALSE,
+							       G_PARAM_READWRITE));
+	g_object_class_install_property (object_class,
+					 PROP_NEEDS_NULL,
 					 g_param_spec_boolean ("needs-null",
 							       "Needs null",
 							       "Needs null",
@@ -251,9 +269,15 @@
 	case PROP_IS_CONDITION:
 		g_value_set_boolean (value, priv->is_condition);
 		break;
+	case PROP_IS_ORDER:
+		g_value_set_boolean (value, priv->is_order);
+		break;
 	case PROP_NEEDS_JOIN:
 		g_value_set_boolean (value, priv->needs_join);
 		break;
+	case PROP_NEEDS_COLLATE:
+		g_value_set_boolean (value, priv->needs_collate);
+		break;
 	case PROP_NEEDS_NULL:
 		g_value_set_boolean (value, priv->needs_null);
 		break;		
@@ -314,10 +338,18 @@
 		tracker_field_data_set_is_condition (TRACKER_FIELD_DATA (object),
 						     g_value_get_boolean (value));
 		break;
+	case PROP_IS_ORDER:
+		tracker_field_data_set_is_order (TRACKER_FIELD_DATA (object),
+						 g_value_get_boolean (value));
+		break;
 	case PROP_NEEDS_JOIN:
 		tracker_field_data_set_needs_join (TRACKER_FIELD_DATA (object),
 						   g_value_get_boolean (value));
 		break;
+	case PROP_NEEDS_COLLATE:
+		tracker_field_data_set_needs_collate (TRACKER_FIELD_DATA (object),
+						      g_value_get_boolean (value));
+		break;
 	case PROP_NEEDS_NULL:
 		tracker_field_data_set_needs_null (TRACKER_FIELD_DATA (object),
 						   g_value_get_boolean (value));
@@ -472,6 +504,18 @@
 }
 
 gboolean
+tracker_field_data_get_is_order (TrackerFieldData *field_data)
+{
+	TrackerFieldDataPriv *priv;
+
+	g_return_val_if_fail (TRACKER_IS_FIELD_DATA (field_data), FALSE);
+
+	priv = GET_PRIV (field_data);
+
+	return priv->is_order;
+}
+
+gboolean
 tracker_field_data_get_needs_join (TrackerFieldData *field_data)
 {
 	TrackerFieldDataPriv *priv;
@@ -484,6 +528,18 @@
 }
 
 gboolean
+tracker_field_data_get_needs_collate (TrackerFieldData *field_data)
+{
+	TrackerFieldDataPriv *priv;
+
+	g_return_val_if_fail (TRACKER_IS_FIELD_DATA (field_data), FALSE);
+
+	priv = GET_PRIV (field_data);
+
+	return priv->needs_collate;
+}
+
+gboolean
 tracker_field_data_get_needs_null (TrackerFieldData *field_data)
 {
 	TrackerFieldDataPriv *priv;
@@ -699,6 +755,20 @@
 }
 
 void
+tracker_field_data_set_is_order (TrackerFieldData *field_data,
+				 gboolean	       value)
+{
+	TrackerFieldDataPriv *priv;
+	
+	g_return_if_fail (TRACKER_IS_FIELD_DATA (field_data));
+	
+	priv = GET_PRIV (field_data);
+	
+	priv->is_order = value;
+	g_object_notify (G_OBJECT (field_data), "is-order");
+}
+
+void
 tracker_field_data_set_needs_join (TrackerFieldData *field_data,
 				   gboolean	     value)
 {
@@ -713,6 +783,20 @@
 }
 
 void
+tracker_field_data_set_needs_collate (TrackerFieldData *field_data,
+				   gboolean	     value)
+{
+	TrackerFieldDataPriv *priv;
+
+	g_return_if_fail (TRACKER_IS_FIELD_DATA (field_data));
+
+	priv = GET_PRIV (field_data);
+
+	priv->needs_collate = value;
+	g_object_notify (G_OBJECT (field_data), "needs-collate");
+}
+
+void
 tracker_field_data_set_needs_null (TrackerFieldData *field_data,
 				   gboolean	     value)
 {

Modified: trunk/src/libtracker-data/tracker-field-data.h
==============================================================================
--- trunk/src/libtracker-data/tracker-field-data.h	(original)
+++ trunk/src/libtracker-data/tracker-field-data.h	Wed Mar 25 11:00:28 2009
@@ -61,7 +61,9 @@
 gboolean	  tracker_field_data_get_multiple_values (TrackerFieldData *field_data);
 gboolean	  tracker_field_data_get_is_select	 (TrackerFieldData *field_data);
 gboolean	  tracker_field_data_get_is_condition	 (TrackerFieldData *field_data);
+gboolean          tracker_field_data_get_is_order        (TrackerFieldData *field_data);
 gboolean	  tracker_field_data_get_needs_join	 (TrackerFieldData *field_data);
+gboolean          tracker_field_data_get_needs_collate   (TrackerFieldData *field_data);
 gboolean          tracker_field_data_get_needs_null      (TrackerFieldData *field_data);
 
 void		  tracker_field_data_set_alias		 (TrackerFieldData *field_data,
@@ -86,8 +88,12 @@
 							  gboolean	    value);
 void		  tracker_field_data_set_is_condition	 (TrackerFieldData *field_data,
 							  gboolean	    value);
+void		  tracker_field_data_set_is_order	 (TrackerFieldData *field_data,
+							  gboolean	    value);
 void		  tracker_field_data_set_needs_join	 (TrackerFieldData *field_data,
 							  gboolean	    value);
+void		  tracker_field_data_set_needs_collate	 (TrackerFieldData *field_data,
+							  gboolean	    value);
 void		  tracker_field_data_set_needs_null	 (TrackerFieldData *field_data,
 							  gboolean	    value);
 G_END_DECLS

Modified: trunk/src/libtracker-data/tracker-rdf-query.c
==============================================================================
--- trunk/src/libtracker-data/tracker-rdf-query.c	(original)
+++ trunk/src/libtracker-data/tracker-rdf-query.c	Wed Mar 25 11:00:28 2009
@@ -361,7 +361,8 @@
 add_metadata_field (ParserData	*data,
 		    const gchar *field_name,
 		    gboolean	 is_select,
-		    gboolean	 is_condition)
+		    gboolean	 is_condition,
+		    gboolean     is_order)
 {
 	TrackerFieldData *field_data;
 	gboolean	  field_exists;
@@ -401,6 +402,10 @@
 				}
 			}
 
+			if (is_order) {
+				tracker_field_data_set_is_order (field_data, TRUE);
+			}
+
 			break;
 		}
 	}
@@ -776,7 +781,7 @@
 						  state != STATE_END_INTEGER &&
 						  state != STATE_END_FLOAT));
 
-	field_data = add_metadata_field (data, data->current_field, FALSE, TRUE);
+	field_data = add_metadata_field (data, data->current_field, FALSE, TRUE, FALSE);
 
 	if (!field_data) {
 		g_free (avalue);
@@ -1148,6 +1153,31 @@
 
 }
 
+static void
+append_where_header (GString *string, const char *service)
+{
+	int type;
+	
+	type = tracker_ontology_get_service_id_by_name (service);
+
+	/* Sqlite is currently unable to perform any ORDER or GROUP BY on split index, so we try not to split
+	   unless necessary on ServiceType FIXME remove when not needed anymore (ie. fixed in sqlite) */
+
+	switch (type) {
+	case 2:
+		/* FILES */
+	case 12:
+		/* EMAILS */
+		g_string_append_printf (string, " (S.ServiceTypeID in (select TypeId from ServiceTypes where TypeName = '%s' or Parent = '%s')) ", service, service);
+		break;
+	default:
+		g_string_append_printf (string, " (S.ServiceTypeID=%d) ", type);
+	}
+
+	/* only search for items on enabled volumes */
+	g_string_append_printf (string, "AND (S.AuxilaryID = 0 OR S.AuxilaryID IN (SELECT VolumeID FROM Volumes WHERE Enabled = 1)) ");
+}
+
 GQuark
 tracker_rdf_error_quark (void)
 {
@@ -1195,7 +1225,7 @@
 		for (i = 0; i < field_count; i++) {
 			TrackerFieldData *field_data;
 
-			field_data = add_metadata_field (&data, fields[i], TRUE, FALSE);
+			field_data = add_metadata_field (&data, fields[i], TRUE, FALSE, FALSE);
 
 			if (!field_data) {
 				g_set_error (error,
@@ -1230,12 +1260,9 @@
 					table_name);
 	}
 
-	data.sql_where = g_string_new ("");
-
-	g_string_append_printf (data.sql_where, "\n WHERE (S.ServiceTypeID in (select TypeId from ServiceTypes where TypeName = '%s' or Parent = '%s')) ", service, service);
+	data.sql_where = g_string_new ("\nWHERE ");
 
-	/* only search for items on enabled volumes */
-	g_string_append_printf (data.sql_where, "AND (S.AuxilaryID = 0 OR S.AuxilaryID IN (SELECT VolumeID FROM Volumes WHERE Enabled = 1)) ");
+	append_where_header(data.sql_where, service);
 
 	if (keyword_count > 0) {
 		guint keyword;
@@ -1327,8 +1354,7 @@
 		for (i = 0; i < sort_field_count; i++) {
 			TrackerFieldData *field_data;
 
-			field_data = add_metadata_field (&data, sort_fields[i], FALSE, FALSE);
-			tracker_field_data_set_needs_join (field_data, TRUE);
+			field_data = add_metadata_field (&data, sort_fields[i], FALSE, FALSE, TRUE);
 
 			if (!field_data) {
 				g_set_error (error,
@@ -1393,7 +1419,17 @@
 
 		for (l = data.fields; l; l = l->next) {
 			if (!tracker_field_data_get_is_condition (l->data)) {
-				if (tracker_field_data_get_needs_join (l->data)) {
+				if (tracker_field_data_get_is_select (l->data) &&
+				    tracker_field_data_get_needs_join (l->data)) {
+					g_string_append_printf (data.sql_from,
+								"\n LEFT OUTER JOIN %s %s ON (S.ID = %s.ServiceID and %s.MetaDataID = %s) ",
+								tracker_field_data_get_table_name (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_id_field (l->data));
+				} else if (tracker_field_data_get_is_order (l->data) &&
+					   tracker_field_data_get_needs_collate (l->data)) {
 					g_string_append_printf (data.sql_from,
 								"\n LEFT OUTER JOIN %s %s ON (S.ID = %s.ServiceID and %s.MetaDataID = %s) ",
 								tracker_field_data_get_table_name (l->data),
@@ -1495,14 +1531,10 @@
 
 	data.sql_from = g_string_new ("");
 	data.sql_where = g_string_new ("");
+	append_where_header (data.sql_where, service);
 
 	data.fields = *fields;
 
-	g_string_append_printf (data.sql_where, " (S.ServiceTypeID in (select TypeId from ServiceTypes where TypeName = '%s' or Parent = '%s')) ", service, service);
-
-	/* only search for items on enabled volumes */
-	g_string_append (data.sql_where, "AND (S.AuxilaryID = 0 OR S.AuxilaryID IN (SELECT VolumeID FROM Volumes WHERE Enabled = 1)) ");
-
 	if (strlen (query) >= 10) {
 		g_string_append (data.sql_where, "AND ");
 	}
@@ -1534,7 +1566,17 @@
 
 		for (l = data.fields; l; l = l->next) {
 			if (!tracker_field_data_get_is_condition (l->data)) {
-				if (tracker_field_data_get_needs_join (l->data)) {
+				if (tracker_field_data_get_is_select (l->data) &&
+				    tracker_field_data_get_needs_join (l->data)) {
+					g_string_append_printf (data.sql_from,
+								"\n LEFT OUTER JOIN %s %s ON (S.ID = %s.ServiceID and %s.MetaDataID = %s) ",
+								tracker_field_data_get_table_name (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_alias (l->data),
+								tracker_field_data_get_id_field (l->data));
+				} else if (tracker_field_data_get_is_order (l->data) &&
+					   tracker_field_data_get_needs_collate (l->data)) {
 					g_string_append_printf (data.sql_from,
 								"\n LEFT OUTER JOIN %s %s ON (S.ID = %s.ServiceID and %s.MetaDataID = %s) ",
 								tracker_field_data_get_table_name (l->data),

Modified: trunk/src/libtracker-db/tracker-db-manager.c
==============================================================================
--- trunk/src/libtracker-db/tracker-db-manager.c	(original)
+++ trunk/src/libtracker-db/tracker-db-manager.c	Wed Mar 25 11:00:28 2009
@@ -1931,12 +1931,19 @@
 	}
 
 	if (g_strcmp0 (current_locale, stored_locale) != 0) {
+		guint collate_key;
 		/* Locales differ, update collate keys */
 		g_message ("Updating DB locale dependent data to: %s\n", current_locale);
 
 		iface = dbs[TRACKER_DB_FILE_METADATA].iface;
 		tracker_db_interface_execute_procedure (iface, NULL, "UpdateMetadataCollation", NULL);
 
+		for (collate_key = 1; collate_key<6; collate_key++) {
+			tracker_db_interface_execute_query (iface, NULL,
+			   		    "UPDATE Services SET KeyMetadataCollation%d=CollateKey(KeyMetadata%d)",
+					    collate_key, collate_key);
+		} 
+
 		iface = dbs[TRACKER_DB_EMAIL_METADATA].iface;
 		tracker_db_interface_execute_procedure (iface, NULL, "UpdateMetadataCollation", NULL);
[Date Prev][Date Next] [Thread Prev][Thread Next] [Thread Index] [Date Index] [Author Index]