Changeset: a4228684b1a8 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a4228684b1a8 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:
merge 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
