null+****@clear*****
null+****@clear*****
2011年 10月 24日 (月) 19:33:51 JST
Kouhei Sutou 2011-10-24 10:33:51 +0000 (Mon, 24 Oct 2011)
New Revision: b08f05f4420e6a49e720fb5f3ccc54d1c3423683
Log:
[storage] support multi column index for fulltext search.
Added files:
test/sql/groonga_storage/r/fulltext_multiple_column_index_delete.result
test/sql/groonga_storage/r/fulltext_multiple_column_index_insert.result
test/sql/groonga_storage/r/fulltext_multiple_column_index_recreate.result
test/sql/groonga_storage/r/fulltext_multiple_column_index_update.result
test/sql/groonga_storage/t/fulltext_multiple_column_index_delete.test
test/sql/groonga_storage/t/fulltext_multiple_column_index_insert.test
test/sql/groonga_storage/t/fulltext_multiple_column_index_recreate.test
test/sql/groonga_storage/t/fulltext_multiple_column_index_update.test
Modified files:
ha_mroonga.cc
Modified: ha_mroonga.cc (+45 -18)
===================================================================
--- ha_mroonga.cc 2011-10-24 07:47:34 +0000 (dbf6245)
+++ ha_mroonga.cc 2011-10-24 10:33:51 +0000 (7fd2c59)
@@ -1898,6 +1898,8 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
int key_parts = key_info->key_parts;
grn_obj_flags index_table_flags = GRN_OBJ_PERSISTENT;
+ grn_obj_flags index_column_flags =
+ GRN_OBJ_COLUMN_INDEX | GRN_OBJ_WITH_POSITION | GRN_OBJ_PERSISTENT;
if (key_parts == 1) {
Field *field = key_info->key_part[0].field;
const char *column_name = field->field_name;
@@ -1912,14 +1914,15 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
int mysql_field_type = field->type();
grn_builtin_type groonga_type = mrn_get_type(ctx, mysql_field_type);
index_type = grn_ctx_at(ctx, groonga_type);
- index_table_flags |= GRN_OBJ_KEY_NORMALIZE;
} else {
index_type = grn_ctx_at(ctx, GRN_DB_SHORT_TEXT);
+ index_column_flags |= GRN_OBJ_WITH_SECTION;
}
int key_alg = key_info->algorithm;
- if (key_alg == HA_KEY_ALG_FULLTEXT) {
+ if (key_info->flags & HA_FULLTEXT) {
index_table_flags |= GRN_OBJ_TABLE_PAT_KEY;
+ index_table_flags |= GRN_OBJ_KEY_NORMALIZE;
} else if (key_alg == HA_KEY_ALG_HASH) {
index_table_flags |= GRN_OBJ_TABLE_HASH_KEY;
} else {
@@ -1932,7 +1935,7 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
NULL,
index_table_flags,
index_type,
- 0);
+ NULL);
if (ctx->rc) {
grn_obj_remove(ctx, grn_table);
error = ER_CANT_CREATE_TABLE;
@@ -1940,7 +1943,7 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
DBUG_RETURN(error);
}
- if (key_alg == HA_KEY_ALG_FULLTEXT) {
+ if (key_info->flags & HA_FULLTEXT) {
grn_info_type info_type = GRN_INFO_DEFAULT_TOKENIZER;
grn_obj *tokenizer = find_tokenizer(tmp_share->key_parser[i],
tmp_share->key_parser_length[i]);
@@ -1948,8 +1951,6 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
grn_obj_unlink(ctx, tokenizer);
}
- grn_obj_flags index_column_flags =
- GRN_OBJ_COLUMN_INDEX | GRN_OBJ_WITH_POSITION | GRN_OBJ_PERSISTENT;
index_column = grn_column_create(ctx,
index_table,
index_column_name,
@@ -1965,15 +1966,40 @@ int ha_mroonga::storage_create_index(TABLE *table, const char *grn_table_name,
DBUG_RETURN(error);
}
- if (column) {
- grn_obj source_ids;
- grn_id source_id = grn_obj_id(ctx, column);
- GRN_UINT32_INIT(&source_ids, GRN_OBJ_VECTOR);
- GRN_UINT32_PUT(ctx, &source_ids, source_id);
- grn_obj_set_info(ctx, index_column, GRN_INFO_SOURCE, &source_ids);
- grn_obj_unlink(ctx, &source_ids);
+ if (key_parts == 1) {
+ if (column) {
+ grn_obj source_ids;
+ grn_id source_id = grn_obj_id(ctx, column);
+ GRN_UINT32_INIT(&source_ids, GRN_OBJ_VECTOR);
+ GRN_UINT32_PUT(ctx, &source_ids, source_id);
+ grn_obj_set_info(ctx, index_column, GRN_INFO_SOURCE, &source_ids);
+ grn_obj_unlink(ctx, &source_ids);
+ }
+ } else {
+ if (key_info->flags & HA_FULLTEXT) {
+ grn_obj source_ids;
+ GRN_UINT32_INIT(&source_ids, GRN_OBJ_VECTOR);
+
+ int j, n_key_parts = key_info->key_parts;
+ for (j = 0; j < n_key_parts; j++) {
+ Field *field = key_info->key_part[j].field;
+ const char *column_name = field->field_name;
+ int column_name_size = strlen(column_name);
+ grn_obj *source_column = grn_obj_column(ctx, grn_table,
+ column_name, column_name_size);
+ grn_id source_id = grn_obj_id(ctx, source_column);
+ GRN_UINT32_PUT(ctx, &source_ids, source_id);
+ grn_obj_unlink(ctx, source_column);
+ }
+ grn_obj_set_info(ctx, index_column, GRN_INFO_SOURCE, &source_ids);
+ grn_obj_unlink(ctx, &source_ids);
+ }
}
+
index_tables[i] = index_table;
+ if (column) {
+ grn_obj_unlink(ctx, column);
+ }
DBUG_RETURN(error);
}
@@ -1992,8 +2018,9 @@ int ha_mroonga::storage_create_indexs(TABLE *table, const char *grn_table_name,
}
if ((error = storage_create_index(table, grn_table_name, grn_table,
tmp_share, &table->s->key_info[i],
- index_tables, i)))
- DBUG_RETURN(error);
+ index_tables, i))) {
+ break;
+ }
}
if (error) {
for (; i >= 0; i--) {
@@ -3520,7 +3547,7 @@ int ha_mroonga::storage_write_row_index(uchar *buf, grn_id record_id)
KEY key_info = table->key_info[i];
- if (key_info.key_parts == 1) {
+ if (key_info.key_parts == 1 || (key_info.flags & HA_FULLTEXT)) {
continue;
}
@@ -3869,7 +3896,7 @@ int ha_mroonga::storage_update_row_index(const uchar *old_data, uchar *new_data)
KEY key_info = table->key_info[i];
- if (key_info.key_parts == 1) {
+ if (key_info.key_parts == 1 || (key_info.flags & HA_FULLTEXT)) {
continue;
}
@@ -4073,7 +4100,7 @@ int ha_mroonga::storage_delete_row_index(const uchar *buf)
KEY key_info = table->key_info[i];
- if (key_info.key_parts == 1) {
+ if (key_info.key_parts == 1 || (key_info.flags & HA_FULLTEXT)) {
continue;
}
Added: test/sql/groonga_storage/r/fulltext_multiple_column_index_delete.result (+34 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/r/fulltext_multiple_column_index_delete.result 2011-10-24 10:33:51 +0000 (a5181d2)
@@ -0,0 +1,34 @@
+drop table if exists diaries;
+set names utf8;
+create table diaries (
+id int primary key,
+title varchar(255),
+content text,
+fulltext index (title, content),
+fulltext index (title),
+fulltext index (content)
+) default charset utf8;
+show create table diaries;
+Table Create Table
+diaries CREATE TABLE `diaries` (
+ `id` int(11) NOT NULL,
+ `title` varchar(255) DEFAULT NULL,
+ `content` text,
+ PRIMARY KEY (`id`),
+ FULLTEXT KEY `title` (`title`,`content`),
+ FULLTEXT KEY `title_2` (`title`),
+ FULLTEXT KEY `content` (`content`)
+) ENGINE=groonga DEFAULT CHARSET=utf8
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+delete from diaries where id = 2;
+select * from diaries where match(title, content) against("富士山");
+id title content
+3 富士山 今日もきれい。
+select * from diaries where match(title) against("富士山");
+id title content
+3 富士山 今日もきれい。
+select * from diaries where match(content) against("富士山");
+id title content
+drop table diaries;
Added: test/sql/groonga_storage/r/fulltext_multiple_column_index_insert.result (+40 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/r/fulltext_multiple_column_index_insert.result 2011-10-24 10:33:51 +0000 (e969673)
@@ -0,0 +1,40 @@
+drop table if exists diaries;
+set names utf8;
+create table diaries (
+id int primary key,
+title varchar(255),
+content text,
+fulltext index (title, content),
+fulltext index (title),
+fulltext index (content)
+) default charset utf8;
+show create table diaries;
+Table Create Table
+diaries CREATE TABLE `diaries` (
+ `id` int(11) NOT NULL,
+ `title` varchar(255) DEFAULT NULL,
+ `content` text,
+ PRIMARY KEY (`id`),
+ FULLTEXT KEY `title` (`title`,`content`),
+ FULLTEXT KEY `title_2` (`title`),
+ FULLTEXT KEY `content` (`content`)
+) ENGINE=groonga DEFAULT CHARSET=utf8
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+select * from diaries;
+id title content
+1 Hello はじめました。
+2 天気 明日の富士山の天気について
+3 富士山 今日もきれい。
+select * from diaries where match(title, content) against("富士山");
+id title content
+2 天気 明日の富士山の天気について
+3 富士山 今日もきれい。
+select * from diaries where match(title) against("富士山");
+id title content
+3 富士山 今日もきれい。
+select * from diaries where match(content) against("富士山");
+id title content
+2 天気 明日の富士山の天気について
+drop table diaries;
Added: test/sql/groonga_storage/r/fulltext_multiple_column_index_recreate.result (+42 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/r/fulltext_multiple_column_index_recreate.result 2011-10-24 10:33:51 +0000 (7ba2dae)
@@ -0,0 +1,42 @@
+drop table if exists diaries;
+set names utf8;
+create table diaries (
+id int primary key,
+title varchar(255),
+content text,
+fulltext index (title, content),
+fulltext index (title),
+fulltext index (content)
+) default charset utf8;
+show create table diaries;
+Table Create Table
+diaries CREATE TABLE `diaries` (
+ `id` int(11) NOT NULL,
+ `title` varchar(255) DEFAULT NULL,
+ `content` text,
+ PRIMARY KEY (`id`),
+ FULLTEXT KEY `title` (`title`,`content`),
+ FULLTEXT KEY `title_2` (`title`),
+ FULLTEXT KEY `content` (`content`)
+) ENGINE=groonga DEFAULT CHARSET=utf8
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+select * from diaries where match(title, content) against("富士山");
+id title content
+2 天気 明日の富士山の天気について
+3 富士山 今日もきれい。
+drop index title on diaries;
+select * from diaries where match(title, content) against("富士山");
+ERROR HY000: Can't find FULLTEXT index matching the column list
+create fulltext index new_title_content_index on diaries (title, content);
+select * from diaries where match(title, content) against("富士山");
+id title content
+2 天気 明日の富士山の天気について
+3 富士山 今日もきれい。
+select * from diaries;
+id title content
+1 Hello はじめました。
+2 天気 明日の富士山の天気について
+3 富士山 今日もきれい。
+drop table diaries;
Added: test/sql/groonga_storage/r/fulltext_multiple_column_index_update.result (+37 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/r/fulltext_multiple_column_index_update.result 2011-10-24 10:33:51 +0000 (4b56d4d)
@@ -0,0 +1,37 @@
+drop table if exists diaries;
+set names utf8;
+create table diaries (
+id int primary key,
+title varchar(255),
+content text,
+fulltext index (title, content),
+fulltext index (title),
+fulltext index (content)
+) default charset utf8;
+show create table diaries;
+Table Create Table
+diaries CREATE TABLE `diaries` (
+ `id` int(11) NOT NULL,
+ `title` varchar(255) DEFAULT NULL,
+ `content` text,
+ PRIMARY KEY (`id`),
+ FULLTEXT KEY `title` (`title`,`content`),
+ FULLTEXT KEY `title_2` (`title`),
+ FULLTEXT KEY `content` (`content`)
+) ENGINE=groonga DEFAULT CHARSET=utf8
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+update diaries set title = "チョモランマ" where id = 3;
+update diaries set content = "チョモランマと富士山" where id = 1;
+select * from diaries where match(title, content) against("富士山");
+id title content
+1 Hello チョモランマと富士山
+2 天気 明日の富士山の天気について
+select * from diaries where match(title) against("富士山");
+id title content
+select * from diaries where match(content) against("富士山");
+id title content
+1 Hello チョモランマと富士山
+2 天気 明日の富士山の天気について
+drop table diaries;
Added: test/sql/groonga_storage/t/fulltext_multiple_column_index_delete.test (+42 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/t/fulltext_multiple_column_index_delete.test 2011-10-24 10:33:51 +0000 (34d4ee6)
@@ -0,0 +1,42 @@
+# Copyright(C) 2011 Kouhei Sutou <kou****@clear*****>
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library 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
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source suite/groonga_include/groonga_init.inc
+
+--disable_warnings
+drop table if exists diaries;
+--enable_warnings
+
+set names utf8;
+create table diaries (
+ id int primary key,
+ title varchar(255),
+ content text,
+ fulltext index (title, content),
+ fulltext index (title),
+ fulltext index (content)
+) default charset utf8;
+show create table diaries;
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+delete from diaries where id = 2;
+select * from diaries where match(title, content) against("富士山");
+select * from diaries where match(title) against("富士山");
+select * from diaries where match(content) against("富士山");
+drop table diaries;
+
+--source suite/groonga_include/groonga_deinit.inc
Added: test/sql/groonga_storage/t/fulltext_multiple_column_index_insert.test (+42 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/t/fulltext_multiple_column_index_insert.test 2011-10-24 10:33:51 +0000 (3b76545)
@@ -0,0 +1,42 @@
+# Copyright(C) 2011 Kouhei Sutou <kou****@clear*****>
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library 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
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source suite/groonga_include/groonga_init.inc
+
+--disable_warnings
+drop table if exists diaries;
+--enable_warnings
+
+set names utf8;
+create table diaries (
+ id int primary key,
+ title varchar(255),
+ content text,
+ fulltext index (title, content),
+ fulltext index (title),
+ fulltext index (content)
+) default charset utf8;
+show create table diaries;
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+select * from diaries;
+select * from diaries where match(title, content) against("富士山");
+select * from diaries where match(title) against("富士山");
+select * from diaries where match(content) against("富士山");
+drop table diaries;
+
+--source suite/groonga_include/groonga_deinit.inc
Added: test/sql/groonga_storage/t/fulltext_multiple_column_index_recreate.test (+50 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/t/fulltext_multiple_column_index_recreate.test 2011-10-24 10:33:51 +0000 (59d77a1)
@@ -0,0 +1,50 @@
+# Copyright(C) 2011 Kouhei Sutou <kou****@clear*****>
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library 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
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source suite/groonga_include/groonga_init.inc
+
+--disable_warnings
+drop table if exists diaries;
+--enable_warnings
+
+set names utf8;
+create table diaries (
+ id int primary key,
+ title varchar(255),
+ content text,
+ fulltext index (title, content),
+ fulltext index (title),
+ fulltext index (content)
+) default charset utf8;
+show create table diaries;
+
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+select * from diaries where match(title, content) against("富士山");
+
+drop index title on diaries;
+--error 1191
+select * from diaries where match(title, content) against("富士山");
+
+create fulltext index new_title_content_index on diaries (title, content);
+select * from diaries where match(title, content) against("富士山");
+
+select * from diaries;
+
+drop table diaries;
+
+--source suite/groonga_include/groonga_deinit.inc
Added: test/sql/groonga_storage/t/fulltext_multiple_column_index_update.test (+43 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/groonga_storage/t/fulltext_multiple_column_index_update.test 2011-10-24 10:33:51 +0000 (de2c0b1)
@@ -0,0 +1,43 @@
+# Copyright(C) 2011 Kouhei Sutou <kou****@clear*****>
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library 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
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source suite/groonga_include/groonga_init.inc
+
+--disable_warnings
+drop table if exists diaries;
+--enable_warnings
+
+set names utf8;
+create table diaries (
+ id int primary key,
+ title varchar(255),
+ content text,
+ fulltext index (title, content),
+ fulltext index (title),
+ fulltext index (content)
+) default charset utf8;
+show create table diaries;
+insert into diaries values(1, "Hello", "はじめました。");
+insert into diaries values(2, "天気", "明日の富士山の天気について");
+insert into diaries values(3, "富士山", "今日もきれい。");
+update diaries set title = "チョモランマ" where id = 3;
+update diaries set content = "チョモランマと富士山" where id = 1;
+select * from diaries where match(title, content) against("富士山");
+select * from diaries where match(title) against("富士山");
+select * from diaries where match(content) against("富士山");
+drop table diaries;
+
+--source suite/groonga_include/groonga_deinit.inc