From: Stewart Smith Date: April 6 2006 4:02am Subject: RE: getting table meta data (primary key, in this case) List-Archive: http://lists.mysql.com/internals/33498 Message-Id: <1144296164.30369.12.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-lMk7CbMxM9yCuERzLN1u" --=-lMk7CbMxM9yCuERzLN1u Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2006-04-05 at 11:28 -0700, Rick James wrote: > If I could use Perl, I would do... >=20 > 1. Get output from "SHOW CREATE TABLE foo"=20 > 2. foreach line: > 2a. if ($lin =3D~ m{^\s*PRIMARY KEY.*\((.*)\)}) > commalist of keys in the PK is in $1 > 2b. if ($lin =3D~ 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 >=20 >=20 > > -----Original Message----- > > From: Eric Prud'hommeaux [mailto:eric@stripped]=20 > > 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) > >=20 > > 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: > > > > / > > > >=20 > > > > Eric Prud'hommeaux wrote on 04/01/2006 09:59:00 AM: > > > >=20 > > > > > How can I, during query parsing on the server,=20 > > efficiently find what > > > > > field is labeled as a primary key for a given table? > > > > >=20 > > > > > Why: > > > > > SPARQL is a graph-based query language and doesn't=20 > > (ideally) worry > > > > > about how the links in the graph are constructed. The=20 > > obvious mapping > > > > > for the SPARQL query > > > > > SELECT ?apt WHERE { =20 > > ?d . > > > > > ?d ?apt }; > > > > > is > > > > > SELECT Addresses_0.apt > > > > > FROM Orders AS Orders_0 > > > > > JOIN Addresses as Addresses_0 > > > > > ON Orders_0.shippingAddress=3DAddresses_0.id > > > > > WHERE Orders_0.id=3D3183; > > > > >=20 > > > > > The missing piece of info is that the=20 > > Orders.shippingAddress is a > > > > > foreign key for Addresses.id . For simplicity on the=20 > > 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=20 > > identifier > > > > >=20 > > > > > Encountering ?d a second time in the query allows me to=20 > > infer that > > > > > Orders.shippingAddress is a foreign key to Addresses. I=20 > > just need to > > > > > pull "id" out of the air, or better yet, out of the=20 > > table meta data. > > > > >=20 > > > > > I have only seen this done when the client asks the=20 > > question, which it > > > > > does with a particular query. I'm hoping not to have to=20 > > do a query > > > > > while parsing, but will if I have to. I currently share no info > > > > > between queries. (how do I do that, anyways?) > > > > >=20 > > > > > 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/ > > > >=20 > > > > Use one of the commands SHOW CREATE TABLE or SHOW=20 > > INDEXES, or the=20 > > > > INFORMATION SCHEMA views to get at the metadata you seek. > > >=20 > > > I walked around in sql_yacc.yy to translate that to=20 > > something I can do > > > on the server side at request time: > > >=20 > > > subselect_start: > > > '(' SELECT_SYM > > > { > > > ... > > > mysql_new_select(Lex, 1); > > > } > > > subselect_end: > > > ')' > > > { > > > LEX *lex=3DLex; > > > lex->pop_context(); > > > lex->current_select =3D=20 > > lex->current_select->return_after_parsing(); > > > }; > > >=20 > > > Queries like SHOW and DESCRIBE appear to create tables like > > > "/tmp/#sql_7bae_0". Any pointers to how that stuff works=20 > > would save me > > > a lot of head-scratching time. > > >=20 > > > 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=3D( > > > SELECT Column_name SHOW INDEXES FROM Orders WHERE=20 > > Key_name=3D'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. > > >=20 > > > Ultimately, it seems the best thing to do is to get=20 > > make_schema_select > > > to fabricate a query and execute it in an entirely separate=20 > > query. My > > > SPARQL parser is called from mysql_parse: > > > if (!strncmp(inBuf, "SPARQL:", 7)) { > > > lex->ptr =3D (uchar*)inBuf+7; > > > sparqlFrob frob(thd, (char**)&lex->ptr); > > > parse_res =3D frob.parse(); > > > } else { > > > parse_res =3D 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=20 > > 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? > >=20 > > 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=20 > > *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? > >=20 > > 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=20 > > the SELECT? > >=20 > > 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. > >=20 > >=20 > > > > =20 > > The IS views are=20 > > > > not available until v5.0 > > >=20 > > > What are IS views? > > >=20 > > > > Shawn Green > > > > Database Administrator > > > > Unimin Corporation - Spruce Pine > >=20 > >=20 > >=20 > > --=20 > > -eric > >=20 > > 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 > >=20 > > (eric@stripped) > > Feel free to forward this message to any list for any purpose=20 > > other than > > email address distribution. > >=20 >=20 >=20 --=20 Stewart Smith (stewart@stripped) http://www.flamingspork.com/ --=-lMk7CbMxM9yCuERzLN1u Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQBENJLjKglWCUL+FDoRArl4AJ4xP0kCTx0AD2hO7eTPJOt45W4z1QCcCo/r C0N91EhriPvD+yqFoFbjBz8= =HLq0 -----END PGP SIGNATURE----- --=-lMk7CbMxM9yCuERzLN1u--