From: Rick James Date: April 5 2006 6:28pm Subject: RE: getting table meta data (primary key, in this case) List-Archive: http://lists.mysql.com/internals/33497 Message-Id: <0ae001c658de$cb3797c0$230110ac@ds.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 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 { > ?d . > > > > ?d ?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. >