Re: [gnome-db] parameterized queries

[Neil Zanella <nzanella gmail com>]

On Wed, 10 Nov 2004 10:43:36 +0100, Vivien Malerba <vmalerba gmail com> wrote:

> > 1. Encode your database schema as XML.
> 
> This is done.
> 
> > 2. Encode your query as XML.
> 
> This is almost done; it needs some documentation, though.
> 
> > 3. Then, you can use a simpler syntax: where id = $myparam.
> >
> > How?
> >
> > Cause you look up id in the XML query, you find it is coming from table Foo,
> > so you look up the XML in table foo, to find out that it's of type int4, and can
> > thus figure out the type of myparam. Right? And just use hash function
> > lookups to speed things up.
> 
> What if the parameter is for something like "... WHERE my_defined_func
> ($myparam)...", how can you guess the data type?

You use XML to add a function declaration for my_defined_func

<function name="my_defined_func"/>
  <parameter type="foo"/>
  <parameter type="bar"/>
  <returnvalue type="foobar"/>
</function>

Cause I wanna specify what that function returns once, just like in any
language, not 100 times for each of the 100 times I call it.

> You could use the
> functions into the data dictionnary, and then it's ok, but what if the
> parameter is used at several places in the query where some implicit
> casts must be made? I think to avoid problems, then the data type must
> be specified (of course we could add some "guesses" for simple cases
> like "id = $myparam" to make it easier for the user, so the ##[...]
> syntax can't be avoided.
> 
> This syntax also allows you to set a default value for the parameter,
> such as "..=1234[:name="myparam"]", the type is automatically found in
> this case.

OK, we could the default value thing is a feature. Why do you need the : and the
double # though?

> > > context = mg_entity_get_exec_context (MG_ENTITY (query));
> > > param = context->parameters->data; /* first and only parameter */
> > > myval = gda_value_new_integer (1234);
> > > mg_parameter_set_value (param, myval);
> > > gda_value_free (myval);
> > > str = mg_renderer_render_as_sql (MG_RENDERER (query), context, 0, NULL);
> > > /* use str */
> > > g_free (str);
> > > g_object_unref (context);
> > > g_object_unref (query);
> > > g_object_unref (the_dictionnary);
> >
> > Cool, but there seem to be lots of fucnction calls and stuff not clear
> > to me right
> > now (renderer/dictionary/etc...). I'll have to check the API.
> 
> Basically the dictionnary (MgDict) is an in-memory representation of
> all the database structures, data types, etc and queries.

Nice.

> The MgEntity is an interface for anything that looks like a tabular,
> to be able to get the columns, etc in a consistent way, and also the
> execution parameters.

OK. Basically, something like an SQL table correct?

> The MgRenderer is an interface to "render" an object into SQL, or into
> other things like XML queries, when implemented.

OK, it's neat.

So how is this going to be added to libgda? You change Mg to Gda? How is this
all going to fit in with the Gda framework (i.e. aren't there going to
be some clashes
with dattypes, functions, etc...). Please let us know what the plans are.

> > > Check the docs for more options and explanations about the different objects.
> >
> > Thanks, I'll have a look.
> >
> > Anyways, I'd be relly happy if you could get that GDA parameter stuff to work:
> > the libgda parameters don't work since 2002 as some google search reveals,
> > and the libmg stuff is going to be merged with libgda so will be gone, and
> > doesn't seem to do the automatic type lookup I describe, so I think I'll
> > have to code yet another set of functions to do what I want myself.
> > I don't know, maybe I'm coming from a different point of view.
> > Please consider the idea of having automatic type lookup for
> > variables and the possibilities associated with allowing apps
> > to read XML encoded schemas at runtime and define
> > variables that reference SQL fields within XML files
> > cause apps can then really build on these ideas.
> >
> > GdaVariable var = the_one_I_should_use_in_table_for_field("TableX", "fieldFoo");
> >
> > Imagine how simple: then I can change my DB database types without changing the
> > application code if the application code is designed properly...
> >
> 
> If I understand what you want to do in your application is:
> -> load an XML file which ayou have written

Yes.

> -> use a query (with or without parameters) defined in that file to
> make some queries to the database to which you are connected

Yes.

> -> AND/OR use grids, forms, etc for the same purpose

No, I've decupled widgets from data extraction: the right thing
to do: never stick the model and the view of MVC without having
the C in between (well, never say never). But whenever a widget
is coupled with data in a predefined way I lose control over how
things work. I'll never do that ever again.

> you can already do _all_ of this with libmergeant (I do it at work),
> this is why I have written libmergeant for. I'm now merging all that
> work into libgnomedb (and probably libgda will also be modified for
> coherence later).

Well, I want to build my application on top of both libmergeant and glade.
So mine sits on top of the two. Easy: I connect things up in an XML file
which is flexible enough to accomodate various "models of behavior".
Then, in the ideal scenario, my library looks up widgets in the glade
file, encodes queries in the mergeant compatible XML Queries, and
looks up that the fields exist etc... (proper validation), and goes on
to specify the "models of interaction between data and wigets" (
which can be much more complex than just execute this query
and stick that value in that table: there is much more to it: I've
developed a very high level behavioral modeling file format).

If you can document some of the libmergeant stuff it would be
great. Also, I still get that segfault upon connecting to my
PostgreSQL DB on Fedora Core 2. I've been checking out
of CVS but no fix yet. If you have access to FC2 try it and
you will likely get the same result, otherwise there may be
an uninitialized block of memory somewhere, not sure tho.

Regards,

Neil

> 
> Regards,
> 
> Vivien
>