MDEV-11952 Oracle-style packages: stage#5mariadb-10.3.5bb-10.3-compatibility

- CREATE PACKAGE [BODY] statements are now
  entirely written to mysql.proc with type='PACKAGE' and type='PACKAGE BODY'.
- CREATE PACKAGE BODY now supports IF NOT EXISTS
- DROP PACKAGE BODY now supports IF EXISTS
- CREATE OR REPLACE PACKAGE [BODY] is now supported
- CREATE PACKAGE [BODY] now support the DEFINER clause:

    CREATE DEFINER user@host PACKAGE pkg ... END;
    CREATE DEFINER user@host PACKAGE BODY pkg ... END;

- CREATE PACKAGE [BODY] now supports SQL SECURITY and COMMENT clauses, e.g.:

    CREATE PACKAGE p1 SQL SECURITY INVOKER COMMENT "comment" AS ... END;

- Package routines are now created from the package CREATE PACKAGE BODY
  statement and don't produce individual records in mysql.proc.

- CREATE PACKAGE BODY now supports package-wide variables.
  Package variables can be read and set inside package routines.
  Package variables are stored in a separate sp_rcontext,
  which is cached in THD on the first packate routine call.

- CREATE PACKAGE BODY now supports the initialization section.

- All public routines (i.e. declared in CREATE PACKAGE)
  must have implementations in CREATE PACKAGE BODY

- Only public package routines are available outside of the package

- {CREATE|DROP} PACKAGE [BODY] now respects CREATE ROUTINE and ALTER ROUTINE
  privileges

- "GRANT EXECUTE ON PACKAGE BODY pkg" is now supported

- SHOW CREATE PACKAGE [BODY] is now supported

- SHOW PACKAGE [BODY] STATUS is now supported

- CREATE and DROP for PACKAGE [BODY] now works for non-current databases

- mysqldump now supports packages

- "SHOW {PROCEDURE|FUNCTION) CODE pkg.routine" now works for package routines

- "SHOW PACKAGE BODY CODE pkg" now works (the package initialization section)

- A new package body level MDL was added

- Recursive calls for package procedures are now possible

- Routine forward declarations in CREATE PACKATE BODY are now supported.

- Package body variables now work as SP OUT parameters

- Package body variables now work as SELECT INTO targets

- Package body variables now support ROW, %ROWTYPE, %TYPE

1 files changed, 110 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/t/sp-package-mdl.test b/mysql-test/suite/compat/oracle/t/sp-package-mdl.test
new file mode 100644
index 00000000000..de4f7aaabad
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-package-mdl.test
@@ -0,0 +1,110 @@
+--source include/have_metadata_lock_info.inc
+
+#
+# This test demonstrates that:
+# - A call to a package routine acquires a shared MDL lock on the entire package
+# - "DROP PACKAGE" waits until the currently running package routines end
+#
+
+SET sql_mode=ORACLE;
+DO GET_LOCK('lock',300);
+
+
+#
+# conn1 will execute package pkg1 routines and
+# and therefore acquire a shared MDL on "package body pkg1"
+#
+
+connect (conn1,localhost,root,,);
+SET sql_mode=ORACLE;
+let $conn1_id= `SELECT CONNECTION_ID()`;
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    DO GET_LOCK('lock',300);
+  END;
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    CALL p1;
+    RETURN 1;
+  END;
+END;
+$$
+DELIMITER ;$$
+send SELECT pkg1.f1();
+
+#
+# wait for conn1 to actually start execution of pkg1.p1
+#
+
+connection default;
+let $wait_timeout= 60;
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID=$conn1_id AND INFO LIKE '%GET_LOCK%' AND STATE='User lock';
+--source include/wait_condition.inc
+
+
+#
+# conn2 will do "DROP PACKAGE pkg1".
+# It will acquire an exclusive MDL on "package body pkg1", and therefore
+# it should wait until conn1 ends the package routine execution
+#
+
+connect (conn2,localhost,root,,);
+let $conn2_id= `SELECT CONNECTION_ID()`;
+SET sql_mode=ORACLE;
+send DROP PACKAGE pkg1;
+
+#
+# wait for conn2 to actually enter the "DROP" statement and get locked by conn1
+#
+connection default;
+let $wait_timeout= 60;
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE ID=$conn2_id
+  AND INFO LIKE '%DROP PACKAGE%'
+  AND STATE='Waiting for stored package body metadata lock';
+--source include/wait_condition.inc
+
+#
+# Now we have three threads involved.
+# The following I_S query will check that the threads are in these states:
+#
+#  default (0) - is holding a user lock 'lock'
+#  conn1   (1) - is executing the package procedure test.pkg1.p1,
+#                is holding a shared MDL on 'package body pkg1',
+#                is waiting for the user lock 'lock' to be released
+#  conn2   (2) - is waiting for 'conn1' to end execution of test.pkg1.* routines,
+#                to acquire an exclusive MDL on 'package body pkg1',
+#                to DROP the package pkg1
+#
+--vertical_results
+SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME
+  FROM INFORMATION_SCHEMA.PROCESSLIST
+  LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO
+   ON (ID=THREAD_ID)
+  ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE;
+--horizontal_results
+
+#
+# Now let conn1 finish the package routine execution
+#
+DO RELEASE_LOCK('lock');
+connection conn1;
+reap;
+disconnect conn1;
+
+#
+# Now conn2 should actually DROP the package
+#
+connection conn2;
+reap;
+disconnect conn2;
+
+connection default;