CREATE DEFINER = root @localhost PROCEDURE create_synonym_db(
IN in_db_name VARCHAR(64),
IN in_synonym VARCHAR(64)
) MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT '\n Description\n -----------\n\n Takes a source database name and synonym name, and then creates the \n synonym database with views that point to all of the tables within\n the source database.\n\n Useful for creating a "ps" synonym for "performance_schema",\n or "is" instead of "information_schema", for example.\n\n Parameters\n -----------\n\n in_db_name (VARCHAR(64)):\n The database name that you would like to create a synonym for.\n in_synonym (VARCHAR(64)):\n The database synonym name.\n\n Example\n -----------\n\n mysql> SHOW DATABASES;\n +--------------------+\n | Database |\n +--------------------+\n | information_schema |\n | mysql |\n | performance_schema |\n | sys |\n | test |\n +--------------------+\n 5 rows in set (0.00 sec)\n\n mysql> CALL sys.create_synonym_db(''performance_schema'', ''ps'');\n +-------------------------------------+\n | summary |\n +-------------------------------------+\n | Created 74 views in the ps database |\n +-------------------------------------+\n 1 row in set (8.57 sec)\n\n Query OK, 0 rows affected (8.57 sec)\n\n mysql> SHOW DATABASES;\n +--------------------+\n | Database |\n +--------------------+\n | information_schema |\n | mysql |\n | performance_schema |\n | ps |\n | sys |\n | test |\n +--------------------+\n 6 rows in set (0.00 sec)\n\n mysql> SHOW FULL TABLES FROM ps;\n +------------------------------------------------------+------------+\n | Tables_in_ps | Table_type |\n +------------------------------------------------------+------------+\n | accounts | VIEW |\n | cond_instances | VIEW |\n | events_stages_current | VIEW |\n | events_stages_history | VIEW |\n ...\n ' BEGIN DECLARE v_done bool DEFAULT FALSE; DECLARE v_db_name_check VARCHAR(64); DECLARE v_db_err_msg TEXT; DECLARE v_table VARCHAR(64); DECLARE v_views_created INT DEFAULT 0; DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000'; DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000'; DECLARE c_table_names CURSOR FOR
SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = in_db_name; DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
v_done = TRUE;
SELECT
SCHEMA_NAME INTO v_db_name_check
FROM
CREATE DEFINER =
root@localhostPROCEDUREcreate_synonym_db( IN in_db_name VARCHAR(64), IN in_synonym VARCHAR(64) ) MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT '\n Description\n -----------\n\n Takes a source database name and synonym name, and then creates the \n synonym database with views that point to all of the tables within\n the source database.\n\n Useful for creating a "ps" synonym for "performance_schema",\n or "is" instead of "information_schema", for example.\n\n Parameters\n -----------\n\n in_db_name (VARCHAR(64)):\n The database name that you would like to create a synonym for.\n in_synonym (VARCHAR(64)):\n The database synonym name.\n\n Example\n -----------\n\n mysql> SHOW DATABASES;\n +--------------------+\n | Database |\n +--------------------+\n | information_schema |\n | mysql |\n | performance_schema |\n | sys |\n | test |\n +--------------------+\n 5 rows in set (0.00 sec)\n\n mysql> CALL sys.create_synonym_db(''performance_schema'', ''ps'');\n +-------------------------------------+\n | summary |\n +-------------------------------------+\n | Created 74 views in the ps database |\n +-------------------------------------+\n 1 row in set (8.57 sec)\n\n Query OK, 0 rows affected (8.57 sec)\n\n mysql> SHOW DATABASES;\n +--------------------+\n | Database |\n +--------------------+\n | information_schema |\n | mysql |\n | performance_schema |\n | ps |\n | sys |\n | test |\n +--------------------+\n 6 rows in set (0.00 sec)\n\n mysql> SHOW FULL TABLES FROM ps;\n +------------------------------------------------------+------------+\n | Tables_in_ps | Table_type |\n +------------------------------------------------------+------------+\n | accounts | VIEW |\n | cond_instances | VIEW |\n | events_stages_current | VIEW |\n | events_stages_history | VIEW |\n ...\n ' BEGIN DECLARE v_done bool DEFAULT FALSE; DECLARE v_db_name_check VARCHAR(64); DECLARE v_db_err_msg TEXT; DECLARE v_table VARCHAR(64); DECLARE v_views_created INT DEFAULT 0; DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000'; DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000'; DECLARE c_table_names CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = in_db_name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; SELECT SCHEMA_NAME INTO v_db_name_check FROM