diff options
| -rw-r--r-- | Docs/manual.texi | 246 | ||||
| -rw-r--r-- | mysql-test/r/handler.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/handler.test | 65 | ||||
| -rw-r--r-- | sql/Makefile.am | 2 | ||||
| -rw-r--r-- | sql/lex.h | 7 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 8 | ||||
| -rw-r--r-- | sql/sql_base.cc | 4 | ||||
| -rw-r--r-- | sql/sql_class.cc | 8 | ||||
| -rw-r--r-- | sql/sql_class.h | 6 | ||||
| -rw-r--r-- | sql/sql_handler.cc | 249 | ||||
| -rw-r--r-- | sql/sql_lex.h | 5 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 18 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 118 |
13 files changed, 543 insertions, 219 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 2b8a6c20001..62b9bdb2134 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -411,6 +411,7 @@ MySQL Language Reference * SET OPTION:: @code{SET OPTION} syntax * SET TRANSACTION:: @code{SET TRANSACTION} syntax * GRANT:: @code{GRANT} and @code{REVOKE} syntax +* HANDLER:: @code{HANDLER} syntax * CREATE INDEX:: @code{CREATE INDEX} syntax * DROP INDEX:: @code{DROP INDEX} syntax * Comments:: Comment syntax @@ -13653,6 +13654,7 @@ to restart @code{mysqld} with @code{--skip-grant-tables} to run * SET OPTION:: @code{SET OPTION} syntax * SET TRANSACTION:: @code{SET TRANSACTION} syntax * GRANT:: @code{GRANT} and @code{REVOKE} syntax +* HANDLER:: @code{HANDLER} syntax * CREATE INDEX:: @code{CREATE INDEX} syntax * DROP INDEX:: @code{DROP INDEX} syntax * Comments:: Comment syntax @@ -22615,7 +22617,7 @@ You can set the default isolation level for @code{mysqld} with @findex GRANT @findex REVOKE -@node GRANT, CREATE INDEX, SET TRANSACTION, Reference +@node GRANT, HANDLER, SET TRANSACTION, Reference @section @code{GRANT} and @code{REVOKE} Syntax @example @@ -22843,11 +22845,52 @@ dropped only with explicit @code{REVOKE} commands or by manipulating the @strong{MySQL} grant tables. @end itemize +@findex HANDLER +@node HANDLER, CREATE INDEX, GRANT, Reference +@section @code{HANDLER} Syntax + +@example +HANDLER table OPEN [ AS alias ] +HANDLER table READ index @{ = | >= | <= | < @} (value1, value2, ... ) [ WHERE ... ] [LIMIT ... ] +HANDLER table READ index @{ FIRST | NEXT | PREV | LAST @} [ WHERE ... ] [LIMIT ... ] +HANDLER table READ @{ FIRST | NEXT @} [ WHERE ... ] [LIMIT ... ] +HANDLER table CLOSE +@end example + +The @code{HANDLER} statement provides direct access to @strong{MySQL} table +interface, bypassing SQL optimizer. Thus, it is faster then SELECT. + +The first form of @code{HANDLER} statement opens a table, making +in accessible via the following @code{HANDLER ... READ} routines. + +The second form fetches one (or, specified by @code{LIMIT} clause) row +where the index specified complies to the condition and @code{WHERE} +condition is met. If the index consists of several parts (spans over +several columns) the values are specified in comma-separated list, +providing values only for few first columns is possible. + +The third form fetches one (or, specified by @code{LIMIT} clause) row +from the table in index order, matching @code{WHERE} condition. + +The fourth form (without index specification) fetches one (or, specified +by @code{LIMIT} clause) row from the table in natural row order (as stored +in data file) matching @code{WHERE} condition. It is faster than +@code{HANDLER table READ index} when full table scan is desired. + +The last form closes the table, opened with @code{HANDLER ... OPEN}. + +@code{HANDLER} is somewhat low-level statement, for example it does not +provide consistency. That is @code{HANDLER ... OPEN} does @strong{not} +takes a snapshot of the table, and does @strong{not} locks the table. The +above means, that after @code{HANDLER ... OPEN} table data can be +modified (by this or other thread) and these modifications may appear only +partially in @code{HANDLER ... NEXT} or @code{HANDLER ... PREV} scans. + @cindex indexes @cindex indexes, multi-part @cindex multi-part index @findex CREATE INDEX -@node CREATE INDEX, DROP INDEX, GRANT, Reference +@node CREATE INDEX, DROP INDEX, HANDLER, Reference @section @code{CREATE INDEX} Syntax @example @@ -40814,205 +40857,6 @@ started to read and apply updates from the master. @code{mysqladmin processlist} only shows the connection, @code{INSERT DELAYED}, and replication threads. -@cindex searching, full-text -@cindex full-text search -@cindex FULLTEXT -@node MySQL full-text search, MySQL test suite, MySQL threads, MySQL internals -@section MySQL Full-text Search - -Since Version 3.23.23, @strong{MySQL} has support for full-text indexing -and searching. Full-text indexes in @strong{MySQL} are an index of type -@code{FULLTEXT}. @code{FULLTEXT} indexes can be created from @code{VARCHAR} -and @code{TEXT} columns at @code{CREATE TABLE} time or added later with -@code{ALTER TABLE} or @code{CREATE INDEX}. For large datasets, adding -@code{FULLTEXT} index with @code{ALTER TABLE} (or @code{CREATE INDEX}) would -be much faster than inserting rows into the empty table with a @code{FULLTEXT} -index. - -Full-text search is performed with the @code{MATCH} function. - -@example -mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); -Query OK, 0 rows affected (0.00 sec) - -mysql> INSERT INTO t VALUES - -> ('MySQL has now support', 'for full-text search'), - -> ('Full-text indexes', 'are called collections'), - -> ('Only MyISAM tables','support collections'), - -> ('Function MATCH ... AGAINST()','is used to do a search'), - -> ('Full-text search in MySQL', 'implements vector space model'); -Query OK, 5 rows affected (0.00 sec) -Records: 5 Duplicates: 0 Warnings: 0 - -mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL'); -+---------------------------+-------------------------------+ -| a | b | -+---------------------------+-------------------------------+ -| MySQL has now support | for full-text search | -| Full-text search in MySQL | implements vector-space-model | -+---------------------------+-------------------------------+ -2 rows in set (0.00 sec) - -mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t; -+------------------------------+-------------------------------+--------+ -| a | b | x | -+------------------------------+-------------------------------+--------+ -| MySQL has now support | for full-text search | 0.3834 | -| Full-text indexes | are called collections | 0.3834 | -| Only MyISAM tables | support collections | 0.7668 | -| Function MATCH ... AGAINST() | is used to do a search | 0 | -| Full-text search in MySQL | implements vector space model | 0 | -+------------------------------+-------------------------------+--------+ -5 rows in set (0.00 sec) -@end example - -The function @code{MATCH} matches a natural language query @code{AGAINST} -a text collection (which is simply the columns that are covered by a -@strong{FULLTEXT} index). For every row in a table it returns relevance - -a similarity measure between the text in that row (in the columns that are -part of the collection) and the query. When it is used in a @code{WHERE} -clause (see example above) the rows returned are automatically sorted with -relevance decreasing. Relevance is a non-negative floating-point number. -Zero relevance means no similarity. Relevance is computed based on the -number of words in the row, the number of unique words in that row, the -total number of words in the collection, and the number of documents (rows) -that contain a particular word. - -MySQL uses a very simple parser to split text into words. A ``word'' is -any sequence of letters, numbers, @samp{'}, and @samp{_}. Any ``word'' -that is present in the stopword list or just too short (3 characters -or less) is ignored. - -Every correct word in the collection and in the query is weighted, -according to its significance in the query or collection. This way, a -word that is present in many documents will have lower weight (and may -even have a zero weight), because it has lower semantic value in this -particular collection. Otherwise, if the word is rare, it will receive a -higher weight. The weights of the words are then combined to compute the -relevance of the row. - -Such a technique works best with large collections (in fact, it was -carefully tuned this way). For very small tables, word distribution -does not reflect adequately their semantical value, and this model -may sometimes produce bizarre results. - -For example, search for the word "search" will produce no results in the -above example. Word "search" is present in more than half of rows, and -as such, is effectively treated as a stopword (that is, with semantical value -zero). It is, really, the desired behavior - a natural language query -should not return every other row in 1GB table. - -A word that matches half of rows in a table is less likely to locate relevant -documents. In fact, it will most likely find plenty of irrelevant documents. -We all know this happens far too often when we are trying to find something on -the Internet with a search engine. It is with this reasoning that such rows -have been assigned a low semantical value in @strong{a particular dataset}. - -@menu -* Fulltext Fine-tuning:: -* Fulltext features to appear in MySQL 4.0:: -* Fulltext TODO:: -@end menu - -@node Fulltext Fine-tuning, Fulltext features to appear in MySQL 4.0, MySQL full-text search, MySQL full-text search -@subsection Fine-tuning MySQL Full-text Search - -Unfortunately, full-text search has no user-tunable parameters yet, -although adding some is very high on the TODO. However, if you have a -@strong{MySQL} source distribution (@xref{Installing source}.), you can -somewhat alter the full-text search behavior. - -Note that full-text search was carefully tuned for the best searching -effectiveness. Modifying the default behavior will, in most cases, -only make the search results worse. Do not alter the @strong{MySQL} sources -unless you know what you are doing! - -@itemize - -@item -Minimal length of word to be indexed is defined in -@code{myisam/ftdefs.h} file by the line -@example -#define MIN_WORD_LEN 4 -@end example -Change it to the value you prefer, recompile @strong{MySQL}, and rebuild -your @code{FULLTEXT} indexes. - -@item -The stopword list is defined in @code{myisam/ft_static.c} -Modify it to your taste, recompile @strong{MySQL} and rebuild -your @code{FULLTEXT} indexes. - -@item -The 50% threshold is caused by the particular weighting scheme chosen. To -disable it, change the following line in @code{myisam/ftdefs.h}: -@example -#define GWS_IN_USE GWS_PROB -@end example -to -@example -#define GWS_IN_USE GWS_FREQ -@end example -and recompile @strong{MySQL}. -There is no need to rebuild the indexes in this case. - -@end itemize - -@node Fulltext features to appear in MySQL 4.0, Fulltext TODO, Fulltext Fine-tuning, MySQL full-text search -@subsection New Features of Full-text Search to Appear in MySQL 4.0 - -This section includes a list of the fulltext features that are already -implemented in the 4.0 tree. It explains -@strong{More functions for full-text search} entry of @ref{TODO MySQL 4.0}. - -@itemize @bullet -@item @code{REPAIR TABLE} with @code{FULLTEXT} indexes, -@code{ALTER TABLE} with @code{FULLTEXT} indexes, and -@code{OPTIMIZE TABLE} with @code{FULLTEXT} indexes are now -up to 100 times faster. - -@item @code{MATCH ... AGAINST} now supports the following -@strong{boolean operators}: - -@itemize @bullet -@item @code{+}word means the that word @strong{must} be present in every -row returned. -@item @code{-}word means the that word @strong{must not} be present in every -row returned. -@item @code{<} and @code{>} can be used to decrease and increase word -weight in the query. -@item @code{~} can be used to assign a @strong{negative} weight to a noise -word. -@item @code{*} is a truncation operator. -@end itemize - -Boolean search utilizes a more simplistic way of calculating the relevance, -that does not have a 50% threshold. - -@item Searches are now up to 2 times faster due to optimized search algorithm. - -@item Utility program @code{ft_dump} added for low-level @code{FULLTEXT} -index operations (querying/dumping/statistics). - -@end itemize - -@node Fulltext TODO, , Fulltext features to appear in MySQL 4.0, MySQL full-text search -@subsection Full-text Search TODO - -@itemize @bullet -@item Make all operations with @code{FULLTEXT} index @strong{faster}. -@item Support for braces @code{()} in boolean fulltext search. -@item Support for "always-index words". They could be any strings -the user wants to treat as words, examples are "C++", "AS/400", "TCP/IP", etc. -@item Support for fulltext search in @code{MERGE} tables. -@item Support for multi-byte charsets. -@item Make stopword list to depend of the language of the data. -@item Stemming (dependent of the language of the data, of course). -@item Generic user-suppied UDF (?) preparser. -@item Make the model more flexible (by adding some adjustable -parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). -@end itemize - @cindex mysqltest, MySQL Test Suite @cindex testing mysqld, mysqltest @node MySQL test suite, , MySQL threads, MySQL internals diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result new file mode 100644 index 00000000000..b13199ba96d --- /dev/null +++ b/mysql-test/r/handler.result @@ -0,0 +1,26 @@ +a b +14 aaa +a b +15 bbb +a b +16 ccc +a b +15 bbb +a b +22 iii +a b +21 hhh +a b +20 ggg +a b +14 aaa +a b +a b +22 iii +a b +21 hhh +a b +22 iii +a b +a b +15 bbb diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test new file mode 100644 index 00000000000..a22e5546252 --- /dev/null +++ b/mysql-test/t/handler.test @@ -0,0 +1,65 @@ +# +# test of HANDLER ... +# + +drop table if exists t1; +create table t1 (a int, b char(10), key a(a), key b(a,b)); +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +handler t2 read a next; +handler t2 read a next; +handler t2 read a prev; +handler t2 read a last; +handler t2 read a prev; +handler t2 read a prev; + +handler t2 read a first; +handler t2 read a prev; + +handler t2 read a last; +handler t2 read a prev; +handler t2 read a next; +handler t2 read a next; + +handler t2 read a=(15); +handler t2 read a=(16); + +!$1070 handler t2 read a=(19,"fff"); + +handler t2 read b=(19,"fff"); +handler t2 read b=(19,"yyy"); +handler t2 read b=(19); + +!$1109 handler t1 read a last; + +handler t2 read a=(11); +handler t2 read a>=(11); + +handler t2 read a=(18); +handler t2 read a>=(18); +handler t2 read a>(18); +handler t2 read a<=(18); +handler t2 read a<(18); + +handler t2 read a first limit 5; +handler t2 read a next limit 3; +handler t2 read a prev limit 10; + +handler t2 read a>=(16) limit 4; +handler t2 read a>=(16) limit 2,2; +handler t2 read a last limit 3; + +handler t2 read a=(19); +handler t2 read a=(19) where b="yyy"; + +handler t2 read first; +handler t2 read next; +handler t2 read next; +handler t2 read last; + +handler t2 close; +drop table if exists t1; diff --git a/sql/Makefile.am b/sql/Makefile.am index 4291c3f3c7b..2fc621090b3 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -56,7 +56,7 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ sql_select.h structs.h table.h sql_udf.h hash_filo.h\ lex.h lex_symbol.h sql_acl.h sql_crypt.h md5.h \ log_event.h mini_client.h sql_repl.h slave.h -mysqld_SOURCES = sql_lex.cc \ +mysqld_SOURCES = sql_lex.cc sql_handler.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ item_cmpfunc.cc item_strfunc.cc item_timefunc.cc \ thr_malloc.cc item_create.cc \ diff --git a/sql/lex.h b/sql/lex.h index f1f35ba3759..7f902787223 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -82,6 +82,7 @@ static SYMBOL symbols[] = { { "CHANGED", SYM(CHANGED),0,0}, { "CHECK", SYM(CHECK_SYM),0,0}, { "CHECKSUM", SYM(CHECKSUM_SYM),0,0}, + { "CLOSE", SYM(CLOSE_SYM),0,0}, { "COLUMN", SYM(COLUMN_SYM),0,0}, { "COLUMNS", SYM(COLUMNS),0,0}, { "COMMENT", SYM(COMMENT_SYM),0,0}, @@ -152,6 +153,7 @@ static SYMBOL symbols[] = { { "GRANTS", SYM(GRANTS),0,0}, { "GROUP", SYM(GROUP),0,0}, { "HAVING", SYM(HAVING),0,0}, + { "HANDLER", SYM(HANDLER_SYM),0,0}, { "HEAP", SYM(HEAP_SYM),0,0}, { "HIGH_PRIORITY", SYM(HIGH_PRIORITY),0,0}, { "HOUR", SYM(HOUR_SYM),0,0}, @@ -185,6 +187,7 @@ static SYMBOL symbols[] = { { "KEY", SYM(KEY_SYM),0,0}, { "KEYS", SYM(KEYS),0,0}, { "KILL", SYM(KILL_SYM),0,0}, + { "LAST", SYM(LAST_SYM),0,0}, { "LAST_INSERT_ID", SYM(LAST_INSERT_ID),0,0}, { "LEADING", SYM(LEADING),0,0}, { "LEFT", SYM(LEFT),0,0}, @@ -226,11 +229,12 @@ static SYMBOL symbols[] = { { "MYISAM", SYM(MYISAM_SYM),0,0}, { "NATURAL", SYM(NATURAL),0,0}, { "NATIONAL", SYM(NATIONAL_SYM),0,0}, + { "NEXT", SYM(NEXT_SYM),0,0}, { "NCHAR", SYM(NCHAR_SYM),0,0}, - { "NUMERIC", SYM(NUMERIC_SYM),0,0}, { "NO", SYM(NO_SYM),0,0}, { "NOT", SYM(NOT),0,0}, { "NULL", SYM(NULL_SYM),0,0}, + { "NUMERIC", SYM(NUMERIC_SYM),0,0}, { "ON", SYM(ON),0,0}, { "OPEN", SYM(OPEN_SYM),0,0}, { "OPTIMIZE", SYM(OPTIMIZE),0,0}, @@ -245,6 +249,7 @@ static SYMBOL symbols[] = { { "PASSWORD", SYM(PASSWORD),0,0}, { "PURGE", SYM(PURGE),0,0}, { "PRECISION", SYM(PRECISION),0,0}, + { "PREV", SYM(PREV_SYM),0,0}, { "PRIMARY", SYM(PRIMARY_SYM),0,0}, { "PROCEDURE", SYM(PROCEDURE),0,0}, { "PROCESS" , SYM(PROCESS),0,0}, diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 7a6d95facd3..31293951a90 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -385,6 +385,12 @@ int mysqld_show_status(THD *thd); int mysqld_show_variables(THD *thd,const char *wild); int mysqld_show(THD *thd, const char *wild, show_var_st *variables); +/* sql_handler.cc */ +int mysql_ha_open(THD *thd, TABLE_LIST *tables); +int mysql_ha_close(THD *thd, TABLE_LIST *tables); +int mysql_ha_read(THD *, TABLE_LIST *,enum enum_ha_read_modes,char *, + List<Item> *,enum ha_rkey_function,Item *,ha_rows,ha_rows); + /* sql_base.cc */ void set_item_name(Item *item,char *pos,uint length); bool add_field_to_list(char *field_name, enum enum_field_types type, @@ -406,6 +412,8 @@ TABLE *unlink_open_table(THD *thd,TABLE *list,TABLE *find); SQL_SELECT *make_select(TABLE *head, table_map const_tables, table_map read_tables, COND *conds, int *error); Item ** find_item_in_list(Item *item,List<Item> &items); +bool insert_fields(THD *thd,TABLE_LIST *tables, const char *table_name, + List_iterator<Item> *it); bool setup_tables(TABLE_LIST *tables); int setup_fields(THD *thd,TABLE_LIST *tables,List<Item> &item, bool set_query_id,List<Item> *sum_func_list); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c4798ac9d31..8980684dc66 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -34,8 +34,6 @@ HASH open_cache; /* Used by mysql_test */ static int open_unireg_entry(THD *thd,TABLE *entry,const char *db, const char *name, const char *alias, bool locked); -static bool insert_fields(THD *thd,TABLE_LIST *tables, const char *table_name, - List_iterator<Item> *it); static void free_cache_entry(TABLE *entry); static void mysql_rm_tmp_tables(void); static key_map get_key_map_from_key_list(TABLE *table, @@ -1815,7 +1813,7 @@ static key_map get_key_map_from_key_list(TABLE *table, ** Returns pointer to last inserted field if ok ****************************************************************************/ -static bool +bool insert_fields(THD *thd,TABLE_LIST *tables, const char *table_name, List_iterator<Item> *it) { diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 9ea3896fd78..ffcb15b4c9b 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -85,7 +85,8 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0), query_length=col_access=0; query_error=0; next_insert_id=last_insert_id=0; - open_tables=temporary_tables=0; + open_tables=temporary_tables=handler_tables=0; + handler_items=0; tmp_table=0; lock=locked_tables=0; used_tables=0; @@ -163,6 +164,11 @@ THD::~THD() lock=locked_tables; locked_tables=0; close_thread_tables(this); } + if (handler_tables) + { + open_tables=handler_tables; handler_tables=0; + close_thread_tables(this); + } close_temporary_tables(this); #ifdef USING_TRANSACTIONS if (opt_using_transactions) diff --git a/sql/sql_class.h b/sql/sql_class.h index ef6dc97cae6..fdfa6ec411b 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -24,7 +24,7 @@ class Query_log_event; class Load_log_event; - +enum enum_ha_read_modes { RFIRST, RNEXT, RPREV, RLAST, RKEY }; enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_IGNORE }; enum enum_log_type { LOG_CLOSED, LOG_NORMAL, LOG_NEW, LOG_BIN }; @@ -231,7 +231,7 @@ public: const char *proc_info; uint client_capabilities,max_packet_length; uint master_access,db_access; - TABLE *open_tables,*temporary_tables; + TABLE *open_tables,*temporary_tables, *handler_tables; MYSQL_LOCK *lock,*locked_tables; ULL *ull; struct st_my_thread_var *mysys_var; @@ -253,7 +253,7 @@ public: #ifdef HAVE_GEMINI_DB struct st_gemini gemini; #endif - Item *free_list; + Item *free_list, *handler_items; CONVERT *convert_set; Field *dupp_field; #ifndef __WIN__ diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc new file mode 100644 index 00000000000..9dbfc6f8626 --- /dev/null +++ b/sql/sql_handler.cc @@ -0,0 +1,249 @@ +/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; either version 2 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ + + +/* HANDLER ... commands - direct access to ISAM */ + +#include <assert.h> +#include "mysql_priv.h" +#include "sql_select.h" + +/* TODO: + HANDLER blabla OPEN [ AS foobar ] [ (column-list) ] + + the most natural (easiest, fastest) way to do it is to + compute List<Item> field_list not in mysql_ha_read + but in mysql_ha_open, and then store it in TABLE structure. + + The problem here is that mysql_parse calls free_item to free all the + items allocated at the end of every query. The workaround would to + keep two item lists per THD - normal free_list and handler_items. + The second is to be freeed only on thread end. mysql_ha_open should + then do { handler_items=concat(handler_items, free_list); free_list=0; } + + But !!! do_cammand calls free_root at the end of every query and frees up + all the sql_alloc'ed memory. It's harder to work around... + */ + +#define HANDLER_TABLES_HACK(thd) { \ + TABLE *tmp=thd->open_tables; \ + thd->open_tables=thd->handler_tables; \ + thd->handler_tables=tmp; } + +static TABLE *find_table_by_name(THD *thd, char *db, char *table_name); + +int mysql_ha_open(THD *thd, TABLE_LIST *tables) +{ + HANDLER_TABLES_HACK(thd); + int err=open_tables(thd,tables); + HANDLER_TABLES_HACK(thd); + if (err) + return -1; + + send_ok(&thd->net); + return 0; +} + +int mysql_ha_close(THD *thd, TABLE_LIST *tables) +{ + /* Perhaps, we should close table here. + But it's not easy - *tables is a single-linked list, designed + to be closed at all once. + So, why bother ? All the tables will be closed at thread exit. + */ + send_ok(&thd->net); + return 0; +} + +static enum enum_ha_read_modes rkey_to_rnext[]= + { RNEXT, RNEXT, RPREV, RNEXT, RPREV, RNEXT, RPREV }; + +int mysql_ha_read(THD *thd, TABLE_LIST *tables, + enum enum_ha_read_modes mode, char *keyname, List<Item> *key_expr, + enum ha_rkey_function ha_rkey_mode, Item *cond, + ha_rows select_limit,ha_rows offset_limit) +{ + int err, keyno=-1; + TABLE *table=find_table_by_name(thd, tables->db, tables->name); + if (!table) + { + my_printf_error(ER_UNKNOWN_TABLE,ER(ER_UNKNOWN_TABLE),MYF(0), + tables->name,"HANDLER"); + return -1; + } + tables->table=table; + + if (cond && cond->fix_fields(thd,tables)) + return -1; + + if (keyname) + { + if ((keyno=find_type(keyname, &table->keynames, 1+2)-1)<0) + { + my_printf_error(ER_KEY_DOES_NOT_EXITS,ER(ER_KEY_DOES_NOT_EXITS),MYF(0), + keyname,tables->name); + return -1; + } + } + + List<Item> list; + list.push_front(new Item_field(NULL,NULL,"*")); + List_iterator<Item> it(list); + it++; + + insert_fields(thd,tables,tables->name,&it); + + table->file->index_init(keyno); + + select_limit+=offset_limit; + send_fields(thd,list,1); + + MYSQL_LOCK *lock=mysql_lock_tables(thd,&tables->table,1); + + for (uint num_rows=0; num_rows < select_limit; ) + { + switch(mode) + { + case RFIRST: + err=keyname ? + table->file->index_first(table->record[0]) : + table->file->rnd_init(1) || + table->file->rnd_next(table->record[0]); + mode=RNEXT; + break; + case RLAST: + dbug_assert(keyname != 0); + err=table->file->index_last(table->record[0]); + mode=RPREV; + break; + case RNEXT: + err=keyname ? + table->file->index_next(table->record[0]) : + table->file->rnd_next(table->record[0]); + break; + case RPREV: + dbug_assert(keyname != 0); + err=table->file->index_prev(table->record[0]); + break; + case RKEY: + { + dbug_assert(keyname != 0); + KEY *keyinfo=table->key_info+keyno; + KEY_PART_INFO *key_part=keyinfo->key_part; + uint key_len; + byte *key; + if (key_expr->elements > keyinfo->key_parts) + { + my_printf_error(ER_TOO_MANY_KEY_PARTS,ER(ER_TOO_MANY_KEY_PARTS), + MYF(0),keyinfo->key_parts); + goto err; + } + List_iterator<Item> it_ke(*key_expr); + Item *item; + for (key_len=0 ; (item=it_ke++) ; key_part++) + { + item->save_in_field(key_part->field); + key_len+=key_part->store_length; + } + if (!(key=sql_calloc(ALIGN_SIZE(key_len)))) + { + send_error(&thd->net,ER_OUTOFMEMORY); + goto err; + } + key_copy(key, table, keyno, key_len); + err=table->file->index_read(table->record[0], + key,key_len,ha_rkey_mode); + mode=rkey_to_rnext[(int)ha_rkey_mode]; + break; + } + default: + send_error(&thd->net,ER_ILLEGAL_HA); + goto err; + } + + if (err) + { + if (err != HA_ERR_KEY_NOT_FOUND && err != HA_ERR_END_OF_FILE) + { + sql_print_error("mysql_ha_read: Got error %d when reading table", + err); + table->file->print_error(err,MYF(0)); + goto err; + } + goto ok; + } + if (cond) + { + err=err; + if(!cond->val_int()) + continue; + } + if (num_rows>=offset_limit) + { + if (!err) + { + String *packet = &thd->packet; + Item *item; + packet->length(0); + it.rewind(); + while ((item=it++)) + { + if (item->send(packet)) + { + packet->free(); // Free used + my_error(ER_OUT_OF_RESOURCES,MYF(0)); + goto err; + } + } + my_net_write(&thd->net, (char*)packet->ptr(), packet->length()); + } + } + num_rows++; + } +ok: + mysql_unlock_tables(thd,lock); + send_eof(&thd->net); + return 0; +err: + mysql_unlock_tables(thd,lock); + return -1; +} + +/************************************************************************** + 2Monty: It could easily happen, that the following service functions are + already defined somewhere in the code, but I failed to find them. + If this is the case, just say a word and I'll use old functions here. +**************************************************************************/ + +/* Note: this function differs from find_locked_table() because we're looking + here for alias, not real table name + */ +static TABLE *find_table_by_name(THD *thd, char *db, char *table_name) +{ + int dblen; + + if (!db || ! *db) db=thd->db; + if (!db || ! *db) db=""; + dblen=strlen(db); + + for (TABLE *table=thd->handler_tables; table ; table=table->next) + { + if (!memcmp(table->table_cache_key, db, dblen) && + !my_strcasecmp(table->table_name,table_name)) + return table; + } + return(0); +} diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 10a99f920bd..f505b870d67 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -53,7 +53,8 @@ enum enum_sql_command { SQLCOM_BEGIN, SQLCOM_LOAD_MASTER_TABLE, SQLCOM_CHANGE_MASTER, SQLCOM_RENAME_TABLE, SQLCOM_BACKUP_TABLE, SQLCOM_RESTORE_TABLE, SQLCOM_RESET, SQLCOM_PURGE, SQLCOM_SHOW_BINLOGS, - SQLCOM_SHOW_OPEN_TABLES + SQLCOM_SHOW_OPEN_TABLES, + SQLCOM_HA_OPEN, SQLCOM_HA_CLOSE, SQLCOM_HA_READ }; enum lex_states { STATE_START, STATE_CHAR, STATE_IDENT, @@ -141,6 +142,8 @@ typedef struct st_lex { enum lex_states next_state; enum enum_duplicates duplicates; enum enum_tx_isolation tx_isolation; + enum enum_ha_read_modes ha_read_mode; + enum ha_rkey_function ha_rkey_mode; uint in_sum_expr,grant,grant_tot_col,which_columns, sort_default; thr_lock_type lock_option; bool create_refs,drop_primary,drop_if_exists,local_file; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index de32d090048..1dc6b22c6ac 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2008,6 +2008,24 @@ mysql_execute_command(void) res = mysql_show_grants(thd,lex->grant_user); } break; + case SQLCOM_HA_OPEN: + if (check_db_used(thd,tables) || check_table_access(thd,SELECT_ACL, tables)) + goto error; + res = mysql_ha_open(thd, tables); + break; + case SQLCOM_HA_CLOSE: + if (check_db_used(thd,tables)) + goto error; + res = mysql_ha_close(thd, tables); + break; + case SQLCOM_HA_READ: + if (check_db_used(thd,tables) || check_table_access(thd,SELECT_ACL, tables)) + goto error; + res = mysql_ha_read(thd, tables, lex->ha_read_mode, lex->backup_dir, + lex->insert_list, lex->ha_rkey_mode, lex->where, + lex->select_limit, lex->offset_limit); + break; + case SQLCOM_BEGIN: if (end_active_trans(thd)) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 29f7bf73ec4..ef271bb888e 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -110,6 +110,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token CHECKSUM_SYM %token CHECK_SYM %token COALESCE +%token CLOSE_SYM %token COLUMNS %token COLUMN_SYM %token COMMENT_SYM @@ -193,6 +194,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token GROUP_UNIQUE_USERS %token GT_SYM %token HAVING +%token HANDLER_SYM %token HEAP_SYM %token HEX_NUM %token HIGH_PRIORITY @@ -222,6 +224,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token KEY_SYM %token KILL_SYM %token LAST_INSERT_ID +%token LAST_SYM %token LE %token LEADING %token LEAST_SYM @@ -271,6 +274,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token NATURAL %token NCHAR_SYM %token NE +%token NEXT_SYM %token NOT %token NOW_SYM %token NO_SYM @@ -292,6 +296,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token PASSWORD %token POSITION_SYM %token PRECISION +%token PREV_SYM %token PRIMARY_SYM %token PRIVILEGES %token PROCEDURE @@ -403,6 +408,43 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); %token USE_SYM %token USING %token VALUES +%token VARIABLES +%token WHERE +%token WITH +%token WRITE_SYM +%token COMPRESSED_SYM + +%token BIGINT +%token BLOB_SYM +%token CHAR_SYM +%token CHANGED +%token DATETIME +%token DATE_SYM +%token DECIMAL_SYM +%token DOUBLE_SYM +%token ENUM +%token FAST_SYM +%token FLOAT_SYM +%token INT_SYM +%token LONGBLOB +%token LONGTEXT +%token MEDIUMBLOB +%token MEDIUMINT +%token MEDIUMTEXT +%token NUMERIC_SYM +%token PRECISION +%token QUICK +%token REAL +%token SMALLINT +%token STRING_SYM +%token TEXT_SYM +%token TIMESTAMP +%token TIME_SYM +%token TINYBLOB +%token TINYINT +%token TINYTEXT +%token UNSIGNED +>>>>>>> BitKeeper/tmp/sql_yacc.yy_serg@1.85 %token VARBINARY %token VARCHAR %token VARIABLES @@ -527,13 +569,13 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); select_item_list select_item values_list no_braces limit_clause delete_limit_clause fields opt_values values procedure_list procedure_list2 procedure_item - when_list2 expr_list2 + when_list2 expr_list2 handler opt_precision opt_ignore opt_column opt_restrict grant revoke set lock unlock string_list field_options field_option field_opt_list opt_binary table_lock_list table_lock varchar references opt_on_delete opt_on_delete_list opt_on_delete_item use opt_delete_options opt_delete_option - opt_outer table_list table opt_option opt_place opt_low_priority + opt_outer table_list table_name opt_option opt_place opt_low_priority opt_attribute opt_attribute_list attribute column_list column_list_id opt_column_list grant_privileges opt_table user_list grant_option grant_privilege grant_privilege_list @@ -541,7 +583,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize); equal optional_braces opt_key_definition key_usage_list2 opt_mi_check_type opt_to mi_check_types normal_join table_to_table_list table_to_table opt_table_list opt_as - END_OF_INPUT + handler_rkey_function handler_rkey_mode handler_read_or_scan + END_OF_INPUT %type <NONE> '-' '+' '*' '/' '%' '(' ')' @@ -590,6 +633,7 @@ verb_clause: | slave | show | truncate + | handler | unlock | update | use @@ -1910,7 +1954,8 @@ order_dir: limit_clause: /* empty */ { - Lex->select_limit= current_thd->default_select_limit; + Lex->select_limit= (Lex->sql_command == SQLCOM_HA_READ) ? + 1 : current_thd->default_select_limit; Lex->offset_limit= 0L; } | LIMIT ULONG_NUM @@ -2015,10 +2060,10 @@ drop: table_list: - table - | table_list ',' table + table_name + | table_list ',' table_name -table: +table_name: table_ident { if (!add_table_to_list($1,NULL,1)) YYABORT; } @@ -2051,7 +2096,7 @@ insert2: | insert_table {} insert_table: - table + table_name { Lex->field_list.empty(); Lex->many_values.empty(); @@ -2550,6 +2595,7 @@ keyword: | CHANGED {} | CHECKSUM_SYM {} | CHECK_SYM {} + | CLOSE_SYM {} | COMMENT_SYM {} | COMMIT_SYM {} | COMMITTED_SYM {} @@ -2575,12 +2621,14 @@ keyword: | GEMINI_SYM {} | GLOBAL_SYM {} | HEAP_SYM {} + | HANDLER_SYM {} | HOSTS_SYM {} | HOUR_SYM {} | IDENTIFIED_SYM {} | ISOLATION {} | ISAM_SYM {} | INNOBASE_SYM {} + | LAST_SYM {} | LEVEL_SYM {} | LOCAL_SYM {} | LOGS_SYM {} @@ -2603,10 +2651,12 @@ keyword: | MYISAM_SYM {} | NATIONAL_SYM {} | NCHAR_SYM {} + | NEXT_SYM {} | NO_SYM {} | OPEN_SYM {} | PACK_KEYS_SYM {} | PASSWORD {} + | PREV_SYM {} | PROCESS {} | PROCESSLIST_SYM {} | QUICK {} @@ -2863,6 +2913,58 @@ unlock: UNLOCK_SYM table_or_tables { Lex->sql_command=SQLCOM_UNLOCK_TABLES; } +/* +** Handler: direct access to ISAM functions +*/ + +handler: + HANDLER_SYM table_ident OPEN_SYM opt_table_alias + { + Lex->sql_command = SQLCOM_HA_OPEN; + if (!add_table_to_list($2,$4,0)) + YYABORT; + } + | HANDLER_SYM table_ident CLOSE_SYM + { + Lex->sql_command = SQLCOM_HA_CLOSE; + if (!add_table_to_list($2,0,0)) + YYABORT; + } + | HANDLER_SYM table_ident READ_SYM handler_read_or_scan + { + Lex->sql_command = SQLCOM_HA_READ; + if (!add_table_to_list($2,0,0)) + YYABORT; + } + where_clause limit_clause { } + +handler_read_or_scan: + handler_scan_function { Lex->backup_dir= 0; } + | ident handler_rkey_function { Lex->backup_dir= $1.str; } + +handler_scan_function: + FIRST_SYM { Lex->ha_read_mode = RFIRST; } + | NEXT_SYM { Lex->ha_read_mode = RNEXT; } + +handler_rkey_function: + FIRST_SYM { Lex->ha_read_mode = RFIRST; } + | NEXT_SYM { Lex->ha_read_mode = RNEXT; } + | PREV_SYM { Lex->ha_read_mode = RPREV; } + | LAST_SYM { Lex->ha_read_mode = RLAST; } + | handler_rkey_mode + { + Lex->ha_read_mode = RKEY; + if (!(Lex->insert_list = new List_item)) + YYABORT; + } '(' values ')' { } + +handler_rkey_mode: + EQ { Lex->ha_rkey_mode=HA_READ_KEY_EXACT; } + | GE { Lex->ha_rkey_mode=HA_READ_KEY_OR_NEXT; } + | LE { Lex->ha_rkey_mode=HA_READ_KEY_OR_PREV; } + | GT_SYM { Lex->ha_rkey_mode=HA_READ_AFTER_KEY; } + | LT { Lex->ha_rkey_mode=HA_READ_BEFORE_KEY; } + /* GRANT / REVOKE */ revoke: |