GNOME.org
 

[evolution-data-server/openismus-work-master: 4/12] Handle summary fields and fields of interest better in e-book-backend-sqlitedb.c

commit 1ce7b0f614ed7ec5cfe2f10a8cbab51ee5c30387
Author: Tristan Van Berkom <tristan van berkom gmail com>
Date:   Fri Jul 29 19:28:08 2011 -0400

    Handle summary fields and fields of interest better in e-book-backend-sqlitedb.c
    
    This patch dramatically changes the sqlitedb cache code by introducing
    a table (array of structures) describing all of the fields which should
    be included in the (summary) cache. Thus, all code that treats the
    summary fields by hand previously now consults the cache generically.
    
    The REV field is added to the summary table, the UID is always returned
    in any results from e_book_backend_sqlitedb_search() and when
    'fields_of_interest' is specified then the sqlite3 db will only
    be queried for the fields_of_interest + UID (thus only those fields
    will be present in any virtually created vcard objects).

 .../libedata-book/e-book-backend-sqlitedb.c        |  354 +++++++++++++------
 1 files changed, 242 insertions(+), 112 deletions(-)
---
diff --git a/addressbook/libedata-book/e-book-backend-sqlitedb.c b/addressbook/libedata-book/e-book-backend-sqlitedb.c
index 728adf7..526050a 100644
--- a/addressbook/libedata-book/e-book-backend-sqlitedb.c
+++ b/addressbook/libedata-book/e-book-backend-sqlitedb.c
@@ -61,6 +61,41 @@ G_DEFINE_TYPE (EBookBackendSqliteDB, e_book_backend_sqlitedb, G_TYPE_OBJECT)
 static GHashTable *db_connections = NULL;
 static GStaticMutex dbcon_lock = G_STATIC_MUTEX_INIT;
 
+typedef struct {
+	EContactField field;            /* The EContact field */
+	GType         fundamental_type; /* The fundamental type (string or int) */
+	const gchar  *dbname;           /* The key for this field in the sqlite3 table */
+} SummeryField;
+
+static SummeryField summary_fields[] = {
+	{ E_CONTACT_UID,                 G_TYPE_STRING, "uid" },
+	{ E_CONTACT_REV,                 G_TYPE_STRING, "rev" },
+	{ E_CONTACT_FILE_AS,             G_TYPE_STRING, "file_as" },
+	{ E_CONTACT_NICKNAME,            G_TYPE_STRING, "nickname" },
+	{ E_CONTACT_FULL_NAME,           G_TYPE_STRING, "full_name" },
+	{ E_CONTACT_GIVEN_NAME,          G_TYPE_STRING, "given_name" },
+	{ E_CONTACT_FAMILY_NAME,         G_TYPE_STRING, "family_name" },
+	{ E_CONTACT_EMAIL_1,             G_TYPE_STRING, "email_1" },
+	{ E_CONTACT_EMAIL_2,             G_TYPE_STRING, "email_2" },
+	{ E_CONTACT_EMAIL_3,             G_TYPE_STRING, "email_3" },
+	{ E_CONTACT_EMAIL_4,             G_TYPE_STRING, "email_4" },
+	{ E_CONTACT_IS_LIST,             G_TYPE_INT,    "is_list" },
+	{ E_CONTACT_LIST_SHOW_ADDRESSES, G_TYPE_INT,    "list_show_addresses" },
+	{ E_CONTACT_WANTS_HTML,          G_TYPE_INT,    "wants_html" }
+};
+
+static const gchar *
+summary_dbname_from_field (EContactField field)
+{
+	gint i;
+
+	for (i = 0; i < G_N_ELEMENTS (summary_fields); i++) {
+		if (summary_fields[i].field == field)
+			return summary_fields[i].dbname;
+	}
+	return NULL;
+}
+
 static gint
 store_data_to_vcard (gpointer ref, gint ncol, gchar **cols, gchar **name);
 
@@ -352,21 +387,29 @@ create_contacts_table	(EBookBackendSqliteDB *ebsdb,
 			 const gchar *folderid,
 			 GError **error)
 {
-	gint ret;
+	gint ret, i;
 	gchar *stmt, *tmp;
 	GError *err = NULL;
+	GString *string;
+
+	/* Construct the create statement from the summary fields table */
+	string = g_string_new ("CREATE TABLE IF NOT EXISTS %Q ( uid TEXT PRIMARY KEY, ");
 
-	stmt = sqlite3_mprintf ("CREATE TABLE IF NOT EXISTS %Q"
-				"( uid  TEXT PRIMARY KEY,"
-				" nickname TEXT, full_name TEXT,"
-				" given_name TEXT, family_name TEXT,"
-				" file_as TEXT,"
-				" email_1 TEXT, email_2 TEXT,"
-				" email_3 TEXT, email_4 TEXT,"
-				" partial_content INTEGER,"
-				" is_list INTEGER, list_show_addresses INTEGER,"
-				" wants_html INTEGER,"
-				" vcard TEXT, bdata TEXT)", folderid);
+	for (i = 1; i < G_N_ELEMENTS (summary_fields); i++) {
+		g_string_append   (string, summary_fields[i].dbname);
+		g_string_append_c (string, ' ');
+
+		if (summary_fields[i].fundamental_type == G_TYPE_STRING)
+			g_string_append (string, "TEXT, ");
+		else if (summary_fields[i].fundamental_type == G_TYPE_INT)
+			g_string_append (string, "INTEGER, ");
+		else
+			g_assert_not_reached ();
+	}
+	g_string_append (string, "vcard TEXT, bdata TEXT)");
+
+	stmt = sqlite3_mprintf (string->str, folderid);
+	g_string_free (string, TRUE);
 
 	WRITER_LOCK (ebsdb);
 	ret = book_backend_sql_exec (ebsdb->priv->db, stmt, NULL, NULL , &err);
@@ -516,54 +559,55 @@ exit:
 	return ebsdb;
 }
 
-/* Add Contact */
+/* Add Contact (free the result with g_free() ) */
 static gchar *
 insert_stmt_from_contact	(EContact *contact,
 				 gboolean partial_content,
 				 const gchar *folderid,
 				 gboolean store_vcard)
 {
-	gchar *stmt = NULL;
-	gchar *id, *nickname, *full_name;
-	gchar *given_name, *surname, *file_as;
-	gchar *email_1, *email_2, *email_3, *email_4;
-	gchar *vcard_str = NULL;
-	gint wants_html, is_list, list_show_addresses;
-
-	id          = e_contact_get (contact, E_CONTACT_UID);
-	nickname    = e_contact_get (contact, E_CONTACT_NICKNAME);
-	full_name   = e_contact_get (contact, E_CONTACT_FULL_NAME);
-	given_name  = e_contact_get (contact, E_CONTACT_GIVEN_NAME);
-	surname     = e_contact_get (contact, E_CONTACT_FAMILY_NAME);
-	file_as     = e_contact_get (contact, E_CONTACT_FILE_AS);
-	email_1     = e_contact_get (contact, E_CONTACT_EMAIL_1);
-	email_2     = e_contact_get (contact, E_CONTACT_EMAIL_2);
-	email_3     = e_contact_get (contact, E_CONTACT_EMAIL_3);
-	email_4     = e_contact_get (contact, E_CONTACT_EMAIL_4);
-	is_list     = GPOINTER_TO_INT (e_contact_get (contact, E_CONTACT_IS_LIST));
-	wants_html  = GPOINTER_TO_INT (e_contact_get (contact, E_CONTACT_WANTS_HTML));
-	list_show_addresses = GPOINTER_TO_INT (e_contact_get (contact, E_CONTACT_LIST_SHOW_ADDRESSES));
-
-	if (store_vcard)
-		vcard_str = e_vcard_to_string (E_VCARD (contact), EVC_FORMAT_VCARD_30);
-
-	stmt = sqlite3_mprintf ("INSERT or REPLACE INTO %Q VALUES (%Q, %Q, %Q, "
-		"%Q, %Q, %Q, %Q, %Q, %Q, %Q, %d, %d, %d, %d, %Q, %Q)", folderid, id, nickname,
-				full_name, given_name, surname, file_as, email_1,
-				email_2, email_3, email_4, partial_content, is_list, wants_html, list_show_addresses, vcard_str, NULL);
-
-	g_free (id);
-	g_free (nickname);
-	g_free (given_name);
-	g_free (surname);
-	g_free (file_as);
-	g_free (email_1);
-	g_free (email_2);
-	g_free (email_3);
-	g_free (email_4);
+	GString *string;
+	gchar   *str, *vcard_str;
+	gint     i;
+
+	str    = sqlite3_mprintf ("INSERT or REPLACE INTO %Q VALUES (", folderid);
+	string = g_string_new (str);
+	sqlite3_free (str);
+
+	for (i = 0; i < G_N_ELEMENTS (summary_fields); i++) {
+		if (i > 0)
+			g_string_append (string, ", ");
+
+		if (summary_fields[i].fundamental_type == G_TYPE_STRING) {
+			gchar *val;
+
+			val = e_contact_get (contact, summary_fields[i].field);
+			str = sqlite3_mprintf ("%Q", val);
+
+			g_string_append (string, str);
+
+			sqlite3_free (str);
+			g_free (val);
+
+		} else if (summary_fields[i].fundamental_type == G_TYPE_INT) {
+			gint val;
+
+			val = GPOINTER_TO_INT (e_contact_get (contact, summary_fields[i].field));
+			g_string_append_printf (string, "%d", val);
+
+		} else
+			g_assert_not_reached ();
+	}
+
+	vcard_str = store_vcard ? e_vcard_to_string (E_VCARD (contact), EVC_FORMAT_VCARD_30) : NULL;
+	str       = sqlite3_mprintf (", %Q, %Q)", vcard_str, NULL);
+
+	g_string_append (string, str);
+
+	sqlite3_free (str);
 	g_free (vcard_str);
 
-	return stmt;
+	return g_string_free (string, FALSE);
 }
 
 /**
@@ -631,7 +675,7 @@ e_book_backend_sqlitedb_add_contacts	(EBookBackendSqliteDB *ebsdb,
 						 priv->store_vcard);
 		book_backend_sql_exec (priv->db, stmt, NULL, NULL, &err);
 
-		sqlite3_free (stmt);
+		g_free (stmt);
 	}
 
 	book_backend_sqlitedb_end_transaction (ebsdb, &err);
@@ -782,13 +826,63 @@ e_book_backend_sqlitedb_get_contact	(EBookBackendSqliteDB *ebsdb,
 	return contact;
 }
 
+/* free return value with g_free */
+static gchar *
+summary_select_stmt (const gchar *folderid,
+		     GSList      *fields_of_interest)
+{
+	GString   *string;
+	GSList    *l;
+	gchar     *str;
+	gint       i;
+
+	string = g_string_new ("SELECT uid");
+
+	/* If filtering by fields of interest, only query those and include the 'uid' */
+	if (fields_of_interest) {
+
+		for (l = fields_of_interest; l; l = l->next) {
+			EContactField field = e_contact_field_id ((const gchar *)l->data);
+			const gchar *dbname = NULL;
+
+			if (field == E_CONTACT_UID)
+				continue;
+
+			dbname = summary_dbname_from_field (field);
+
+			/* The field of interest is not in the summary information,
+			 * technically we shouldnt reach this case
+			 */
+			if (!dbname)
+				continue;
+
+			g_string_append (string, ", ");
+			g_string_append (string, dbname);
+		}
+
+	} else {
+		/* ... Otherwise just select all the summary information */
+		for (i = 1; i < G_N_ELEMENTS (summary_fields); i++) {
+			g_string_append (string, ", ");
+			g_string_append (string, summary_fields[i].dbname);
+		}
+	}
+
+	str = sqlite3_mprintf (" FROM %Q", folderid);
+	g_string_append (string, str);
+	sqlite3_free (str);
+
+	return g_string_free (string, FALSE);
+}
+
+
 gchar *
 e_book_backend_sqlitedb_get_vcard_string	(EBookBackendSqliteDB *ebsdb,
 						 const gchar *folderid,
 						 const gchar *uid,
 						 GError **error)
 {
-	gchar *stmt;
+	gchar *stmt, *select_stmt;
 	gchar *vcard_str = NULL;
 
 	READER_LOCK (ebsdb);
@@ -796,19 +890,22 @@ e_book_backend_sqlitedb_get_vcard_string	(EBookBackendSqliteDB *ebsdb,
 	if (!ebsdb->priv->store_vcard) {
 		GSList *vcards = NULL;
 
-		stmt = sqlite3_mprintf ("SELECT uid, nickname, full_name, given_name, family_name, file_as, email_1, email_2, " 
-					"email_3, is_list, list_show_addresses, wants_html FROM %Q WHERE uid = %Q", folderid, uid);
+		select_stmt = summary_select_stmt (folderid, NULL);
+		stmt        = sqlite3_mprintf ("%s WHERE uid = %Q", select_stmt, uid);
+
 		book_backend_sql_exec (ebsdb->priv->db, stmt, store_data_to_vcard, &vcards, error);
+
 		sqlite3_free (stmt);
+		g_free (select_stmt);
 
 		if (vcards) {
 			EbSdbSearchData *s_data = (EbSdbSearchData *) vcards->data;
 
-			vcard_str = s_data->vcard;
+			vcard_str     = s_data->vcard;
+			s_data->vcard = NULL;
+
+			e_book_backend_sqlitedb_search_data_free (s_data);
 
-			g_free (s_data->uid);
-			g_free (s_data->bdata);
-			g_free (s_data);
 			g_slist_free (vcards);
 			vcards = NULL;
 		}
@@ -832,13 +929,14 @@ func_check (struct _ESExp *f, gint argc, struct _ESExpResult **argv, gpointer da
 	if (argc == 2
 	    && argv[0]->type == ESEXP_RES_STRING
 	    && argv[1]->type == ESEXP_RES_STRING) {
+
 		gchar *query_name = argv[0]->value.string;
+		gint   i;
+
+		for (i = 0; truth == FALSE && i < G_N_ELEMENTS (summary_fields); i++) {
 
-		if (!strcmp (query_name, "nickname") ||
-		    !strcmp (query_name, "full_name") ||
-		    !strcmp (query_name, "file_as") ||
-		    !strcmp (query_name, "email")) {
-			truth = TRUE;
+			if (!strcmp (e_contact_field_name (summary_fields[i].field), query_name))
+				truth = TRUE;
 		}
 	}
 
@@ -863,7 +961,26 @@ static const struct {
 };
 
 static gboolean
-book_backend_sqlitedb_is_summary_query (const gchar *query)
+book_backend_sqlitedb_is_summary_fields (GSList *fields_of_interest)
+{
+	gboolean summary_fields = TRUE;
+	GSList *l;
+
+	for (l = fields_of_interest; l; l = l->next) {
+		const gchar    *field_name = l->data;
+		EContactField   field = e_contact_field_id (field_name);
+
+		if (!summary_dbname_from_field (field)) {
+			summary_fields = FALSE;
+			break;
+		}
+	}
+
+	return summary_fields;
+}
+
+static gboolean
+book_backend_sqlitedb_is_summary_query (const gchar *query, GSList *fields_of_interest)
 {
 	ESExp *sexp;
 	ESExpResult *r;
@@ -871,6 +988,10 @@ book_backend_sqlitedb_is_summary_query (const gchar *query)
 	gint i;
 	gint esexp_error;
 
+	if (fields_of_interest && 
+	    book_backend_sqlitedb_is_summary_fields (fields_of_interest))
+		return TRUE;
+
 	sexp = e_sexp_new ();
 
 	for (i = 0; i < G_N_ELEMENTS (check_symbols); i++) {
@@ -1084,7 +1205,7 @@ addto_vcard_list_cb (gpointer ref, gint col, gchar **cols, gchar **name)
 		s_data->vcard = g_strdup (cols[1]);
 
 	if (cols[2])
-		s_data->bdata = g_strdup (cols[1]);
+		s_data->bdata = g_strdup (cols[2]);
 
 	*vcard_data = g_slist_prepend (*vcard_data, s_data);
 
@@ -1109,47 +1230,41 @@ store_data_to_vcard (gpointer ref, gint ncol, gchar **cols, gchar **name)
 	EbSdbSearchData *search_data = g_new0 (EbSdbSearchData, 1);
 	EContact *contact = e_contact_new ();
 	gchar *vcard;
-	gint i;
+	gint i, j;
 
 	/* parse through cols, this will be useful if the api starts supporting field restrictions */
 	for (i = 0; i < ncol; i++)
 	{
+		gboolean found = FALSE;
+
 		if (!name[i] || !cols[i])
 			continue;
 
-		if (!strcmp (name [i], "uid")) {
-			e_contact_set (contact, E_CONTACT_UID, cols[i]);
-
-			search_data->uid = g_strdup (cols[i]);
-		} else if (!strcmp (name [i], "nickname"))
-			e_contact_set (contact, E_CONTACT_NICKNAME, cols[i]);
-		else if (!strcmp (name [i], "full_name"))
-			e_contact_set (contact, E_CONTACT_FULL_NAME, cols[i]);
-		else if (!strcmp (name [i], "given_name"))
-			e_contact_set (contact, E_CONTACT_GIVEN_NAME, cols[i]);
-		else if (!strcmp (name [i], "family_name"))
-			e_contact_set (contact, E_CONTACT_FAMILY_NAME, cols[i]);
-		else if (!strcmp (name [i], "file_as"))
-			e_contact_set (contact, E_CONTACT_FILE_AS, cols[i]);
-		else if (!strcmp (name [i], "email_1"))
-			e_contact_set (contact, E_CONTACT_EMAIL_1, cols[i]);
-		else if (!strcmp (name [i], "email_2"))
-			e_contact_set (contact, E_CONTACT_EMAIL_2, cols[i]);
-		else if (!strcmp (name [i], "email_3"))
-			e_contact_set (contact, E_CONTACT_EMAIL_3, cols[i]);
-		else if (!strcmp (name [i], "is_list")) {
-			gint val = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
-			e_contact_set (contact, E_CONTACT_IS_LIST, GINT_TO_POINTER (val));
-		} else if (!strcmp (name [i], "list_show_addresses")) {
-			gint val = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
-			e_contact_set (contact, E_CONTACT_LIST_SHOW_ADDRESSES, GINT_TO_POINTER (val));
-		} else if (!strcmp (name [i], "wants_html")) {
-			gint val = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
-			e_contact_set (contact, E_CONTACT_WANTS_HTML, GINT_TO_POINTER (val));
-		} else if (!strcmp (name [i], "bdata")) {
-			search_data->bdata = g_strdup (cols[i]);
+		for (j = 0; j < G_N_ELEMENTS (summary_fields); j++) {
+
+			if (!strcmp (name[i], summary_fields[j].dbname)) {
+
+				if (summary_fields[j].fundamental_type == G_TYPE_STRING)
+					e_contact_set (contact, summary_fields[j].field, cols[i]);
+				else if (summary_fields[j].fundamental_type == G_TYPE_INT) {
+					gint val = cols[i] ? strtoul (cols[i], NULL, 10) : 0;
+					e_contact_set (contact, summary_fields[j].field, GINT_TO_POINTER (val));
+				} else
+					g_assert_not_reached ();
+
+				if (summary_fields[j].field == E_CONTACT_UID)
+					search_data->uid = g_strdup (cols[i]);
+
+				found = TRUE;
+				break;
+			}
 		}
 
+		if (found)
+			continue;
+
+		if (!strcmp (name [i], "bdata"))
+			search_data->bdata = g_strdup (cols[i]);
 	}
 
 	vcard = e_vcard_to_string (E_VCARD (contact), EVC_FORMAT_VCARD_30);
@@ -1169,20 +1284,35 @@ book_backend_sqlitedb_search_query	(EBookBackendSqliteDB *ebsdb,
 {
 	GError *err = NULL;
 	GSList *vcard_data = NULL;
-	gchar *stmt;
+	gchar  *stmt, *select_stmt;
 
 	READER_LOCK (ebsdb);
 
-	/* TODO enable return just the requested fields. */
-	if (!ebsdb->priv->store_vcard || fields_of_interest) {
-		stmt = sqlite3_mprintf ("SELECT uid, nickname, full_name, given_name, family_name, file_as, email_1, email_2, " 
-					"email_3, is_list, list_show_addresses, wants_html FROM %Q WHERE %s", folderid, sql);
-		book_backend_sql_exec (ebsdb->priv->db, stmt, store_data_to_vcard, &vcard_data, &err);
-		sqlite3_free (stmt);
+	if (!ebsdb->priv->store_vcard) {
+
+		select_stmt = summary_select_stmt (folderid, fields_of_interest);
+
+		if (sql && sql[0]) {
+			stmt = sqlite3_mprintf ("%s WHERE %s", select_stmt, sql);
+
+			book_backend_sql_exec (ebsdb->priv->db, stmt, store_data_to_vcard, &vcard_data, &err);
+			sqlite3_free (stmt);
+		} else
+			book_backend_sql_exec (ebsdb->priv->db, select_stmt, 
+					       store_data_to_vcard, &vcard_data, &err);
+
+		g_free (select_stmt);
+
 	} else {
-		stmt = sqlite3_mprintf ("SELECT uid, vcard, bdata FROM %Q WHERE %s", folderid, sql);
-		book_backend_sql_exec (ebsdb->priv->db, stmt, addto_vcard_list_cb , &vcard_data, &err);
-		sqlite3_free (stmt);
+		if (sql && sql[0]) {
+			stmt = sqlite3_mprintf ("SELECT uid, vcard, bdata FROM %Q WHERE %s", folderid, sql);
+			book_backend_sql_exec (ebsdb->priv->db, stmt, addto_vcard_list_cb , &vcard_data, &err);
+			sqlite3_free (stmt);
+		} else {
+			stmt = sqlite3_mprintf ("SELECT uid, vcard, bdata FROM %Q", folderid);
+			book_backend_sql_exec (ebsdb->priv->db, stmt, addto_vcard_list_cb , &vcard_data, &err);
+			sqlite3_free (stmt);
+		}
 	}
 
 	READER_UNLOCK (ebsdb);
@@ -1242,7 +1372,7 @@ book_backend_sqlitedb_search_full (EBookBackendSqliteDB *ebsdb, const gchar *sex
  * @ebsdb: 
  * @folderid: 
  * @sexp: search expression.
- * &fields_of_interest: a #GList containing the names of fields to return, or NULL for all. 
+ * @fields_of_interest: a #GList containing the names of fields to return, or NULL for all. 
  *  At the moment if this is non-null, the vcard will be populated with summary fields, else it would return the 
  *  whole vcard if its stored in the db. [not implemented fully]
  * @error: 
@@ -1263,7 +1393,7 @@ e_book_backend_sqlitedb_search	(EBookBackendSqliteDB *ebsdb,
 {
 	GSList *search_contacts = NULL;
 
-	if (book_backend_sqlitedb_is_summary_query (sexp)) {
+	if (book_backend_sqlitedb_is_summary_query (sexp, fields_of_interest)) {
 		gchar *sql_query;
 
 		sql_query = sexp_to_sql_query (sexp);
@@ -1287,7 +1417,7 @@ e_book_backend_sqlitedb_search_uids	(EBookBackendSqliteDB *ebsdb,
 {
 	GSList *uids = NULL;
 
-	if (book_backend_sqlitedb_is_summary_query (sexp)) {
+	if (book_backend_sqlitedb_is_summary_query (sexp, NULL)) {
 		gchar *stmt;
 		gchar *sql_query = sexp_to_sql_query (sexp);
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]