From 205239278936f316ce97df4223f4eb5aeaa3aca6 Mon Sep 17 00:00:00 2001 From: Tristan Van Berkom Date: Mon, 22 Apr 2013 20:42:14 +0900 Subject: [PATCH] EBookBackendSqliteDB: Adding cursor APIs Added the following APIs: o e_book_backend_sqlitedb_cursor_new() Creates a cursor for a given query expression and sort order o e_book_backend_sqlitedb_cursor_free() Frees a cursor and it's resources o e_book_backend_sqlitedb_cursor_move_by() Moves the cursor and fetches results o e_book_backend_sqlitedb_cursor_set_target() Sets the cursor state to a given position in the results o e_book_backend_sqlite_cursor_calculate() Calculates the position / total values of a cursor. o e_book_backend_sqlitedb_cursor_set_sexp() Sets the search expression for a given cursor Also restored DB overwrite when country code changes. --- .../libebook-contacts/e-book-contacts-types.h | 12 + .../libedata-book/e-book-backend-sqlitedb.c | 1124 +++++++++++++++++++- .../libedata-book/e-book-backend-sqlitedb.h | 51 + 3 files changed, 1146 insertions(+), 41 deletions(-) diff --git a/addressbook/libebook-contacts/e-book-contacts-types.h b/addressbook/libebook-contacts/e-book-contacts-types.h index 7208b67..964f57e 100644 --- a/addressbook/libebook-contacts/e-book-contacts-types.h +++ b/addressbook/libebook-contacts/e-book-contacts-types.h @@ -135,6 +135,18 @@ typedef enum { E_BOOK_INDEX_PHONE } EBookIndexType; +/** + * EBookSortType: + * @E_BOOK_SORT_ASCENDING: Sort results in ascending order + * @E_BOOK_SORT_ASCENDING: Sort results in descending order + * + * Specifies the sort order of an ordered query + */ +typedef enum { + E_BOOK_SORT_ASCENDING = 0, + E_BOOK_SORT_DESCENDING +} EBookSortType; + GQuark e_book_client_error_quark (void) G_GNUC_CONST; const gchar * e_book_client_error_to_string (EBookClientError code); diff --git a/addressbook/libedata-book/e-book-backend-sqlitedb.c b/addressbook/libedata-book/e-book-backend-sqlitedb.c index 374cd86..6308c78 100644 --- a/addressbook/libedata-book/e-book-backend-sqlitedb.c +++ b/addressbook/libedata-book/e-book-backend-sqlitedb.c @@ -59,7 +59,7 @@ #endif #define DB_FILENAME "contacts.db" -#define FOLDER_VERSION 6 +#define FOLDER_VERSION 7 typedef enum { INDEX_PREFIX = (1 << 0), @@ -123,6 +123,12 @@ static EBookIndexType default_index_types[] = { E_BOOK_INDEX_PREFIX }; +static void +destroy_search_data (gpointer data) +{ + e_book_backend_sqlitedb_search_data_free (data); +} + static SummaryField * append_summary_field (GArray *array, EContactField field, gboolean *have_attr_list, @@ -130,6 +136,7 @@ static SummaryField * append_summary_field (GArray *array, static gboolean upgrade_contacts_table (EBookBackendSqliteDB *ebsdb, const gchar *folderid, + const gchar *region, GError **error); static const gchar * @@ -473,6 +480,30 @@ collect_versions_cb (gpointer ref, return 0; } +typedef struct { + gboolean has_countrycode; + gboolean has_lc_collate; +} LocaleColumns; + +static gint +find_locale_columns (gpointer data, + gint n_cols, + gchar **cols, + gchar **name) +{ + LocaleColumns *columns = (LocaleColumns *)data; + gint i; + + for (i = 0; i < n_cols; i++) { + if (g_strcmp0 (cols[i], "countrycode") == 0) + columns->has_countrycode = TRUE; + else if (g_strcmp0 (cols[i], "lc_collate") == 0) + columns->has_lc_collate = TRUE; + } + + return 0; +} + static gboolean create_folders_table (EBookBackendSqliteDB *ebsdb, gint *previous_schema, @@ -480,6 +511,7 @@ create_folders_table (EBookBackendSqliteDB *ebsdb, { gboolean success; gint version = 0; + LocaleColumns locale_columns = { FALSE, FALSE }; /* sync_data points to syncronization data, it could be last_modified * time or a sequence number or some text depending on the backend. @@ -561,7 +593,7 @@ create_folders_table (EBookBackendSqliteDB *ebsdb, * create_contacts_table() as we need introspection details for doing * that. */ - if (version >= 3 && version < 5) { + if (version >= 3 && version < FOLDER_VERSION) { stmt = "UPDATE folders SET " "multivalues = REPLACE(RTRIM(REPLACE(" "multivalues || ':', ':', " @@ -593,6 +625,36 @@ create_folders_table (EBookBackendSqliteDB *ebsdb, if (!success) goto rollback; + /* Adding countrycode column again to track the addressbook's country code. + * Detecting presence of this column by reflection instead of assigning a + * dedicated schema version number, because upstream is not going to accept + * a locale bound addressbook database. See discussion in bug 689622. + */ + stmt = "PRAGMA table_info(folders)"; + success = book_backend_sql_exec ( + ebsdb->priv->db, stmt, find_locale_columns, &locale_columns, error); + + if (!success) + goto rollback; + + if (!locale_columns.has_countrycode) { + stmt = "ALTER TABLE folders ADD COLUMN countrycode VARCHAR(2)"; + success = book_backend_sql_exec (ebsdb->priv->db, stmt, NULL, NULL, error); + + if (!success) + goto rollback; + + } + + if (!locale_columns.has_lc_collate) { + stmt = "ALTER TABLE folders ADD COLUMN lc_collate TEXT"; + success = book_backend_sql_exec (ebsdb->priv->db, stmt, NULL, NULL, error); + + if (!success) + goto rollback; + } + + /* Remember the schema version for later use and finish the transaction. */ *previous_schema = version; return book_backend_sqlitedb_commit_transaction (ebsdb, error); @@ -709,7 +771,7 @@ get_count_cb (gpointer ref, gint i; for (i = 0; i < n_cols; i++) { - if (g_strcmp0 (name[i], "count(*)") == 0) { + if (name[i] && strncmp (name[i], "count", 5) == 0) { count = g_ascii_strtoll (cols[i], NULL, 10); } } @@ -769,6 +831,10 @@ introspect_summary (EBookBackendSqliteDB *ebsdb, gchar *p; IndexFlags computed = 0; + /* Ignore the 'localized' columns */ + if (g_str_has_suffix (col, "_localized")) + continue; + /* Check if we're parsing a reverse field */ if ((p = strstr (col, "_reverse")) != NULL) { computed = INDEX_SUFFIX; @@ -889,11 +955,20 @@ create_contacts_table (EBookBackendSqliteDB *ebsdb, gchar *stmt, *tmp; GString *string; gboolean already_exists = FALSE; + gboolean relocalized = FALSE; + gchar *current_region = NULL; success = check_folderid_exists (ebsdb, folderid, &already_exists, error); if (!success) return FALSE; + if (e_phone_number_is_supported ()) { + current_region = e_phone_number_get_default_region (error); + + if (current_region == NULL) + return FALSE; + } + string = g_string_new ( "CREATE TABLE IF NOT EXISTS %Q ( uid TEXT PRIMARY KEY, "); @@ -901,6 +976,15 @@ create_contacts_table (EBookBackendSqliteDB *ebsdb, if (ebsdb->priv->summary_fields[i].type == G_TYPE_STRING) { g_string_append (string, ebsdb->priv->summary_fields[i].dbname); g_string_append (string, " TEXT, "); + + /* For any string columns (not multivalued columns), also create a localized + * data column for sort ordering + */ + if (ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + g_string_append (string, "_localized TEXT, "); + } + } else if (ebsdb->priv->summary_fields[i].type == G_TYPE_BOOLEAN) { g_string_append (string, ebsdb->priv->summary_fields[i].dbname); g_string_append (string, " INTEGER, "); @@ -930,6 +1014,29 @@ create_contacts_table (EBookBackendSqliteDB *ebsdb, sqlite3_free (stmt); + /* Now, if we're upgrading from < version 7, we need to add the _localized columns */ + if (success && previous_schema >= 1 && previous_schema < 7) { + + tmp = sqlite3_mprintf ("ALTER TABLE %Q ADD COLUMN ", folderid); + + for (i = 1; i < ebsdb->priv->n_summary_fields && success; i++) { + + if (ebsdb->priv->summary_fields[i].type == G_TYPE_STRING && + ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { + string = g_string_new (tmp); + + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + g_string_append (string, "_localized TEXT, "); + + success = book_backend_sql_exec ( + ebsdb->priv->db, string->str, NULL, NULL , error); + + g_string_free (string, TRUE); + } + } + sqlite3_free (tmp); + } + /* Construct the create statement from the attribute list summary table */ if (success && ebsdb->priv->have_attr_list) { string = g_string_new ("CREATE TABLE IF NOT EXISTS %Q ( uid TEXT NOT NULL REFERENCES %Q(uid), " @@ -995,6 +1102,20 @@ create_contacts_table (EBookBackendSqliteDB *ebsdb, success = book_backend_sql_exec (ebsdb->priv->db, stmt, NULL, NULL, error); sqlite3_free (stmt); g_free (tmp); + + /* For any indexed column, also index the localized column */ + if (ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { + tmp = g_strdup_printf ( + "INDEX_%s_localized_%s", + summary_dbname_from_field (ebsdb, ebsdb->priv->summary_fields[i].field), + folderid); + stmt = sqlite3_mprintf ( + "CREATE INDEX IF NOT EXISTS %Q ON %Q (%s_localized)", tmp, folderid, + summary_dbname_from_field (ebsdb, ebsdb->priv->summary_fields[i].field)); + success = book_backend_sql_exec (ebsdb->priv->db, stmt, NULL, NULL, error); + sqlite3_free (stmt); + g_free (tmp); + } } if (success && @@ -1029,9 +1150,47 @@ create_contacts_table (EBookBackendSqliteDB *ebsdb, } } - /* Until version 6, the whole contacts table requires a re-normalization of the data */ - if (success && previous_schema < 6) - success = upgrade_contacts_table (ebsdb, folderid, error); + /* Need to relocalize the whole thing if the schema has been upgraded to version 7 */ + if (success && previous_schema >= 1 && previous_schema < 7) { + success = upgrade_contacts_table (ebsdb, folderid, current_region, error); + relocalized = TRUE; + } + + /* We may need to relocalize for a country code change */ + if (success && relocalized == FALSE && e_phone_number_is_supported ()) { + gchar *stored_region = NULL; + + stmt = sqlite3_mprintf ("SELECT countrycode FROM folders WHERE folder_id = %Q", folderid); + success = book_backend_sql_exec (ebsdb->priv->db, stmt, get_string_cb, &stored_region, error); + sqlite3_free (stmt); + + if (success && g_strcmp0 (current_region, stored_region) != 0) { + success = upgrade_contacts_table (ebsdb, folderid, current_region, error); + relocalized = TRUE; + } + + g_free (stored_region); + } + + /* We may need to relocalize for an LC_COLLATE locale change */ + if (success && relocalized == FALSE) { + const gchar *lc_collate = NULL; + gchar *stored_lc_collate; + + stmt = sqlite3_mprintf ("SELECT lc_collate FROM folders WHERE folder_id = %Q", folderid); + success = book_backend_sql_exec (ebsdb->priv->db, stmt, get_string_cb, &stored_lc_collate, error); + sqlite3_free (stmt); + + lc_collate = setlocale (LC_COLLATE, NULL); + + if (success && g_strcmp0 (stored_lc_collate, lc_collate) != 0) { + success = upgrade_contacts_table (ebsdb, folderid, current_region, error); + relocalized = TRUE; + } + + g_free (stored_lc_collate); + } + g_free (current_region); return success; } @@ -1793,32 +1952,90 @@ insert_stmt_from_contact (EBookBackendSqliteDB *ebsdb, gint i; str = sqlite3_mprintf ( - "INSERT or %s INTO %Q VALUES (", + "INSERT or %s INTO %Q (", replace_existing ? "REPLACE" : "FAIL", folderid); string = g_string_new (str); sqlite3_free (str); + /* + * First specify the column names for the insert, since it's possible we + * upgraded the DB and cannot be sure the order of the columns are ordered + * just how we like them to be. + */ + for (i = 0; i < ebsdb->priv->n_summary_fields; i++) { + + /* Multi values go into a separate table/statement */ + if (ebsdb->priv->summary_fields[i].type != E_TYPE_CONTACT_ATTR_LIST) { + + if (i > 0) + g_string_append (string, ", "); + + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + } + + if (ebsdb->priv->summary_fields[i].type == G_TYPE_STRING) { + + if (ebsdb->priv->summary_fields[i].field != E_CONTACT_UID && + ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { + g_string_append (string, ", "); + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + g_string_append (string, "_localized"); + } + + if ((ebsdb->priv->summary_fields[i].index & INDEX_SUFFIX) != 0) { + g_string_append (string, ", "); + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + g_string_append (string, "_reverse"); + } + + if ((ebsdb->priv->summary_fields[i].index & INDEX_PHONE) != 0) { + g_string_append (string, ", "); + g_string_append (string, ebsdb->priv->summary_fields[i].dbname); + g_string_append (string, "_phone"); + } + } + } + g_string_append (string, ", vcard, bdata)"); + + /* + * Now specify values for all of the column names we specified. + */ + g_string_append (string, " VALUES ("); for (i = 0; i < ebsdb->priv->n_summary_fields; i++) { if (ebsdb->priv->summary_fields[i].type == G_TYPE_STRING) { gchar *val; gchar *normal; + gchar *localized = NULL; if (i > 0) g_string_append (string, ", "); val = e_contact_get (contact, ebsdb->priv->summary_fields[i].field); - /* Special exception, never normalize the UID or REV string */ + /* Special exception, never normalize/localize the UID or REV string */ if (ebsdb->priv->summary_fields[i].field != E_CONTACT_UID && - ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) + ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { normal = e_util_utf8_normalize (val); - else + + if (val) + localized = g_utf8_collate_key (val, strlen (val)); + else + localized = g_strdup (""); + } else normal = g_strdup (val); str = sqlite3_mprintf ("%Q", normal); g_string_append (string, str); sqlite3_free (str); + if (ebsdb->priv->summary_fields[i].field != E_CONTACT_UID && + ebsdb->priv->summary_fields[i].field != E_CONTACT_REV) { + str = sqlite3_mprintf ("%Q", localized); + g_string_append (string, ", "); + g_string_append (string, str); + sqlite3_free (str); + } + if ((ebsdb->priv->summary_fields[i].index & INDEX_SUFFIX) != 0) { str = mprintf_suffix (normal); g_string_append (string, ", "); @@ -1835,6 +2052,7 @@ insert_stmt_from_contact (EBookBackendSqliteDB *ebsdb, g_free (normal); g_free (val); + g_free (localized); } else if (ebsdb->priv->summary_fields[i].type == G_TYPE_BOOLEAN) { gboolean val; @@ -2629,9 +2847,7 @@ e_book_backend_sqlitedb_get_vcard_string (EBookBackendSqliteDB *ebsdb, vcard_str = s_data->vcard; s_data->vcard = NULL; - e_book_backend_sqlitedb_search_data_free (s_data); - - g_slist_free (vcards); + g_slist_free_full (vcards, destroy_search_data); vcards = NULL; } @@ -4652,22 +4868,17 @@ e_book_backend_sqlitedb_remove (EBookBackendSqliteDB *ebsdb, return TRUE; } -static void -destroy_search_data (gpointer data) -{ - e_book_backend_sqlitedb_search_data_free (data); -} - static gboolean -upgrade_contacts_table (EBookBackendSqliteDB *ebsdb, - const gchar *folderid, - GError **error) +upgrade_contacts_table (EBookBackendSqliteDB *ebsdb, + const gchar *folderid, + const gchar *region, + GError **error) { gchar *stmt; + const gchar *lc_collate; gboolean success = FALSE; GSList *vcard_data = NULL; GSList *l; - gchar *default_region = NULL; stmt = sqlite3_mprintf ("SELECT uid, vcard, NULL FROM %Q", folderid); success = book_backend_sql_exec ( @@ -4677,38 +4888,869 @@ upgrade_contacts_table (EBookBackendSqliteDB *ebsdb, if (vcard_data == NULL) return TRUE; - if (e_phone_number_is_supported ()) { - default_region = e_phone_number_get_default_region (error); + for (l = vcard_data; success && l; l = l->next) { + EbSdbSearchData *const s_data = l->data; + EContact *contact = e_contact_new_from_vcard_with_uid (s_data->vcard, s_data->uid); - if (default_region == NULL) - success = FALSE; + if (contact == NULL) + continue; + + success = insert_contact (ebsdb, contact, folderid, TRUE, region, error); + + g_object_unref (contact); } - success = book_backend_sqlitedb_start_transaction (ebsdb, error); + g_slist_free_full (vcard_data, destroy_search_data); if (success) { + lc_collate = setlocale (LC_COLLATE, NULL); - for (l = vcard_data; success && l; l = l->next) { - EbSdbSearchData *const s_data = l->data; - EContact *contact = e_contact_new_from_vcard_with_uid (s_data->vcard, s_data->uid); + stmt = sqlite3_mprintf ( + "UPDATE folders SET countrycode = %Q WHERE folder_id = %Q", + region, folderid); + success = book_backend_sql_exec ( + ebsdb->priv->db, stmt, NULL, NULL, error); + sqlite3_free (stmt); - if (contact == NULL) - continue; + stmt = sqlite3_mprintf ( + "UPDATE folders SET lc_collate = %Q WHERE folder_id = %Q", + lc_collate, folderid); + success = book_backend_sql_exec ( + ebsdb->priv->db, stmt, NULL, NULL, error); + sqlite3_free (stmt); + } + + return success; +} + +/****************************************************************** + * EbSdbCursor apis * + ******************************************************************/ +struct _EbSdbCursor { + gchar *folderid; /* The folderid for this cursor */ + + gchar *select_vcards; /* The first fragment when querying results */ + gchar *select_count; /* The first fragment when querying contact counts */ + gchar *query; /* The SQL query expression derived from the passed search expression */ + gchar *order; /* The normal order SQL query fragment to append at the end, containing ORDER BY etc */ + gchar *reverse_order; /* The reverse order SQL query fragment to append at the end, containing ORDER BY etc */ + + EContactField *sort_fields; /* The fields to sort in a query in the order or sort priority */ + EBookSortType *sort_types; /* The sort method to use for each field */ + gchar **values; /* The current cursor position, results will be returned after this position */ + + gint n_sort_fields; /* The amound of sort fields */ + + gchar *last_uid; /* The current cursor contact UID position, used as a tie breaker */ +}; + +static void +ebsdb_cursor_setup_query (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + const gchar *sexp, + gboolean query_with_list_attrs) +{ + gchar *stmt; + gchar *count_stmt; + + g_free (cursor->select_vcards); + g_free (cursor->select_count); + g_free (cursor->query); + + if (query_with_list_attrs) { + gchar *list_table = g_strconcat (cursor->folderid, "_lists", NULL); + + stmt = sqlite3_mprintf ("SELECT DISTINCT summary.uid, vcard, bdata FROM %Q AS summary " + "LEFT OUTER JOIN %Q AS multi ON summary.uid = multi.uid", + cursor->folderid, list_table); + + count_stmt = sqlite3_mprintf ("SELECT count(DISTINCT summary.uid), vcard, bdata FROM %Q AS summary " + "LEFT OUTER JOIN %Q AS multi ON summary.uid = multi.uid", + cursor->folderid, list_table); + g_free (list_table); + } else { + stmt = sqlite3_mprintf ("SELECT uid, vcard, bdata FROM %Q AS summary", cursor->folderid); + count_stmt = sqlite3_mprintf ("SELECT count(*) FROM %Q AS summary", cursor->folderid); + } + + cursor->select_vcards = g_strdup (stmt); + cursor->select_count = g_strdup (count_stmt); + sqlite3_free (stmt); + sqlite3_free (count_stmt); + + cursor->query = sexp ? sexp_to_sql_query (ebsdb, cursor->folderid, sexp) : NULL; +} + +static gchar * +ebsdb_cursor_order_by_fragment (EBookBackendSqliteDB *ebsdb, + EContactField *sort_fields, + EBookSortType *sort_types, + guint n_sort_fields, + gboolean reverse) +{ + GString *string; + const gchar *field_name; + gint i; + + string = g_string_new ("ORDER BY "); + + for (i = 0; i < n_sort_fields; i++) { + + field_name = summary_dbname_from_field (ebsdb, sort_fields[i]); + + if (i > 0) + g_string_append (string, ", "); + + g_string_append_printf (string, "summary.%s_localized %s", field_name, + reverse ? + (sort_types[i] == E_BOOK_SORT_ASCENDING ? "DESC" : "ASC") : + (sort_types[i] == E_BOOK_SORT_ASCENDING ? "ASC" : "DESC")); + } + + return g_string_free (string, FALSE); +} + +static EbSdbCursor * +ebsdb_cursor_new (EBookBackendSqliteDB *ebsdb, + const gchar *folderid, + const gchar *sexp, + gboolean query_with_list_attrs, + EContactField *sort_fields, + EBookSortType *sort_types, + guint n_sort_fields) +{ + EbSdbCursor *cursor = g_slice_new0 (EbSdbCursor); + + cursor->folderid = g_strdup (folderid); + + /* Setup the initial query fragments */ + ebsdb_cursor_setup_query (ebsdb, cursor, sexp, query_with_list_attrs); + + cursor->order = ebsdb_cursor_order_by_fragment (ebsdb, + sort_fields, + sort_types, + n_sort_fields, + FALSE); + cursor->reverse_order = ebsdb_cursor_order_by_fragment (ebsdb, + sort_fields, + sort_types, + n_sort_fields, + TRUE); + + cursor->n_sort_fields = n_sort_fields; + cursor->sort_fields = g_memdup (sort_fields, sizeof (EContactField) * n_sort_fields); + cursor->sort_types = g_memdup (sort_types, sizeof (EBookSortType) * n_sort_fields); + cursor->values = g_new0 (gchar *, n_sort_fields); + + return cursor; +} + +static void +ebsdb_cursor_clear_state (EbSdbCursor *cursor) +{ + gint i; + + for (i = 0; i < cursor->n_sort_fields; i++) { + g_free (cursor->values[i]); + cursor->values[i] = NULL; + } + + g_free (cursor->last_uid); + cursor->last_uid = NULL; +} + +static void +ebsdb_cursor_free (EbSdbCursor *cursor) +{ + if (cursor) { + ebsdb_cursor_clear_state (cursor); + + g_free (cursor->folderid); + g_free (cursor->select_vcards); + g_free (cursor->select_count); + g_free (cursor->query); + g_free (cursor->order); + g_free (cursor->reverse_order); + g_free (cursor->sort_fields); + g_free (cursor->sort_types); + g_free (cursor->values); + + g_slice_free (EbSdbCursor, cursor); + } +} + +static void +ebsdb_cursor_set_state_from_contact (EbSdbCursor *cursor, + EContact *contact) +{ + gint i; + + ebsdb_cursor_clear_state (cursor); + + for (i = 0; i < cursor->n_sort_fields; i++) { + const gchar *string = e_contact_get_const (contact, cursor->sort_fields[i]); + + if (string) + cursor->values[i] = g_utf8_collate_key (string, -1); + else + cursor->values[i] = g_strdup (""); + } + + cursor->last_uid = e_contact_get (contact, E_CONTACT_UID); +} + +static void +ebsdb_cursor_set_state (EbSdbCursor *cursor, + const gchar *vcard) +{ + EContact *contact; + + if (vcard) { + contact = e_contact_new_from_vcard (vcard); + ebsdb_cursor_set_state_from_contact (cursor, contact); + g_object_unref (contact); + } else { + ebsdb_cursor_clear_state (cursor); + } +} + +#define GREATER_OR_LESS(cursor, index, reverse) \ + (reverse ? \ + (((EbSdbCursor *)cursor)->sort_types[index == E_BOOK_SORT_ASCENDING] ? '>' : '<') : \ + (((EbSdbCursor *)cursor)->sort_types[index == E_BOOK_SORT_ASCENDING] ? '<' : '>')) + +static gchar * +ebsdb_cursor_constraints (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gboolean reverse, + gboolean include_current_uid) +{ + GString *string; + const gchar *field_name; + gint i, j; + + /* Example for: + * ORDER BY family_name ASC, given_name DESC + * + * Where current cursor values are: + * family_name = Jackson + * given_name = Micheal + * + * With reverse = FALSE + * + * (summary.family_name > 'Jackson') OR + * (summary.family_name = 'Jackson' AND summary.given_name < 'Micheal') OR + * (summary.family_name = 'Jackson' AND summary.given_name = 'Micheal' AND summary.uid > 'last-uid') + * + * With reverse = TRUE (needed for moving the cursor backwards through results) + * + * (summary.family_name < 'Jackson') OR + * (summary.family_name = 'Jackson' AND summary.given_name > 'Micheal') OR + * (summary.family_name = 'Jackson' AND summary.given_name = 'Micheal' AND summary.uid < 'last-uid') + * + */ + + string = g_string_new (NULL); + + for (i = 0; i < (cursor->n_sort_fields + 1); i++) { + gchar *stmt; + + /* Break once we hit a NULL value */ + if ((i < cursor->n_sort_fields && cursor->values[i] == NULL) || + (i == cursor->n_sort_fields && cursor->last_uid == NULL)) + break; + + /* Between each qualifier, add an 'OR' */ + if (i > 0) + g_string_append (string, " OR "); + + /* Begin qualifier */ + g_string_append_c (string, '('); + + /* Create the '=' statements leading up to the current tie breaker */ + for (j = 0; j < i; j++) { + field_name = summary_dbname_from_field (ebsdb, cursor->sort_fields[j]); + + stmt = sqlite3_mprintf ("summary.%s_localized = %Q", + field_name, cursor->values[j]); + + g_string_append (string, stmt); + g_string_append (string, " AND "); + + sqlite3_free (stmt); - success = insert_contact (ebsdb, contact, folderid, TRUE, default_region, error); + } + + if (i == cursor->n_sort_fields) { + + /* The 'include_current_uid' clause is used for calculating + * the current position of the cursor, inclusive of the + * current position. + */ + if (include_current_uid) + g_string_append_c (string, '('); + + /* Append the UID tie breaker */ + stmt = sqlite3_mprintf ("summary.uid %c %Q", + reverse ? '<' : '>', + cursor->last_uid); + g_string_append (string, stmt); + sqlite3_free (stmt); + + if (include_current_uid) { + stmt = sqlite3_mprintf (" OR summary.uid = %Q", + cursor->last_uid); + g_string_append (string, stmt); + g_string_append_c (string, ')'); + sqlite3_free (stmt); + } - g_object_unref (contact); + } else { + + /* SPECIAL CASE: If we have a parially set cursor state, then we must + * report next results that are inclusive of the final qualifier. + * + * This allows one to set the cursor with the family name set to 'J' + * and include the results for contact's Mr & Miss 'J'. + */ + gboolean include_exact_match = + (reverse == FALSE && + ((i + 1 < cursor->n_sort_fields && cursor->values[i + 1] == NULL) || + (i + 1 == cursor->n_sort_fields && cursor->last_uid == NULL))); + + if (include_exact_match) + g_string_append_c (string, '('); + + /* Append the final qualifier for this field */ + field_name = summary_dbname_from_field (ebsdb, cursor->sort_fields[i]); + + stmt = sqlite3_mprintf ("summary.%s_localized %c %Q", + field_name, + GREATER_OR_LESS (cursor, i, reverse), + cursor->values[i]); + + g_string_append (string, stmt); + sqlite3_free (stmt); + + if (include_exact_match) { + + stmt = sqlite3_mprintf (" OR summary.%s_localized = %Q", + field_name, cursor->values[i]); + + g_string_append (string, stmt); + g_string_append_c (string, ')'); + sqlite3_free (stmt); + } } - if (success) - success = book_backend_sqlitedb_commit_transaction (ebsdb, error); + /* End qualifier */ + g_string_append_c (string, ')'); + } + + return g_string_free (string, FALSE); +} + +static gboolean +cursor_count_total_locked (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint *total, + GError **error) +{ + GString *query; + gboolean success; + + query = g_string_new (cursor->select_count); + + /* Add the filter constraints (if any) */ + if (cursor->query) { + g_string_append (query, " WHERE "); + + g_string_append_c (query, '('); + g_string_append (query, cursor->query); + g_string_append_c (query, ')'); + } + + /* Execute the query */ + success = book_backend_sql_exec (ebsdb->priv->db, query->str, + get_count_cb, total, error); + + g_string_free (query, TRUE); + + return success; +} + +static gboolean +cursor_count_position_locked (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint *position, + GError **error) +{ + GString *query; + gboolean success; + + query = g_string_new (cursor->select_count); + + /* Add the filter constraints (if any) */ + if (cursor->query) { + g_string_append (query, " WHERE "); + + g_string_append_c (query, '('); + g_string_append (query, cursor->query); + g_string_append_c (query, ')'); + } + + /* Add the cursor constraints (if any) */ + if (cursor->values[0] != NULL) { + gchar *constraints = NULL; + + if (!cursor->query) + g_string_append (query, " WHERE "); else - /* The GError is already set. */ - book_backend_sqlitedb_rollback_transaction (ebsdb, NULL); + g_string_append (query, " AND "); + + /* Here we do a reverse query, we're looking for all the + * results leading up to the current cursor value, including + * the cursor value + */ + constraints = ebsdb_cursor_constraints (ebsdb, cursor, TRUE, TRUE); + + g_string_append_c (query, '('); + g_string_append (query, constraints); + g_string_append_c (query, ')'); + + g_free (constraints); } - g_slist_free_full (vcard_data, destroy_search_data); - g_free (default_region); + /* Execute the query */ + success = book_backend_sql_exec (ebsdb->priv->db, query->str, + get_count_cb, position, error); + + g_string_free (query, TRUE); + + return success; +} + +/** + * e_book_backend_sqlitedb_cursor_new: + * @ebsdb: An #EBookBackendSqliteDB + * @folderid: folder id of the address-book + * @sexp: search expression; use NULL or an empty string to get all stored contacts. + * @sort_fields: (array length=n_sort_fields): An array of #EContactFields as sort keys in order of priority + * @sort_types: (array length=n_sort_fields): An array of #EBookSortTypes, one for each field in @sort_fields + * @n_sort_fields: The number of fields to sort results by. + * @error: A return location to story any error that might be reported. + * + * Creates a new #EbSdbCursor. + * + * The cursor should be freed with e_book_backend_sqlitedb_cursor_free(). + * + * Returns: (transfer full): A newly created #EbSdbCursor + * + * Since: 3.10 + */ +EbSdbCursor * +e_book_backend_sqlitedb_cursor_new (EBookBackendSqliteDB *ebsdb, + const gchar *folderid, + const gchar *sexp, + EContactField *sort_fields, + EBookSortType *sort_types, + guint n_sort_fields, + GError **error) +{ + gboolean query_with_list_attrs = FALSE; + gint i; + + g_return_val_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb), NULL); + g_return_val_if_fail (folderid && folderid[0], NULL); + + /* We only support cursors for summary fields in the query */ + if (sexp && !e_book_backend_sqlitedb_check_summary_query (ebsdb, sexp, &query_with_list_attrs)) { + g_set_error (error, E_BOOK_SDB_ERROR, E_BOOK_SDB_ERROR_INVALID_QUERY, + _("Only summary queries are supported by EbSdbCursor")); + return NULL; + } + + if (n_sort_fields == 0) { + g_set_error (error, E_BOOK_SDB_ERROR, E_BOOK_SDB_ERROR_INVALID_QUERY, + _("At least one sort field must be specified to use an EbSdbCursor")); + return NULL; + } + + /* We only support summarized sort keys which are not multi value fields */ + for (i = 0; i < n_sort_fields; i++) { + + gint support; + + support = func_check_field_test (ebsdb, e_contact_field_name (sort_fields[i]), NULL); + + if ((support & CHECK_IS_SUMMARY) == 0) { + g_set_error (error, E_BOOK_SDB_ERROR, E_BOOK_SDB_ERROR_INVALID_QUERY, + _("Cannot sort by a field that is not in the summary")); + return NULL; + } + + if ((support & CHECK_IS_LIST_ATTR) != 0) { + g_set_error (error, E_BOOK_SDB_ERROR, E_BOOK_SDB_ERROR_INVALID_QUERY, + _("Cannot sort by a field which may have multiple values")); + return NULL; + } + } + + return ebsdb_cursor_new (ebsdb, folderid, sexp, query_with_list_attrs, + sort_fields, sort_types, n_sort_fields); +} + +/** + * e_book_backend_sqlitedb_cursor_free: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor to free + * + * Frees @cursor. + * + * Since: 3.10 + */ +void +e_book_backend_sqlitedb_cursor_free (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor) +{ + g_return_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb)); + + ebsdb_cursor_free (cursor); +} + +/** + * e_book_backend_sqlitedb_cursor_move_by: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor to use + * @count: A positive or negative amount of contacts to try and fetch + * @error: A return location to story any error that might be reported. + * + * Moves @cursor through the @ebsdb by @count and fetch a maximum of @count contacts. + * + * If @count is negative, then the cursor will move backwards. + * + * If @cursor is in an empty state, then @count contacts will be fetched + * from the beginning of the cursor's query results, or from the ending + * of the query results for a negative value of @count. + * + * If @cursor reaches the beginning or end of the query results, then the + * returned list might not contain the amount of desired contacts, or might + * return no results. This is not considered an error condition. + * + * Returns: (element-type EbSdbSearchData) (transfer full): + * A list of #EbSdbSearchData, the list should be freed with g_slist_free() + * and all elements freed with e_book_backend_sqlitedb_search_data_free(). + * + * Since: 3.10 + */ +GSList * +e_book_backend_sqlitedb_cursor_move_by (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint count, + GError **error) +{ + GSList *results = NULL; + GString *query; + gboolean success; + + g_return_val_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb), NULL); + g_return_val_if_fail (cursor != NULL, NULL); + g_return_val_if_fail (count != 0, NULL); + + query = g_string_new (cursor->select_vcards); + + /* Add the filter constraints (if any) */ + if (cursor->query) { + g_string_append (query, " WHERE "); + + g_string_append_c (query, '('); + g_string_append (query, cursor->query); + g_string_append_c (query, ')'); + } + + /* Add the cursor constraints (if any) */ + if (cursor->values[0] != NULL) { + gchar *constraints = NULL; + + if (!cursor->query) + g_string_append (query, " WHERE "); + else + g_string_append (query, " AND "); + + constraints = ebsdb_cursor_constraints (ebsdb, cursor, count < 0, FALSE); + + g_string_append_c (query, '('); + g_string_append (query, constraints); + g_string_append_c (query, ')'); + + g_free (constraints); + } + + /* Add the sort order */ + g_string_append_c (query, ' '); + if (count > 0) + g_string_append (query, cursor->order); + else + g_string_append (query, cursor->reverse_order); + + /* Add the limit */ + g_string_append_printf (query, " LIMIT %d", ABS (count)); + + /* Execute the query */ + LOCK_MUTEX (&ebsdb->priv->lock); + success = book_backend_sql_exec (ebsdb->priv->db, query->str, + addto_vcard_list_cb , &results, + error); + UNLOCK_MUTEX (&ebsdb->priv->lock); + + g_string_free (query, TRUE); + + /* Correct the order of results, since + * addto_vcard_list_cb() prepends them (as it should) + */ + results = g_slist_reverse (results); + + /* If there was no error, update the internal cursor state */ + if (success) { + + if (g_slist_length (results) < ABS (count)) + /* We've reached the end, clear the state */ + ebsdb_cursor_clear_state (cursor); + else { + /* Set the cursor state to the last result */ + GSList *last = g_slist_last (results); + EbSdbSearchData *data = last->data; + + ebsdb_cursor_set_state (cursor, data->vcard); + } + } + + return results; +} + +/** + * e_book_backend_sqlitedb_cursor_set_targetv: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor to modify + * @values: (allow-none) (array length=n_values): An array of values to set the cursor position with + * @n_values: The length of the passed @values + * + * Set's the current cursor target position. + * + * The passed values set the relative @cursor position in it's result set + * with the passed @values. Each member of the passed @values represents + * a target position for it's corresponding sort field which the cursor + * was created for (See the @sort_fields argument of e_book_backend_sqlitedb_cursor_new()). + * + * The @values array passed to this function need not be as long as + * the array of @sort_fields originally passed to e_book_backend_sqlitedb_cursor_new(). + * A shorter array of @values indicates that the cursor's target is set with + * less specificity. + * + * In addition to the @cursor's @sort_fields, a single extra value can + * be passed which is an %E_CONTACT_UID. If the uid is given as an additional + * value in @values, it will be used to specify exactly which contact the + * cursor should currently point to. + * + * Note that if the final %E_CONTACT_UID is not specified, then the cursor + * is said to be in an 'incomplete state' or a 'partial state'. If the cursor + * is in a partial state, then the next call to e_book_backend_sqlitedb_cursor_move_by() + * with a positive @count will include any exact matches for the given values. + * + * A %NULL value for @values resets the internal state of @cursor completely, + * so that any further calls to e_book_backend_sqlitedb_cursor_move_by() will + * report results from the beginning or ending of the @cursor's query. + * + * Since: 3.10 + */ +void +e_book_backend_sqlitedb_cursor_set_targetv (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + const gchar **values, + gint n_values) +{ + gint i; + + g_return_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb)); + g_return_if_fail (cursor != NULL); + g_return_if_fail (n_values <= cursor->n_sort_fields + 1); + + ebsdb_cursor_clear_state (cursor); + + for (i = 0; i < MIN (cursor->n_sort_fields, n_values); i++) { + cursor->values[i] = g_utf8_collate_key (values[i], -1); + } + + if (n_values > cursor->n_sort_fields) + cursor->last_uid = g_strdup (values[n_values - 1]); +} + +/** + * e_book_backend_sqlitedb_cursor_set_target: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor to modify + * @...: A null terminated list of values + * + * A convenience function for calling e_book_backend_sqlitedb_cursor_set_targetv(). + * + * Since: 3.10 + */ +void +e_book_backend_sqlitedb_cursor_set_target (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + ...) +{ + GArray *array; + gchar *value = NULL; + va_list args; + + g_return_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb)); + g_return_if_fail (cursor != NULL); + + array = g_array_new (FALSE, FALSE, sizeof (gchar *)); + + va_start (args, cursor); + value = va_arg (args, gchar*); + while (value) { + g_array_append_val (array, value); + value = va_arg (args, gchar*); + } + va_end (args); + + e_book_backend_sqlitedb_cursor_set_targetv (ebsdb, cursor, + (const gchar **)array->data, + array->len); + g_array_free (array, TRUE); +} + +/** + * e_book_backend_sqlitedb_cursor_set_target_contact: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor to modify + * @contact: (allow-none): An #EContact + * + * A convenience function for calling e_book_backend_sqlitedb_cursor_set_targetv(), + * + * This function will set the cursor values automatically from @contact. + * + * Since: 3.10 + */ +void +e_book_backend_sqlitedb_cursor_set_target_contact (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + EContact *contact) +{ + g_return_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb)); + g_return_if_fail (contact == NULL || E_IS_CONTACT (contact)); + g_return_if_fail (cursor != NULL); + + if (contact) + ebsdb_cursor_set_state_from_contact (cursor, contact); + else + ebsdb_cursor_clear_state (cursor); +} + +/** + * e_book_backend_sqlitedb_cursor_set_sexp: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor + * @sexp: The new query expression for @cursor + * @error: A return location to story any error that might be reported. + * + * Modifies the current query expression for @cursor. This will not + * modify @cursor's state, but will change the outcome of any further + * calls to e_book_backend_sqlitedb_cursor_calculate() or + * e_book_backend_sqlitedb_cursor_move_by(). + * + * Returns: %TRUE if the expression was valid and accepted by @ebsdb + * + * Since: 3.10 + */ +gboolean +e_book_backend_sqlitedb_cursor_set_sexp (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + const gchar *sexp, + GError **error) +{ + gboolean query_with_list_attrs = FALSE; + + g_return_val_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb), FALSE); + g_return_val_if_fail (cursor != NULL, FALSE); + + /* We only support cursors for summary fields in the query */ + if (sexp && !e_book_backend_sqlitedb_check_summary_query (ebsdb, sexp, &query_with_list_attrs)) { + g_set_error (error, E_BOOK_SDB_ERROR, E_BOOK_SDB_ERROR_INVALID_QUERY, + _("Only summary queries are supported by EbSdbCursor")); + return FALSE; + } + + ebsdb_cursor_setup_query (ebsdb, cursor, sexp, query_with_list_attrs); + + return TRUE; +} + +/** + * e_book_backend_sqlitedb_cursor_calculate: + * @ebsdb: An #EBookBackendSqliteDB + * @cursor: The #EbSdbCursor + * @total: (out) (allow-none): A return location to store the total result set for this cursor + * @position: (out) (allow-none): A return location to store the total results before the cursor value + * @error: (allow-none): A return location to story any error that might be reported. + * + * Calculates the @total amount of results for the @cursor's query expression, + * as well as the current @position of @cursor in the results. @position is + * represented as the amount of results which lead up to the current value + * of @cursor, if @cursor currently points to an exact contact, the position + * also includes the cursor contact. + * + * Returns: Whether @total and @position were successfully calculated. + * + * Since: 3.10 + */ +gboolean +e_book_backend_sqlitedb_cursor_calculate (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint *total, + gint *position, + GError **error) +{ + gboolean success = TRUE; + + g_return_val_if_fail (E_IS_BOOK_BACKEND_SQLITEDB (ebsdb), FALSE); + g_return_val_if_fail (cursor != NULL, FALSE); + + /* If we're in a clear cursor state, then the position is 0 */ + if (position && cursor->values[0] == NULL) { + *position = 0; + + /* Mark the local pointer NULL, no need to calculate this anymore */ + position = NULL; + } + + /* Early return if there is nothing to do */ + if (!total && !position) + return TRUE; + + LOCK_MUTEX (&ebsdb->priv->lock); + + if (!book_backend_sqlitedb_start_transaction (ebsdb, error)) { + UNLOCK_MUTEX (&ebsdb->priv->lock); + return FALSE; + } + + if (total) + success = cursor_count_total_locked (ebsdb, cursor, total, error); + + if (success && position) + success = cursor_count_position_locked (ebsdb, cursor, position, error); + + if (success) + success = book_backend_sqlitedb_commit_transaction (ebsdb, error); + else + /* The GError is already set. */ + book_backend_sqlitedb_rollback_transaction (ebsdb, NULL); + + UNLOCK_MUTEX (&ebsdb->priv->lock); return success; } diff --git a/addressbook/libedata-book/e-book-backend-sqlitedb.h b/addressbook/libedata-book/e-book-backend-sqlitedb.h index f6b3433..571aac0 100644 --- a/addressbook/libedata-book/e-book-backend-sqlitedb.h +++ b/addressbook/libedata-book/e-book-backend-sqlitedb.h @@ -112,6 +112,15 @@ typedef struct { gchar *bdata; } EbSdbSearchData; +/** + * EbSdbCuror: + * + * An opaque cursor pointer + * + * Since: 3.10 + */ +typedef struct _EbSdbCursor EbSdbCursor; + GType e_book_backend_sqlitedb_get_type (void) G_GNUC_CONST; GQuark e_book_backend_sqlitedb_error_quark @@ -277,6 +286,48 @@ gboolean e_book_backend_sqlitedb_check_summary_fields (EBookBackendSqliteDB *ebsdb, GHashTable *fields_of_interest); +/* Cursor API */ +EbSdbCursor *e_book_backend_sqlitedb_cursor_new + (EBookBackendSqliteDB *ebsdb, + const gchar *folderid, + const gchar *sexp, + EContactField *sort_fields, + EBookSortType *sort_types, + guint n_sort_fields, + GError **error); +void e_book_backend_sqlitedb_cursor_free + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor); +GSList *e_book_backend_sqlitedb_cursor_move_by + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint count, + GError **error); +void e_book_backend_sqlitedb_cursor_set_targetv + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + const gchar **values, + gint n_values); +void e_book_backend_sqlitedb_cursor_set_target + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + ...) G_GNUC_NULL_TERMINATED; +void e_book_backend_sqlitedb_cursor_set_target_contact + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + EContact *contact); +gboolean e_book_backend_sqlitedb_cursor_set_sexp + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + const gchar *sexp, + GError **error); +gboolean e_book_backend_sqlitedb_cursor_calculate + (EBookBackendSqliteDB *ebsdb, + EbSdbCursor *cursor, + gint *total, + gint *position, + GError **error); + #ifndef EDS_DISABLE_DEPRECATED gboolean e_book_backend_sqlitedb_is_summary_query (const gchar *query); -- 2.7.4