[libgda] Added SQLite collations and locale related functions
- From: Vivien Malerba <vivien src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [libgda] Added SQLite collations and locale related functions
- Date: Fri, 27 May 2011 19:41:40 +0000 (UTC)
commit cf15512764a2cd83b8e43f0180e9f42f1c5bf3e4
Author: Vivien Malerba <malerba gnome-db org>
Date: Fri May 27 18:55:43 2011 +0200
Added SQLite collations and locale related functions
doc/C/libgda-5.0-docs.sgml | 4 +-
doc/C/prov-notes.xml | 86 +++++++++-
libgda/sqlite/gda-sqlite-provider.c | 207 +++++++++++++++++++++-
libgda/sqlite/virtual/gda-vprovider-data-model.c | 4 +-
providers/sqlite/sqlite_specs_dsn.xml.in | 7 +-
tools/browser/auth-dialog.c | 2 +-
tools/gda-sql.c | 2 +-
7 files changed, 295 insertions(+), 17 deletions(-)
---
diff --git a/doc/C/libgda-5.0-docs.sgml b/doc/C/libgda-5.0-docs.sgml
index e52feab..77c56b1 100644
--- a/doc/C/libgda-5.0-docs.sgml
+++ b/doc/C/libgda-5.0-docs.sgml
@@ -1226,7 +1226,7 @@ Provider | Description | DSN parameters | File
------------+-----------------------------------+--------------------+-----------------------------------------------
SQLite | Provider for SQLite databases | DB_NAME, | /usr/lib/libgda-5.0/providers/libgda-sqlite.so
DB_DIR,
- LOAD_GDA_FUNCTIONS
+ EXTRA_FUNCTIONS
Berkeley-DB | Provider for Berkeley databases | FILE, | /usr/lib/libgda-5.0/providers/libgda-bdb.so
DATABASE
[...]
@@ -1340,7 +1340,7 @@ Location: /usr/lib/libgda-5.0/providers/libgda-sqlite.so
Data source's parameters (Name / Type / Description):
DB_NAME / gchararray / Database name
DB_DIR / gchararray / Directory
- LOAD_GDA_FUNCTIONS / gboolean / Extra functions
+ EXTRA_FUNCTIONS / gboolean / Extra functions
Provider: Berkeley-DB
Description: Provider for Berkeley databases
diff --git a/doc/C/prov-notes.xml b/doc/C/prov-notes.xml
index e4afc67..c64ce64 100644
--- a/doc/C/prov-notes.xml
+++ b/doc/C/prov-notes.xml
@@ -79,15 +79,28 @@
<entry>No</entry>
</row>
<row>
- <entry>LOAD_GDA_FUNCTIONS</entry>
- <entry>If set to TRUE, then some extra functions defined by &LIBGDA; are added. The functions are
- for &LIBGDA;'s own internal use and of little interrest otherwise.
+ <entry>EXTRA_FUNCTIONS</entry>
+ <entry>If set to TRUE (or unspecified), then some extra functions defined by &LIBGDA; are added. The functions are:
+ <function>gda_file_exists()</function>, <function>gda_hex_print()</function>,
+ <function>gda_hex()</function>, <function>gda_rmdiacr()</function>,
+ <function>gda_lower()</function> and <function>gda_upper()</function>; see below
+ for more information about them.
+ </entry>
+ <entry>No</entry>
+ </row>
+ <row>
+ <entry>EXTRA_COLLATIONS</entry>
+ <entry>If set to TRUE (or unspecified), then some extra collations defined by &LIBGDA; are added.
+ They are:
+ <function>LOCALE</function> (the strings are compared taking into account UTF8 sorting and the
+ current locale) and <function>DCASE</function> (before comparison, all the diacritical
+ signs (for example accents) are removed from the strings and they are converted to lower case).
</entry>
<entry>No</entry>
</row>
<row>
<entry>REGEXP</entry>
- <entry>If set to TRUE, then the <function>regexp()</function> and <function>regexp_match()</function>
+ <entry>If set to TRUE (or unspecified), then the <function>regexp()</function> and <function>regexp_match()</function>
functions are defined. The <function>regexp()</function> function is used by SQL statement with a
construct as "x REGEXP y", and the <function>regexp_match()</function> is more general. The default for
this option is TRUE. See below for more information about this function.
@@ -98,6 +111,71 @@
</tgroup>
</table>
</para>
+
+ <sect2>
+ <title>The <function>gda_file_exists()</function> function</title>
+ <para>
+ This function accepts a filename as argument, and returns 0 if the file with that filename does not
+ exist, or 1 if it does.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>The <function>gda_hex_print()</function> function</title>
+ <para>
+ This function accepts at most 2 arguments, in that order:
+ <itemizedlist>
+ <listitem><para>a blob value</para></listitem>
+ <listitem><para>a length (not mandatory)</para></listitem>
+ </itemizedlist>
+ </para>
+ <para>
+ It returns a string suitable to be printed (where all the non ascii characters are converted to
+ the "\xyz" syntax where "xyz" is the decimal value of the character), limited to the specified
+ length if any.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>The <function>gda_hex()</function> function</title>
+ <para>
+ This function accepts at most 2 arguments, in that order:
+ <itemizedlist>
+ <listitem><para>a blob value</para></listitem>
+ <listitem><para>a length (not mandatory)</para></listitem>
+ </itemizedlist>
+ </para>
+ <para>
+ It returns a hex dump string of the blob value, limited to the specified length if any.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>The <function>gda_rmdiacr()</function> function</title>
+ <para>
+ This function accepts at most 2 arguments, in that order:
+ <itemizedlist>
+ <listitem><para>a string value</para></listitem>
+ <listitem><para>a case conversion to do (not mandatory), as a string which must be
+ 'upper' or 'lower'</para></listitem>
+ </itemizedlist>
+ </para>
+ <para>
+ It returns a string where all the diacritical signs (for example accents) from the input string,
+ and optionally converts the string to upper or lower case if specified. This function takes into
+ account the current locale and is usefull to do some text search.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>The <function>gda_upper()</function> and <function>gda_lower()</function> functions</title>
+ <para>
+ These function accept one string argument and convert it to upper or lower case, taking into account
+ the locale (the standard SQLite <function>upper()</function> and <function>lower()</function>
+ functions only operating on ASCII characters).
+ </para>
+ </sect2>
+
<sect2>
<title>The <function>regexp_match()</function> function</title>
<para>
diff --git a/libgda/sqlite/gda-sqlite-provider.c b/libgda/sqlite/gda-sqlite-provider.c
index 0faec1d..b766e82 100644
--- a/libgda/sqlite/gda-sqlite-provider.c
+++ b/libgda/sqlite/gda-sqlite-provider.c
@@ -327,13 +327,16 @@ static gchar *gda_sqlite_provider_unescape_string (GdaServerProvid
static void gda_sqlite_free_cnc_data (SqliteConnectionData *cdata);
/*
- * extending SQLite with our own functions
+ * extending SQLite with our own functions and collations
*/
static void scalar_gda_file_exists_func (sqlite3_context *context, int argc, sqlite3_value **argv);
static void scalar_gda_hex_print_func (sqlite3_context *context, int argc, sqlite3_value **argv);
static void scalar_gda_hex_print_func2 (sqlite3_context *context, int argc, sqlite3_value **argv);
static void scalar_gda_hex_func (sqlite3_context *context, int argc, sqlite3_value **argv);
static void scalar_gda_hex_func2 (sqlite3_context *context, int argc, sqlite3_value **argv);
+static void scalar_rmdiacr (sqlite3_context *context, int argc, sqlite3_value **argv);
+static void scalar_lower (sqlite3_context *context, int argc, sqlite3_value **argv);
+static void scalar_upper (sqlite3_context *context, int argc, sqlite3_value **argv);
typedef struct {
char *name;
int nargs;
@@ -345,7 +348,11 @@ static ScalarFunction scalars[] = {
{"gda_hex_print", 1, NULL, scalar_gda_hex_print_func},
{"gda_hex_print", 2, NULL, scalar_gda_hex_print_func2},
{"gda_hex", 1, NULL, scalar_gda_hex_func},
- {"gda_hex", 2, NULL, scalar_gda_hex_func2}
+ {"gda_hex", 2, NULL, scalar_gda_hex_func2},
+ {"gda_rmdiacr", 1, NULL, scalar_rmdiacr},
+ {"gda_rmdiacr", 2, NULL, scalar_rmdiacr},
+ {"gda_lower", 1, NULL, scalar_lower},
+ {"gda_upper", 1, NULL, scalar_upper},
};
/* Regexp handling */
@@ -359,6 +366,20 @@ static ScalarFunction regexp_functions[] = {
{"regexp_match", 3, NULL, scalar_regexp_match_func}
};
+/* Collations */
+static int locale_collate_func (G_GNUC_UNUSED void *pArg, int nKey1, const void *pKey1,
+ int nKey2, const void *pKey2);
+static int dcase_collate_func (G_GNUC_UNUSED void *pArg, int nKey1, const void *pKey1,
+ int nKey2, const void *pKey2);
+typedef struct {
+ char *name;
+ int (*xFunc)(void *, int, const void *, int, const void *);
+} CollationFunction;
+static CollationFunction collation_functions[] = {
+ {"LOCALE", locale_collate_func},
+ {"DCASE", dcase_collate_func}
+};
+
/*
* Prepared internal statements
*/
@@ -632,6 +653,55 @@ gda_sqlite_provider_get_version (G_GNUC_UNUSED GdaServerProvider *provider)
return PACKAGE_VERSION;
}
+typedef enum {
+ CASE_UP,
+ CASE_DOWN,
+ CASE_UNCHANGED
+} CaseModif;
+
+gchar *
+remove_diacritics_and_change_case (const gchar *str, gssize len, CaseModif cmod)
+{
+ gchar *retval = NULL;
+
+ if (str) {
+ GString* string;
+ gchar *normstr, *ptr;
+
+ normstr = g_utf8_normalize (str, len, G_NORMALIZE_NFD);
+ string = g_string_new ("");
+ ptr = normstr;
+ while (ptr) {
+ gunichar c;
+ c = g_utf8_get_char (ptr);
+ if (c != '\0') {
+ if (!g_unichar_ismark(c)) {
+ switch (cmod) {
+ case CASE_UP:
+ c = g_unichar_toupper (c);
+ break;
+ case CASE_DOWN:
+ c = g_unichar_tolower (c);
+ break;
+ case CASE_UNCHANGED:
+ break;
+ }
+ g_string_append_unichar (string, c);
+ }
+ ptr = g_utf8_next_char (ptr);
+ }
+ else
+ break;
+ }
+
+ retval = g_string_free (string, FALSE);
+ g_free (normstr);
+ }
+
+ return retval;
+}
+
+
/*
* Open connection request
*/
@@ -643,7 +713,7 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
gchar *filename = NULL;
const gchar *dirname = NULL, *dbname = NULL;
const gchar *is_virtual = NULL;
- const gchar *use_extra_functions = NULL, *with_fk = NULL, *regexp;
+ const gchar *use_extra_functions = NULL, *with_fk = NULL, *regexp, *locale_collate;
gint errmsg;
SqliteConnectionData *cdata;
gchar *dup = NULL;
@@ -666,8 +736,12 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
dbname = gda_quark_list_find (params, "DB_NAME");
is_virtual = gda_quark_list_find (params, "_IS_VIRTUAL");
with_fk = gda_quark_list_find (params, "FK");
- use_extra_functions = gda_quark_list_find (params, "LOAD_GDA_FUNCTIONS");
+ use_extra_functions = gda_quark_list_find (params, "EXTRA_FUNCTIONS");
+ if (!use_extra_functions)
+ use_extra_functions = gda_quark_list_find (params, "LOAD_GDA_FUNCTIONS");
+
regexp = gda_quark_list_find (params, "REGEXP");
+ locale_collate = gda_quark_list_find (params, "EXTRA_COLLATIONS");
if (auth)
passphrase = gda_quark_list_find (auth, "PASSWORD");
@@ -879,7 +953,7 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
#endif
}
- if (use_extra_functions && ((*use_extra_functions == 't') || (*use_extra_functions == 'T'))) {
+ if (!use_extra_functions || ((*use_extra_functions == 't') || (*use_extra_functions == 'T'))) {
gsize i;
for (i = 0; i < sizeof (scalars) / sizeof (ScalarFunction); i++) {
@@ -889,6 +963,8 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
SQLITE_UTF8, func->user_data,
func->xFunc, NULL, NULL);
if (res != SQLITE_OK) {
+ gda_connection_add_event_string (cnc, _("Could not register function '%s'"),
+ func->name);
gda_sqlite_free_cnc_data (cdata);
gda_connection_internal_set_provider_data (cnc, NULL, (GDestroyNotify) gda_sqlite_free_cnc_data);
g_static_rec_mutex_unlock (&cnc_mutex);
@@ -907,6 +983,8 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
SQLITE_UTF8, func->user_data,
func->xFunc, NULL, NULL);
if (res != SQLITE_OK) {
+ gda_connection_add_event_string (cnc, _("Could not register function '%s'"),
+ func->name);
gda_sqlite_free_cnc_data (cdata);
gda_connection_internal_set_provider_data (cnc, NULL, (GDestroyNotify) gda_sqlite_free_cnc_data);
g_static_rec_mutex_unlock (&cnc_mutex);
@@ -915,6 +993,25 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
}
}
+ if (! locale_collate || ((*locale_collate == 't') || (*locale_collate == 'T'))) {
+ gsize i;
+ for (i = 0; i < sizeof (collation_functions) / sizeof (CollationFunction); i++) {
+ CollationFunction *func = (CollationFunction*) &(collation_functions [i]);
+ gint res;
+ res = SQLITE3_CALL (sqlite3_create_collation) (cdata->connection, func->name,
+ SQLITE_UTF8, NULL, func->xFunc);
+ if (res != SQLITE_OK) {
+ gda_connection_add_event_string (cnc,
+ _("Could not define the %s collation"),
+ func->name);
+ gda_sqlite_free_cnc_data (cdata);
+ gda_connection_internal_set_provider_data (cnc, NULL, (GDestroyNotify) gda_sqlite_free_cnc_data);
+ g_static_rec_mutex_unlock (&cnc_mutex);
+ return FALSE;
+ }
+ }
+ }
+
if (SQLITE3_CALL (sqlite3_threadsafe) ())
g_object_set (G_OBJECT (cnc), "thread-owner", NULL, NULL);
else
@@ -924,6 +1021,36 @@ gda_sqlite_provider_open_connection (GdaServerProvider *provider, GdaConnection
return TRUE;
}
+static int
+locale_collate_func (G_GNUC_UNUSED void *pArg,
+ int nKey1, const void *pKey1,
+ int nKey2, const void *pKey2)
+{
+ gchar *tmp1, *tmp2;
+ int res;
+ tmp1 = g_utf8_collate_key ((gchar*) pKey1, nKey1);
+ tmp2 = g_utf8_collate_key ((gchar*) pKey2, nKey2);
+ res = strcmp (tmp1, tmp2);
+ g_free (tmp1);
+ g_free (tmp2);
+ return res;
+}
+
+static int
+dcase_collate_func (G_GNUC_UNUSED void *pArg,
+ int nKey1, const void *pKey1,
+ int nKey2, const void *pKey2)
+{
+ gchar *tmp1, *tmp2;
+ int res;
+ tmp1 = remove_diacritics_and_change_case ((gchar*) pKey1, nKey1, CASE_DOWN);
+ tmp2 = remove_diacritics_and_change_case ((gchar*) pKey2, nKey2, CASE_DOWN);
+ res = strcmp (tmp1, tmp2);
+ g_free (tmp1);
+ g_free (tmp2);
+ return res;
+}
+
/*
* Close connection request
*/
@@ -3191,7 +3318,75 @@ scalar_gda_hex_print_func2 (sqlite3_context *context, int argc, sqlite3_value **
size = SQLITE3_CALL (sqlite3_value_int) (argv [1]);
- SQLITE3_CALL (sqlite3_result_text) (context, str, -1, g_free);
+ SQLITE3_CALL (sqlite3_result_text) (context, str, size, g_free);
+}
+
+static void
+scalar_rmdiacr (sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ gchar *data, *tmp;
+ CaseModif ncase = CASE_UNCHANGED;
+
+ if (argc == 2) {
+ data = (gchar*) SQLITE3_CALL (sqlite3_value_text) (argv [1]);
+ if ((*data == 'u') || (*data == 'U'))
+ ncase = CASE_UP;
+ else if ((*data == 'l') || (*data == 'l'))
+ ncase = CASE_DOWN;
+ }
+ else if (argc != 1) {
+ SQLITE3_CALL (sqlite3_result_error) (context, _("Function requires one or two arguments"), -1);
+ return;
+ }
+
+ data = (gchar*) SQLITE3_CALL (sqlite3_value_text) (argv [0]);
+ if (!data) {
+ SQLITE3_CALL (sqlite3_result_null) (context);
+ return;
+ }
+
+ tmp = remove_diacritics_and_change_case (data, -1, ncase);
+ SQLITE3_CALL (sqlite3_result_text) (context, tmp, -1, g_free);
+}
+
+static void
+scalar_lower (sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ gchar *data, *tmp;
+
+ if (argc != 1) {
+ SQLITE3_CALL (sqlite3_result_error) (context, _("Function requires one arguments"), -1);
+ return;
+ }
+
+ data = (gchar*) SQLITE3_CALL (sqlite3_value_text) (argv [0]);
+ if (!data) {
+ SQLITE3_CALL (sqlite3_result_null) (context);
+ return;
+ }
+
+ tmp = g_utf8_strdown (data, -1);
+ SQLITE3_CALL (sqlite3_result_text) (context, tmp, -1, g_free);
+}
+
+static void
+scalar_upper (sqlite3_context *context, int argc, sqlite3_value **argv)
+{
+ gchar *data, *tmp;
+
+ if (argc != 1) {
+ SQLITE3_CALL (sqlite3_result_error) (context, _("Function requires one arguments"), -1);
+ return;
+ }
+
+ data = (gchar*) SQLITE3_CALL (sqlite3_value_text) (argv [0]);
+ if (!data) {
+ SQLITE3_CALL (sqlite3_result_null) (context);
+ return;
+ }
+
+ tmp = g_utf8_strup (data, -1);
+ SQLITE3_CALL (sqlite3_result_text) (context, tmp, -1, g_free);
}
static void
diff --git a/libgda/sqlite/virtual/gda-vprovider-data-model.c b/libgda/sqlite/virtual/gda-vprovider-data-model.c
index bf5b330..de5ce7a 100644
--- a/libgda/sqlite/virtual/gda-vprovider-data-model.c
+++ b/libgda/sqlite/virtual/gda-vprovider-data-model.c
@@ -273,10 +273,10 @@ gda_vprovider_data_model_open_connection (GdaServerProvider *provider, GdaConnec
if (params) {
m_params = gda_quark_list_copy (params);
- gda_quark_list_add_from_string (m_params, "_IS_VIRTUAL=TRUE;LOAD_GDA_FUNCTIONS=TRUE", TRUE);
+ gda_quark_list_add_from_string (m_params, "_IS_VIRTUAL=TRUE;EXTRA_FUNCTIONS=TRUE", TRUE);
}
else
- m_params = gda_quark_list_new_from_string ("_IS_VIRTUAL=TRUE;LOAD_GDA_FUNCTIONS=TRUE");
+ m_params = gda_quark_list_new_from_string ("_IS_VIRTUAL=TRUE;EXTRA_FUNCTIONS=TRUE");
if (! GDA_SERVER_PROVIDER_CLASS (parent_class)->open_connection (GDA_SERVER_PROVIDER (provider), cnc, m_params,
auth, NULL, NULL, NULL)) {
diff --git a/providers/sqlite/sqlite_specs_dsn.xml.in b/providers/sqlite/sqlite_specs_dsn.xml.in
index 201a379..2f954d8 100644
--- a/providers/sqlite/sqlite_specs_dsn.xml.in
+++ b/providers/sqlite/sqlite_specs_dsn.xml.in
@@ -3,10 +3,15 @@
<parameters>
<parameter id="DB_NAME" _name="Database name" _descr="The name of a database to use (without the .db)" gdatype="gchararray" nullok="FALSE"/>
<parameter id="DB_DIR" _name="Directory" _descr="Directory where the database file is stored" gdatype="gchararray" nullok="FALSE" plugin="filesel:MODE=PICKFOLDER"/>
- <parameter id="LOAD_GDA_FUNCTIONS" _name="Extra functions" _descr="Enable usage of some extra functions in SQL" gdatype="gboolean" nullok="TRUE"/>
+ <parameter id="EXTRA_FUNCTIONS" _name="Extra functions" _descr="Enable usage of extra functions (gda_upper, ...)" gdatype="gboolean" nullok="TRUE">
+ <gda_value>TRUE</gda_value>
+ </parameter>
<parameter id="REGEXP" _name="Define REGEXP" _descr="Define the REGEXP function" gdatype="gboolean" nullok="TRUE">
<gda_value>TRUE</gda_value>
</parameter>
+ <parameter id="EXTRA_COLLATIONS" _name="Localized comparisons" _descr="Enable usage of extra collation methods (LOCALE and DCASE)" gdatype="gboolean" nullok="TRUE">
+ <gda_value>TRUE</gda_value>
+ </parameter>
<parameter id="FK" _name="With foreign keys" _descr="Enforce foreign keys" gdatype="gboolean" nullok="TRUE"/>
</parameters>
</data-set-spec>
diff --git a/tools/browser/auth-dialog.c b/tools/browser/auth-dialog.c
index 8231624..007ca9a 100644
--- a/tools/browser/auth-dialog.c
+++ b/tools/browser/auth-dialog.c
@@ -404,7 +404,7 @@ auth_dialog_add_cnc_string (AuthDialog *dialog, const gchar *cnc_string, GError
e2 = gda_rfc1738_encode (file);
g_free (path);
g_free (file);
- real_cnc_string = g_strdup_printf ("%s://DB_DIR=%s;LOAD_GDA_FUNCTIONS=TRUE;DB_NAME=%s", pname, e1, e2);
+ real_cnc_string = g_strdup_printf ("%s://DB_DIR=%s;EXTRA_FUNCTIONS=TRUE;DB_NAME=%s", pname, e1, e2);
g_free (e1);
g_free (e2);
gda_connection_string_split (real_cnc_string, &real_cnc, &real_provider, &user, &pass);
diff --git a/tools/gda-sql.c b/tools/gda-sql.c
index 2f83424..10be1c1 100644
--- a/tools/gda-sql.c
+++ b/tools/gda-sql.c
@@ -1367,7 +1367,7 @@ open_connection (SqlConsole *console, const gchar *cnc_name, const gchar *cnc_st
e2 = gda_rfc1738_encode (file);
g_free (path);
g_free (file);
- real_cnc_string = g_strdup_printf ("%s://DB_DIR=%s;LOAD_GDA_FUNCTIONS=TRUE;DB_NAME=%s", pname, e1, e2);
+ real_cnc_string = g_strdup_printf ("%s://DB_DIR=%s;EXTRA_FUNCTIONS=TRUE;DB_NAME=%s", pname, e1, e2);
g_free (e1);
g_free (e2);
gda_connection_string_split (real_cnc_string, &real_cnc, &real_provider, &user, &pass);
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
