Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 187 insertions, 0 deletions

diff --git a/scripts/sys_schema/procedures/create_synonym_db.sql b/scripts/sys_schema/procedures/create_synonym_db.sql
new file mode 100644
index 00000000..e373a9b4
--- /dev/null
+++ b/scripts/sys_schema/procedures/create_synonym_db.sql
@@ -0,0 +1,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 ;