From: Date: April 3 2006 8:53am Subject: Re: getting table meta data (primary key, in this case) List-Archive: http://lists.mysql.com/internals/33494 Message-Id: <20060403065307.GM1596@w3.org> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="qYrsQHciA3Wqs7Iv" --qYrsQHciA3Wqs7Iv Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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, 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 (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=3DAddresses_0.id > > WHERE Orders_0.id=3D3183; > >=20 > > 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 > >=20 > > 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. > >=20 > > 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?) > >=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 INDEXES, or the=20 > 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=3DLex; lex->pop_context(); lex->current_select =3D 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=3D( SELECT Column_name SHOW INDEXES FROM Orders WHERE 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. 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 =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 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 ar= e=20 > not available until v5.0 What are IS views? > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine --=20 -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. --qYrsQHciA3Wqs7Iv Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iQEVAwUBRDDGU5ZX2p1ccTnpAQLf7gf+IUAdwYM9kXkK9nBBpb5uCJ+64mR2B1qh F8B156BuGoD8xeh+6+bCOa8bZlR/IKAMo5otNj6nym6FnNNC7ngqpi4cHH6y8lkJ m4Q3qWFp84kkFyxKk0VCZfeE0IcSk9G84ikVXhD4txdWdI/HfEtvMJ+2PUJmpjj8 OqKFcWhxkbN16HSXZAG4y2Gv2qTwB62TVwkHnZeNyTXLVznOsxt2AqpEeNBL6BSa yhUXkGyO81zMtoNvmI5/lSoh+zFqClJsdG/8kuKQqacrocLD/90jnenXygyk+sbT iEivMTHG/zAKQOvIMfDrOaiuWDyrt8gdfauMme0LEyitpV93N2wJ4A== =vtee -----END PGP SIGNATURE----- --qYrsQHciA3Wqs7Iv--