List:Internals« Previous MessageNext Message »
From:Eric Prud'hommeaux Date:April 6 2006 12:19pm
Subject:Re: getting table meta data (primary key, in this case)
View as plain text  
On Thu, Apr 06, 2006 at 10:24:32AM +0200, Sergei Golubchik wrote:
> Hi!
> 
> On Apr 05, Eric Prud'hommeaux wrote:
> > 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:
> > > 
> > > 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.
> 
> Not really :(
> Not with one SQL query, at least.
> But unless you limit yourself to rewriting-into-SQL approach, you should
> not really care what SQL limits are.

true. this approach (finding the key and joining on that field) is
limited only by the expressivity of the MySQL query structure.
However, as far as I can see, I'd need to do something like:
      Item* l = new Item_field(lex->current_context(), NullS, this_alias,
primary_key_name);
      Item* r = new Item_field(lex->current_context(), NullS, linked_alias,
linked_foreign_key_name);
      chooser_compare_func_creator eq = &comp_eq_creator;
      Item* on = eq(0)->create(l, r);
      add_join_on(table_list, on);
and late bind primary_key_name. I don't think that's possible, at
least with an Item_field. I'd make a Item_field_late_binding and add a
schema table to the mix, but I can't see how I'd get select to call
some bind method on it while executing.


> > > 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.
> 
> Assuming you use at least 5.0, you write instead
> 
>   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS

Ahh, neet.
My function knows the table name and can discover (I believe) the
database name, and assumes only one field in the primary key:
  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS
   WHERE INDEX_NAME='PRIMARY'
     AND INDEX_SCHEMA='OrderTracking'
     AND TABLE_NAME='Orders'
     AND SEQ_IN_INDEX=1;
This gives me projection, but I still can't take that result (say, 1
row with a value of ('Id')) and use it to say Orders_0.id=Customers.order
*in the same query*.

> > > 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 TABLE *open_table(...).
> > Any advice of the difficulty of that?
> 
> Not prohibitively difficult. But, again, the better approach would be to
> do this after the parsing.

So if mysqld allocates a thread and calls my parser, I guess I could
  do a first pass to get all the primary key tables that I will need
  create a and execute *new* thread to get all the relevent names
  generate my query structure (2nd pass or stored compile tree)
  return (and let mysqld execute the query structure)
I wonder if thread construction/tear down is relatively simple. My
primary goal here is implementability. Moving all this server-side
already gave me significant performance increase and I can eek out
the rest later on.

> > 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?
> 
> Not really safe. That is opening the _same_ table could be (I don't
> remember), but in general you should open all tables at once. You cannot
> open a few tables, and later decide to open some more.
> 
> That means - if you open a table before executing the SELECT, you need
> to close it again.

sql_base.cc close_old_data_files and close_tables_for_reopen seem like
contender for this opperation. If I keep the schema table out of the
thread list, close_thread_tables would be inappropriate. Perhaps I am
living so dangerously as to need to use intern_close_table.

> > 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.
> 
> Right, but you don't have to do it _in the parser_.

I haven't played with what's available at initialization, but I guess
I could fabricate a thread to look at all the tables' primary keys and
cache that info (hoping it didn't change without a server restart).

Alternatively, I could make the person or a maintenance app write it
down in a conf file, but that seems dangerously redundant.


I'm keen on advice and experience here. I assume noone has ever added
a parser to mysql for a language that does primary key-foreign key
joins implicitly, but isn't that kinda cool? If mysql hackers want
their name on a paper at XTech, they can join me in this venture. (And
beer, I offer lots of beer. Those geographically near Paris can
collect that beer immediately.)
-- 
-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: [application/pgp-signature] Digital signature signature.asc
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