Changeset: 699fae03a99a for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=699fae03a99a Modified Files: sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out Branch: Dec2016 Log Message:
Correcting PKey and adding FKey integrity checks on sys.privileges. Adding AKey check for id's used in multiple system tables combined (which serves as a FK from sys.privileges.obj_id). diffs (92 lines): diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql --- a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql +++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql @@ -9,6 +9,10 @@ SELECT COUNT(*) AS duplicates, table_id, SELECT COUNT(*) AS duplicates, table_id, name FROM tmp._columns GROUP BY table_id, name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, table_id, name FROM sys.columns GROUP BY table_id, name HAVING COUNT(*) > 1; +-- The id values from sys.schemas, sys._tables, sys._columns and sys.functions combined must be exclusive (see FK from sys.privileges.obj_id) +SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id HAVING COUNT(*) > 1; + -- the next query returns duplicates for overloaded functions (same function but with different args), hence it has been disabled --SELECT COUNT(*) AS duplicates, schema_id, name, func, mod, language, type, side_effect, varres, vararg FROM sys.functions -- GROUP BY schema_id, name, func, mod, language, type, side_effect, varres, vararg HAVING COUNT(*) > 1; diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out --- a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out +++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out @@ -64,6 +64,16 @@ Ready. % duplicates, table_id, name # name % bigint, int, varchar # type % 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id HAVING COUNT(*) > 1; +% .L26, .t # table_name +% duplicates, id # name +% bigint, int # type +% 1, 1 # length +#SELECT COUNT(*) AS duplicates, T.id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys.tables UNION ALL SELECT id FROM sys.columns UNION ALL SELECT id FROM sys.functions) T GROUP BY T.id HAVING COUNT(*) > 1; +% .L26, .t # table_name +% duplicates, id # name +% bigint, int # type +% 1, 1 # length #SELECT COUNT(*) AS duplicates, func_id, name FROM sys.args GROUP BY func_id, name HAVING COUNT(*) > 1; % sys.L3, sys.args, sys.args # table_name % duplicates, func_id, name # name 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 @@ -93,6 +93,7 @@ SELECT * FROM sys.user_role WHERE login_ SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT id FROM sys.auths); 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.privileges WHERE obj_id NOT IN (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions); 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 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 @@ -309,6 +309,11 @@ Ready. % login_id, role_id # name % int, int # type % 1, 1 # length +#SELECT * FROM sys.privileges WHERE obj_id NOT IN (SELECT id FROM sys.schemas UNION ALL SELECT id FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM sys.functions); +% sys.privileges, sys.privileges, sys.privileges, sys.privileges, sys.privileges # table_name +% obj_id, auth_id, privileges, grantor, grantable # name +% int, int, int, int, int # type +% 1, 1, 1, 1, 1 # length #SELECT * FROM sys.privileges WHERE auth_id NOT IN (SELECT id FROM sys.auths); % sys.privileges, sys.privileges, sys.privileges, sys.privileges, sys.privileges # table_name % obj_id, auth_id, privileges, grantor, grantable # name diff --git a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql --- a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql +++ b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql @@ -31,7 +31,7 @@ SELECT COUNT(*) AS duplicates, id, depen SELECT COUNT(*) AS duplicates, id FROM sys.auths GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, name FROM sys.users GROUP BY name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, login_id, role_id FROM sys.user_role GROUP BY login_id, role_id HAVING COUNT(*) > 1; -SELECT COUNT(*) AS duplicates, obj_id FROM sys.privileges GROUP BY obj_id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, obj_id, auth_id FROM sys.privileges GROUP BY obj_id, auth_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.querylog_catalog GROUP BY id HAVING COUNT(*) >1; SELECT COUNT(*) AS duplicates, id FROM sys.querylog_calls GROUP BY id HAVING COUNT(*) > 1; diff --git a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out --- a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out +++ b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out @@ -202,11 +202,11 @@ Ready. % duplicates, login_id, role_id # name % bigint, int, int # type % 1, 1, 1 # length -#SELECT COUNT(*) AS duplicates, obj_id FROM sys.privileges GROUP BY obj_id HAVING COUNT(*) > 1; -% sys.L3, sys.privileges # table_name -% duplicates, obj_id # name -% bigint, int # type -% 1, 1 # length +#SELECT COUNT(*) AS duplicates, obj_id, auth_id FROM sys.privileges GROUP BY obj_id, auth_id HAVING COUNT(*) > 1; +% sys.L3, sys.privileges, sys.privileges # table_name +% duplicates, obj_id, auth_id # name +% bigint, int, int # type +% 1, 1, 1 # length #SELECT COUNT(*) AS duplicates, id FROM sys.querylog_catalog GROUP BY id HAVING COUNT(*) >1; % .L10, .querylog_catalog # table_name % duplicates, id # name _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
