Changeset: 13403d6262c5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/13403d6262c5
Added Files:
sql/test/information-schema/Tests/check_constraints.test
sql/test/information-schema/Tests/parameters.test
sql/test/information-schema/Tests/referential_constraints.test
sql/test/information-schema/Tests/routines.test
sql/test/information-schema/Tests/sequences.test
sql/test/information-schema/Tests/table_constraints.test
Modified Files:
sql/ChangeLog.Dec2023
sql/test/information-schema/Tests/All
sql/test/information-schema/Tests/character_sets.test
sql/test/information-schema/Tests/columns.test
sql/test/information-schema/Tests/schemata.test
sql/test/information-schema/Tests/tables.test
sql/test/information-schema/Tests/views.test
Branch: Dec2023
Log Message:
Add tests for new information_schema views
diffs (truncated from 605 to 300 lines):
diff --git a/sql/ChangeLog.Dec2023 b/sql/ChangeLog.Dec2023
--- a/sql/ChangeLog.Dec2023
+++ b/sql/ChangeLog.Dec2023
@@ -12,6 +12,7 @@
information_schema.table_constraints
information_schema.referential_constraints
information_schema.routines
+ information_schema.parameters
information_schema.sequences
Most views have been extended (after the standard columns) with MonetDB
diff --git a/sql/test/information-schema/Tests/All
b/sql/test/information-schema/Tests/All
--- a/sql/test/information-schema/Tests/All
+++ b/sql/test/information-schema/Tests/All
@@ -3,4 +3,9 @@ schemata
tables
views
columns
-
+check_constraints
+table_constraints
+referential_constraints
+routines
+parameters
+sequences
diff --git a/sql/test/information-schema/Tests/character_sets.test
b/sql/test/information-schema/Tests/character_sets.test
--- a/sql/test/information-schema/Tests/character_sets.test
+++ b/sql/test/information-schema/Tests/character_sets.test
@@ -20,7 +20,8 @@ NULL
NULL
query ITTT rowsort
-SELECT COUNT(*) AS duplicates, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.CHARACTER_SETS
+SELECT COUNT(*) AS duplicates, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME
+ FROM INFORMATION_SCHEMA.CHARACTER_SETS
GROUP BY CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME
HAVING COUNT(*) > 1
----
diff --git a/sql/test/information-schema/Tests/check_constraints.test
b/sql/test/information-schema/Tests/check_constraints.test
new file mode 100644
--- /dev/null
+++ b/sql/test/information-schema/Tests/check_constraints.test
@@ -0,0 +1,17 @@
+query TTTT rowsort
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ CHECK_CLAUSE
+FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+WHERE CONSTRAINT_SCHEMA = '' OR CONSTRAINT_NAME = ''
+----
+
+query ITTT rowsort
+SELECT COUNT(*) AS duplicates, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
+ GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
diff --git a/sql/test/information-schema/Tests/columns.test
b/sql/test/information-schema/Tests/columns.test
--- a/sql/test/information-schema/Tests/columns.test
+++ b/sql/test/information-schema/Tests/columns.test
@@ -104,7 +104,7 @@ NULL
NULL
NULL
-query TTTTITTTIIIIIITITTTTTTTTTTTTTTTITTTTIIIITTTTTTTTIIIIIIT rowsort
+query TTTTITTTIIIIIITITTTTTTTTTTTTTTTITTTTIIIITTTTTTTTIIIIIIIT rowsort
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
@@ -159,6 +159,7 @@ SELECT
schema_id,
table_id,
column_id,
+ sequence_id,
is_system,
comments
FROM INFORMATION_SCHEMA.COLUMNS
@@ -166,7 +167,8 @@ WHERE TABLE_SCHEMA = '' OR TABLE_NAME =
----
query ITTTT rowsort
-SELECT COUNT(*) AS duplicates, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
+SELECT COUNT(*) AS duplicates, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME
+ FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
HAVING COUNT(*) > 1
----
diff --git a/sql/test/information-schema/Tests/parameters.test
b/sql/test/information-schema/Tests/parameters.test
new file mode 100644
--- /dev/null
+++ b/sql/test/information-schema/Tests/parameters.test
@@ -0,0 +1,107 @@
+query TTTITTTTTTTTTTTIITTTTTTIIIITITTTTTTITTIIT rowsort
+SELECT
+ SPECIFIC_CATALOG,
+ SPECIFIC_SCHEMA,
+ SPECIFIC_NAME,
+ ORDINAL_POSITION,
+ PARAMETER_MODE,
+ IS_RESULT,
+ AS_LOCATOR,
+ PARAMETER_NAME,
+ FROM_SQL_SPECIFIC_CATALOG,
+ FROM_SQL_SPECIFIC_SCHEMA,
+ FROM_SQL_SPECIFIC_NAME,
+ TO_SQL_SPECIFIC_CATALOG,
+ TO_SQL_SPECIFIC_SCHEMA,
+ TO_SQL_SPECIFIC_NAME,
+ DATA_TYPE,
+ CHARACTER_MAXIMUM_LENGTH,
+ CHARACTER_OCTET_LENGTH,
+ CHARACTER_SET_CATALOG,
+ CHARACTER_SET_SCHEMA,
+ CHARACTER_SET_NAME,
+ COLLATION_CATALOG,
+ COLLATION_SCHEMA,
+ COLLATION_NAME,
+ NUMERIC_PRECISION,
+ NUMERIC_PRECISION_RADIX,
+ NUMERIC_SCALE,
+ DATETIME_PRECISION,
+ INTERVAL_TYPE,
+ INTERVAL_PRECISION,
+ UDT_CATALOG,
+ UDT_SCHEMA,
+ UDT_NAME,
+ SCOPE_CATALOG,
+ SCOPE_SCHEMA,
+ SCOPE_NAME,
+ MAXIMUM_CARDINALITY,
+ DTD_IDENTIFIER,
+ DECLARED_DATA_TYPE,
+ DECLARED_NUMERIC_PRECISION,
+ DECLARED_NUMERIC_SCALE,
+ PARAMETER_DEFAULT
+FROM INFORMATION_SCHEMA.PARAMETERS
+WHERE SPECIFIC_SCHEMA = '' OR SPECIFIC_NAME = ''
+----
+
+query TTTITTTTTTTTTTTIITTTTTTIIIITITTTTTTITTIITIIITII rowsort
+SELECT
+ SPECIFIC_CATALOG,
+ SPECIFIC_SCHEMA,
+ SPECIFIC_NAME,
+ ORDINAL_POSITION,
+ PARAMETER_MODE,
+ IS_RESULT,
+ AS_LOCATOR,
+ PARAMETER_NAME,
+ FROM_SQL_SPECIFIC_CATALOG,
+ FROM_SQL_SPECIFIC_SCHEMA,
+ FROM_SQL_SPECIFIC_NAME,
+ TO_SQL_SPECIFIC_CATALOG,
+ TO_SQL_SPECIFIC_SCHEMA,
+ TO_SQL_SPECIFIC_NAME,
+ DATA_TYPE,
+ CHARACTER_MAXIMUM_LENGTH,
+ CHARACTER_OCTET_LENGTH,
+ CHARACTER_SET_CATALOG,
+ CHARACTER_SET_SCHEMA,
+ CHARACTER_SET_NAME,
+ COLLATION_CATALOG,
+ COLLATION_SCHEMA,
+ COLLATION_NAME,
+ NUMERIC_PRECISION,
+ NUMERIC_PRECISION_RADIX,
+ NUMERIC_SCALE,
+ DATETIME_PRECISION,
+ INTERVAL_TYPE,
+ INTERVAL_PRECISION,
+ UDT_CATALOG,
+ UDT_SCHEMA,
+ UDT_NAME,
+ SCOPE_CATALOG,
+ SCOPE_SCHEMA,
+ SCOPE_NAME,
+ MAXIMUM_CARDINALITY,
+ DTD_IDENTIFIER,
+ DECLARED_DATA_TYPE,
+ DECLARED_NUMERIC_PRECISION,
+ DECLARED_NUMERIC_SCALE,
+ PARAMETER_DEFAULT,
+ schema_id,
+ function_id,
+ arg_id,
+ function_name,
+ function_type,
+ is_system
+FROM INFORMATION_SCHEMA.PARAMETERS
+WHERE SPECIFIC_SCHEMA = '' OR SPECIFIC_NAME = ''
+----
+
+query ITTTTI rowsort
+SELECT COUNT(*) AS duplicates, SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
SPECIFIC_NAME, PARAMETER_MODE, ORDINAL_POSITION
+ FROM INFORMATION_SCHEMA.PARAMETERS
+ GROUP BY SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_MODE,
ORDINAL_POSITION
+ HAVING COUNT(*) > 1
+----
+
diff --git a/sql/test/information-schema/Tests/referential_constraints.test
b/sql/test/information-schema/Tests/referential_constraints.test
new file mode 100644
--- /dev/null
+++ b/sql/test/information-schema/Tests/referential_constraints.test
@@ -0,0 +1,45 @@
+query TTTTTTTTT rowsort
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ UNIQUE_CONSTRAINT_CATALOG,
+ UNIQUE_CONSTRAINT_SCHEMA,
+ UNIQUE_CONSTRAINT_NAME,
+ MATCH_OPTION,
+ UPDATE_RULE,
+ DELETE_RULE
+FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
+WHERE CONSTRAINT_SCHEMA = '' OR CONSTRAINT_NAME = ''
+----
+
+query TTTTTTTTTIITIIITI rowsort
+SELECT
+ CONSTRAINT_CATALOG,
+ CONSTRAINT_SCHEMA,
+ CONSTRAINT_NAME,
+ UNIQUE_CONSTRAINT_CATALOG,
+ UNIQUE_CONSTRAINT_SCHEMA,
+ UNIQUE_CONSTRAINT_NAME,
+ MATCH_OPTION,
+ UPDATE_RULE,
+ DELETE_RULE,
+ fk_schema_id,
+ fk_table_id,
+ fk_table_name,
+ fk_key_id,
+ uc_schema_id,
+ uc_table_id,
+ uc_table_name,
+ uc_key_id
+FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
+WHERE CONSTRAINT_SCHEMA = '' OR CONSTRAINT_NAME = ''
+----
+
+query ITTT rowsort
+SELECT COUNT(*) AS duplicates, CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME
+ FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
+ GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
+ HAVING COUNT(*) > 1
+----
+
diff --git a/sql/test/information-schema/Tests/routines.test
b/sql/test/information-schema/Tests/routines.test
new file mode 100644
--- /dev/null
+++ b/sql/test/information-schema/Tests/routines.test
@@ -0,0 +1,201 @@
+query
TTTTTTTTTTTTTTIITTTTTTIIIITITTTTTTIITTTTTTTTTTITTTTTTTTTTTTTIITTTTTTIIIITITTTTTTITTIITII
rowsort
+SELECT
+ SPECIFIC_CATALOG,
+ SPECIFIC_SCHEMA,
+ SPECIFIC_NAME,
+ ROUTINE_CATALOG,
+ ROUTINE_SCHEMA,
+ ROUTINE_NAME,
+ ROUTINE_TYPE,
+ MODULE_CATALOG,
+ MODULE_SCHEMA,
+ MODULE_NAME,
+ UDT_CATALOG,
+ UDT_SCHEMA,
+ UDT_NAME,
+ DATA_TYPE,
+ CHARACTER_MAXIMUM_LENGTH,
+ CHARACTER_OCTET_LENGTH,
+ CHARACTER_SET_CATALOG,
+ CHARACTER_SET_SCHEMA,
+ CHARACTER_SET_NAME,
+ COLLATION_CATALOG,
+ COLLATION_SCHEMA,
+ COLLATION_NAME,
+ NUMERIC_PRECISION,
+ NUMERIC_PRECISION_RADIX,
+ NUMERIC_SCALE,
+ DATETIME_PRECISION,
+ INTERVAL_TYPE,
+ INTERVAL_PRECISION,
+ TYPE_UDT_CATALOG,
+ TYPE_UDT_SCHEMA,
+ TYPE_UDT_NAME,
+ SCOPE_CATALOG,
+ SCOPE_SCHEMA,
+ SCOPE_NAME,
+ MAXIMUM_CARDINALITY,
+ DTD_IDENTIFIER,
+ ROUTINE_BODY,
+ ROUTINE_DEFINITION,
+ EXTERNAL_NAME,
+ EXTERNAL_LANGUAGE,
+ PARAMETER_STYLE,
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
