Changeset: 57c1a368d729 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=57c1a368d729
Added Files:
sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.test
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
sql/test/sys-schema/Tests/check_MaxStrLength_violations.test
sql/test/sys-schema/Tests/check_Not_Nullable_columns.test
sql/test/sys-schema/Tests/check_PrimaryKey_uniqueness.test
sql/test/sys-schema/Tests/geom_tables_checks.test
sql/test/sys-schema/Tests/netcdf_tables_checks.test
sql/test/sys-schema/Tests/systemfunctions.test
sql/test/sys-schema/Tests/update_statistics.test
sql/test/sys-schema/Tests/utilities.test
Branch: mtest
Log Message:
Converted sql/test/sys-schema.
diffs (truncated from 2357 to 300 lines):
diff --git a/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.test
b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.test
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/check_AlternateKey_uniqueness.test
@@ -0,0 +1,145 @@
+query IT rowsort
+SELECT COUNT(*) AS duplicates, name FROM sys.schemas GROUP BY name HAVING
COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, table_type_name FROM sys.table_types GROUP BY
table_type_name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, name FROM sys._tables GROUP BY
schema_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, name FROM tmp._tables GROUP BY
schema_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.tables GROUP BY
schema_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM sys._columns GROUP BY
table_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM tmp._columns GROUP BY
table_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM sys.columns GROUP BY
table_id, name HAVING COUNT(*) > 1
+----
+
+query III rowsort
+SELECT COUNT(*) AS duplicates, table_id, number FROM sys._columns GROUP BY
table_id, number HAVING COUNT(*) > 1
+----
+
+query III rowsort
+SELECT COUNT(*) AS duplicates, table_id, number FROM tmp._columns GROUP BY
table_id, number HAVING COUNT(*) > 1
+----
+
+query III rowsort
+SELECT COUNT(*) AS duplicates, table_id, number FROM sys.columns GROUP BY
table_id, number HAVING COUNT(*) > 1
+----
+
+query II rowsort
+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
+----
+
+query II rowsort
+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
+----
+
+query IITI rowsort
+SELECT COUNT(*) AS duplicates, func_id, name, inout FROM sys.args GROUP BY
func_id, name, inout HAVING COUNT(*) > 1
+----
+
+query IITT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, systemname, sqlname FROM sys.types
GROUP BY schema_id, systemname, sqlname HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, id, name FROM sys.objects GROUP BY id, name
HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, id, name FROM tmp.objects GROUP BY id, name
HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM sys.keys GROUP BY table_id,
name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.keys GROUP BY table_id,
name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM sys.idxs GROUP BY table_id,
name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.idxs GROUP BY table_id,
name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM sys.triggers GROUP BY
table_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, name FROM tmp.triggers GROUP BY
table_id, name HAVING COUNT(*) > 1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, schema_id, name FROM sys.sequences GROUP BY
schema_id, name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, dependency_type_name FROM sys.dependency_types
GROUP BY dependency_type_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, function_type_name FROM sys.function_types
GROUP BY function_type_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, language_name FROM sys.function_languages GROUP
BY language_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, key_type_name FROM sys.key_types GROUP BY
key_type_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, index_type_name FROM sys.index_types GROUP BY
index_type_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, privilege_code_name FROM sys.privilege_codes
GROUP BY privilege_code_name HAVING COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, name FROM sys.auths GROUP BY name HAVING
COUNT(*) > 1
+----
+
+query IT rowsort
+SELECT COUNT(*) AS duplicates, def FROM sys.optimizers GROUP BY def HAVING
COUNT(*) > 1
+----
+
+query III rowsort
+SELECT COUNT(*) AS duplicates, table_id, column_id FROM sys.table_partitions
WHERE "column_id" IS NOT NULL GROUP BY table_id, column_id HAVING COUNT(*) >1
+----
+
+query IIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, "expression" FROM
sys.table_partitions WHERE "expression" IS NOT NULL GROUP BY table_id,
"expression" HAVING COUNT(*) >1
+----
+
+query IIIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, partition_id, "maximum" FROM
sys.range_partitions GROUP BY table_id, partition_id, "maximum" HAVING COUNT(*)
>1
+----
+
+query IIIT rowsort
+SELECT COUNT(*) AS duplicates, table_id, partition_id, "minimum" FROM
sys.range_partitions GROUP BY table_id, partition_id, "minimum" HAVING
COUNT(*) >1
+----
+
+
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test
@@ -0,0 +1,433 @@
+query ITIIT rowsort
+SELECT * FROM sys.schemas WHERE authorization NOT IN (SELECT id FROM sys.auths)
+----
+
+query ITIIT rowsort
+SELECT * FROM sys.schemas WHERE owner NOT IN (SELECT id FROM sys.auths)
+----
+
+query ITITITIII rowsort
+SELECT * FROM sys.tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
+----
+
+query ITITITII rowsort
+SELECT * FROM sys._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
+----
+
+query ITITITII rowsort
+SELECT * FROM tmp._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
+----
+
+query ITITITIII rowsort
+SELECT * FROM sys.tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types)
+----
+
+query ITITITII rowsort
+SELECT * FROM sys._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types)
+----
+
+query ITITITII rowsort
+SELECT * FROM tmp._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM tmp._columns WHERE table_id NOT IN (SELECT id FROM tmp._tables)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM sys.columns WHERE type NOT IN (SELECT sqlname FROM sys.types)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM sys._columns WHERE type NOT IN (SELECT sqlname FROM sys.types)
+----
+
+query ITTIIITTIT rowsort
+SELECT * FROM tmp._columns WHERE type NOT IN (SELECT sqlname FROM sys.types)
+----
+
+query ITTTIITTTITT rowsort
+SELECT * FROM sys.functions WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
+----
+
+query ITTTIITTTITT rowsort
+SELECT * FROM sys.functions WHERE type NOT IN (SELECT function_type_id FROM
sys.function_types)
+----
+
+query ITTTIITTTITT rowsort
+SELECT * FROM sys.functions WHERE language NOT IN (SELECT language_id FROM
sys.function_languages)
+----
+
+query ITTTIITTTITT rowsort
+SELECT * FROM sys.functions WHERE system AND schema_id NOT IN (SELECT id FROM
sys.schemas WHERE system)
+----
+
+query IITTIIII rowsort
+SELECT * FROM sys.args WHERE func_id NOT IN (SELECT id FROM sys.functions)
+----
+
+query IITTIIII rowsort
+SELECT * FROM sys.args WHERE type NOT IN (SELECT sqlname FROM sys.types)
+----
+
+query ITTIIIII rowsort
+SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
+----
+
+query ITTIIIII rowsort
+SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
AND schema_id <> 0
+----
+
+query ITI rowsort
+SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids)
+----
+
+query ITIITTT rowsort
+SELECT * FROM sys.ids WHERE obj_type IN ('key', 'index') AND id NOT IN (SELECT
id FROM sys.objects)
+----
+
+query IIITII rowsort
+SELECT * FROM sys.keys WHERE id NOT IN (SELECT id FROM sys.objects)
+----
+
+query IIITII rowsort
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables)
+----
+
+query IIITII rowsort
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys._tables)
+----
+
+query IIITII rowsort
+SELECT * FROM tmp.keys WHERE table_id NOT IN (SELECT id FROM tmp._tables)
+----
+
+query IIITII rowsort
+SELECT * FROM sys.keys WHERE type NOT IN (SELECT key_type_id FROM
sys.key_types)
+----
+
+query IIITII rowsort
+SELECT * FROM tmp.keys WHERE type NOT IN (SELECT key_type_id FROM
sys.key_types)
+----
+
+query IIITII rowsort
+SELECT * FROM sys.keys WHERE rkey <> -1 AND rkey NOT IN (SELECT id FROM
sys.keys)
+----
+
+query IIITII rowsort
+SELECT * FROM tmp.keys WHERE rkey <> -1 AND rkey NOT IN (SELECT id FROM
tmp.keys)
+----
+
+query IIIT rowsort
+SELECT * FROM sys.idxs WHERE id NOT IN (SELECT id FROM sys.objects)
+----
+
+query IIIT rowsort
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables)
+----
+
+query IIIT rowsort
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys._tables)
+----
+
+query IIIT rowsort
+SELECT * FROM tmp.idxs WHERE table_id NOT IN (SELECT id FROM tmp._tables)
+----
+
+query IIIT rowsort
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
