GNOME.org
 

[libgda/LIBGDA_4.0] Documentation improvements regarding SQL identifiers

commit 48d3dba2c2c1042eb535ad8c60df784111c3928a
Author: Vivien Malerba <malerba gnome-db org>
Date:   Sat Jul 18 14:07:38 2009 +0200

    Documentation improvements regarding SQL identifiers

 doc/C/SqlIdentifiers.dia   |  Bin 2964 -> 3560 bytes
 doc/C/SqlIdentifiers.png   |  Bin 54538 -> 69671 bytes
 doc/C/howto.xml            |   19 ++++++++++++++++++-
 doc/C/libgda-4.0-docs.sgml |   40 ++++++++++++++++------------------------
 libgda/gda-connection.c    |   11 +++++++++++
 libgda/gda-meta-store.c    |    4 ++++
 libgda/gda-meta-struct.c   |   15 +++++++++++++--
 libgda/gda-util.c          |    4 ++--
 po/POTFILES.in             |    2 +-
 9 files changed, 65 insertions(+), 30 deletions(-)
---
diff --git a/doc/C/SqlIdentifiers.dia b/doc/C/SqlIdentifiers.dia
index e8f105e..bb78d75 100644
Binary files a/doc/C/SqlIdentifiers.dia and b/doc/C/SqlIdentifiers.dia differ
diff --git a/doc/C/SqlIdentifiers.png b/doc/C/SqlIdentifiers.png
index d4a16da..63dd8a2 100644
Binary files a/doc/C/SqlIdentifiers.png and b/doc/C/SqlIdentifiers.png differ
diff --git a/doc/C/howto.xml b/doc/C/howto.xml
index a5129a3..1d88058 100644
--- a/doc/C/howto.xml
+++ b/doc/C/howto.xml
@@ -438,7 +438,8 @@ gboolean result;
 mcontext.column_names = g_new (gchar *, 1);
 mcontext.column_names[0] = "table_name";
 mcontext.column_values = g_new (GValue *, 1);
-g_value_set_string ((mcontext.column_values[0] = gda_value_new (G_TYPE_STRING)), "customers");
+g_value_take_string ((mcontext.column_values[0] = gda_value_new (G_TYPE_STRING)),
+                     gda_sql_identifier_quote ("customers"));
 result = gda_connection_update_meta_store (connection, &mcontext, &error);
 gda_value_free (mcontext.column_values[0]);
 if (!result) {
@@ -447,6 +448,22 @@ if (!result) {
 }]]>
       </programlisting>
     </para>
+    <para>
+      An important note about the <link linkend="gda-sql-identifier-quote">gda_sql_identifier_quote()</link> call
+      which has been added to ensure
+      that the contents of <code>mcontext.column_values</code> values is
+      conform to the convention used by the <link linkend="GdaMetaStore">GdaMetaStore</link>
+      to represent SQL identifiers when the
+      values represent an SQL identifier (which is the case in the example above). For more information,
+      see the <link linkend="information_schema:sql_identifiers">meta data section about SQL identifiers</link>.
+    </para>
+    <para>
+      Even though not strictly necessary, it can safely be used all the time.
+      For example if the table name for which a meta data update is requested had been <emphasis>"Customers"</emphasis>
+      (note the upper case C at the beginning and the double quotes) because this is how the database know it, then
+      not using <link linkend="gda-sql-identifier-quote">gda_sql_identifier_quote()</link> may not have done as
+      expected.
+    </para>
   </sect1>
 
   <sect1>
diff --git a/doc/C/libgda-4.0-docs.sgml b/doc/C/libgda-4.0-docs.sgml
index 44f41ba..7f6f8b1 100644
--- a/doc/C/libgda-4.0-docs.sgml
+++ b/doc/C/libgda-4.0-docs.sgml
@@ -441,32 +441,28 @@
 	  subject to each database engine implementation. All of them accept two syntaxes for SQL identifiers:
 	  <itemizedlist>
 	    <listitem><para>the first is if the SQL identifier is surrounded by double quotes (sometimes backquotes
-		or other characters), making the SQL identifier case sensitive (and also making
+		or other characters), usually making the SQL identifier case sensitive (and also making
 		it possible to use reserved SQL keywords as identifiers).</para></listitem>
-	    <listitem><para>the second is if it's not enquoted, meaning that the SQL identifier is not
+	    <listitem><para>the second is if it's not enquoted, usually meaning that the SQL identifier is not
 		case sensitive.</para></listitem>
 	  </itemizedlist>
 	  <note>
-	    <para>
-	      Many people consider that using the quoted syntax should be avoided if possible because:
-	      <itemizedlist>
-		<listitem><para>once an SQL identifier has been defined using the quoted syntax, it must
-		    <emphasis>always</emphasis> be used quoted, which is error prone especially if external
-		    tools are used on the database</para></listitem>
-		<listitem><para>the SQL code is less readable (there are quoted strings everywhere)</para></listitem>
-		<listitem><para>the SQL code is less portable as each database engine sets up different techniques to
-		    avoid confusing the SQL identifiers' quoting with SQL literals which are quoted when writing
-		    complex statements</para></listitem>
-		<listitem><para>writing SQL requires a more in-depth knowledge of the database engine being used and as
-		    a consequence it's far easier to make errors when writing SQL</para></listitem>
-	      </itemizedlist>
-	      For some more example, see
-	      <ulink url="http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html";>Database identifiers, quoting and case sensitivity</ulink> from  Lorenzo Alberton.
+	    <para>Sometimes those rules don't apply or apply only partially. For example a MySQL server, depending
+	      on how it is configured and on what kind of OS it is running on, will have different sets of meanings
+	      for these notations.
 	    </para>
 	  </note>
 	</para>
 
 	<para>
+	  As a result, &LIBGDA; has to be the least intrusive possible when the user wants to execute an SQL statement,
+	  and lets the database being accessed apply its own rules. However &LIBGDA; features meta data information
+	  retreival (getting the list of tables, views,...) and there some representation conventions have been fixed,
+	  see the <link linkend="information_schema:sql_identifiers">meta data section about SQL identifiers</link>
+	  for more information.
+	</para>
+
+	<para>
 	  The following diagram illustrates how &LIBGDA; handles SQL identifiers' representations depending where
 	  they are used:
 	  <mediaobject>
@@ -950,8 +946,9 @@ g_object_unref (store);
 	    represents them in lower
 	    case whereas Oracle represents them in upper case and MySQL represents them in upper or lower case
 	    depending on the platform and the configuration), &LIBGDA; has chosen by convention
-	    to represent, in the meta data, case insensitive SQL identifiers in lower case 
-	    and case sensitive SQL identifiers enclosed between double quotes. 
+	    to represent <emphasis>case insensitive SQL identifiers in lower case 
+	      and case sensitive SQL identifiers enclosed between double quotes</emphasis> when storing
+	    meta data in <link linkend="GdaMetaStore">GdaMetaStore</link>. 
 	  </para>
 	  <para>
 	    This convention has been made necessary to avoid confusion. To illustrate the problem, let's consider a 
@@ -974,11 +971,6 @@ g_object_unref (store);
 	    or systematically enquote the SQL identifier which is not something &LIBGDA; can impose to
 	    application developpers.
 	  </para>
-	  <para>
-	    This convention must be respected by database providers' implementations or the 
-	    <link linkend="GdaMetaStruct">GdaMetaStruct</link> object and the associated features
-	    (such as keyword completion) will not work properly.
-	  </para>
 	</sect2>
 
 	<sect2>
diff --git a/libgda/gda-connection.c b/libgda/gda-connection.c
index 4f4d65c..cfde721 100644
--- a/libgda/gda-connection.c
+++ b/libgda/gda-connection.c
@@ -3035,6 +3035,12 @@ suggest_update_cb_downstream (GdaMetaStore *store, GdaMetaContext *suggest, Down
  *                   about views may be updated</para></listitem>
  * </itemizedlist>
  *
+ * When @context is not %NULL, and contains specified SQL identifiers (for example the "table_name" of the "_tables"
+ * table), then each SQL identifier has to match the convention the #GdaMetaStore has adopted regarding
+ * case sensitivity. see the <link linkend="information_schema:sql_identifiers">
+ * meta data section about SQL identifiers</link> for more information, and the documentation about the
+ * gda_sql_identifier_quote() function which will be most usefull.
+ *
  * Note however that usually <emphasis>more</emphasis> information will be updated than strictly requested by
  * the @context argument.
  *
@@ -3414,6 +3420,11 @@ prepare_meta_statements_hash (void)
  *
  * For more information about the returned data model's attributes, or about the @meta_type and ... filter arguments,
  * see <link linkend="GdaConnectionMetaTypeHead">this description</link>.
+ *
+ * Also, when using filters involving data which are SQL identifiers, make sure each SQL identifier
+ * is represented using the #GdaMetaStore convention; see the <link linkend="information_schema:sql_identifiers">
+ * meta data section about SQL identifiers</link> for more information, and the documentation about the
+ * gda_sql_identifier_quote() function which will be most usefull.
  * 
  * Returns: a #GdaDataModel containing the data required. The caller is responsible
  * for freeing the returned model using g_object_unref().
diff --git a/libgda/gda-meta-store.c b/libgda/gda-meta-store.c
index 7913c1c..7ca8208 100644
--- a/libgda/gda-meta-store.c
+++ b/libgda/gda-meta-store.c
@@ -2043,6 +2043,10 @@ gda_meta_store_get_internal_connection (GdaMetaStore *store) {
  *
  * Extracts some data stored in @store using a custom SELECT query.
  *
+ * For more information about
+ * SQL identifiers are represented in @store, see the
+ * <link linkend="information_schema:sql_identifiers">meta data section about SQL identifiers</link>.
+ *
  * Returns: a new #GdaDataModel, or %NULL if an error occurred
  */
 GdaDataModel *
diff --git a/libgda/gda-meta-struct.c b/libgda/gda-meta-struct.c
index 4e2dc88..c64fa05 100644
--- a/libgda/gda-meta-struct.c
+++ b/libgda/gda-meta-struct.c
@@ -433,12 +433,15 @@ prepare_sql_identifier_for_compare (gchar *str)
  * If @catalog is %NULL and @schema is not %NULL, then the database object will be the one which 
  * can be accessed by its @schema  name name.
  *
- * Important note: @catalog, @schema and @name must respect the following convention:
+ * Important note: @catalog, @schema and @name will be used using the following convention:
  * <itemizedlist>
  *   <listitem><para>be surrounded by double quotes for a case sensitive search</para></listitem>
- *   <listitem><para>otherwise they will be converted to lower case for search</para></listitem>
+ *   <listitem><para>otherwise for case insensitive search</para></listitem>
  * </itemizedlist>
  *
+ * For more information, see the <link linkend="information_schema:sql_identifiers">
+ * meta data section about SQL identifiers</link>.
+ *
  * Returns: the #GdaMetaDbObject corresponding to the database object if no error occurred, or %NULL
  */
 GdaMetaDbObject *
@@ -1090,6 +1093,8 @@ array_type_to_sql (GdaMetaStore *store, const GValue *specific_name)
  * If @catalog is %NULL, then any catalog will be used, and
  * if @schema is %NULL then any schema will be used (if @schema is %NULL then catalog must also be %NULL).
  *
+ * Please refer to gda_meta_struct_complement() form more information.
+ *
  * Returns: TRUE if no error occurred
  */
 gboolean
@@ -1288,6 +1293,8 @@ real_gda_meta_struct_complement_all (GdaMetaStruct *mstruct, gboolean default_on
  * database object which are useable using only their short name (that is which do not need to be prefixed by 
  * the schema in which they are to be used).
  *
+ * Please refer to gda_meta_struct_complement() form more information.
+ *
  * Returns: TRUE if no error occurred
  */
 gboolean
@@ -1303,6 +1310,8 @@ gda_meta_struct_complement_default (GdaMetaStruct *mstruct, GError **error)
  *
  * This method is similar to gda_meta_struct_complement() and gda_meta_struct_complement_default()
  * but creates #GdaMetaDbObject for all the database object.
+ * 
+ * Please refer to gda_meta_struct_complement() form more information.
  *
  * Returns: TRUE if no error occurred
  */
@@ -1321,6 +1330,8 @@ gda_meta_struct_complement_all (GdaMetaStruct *mstruct, GError **error)
  * This method is similar to gda_meta_struct_complement() but creates #GdaMetaDbObject for all the dependencies
  * of @dbo.
  *
+ * Please refer to gda_meta_struct_complement() form more information.
+ *
  * Returns: TRUE if no error occurred
  */
 gboolean
diff --git a/libgda/gda-util.c b/libgda/gda-util.c
index fd1d458..750ed69 100644
--- a/libgda/gda-util.c
+++ b/libgda/gda-util.c
@@ -1592,8 +1592,8 @@ static gboolean _sql_identifier_needs_quotes (const gchar *str);
  * <itemizedlist>
  *   <listitem>
  *     <para>it is correctly formatted
- *           to be used with @cnc (if @cnc is %NULL, then the standard SQL quoting rules will be applied) if
- *           @for_meta_store is %FALSE;
+ *           to be used with @cnc (if @cnc is %NULL, then some default SQL quoting rules will be applied,
+ *           similar to PostgreSQL's way) if @for_meta_store is %FALSE;
  *     </para>
  *   </listitem>
  *   <listitem>
diff --git a/po/POTFILES.in b/po/POTFILES.in
index a022d40..8516719 100644
--- a/po/POTFILES.in
+++ b/po/POTFILES.in
@@ -42,7 +42,7 @@ libgda/sqlite/gda-sqlite-meta.c
 libgda/sqlite/gda-sqlite-provider.c
 libgda/sqlite/gda-sqlite-pstmt.c
 libgda/sqlite/gda-sqlite-recordset.c
-libgda/sqlite/utils.c
+libgda/sqlite/gda-sqlite-util.c
 libgda/sqlite/virtual/gda-vconnection-data-model.c
 libgda/sqlite/virtual/gda-vconnection-hub.c
 libgda/sqlite/virtual/gda-virtual-connection.c
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]