GNOME.org
 

[libgda/LIBGDA_4.2] Added SQLite collations and locale related functions

commit 001d1812e2b4f30785ee12391346a4f95b9ccf7a
Author: Vivien Malerba <malerba gnome-db org>
Date:   Fri May 27 22:07:19 2011 +0200

    Added SQLite collations and locale related functions

 doc/C/libgda-4.0-docs.sgml                       |    4 +-
 doc/C/prov-notes.xml                             |   85 +++++++++
 libgda/sqlite/gda-sqlite-provider.c              |  205 +++++++++++++++++++++-
 libgda/sqlite/virtual/gda-vprovider-data-model.c |    4 +-
 providers/sqlite/sqlite_specs_dsn.xml.in         |   10 +-
 tools/browser/auth-dialog.c                      |    2 +-
 tools/gda-sql.c                                  |    2 +-
 7 files changed, 299 insertions(+), 13 deletions(-)
---
diff --git a/doc/C/libgda-4.0-docs.sgml b/doc/C/libgda-4.0-docs.sgml
index 256eba9..7336df7 100644
--- a/doc/C/libgda-4.0-docs.sgml
+++ b/doc/C/libgda-4.0-docs.sgml
@@ -1366,7 +1366,7 @@ Provider    | Description                       | DSN parameters     | File
 ------------+-----------------------------------+--------------------+-----------------------------------------------
 SQLite      | Provider for SQLite databases     | DB_NAME,           | /usr/lib/libgda-4.0/providers/libgda-sqlite.so  
                                                   DB_DIR,                                                            
-                                                  LOAD_GDA_FUNCTIONS                                                       
+                                                  EXTRA_FUNCTIONS                                                       
 Berkeley-DB | Provider for Berkeley databases   | FILE,              | /usr/lib/libgda-4.0/providers/libgda-bdb.so 
                                                   DATABASE                                                                
 [...]
@@ -1480,7 +1480,7 @@ Location: /usr/lib/libgda-4.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 2f2fcff..af76d11 100644
--- a/doc/C/prov-notes.xml
+++ b/doc/C/prov-notes.xml
@@ -73,10 +73,95 @@
 	      directory is used.</entry>
               <entry>No</entry>
 	    </row>
+	    <row>
+              <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>
 	  </tbody>
 	</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>
+
     <para>
       Also refer to the <link linkend="limitations_sqlite">SQLite's provider's limitations</link>.
     </para>
diff --git a/libgda/sqlite/gda-sqlite-provider.c b/libgda/sqlite/gda-sqlite-provider.c
index f0d770a..f3d6b1b 100644
--- a/libgda/sqlite/gda-sqlite-provider.c
+++ b/libgda/sqlite/gda-sqlite-provider.c
@@ -321,13 +321,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;
@@ -340,9 +343,27 @@ static ScalarFunction scalars[] = {
 	{"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_rmdiacr", 1, NULL, scalar_rmdiacr},
+	{"gda_rmdiacr", 2, NULL, scalar_rmdiacr},
+	{"gda_lower", 1, NULL, scalar_lower},
+	{"gda_upper", 1, NULL, scalar_upper},
 };
 
 
+/* 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
  */
@@ -610,6 +631,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
  */
@@ -621,7 +691,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;
+	const gchar *use_extra_functions = NULL, *with_fk = NULL, *locale_collate;
 	gint errmsg;
 	SqliteConnectionData *cdata;
 	gchar *dup = NULL;
@@ -644,7 +714,11 @@ 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");
+
+	locale_collate = gda_quark_list_find (params, "EXTRA_COLLATIONS");
 	if (auth)
 		passphrase = gda_quark_list_find (auth, "PASSWORD");
 
@@ -856,7 +930,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++) {
@@ -866,6 +940,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);
@@ -873,7 +949,26 @@ 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
@@ -883,6 +978,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
  */
@@ -3067,7 +3192,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 93ef15a..3050227 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 8eb2308..2f954d8 100644
--- a/providers/sqlite/sqlite_specs_dsn.xml.in
+++ b/providers/sqlite/sqlite_specs_dsn.xml.in
@@ -3,7 +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 c167dad..6e509f1 100644
--- a/tools/browser/auth-dialog.c
+++ b/tools/browser/auth-dialog.c
@@ -411,7 +411,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]