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

[Eric Prud'hommeaux]

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?

>                                                           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.

Attachment: [application/pgp-signature] Digital signature signature.asc