#
# WL#11864 Implement I_S.VIEW_TABLE_USAGE and I_S.VIEW_ROUTINE_USAGE
#
# Basic schema definition used by test cases.
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1 (c1 INT);
CREATE FUNCTION f1() RETURNS INT RETURN 1;
CREATE FUNCTION f2() RETURNS INT RETURN 2;
CREATE VIEW v1 AS SELECT f1() AS f1;
CREATE VIEW v2 AS SELECT f1, f2() AS f2  FROM v1;
CREATE VIEW v3 AS SELECT c1, f2 FROM t1, v2;
# Case 1: Show table and views used by view v2 and v3.
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v2	def	db1	v1
def	db1	v3	def	db1	t1
def	db1	v3	def	db1	v2
# Case 2: Show routines used by views.
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
def	db1	v1	def	db1	f1
def	db1	v2	def	db1	f2
# Case 3: Rename the column of t1 and list view dependency.
# The query should throw warning on view v3.
ALTER TABLE t1 RENAME COLUMN c1 TO c2;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' AND VIEW_NAME='v3';
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v3	def	db1	t1
def	db1	v3	def	db1	v2
Warnings:
Warning	1356	View 'db1.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
ALTER TABLE t1 RENAME COLUMN c2 TO c1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' AND VIEW_NAME='v3';
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v3	def	db1	t1
def	db1	v3	def	db1	v2
# Case 4: Drop function f1 and list dependency.
# The query should throw warning on view v1.
DROP FUNCTION f1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='v1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
Warnings:
Warning	1356	View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
CREATE FUNCTION f1() RETURNS INT return 1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='v1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
def	db1	v1	def	db1	f1
# Case 5: Access checks.
CREATE USER 'testuser'@'localhost';
# 5.1 Grant permission on v2, but not v1. VIEW_TABLE_USAGE will not
# list any entry because user has no permission on v1.
GRANT ALL ON db1.v2 TO 'testuser'@'localhost';
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
# 5.2 Give permission on v1 and that VIEW_TABLE_USAGE shows v2 entry.
GRANT ALL ON db1.v1 TO 'testuser'@'localhost';
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v2	def	db1	v1
# 5.3 VIEW_ROUTINE_USAGE does not list v1, as it have no grant on f1.
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
# 5.4 Grant access to f1 and check that VIEW_ROUTINE_USAGE shows it.
GRANT EXECUTE ON FUNCTION db1.f1 TO 'testuser'@'localhost';
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
def	db1	v1	def	db1	f1
# 5.5 Grant access on v3, see that dependency 't1' is not listed.
GRANT ALL ON db1.v3 TO 'testuser'@'localhost';
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v2	def	db1	v1
def	db1	v3	def	db1	v2
# 5.6 Grant access on t1, see that dependency 't1' is listed.
GRANT SELECT on db1.t1 TO 'testuser'@'localhost';
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v2	def	db1	v1
def	db1	v3	def	db1	t1
def	db1	v3	def	db1	v2
DROP USER 'testuser'@'localhost';
# Cleanup
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP VIEW v1, v2, v3;
DROP TABLE t1;
# Case 6:
# 6.1 Rename table t1 to t2 and list view dependency.
# The query should throw warning on view v1.
CREATE TABLE t1 (c1 INT);
CREATE VIEW v1 AS SELECT c1 FROM t1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
RENAME TABLE t1 to t2;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
Warnings:
Warning	1356	View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
RENAME TABLE t2 to t1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
# 6.2 Drop table t1 and list view dependency.
DROP TABLE t1;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
Warnings:
Warning	1356	View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
DROP VIEW v1;
# Case 7: List dependent table and function names
# that are permitted to the current user.
CREATE USER testuser1@localhost;
GRANT CREATE ON db1.* TO testuser1@localhost;
GRANT SELECT ON db1.* TO testuser1@localhost;
GRANT CREATE VIEW ON db1.* TO testuser1@localhost;
GRANT CREATE ROUTINE ON db1.* TO testuser1@localhost;
GRANT DROP ON db1.* TO testuser1@localhost;
CREATE USER testuser2@localhost;
GRANT CREATE ON db1.* TO testuser2@localhost;
GRANT SELECT ON db1.* TO testuser2@localhost;
GRANT CREATE VIEW ON db1.* TO testuser2@localhost;
GRANT CREATE ROUTINE ON db1.* TO testuser2@localhost;
GRANT DROP ON db1.* TO testuser2@localhost;
CREATE TABLE t1(c1 INT);
CREATE VIEW v1 AS SELECT c1 FROM t1;
CREATE FUNCTION f1() RETURNS INT RETURN 1;
CREATE VIEW v2 AS SELECT f1();
CREATE TABLE t2(c1 INT);
CREATE VIEW v3 AS SELECT c1 FROM t2;
CREATE FUNCTION f2() RETURNS INT RETURN 1;
CREATE VIEW v4 AS SELECT f2();
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
def	db1	v2	def	db1	f1
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v3	def	db1	t2
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
WHERE TABLE_SCHEMA='db1';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME
def	db1	v4	def	db1	f2
DROP VIEW v1,v2;
DROP FUNCTION f1;
DROP TABLE t1;
DROP VIEW v3,v4;
DROP FUNCTION f2;
DROP TABLE t2;
DROP USER testuser1@localhost;
DROP USER testuser2@localhost;
# Case 8: Revoke view privilege
CREATE USER testuser1@localhost;
CREATE TABLE t1(c1 INT);
CREATE VIEW v1 AS SELECT c1 FROM t1;
GRANT ALL ON db1.v1 TO testuser1@localhost;
GRANT ALL ON db1.t1 TO testuser1@localhost;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
def	db1	v1	def	db1	t1
REVOKE ALL ON db1.v1 FROM testuser1@localhost;
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
VIEW_CATALOG	VIEW_SCHEMA	VIEW_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
REVOKE ALL ON db1.t1 FROM testuser1@localhost;
DROP USER testuser1@localhost;
DROP VIEW v1;
DROP TABLE t1;
DROP DATABASE db1;