Changeset: caa76e8e42ca for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=caa76e8e42ca
Modified Files:
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
Branch: Dec2016
Log Message:
Add some missing referential integrity checks.
diffs (161 lines):
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
--- a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
+++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
@@ -33,6 +33,7 @@ SELECT * FROM sys.args WHERE type NOT IN
SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
AND schema_id <> 0;
+SELECT * FROM sys.keys WHERE id NOT IN (SELECT id FROM sys.objects);
SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys._tables);
SELECT * FROM tmp.keys WHERE table_id NOT IN (SELECT id FROM tmp._tables);
@@ -41,6 +42,7 @@ SELECT * FROM tmp.keys WHERE table_id NO
SELECT * FROM sys.keys WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.key_types becomes available
SELECT * FROM tmp.keys WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.key_types becomes available
+SELECT * FROM sys.idxs WHERE id NOT IN (SELECT id FROM sys.objects);
SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys._tables);
SELECT * FROM tmp.idxs WHERE table_id NOT IN (SELECT id FROM tmp._tables);
@@ -81,19 +83,27 @@ SELECT * FROM sys.dependencies WHERE dep
--SELECT * FROM sys.dependencies WHERE depend_type = 15 and depend_id NOT IN
(SELECT id FROM sys.types); -- TODO: change check as it lists all 46 rows
SELECT * FROM sys.auths WHERE grantor NOT IN (SELECT id FROM sys.auths) AND
grantor > 0;
+SELECT * FROM sys.users WHERE name NOT IN (SELECT name FROM sys.auths);
SELECT * FROM sys.users WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
+SELECT * FROM sys.db_user_info WHERE name NOT IN (SELECT name FROM sys.auths);
SELECT * FROM sys.db_user_info WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
---SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT name FROM
sys.users); -- how is user_role.login_id connected to users.name? They have
different data types/domains
+SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT id FROM sys.auths);
+SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT a.id FROM sys.auths
a WHERE a.name IN (SELECT u.name FROM sys.users u));
+SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT a.id FROM sys.auths a
WHERE a.name NOT IN (SELECT u.name FROM sys.users u));
SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT id FROM sys.auths);
+
SELECT * FROM sys.privileges WHERE auth_id NOT IN (SELECT id FROM sys.auths);
SELECT * FROM sys.privileges WHERE grantor NOT IN (SELECT id FROM sys.auths)
AND grantor > 0;
-- SELECT * FROM sys.privileges WHERE privileges NOT IN (SELECT
privilege_code_id FROM sys.privilege_codes); -- 1 and 16 -- table
sys.privilege_codes added in default
SELECT * FROM sys.privileges WHERE privileges NOT IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,32); -- replace this check when table
sys.privilege_codes becomes available
SELECT * FROM sys.querylog_catalog WHERE owner NOT IN (SELECT name FROM
sys.users);
+SELECT * FROM sys.querylog_catalog WHERE pipe NOT IN (SELECT name FROM
sys.optimizers);
SELECT * FROM sys.querylog_calls WHERE id NOT IN (SELECT id FROM
sys.querylog_catalog);
SELECT * FROM sys.querylog_history WHERE id NOT IN (SELECT id FROM
sys.querylog_catalog);
+SELECT * FROM sys.querylog_history WHERE owner NOT IN (SELECT name FROM
sys.users);
+SELECT * FROM sys.querylog_history WHERE pipe NOT IN (SELECT name FROM
sys.optimizers);
SELECT * FROM sys.queue WHERE tag > 0 AND tag NOT IN (SELECT qtag FROM
sys.queue);
SELECT * FROM sys.queue WHERE "user" NOT IN (SELECT name FROM sys.users);
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
---
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
+++
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
@@ -19,14 +19,6 @@ stdout of test 'check_ForeignKey_referen
# MonetDB/SQL module loaded
Ready.
-#WARNING To speedup user.columnsize a bulk operator implementation is needed
-# X_61:bat[:lng] :=
mal.multiplex("user":str,"columnsize":str,X_46:bat[:str],X_53:bat[:lng],X_60:bat[:lng]);
-#WARNING To speedup user.heapsize a bulk operator implementation is needed
-# X_71:bat[:lng] :=
mal.multiplex("user":str,"heapsize":str,X_46:bat[:str],X_60:bat[:lng],X_70:bat[:int]);
-#WARNING To speedup user.hashsize a bulk operator implementation is needed
-# X_80:bat[:lng] :=
mal.multiplex("user":str,"hashsize":str,X_79:bat[:bit],X_53:bat[:lng]);
-#WARNING To speedup user.imprintsize a bulk operator implementation is needed
-# X_82:bat[:lng] :=
mal.multiplex("user":str,"imprintsize":str,X_53:bat[:lng],X_46:bat[:str]);
# 16:15:38 >
# 16:15:38 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-32669" "--port=34676"
@@ -147,6 +139,11 @@ Ready.
% id, systemname, sqlname, digits, scale, radix, eclass,
schema_id # name
% int, varchar, varchar, int, int, int, int, int #
type
% 1, 0, 0, 1, 1, 1, 1, 1 # length
+#SELECT * FROM sys.keys WHERE id NOT IN (SELECT id FROM sys.objects);
+% sys.keys, sys.keys, sys.keys, sys.keys, sys.keys,
sys.keys # table_name
+% id, table_id, type, name, rkey, action # name
+% int, int, int, varchar, int, int # type
+% 1, 1, 1, 0, 1, 1 # length
#SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
% sys.keys, sys.keys, sys.keys, sys.keys, sys.keys,
sys.keys # table_name
% id, table_id, type, name, rkey, action # name
@@ -172,6 +169,11 @@ Ready.
% id, table_id, type, name, rkey, action # name
% int, int, int, varchar, int, int # type
% 1, 1, 1, 0, 1, 1 # length
+#SELECT * FROM sys.idxs WHERE id NOT IN (SELECT id FROM sys.objects);
+% sys.idxs, sys.idxs, sys.idxs, sys.idxs # table_name
+% id, table_id, type, name # name
+% int, int, int, varchar # type
+% 1, 1, 1, 0 # length
#SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
% sys.idxs, sys.idxs, sys.idxs, sys.idxs # table_name
% id, table_id, type, name # name
@@ -267,16 +269,41 @@ Ready.
% id, name, grantor # name
% int, varchar, int # type
% 1, 0, 1 # length
+#SELECT * FROM sys.users WHERE name NOT IN (SELECT name FROM sys.auths);
+% .users, .users, .users # table_name
+% name, fullname, default_schema # name
+% varchar, varchar, int # type
+% 0, 0, 1 # length
#SELECT * FROM sys.users WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
% .users, .users, .users # table_name
% name, fullname, default_schema # name
% varchar, varchar, int # type
% 0, 0, 1 # length
+#SELECT * FROM sys.db_user_info WHERE name NOT IN (SELECT name FROM sys.auths);
+% sys.db_user_info, sys.db_user_info, sys.db_user_info # table_name
+% name, fullname, default_schema # name
+% varchar, varchar, int # type
+% 0, 0, 1 # length
#SELECT * FROM sys.db_user_info WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
% sys.db_user_info, sys.db_user_info, sys.db_user_info # table_name
% name, fullname, default_schema # name
% varchar, varchar, int # type
% 0, 0, 1 # length
+#SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT id FROM sys.auths);
+% sys.user_role, sys.user_role # table_name
+% login_id, role_id # name
+% int, int # type
+% 1, 1 # length
+#SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT a.id FROM sys.auths
a WHERE a.name IN (SELECT u.name FROM sys.users u));
+% sys.user_role, sys.user_role # table_name
+% login_id, role_id # name
+% int, int # type
+% 1, 1 # length
+#SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT a.id FROM sys.auths
a WHERE a.name NOT IN (SELECT u.name FROM sys.users u));
+% sys.user_role, sys.user_role # table_name
+% login_id, role_id # name
+% int, int # type
+% 1, 1 # length
#SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT id FROM sys.auths);
% sys.user_role, sys.user_role # table_name
% login_id, role_id # name
@@ -302,6 +329,11 @@ Ready.
% id, owner, defined, query, pipe, plan, mal, optimize # name
% oid, clob, timestamp, clob, clob, clob, int, bigint # type
% 3, 0, 26, 0, 0, 0, 1, 1 # length
+#SELECT * FROM sys.querylog_catalog WHERE pipe NOT IN (SELECT name FROM
sys.optimizers);
+% .querylog_catalog, .querylog_catalog, .querylog_catalog,
.querylog_catalog, .querylog_catalog, .querylog_catalog,
.querylog_catalog, .querylog_catalog # table_name
+% id, owner, defined, query, pipe, plan, mal, optimize # name
+% oid, clob, timestamp, clob, clob, clob, int, bigint # type
+% 3, 0, 26, 0, 0, 0, 1, 1 # length
#SELECT * FROM sys.querylog_calls WHERE id NOT IN (SELECT id FROM
sys.querylog_catalog);
% .querylog_calls, .querylog_calls, .querylog_calls,
.querylog_calls, .querylog_calls, .querylog_calls,
.querylog_calls, .querylog_calls, .querylog_calls # table_name
% id, start, stop, arguments, tuples, run, ship, cpu, io #
name
@@ -312,6 +344,16 @@ Ready.
% id, owner, defined, query, pipe, plan, mal, optimize,
start, stop, arguments, tuples, run, ship, cpu, io # name
% oid, clob, timestamp, clob, clob, clob, int, bigint,
timestamp, timestamp, clob, bigint, bigint, bigint, int, int #
type
% 3, 0, 26, 0, 0, 0, 1, 1, 26, 26,
0, 1, 1, 1, 1, 1 # length
+#SELECT * FROM sys.querylog_history WHERE owner NOT IN (SELECT name FROM
sys.users);
+% .querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history # table_name
+% id, owner, defined, query, pipe, plan, mal, optimize,
start, stop, arguments, tuples, run, ship, cpu, io # name
+% oid, clob, timestamp, clob, clob, clob, int, bigint,
timestamp, timestamp, clob, bigint, bigint, bigint, int, int #
type
+% 3, 0, 26, 0, 0, 0, 1, 1, 26, 26,
0, 1, 1, 1, 1, 1 # length
+#SELECT * FROM sys.querylog_history WHERE pipe NOT IN (SELECT name FROM
sys.optimizers);
+% .querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history, .querylog_history, .querylog_history,
.querylog_history # table_name
+% id, owner, defined, query, pipe, plan, mal, optimize,
start, stop, arguments, tuples, run, ship, cpu, io # name
+% oid, clob, timestamp, clob, clob, clob, int, bigint,
timestamp, timestamp, clob, bigint, bigint, bigint, int, int #
type
+% 3, 0, 26, 0, 0, 0, 1, 1, 26, 26,
0, 1, 1, 1, 1, 1 # length
#SELECT * FROM sys.queue WHERE tag > 0 AND tag NOT IN (SELECT qtag FROM
sys.queue);
% .queue, .queue, .queue, .queue, .queue, .queue, .queue, .queue #
table_name
% qtag, user, started, estimate, progress, status,
tag, query # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
