Changeset: 1a99e3095157 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1a99e3095157
Branch: userprofile
Log Message:
Merge with default branch.
diffs (truncated from 1249 to 300 lines):
diff --git a/clients/odbc/driver/ODBCQueries.h
b/clients/odbc/driver/ODBCQueries.h
--- a/clients/odbc/driver/ODBCQueries.h
+++ b/clients/odbc/driver/ODBCQueries.h
@@ -136,7 +136,7 @@
"else " #t ".type_digits " \
"end as \"COLUMN_SIZE\""
-#define BUFFER_LENGTH(t) "case " #t ".type " \
+#define BUFFER_LENGTH(t) "cast(case " #t ".type "
\
"when 'bigint' then 20 "
\
"when 'char' then 2 * " #t ".type_digits " \
"when 'clob' then 2 * " #t ".type_digits " \
@@ -173,9 +173,9 @@
"when 'tinyint' then 4 "
\
"when 'varchar' then 2 * " #t ".type_digits " \
"else " #t ".type_digits "
\
- "end as \"BUFFER_LENGTH\""
+ "end as integer) as \"BUFFER_LENGTH\""
-#define DECIMAL_DIGITS(t) "case " #t ".type "
\
+#define DECIMAL_DIGITS(t) "cast(case " #t ".type "
\
"when 'bigint' then 0 "
\
"when 'day_interval' then 0 "
\
"when 'decimal' then " #t ".type_scale "
\
@@ -198,7 +198,7 @@
"when 'timetz' then " #t ".type_digits - 1 "
\
"when 'tinyint' then 0 "
\
"else cast(null as smallint) "
\
- "end as \"DECIMAL_DIGITS\""
+ "end as smallint) as \"DECIMAL_DIGITS\""
#define NUM_PREC_RADIX(t) "case " #t ".type "
\
"when 'bigint' then 2 "
\
@@ -289,10 +289,10 @@
SQL_CODE_TIME
#define CHAR_OCTET_LENGTH(t)
\
- "case " #t ".type "
\
+ "cast(case " #t ".type "
\
"when 'char' then 2 * " #t ".type_digits " \
"when 'varchar' then 2 * " #t ".type_digits " \
"when 'clob' then 2 * " #t ".type_digits " \
"when 'blob' then " #t ".type_digits "
\
"else cast(null as integer) "
\
- "end as \"CHAR_OCTET_LENGTH\""
+ "end as integer) as \"CHAR_OCTET_LENGTH\""
diff --git a/clients/odbc/driver/SQLProcedureColumns.c
b/clients/odbc/driver/SQLProcedureColumns.c
--- a/clients/odbc/driver/SQLProcedureColumns.c
+++ b/clients/odbc/driver/SQLProcedureColumns.c
@@ -153,10 +153,10 @@ MNDBProcedureColumns(ODBCStmt *stmt,
"s.name as \"PROCEDURE_SCHEM\", "
"p.name as \"PROCEDURE_NAME\", "
"a.name as \"COLUMN_NAME\", "
- "case when a.inout = 1 then %d "
+ "cast(case when a.inout = 1 then %d "
"when p.type = %d then %d "
"else %d "
- "end as \"COLUMN_TYPE\", "
+ "end as smallint) as \"COLUMN_TYPE\", "
DATA_TYPE(a) ", "
TYPE_NAME(a) ", "
COLUMN_SIZE(a) ", "
@@ -169,12 +169,12 @@ MNDBProcedureColumns(ODBCStmt *stmt,
SQL_DATA_TYPE(a) ", "
SQL_DATETIME_SUB(a) ", "
CHAR_OCTET_LENGTH(a) ", "
- "case when p.type = 5 and a.inout = 0 then a.number + 1 "
+ "cast(case when p.type = 5 and a.inout = 0 then a.number
+ 1 "
"when p.type = 5 and a.inout = 1 then a.number -
x.maxout "
"when p.type = 2 and a.inout = 1 then a.number + 1 "
"when a.inout = 0 then 0 "
"else a.number "
- "end as \"ORDINAL_POSITION\", "
+ "end as integer) as \"ORDINAL_POSITION\", "
"'' as \"IS_NULLABLE\", "
/* Only the id value uniquely identifies a specific
procedure.
Include it to be able to differentiate between
multiple
diff --git a/clients/odbc/driver/SQLSpecialColumns.c
b/clients/odbc/driver/SQLSpecialColumns.c
--- a/clients/odbc/driver/SQLSpecialColumns.c
+++ b/clients/odbc/driver/SQLSpecialColumns.c
@@ -424,7 +424,7 @@ MNDBSpecialColumns(ODBCStmt *stmt,
assert(IdentifierType == SQL_ROWVER);
/* The backend does not have such info available */
/* create just a query which results in zero rows */
- /* Note: pseudo_column is sql_pc_unknown is 0 */
+ /* Note: PSEUDO_COLUMN is SQL_PC_UNKNOWN is 0 */
query = strdup("select cast(null as smallint) as \"SCOPE\", "
"cast('' as varchar(1)) as
\"COLUMN_NAME\", "
"cast(1 as smallint) as \"DATA_TYPE\", "
diff --git a/clients/odbc/driver/SQLTables.c b/clients/odbc/driver/SQLTables.c
--- a/clients/odbc/driver/SQLTables.c
+++ b/clients/odbc/driver/SQLTables.c
@@ -76,6 +76,7 @@ MNDBTables(ODBCStmt *stmt,
CatalogName &&
strcmp((char *) CatalogName, SQL_ALL_CATALOGS) == 0) {
/* Special case query to fetch all Catalog names. */
+ /* All columns except the TABLE_CAT column contain NULLs. */
query = strdup("select cast(null as varchar(1)) as
\"TABLE_CAT\", "
"cast(null as varchar(1)) as
\"TABLE_SCHEM\", "
"cast(null as varchar(1)) as
\"TABLE_NAME\", "
@@ -89,13 +90,14 @@ MNDBTables(ODBCStmt *stmt,
SchemaName &&
strcmp((char *) SchemaName, SQL_ALL_SCHEMAS) == 0) {
/* Special case query to fetch all Schema names. */
+ /* All columns except the TABLE_SCHEM column contain NULLs. */
query = strdup("select cast(null as varchar(1)) as
\"TABLE_CAT\", "
"name as \"TABLE_SCHEM\", "
"cast(null as varchar(1)) as
\"TABLE_NAME\", "
"cast(null as varchar(1)) as
\"TABLE_TYPE\", "
/* ODBC says remarks column contains
* NULL even though MonetDB supports
- * schema remarks */
+ * schema remarks. We must comply with ODBC */
"cast(null as varchar(1)) as \"REMARKS\" "
"from sys.schemas order by \"TABLE_SCHEM\"");
if (query == NULL)
@@ -106,6 +108,7 @@ MNDBTables(ODBCStmt *stmt,
TableType &&
strcmp((char *) TableType, SQL_ALL_TABLE_TYPES) == 0) {
/* Special case query to fetch all Table type names. */
+ /* All columns except the TABLE_TYPE column contain NULLs. */
query = strdup("select cast(null as varchar(1)) as
\"TABLE_CAT\", "
"cast(null as varchar(1)) as
\"TABLE_SCHEM\", "
"cast(null as varchar(1)) as
\"TABLE_NAME\", "
@@ -196,7 +199,10 @@ MNDBTables(ODBCStmt *stmt,
}
if (NameLength4 > 0) {
- /* filtering requested on table type */
+ /* filtering requested on table type(s)
+ * each table type can be enclosed in single quotation
marks (')
+ * or unquoted, for example, 'TABLE', 'VIEW' or TABLE,
VIEW.
+ */
char buf[32]; /* the longest string is "GLOBAL
TEMPORARY TABLE" */
int i;
size_t j;
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
@@ -363,10 +363,25 @@ main(int argc, char **argv)
check(ret, SQL_HANDLE_DBC, dbc, "SQLExecDirect (add privileges
script)");
// TODO add user procedures / functions to test SQLProcedures() and
SQLProcedureColumns() more
- // TODO add SQL COMMENT to tables, columns, indexes, procedures,
functions to fetch (and test) data in the REMARKS result column
+
+ // set COMMENT ON schema, tables, columns, indexes, procedures and
functions to fetch (and test) data in the REMARKS result column
+ ret = SQLExecDirect(stmt, (SQLCHAR *)
+ "COMMENT ON SCHEMA odbctst IS 'odbctst schema comment';\n"
+ "COMMENT ON TABLE odbctst.pk_uc IS 'odbctst.pk_uc table
comment';\n"
+ "COMMENT ON TABLE odbctst.nopk_twoucs IS 'odbctst.nopk_twoucs
table comment';\n"
+ "COMMENT ON COLUMN odbctst.nopk_twoucs.id2 IS
'odbctst.nopk_twoucs.id2 column comment';\n"
+ "COMMENT ON COLUMN odbctst.nopk_twoucs.name2 IS
'odbctst.nopk_twoucs.name2 column comment';\n"
+ "COMMENT ON INDEX odbctst.pk_uc_i IS 'odbctst.pk_uc_i index
comment';\n"
+ "COMMENT ON INDEX odbctst.nopk_twoucs_i IS
'odbctst.nopk_twoucs_i index comment';\n"
+ "COMMENT ON PROCEDURE sys.analyze() IS 'sys.analyze() procedure
comment';\n"
+ "COMMENT ON FUNCTION sys.sin(double) IS 'sys.sin(double)
function comment';\n"
+ "COMMENT ON FUNCTION sys.env() IS 'sys.env() function
comment';\n"
+ "COMMENT ON FUNCTION sys.statistics() IS 'sys.statistics()
function comment';\n"
+ , SQL_NTS);
+ check(ret, SQL_HANDLE_DBC, dbc, "SQLExecDirect (add comments script)");
/* run actual metadata query tests */
- // All catalogs query
+ // All catalogs query. MonetDB should return no rows. Catalog qualifier
not supported.
ret = SQLTables(stmt, (SQLCHAR*)SQL_ALL_CATALOGS, SQL_NTS,
(SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"", SQL_NTS);
@@ -376,7 +391,7 @@ main(int argc, char **argv)
"TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE
REMARKS\n"
"WVARCHAR(1) WVARCHAR(1) WVARCHAR(1) WVARCHAR(1)
WVARCHAR(1)\n");
- // All schemas query
+ // All schemas query. All columns except the TABLE_SCHEM column should
contain NULLs.
ret = SQLTables(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)SQL_ALL_SCHEMAS, SQL_NTS,
(SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS);
@@ -394,7 +409,7 @@ main(int argc, char **argv)
"NULL wlc NULL NULL NULL\n"
"NULL wlr NULL NULL NULL\n");
- // All table types query
+ // All table types query. All columns except the TABLE_TYPE column
should contain NULLs.
ret = SQLTables(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)SQL_ALL_TABLE_TYPES, SQL_NTS);
@@ -427,11 +442,28 @@ main(int argc, char **argv)
"NULL odbctst LINES TABLE NULL\n"
"NULL odbctst ORDERS TABLE NULL\n"
"NULL odbctst fk2c TABLE NULL\n"
- "NULL odbctst nopk_twoucs TABLE NULL\n"
+ "NULL odbctst nopk_twoucs TABLE odbctst.nopk_twoucs
table comment\n"
"NULL odbctst pk2c TABLE NULL\n"
- "NULL odbctst pk_uc TABLE NULL\n");
+ "NULL odbctst pk_uc TABLE odbctst.pk_uc table comment\n");
- // All user tables and views
+ // All user tables and views in schema odbctst
+ ret = SQLTables(stmt, (SQLCHAR*)"", SQL_NTS,
+ (SQLCHAR*)"odbctst", SQL_NTS, (SQLCHAR*)"%", SQL_NTS,
+ (SQLCHAR*)"'TABLE' , 'VIEW'", SQL_NTS); // using quotes
around the type names
+ compareResult(stmt, ret, "SQLTables (odbctst, %, 'TABLE' , 'VIEW')",
+ "Resultset with 5 columns\n"
+ "Resultset with 7 rows\n"
+ "TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE
REMARKS\n"
+ "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(25)
WVARCHAR(65000)\n"
+ "NULL odbctst CUSTOMERS TABLE NULL\n"
+ "NULL odbctst LINES TABLE NULL\n"
+ "NULL odbctst ORDERS TABLE NULL\n"
+ "NULL odbctst fk2c TABLE NULL\n"
+ "NULL odbctst nopk_twoucs TABLE odbctst.nopk_twoucs
table comment\n"
+ "NULL odbctst pk2c TABLE NULL\n"
+ "NULL odbctst pk_uc TABLE odbctst.pk_uc table comment\n");
+
+ // All user tables and views in all schemas
ret = SQLTables(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"%", SQL_NTS, (SQLCHAR*)"%", SQL_NTS,
(SQLCHAR*)"TABLE, VIEW, GLOBAL TEMPORARY TABLE, LOCAL
TEMPORARY TABLE", SQL_NTS);
@@ -448,9 +480,9 @@ main(int argc, char **argv)
"NULL odbctst LINES TABLE NULL\n"
"NULL odbctst ORDERS TABLE NULL\n"
"NULL odbctst fk2c TABLE NULL\n"
- "NULL odbctst nopk_twoucs TABLE NULL\n"
+ "NULL odbctst nopk_twoucs TABLE odbctst.nopk_twoucs
table comment\n"
"NULL odbctst pk2c TABLE NULL\n"
- "NULL odbctst pk_uc TABLE NULL\n");
+ "NULL odbctst pk_uc TABLE odbctst.pk_uc table comment\n");
// All columns of odbctst tables containg 'pk' in their name
ret = SQLColumns(stmt, (SQLCHAR*)"", SQL_NTS,
@@ -460,9 +492,9 @@ main(int argc, char **argv)
"Resultset with 18 columns\n"
"Resultset with 7 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE
SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
IS_NULLABLE\n"
- "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER BIGINT BIGINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
BIGINT INTEGER WVARCHAR(3)\n"
- "NULL odbctst nopk_twoucs id2 4 INTEGER 32
11 0 2 0 NULL NULL 4 NULL NULL 1
NO\n"
- "NULL odbctst nopk_twoucs name2 -9 VARCHAR 99
198 NULL NULL 1 NULL NULL -9 NULL 198 2
YES\n"
+ "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER INTEGER SMALLINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
INTEGER INTEGER WVARCHAR(3)\n"
+ "NULL odbctst nopk_twoucs id2 4 INTEGER 32
11 0 2 0 odbctst.nopk_twoucs.id2 column comment NULL
4 NULL NULL 1 NO\n"
+ "NULL odbctst nopk_twoucs name2 -9 VARCHAR 99
198 NULL NULL 1 odbctst.nopk_twoucs.name2 column comment
NULL -9 NULL 198 2 YES\n"
"NULL odbctst pk2c pkc1 4 INTEGER 32 11
0 2 0 NULL NULL 4 NULL NULL 1 NO\n"
"NULL odbctst pk2c pkc2 -9 VARCHAR 99 198
NULL NULL 0 NULL NULL -9 NULL 198 2 NO\n"
"NULL odbctst pk2c name1 -9 VARCHAR 99 198
NULL NULL 1 NULL NULL -9 NULL 198 3 YES\n"
@@ -477,7 +509,7 @@ main(int argc, char **argv)
"Resultset with 18 columns\n"
"Resultset with 8 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE
SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
IS_NULLABLE\n"
- "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER BIGINT BIGINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
BIGINT INTEGER WVARCHAR(3)\n"
+ "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER INTEGER SMALLINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
INTEGER INTEGER WVARCHAR(3)\n"
"NULL tmp glbl_nopk_twoucs id2 4 INTEGER
32 11 0 2 0 NULL NULL 4 NULL NULL
1 NO\n"
"NULL tmp glbl_nopk_twoucs name2 -9 VARCHAR
99 198 NULL NULL 1 NULL NULL -9 NULL 198
2 YES\n"
"NULL tmp glbl_pk_uc id1 4 INTEGER 32
11 0 2 0 NULL NULL 4 NULL NULL 1
NO\n"
@@ -495,12 +527,21 @@ main(int argc, char **argv)
"Resultset with 18 columns\n"
"Resultset with 4 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE
SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
IS_NULLABLE\n"
- "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER BIGINT BIGINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
BIGINT INTEGER WVARCHAR(3)\n"
+ "WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) WVARCHAR(1024)
SMALLINT WCHAR(25) INTEGER INTEGER SMALLINT SMALLINT
SMALLINT WVARCHAR(65000) WVARCHAR(2048) SMALLINT SMALLINT
INTEGER INTEGER WVARCHAR(3)\n"
"NULL tmp glbl_nopk_twoucs name2 -9 VARCHAR
99 198 NULL NULL 1 NULL NULL -9 NULL 198
2 YES\n"
"NULL tmp glbl_pk_uc name1 -9 VARCHAR 99
198 NULL NULL 1 NULL NULL -9 NULL 198 2
YES\n"
"NULL tmp tmp_nopk_twoucs name2 -9 VARCHAR 99
198 NULL NULL 1 NULL NULL -9 NULL 198 2
YES\n"
"NULL tmp tmp_pk_uc name1 -9 VARCHAR 99
198 NULL NULL 1 NULL NULL -9 NULL 198 2
YES\n");
+ ret = SQLSpecialColumns(stmt, SQL_ROWVER, (SQLCHAR*)NULL, 0,
+ (SQLCHAR*)"%", SQL_NTS, (SQLCHAR*)"%", SQL_NTS,
+ SQL_SCOPE_SESSION, SQL_NO_NULLS);
+ compareResult(stmt, ret, "SQLSpecialColumns (%, %, SQL_ROWVER)",
+ "Resultset with 8 columns\n"
+ "Resultset with 0 rows\n"
+ "SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n"
+ "SMALLINT WVARCHAR(1) SMALLINT WVARCHAR(4)
INTEGER INTEGER SMALLINT SMALLINT\n");
+
// sys.table_types
ret = SQLPrimaryKeys(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"table_types",
SQL_NTS);
@@ -518,7 +559,7 @@ main(int argc, char **argv)
"Resultset with 8 columns\n"
"Resultset with 1 rows\n"
"SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n"
- "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER BIGINT BIGINT SMALLINT\n"
+ "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER INTEGER SMALLINT SMALLINT\n"
"1 table_type_id 5 SMALLINT 16 6
0 1\n");
ret = SQLStatistics(stmt, (SQLCHAR*)"", SQL_NTS,
@@ -577,7 +618,7 @@ main(int argc, char **argv)
"Resultset with 8 columns\n"
"Resultset with 1 rows\n"
"SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n"
- "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER BIGINT BIGINT SMALLINT\n"
+ "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER INTEGER SMALLINT SMALLINT\n"
"1 id1 4 INTEGER 32 11 0 1\n");
ret = SQLSpecialColumns(stmt, SQL_ROWVER, (SQLCHAR*)"", SQL_NTS,
@@ -651,7 +692,7 @@ main(int argc, char **argv)
"Resultset with 8 columns\n"
"Resultset with 1 rows\n"
"SCOPE COLUMN_NAME DATA_TYPE TYPE_NAME
COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS PSEUDO_COLUMN\n"
- "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER BIGINT BIGINT SMALLINT\n"
+ "SMALLINT WVARCHAR(1024) SMALLINT WCHAR(25)
INTEGER INTEGER SMALLINT SMALLINT\n"
"1 id1 4 INTEGER 32 11 0 1\n");
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
