Re: [gnome-db] Per-table meta-data with capital letters in the table name?

[Vivien Malerba <vmalerba gmail com>]

2009/6/30 Murray Cumming <murrayc murrayc com>

On Tue, 2009-06-30 at 09:08 +0200, Vivien Malerba wrote:
> So we need to add quotes around the table_name (and escape the text),
> > when setting the table name in GdaMetaContext, as in the libgdamm
> code
> > below? If so, that's definitely something that should be mentioned
> in
> > the gda_connection_update_meta_store() and/or GdaMetaContext
> > documentation.
>
>
> Yes, and I'll correct that documentation point.
> >
> > Is there a function we can use to quote and escape the table name?

> Yes:  gda_sql_identifier_add_quotes(); not that there is also a
> gda_sql_identifier_remove_quotes() function.

And we must also us gda_server_provider_escape_string(), and
gda_server_provider_unescape_string(), right?

No, you only need to use these if you want to create SQL statements with literals in it, which is a _horrible_ thing since it is an open door to SQL injection problems. If you use variables, then you never have to worry about escaping your input values.

We also noticed that the GDA_CONNECTION_META_TABLES meta data has table
names that are sometimes quoted, but sometimes not. That's horribly
inconsistent. Surely libgda should remove any quotes for us before it
gets to the application.
http://bugzilla.gnome.org/show_bug.cgi?id=587051#c5

This is due to a choice I made when implementing the GdaMetaStore. I really wanted to store identifiers in a format common to all the databases, and the rule I chose is the following: the identifier as reported by the meta store can _always_ be used as is, without having to worry about whether it need quotes or not (the easiest path for the user). This is why sometimes you have quoted identifiers and sometimes not in the meta store.
 
Just to remind you of the variety of situations:
* Postgres internally uses lower case SQL identifiers, and converts upper case to lower case if not quoted
* Oracle internally uses upper case SQL identifiers, and converts lower case to upper case if not quoted
* MySQL internally uses upper or lower case depending on the filesystem which actually stores the data

I guess we are lucky that " must be escaped in table names, or there
would be no way to know what was a quote and what was a quote character
that's part of the name. Yes, I know that " is unusual in a table name,
but not impossible.

This is why it's now the easiest: use the SQL identifier as it comes from the meta store, don't try to see if it needs to be quoted or not.