List:Internals« Previous MessageNext Message »
From:Eric Prud'hommeaux Date:April 5 2006 2:15pm
Subject:Re: getting table meta data (primary key, in this case)
View as plain text  
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.

Attachment: [application/pgp-signature] Digital signature signature.asc
Thread
getting table meta data (primary key, in this case)Eric Prud'hommeaux1 Apr
  • Re: getting table meta data (primary key, in this case)SGreen3 Apr
    • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux3 Apr
      • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux5 Apr
        • RE: getting table meta data (primary key, in this case)Rick James5 Apr
          • RE: getting table meta data (primary key, in this case)Stewart Smith6 Apr
          • RE: getting table meta data (primary key, in this case)Stewart Smith6 Apr
        • Re: getting table meta data (primary key, in this case)Sergei Golubchik6 Apr
          • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux6 Apr
            • Re: getting table meta data (primary key, in this case)Sergei Golubchik10 Apr
              • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux15 Apr
                • Re: getting table meta data (primary key, in this case)Sergei Golubchik18 Apr
                • Re: getting table meta data (primary key, in this case)Sanja Byelkin18 Apr
  • Re: getting table meta data (primary key, in this case)Sergei Golubchik3 Apr
Re: getting table meta data (primary key, in this case)Sergei Golubchik10 Apr
  • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux10 Apr
    • Re: getting table meta data (primary key, in this case)Eric Prud'hommeaux11 Apr