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

[Rick James]

If I could use Perl, I would do...

1. Get output from "SHOW CREATE TABLE foo" 
2. foreach line:
2a.  if ($lin =~ m{^\s*PRIMARY KEY.*\((.*)\)})
             commalist of keys in the PK is in $1
2b.  if ($lin =~ m{^\s*UNIQUE.*\((.*)\)})
     AND you don't already have the PK,
             commalist of keys in the PK is in $1


> -----Original Message-----
> From: Eric Prud'hommeaux [mailto:eric@stripped] 
> Sent: Wednesday, April 05, 2006 7:15 AM
> To: SGreen@stripped
> Cc: internals@stripped
> Subject: Re: getting table meta data (primary key, in this case)
> 
> 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:
> > > /
> > > 
> > > Eric Prud'hommeaux <eric@stripped> wrote on 04/01/2006 09:59:00 AM:
> > > 
> > > > How can I, during query parsing on the server, 
> efficiently find what
> > > > field is labeled as a primary key for a given table?
> > > > 
> > > > Why:
> > > > SPARQL is a graph-based query language and doesn't 
> (ideally) worry
> > > > about how the links in the graph are constructed. The 
> obvious mapping
> > > > for the SPARQL query
> > > >   SELECT ?apt WHERE { <Orders.id=2186> 
> <Orders.shippingAddress> ?d .
> > > >             ?d               <Addresses.apt>          ?apt };
> > > > is
> > > >   SELECT Addresses_0.apt
> > > >     FROM Orders AS Orders_0
> > > >     JOIN Addresses as Addresses_0
> > > >          ON Orders_0.shippingAddress=Addresses_0.id
> > > >    WHERE Orders_0.id=3183;
> > > > 
> > > > The missing piece of info is that the 
> Orders.shippingAddress is a
> > > > foreign key for Addresses.id . For simplicity on the 
> first round, I'm
> > > > assuming:
> > > >   1 tuple identifiers are "primary keys" and are one field wide
> > > >   2 tuple references are "foreign keys" and are one field wide
> > > >   3 all tuple references to a table reference the same 
> identifier
> > > > 
> > > > Encountering ?d a second time in the query allows me to 
> infer that
> > > > Orders.shippingAddress is a foreign key to Addresses. I 
> just need to
> > > > pull "id" out of the air, or better yet, out of the 
> table meta data.
> > > > 
> > > > I have only seen this done when the client asks the 
> question, which it
> > > > does with a particular query. I'm hoping not to have to 
> do a query
> > > > while parsing, but will if I have to. I currently share no info
> > > > between queries. (how do I do that, anyways?)
> > > > 
> > > > For more info on this, see
> > > >   http://www.w3.org/2005/05/22-SPARQL-MySQL/
> > > >   http://www.w3.org/2003/01/21-RDF-RDB-access/
> > > 
> > > Use one of the commands  SHOW CREATE TABLE or SHOW 
> INDEXES, or the 
> > > INFORMATION SCHEMA views to get at the metadata you seek.
> > 
> > I walked around in sql_yacc.yy to translate that to 
> something I can do
> > on the server side at request time:
> > 
> > subselect_start:
> > 	'(' SELECT_SYM
> > 	{
> > 	  ...
> > 	  mysql_new_select(Lex, 1);
> > 	}
> > subselect_end:
> > 	')'
> > 	{
> > 	  LEX *lex=Lex;
> >           lex->pop_context();
> > 	  lex->current_select = 
> lex->current_select->return_after_parsing();
> > 	};
> > 
> > Queries like SHOW and DESCRIBE appear to create tables like
> > "/tmp/#sql_7bae_0". Any pointers to how that stuff works 
> would save me
> > a lot of head-scratching time.
> > 
> > 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. 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.
> > 
> > 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 ABLE 
> *open_table(...).
> Any advice of the difficulty of that? Would it be easier to examine
> the disk directly with a magnet and a magnifying glass?
> 
> 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?
> 
> 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.
> 
> 
> > >                                                           
> The IS views are 
> > > not available until v5.0
> > 
> > What are IS views?
> > 
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine
> 
> 
> 
> -- 
> -eric
> 
> office: +81.466.49.1170 W3C, Keio Research Institute at SFC,
>                         Shonan Fujisawa Campus, Keio University,
>                         5322 Endo, Fujisawa, Kanagawa 252-8520
>                         JAPAN
>         +1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
> cell:   +81.90.6533.3882
> 
> (eric@stripped)
> Feel free to forward this message to any list for any purpose 
> other than
> email address distribution.
>