Changeset: 300879ab6003 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/300879ab6003
Added Files:
sql/scripts/91_information_schema.sql
Modified Files:
clients/odbc/tests/ODBCmetadata.c
sql/backends/monet5/CMakeLists.txt
sql/backends/monet5/sql_upgrades.c
sql/scripts/CMakeLists.txt
sql/test/BugDay_2005-10-06_2.9.3/Tests/CrashMe_SQL_server_crash-2.SF-921673.test
sql/test/BugTracker-2009/Tests/join_topn.SF-2654133.test
sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.test
sql/test/Dependencies/Tests/dependency_DBobjects.test
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/Tests/comment-dump.test
sql/test/bugs/Tests/groupby_having-bug-sf-947600.test
sql/test/bugs/Tests/groupby_having_charlength-bug-sf-943566.test
sql/test/bugs/Tests/innerjoin_multiple-bug-sf-943661.test
sql/test/sql_dump/Tests/dump.test
Branch: default
Log Message:
Added SQL standard INFORMATION_SCHEMA schema and first standard views:
SCHEMATA, TABLES, VIEWS, COLUMNS and CHARACTER_SETS.
diffs (truncated from 1063 to 300 lines):
diff --git a/clients/odbc/tests/ODBCmetadata.c
b/clients/odbc/tests/ODBCmetadata.c
--- a/clients/odbc/tests/ODBCmetadata.c
+++ b/clients/odbc/tests/ODBCmetadata.c
@@ -482,9 +482,10 @@ main(int argc, char **argv)
(SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS);
compareResult(stmt, ret, "SQLTables (SQL_ALL_SCHEMAS)",
"Resultset with 5 columns\n"
- "Resultset with 6 rows\n"
+ "Resultset with 7 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE
REMARKS\n"
"WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1) WVARCHAR(1)
WVARCHAR(1)\n"
+ "NULL information_schema NULL NULL NULL\n"
"NULL json NULL NULL NULL\n"
"NULL logging NULL NULL NULL\n"
"NULL odbctst NULL NULL NULL\n"
diff --git a/sql/backends/monet5/CMakeLists.txt
b/sql/backends/monet5/CMakeLists.txt
--- a/sql/backends/monet5/CMakeLists.txt
+++ b/sql/backends/monet5/CMakeLists.txt
@@ -114,7 +114,8 @@ set(include_sql_files
75_storagemodel
76_dump
80_statistics
- 81_tracer)
+ 81_tracer
+ 91_information_schema)
if(HAVE_HGE)
list(APPEND include_sql_files
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -326,6 +326,7 @@ sql_create_shp(Client c)
"create procedure SHPLoad(fname string, tablename string)
external name shp.load;\n"
"update sys.functions set system = true where schema_id = 2000
and name in ('shpload');";
printf("Running database upgrade commands:\n%s\n", query);
+ fflush(stdout);
return SQLstatementIntern(c, query, "update", true, false, NULL);
}
#endif
@@ -5204,6 +5205,7 @@ sql_update_jun2023(Client c, mvc *sql, s
"drop schema if exists wlc cascade;\n"
"drop schema if exists wlr cascade;\n";
printf("Running database upgrade commands:\n%s\n",
query);
+ fflush(stdout);
err = SQLstatementIntern(c, query, "update", true,
false, NULL);
}
}
@@ -5236,6 +5238,7 @@ sql_update_jun2023(Client c, mvc *sql, s
sql->session->status = 0;
sql->errstr[0] = '\0';
printf("Running database upgrade
commands:\n%s\n", buf);
+ fflush(stdout);
err = SQLstatementIntern(c, buf, "update",
true, false, NULL);
}
sa_destroy(sql->sa);
@@ -5469,6 +5472,7 @@ sql_update_jun2023(Client c, mvc *sql, s
F_FUNC, F_PROC,
F_UNION);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
err = SQLstatementIntern(c, buf, "update", true, false,
NULL);
}
res_table_destroy(output);
@@ -5507,6 +5511,7 @@ sql_update_jun2023(Client c, mvc *sql, s
"('dependency_tables_on_functions','dependency_views_on_functions') and
schema_id = 2000;\n");
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
err = SQLstatementIntern(c, buf, "update", true, false,
NULL);
}
}
@@ -5522,6 +5527,7 @@ sql_update_jun2023(Client c, mvc *sql, s
"update sys.functions set system =
true where system <> true and name = 'database' and schema_id = 2000 and type =
%d;\n",
(int) F_FUNC);
printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
}
@@ -5541,6 +5547,7 @@ sql_update_jun2023(Client c, mvc *sql, s
"create procedure sys.stop(tag bigint, username string)
external name sysmon.stop;\n"
"update sys.functions set system = true where system <>
true and mod = 'sysmon' and name in ('stop', 'pause', 'resume', 'queue');\n";
printf("Running database upgrade commands:\n%s\n", query);
+ fflush(stdout);
err = SQLstatementIntern(c, query, "update", true, false, NULL);
}
@@ -5551,6 +5558,7 @@ sql_update_jun2023(Client c, mvc *sql, s
"drop procedure sys.settimeout(bigint, bigint)
cascade;\n"
"drop procedure sys.setsession(bigint) cascade;\n";
printf("Running database upgrade commands:\n%s\n", query);
+ fflush(stdout);
err = SQLstatementIntern(c, query, "update", true, false, NULL);
}
sql->session->status = 0; /* if the function was not found clean the
error */
@@ -5653,6 +5661,7 @@ sql_update_jun2023(Client c, mvc *sql, s
F_FUNC, F_FILT, F_FUNC, F_FILT);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
}
@@ -5840,6 +5849,7 @@ sql_update_default_geom(Client c, mvc *s
"update sys.functions set system = true where
system <> true and schema_id = 2000 and name in ('st_collect',
'st_distancegeographic', 'st_dwithingeographic', 'st_dwithin',
'st_dwithin_noindex', 'st_dwithin2', 'st_intersectsgeographic',
'st_intersects', 'st_intersects_noindex', 'st_makeline');\n"
"update sys._tables set system = true where
system <> true and schema_id = 2000 and name = 'geometry_columns';\n";
printf("Running database upgrade commands:\n%s\n",
query);
+ fflush(stdout);
err = SQLstatementIntern(c, query, "update", true,
false, NULL);
}
}
@@ -5852,11 +5862,13 @@ sql_update_default(Client c, mvc *sql, s
{
sql_subtype tp;
char *err = NULL;
+ sql_schema *info;
sql_find_subtype(&tp, "varchar", 0, 0);
if (sql_bind_func(sql, s->base.name, "similarity", &tp, &tp, F_FUNC,
true)) {
const char *query = "drop function sys.similarity(string,
string) cascade;\n";
printf("Running database upgrade commands:\n%s\n", query);
+ fflush(stdout);
err = SQLstatementIntern(c, query, "update", true, false, NULL);
} else {
sql->session->status = 0; /* if the function was not found
clean the error */
@@ -5864,7 +5876,7 @@ sql_update_default(Client c, mvc *sql, s
}
if (mvc_bind_table(sql, s, "describe_accessible_tables") == NULL) {
- sql->session->status = 0; /* if the function was not found
clean the error */
+ sql->session->status = 0; /* if the view was not found clean
the error */
sql->errstr[0] = '\0';
const char *query =
"create view sys.describe_accessible_tables as\n"
@@ -5889,6 +5901,7 @@ sql_update_default(Client c, mvc *sql, s
"GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;\n"
"update sys._tables set system = true where system <> true and
schema_id = 2000 and name = 'describe_accessible_tables';\n"
+ /* PYTHON_MAP and PYTHON3_MAP have been removed */
"alter table sys.function_languages set read write;\n"
"delete from sys.function_languages where
language_keyword like 'PYTHON%_MAP';\n"
/* for these two, also see load_func() */
@@ -5906,6 +5919,178 @@ sql_update_default(Client c, mvc *sql, s
}
}
+ /* 91_information_schema.sql */
+ info = mvc_bind_schema(sql, "information_schema");
+ if (info == NULL) {
+ sql->session->status = 0; /* if the schema was not found clean
the error */
+ sql->errstr[0] = '\0';
+ const char *cmds =
+ "CREATE SCHEMA INFORMATION_SCHEMA;\n"
+ "COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11
SQL/Schemata';\n"
+ "\n"
+ "update sys.schemas set system = true where name =
'information_schema';\n"
+ "\n"
+ "CREATE VIEW INFORMATION_SCHEMA.CHARACTER_SETS AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
+ " cast('UTF-8' AS varchar(16)) AS CHARACTER_SET_NAME,\n"
+ " cast('ISO/IEC 10646:2021' AS varchar(20)) AS
CHARACTER_REPERTOIRE,\n"
+ " cast('UTF-8' AS varchar(16)) AS FORM_OF_USE,\n"
+ " cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_NAME;\n"
+ "\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO
PUBLIC WITH GRANT OPTION;\n"
+ "\n"
+ "CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS CATALOG_NAME,\n"
+ " s.\"name\" AS SCHEMA_NAME,\n"
+ " a.\"name\" AS SCHEMA_OWNER,\n"
+ " cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_SCHEMA,\n"
+ " cast('UTF-8' AS varchar(16)) AS
DEFAULT_CHARACTER_SET_NAME,\n"
+ " cast(NULL AS varchar(1)) AS SQL_PATH,\n"
+ " -- MonetDB column extensions\n"
+ " s.\"id\" AS schema_id,\n"
+ " s.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"schemas\" s\n"
+ " INNER JOIN sys.\"auths\" a ON s.\"owner\" = a.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON s.\"id\" = cm.\"id\"\n"
+ " ORDER BY s.\"name\";\n"
+ "\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC
WITH GRANT OPTION;\n"
+ "\n"
+ "CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " tt.\"table_type_name\" AS TABLE_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS SELF_REFERENCING_COLUMN_NAME,\n"
+ " cast(NULL AS varchar(1)) AS REFERENCE_GENERATION,\n"
+ " cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_NAME,\n"
+ " cast(sys.ifthenelse((t.\"type\" IN (0, 3, 7, 20, 30) AND
t.\"access\" IN (0, 2)), 'YES', 'NO') AS varchar(3)) AS IS_INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS IS_TYPED,\n"
+ " cast((CASE t.\"commit_action\" WHEN 1 THEN 'DELETE' WHEN 2
THEN 'PRESERVE' WHEN 3 THEN 'DROP' ELSE NULL END) AS varchar(10)) AS
COMMIT_ACTION,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " st.\"count\" AS row_count,\n"
+ " t.\"system\" AS is_system,\n"
+ " sys.ifthenelse(t.\"type\" IN (1, 11), TRUE, FALSE) AS
is_view,\n"
+ " t.\"query\" AS query_def,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON t.\"schema_id\" = s.\"id\"\n"
+ " INNER JOIN sys.\"table_types\" tt ON t.\"type\" =
tt.\"table_type_id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON t.\"id\" = cm.\"id\"\n"
+ " LEFT OUTER JOIN (SELECT DISTINCT \"schema\", \"table\",
\"count\" FROM sys.\"statistics\"()) st ON (s.\"name\" = st.\"schema\" AND
t.\"name\" = st.\"table\")\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLES TO PUBLIC WITH
GRANT OPTION;\n"
+ "\n"
+ "CREATE VIEW INFORMATION_SCHEMA.VIEWS AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " t.\"query\" AS VIEW_DEFINITION,\n"
+ " cast('NONE' AS varchar(10)) AS CHECK_OPTION,\n"
+ " cast('NO' AS varchar(3)) AS IS_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS IS_TRIGGER_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS IS_TRIGGER_DELETABLE,\n"
+ " cast('NO' AS varchar(3)) AS IS_TRIGGER_INSERTABLE_INTO,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " cast(sys.ifthenelse(t.\"system\", t.\"type\" + 10 /* system
view */, t.\"type\") AS smallint) AS table_type_id,\n"
+ " t.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"_tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON t.\"schema_id\" = s.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON t.\"id\" = cm.\"id\"\n"
+ " WHERE t.\"type\" = 1\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH
GRANT OPTION;\n"
+ "\n"
+ "CREATE VIEW INFORMATION_SCHEMA.COLUMNS AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " c.\"name\" AS COLUMN_NAME,\n"
+ " cast(c.\"number\" +1 AS int) AS ORDINAL_POSITION,\n"
+ " c.\"default\" AS COLUMN_DEFAULT,\n"
+ " cast(sys.ifthenelse(c.\"null\", 'YES', 'NO') AS varchar(3))
AS IS_NULLABLE,\n"
+ " c.\"type\" AS DATA_TYPE,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\", NULL) AS int)
AS CHARACTER_MAXIMUM_LENGTH,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), c.\"type_digits\" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_digits\", NULL) AS int) AS NUMERIC_PRECISION,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c.\"type\" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('int','bigint','smallint','tinyint','hugeint','float','real','double','decimal','numeric','oid'),
c.\"type_scale\", NULL) AS int) AS NUMERIC_SCALE,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('date','timestamp','timestamptz','time','timetz'), c.\"type_scale\" -1, NULL)
AS int) AS DATETIME_PRECISION,\n"
+ " cast(CASE c.\"type\" WHEN 'day_interval' THEN 'interval day'
WHEN 'month_interval' THEN 'interval month' WHEN 'sec_interval' THEN 'interval
second' ELSE NULL END AS varchar(40)) AS INTERVAL_TYPE,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('day_interval','month_interval','sec_interval'), c.\"type_scale\" -1, NULL) AS
int) AS INTERVAL_PRECISION,\n"
+ " cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,\n"
+ " cast(sys.ifthenelse(c.\"type\" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) AS
CHARACTER_SET_NAME,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS COLLATION_NAME,\n"
+ " cast(NULL AS varchar(1)) AS DOMAIN_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS DOMAIN_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS DOMAIN_NAME,\n"
+ " cast(NULL AS varchar(1)) AS UDT_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS UDT_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS UDT_NAME,\n"
+ " cast(NULL AS varchar(1)) AS SCOPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS SCOPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS SCOPE_NAME,\n"
+ " cast(NULL AS int) AS MAXIMUM_CARDINALITY,\n"
+ " cast(NULL AS varchar(1)) AS DTD_IDENTIFIER,\n"
+ " cast('NO' AS varchar(3)) AS IS_SELF_REFERENCING,\n"
+ " cast(CASE WHEN c.\"default\" LIKE 'next value for %' THEN
'YES' ELSE 'NO' END AS varchar(3)) AS IS_IDENTITY,\n"
+ " cast(NULL AS varchar(10)) AS IDENTITY_GENERATION,\n"
+ " cast(NULL AS int) AS IDENTITY_START,\n"
+ " cast(NULL AS int) AS IDENTITY_INCREMENT,\n"
+ " cast(NULL AS int) AS IDENTITY_MAXIMUM,\n"
+ " cast(NULL AS int) AS IDENTITY_MINIMUM,\n"
+ " cast(NULL AS varchar(3)) AS IDENTITY_CYCLE,\n"
+ " cast('NO' AS varchar(3)) AS IS_GENERATED,\n"
+ " cast(NULL AS varchar(1)) AS GENERATION_EXPRESSION,\n"
+ " cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_START,\n"
+ " cast('NO' AS varchar(3)) AS IS_SYSTEM_TIME_PERIOD_END,\n"
+ " cast('NO' AS varchar(3)) AS
SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION,\n"
+ " cast(sys.ifthenelse(t.\"type\" IN (0,3,7,20,30), 'YES',
'NO') AS varchar(3)) AS IS_UPDATABLE,\n"
+ " cast(NULL AS varchar(1)) AS DECLARED_DATA_TYPE,\n"
+ " cast(NULL AS int) AS DECLARED_NUMERIC_PRECISION,\n"
+ " cast(NULL AS int) AS DECLARED_NUMERIC_SCALE,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " c.\"table_id\" AS table_id,\n"
+ " c.\"id\" AS column_id,\n"
+ " t.\"system\" AS is_system,\n"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
