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

[Stewart Smith]

On Wed, 2006-04-05 at 11:28 -0700, Rick James wrote:
> 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

but you'd query INFORMATION_SCHEMA in 5.0 and above
> 
> 
> > -----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.
> > 
> 
> 
-- 
Stewart Smith (stewart@stripped)
http://www.flamingspork.com/


Attachment: [application/pgp-signature] This is a digitally signed message part signature.asc