Re: getting table meta data (primary key, in this case)

[Sergei Golubchik]

Hi!

On Apr 06, Eric Prud'hommeaux wrote:
> On Thu, Apr 06, 2006 at 10:24:32AM +0200, Sergei Golubchik wrote:
> > Hi!
> > 
> > On Apr 05, Eric Prud'hommeaux wrote:
> > > On Mon, Apr 03, 2006 at 02:53:08AM -0400, Eric Prud'hommeaux wrote:
> > > > On Mon, Apr 03, 2006 at 12:41:30AM -0400, SGreen@stripped wrote:
> > > > 
> > > > The problem is that I can't express higher-order-logic (symbols as
> > > > values) in relational calculus. That is, I can't express like
> > > > (inventing a $var notation here):
> > > >   SELECT Orders.$field FROM Orders WHERE $field=(
> > > >     SELECT Column_name SHOW INDEXES FROM Orders WHERE
> Key_name='PRIMARY')
> > > > I don't know if SQL limits itself to relational calculus, so perhaps
> > > > there is some magic to do this.
> > 
> > Not really :(
> > Not with one SQL query, at least.
> > But unless you limit yourself to rewriting-into-SQL approach, you should
> > not really care what SQL limits are.
> 
> true. this approach (finding the key and joining on that field) is
> limited only by the expressivity of the MySQL query structure.
> However, as far as I can see, I'd need to do something like:
>       Item* l = new Item_field(lex->current_context(), NullS,
>                                this_alias, primary_key_name);
>       Item* r = new Item_field(lex->current_context(), NullS,
>                                linked_alias, linked_foreign_key_name);
>       chooser_compare_func_creator eq = &comp_eq_creator;
>       Item* on = eq(0)->create(l, r);
>       add_join_on(table_list, on);
>
> and late bind primary_key_name. I don't think that's possible, at
> least with an Item_field. I'd make a Item_field_late_binding and add a
> schema table to the mix, but I can't see how I'd get select to call
> some bind method on it while executing.

It's possible.

First - one can write SELECT * FROM t1, t2, ...
and * will be expanded to a full list of fields after all tables are
opened.

Then - in fact all Item_field's always use this "late binding".
Before Item_field::fix_fields(), Item_field only knows its name,
sometimes database name and table name, but not always. And nothing
else. That's all that the parser can tell about the field.
For example:

  SELECT f1, t1.f2, db1.t2.f3 FROM t1, db1.t2, t3;

and only after all tables are opened, MySQL calls
Item_field::fix_fields() for all Item_field's, and it's the method where
"binding" happens - a field finds what table and database it belongs to,
finds all metadata about itself, data type, constrains, indexes, and so
on.

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Kerpen, Germany
       <___/  www.mysql.com