6.5_about_information_schema.md

6.5 information_schema等库的支持

背景

用户使用 Navicat Premium 12 连接dble时，Navicat Premium 12会查询 information_schema，mysql 等数据库中系统表的数据。
dble 需要支持查询这些系统表的语句，保证这些 driver 的正常使用。

Navicat Premium12

1. SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;

该语句查询当前 mysql 实例中的所有 scheme的名称，character set 以及 collation。该语句会影响Navicat Premium 12的使用。
dble中的scheme是逻辑上的，可以通过 SchemeConfig 获取所有 scheme 的名称，scheme 的 character set 以及 collation 使用默认返回。
问题1：dble中的scheme是逻辑上的，会对应多个shardingNode，会出现shardingNode的 character set 以及 collation 与 默认character set 和 collation 不同,需要运维安装MySQL时候保证。
问题2：当前dble中处理 SCHEMATA 系统表时，只是对表名做了判断，并未对查询字段做检验。

以下语句不影响driver的使用

对此种语句的处理是根据请求字段，伪造一个行数为0的报文返回。

1. SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
    FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb' 
    ORDER BY TABLE_SCHEMA, TABLE_TYPE 
2. SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'testdb' 
    ORDER BY TABLE_SCHEMA, TABLE_NAME  
3. SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME, PARAMS.PARAMETER 
    FROM information_schema.ROUTINES LEFT JOIN  
    ( SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, 
        GROUP_CONCAT(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') PARAMETER, ROUTINE_TYPE 
	FROM information_schema.PARAMETERS GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE 
    )PARAMS  
    ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA AND 
    ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME AND 
    ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE  
    WHERE ROUTINE_SCHEMA = 'testdb' ORDER BY ROUTINE_SCHEMA  
4. SELECT TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, SECURITY_TYPE, DEFINER 
    FROM information_schema.VIEWS 
    WHERE TABLE_SCHEMA = 'testdb' ORDER BY TABLE_NAME ASC
5. SELECT * FROM information_schema.ROUTINES 
    WHERE ROUTINE_SCHEMA = 'testdb' ORDER BY ROUTINE_NAME  
6. SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, 
    EVENT_DEFINITION, EVENT_BODY, EVENT_TYPE, SQL_MODE, STATUS, EXECUTE_AT, 
    INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, ON_COMPLETION, CREATED, 
    LAST_ALTERED, LAST_EXECUTED, ORIGINATOR, CHARACTER_SET_CLIENT, 
    COLLATION_CONNECTION, DATABASE_COLLATION, EVENT_COMMENT 
    FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'testdb' 
    ORDER BY EVENT_NAME ASC  
7. SELECT COUNT(*) FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'testdb' UNION 
   SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'testdb' UNION 
   SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'testdb'