Changeset: 61f14f5f1afd for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=61f14f5f1afd Added Files: sql/test/sys-schema/Tests/All sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.err 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.err sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.out Branch: Dec2016 Log Message:
Added data integrity tests for MonetDB system (and tmp) table data. More (FK and other tests) will be added later. These scripts can be used to check meta data integrity issues on any MonetDB database. It can also be used as part of the documentation of the MonetDB system tables. diffs (truncated from 636 to 300 lines): diff --git a/sql/test/sys-schema/Tests/All b/sql/test/sys-schema/Tests/All new file mode 100644 --- /dev/null +++ b/sql/test/sys-schema/Tests/All @@ -0,0 +1,2 @@ +check_PrimaryKey_uniqueness +check_AlternateKey_uniqueness diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql new file mode 100644 --- /dev/null +++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.sql @@ -0,0 +1,36 @@ +-- check all standard sys (and tmp) tables on Alternate Key uniqueness +-- All queries should return NO rows (so no duplicates found). +SELECT COUNT(*) AS duplicates, name FROM sys.schemas GROUP BY name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_type_name FROM sys.table_types GROUP BY table_type_name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema_id, name FROM sys._tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema_id, name FROM tmp._tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM sys._columns GROUP BY table_id, name HAVING COUNT(*) > 1; +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 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; +SELECT COUNT(*) AS duplicates, func_id, name FROM sys.args GROUP BY func_id, name HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, schema_id, systemname, sqlname FROM sys.types GROUP BY schema_id, systemname, sqlname HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, id, name FROM sys.objects GROUP BY id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id, name FROM tmp.objects GROUP BY id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM sys.keys GROUP BY table_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.keys GROUP BY table_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM sys.idxs GROUP BY table_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.idxs GROUP BY table_id, name HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, table_id, name FROM sys.triggers GROUP BY table_id, name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.triggers GROUP BY table_id, name HAVING COUNT(*) > 1; + +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, name FROM sys.auths GROUP BY name HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, def FROM sys.optimizers GROUP BY def HAVING COUNT(*) > 1; + diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.err b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.err @@ -0,0 +1,34 @@ +stderr of test 'check_AlternateKey_uniqueness` in directory 'sql/test/sys-schema` itself: + + +# 16:31:35 > +# 16:31:35 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=31486" "--set" "mapi_usock=/var/tmp/mtest-10384/.s.monetdb.31486" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema" +# 16:31:35 > + +# builtin opt gdk_dbpath = /export/scratch2/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 31486 +# cmdline opt mapi_usock = /var/tmp/mtest-10384/.s.monetdb.31486 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema +# cmdline opt gdk_debug = 536870922 + +# 16:31:35 > +# 16:31:35 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-10384" "--port=31486" +# 16:31:35 > + + +# 16:31:36 > +# 16:31:36 > "Done." +# 16:31:36 > + diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.stable.out @@ -0,0 +1,141 @@ +stdout of test 'check_AlternateKey_uniqueness` in directory 'sql/test/sys-schema` itself: + + +# 16:31:35 > +# 16:31:35 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=31486" "--set" "mapi_usock=/var/tmp/mtest-10384/.s.monetdb.31486" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema" +# 16:31:35 > + +# MonetDB 5 server v11.25.2 +# This is an unreleased version +# Serving database 'mTests_sql_test_sys-schema', using 8 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 15.589 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://toulouse.da.cwi.nl:31486/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-10384/.s.monetdb.31486 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 16:31:35 > +# 16:31:35 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-10384" "--port=31486" +# 16:31:35 > + +#SELECT COUNT(*) AS duplicates, name FROM sys.schemas GROUP BY name HAVING COUNT(*) > 1; +% sys.L3, sys.schemas # table_name +% duplicates, name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_type_name FROM sys.table_types GROUP BY table_type_name HAVING COUNT(*) > 1; +% sys.L3, sys.table_types # table_name +% duplicates, table_type_name # name +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, schema_id, name FROM sys._tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +% sys.L3, sys._tables, sys._tables # table_name +% duplicates, schema_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, schema_id, name FROM tmp._tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp._tables, tmp._tables # table_name +% duplicates, schema_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.tables GROUP BY schema_id, name HAVING COUNT(*) > 1; +% .L40, .tables, .tables # table_name +% duplicates, schema_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM sys._columns GROUP BY table_id, name HAVING COUNT(*) > 1; +% sys.L3, sys._columns, sys._columns # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM tmp._columns GROUP BY table_id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp._columns, tmp._columns # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM sys.columns GROUP BY table_id, name HAVING COUNT(*) > 1; +% .L14, .columns, .columns # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # 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 +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, schema_id, systemname, sqlname FROM sys.types GROUP BY schema_id, systemname, sqlname HAVING COUNT(*) > 1; +% sys.L3, sys.types, sys.types, sys.types # table_name +% duplicates, schema_id, systemname, sqlname # name +% bigint, int, varchar, varchar # type +% 1, 1, 0, 0 # length +#SELECT COUNT(*) AS duplicates, id, name FROM sys.objects GROUP BY id, name HAVING COUNT(*) > 1; +% sys.L3, sys.objects, sys.objects # table_name +% duplicates, id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, id, name FROM tmp.objects GROUP BY id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp.objects, tmp.objects # table_name +% duplicates, id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM sys.keys GROUP BY table_id, name HAVING COUNT(*) > 1; +% sys.L3, sys.keys, sys.keys # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.keys GROUP BY table_id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp.keys, tmp.keys # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM sys.idxs GROUP BY table_id, name HAVING COUNT(*) > 1; +% sys.L3, sys.idxs, sys.idxs # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.idxs GROUP BY table_id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp.idxs, tmp.idxs # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM sys.triggers GROUP BY table_id, name HAVING COUNT(*) > 1; +% sys.L3, sys.triggers, sys.triggers # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.triggers GROUP BY table_id, name HAVING COUNT(*) > 1; +% tmp.L3, tmp.triggers, tmp.triggers # table_name +% duplicates, table_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.sequences GROUP BY schema_id, name HAVING COUNT(*) > 1; +% sys.L3, sys.sequences, sys.sequences # table_name +% duplicates, schema_id, name # name +% bigint, int, varchar # type +% 1, 1, 0 # length +#SELECT COUNT(*) AS duplicates, dependency_type_name FROM sys.dependency_types GROUP BY dependency_type_name HAVING COUNT(*) > 1; +% sys.L3, sys.dependency_types # table_name +% duplicates, dependency_type_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 +% bigint, varchar # type +% 1, 0 # length +#SELECT COUNT(*) AS duplicates, def FROM sys.optimizers GROUP BY def HAVING COUNT(*) > 1; +% .L10, .optimizers # table_name +% duplicates, def # name +% bigint, clob # type +% 1, 0 # length + +# 16:31:36 > +# 16:31:36 > "Done." +# 16:31:36 > + diff --git a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql new file mode 100644 --- /dev/null +++ b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.sql @@ -0,0 +1,60 @@ +-- check all standard MonetDB sys (and tmp) tables on Primary Key uniqueness. +-- All queries should return NO rows (so no duplicates found). +SELECT COUNT(*) AS duplicates, id FROM sys.schemas GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, table_type_id FROM sys.table_types GROUP BY table_type_id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id FROM sys._tables GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id FROM tmp._tables GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id FROM sys.tables GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id FROM sys._columns GROUP BY id HAVING COUNT(*) > 1; +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, 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; +SELECT COUNT(*) AS duplicates, id FROM sys.types GROUP BY id HAVING COUNT(*) > 1; + +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, 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, 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; + +SELECT COUNT(*) AS duplicates, id FROM sys.sequences GROUP BY id HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, dependency_type_id FROM sys.dependency_types GROUP BY dependency_type_id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id, depend_id FROM sys.dependencies GROUP BY id, depend_id HAVING COUNT(*) > 1; + +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, 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; +SELECT COUNT(*) AS duplicates, id FROM sys.querylog_history GROUP BY id HAVING COUNT(*) >1; +SELECT COUNT(*) AS duplicates, qtag FROM sys.queue GROUP BY qtag HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, name FROM sys.optimizers GROUP BY name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, name FROM sys.environment GROUP BY name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, keyword FROM sys.keywords GROUP BY keyword HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, name FROM sys.db_user_info GROUP BY name HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, id FROM sys.connections GROUP BY id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, "user", login, active FROM sys.sessions GROUP BY "user", login, active HAVING COUNT(*) > 1; -- is this really always unique? + +SELECT COUNT(*) AS duplicates, column_id FROM sys.statistics GROUP BY column_id HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, rowid FROM sys.rejects GROUP BY rowid HAVING COUNT(*) > 1; + +SELECT COUNT(*) AS duplicates, schema, table, column FROM sys.storage GROUP BY schema, table, column HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema, table, column FROM sys.storagemodel GROUP BY schema, table, column HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema, table, column FROM sys.storagemodelinput GROUP BY schema, table, column HAVING COUNT(*) > 1; +SELECT COUNT(*) AS duplicates, schema, table FROM sys.tablestoragemodel GROUP BY schema, table HAVING COUNT(*) > 1; + +--SELECT COUNT(*) AS duplicates, event FROM sys.tracelog GROUP BY event HAVING COUNT(*) > 1; -- Error: Profiler not started + +-- NOT included here are the 5 netcdf_* tables and GEOM table spatial_ref_sys as those aren't available on all platforms. + diff --git a/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.err b/sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.stable.err new file mode 100644 _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
