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

[SGreen]

/

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/
> -- 
> -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 "signature.asc" deleted by Shawn Green/Unimin] 

Use one of the commands  SHOW CREATE TABLE or SHOW INDEXES, or the 
INFORMATION SCHEMA views to get at the metadata you seek. The IS views are 
not available until v5.0

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine