Changeset: 09efc2afab49 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=09efc2afab49 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_PrimaryKey_uniqueness.sql sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out Branch: default Log Message:
Adding data integrity tests for 5 new system tables. diffs (143 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 @@ -29,6 +29,11 @@ SELECT COUNT(*) AS duplicates, table_id, SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.sequences GROUP BY schema_id, name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, dependency_type_name FROM sys.dependency_types GROUP BY dependency_type_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, function_type_name FROM sys.function_types GROUP BY function_type_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, language_name FROM sys.function_languages GROUP BY language_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, key_type_name FROM sys.key_types GROUP BY key_type_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, index_type_name FROM sys.index_types GROUP BY index_type_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, privilege_code_name FROM sys.privilege_codes GROUP BY privilege_code_name HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, name FROM sys.auths GROUP BY name 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 @@ -124,6 +124,31 @@ Ready. % duplicates, dependency_type_name # name % bigint, varchar # type % 1, 0 # length +#SELECT COUNT(*) AS duplicates, function_type_name FROM sys.function_types GROUP BY function_type_name HAVING COUNT(*) > 1; +% sys.L3, sys.function_types # table_name +% duplicates, function_type_name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, language_name FROM sys.function_languages GROUP BY language_name HAVING COUNT(*) > 1; +% sys.L3, sys.function_languages # table_name +% duplicates, language_name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, key_type_name FROM sys.key_types GROUP BY key_type_name HAVING COUNT(*) > 1; +% sys.L3, sys.key_types # table_name +% duplicates, key_type_name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, index_type_name FROM sys.index_types GROUP BY index_type_name HAVING COUNT(*) > 1; +% sys.L3, sys.index_types # table_name +% duplicates, index_type_name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, privilege_code_name FROM sys.privilege_codes GROUP BY privilege_code_name HAVING COUNT(*) > 1; +% sys.L3, sys.privilege_codes # table_name +% duplicates, privilege_code_name # name +% bigint, varchar # type +% 1, 0 # length #SELECT COUNT(*) AS duplicates, name FROM sys.auths GROUP BY name HAVING COUNT(*) > 1; % sys.L3, sys.auths # table_name % duplicates, name # 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 @@ -9,6 +9,8 @@ SELECT COUNT(*) AS duplicates, id FROM s SELECT COUNT(*) AS duplicates, id FROM tmp._columns GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.columns GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, function_type_id FROM sys.function_types GROUP BY function_type_id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, language_id FROM sys.function_languages GROUP BY language_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.functions GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, function_id FROM sys.systemfunctions GROUP BY function_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.args GROUP BY id HAVING COUNT(*) > 1; @@ -16,10 +18,15 @@ SELECT COUNT(*) AS duplicates, id FROM s SELECT COUNT(*) AS duplicates, id, nr FROM sys.objects GROUP BY id, nr HAVING COUNT(*) > 1; -- without column: nr it returns duplicates SELECT COUNT(*) AS duplicates, id, nr FROM tmp.objects GROUP BY id, nr HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, key_type_id FROM sys.key_types GROUP BY key_type_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.keys GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM tmp.keys GROUP BY id HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, index_type_id FROM sys.index_types GROUP BY index_type_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.idxs GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM tmp.idxs GROUP BY id HAVING COUNT(*) > 1; + SELECT COUNT(*) AS duplicates, id FROM sys.triggers GROUP BY id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM tmp.triggers GROUP BY id HAVING COUNT(*) > 1; @@ -31,6 +38,8 @@ 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, privilege_code_id FROM sys.privilege_codes GROUP BY privilege_code_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, obj_id FROM sys.privileges GROUP BY obj_id HAVING COUNT(*) > 1; SELECT COUNT(*) AS duplicates, id FROM sys.querylog_catalog 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 @@ -112,6 +112,16 @@ Ready. % duplicates, id # name % bigint, int # type % 1, 1 # length +#SELECT COUNT(*) AS duplicates, function_type_id FROM sys.function_types GROUP BY function_type_id HAVING COUNT(*) > 1; +% sys.L3, sys.function_types # table_name +% duplicates, function_type_id # name +% bigint, smallint # type +% 1, 1 # length +#SELECT COUNT(*) AS duplicates, language_id FROM sys.function_languages GROUP BY language_id HAVING COUNT(*) > 1; +% sys.L3, sys.function_languages # table_name +% duplicates, language_id # name +% bigint, smallint # type +% 1, 1 # length #SELECT COUNT(*) AS duplicates, id FROM sys.functions GROUP BY id HAVING COUNT(*) > 1; % sys.L3, sys.functions # table_name % duplicates, id # name @@ -142,6 +152,11 @@ Ready. % duplicates, id, nr # name % bigint, int, int # type % 1, 1, 1 # length +#SELECT COUNT(*) AS duplicates, key_type_id FROM sys.key_types GROUP BY key_type_id HAVING COUNT(*) > 1; +% sys.L3, sys.key_types # table_name +% duplicates, key_type_id # name +% bigint, smallint # type +% 1, 1 # length #SELECT COUNT(*) AS duplicates, id FROM sys.keys GROUP BY id HAVING COUNT(*) > 1; % sys.L3, sys.keys # table_name % duplicates, id # name @@ -152,6 +167,11 @@ Ready. % duplicates, id # name % bigint, int # type % 1, 1 # length +#SELECT COUNT(*) AS duplicates, index_type_id FROM sys.index_types GROUP BY index_type_id HAVING COUNT(*) > 1; +% sys.L3, sys.index_types # table_name +% duplicates, index_type_id # name +% bigint, smallint # type +% 1, 1 # length #SELECT COUNT(*) AS duplicates, id FROM sys.idxs GROUP BY id HAVING COUNT(*) > 1; % sys.L3, sys.idxs # table_name % duplicates, id # name @@ -202,6 +222,11 @@ Ready. % duplicates, login_id, role_id # name % bigint, int, int # type % 1, 1, 1 # length +#SELECT COUNT(*) AS duplicates, privilege_code_id FROM sys.privilege_codes GROUP BY privilege_code_id HAVING COUNT(*) > 1; +% sys.L3, sys.privilege_codes # table_name +% duplicates, privilege_code_id # name +% bigint, int # type +% 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 _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
