*** Bug 589103 has been marked as a duplicate of this bug. ***

Created attachment 138785 [details] [review]
gda-capitals-add-column.c

Note that I left the working code in, and commenting out the code that shows the problem. Sorry:

  const char* table_name = "\"CapitalTest8\"";
  /* This creates a "capitaltest8" table instead of a "CapitalTest8" table: 
  const char* table_name = "CapitalTest8";
  */

Created attachment 138807 [details]
Slightly modified version where gda_sql_identifier_quote() is used.

Using the GdaServerOperation object does not _require_ you to call gda_sql_identifier_quote() like you have to do with gda_connection_update_meta_store(), where you _have to_ or otherwise you may  not get the expected result.

This means that the SQL identifier string you passed using gda_server_operation_set_value_at() is used as-is, unchanged, in the resulting SQL actually being used.

So the example works as expected, the SQL being "CREATE TABLE CapitalTest8 ...", for which postgres creates a capitaltest8 named table. It does work with SQLite because SQLite implements a different set of rules.

Libgda can't assume someone using CapitalTest8 as a table name really means to keep the case sensitivity because this depends on the application's policy and is a matter of user/programmer choice (imagine if psql did this...)

Now if within Glom you you want to make sure CapitalTest8 creates a table where the case is kept, you need to call gda_sql_identifier_quote (..., TRUE) and pass that string to gda_server_operation_set_value_at(), as illustrated in the slightly modified version of your example.

OK, please update the documentation to mention that you need to use  gda_sql_identifier_quote() there if you want it to create what you said you want to create.

> Libgda can't assume someone using CapitalTest8 as a table name really means to
> keep the case sensitivity because this depends on the application's policy and
> is a matter of user/programmer choice (imagine if psql did this...)

As I've said repeatedly (sorry for that, but I find this so awful), Why can't libgda just do what I say?
1. If the application or programmer wants to lowercase everything then they can. 
2. libgda should hide backend-specific quirks.

I see no need to impose SQL's inconsitency and weirdness on to libgda's API.

(In reply to comment #4)
(imagine if psql did this...)

psql is a place to write SQL, so obviously quotes are part of that system. To prove that this API is a good idea, you'll need to come up with a better real-world example.

Because the default in database applications ecosystem is to consider that the
user does not care about case sensitivity. User A can name a table MyTable and
user B name it mytable cand user C MYTABLE and they usually all speak of the
same object.

As Libgda is a _general purpose_ library (even for writing command line tools), it can't cut itself from that kind of
users/applications.

And even if the majority of users wanted the case sensitivity to be preserved,
the work needed to be done for the other users wo don't care about case
sensitivity would otherwise be enormous to be able to use Libgda.

The current API does on the contrary hide SQL's inconsitency and weirdness: use
gda_sql_identifier_quote() to do it an a database agnostic way. Of course
making this call could be done automatically but deciding to use TRUE or FALSE
as the last argument depend on the programmer.

(In reply to comment #7)
> Because the default in database applications ecosystem is to consider that the
> user does not care about case sensitivity.

There are very few _users_ in the database application ecosytem. Real (non-techy) users have no idea of this issue.

Database developers are well aware of the problem and use quotes when writing SQL. libgda is not SQL so they'd expect it to be better behaved.

Maybe you could consider fixing this for a future parallel-installable libgda.

Ok, here is what I propose: add a new GDA_CONNECTION_OPTIONS_KEEP_IDENTIFIERS_CASE option to gda_connection_open*() which a programmer can use to specify that all the SQL identifiers will have to be kept case sensitive whenever Libgda really manipulates them. This way it can be in 4.1.

What do you think?

That would be a vast improvement that would greatly improve my mood and would make me very thankful.

"case" might not be the best name, because this is also an issue of spaces, etc.

Ok, what about GDA_CONNECTION_OPTIONS_SQL_IDENTIFIERS_CASE_SENSITIVE (a bit long but who cares)?

Along with this I can add:
gda_meta_store_quote(const char*, GdaConnection *cnc)
and
gda_server_provider_quote (GdaServerProvider *provider, const char*), or
gda_connection_sql_identifier_quote(GdaConnection *cnc, const char*) or both.

(In reply to comment #11)
> Ok, what about GDA_CONNECTION_OPTIONS_SQL_IDENTIFIERS_CASE_SENSITIVE (a bit
> long but who cares)?

That still mentions only case. Either is fine, I guess as long as what it actually means is documented. I can't think of a better name than SQL_IDENTIFIERS_AS_SPECIFIED, but that's silly.
 
> Along with this I can add:
> gda_meta_store_quote(const char*, GdaConnection *cnc)
> and
> gda_server_provider_quote (GdaServerProvider *provider, const char*), or
> g_connection_sql_identifier_quote(GdaConnection *cnc, const char*) or both.

gda_connection_quote(GdaConection* cnc, const char*) would be more consistent for that last one.

But I wonder when the GdaServerProvider is relevant? Isn't the GdaConnection enough to know what to do?

> gda_connection_quote(GdaConection* cnc, const char*) would be more consistent
> for that last one.

Ok.

> But I wonder when the GdaServerProvider is relevant? Isn't the GdaConnection
> enough to know what to do?

Usually yes, but sometimes (for example for testing but not only) only a provider pointer is available, but I guess it's a corner case and there is no need for a specific API for this.

Also, if you need it for Glom, I think I can backport the work in LIBGDA_4.0 as well, so tell me.

On second thoughts, a gda_server_provider_quote() would make sense _if_ it would be more efficient if I already have a GdaServerProvider. I have no objection to convenience API.

Yes, this would be welcome in libgda-4.0. You really should have an Amazon wish-list or suchlike.

I've just pushed the changes implementing all this, both in master and LIBGDA_4.0 branches. Can ou check it?

Thanks. It seems to work very well and already makes Glom better.

We seem to now have a problem discovering new fields that we added to tables such as "CapitalTest" but I will investigate tomorrow to see if it's a libgda or Glom problem.

Getting the fields does not seem to work yet: Bug #589607

There's still an issue with updating metadat after adding a field: Bug 589822