- SYSTEM_SCHEMAS =
%w[information_schema mysql performance_schema sys].freeze
- DATABASE_SQL =
'/* rdbr: mysql database */ SELECT DATABASE() AS name'.freeze
- NAMESPACES_SQL =
<<~SQL.freeze
/* rdbr: mysql namespaces */
SELECT SCHEMA_NAME AS name
FROM information_schema.SCHEMATA
%<system_filter>s
ORDER BY SCHEMA_NAME
SQL
- RELATIONS_SQL =
<<~SQL.freeze
/* rdbr: mysql relations */
SELECT TABLE_SCHEMA AS namespace_name, TABLE_NAME AS relation_name,
TABLE_TYPE AS relation_kind, TABLE_COMMENT AS comment
FROM information_schema.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW', 'SYSTEM VIEW')
%<system_filter>s
ORDER BY TABLE_SCHEMA, TABLE_NAME
SQL- COLUMNS_SQL =
<<~SQL.freeze
/* rdbr: mysql columns */
SELECT COLUMN_NAME AS name, DATA_TYPE AS logical_type, COLUMN_TYPE AS sql_type,
IS_NULLABLE AS nullable, COLUMN_DEFAULT AS default_expression,
GENERATION_EXPRESSION AS generated_expression, EXTRA AS extra,
ORDINAL_POSITION AS ordinal
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = %<namespace>s AND TABLE_NAME = %<relation>s
ORDER BY ORDINAL_POSITION
SQL- CONSTRAINTS_SQL =
<<~SQL.freeze
/* rdbr: mysql constraints */
SELECT constraint_record.CONSTRAINT_NAME AS name,
constraint_record.CONSTRAINT_TYPE AS constraint_type,
key_column.COLUMN_NAME AS column_name,
key_column.ORDINAL_POSITION AS ordinal,
key_column.REFERENCED_TABLE_SCHEMA AS referenced_namespace,
key_column.REFERENCED_TABLE_NAME AS referenced_relation,
key_column.REFERENCED_COLUMN_NAME AS referenced_column,
reference.UPDATE_RULE AS on_update, reference.DELETE_RULE AS on_delete
FROM information_schema.TABLE_CONSTRAINTS constraint_record
LEFT JOIN information_schema.KEY_COLUMN_USAGE key_column
ON key_column.CONSTRAINT_SCHEMA = constraint_record.CONSTRAINT_SCHEMA
AND key_column.TABLE_NAME = constraint_record.TABLE_NAME
AND key_column.CONSTRAINT_NAME = constraint_record.CONSTRAINT_NAME
LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS reference
ON reference.CONSTRAINT_SCHEMA = constraint_record.CONSTRAINT_SCHEMA
AND reference.TABLE_NAME = constraint_record.TABLE_NAME
AND reference.CONSTRAINT_NAME = constraint_record.CONSTRAINT_NAME
WHERE constraint_record.TABLE_SCHEMA = %<namespace>s
AND constraint_record.TABLE_NAME = %<relation>s
AND constraint_record.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY')
ORDER BY constraint_record.CONSTRAINT_NAME, key_column.ORDINAL_POSITION
SQL- CHECKS_SQL =
<<~SQL.freeze
/* rdbr: mysql checks */
SELECT constraint_record.CONSTRAINT_NAME AS name, check_record.CHECK_CLAUSE AS expression
FROM information_schema.TABLE_CONSTRAINTS constraint_record
INNER JOIN information_schema.CHECK_CONSTRAINTS check_record
ON check_record.CONSTRAINT_SCHEMA = constraint_record.CONSTRAINT_SCHEMA
AND check_record.CONSTRAINT_NAME = constraint_record.CONSTRAINT_NAME
WHERE constraint_record.TABLE_SCHEMA = %<namespace>s
AND constraint_record.TABLE_NAME = %<relation>s
AND constraint_record.CONSTRAINT_TYPE = 'CHECK'
ORDER BY constraint_record.CONSTRAINT_NAME
SQL- INDEXES_SQL =
<<~SQL.freeze
/* rdbr: mysql indexes */
SELECT INDEX_NAME AS name, NON_UNIQUE AS non_unique, INDEX_TYPE AS index_type,
SEQ_IN_INDEX AS ordinal, COLUMN_NAME AS column_name
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = %<namespace>s AND TABLE_NAME = %<relation>s
ORDER BY INDEX_NAME, SEQ_IN_INDEX
SQL