List:Internals« Previous MessageNext Message »
From:SGreen Date:April 3 2006 4:41am
Subject:Re: getting table meta data (primary key, in this case)
View as plain text  
/

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
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