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

[Sergei Golubchik]

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.

> > Also, playing around a bit, I didn't
> > see how to project from a SHOW command. "SELECT Column_name SHOW
> > INDEXES ..." and "SELECT Column_name FROM SHOW INDEXES ..." violate
> > the grammar.

Assuming you use at least 5.0, you write instead

  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS

> > Ultimately, it seems the best thing to do is to get make_schema_select
> > to fabricate a query and execute it in an entirely separate query. My
> > SPARQL parser is called from mysql_parse:
> >     if (!strncmp(inBuf, "SPARQL:", 7)) {
> >       lex->ptr = (uchar*)inBuf+7;
> >       sparqlFrob frob(thd, (char**)&lex->ptr);
> >       parse_res = frob.parse();
> >     } else {
> >       parse_res = yyparse((void *)thd);
> >     }
> > This, apart from the Makefile, is the only place where I inject my
> > code into the MySQL source. Perhaps I should inject query code before
> > the threads split (ick -- slows down startup) or use shmem to allow
> > the threads to share the metadata they extract. Does MySQL have some
> > sort of shared memory between threads?
> 
> I poked around some more in sql_show.cc and sql_base.cc . It seems I
> can grab key info at query parsing time by calling TABLE *open_table(...).
> Any advice of the difficulty of that?

Not prohibitively difficult. But, again, the better approach would be to
do this after the parsing.
 
> It appears that the thread keeps a list of open tables. Is it safe to
> open one, knowing that the query will also open it, and let the thread
> handle the redundant open gracefully? Is this at all like locking? Do
> I need to make sure I close the table again before execting the SELECT?

Not really safe. That is opening the _same_ table could be (I don't
remember), but in general you should open all tables at once. You cannot
open a few tables, and later decide to open some more.

That means - if you open a table before executing the SELECT, you need
to close it again.
 
> For a frame of reference, SPASQL keeps does foreign key - primary key
> joins for you automagically. I need to know the primary keys to be
> able to build the join structure.

Right, but you don't have to do it _in the parser_.

Regards,
Sergei

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