From 13b7bcd5d44d037984abfd0dd2acf8f1ce54b161 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 9 Apr 2013 16:17:16 +0200 Subject: sequence engine --- storage/sequence/CMakeLists.txt | 1 + storage/sequence/mysql-test/sequence/inc.inc | 4 + storage/sequence/mysql-test/sequence/inc.opt | 1 + storage/sequence/mysql-test/sequence/simple.result | 270 ++++++++++++++++ storage/sequence/mysql-test/sequence/simple.test | 93 ++++++ storage/sequence/sequence.cc | 343 +++++++++++++++++++++ 6 files changed, 712 insertions(+) create mode 100644 storage/sequence/CMakeLists.txt create mode 100644 storage/sequence/mysql-test/sequence/inc.inc create mode 100644 storage/sequence/mysql-test/sequence/inc.opt create mode 100644 storage/sequence/mysql-test/sequence/simple.result create mode 100644 storage/sequence/mysql-test/sequence/simple.test create mode 100644 storage/sequence/sequence.cc (limited to 'storage/sequence') diff --git a/storage/sequence/CMakeLists.txt b/storage/sequence/CMakeLists.txt new file mode 100644 index 00000000000..9a68901520c --- /dev/null +++ b/storage/sequence/CMakeLists.txt @@ -0,0 +1 @@ +MYSQL_ADD_PLUGIN(sequence sequence.cc STORAGE_ENGINE) diff --git a/storage/sequence/mysql-test/sequence/inc.inc b/storage/sequence/mysql-test/sequence/inc.inc new file mode 100644 index 00000000000..702e90b2890 --- /dev/null +++ b/storage/sequence/mysql-test/sequence/inc.inc @@ -0,0 +1,4 @@ +if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'sequence' AND support='YES'`) +{ + --skip Test requires sequence engine +} diff --git a/storage/sequence/mysql-test/sequence/inc.opt b/storage/sequence/mysql-test/sequence/inc.opt new file mode 100644 index 00000000000..ad178af67c2 --- /dev/null +++ b/storage/sequence/mysql-test/sequence/inc.opt @@ -0,0 +1 @@ +--plugin-load=$HA_SEQUENCE_SO diff --git a/storage/sequence/mysql-test/sequence/simple.result b/storage/sequence/mysql-test/sequence/simple.result new file mode 100644 index 00000000000..102f17498fe --- /dev/null +++ b/storage/sequence/mysql-test/sequence/simple.result @@ -0,0 +1,270 @@ +select * from information_schema.engines where engine='sequence'; +ENGINE SEQUENCE +SUPPORT YES +COMMENT Generated tables filled with sequential values +TRANSACTIONS YES +XA YES +SAVEPOINTS YES +set sql_quote_show_create=0; +show create table seq_1_to_15_step_2; +Table Create Table +seq_1_to_15_step_2 CREATE TABLE seq_1_to_15_step_2 ( + seq bigint(20) unsigned NOT NULL, + PRIMARY KEY (seq) +) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 +show create table seq_1_to_15_step; +ERROR 42S02: Table 'test.seq_1_to_15_step' doesn't exist +show create table seq_1_to_15_st; +ERROR 42S02: Table 'test.seq_1_to_15_st' doesn't exist +show create table seq_1_to_15; +Table Create Table +seq_1_to_15 CREATE TABLE seq_1_to_15 ( + seq bigint(20) unsigned NOT NULL, + PRIMARY KEY (seq) +) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 +show create table seq_1_to_1; +Table Create Table +seq_1_to_1 CREATE TABLE seq_1_to_1 ( + seq bigint(20) unsigned NOT NULL, + PRIMARY KEY (seq) +) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 +show create table seq_1_to_; +ERROR 42S02: Table 'test.seq_1_to_' doesn't exist +show create table seq_1_t; +ERROR 42S02: Table 'test.seq_1_t' doesn't exist +show create table seq_1; +ERROR 42S02: Table 'test.seq_1' doesn't exist +show create table seq_; +ERROR 42S02: Table 'test.seq_' doesn't exist +show create table se; +ERROR 42S02: Table 'test.se' doesn't exist +show create table seq_1_to_15_step_0; +ERROR HY000: Got error 140 "Wrong create options" from storage engine +select * from seq_1_to_15_step_2; +seq +1 +3 +5 +7 +9 +11 +13 +15 +select * from seq_1_to_15; +seq +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +select * from seq_1_to_1; +seq +1 +select * from seq_15_to_1; +seq +15 +14 +13 +12 +11 +10 +9 +8 +7 +6 +5 +4 +3 +2 +1 +select * from seq_15_to_1_step_2; +seq +15 +13 +11 +9 +7 +5 +3 +1 +select * from seq_1_to_15_step_12345; +seq +1 +select * from seq_15_to_1_step_12345; +seq +15 +explain select * from seq_15_to_1_step_12345; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_15_to_1_step_12345 ALL NULL NULL NULL NULL 1 +show open tables from test; +Database Table In_use Name_locked +test seq_15_to_1 0 0 +test seq_15_to_1_step_12345 0 0 +test seq_15_to_1_step_2 0 0 +test seq_1_to_1 0 0 +test seq_1_to_15 0 0 +test seq_1_to_15_step_12345 0 0 +test seq_1_to_15_step_2 0 0 +show tables; +Tables_in_test +explain select * from seq_1_to_15_step_2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index +explain select * from seq_1_to_15_step_2 where seq > 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 6 Using where; Using index +explain select * from seq_1_to_15_step_2 where seq between 4 and 9; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 3 Using where; Using index +explain select * from seq_1_to_15_step_2 where seq between 20 and 30; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain select * from seq_1_to_15_step_2 where seq between 4 and 6; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index +explain select * from seq_1_to_15_step_2 where seq between 4 and 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index +explain select * from seq_1_to_15_step_2 where seq between 4 and 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain select * from seq_1_to_15_step_2 where seq between 5 and 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE seq_1_to_15_step_2 const PRIMARY PRIMARY 8 const 1 Using index +create table t1 (a int, aa int, b varchar(100)); +insert t1 select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_20; +select * from t1; +a aa b +1 1 odd +2 4 even +3 9 odd +4 16 even +5 25 odd +6 36 even +7 49 odd +8 64 even +9 81 odd +10 100 even +11 121 odd +12 144 even +13 169 odd +14 196 even +15 225 odd +16 256 even +17 289 odd +18 324 even +19 361 odd +20 400 even +select aa, b from t1, seq_1_to_20_step_3 as seq where a=seq; +aa b +1 odd +16 even +49 odd +100 even +169 odd +256 even +361 odd +insert t1 +select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_30 +where seq > (select max(a) from t1); +select * from t1; +a aa b +1 1 odd +2 4 even +3 9 odd +4 16 even +5 25 odd +6 36 even +7 49 odd +8 64 even +9 81 odd +10 100 even +11 121 odd +12 144 even +13 169 odd +14 196 even +15 225 odd +16 256 even +17 289 odd +18 324 even +19 361 odd +20 400 even +21 441 odd +22 484 even +23 529 odd +24 576 even +25 625 odd +26 676 even +27 729 odd +28 784 even +29 841 odd +30 900 even +drop table t1; +select seq from seq_2_to_50 s1 where 0 not in +(select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); +seq +2 +3 +5 +7 +11 +13 +17 +19 +23 +29 +31 +37 +41 +43 +47 +explain select seq from seq_2_to_50 s1 where 0 not in +(select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY s1 index NULL PRIMARY 8 NULL 49 Using where; Using index +2 DEPENDENT SUBQUERY s2 index PRIMARY PRIMARY 8 NULL 49 Using where; Using index +select year(dt) from +(select '1901-02-28' + interval seq year as dt from seq_0_to_99) as seqdt +where weekday(dt) = 0; +year(dt) +1910 +1916 +1921 +1927 +1938 +1944 +1949 +1955 +1966 +1972 +1977 +1983 +1994 +2000 +create table t1 (a int) engine=innodb; +reset master; +start transaction; +insert t1 select * from seq_1_to_10; +savepoint s1; +insert t1 select * from seq_11_to_20; +rollback to savepoint s1; +commit; +select count(*) from t1; +count(*) +10 +show binlog events limit 2,10; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 286 Query 1 354 BEGIN +master-bin.000001 354 Query 1 452 use test; insert t1 select * from seq_1_to_10 +master-bin.000001 452 Xid 1 479 COMMIT /* xid */ +drop table t1; diff --git a/storage/sequence/mysql-test/sequence/simple.test b/storage/sequence/mysql-test/sequence/simple.test new file mode 100644 index 00000000000..9559e42fafd --- /dev/null +++ b/storage/sequence/mysql-test/sequence/simple.test @@ -0,0 +1,93 @@ +--source inc.inc +--source include/have_xtradb.inc +--source include/have_binlog_format_statement.inc + +--query_vertical select * from information_schema.engines where engine='sequence' + +set sql_quote_show_create=0; + +show create table seq_1_to_15_step_2; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_15_step; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_15_st; +show create table seq_1_to_15; +show create table seq_1_to_1; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_; +--error ER_NO_SUCH_TABLE +show create table seq_1_t; +--error ER_NO_SUCH_TABLE +show create table seq_1; +--error ER_NO_SUCH_TABLE +show create table seq_; +--error ER_NO_SUCH_TABLE +show create table se; +--error ER_GET_ERRNO +show create table seq_1_to_15_step_0; + +# simple select +select * from seq_1_to_15_step_2; +select * from seq_1_to_15; +select * from seq_1_to_1; +# backwards +select * from seq_15_to_1; +select * from seq_15_to_1_step_2; + +# step > |to - from| +select * from seq_1_to_15_step_12345; +select * from seq_15_to_1_step_12345; +explain select * from seq_15_to_1_step_12345; + +--sorted_result +show open tables from test; +show tables; +# row estimates +explain select * from seq_1_to_15_step_2; +explain select * from seq_1_to_15_step_2 where seq > 4; +explain select * from seq_1_to_15_step_2 where seq between 4 and 9; +explain select * from seq_1_to_15_step_2 where seq between 20 and 30; +explain select * from seq_1_to_15_step_2 where seq between 4 and 6; +explain select * from seq_1_to_15_step_2 where seq between 4 and 5; +explain select * from seq_1_to_15_step_2 where seq between 4 and 4; +explain select * from seq_1_to_15_step_2 where seq between 5 and 5; + +# join +create table t1 (a int, aa int, b varchar(100)); +insert t1 select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_20; +select * from t1; +select aa, b from t1, seq_1_to_20_step_3 as seq where a=seq; +# adding more rows, example +insert t1 + select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_30 + where seq > (select max(a) from t1); +select * from t1; +drop table t1; + +# Prime Numbers from 2 to 50 :) +select seq from seq_2_to_50 s1 where 0 not in + (select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); +explain select seq from seq_2_to_50 s1 where 0 not in + (select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); + +# Years of XX-th century where 28th of February was Monday +select year(dt) from + (select '1901-02-28' + interval seq year as dt from seq_0_to_99) as seqdt + where weekday(dt) = 0; + +# transactions and XA +create table t1 (a int) engine=innodb; +reset master; +start transaction; +# No warning about "accesses nontransactional table" +insert t1 select * from seq_1_to_10; +savepoint s1; +insert t1 select * from seq_11_to_20; +rollback to savepoint s1; +commit; +select count(*) from t1; +# must show Xid event +--replace_regex /xid=[0-9]+/xid/ +show binlog events limit 2,10; +drop table t1; + diff --git a/storage/sequence/sequence.cc b/storage/sequence/sequence.cc new file mode 100644 index 00000000000..62915f68484 --- /dev/null +++ b/storage/sequence/sequence.cc @@ -0,0 +1,343 @@ +/* + Copyright (c) 2013 Monty Program 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; version 2 of + the License. + + 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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA +*/ + +/* + a engine that auto-creates tables with rows filled with sequential values +*/ + +#include +#include +#include +#include + +typedef struct st_share { + const char *name; + THR_LOCK lock; + uint use_count; + struct st_share *next; + + ulonglong from, to, step; + bool reverse; +} SHARE; + +class ha_seq: public handler +{ +private: + THR_LOCK_DATA lock; + SHARE *seqs; + ulonglong cur; + +public: + ha_seq(handlerton *hton, TABLE_SHARE *table_arg) + : handler(hton, table_arg), seqs(0) { } + ulonglong table_flags() const { return 0; } + + /* open/close/locking */ + int create(const char *name, TABLE *table_arg, + HA_CREATE_INFO *create_info) { return HA_ERR_WRONG_COMMAND; } + + int open(const char *name, int mode, uint test_if_locked); + int close(void); + THR_LOCK_DATA **store_lock(THD *, THR_LOCK_DATA **, enum thr_lock_type); + + /* table scan */ + int rnd_init(bool scan); + int rnd_next(unsigned char *buf); + void position(const uchar *record); + int rnd_pos(uchar *buf, uchar *pos); + int info(uint flag); + + /* indexes */ + ulong index_flags(uint inx, uint part, bool all_parts) const + { return HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER | + HA_READ_RANGE | HA_KEYREAD_ONLY; } + uint max_supported_keys() const { return 1; } + int index_read_map(uchar *buf, const uchar *key, key_part_map keypart_map, + enum ha_rkey_function find_flag); + int index_next(uchar *buf); + int index_prev(uchar *buf); + int index_first(uchar *buf); + int index_last(uchar *buf); + ha_rows records_in_range(uint inx, key_range *min_key, + key_range *max_key); + + double scan_time() { return nvalues(); } + double read_time(uint index, uint ranges, ha_rows rows) { return rows; } + double keyread_time(uint index, uint ranges, ha_rows rows) { return rows; } + +private: + void set(uchar *buf); + ulonglong nvalues() { return (seqs->to - seqs->from)/seqs->step; } +}; + +THR_LOCK_DATA **ha_seq::store_lock(THD *thd, THR_LOCK_DATA **to, + enum thr_lock_type lock_type) +{ + if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK) + lock.type= TL_WRITE_ALLOW_WRITE; + *to ++= &lock; + return to; +} + +void ha_seq::set(unsigned char *buf) +{ + my_bitmap_map *old_map = dbug_tmp_use_all_columns(table, table->write_set); + my_ptrdiff_t offset = (my_ptrdiff_t) (buf - table->record[0]); + Field *field = table->field[0]; + field->move_field_offset(offset); + field->store(cur, true); + field->move_field_offset(-offset); + dbug_tmp_restore_column_map(table->write_set, old_map); +} + +int ha_seq::rnd_init(bool scan) +{ + cur= seqs->reverse ? seqs->to : seqs->from; + return 0; +} + +int ha_seq::rnd_next(unsigned char *buf) +{ + if (seqs->reverse) + return index_prev(buf); + else + return index_next(buf); +} + +void ha_seq::position(const uchar *record) +{ + *(ulonglong*)ref= cur; +} + +int ha_seq::rnd_pos(uchar *buf, uchar *pos) +{ + cur= *(ulonglong*)pos; + return rnd_next(buf); +} + +int ha_seq::info(uint flag) +{ + if (flag & HA_STATUS_VARIABLE) + stats.records = nvalues(); + return 0; +} + +int ha_seq::index_read_map(uchar *buf, const uchar *key_arg, + key_part_map keypart_map, + enum ha_rkey_function find_flag) +{ + ulonglong key= uint8korr(key_arg); + switch (find_flag) { + case HA_READ_AFTER_KEY: + key++; + // fall through + case HA_READ_KEY_OR_NEXT: + if (key <= seqs->from) + cur= seqs->from; + else + { + cur= (key - seqs->from + seqs->step - 1) / seqs->step * seqs->step + seqs->from; + if (cur >= seqs->to) + return HA_ERR_KEY_NOT_FOUND; + } + return index_next(buf); + + case HA_READ_KEY_EXACT: + if ((key - seqs->from) % seqs->step != 0 || key < seqs->from || key >= seqs->to) + return HA_ERR_KEY_NOT_FOUND; + cur= key; + return index_next(buf); + + case HA_READ_BEFORE_KEY: + key--; + // fall through + case HA_READ_PREFIX_LAST_OR_PREV: + if (key >= seqs->to) + cur= seqs->to; + else + { + if (key < seqs->from) + return HA_ERR_KEY_NOT_FOUND; + cur= (key - seqs->from) / seqs->step * seqs->step + seqs->from; + } + return index_prev(buf); + default: return HA_ERR_WRONG_COMMAND; + } +} + + +int ha_seq::index_next(uchar *buf) +{ + if (cur == seqs->to) + return HA_ERR_END_OF_FILE; + set(buf); + cur+= seqs->step; + return 0; +} + + +int ha_seq::index_prev(uchar *buf) +{ + if (cur == seqs->from) + return HA_ERR_END_OF_FILE; + cur-= seqs->step; + set(buf); + return 0; +} + + +int ha_seq::index_first(uchar *buf) +{ + cur= seqs->from; + return index_next(buf); +} + + +int ha_seq::index_last(uchar *buf) +{ + cur= seqs->to; + return index_prev(buf); +} + +ha_rows ha_seq::records_in_range(uint inx, key_range *min_key, + key_range *max_key) +{ + ulonglong kmin= min_key ? uint8korr(min_key->key) : seqs->from; + ulonglong kmax= max_key ? uint8korr(max_key->key) : seqs->to - 1; + if (kmin >= seqs->to || kmax < seqs->from || kmin > kmax) + return 0; + return (kmax - seqs->from) / seqs->step - + (kmin - seqs->from + seqs->step - 1) / seqs->step + 1; +} + + +int ha_seq::open(const char *name, int mode, uint test_if_locked) +{ + mysql_mutex_lock(&table->s->LOCK_ha_data); + seqs= (SHARE*)table->s->ha_data; + DBUG_ASSERT(my_strcasecmp(table_alias_charset, name, seqs->name) == 0); + if (seqs->use_count++ == 0) + thr_lock_init(&seqs->lock); + mysql_mutex_unlock(&table->s->LOCK_ha_data); + + ref_length= sizeof(cur); + thr_lock_data_init(&seqs->lock,&lock,NULL); + return 0; +} + +int ha_seq::close(void) +{ + mysql_mutex_lock(&table->s->LOCK_ha_data); + if (--seqs->use_count == 0) + thr_lock_delete(&seqs->lock); + mysql_mutex_unlock(&table->s->LOCK_ha_data); + return 0; +} + +static handler *create_handler(handlerton *hton, TABLE_SHARE *table, + MEM_ROOT *mem_root) +{ + return new (mem_root) ha_seq(hton, table); +} + +static int discover_table(handlerton *hton, THD *thd, TABLE_SHARE *share) +{ + // the table is discovered if it has the pattern of seq_1_to_10 or + // seq_1_to_10_step_3 + ulonglong from, to, step= 1; + uint n1= 0, n2= 0; + bool reverse; + sscanf(share->table_name.str, "seq_%llu_to_%llu%n_step_%llu%n", + &from, &to, &n1, &step, &n2); + if (n1 != share->table_name.length && n2 != share->table_name.length) + return HA_ERR_NO_SUCH_TABLE; + + if (step == 0) + return HA_WRONG_CREATE_OPTION; + + const char *sql="create table seq (seq bigint unsigned primary key)"; + int res= share->init_from_sql_statement_string(thd, 0, sql, strlen(sql)); + if (res) + return res; + + if ((reverse = from > to)) + { + if (step > from - to) + to = from; + else + swap_variables(ulonglong, from, to); + /* + when keyread is allowed, optimizer will always prefer an index to a + table scan for our tables, and we'll never see the range reversed. + */ + share->keys_for_keyread.clear_all(); + } + + to= (to - from) / step * step + step + from; + + SHARE *seqs= (SHARE*)alloc_root(&share->mem_root, sizeof(*seqs)); + bzero(seqs, sizeof(*seqs)); + seqs->name = share->normalized_path.str; + seqs->from= from; + seqs->to= to; + seqs->step= step; + seqs->reverse= reverse; + + share->ha_data = seqs; + return 0; +} + + +static int dummy_ret_int() { return 0; } + +static int init(void *p) +{ + handlerton *hton = (handlerton *)p; + hton->create = create_handler; + hton->discover_table = discover_table; + hton->discover_table_existence = + (int (*)(handlerton *, const char *, const char *)) &dummy_ret_int; + hton->commit= hton->rollback= hton->prepare= + (int (*)(handlerton *, THD *, bool)) &dummy_ret_int; + hton->savepoint_set= hton->savepoint_rollback= hton->savepoint_release= + (int (*)(handlerton *, THD *, void *)) &dummy_ret_int; + + return 0; +} + +static struct st_mysql_storage_engine descriptor = +{ MYSQL_HANDLERTON_INTERFACE_VERSION }; + +maria_declare_plugin(seq) +{ + MYSQL_STORAGE_ENGINE_PLUGIN, + &descriptor, + "SEQUENCE", + "Sergei Golubchik", + "Generated tables filled with sequential values", + PLUGIN_LICENSE_GPL, + init, + NULL, + 0x0100, + NULL, + NULL, + "0.1", + MariaDB_PLUGIN_MATURITY_EXPERIMENTAL +} +maria_declare_plugin_end; + -- cgit v1.2.1