[Feature] Support MySQL help command in OceanBase

[Junkrat77]

Abstract

In order to improve the experience of open-source OceanBase(OB), we think OB needs to support command line help syntax just like MySQL.

For examle:

Design

In order to make Oceanbase support 'help' command, we need to create corresponding help tables, parser the 'help' command. After parsing it, we search the help tables and return corresponding contents to client.

Help Tables

Just like MySQL, we have defined four help tables under the mysql database in OB, and they are all inner system table which would be created and initialized during cluster bootstrapping and defined as follows:

Help_category

Description: table holding system help categories which is the top-level concept.

Struction:

Column	Type	Description
help_category_id	int	category id
name	varchar(64)	category name
parent_category_id	int	parent category id
url	text	the url of the category

Definition:

def_table_schema(
  database_id    = 'OB_MYSQL_SCHEMA_ID',
  table_name    = 'help_relation',
  table_id      = '423',
  table_type = 'SYSTEM_TABLE',
  gm_columns = [],
  rowkey_columns = [
      ('help_topic_id', 'int'),
      ('help_keyword_id', 'int')
  ],
  in_tenant_space = True,
  normal_columns = [],
  columns_with_tenant_id = [],
)      

Help_topic

Description: table holding system help topic which is the usage of sql statement and function, or the introduction of a feature.

Struction:

Column	Type	Description
help_topic_id	int	topic id
name	varchar(64)	topic name
help_category_id	int	corresponding policy id
description	text	the description of the topic
example	text	example of how to use
url	text	link url

Definition:

def_table_schema(
  database_id    = 'OB_MYSQL_SCHEMA_ID',
  table_name    = 'help_topic',
  table_id      = '422',
  table_type = 'SYSTEM_TABLE',
  gm_columns = [],
  rowkey_columns = [
      ('help_topic_id', 'int'),
  ],
  in_tenant_space = True,
  normal_columns = [
    ('name', 'varchar:64'),
    ('help_category_id', 'int'),
    ('description', 'text'),
    ('example', 'text'),
    ('url', 'text')
  ],
  columns_with_tenant_id = [],
)

Help_keyword

Description: table holding some keywords. Every keyword is related to some topics.

Struction:

Column	Type	Description
help_keyword_id	int	keyword id
name	varchar(64)	keyword name

Definition:

def_table_schema(
  database_id    = 'OB_MYSQL_SCHEMA_ID',
  table_name    = 'help_keyword',
  table_id      = '421',
  table_type = 'SYSTEM_TABLE',
  gm_columns = [],
  rowkey_columns = [
      ('help_keyword_id', 'int'),
  ],
  in_tenant_space = True,
  normal_columns = [
    ('name', 'varchar:64')
  ],
  columns_with_tenant_id = [],
)

Help_relation

Description: Every table item represents a pair of keyword and topic associated.

Struction:

Column	Type	Description
help_topic_id	int	topic id
help_keyword_id	int	keyword id

Definition:

def_table_schema(
  database_id    = 'OB_MYSQL_SCHEMA_ID',
  table_name    = 'help_relation',
  table_id      = '423',
  table_type = 'SYSTEM_TABLE',
  gm_columns = [],
  rowkey_columns = [
      ('help_topic_id', 'int'),
      ('help_keyword_id', 'int')
  ],
  in_tenant_space = True,
  normal_columns = [],
  columns_with_tenant_id = [],
)      

Create and Initialize

Since ob supports multi-tenant deployment, every tenant holds its own help tables. For sys-tenant, help tables will be created and initialized during bootstrap. For non-sys tenant, help tables will be created and initialized when the tenant is being created. The way of initialization is to execute the pre-defined sql statement by calling inner sql.

How to define the sql statement will be introducted later.

Parser the Help Command

For parsing help command like "help content", we need to add corresponding parsing rules in ./src/sql/parser/sql_parser_mysql_mode.l and ./src/sql/parser/sql_parser_mysql_mode.y. Then execute ./src/sql/parser/gen_sql.pyto generate the corresponding c++ files. Help grammar are as follows:

help_stmt:
HELP ident_or_text
{
  $$=$2;
  $$->type_=T_HELP;
}          
;
ident_or_text:
  NAME_OB     { $$=$1;}
| text_string { $$=$1;}
;

Search and Respond

According to the content of 'help" command, we need to query different help tables in turn(search topic, search keyword, search topic). We do this by Inner SQL. According to different query results, we construct corresponding select sql statement, which will be parsed and execute later to return the MySQL_RES(the result that client wil be recieve) to client(mysql-client or obclient). Finally, client can analyze the result and output the help contents.

We do the above operations in the resolver phase.

seach topic

For exmaple: help "contents"

First, we search help topic . We will perform a fuzzy query on the help topic table by Inner SQl. According to the number of topics queried, wo do different operations as follows:

• topic_num = 0
  It means there has no matching topic, go to the next search stage.
• topic_num = 1
  It means there has only one matching topic. In this case, we want client to recieve the details of this topic. So the final select sql statement we construct is as follow:
  select name, description, example from mysql.help_topic WHERE name like 'contents'
• topic_num > 1
  It means there has multiple matching topics. In this case, we want client to recieve the names of these topics. Not only that, we will search the help category table, and get the matching categories to client. After client recieving the result, it will output the topic list and category list(Only name). The final select sql statement we construct is as follow:

select name, "N" as is_it_category from mysql.help_topic 
where name like '%.*s'"
union all
select name, \"Y\" as is_it_category from mysql.help_category 
where name like '%.*s' order by 2 asc , 1 asc"

search keyword

If we didn't get any topic in "seach topic" phase, we search keyword by Inner SQL. We execute the following sql statement to perform a joint query on tables help_keyword , help relation and help topic to get the matching topics.

select t3.name from mysql.help_keyword t1 
 JOIN mysql.help_relation t2 on (t1.help_keyword_id = t2.help_keyword_id)
 JOIN mysql.help_topic t3 on (t2.help_topic_id = t3.help_topic_id)
 WHERE t1.name like 'contents'.

According to the number of topics queried, wo do different operations as follows(just like 'search topic'):

• topic_num = 0
  It means there has no matching topic, go to the next search stage.
• topic_num = 1
  It means there has only one matching topic. In this case, we want client to recieve the details of this topic.
• topic_num > 1
  It means there has multiple matching topics. In this case, we want client to recieve the names of these topics. Not only that, we will search the help category table, and get the matching categories to client. After client recieving the result, it will output the topic list and category list(Only name).

search category

If we didn't get any topic in "seach topic" and "search keyword" phase, we search category by Inner SQL as follow:

select help_category_id, name from mysql.help_category where name like 'content'

According to the number of categories queried, wo do different operations as follows(just like 'search topic'):

• categories_num = 0
  In this case, we trust there has no matching contents, so we construct a result-empty select sql.
• categories_num = 1
  This situation is more complicated. In this case, In this case, we query help_topic and help_category respectively according to the unique category id we get, and get the topic and subcategories contained in this category.
  In MySQl, the mysql server just return the topics and subcategories(only name), but it may cause bug.(https://bugs.mysql.com/bug.php?id=107997)
  So, we take some measures.
  If this is only one matching topic(no matching category), we will return the details of this topic.
  If this is only one matching category(no matching topic), we will return the name of this category.
  Otherwise, we do the same as MySQL.
• categories_num > 1
  In this case, we construct a select sql statement to retrun match categories(only names).

Parse and execute the select sql

After searching topic, keyword and category, we get a select sql statement. Then we do the same operations like "show" command to return the result.

How to Contribute

In order to allow more people to enrich the content of the help table. We provide a method. There is a file named gen_fill_help_table.py. You can add category, topic or keyword in category_list, topic_list or keyword_list in a fixed format. After this, execute gen_fill_help_table.py then you need to execute obclient -p observer_ip -u root@tenant -P port < ob_fill_help_tables.sqlto update the help tables for any tenant. Newly created tenant will also hold the newest help tables.

category_list = [
    {
		"help_category_id": "1",
		"name": "Contents",
		"parent_category_id": "0",
		"url": ""
	},
    # add more
]
topic_list = [
    {
		"help_topic_id": "1",
		"help_category_id": "21",
		"name": "TINYINT",
		"description": "\\n  TINYINT[(M)] [UNSIGNED] [ZEROFILL]\\n\\n ",
		"example": "",
		"url": ""
	},
    # add more
]
keyword_list = [
    {
		"help_keyword_id": "1",
		"name": "TINYINT",
		"relation_topic": [1]
	},
    # add more
]

What you can contribute:

1. Add a category.
   You need to add a category item in categort_list, then choose a parent-category for it. After that, you can add some subcategories or topics for it, or not.
2. Add a topic
   You need to add a topic item in topic_list, and choose a category to hold it.
3. Add a keyword
   You need to add a keyword item in keyword_list, and bind some topics to it(in the relation_topic filed).
4. Bind a topic to a keyword
   You only need to add the topic id in the relation_topic filed of a keyword item.

Just contribute, we will make the correctness check in the script. Make you own help tables.

[LINxiansheng]

+1

[LINxiansheng]

Can you add the four virtual tables application in the discussion?

[Junkrat77]

Done

[caifeizhi]

每张表的数据是如何生成的？

[Junkrat77]

在创建租户时,会通过inner sql执行一系列的insert语句来对每张表的数据进行填充,这些sql语句由内部来维护，初稿我会把master中的提供给你