From 92c686460e7d4bb904a04d85c91a3b9c5c0582cb Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dani=C3=ABl=20van=20Eeden?= <git@myname.nl>
Date: Sat, 13 May 2017 12:04:48 +0200
Subject: [PATCH] Add is_system_schema() function

Implements this RFE:
Bug #78314 	Register if a schema is a system schema or not
https://bugs.mysql.com/bug.php?id=78314

Note that a hardcoded list of system schemas will make it difficult
to later add/remove schemas. It would be better if a schema could register
itself as a system schema (ALTER SCHEMA foo IS SYSTEM SCHEMA) or by
modifying a table like sys.system_schema_list

Would be more useful if we could do SHOW USER SCHEMAS / SHOW SYSTEM SCHEMAS

Not including the results file for the test case.
---
 functions/is_system_schema.sql                     | 81 ++++++++++++++++++++++
 .../suite/sysschema/t/fn_is_system_schema.test     | 23 ++++++
 sys_56.sql                                         |  1 +
 sys_57.sql                                         |  1 +
 4 files changed, 106 insertions(+)
 create mode 100644 functions/is_system_schema.sql
 create mode 100644 mysql-test/suite/sysschema/t/fn_is_system_schema.test

diff --git a/functions/is_system_schema.sql b/functions/is_system_schema.sql
new file mode 100644
index 0000000..4f0f371
--- /dev/null
+++ b/functions/is_system_schema.sql
@@ -0,0 +1,81 @@
+-- Copyright (c) 2015, 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 FUNCTION IF EXISTS is_system_schema;
+
+DELIMITER $$
+
+CREATE DEFINER='root'@'localhost' FUNCTION is_system_schema(
+        in_schema TEXT
+    )
+    RETURNS BOOLEAN
+    COMMENT '
+             Description
+             -----------
+
+             Takes a schema name and returns TRUE if this is considered a system schema
+
+             Useful for excluding systems schema in information_schema queries
+
+             Parameters
+             -----------
+
+             in_schema (TEXT):
+               The schema name to check
+
+             Returns
+             -----------
+
+             BOOLEAN
+
+             Example
+             --------
+
+             mysql> select schema_name, sys.is_system_schema(SCHEMA_NAME) from information_schema.schemata;
+             +--------------------+-----------------------------------+
+             | schema_name        | sys.is_system_schema(SCHEMA_NAME) |
+             +--------------------+-----------------------------------+
+             | information_schema |                                 1 |
+             | mysql              |                                 1 |
+             | performance_schema |                                 1 |
+             | sys                |                                 1 |
+             | test               |                                 0 |
+             +--------------------+-----------------------------------+
+             5 rows in set (0.00 sec)
+
+            '
+    SQL SECURITY INVOKER
+    DETERMINISTIC
+    CONTAINS SQL
+BEGIN
+
+    IF (in_schema IN (
+        'mysql',
+        'information_schema',
+        'performance_schema',
+        'sys',
+        'ndbinfo',                         -- MySQL Cluster / NDB
+        'innodb_memcache',                 -- InnoDB Memcache Plugin
+        'mysql_innodb_cluster_metadata',   -- InnoDB Cluster
+        'query_rewrite'                    -- Query Rewrite Plugin
+    )) THEN
+        RETURN TRUE;
+    END IF;
+
+    RETURN FALSE;
+
+END$$
+
+DELIMITER ;
diff --git a/mysql-test/suite/sysschema/t/fn_is_system_schema.test b/mysql-test/suite/sysschema/t/fn_is_system_schema.test
new file mode 100644
index 0000000..7ec56a2
--- /dev/null
+++ b/mysql-test/suite/sysschema/t/fn_is_system_schema.test
@@ -0,0 +1,23 @@
+-- source include/not_embedded.inc
+# Tests for sys schema
+# Verify the sys.is_system_schema() function perfoms as expected
+
+# Should return FALSE
+SELECT sys.is_system_schma('test');
+
+# Thse should return TRUE
+SELECT sys.is_system_schma('mysql');
+
+SELECT sys.is_system_schma('information_schema');
+
+SELECT sys.is_system_schma('performance_schema');
+
+SELECT sys.is_system_schma('mysql_innodb_cluster_metadata');
+
+SELECT sys.is_system_schma('innodb_memcache');
+
+SELECT sys.is_system_schma('query_rewrite');
+
+SELECT sys.is_system_schma('ndbinfo');
+
+SELECT sys.is_system_schma('sys');
diff --git a/sys_56.sql b/sys_56.sql
index 1de01d2..296bed8 100644
--- a/sys_56.sql
+++ b/sys_56.sql
@@ -29,6 +29,7 @@ SOURCE ./functions/format_bytes.sql
 SOURCE ./functions/format_path.sql
 SOURCE ./functions/format_statement.sql
 SOURCE ./functions/format_time.sql
+SOURCE ./functions/is_system_schema.sql
 SOURCE ./functions/list_add.sql
 SOURCE ./functions/list_drop.sql
 SOURCE ./functions/ps_is_account_enabled.sql
diff --git a/sys_57.sql b/sys_57.sql
index 277cc62..e284d39 100644
--- a/sys_57.sql
+++ b/sys_57.sql
@@ -29,6 +29,7 @@ SOURCE ./functions/format_bytes.sql
 SOURCE ./functions/format_path_57.sql
 SOURCE ./functions/format_statement.sql
 SOURCE ./functions/format_time.sql
+SOURCE ./functions/is_system_schema.sql
 SOURCE ./functions/list_add.sql
 SOURCE ./functions/list_drop.sql
 SOURCE ./functions/ps_is_account_enabled_57.sql