1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
-- Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
DROP PROCEDURE IF EXISTS create_synonym_db;
DELIMITER $$
CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE create_synonym_db (
IN in_db_name VARCHAR(64),
IN in_synonym VARCHAR(64)
)
COMMENT '
Description
-----------
Takes a source database name and synonym name, and then creates the
synonym database with views that point to all of the tables within
the source database.
Useful for creating a "ps" synonym for "performance_schema",
or "is" instead of "information_schema", for example.
Parameters
-----------
in_db_name (VARCHAR(64)):
The database name that you would like to create a synonym for.
in_synonym (VARCHAR(64)):
The database synonym name.
Example
-----------
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> CALL sys.create_synonym_db(\'performance_schema\', \'ps\');
+---------------------------------------+
| summary |
+---------------------------------------+
| Created 74 views in the `ps` database |
+---------------------------------------+
1 row in set (8.57 sec)
Query OK, 0 rows affected (8.57 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ps |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> SHOW FULL TABLES FROM ps;
+------------------------------------------------------+------------+
| Tables_in_ps | Table_type |
+------------------------------------------------------+------------+
| accounts | VIEW |
| cond_instances | VIEW |
| events_stages_current | VIEW |
| events_stages_history | VIEW |
...
'
SQL SECURITY INVOKER
NOT DETERMINISTIC
MODIFIES SQL DATA
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 v_table_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY') DEFAULT '';
DECLARE v_temp_table TEXT;
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;
-- Check if the source database exists
SELECT SCHEMA_NAME INTO v_db_name_check
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = in_db_name;
IF v_db_name_check IS NULL THEN
SET v_db_err_msg = CONCAT('Unknown database ', in_db_name);
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = v_db_err_msg;
END IF;
-- Check if a database of the synonym name already exists
SELECT SCHEMA_NAME INTO v_db_name_check
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = in_synonym;
IF v_db_name_check = in_synonym THEN
SET v_db_err_msg = CONCAT('Can\'t create database ', in_synonym, '; database exists');
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = v_db_err_msg;
END IF;
-- All good, create the database and views
SET @create_db_stmt := CONCAT('CREATE DATABASE ', sys.quote_identifier(in_synonym));
PREPARE create_db_stmt FROM @create_db_stmt;
EXECUTE create_db_stmt;
DEALLOCATE PREPARE create_db_stmt;
SET v_done = FALSE;
OPEN c_table_names;
c_table_names: LOOP
FETCH c_table_names INTO v_table;
IF v_done THEN
LEAVE c_table_names;
END IF;
-- Check does temporary table shadows the base table. If it is so, terminate.
CALL sys.table_exists(in_db_name, v_table, v_table_exists);
IF (v_table_exists = 'TEMPORARY') THEN
SET v_temp_table =
CONCAT(
'Table',
sys.quote_identifier(in_db_name),
'.',
sys.quote_identifier(v_table),
'shadows base table. View cannot be created! Terminating!');
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = v_temp_table;
LEAVE c_table_names;
END IF;
SET @create_view_stmt = CONCAT(
'CREATE SQL SECURITY INVOKER VIEW ',
sys.quote_identifier(in_synonym),
'.',
sys.quote_identifier(v_table),
' AS SELECT * FROM ',
sys.quote_identifier(in_db_name),
'.',
sys.quote_identifier(v_table)
);
PREPARE create_view_stmt FROM @create_view_stmt;
EXECUTE create_view_stmt;
DEALLOCATE PREPARE create_view_stmt;
SET v_views_created = v_views_created + 1;
END LOOP;
CLOSE c_table_names;
SELECT CONCAT(
'Created ', v_views_created, ' view',
IF(v_views_created != 1, 's', ''), ' in the ',
sys.quote_identifier(in_synonym), ' database'
) AS summary;
END$$
DELIMITER ;
|
