技术分享 | 如何编写 MySQL Shell 插件

下面是一段示例代码

# init.py
# -------
# 演示注册report和plugin两种方式
# 定义一个查询函数，获取没有主键或唯一索引表
def report_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                    SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    report = []
    if (result.has_data()):
        report = [result.get_column_names()]
        for row in result.fetch_all():
            report.append(list(row))
  # 注册为report，需要返回字典类型
    return {"report": report}

# 功能同上，这里为演示以Pluginf方式重新定义函数，两者report和plugin差异主要在于输出方式
def plugin_table_without_pk(session):
    query = '''SELECT tables.table_schema , tables.table_name
                FROM information_schema.tables
                LEFT JOIN (
                SELECT table_schema , table_name
                FROM information_schema.statistics
                GROUP BY table_schema, table_name, index_name HAVING
                SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks
                ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name
                WHERE puks.table_name is null
                AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";'''
    result = session.run_sql(query)
    shell.dump_rows(result)
    return

# 注册一个function，用来给表添加主键字段，变更类操作通常以function
def _add_pk(table, columns, session=None):
    query = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table, columns)
    if session is None:
        session = shell.get_session()
        if session is None:
            print("No session specified. Either pass a session object to this "
                  "function or connect the shell to a database")
            return
    # session = shell.get_session()
    result = session.run_sql(query)

# 这里注册上面定义的report函数，report名称，返回格式类型，函数名，描述
shell.register_report("table_without_pk", "list", report_table_without_pk, {
                      "brief": "Lists the table without primary key."})

# 这里定义全局对象，可以看做一个命名空间，ext是默认社区扩展插件的对象名，或者其他自定义对象名称
if 'ext' in globals():
    global_obj = ext
else:
    # Otherwise register new global object named 'ext'
    global_obj = shell.create_extension_object()
    shell.register_global("ext", global_obj,
                          {"brief": "MySQL Shell extension plugins."})

# 可以按类别在全局对象下添加子对象
try:
    plugin_obj = global_obj.table
except IndexError:

    plugin_obj = shell.create_extension_object()
    shell.add_extension_object_member(global_obj,
                                      "table",
                                      plugin_obj,
                                      {"brief": "Utility object for table operations."})

# 添加功能函数到自定义插件对象中，父级对象名，调用函数名，定义的函数，描述，函数接受的参数名/类型/是否必选/描述
try:
    shell.add_extension_object_member(plugin_obj,
    "add_pk",
    _add_pk,
    {"brief":
    "Add a primary key to the table",
    "parameters": [
        {
            "name": "table",
            "type": "string",
            "required": True,
            "brief": "table name."
        },
        {
            "name": "columns",
            "type": "string",
            "required": True,
            "brief": "column name."
        },
        {
            "name": "session",
            "type": "object",
            "class": "Session",
            "required": False,# 若不想单独传session参数，可以在函数中获取当前会话对象
            "brief": "The session to be used on the operation."
        }
    ]
    })

except Exception as e:
    shell.log("ERROR", "Failed to register ext.table.add_pk ({0}).".format(
        str(e).rstrip()))

# 添加plugin_table_without_pk
try:
    shell.add_extension_object_member(plugin_obj,
    "get_without_pk",
    plugin_table_without_pk,
    {"brief":
    "Lists the table without primary key.",
    })
except Exception as e:
    shell.log("ERROR", "Failed to register ext.table.get_without_pk ({0}).".format(
        str(e).rstrip()))