Import MariaDB

-- Regular MariaDB Database Import Query
WITH fk_info AS (
(
   SELECT (@fk_info:=NULL),
          (
                 SELECT (0)
                 FROM   (
                               SELECT kcu.table_schema,
                                      kcu.table_name,
                                      kcu.column_name                                                                                                                                                                                 AS fk_column,
                                      kcu.constraint_name                                                                                                                                                                             AS foreign_key_name,
                                      kcu.referenced_table_schema                                                                                                                                                                     AS reference_schema,
                                      kcu.referenced_table_name                                                                                                                                                                       AS reference_table,
                                      kcu.referenced_column_name                                                                                                                                                                      AS reference_column,
                                             Concat('FOREIGN KEY (', kcu.column_name, ') REFERENCES ', kcu.referenced_table_name, '(', kcu.referenced_column_name, ') ', 'ON UPDATE ', rc.update_rule, ' ON DELETE ', rc.delete_rule) AS fk_def
                               FROM   information_schema.key_column_usage kcu
                               JOIN   information_schema.referential_constraints rc
                               ON     kcu.constraint_name = rc.constraint_name
                               AND    kcu.table_schema = rc.constraint_schema
                               AND    kcu.table_name = rc.table_name
                               WHERE  kcu.referenced_table_name IS NOT NULL) AS fk
                 WHERE  table_schema LIKE Ifnull(NULL, '%')
                 AND    table_schema = Database()
                 AND    (
                               0x00) IN (@fk_info:=concat_ws(',', @fk_info, Concat('{"schema":"',table_schema, '","table":"',table_name, '","column":"', Ifnull(fk_column, ''), '","foreign_key_name":"', Ifnull(foreign_key_name, ''), '","reference_schema":"', Ifnull(reference_schema, ''), '","reference_table":"', Ifnull(reference_table, ''), '","reference_column":"', Ifnull(reference_column, ''), '","fk_def":"', Ifnull(fk_def, ''), '"}'))))) ), pk_info AS (
(
   SELECT (@pk_info:=NULL),
          (
                 SELECT (0)
                 FROM   (
                                 SELECT   table_schema,
                                          table_name  AS pk_table,
                                          column_name AS pk_column,
                                          (
                                                   SELECT   concat('PRIMARY KEY (', group_concat(inc.column_name ORDER BY inc.ordinal_position separator ', '), ')')
                                                   FROM     information_schema.key_column_usage AS inc
                                                   WHERE    inc.constraint_name = 'PRIMARY'
                                                   AND      outc.table_schema = inc.table_schema
                                                   AND      outc.table_name = inc.table_name) AS pk_def
                                 FROM     information_schema.key_column_usage                 AS outc
                                 WHERE    constraint_name = 'PRIMARY'
                                 GROUP BY table_schema,
                                          table_name,
                                          column_name
                                 ORDER BY table_schema,
                                          table_name,
                                          ordinal_position) AS pk
                 WHERE  table_schema LIKE ifnull(NULL, '%')
                 AND    table_schema = DATABASE()
                 AND    (
                               0x00) IN (@pk_info:=concat_ws(',', @pk_info, concat('{"schema":"', table_schema, '","table":"', pk_table, '","column":"', pk_column, '","pk_def":"', ifnull(pk_def, ''), '"}'))))) ), cols AS (
(
   SELECT (@cols := NULL),
          (
                 SELECT (0)
                 FROM   information_schema.columns cols
                 WHERE  cols.table_schema LIKE ifnull(NULL, '%')
                 AND    cols.table_schema = DATABASE()
                 AND    (
                               0x00) IN (@cols := concat_ws(',', @cols, concat( '{"schema":"', cols.table_schema, '","table":"', cols.table_name, '","name":"', replace(cols.column_name, '"', '\"'), '","type":"', lower(cols.data_type), '","character_maximum_length":"', ifnull(cols.character_maximum_length, 'null'), '","precision":',
                                         CASE
                                                WHEN cols.data_type IN ('decimal',
                                                                        'numeric') THEN concat('{"precision":', ifnull(cols.numeric_precision, 'null'), ',"scale":', ifnull(cols.numeric_scale, 'null'), '}')
                                                ELSE 'null'
                                         END, ',"ordinal_position":"', cols.ordinal_position, '","nullable":',IF(cols.is_nullable = 'YES', 'true', 'false'), ',"default":"', ifnull(replace(replace(cols.column_default, '\\', ''), '"', '\"'), ''), '","collation":"', ifnull(cols.collation_name, ''), '"}' ))))) ), indexes AS (
(
   SELECT (@indexes:=NULL),
          (
                 SELECT (0)
                 FROM   information_schema.STATISTICS indexes
                 WHERE  table_schema LIKE ifnull(NULL, '%')
                 AND    table_schema = DATABASE()
                 AND    (
                               0x00) IN (@indexes:=concat_ws(',', @indexes, concat('{"schema":"',indexes.table_schema, '","table":"',indexes.table_name, '","name":"', indexes.index_name, '","size":"',
                                         (
                                                SELECT ifnull(sum(stat_value \* @@innodb_page_size), -1) AS size_in_bytes
                                                FROM   mysql.innodb_index_stats
                                                WHERE  stat_name = 'size'
                                                AND    index_name != 'PRIMARY'
                                                AND    index_name = indexes.index_name
                                                AND    table_name = indexes.table_name
                                                AND    database_name = indexes.table_schema), '","column":"', indexes.column_name, '","index_type":"', lower(indexes.index_type), '","cardinality":', indexes.cardinality, ',"direction":"', (
                                         CASE
                                                WHEN indexes.collation = 'D' THEN 'desc'
                                                ELSE 'asc'
                                         END), '","unique":', IF(indexes.non_unique = 1, 'false', 'true'), '}'))))) ), tbls AS (
(
   SELECT (@tbls:=NULL),
          (
                 SELECT (0)
                 FROM   information_schema.tables tbls
                 WHERE  table_schema LIKE ifnull(NULL, '%')
                 AND    table_schema = DATABASE()
                 AND    (
                               0x00) IN (@tbls:=concat_ws(',', @tbls, concat('{', '"schema":"', `table_schema`, '",', '"table":"', `table_name`, '",', '"rows":', ifnull(`table_rows`, 0), ', "type":"', ifnull(`table_type`, ''), '",', '"engine":"', ifnull(`engine`, ''), '",', '"collation":"', ifnull(`table_collation`, ''), '"}'))))) ), views AS (
(
   SELECT (@views:=NULL),
          (
                 SELECT (0)
                 FROM   information_schema.views views
                 WHERE  table_schema LIKE ifnull(NULL, '%')
                 AND    table_schema = DATABASE()
                 AND    (
                               0x00) IN (@views:=concat_ws(',', @views, concat('{', '"schema":"', `table_schema`, '",', '"view_name":"', `table_name`, '",', '"view_definition":"', replace(replace(to_base64(view_definition), ' ', ''), ' ', ''), '"}'))) ) ) )
(
   SELECT cast(concat('{"fk_info": [',ifnull(@fk_info,''), '], "pk_info": [', ifnull(@pk_info, ''), '], "columns": [',ifnull(@cols,''), '], "indexes": [',ifnull(@indexes,''), '], "tables":[',ifnull(@tbls,''), '], "views":[',ifnull(@views,''), '], "database_name": "', DATABASE(), '", "version": "', version(), '"}') AS char) AS metadata_json_to_import
   FROM   fk_info,
          pk_info,
          cols,
          indexes,
          tbls,
          views);