HELP Contents need to use MariaDB Knowledgebase content

The problem

Currently, the HELP command in any MariaDB client will return data that is generated from the MySQL documentation. The URL's point to the MySQL documentation instead of MariaDB's, and the text generated is in some cases not accurate. The HELP content is generated from the mysql_install_db and the fill_help_table.sql scripts, which are the MySQL versions.

There is also scope to greatly improve the existing HELP, for example including variables in the help contents.

The solution

In 2013, I wrote a script that would replace all MySQL URL's with MariaDB ones. This worked well and would be relatively easy to maintain, but was not a complete solution, as it did not solve the problem of the inaccurate text.

The ideal solution as I can see it would be to have the HELP contents all automatically generated from the MariaDB Knoweldgebase.

Not all content from the Knowledgebase needs to be included (blog posts, news articles and case studies spring to mind, so the Knowledgebase would need to be amended to cater for selection)

The existing structure

The HELP contents are in the following format. There are four relevant tables in the mysql database: help_topic, help_category, help_keyword and help_relation.

DESC help_keyword;

+-----------------+------------------+------+-----+---------+-------+

| Field           | Type             | Null | Key | Default | Extra |

+-----------------+------------------+------+-----+---------+-------+

| help_keyword_id | int(10) unsigned | NO   | PRI | NULL    |       |

| name            | char(64)         | NO   | UNI | NULL    |       |

+-----------------+------------------+------+-----+---------+-------+

The help_keyword table consists of individual keywords, such as "SHA" or "INNODB". Entering "HELP xxx" will return

DESC help_topic;

+------------------+----------------------+------+-----+---------+-------+

| Field            | Type                 | Null | Key | Default | Extra |

+------------------+----------------------+------+-----+---------+-------+

| help_topic_id    | int(10) unsigned     | NO   | PRI | NULL    |       |

| name             | char(64)             | NO   | UNI | NULL    |       |

| help_category_id | smallint(5) unsigned | NO   |     | NULL    |       |

| description      | text                 | NO   |     | NULL    |       |

| example          | text                 | NO   |     | NULL    |       |

| url              | char(128)            | NO   |     | NULL    |       |

+------------------+----------------------+------+-----+---------+-------+

help_topic contains the final description that is displayed, as well as the URL.

DESC help_category;

+--------------------+----------------------+------+-----+---------+-------+

| Field              | Type                 | Null | Key | Default | Extra |

+--------------------+----------------------+------+-----+---------+-------+

| help_category_id   | smallint(5) unsigned | NO   | PRI | NULL    |       |

| name               | char(64)             | NO   | UNI | NULL    |       |

| parent_category_id | smallint(5) unsigned | YES  |     | NULL    |       |

| url                | char(128)            | NO   |     | NULL    |       |

+--------------------+----------------------+------+-----+---------+-------+

help_category contains a list of categories, for example "Geographic Features". Searching for help by category returns a list of sub-categories and related topics, for example:

HELP Geographic Features;

You asked for help about help category: "Geographic Features"

For more information, type 'help <item>', where <item> is one of the following

topics:

   GEOMETRY

   GEOMETRY HIERARCHY

   SPATIAL

categories:

   Geometry constructors

   Geometry properties

   Geometry relations

   LineString properties

   MBR

   Point properties

   Polygon properties

   WKB

   WKT

DESC help_relation;

+-----------------+------------------+------+-----+---------+-------+

| Field           | Type             | Null | Key | Default | Extra |

+-----------------+------------------+------+-----+---------+-------+

| help_topic_id   | int(10) unsigned | NO   | PRI | NULL    |       |

| help_keyword_id | int(10) unsigned | NO   | PRI | NULL    |       |

+-----------------+------------------+------+-----+---------+-------+

help_relation describes a relation between a topic and a keyword. For example, topic "CREATE TABLE" is related to keyword "ROW_FORMAT". If one searches for "HELP ROW_FORMAT", the content for the topic is returned, as if one had searched for "HELP CREATE TABLE".

Generating the content

To generate the content using the same structure,

In the Knowledgebase tool (admin tool, or simply when editing), add a checkbox labeled "Include in HELP contents", default unchecked. If checked, it becomes mandatory to link the page to a Category (the Category could also be automatically generated from the page's Knowledgebase Category), and optional to link to one or more Keywords. The keyword must be unique to that page, the category can be shared by other pages. A Knowledgebase page that is itself category can also be linked to another category as a child category.

With this structure in place, a script then runs to generate the contents of fill_help_table.sql. Here's a broad spec of what the script needs to:

• empty each of the four tables, then include a top level "Contents" category
• traverse each page of the knowledgebase
• if "Include in HELP contents" is false, move on to the next page, otherwise continue as below
• insert a new record into help_topic (see below), using the title of the page as the name, and the page URL as url with the remaining fields as determined below.
• see if the category the page has been linked to exists in the help_category table.
• If not, insert a record into help_category table, including the parent category if applicable (which may itself need to be inserted here). The url field is always kept empty in the MySQL version of the page. This can be populated or left empty, but I am not sure what effect this has on anything.
• if the page is linked to a keyword, insert the keyword and relationship in the help_keyword and help_relation tables.

When adding a record to the help_topic table, the Knowledgebase formatting has to be sensibly parsed out. Here are some (I'm sure by no means complete) considerations:

• Many pages contain a Syntax/Description/Examples template. These are headings. All headings should be included, with macros (e.g. contents, product macro) and macro formatting removed.
• I am very keen on the idea of including HELP content for each of the variables. Deep links exist so they can be accessed directly, but I haven't yet given this much thought on how this could be automatically generated.
• To be fleshed out.