|
Per-table meta-data with capital letters in the table name?
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/6/29 Murray Cumming <murrayc@...>
I should take the time to make a test case, but it looks like libgda 4.0
fails to get the meta-data for a specified table if the table name has
capital letters. I guess that this is a quoting (and escaping) problem
somewhere. _______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Mon, 2009-06-29 at 17:45 +0200, Vivien Malerba wrote:
>
>
> 2009/6/29 Murray Cumming < murrayc@...>
> I should take the time to make a test case, but it looks like
> libgda 4.0
> fails to get the meta-data for a specified table if the table
> name has
> capital letters. I guess that this is a quoting (and escaping)
> problem
> somewhere.
>
> Yes, you need to surround any SQL identifier which is not case
> insensitive (or which has some spaces, etc in it) with double quotes.
> You can use gda_sql_identifier_needs_quotes().
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.
Is there a function we can use to quote and escape the table name?
bool Connection::update_meta_store_table(const Glib::ustring&
table_name, const Glib::ustring& schema_name)
{
GValue table_name_value = { 0 };
g_value_init(&table_name_value, G_TYPE_STRING);
g_value_set_static_string(&table_name_value, table_name.c_str());
GValue table_schema_value = { 0 };
g_value_init(&table_schema_value, G_TYPE_STRING);
g_value_set_static_string(&table_schema_value, schema_name.c_str());
gchar* column_names[] = { (gchar*)"table_name" ,
(gchar*)"table_schema"};
GValue* column_values[] = { &table_name_value, &table_schema_value };
GdaMetaContext mcontext = {(gchar*)"_tables", schema_name.empty() ?
1 : 2, column_names, column_values };
GError* gerror = 0;
const bool retval = gda_connection_update_meta_store(gobj(),
&mcontext, &gerror);
g_value_unset(&table_name_value);
g_value_unset(&table_schema_value);
if(gerror)
::Glib::Error::throw_exception(gerror);
return retval;
}
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/6/29 Murray Cumming <murrayc@...>
On Mon, 2009-06-29 at 17:45 +0200, Vivien Malerba wrote:
>
>
> 2009/6/29 Murray Cumming < murrayc@...>
> I should take the time to make a test case, but it looks like
> libgda 4.0
> fails to get the meta-data for a specified table if the table
> name has
> capital letters. I guess that this is a quoting (and escaping)
> problem
> somewhere.
>
> Yes, you need to surround any SQL identifier which is not case
> insensitive (or which has some spaces, etc in it) with double quotes.
> You can use gda_sql_identifier_needs_quotes().
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.
bool Connection::update_meta_store_table(const Glib::ustring&
table_name, const Glib::ustring& schema_name)
{
GValue table_name_value = { 0 };
g_value_init(&table_name_value, G_TYPE_STRING);
g_value_set_static_string(&table_name_value, table_name.c_str());
GValue table_schema_value = { 0 };
g_value_init(&table_schema_value, G_TYPE_STRING);
g_value_set_static_string(&table_schema_value, schema_name.c_str());
gchar* column_names[] = { (gchar*)"table_name" ,
(gchar*)"table_schema"};
GValue* column_values[] = { &table_name_value, &table_schema_value };
GdaMetaContext mcontext = {(gchar*)"_tables", schema_name.empty() ?
1 : 2, column_names, column_values };
GError* gerror = 0;
const bool retval = gda_connection_update_meta_store(gobj(),
&mcontext, &gerror);
g_value_unset(&table_name_value);
g_value_unset(&table_schema_value);
if(gerror)
::Glib::Error::throw_exception(gerror);
return retval;
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
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?
This complicates things greatly and makes it very likely that many
libgda-based applications will have bugs because developers will forget
to do this. I think libgda should just accept normal strings and do the
right thing internally.
At the least, libgda should warn if things are not quoted when they
should be quoted.
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#c5I 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.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Tue, 2009-06-30 at 09:08 +0200, Vivien Malerba wrote:
> note that there is also a
> gda_sql_identifier_remove_quotes() function.
From
http://library.gnome.org/devel/libgda/unstable/GdaSqlParser.html#gda-sql-identifier-remove-quotes:
"
Prepares str to be compared: - if surrounded by double quotes or single
quotes, then just remove the quotes - otherwise convert to lower case
"
Why on earth does that covert to lower case if there are no quotes. That
means that my string will be changed if I call this on a string that has
no quotes. That's so annoying.
And there's no has_quotes() function to help me decide to call it. I
guess !gda_sql_identifier_needs_quotes() might do it, but first that
function would have to be documented to say what it actually checks for.
How can it know when something _needs_ quotes?
If you want to do that the you should do it in some other function.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
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.
And do we need to quote the table name when using
gda_connection_get_meta_store_data() with GDA_CONNECTION_META_FIELD?
That seems to break that for any tables, though I don't have a C test
case to prove it yet.
It certainly doesn't work either way for tables with capital letters in
their name, which seem to be stored with quotes in the meta data.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/6/30 Murray Cumming <murrayc@...>
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.
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/6/30 Murray Cumming <murrayc@...>
On Tue, 2009-06-30 at 09:08 +0200, Vivien Malerba wrote:
> note that there is also a
> gda_sql_identifier_remove_quotes() function.
From
http://library.gnome.org/devel/libgda/unstable/GdaSqlParser.html#gda-sql-identifier-remove-quotes
:
"
Prepares str to be compared: - if surrounded by double quotes or single
quotes, then just remove the quotes - otherwise convert to lower case
"
Why on earth does that covert to lower case if there are no quotes. That
means that my string will be changed if I call this on a string that has
no quotes. That's so annoying.
This function is intended to be used for SQL identifiers, not SQL literal values. For SQL identifiers you don't care how they are represented as long as they represent the correct object: for example the *myTable* object is the same as the *mytable* object (I used asterixes to avoid any confusion with double quotes).
And there's no has_quotes() function to help me decide to call it. I
guess !gda_sql_identifier_needs_quotes() might do it, but first that
function would have to be documented to say what it actually checks for.
How can it know when something _needs_ quotes?
You don't need any has_quotes() function for SQL identifiers (again because they are not SQL literal values).
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Wed, 2009-07-01 at 10:15 +0200, Vivien Malerba wrote:
> I really
> wanted to store identifiers in a format common to all the databases,
How you _store_ identifiers should be irrelevant to the API for
applications.
> and the
> rule I chose is the following: the identifier as reported by the meta
> store
> can _always_ be used as is,
Maybe it can be used "as is" for other parts of the metadata API, and
maybe for building SQL statements. But it can't be used as is to show a
table name to a user.
And this quotes/no-quotes/lowercasing dance is awful when trying to give
something to the meta data api that's based on user input. I feel it
makes my app's behaviour very unstable.
I shouldn't need to do
some_api_do_something( some_api_transform_the_parameter_so_it_works(thing) );
If a function sometimes wants quotes around an input string then it
should put quotes around it. Likewise with the lowercasing.
Of course, this could be partly solved by just not using the metadata
API. I'd much rather have simple get_table_names() and
get_field_names_for_table() functions that had none of this quoting
nonsense.
> 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.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Wed, 2009-07-01 at 10:19 +0200, Vivien Malerba wrote:
> For SQL identifiers you don't care how they are represented as long
> as they represent the correct object: for example the *myTable* object
> is
> the same as the *mytable* object (I used asterixes to avoid any
> confusion
> with double quotes).
Are you sure? Is this specified somewhere?
Surely these refer to different tables?
SELECT * FROM "mytable"
SELECT * FROM "MyTable"
Note that I'm not interested in the behaviour if I didn't use quotes in
the SQL statement. I want it to use what I mean.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Tue, 2009-06-30 at 09:08 +0200, Vivien Malerba wrote:
>
>
> 2009/6/29 Murray Cumming < murrayc@...>
> On Mon, 2009-06-29 at 17:45 +0200, Vivien Malerba wrote:
> >
> >
> > 2009/6/29 Murray Cumming < murrayc@...>
> > I should take the time to make a test case, but it
> looks like
> > libgda 4.0
> > fails to get the meta-data for a specified table if
> the table
> > name has
> > capital letters. I guess that this is a quoting (and
> escaping)
> > problem
> > somewhere.
> >
> > Yes, you need to surround any SQL identifier which is not
> case
> > insensitive (or which has some spaces, etc in it) with
> double quotes.
> > You can use gda_sql_identifier_needs_quotes().
>
>
> 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.
Are you sure? The testcase at
http://bugzilla.gnome.org/show_bug.cgi?id=587051#c17 suggests the
contrary.
> 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.
>
>
>
>
>
> bool Connection::update_meta_store_table(const Glib::ustring&
> table_name, const Glib::ustring& schema_name)
> {
> GValue table_name_value = { 0 };
> g_value_init(&table_name_value, G_TYPE_STRING);
> g_value_set_static_string(&table_name_value,
> table_name.c_str());
>
> GValue table_schema_value = { 0 };
> g_value_init(&table_schema_value, G_TYPE_STRING);
> g_value_set_static_string(&table_schema_value,
> schema_name.c_str());
>
> gchar* column_names[] = { (gchar*)"table_name" ,
> (gchar*)"table_schema"};
> GValue* column_values[] = { &table_name_value,
> &table_schema_value };
>
> GdaMetaContext mcontext = {(gchar*)"_tables",
> schema_name.empty() ?
> 1 : 2, column_names, column_values };
> GError* gerror = 0;
> const bool retval = gda_connection_update_meta_store(gobj(),
> &mcontext, &gerror);
>
> g_value_unset(&table_name_value);
> g_value_unset(&table_schema_value);
>
> if(gerror)
> ::Glib::Error::throw_exception(gerror);
>
> return retval;
> }
>
> >
> > The doc in
> >
> http://library.gnome.org/devel/libgda/4.0/information_schema.html#information_schema:sql_identifiers> > mentions the quotes usage in Libgda, but maybe I should add
> reminders
> > in the functions's documentation for functions where this
> may be a
> > problem.
> >
> > Vivien
> >
> >
> >
> >
>
>
> --
> murrayc@...
> www.murrayc.com
> www.openismus.com
Armin
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/7/1 Armin Burgmeier <armin@...>
On Tue, 2009-06-30 at 09:08 +0200, Vivien Malerba wrote:
>
>
> 2009/6/29 Murray Cumming < murrayc@...>
> On Mon, 2009-06-29 at 17:45 +0200, Vivien Malerba wrote:
> >
> >
> > 2009/6/29 Murray Cumming < murrayc@...>
> > I should take the time to make a test case, but it
> looks like
> > libgda 4.0
> > fails to get the meta-data for a specified table if
> the table
> > name has
> > capital letters. I guess that this is a quoting (and
> escaping)
> > problem
> > somewhere.
> >
> > Yes, you need to surround any SQL identifier which is not
> case
> > insensitive (or which has some spaces, etc in it) with
> double quotes.
> > You can use gda_sql_identifier_needs_quotes().
>
>
> 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.
http://bugzilla.gnome.org/show_bug.cgi?id=587051#c17 suggests the
contrary.
There is obviously a bug there... Let me correct it.
Vivien
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/7/1 Murray Cumming <murrayc@...>
On Wed, 2009-07-01 at 10:19 +0200, Vivien Malerba wrote:
> For SQL identifiers you don't care how they are represented as long
> as they represent the correct object: for example the *myTable* object
> is
> the same as the *mytable* object (I used asterixes to avoid any
> confusion
> with double quotes).
Are you sure? Is this specified somewhere?
Surely these refer to different tables?
SELECT * FROM "mytable"
SELECT * FROM "MyTable"
I used asterixes, not double quotes, so I was saying that
SELECT * FROM mytable
SELECT * FROM MyTable
are the same
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Wed, 2009-07-01 at 12:04 +0200, Vivien Malerba wrote:
> I used asterixes, not double quotes, so I was saying that
> SELECT * FROM mytable
> SELECT * FROM MyTable
> are the same
Again, I don't see how this is interesting. I always use quotes in
Glom's generated SQL so that we say what we mean in SQL, without doubt.
I guess that libgda does the same. If not, I wonder why not.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/7/1 Murray Cumming <murrayc@...>
> and the
> rule I chose is the following: the identifier as reported by the meta
> store
> can _always_ be used as is,
Maybe it can be used "as is" for other parts of the metadata API, and
maybe for building SQL statements. But it can't be used as is to show a
table name to a user.
Just test if the 1st char is a double quote and if so then call gda_sql_identifier_remove_quotes().
And this quotes/no-quotes/lowercasing dance is awful when trying to give
something to the meta data api that's based on user input. I feel it
makes my app's behaviour very unstable.
If you have use input, then use gda_sql_identifier_needs_quotes()/gda_sql_identifier_add_quotes().
You won't be able to have at the same time a database abstraction layer and use user input without any validation. For example if you the user has a MyTable table name, then to use this table you _will_ at some point have to check if you need quotes or not yourself. Libgda offers you some functions to do it and gives you some "normalized" representation and behaviour. Of course if you always quote SQL identifiers you don't care, but Libgda can't assume its users want to systematically quote SQL identifiers.
I shouldn't need to do
some_api_do_something( some_api_transform_the_parameter_so_it_works(thing) );
If a function sometimes wants quotes around an input string then it
should put quotes around it. Likewise with the lowercasing.
You can't reliably determine what the user had in mind as SQL has never standardized on case sensitiveness of SQL identifiers (this is AKAIK the only language where this is the case). For example a user may enter (using here asterixes instead of doule quotes to avoid confusion) *mytable*, or *MYTABLE* or *Mytable* to represent the same object because he has his own habits (Postgres or Oracle background, or prefering readability).
Anyway, I've committed to git master and LIBGDA_4.0 branches corrections with documentation which explain the design choices that were made. Regards, Vivien
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Sun, 2009-07-05 at 17:55 +0200, Vivien Malerba wrote:
> You won't be able to have at the same time a database abstraction
> layer and use user input without any validation. For example if you
> the user has a MyTable table name, then to use this table you _will_
> at some point have to check if you need quotes or not yourself.
I always use quotes. I don't need to check.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
On Sun, 2009-07-05 at 19:20 +0200, Murray Cumming wrote:
> On Sun, 2009-07-05 at 17:55 +0200, Vivien Malerba wrote:
> > You won't be able to have at the same time a database abstraction
> > layer and use user input without any validation. For example if you
> > the user has a MyTable table name, then to use this table you _will_
> > at some point have to check if you need quotes or not yourself.
>
> I always use quotes. I don't need to check.
Vivien wrote:
> Anyway, I've committed to git master and LIBGDA_4.0 branches >
> corrections with documentation which explain the design choices that
> were made.
Thanks for the recent fixes. Things seem to work now in Glom but I
wanted to check that we are really doing the right thing. I have some
questions.
1.
You added a section about SQL Identifiers that mentions quotes:
But I find this surprising, and I doubt that it's true:
http://git.gnome.org/cgit/libgda/tree/doc/C/libgda-4.0-docs.sgml#n465"
Many people consider that using the quoted syntax should be avoided if
possible because:
- once an SQL identifier has been defined using the quoted syntax, it
must always be used quoted, which is error prone especially if external
tools are used on the database.
"
2.
The latest documentation is not online yet because it is not yet in a
tarball, so I can't link to it, but I've built it locally.
I still can't see anything in the gda_connection_update_meta_store() (or
anything linked from there) about whether or not we should use
gda_sql_identifier_add_quotes() for the "table_name" value in
GdaMataContext::column_values.
The "Update the meta data about a table" section doesn't use
gda_sql_identifier_add_quotes(), though we now seem to need to use it in
libgdamm. Note that that section mentions a "Get information" section,
but there is no link.
3.
In fact, I don't see any link from GdaMetaContext to a description of
possible values for the GdaMetaContext field structs, though the
gda_connection_update_meta_store() documentation mentios "_tables" and
"_columns" as possible values for GdaMetaContext::table_name.
The "database structure" section provides some hints, but the
documentation should explain how that maps to GdaMetaContext."
Maybe this should be bug reports instead.
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
2009/7/13 Murray Cumming <murrayc@...>
On Sun, 2009-07-05 at 19:20 +0200, Murray Cumming wrote:
> On Sun, 2009-07-05 at 17:55 +0200, Vivien Malerba wrote:
> > You won't be able to have at the same time a database abstraction
> > layer and use user input without any validation. For example if you
> > the user has a MyTable table name, then to use this table you _will_
> > at some point have to check if you need quotes or not yourself.
>
> I always use quotes. I don't need to check.
Vivien wrote:
> Anyway, I've committed to git master and LIBGDA_4.0 branches >
> corrections with documentation which explain the design choices that
> were made.
Thanks for the recent fixes. Things seem to work now in Glom but I
wanted to check that we are really doing the right thing. I have some
questions.
1.
You added a section about SQL Identifiers that mentions quotes:
But I find this surprising, and I doubt that it's true:
http://git.gnome.org/cgit/libgda/tree/doc/C/libgda-4.0-docs.sgml#n465
"
Many people consider that using the quoted syntax should be avoided if
possible because:
- once an SQL identifier has been defined using the quoted syntax, it
must always be used quoted, which is error prone especially if external
tools are used on the database.
"
From my own experience and from what I've gathered from other people and from the misc docs and books, this is a kind of personnal recommendation I'd do. I added it in the doc to explain that Libgda could not force people to use quotes all the time. I can remove it if you think it's out of topic or is out of Libgda's scope.
2.
The latest documentation is not online yet because it is not yet in a
tarball, so I can't link to it, but I've built it locally.
I still can't see anything in the gda_connection_update_meta_store() (or
anything linked from there) about whether or not we should use
gda_sql_identifier_add_quotes() for the "table_name" value in
GdaMataContext::column_values.
The "Update the meta data about a table" section doesn't use
gda_sql_identifier_add_quotes(), though we now seem to need to use it in
libgdamm. Note that that section mentions a "Get information" section,
but there is no link.
3.
In fact, I don't see any link from GdaMetaContext to a description of
possible values for the GdaMetaContext field structs, though the
gda_connection_update_meta_store() documentation mentios "_tables" and
"_columns" as possible values for GdaMetaContext::table_name.
The "database structure" section provides some hints, but the
documentation should explain how that maps to GdaMetaContext."
Sorry I've been so long to answer, but I've been busy fixing the SQL identifiers mess (there are so many differences between databases in details...)
Anyway, I've just pushed in git LIBGDA_4.0 branch, some fixes in the gda_sql_identifier_quote() function which you can use in Glom, along with a test case (which I'll soon add to the NR tests directory), attached (for the PostgreSQL and MySQL you need to create an empty "testcase" database).
Basically, you have an SQL Id (presumably entered by the user), and use gda_sql_identifier_quote(id,...,FALSE,...) to have a valid SQL identifier to create a database object (for example a table), and use again gda_sql_identifier_quote(id,...,TRUE,...) with gda_connection_update_meta_store() or other GdaMetaStore's related functions. See the test case in the attachment.
I'll apply the modifications to the master branch and fix all the documentation which needs to be fixed ASAP.
Regards,
Vivien
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
|
Re: Per-table meta-data with capital letters in the table name?
> You added a section about SQL Identifiers that mentions
> quotes:
>
> But I find this surprising, and I doubt that it's true:
> http://git.gnome.org/cgit/libgda/tree/doc/C/libgda-4.0-docs.sgml#n465> "
> Many people consider that using the quoted syntax should be
> avoided if
> possible because:
> - once an SQL identifier has been defined using the quoted
> syntax, it
> must always be used quoted, which is error prone especially if
> external
> tools are used on the database.
> "
>
> From my own experience and from what I've gathered from other people
> and from the misc docs and books, this is a kind of personnal
> recommendation I'd do. I added it in the doc to explain that Libgda
> could not force people to use quotes all the time. I can remove it if
> you think it's out of topic or is out of Libgda's scope.
If you don't know that it's _true_ then, yes, please remove it.
If it's not true then I am concerned that it's the basis of your API
choices.
> 2.
> The latest documentation is not online yet because it is not
> yet in a
> tarball, so I can't link to it, but I've built it locally.
>
> I still can't see anything in the
> gda_connection_update_meta_store() (or
> anything linked from there) about whether or not we should use
> gda_sql_identifier_add_quotes() for the "table_name" value in
> GdaMataContext::column_values.
>
> The "Update the meta data about a table" section doesn't use
> gda_sql_identifier_add_quotes(), though we now seem to need to
> use it in
> libgdamm. Note that that section mentions a "Get information"
> section,
> but there is no link.
>
> 3.
> In fact, I don't see any link from GdaMetaContext to a
> description of
> possible values for the GdaMetaContext field structs, though
> the
> gda_connection_update_meta_store() documentation mentios
> "_tables" and
> "_columns" as possible values for GdaMetaContext::table_name.
>
> The "database structure" section provides some hints, but the
> documentation should explain how that maps to GdaMetaContext."
>
> Sorry I've been so long to answer, but I've been busy fixing the SQL
> identifiers mess (there are so many differences between databases in
> details...)
> Anyway, I've just pushed in git LIBGDA_4.0 branch,
Oh, I suggested in bugzilla that you keep these changes out of stable
4.0, because it all seems so fragile. But OK, if it works it will be
useful there.
This is all in master (4.1) too, right?
> some fixes in the gda_sql_identifier_quote() function which you can
> use in Glom, along with a test case (which I'll soon add to the NR
> tests directory), attached (for the PostgreSQL and MySQL you need to
> create an empty "testcase" database).
>
> Basically, you have an SQL Id (presumably entered by the user), and
> use gda_sql_identifier_quote(id,...,FALSE,...) to have a valid SQL
> identifier to create a database object (for example a table), and use
> again gda_sql_identifier_quote(id,...,TRUE,...) with
> gda_connection_update_meta_store() or other GdaMetaStore's related
> functions. See the test case in the attachment.
>
> I'll apply the modifications to the master branch and fix all the
> documentation which needs to be fixed ASAP.
Thanks. I'll take a proper look when the documentation is at
library.gnome.org (when a tarball has been released).
--
murrayc@...
www.murrayc.com
www.openismus.com
_______________________________________________
gnome-db-list mailing list
gnome-db-list@...
http://mail.gnome.org/mailman/listinfo/gnome-db-list
|
meta-store-case.tbz (3K) 