Changeset: d70e05683053 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/d70e05683053
Modified Files:
clients/odbc/tests/ODBCmetadata.c
Branch: default
Log Message:
merged with sep2022
diffs (truncated from 473 to 300 lines):
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
@@ -165,7 +165,7 @@ MNDBSpecialColumns(ODBCStmt *stmt,
size_t querylen;
/* determine if we need to add a query against the tmp.* tables
*/
- bool addTmpQuery = (SchemaName == NULL)
+ bool inclTmpKey = (SchemaName == NULL)
|| (SchemaName != NULL
&& (strcmp((const char *) SchemaName, "tmp")
== 0
|| strchr((const char *) SchemaName, '%') !=
NULL
@@ -205,37 +205,141 @@ MNDBSpecialColumns(ODBCStmt *stmt,
}
/* construct the query */
- querylen = 5000 + (sch ? strlen(sch) : 0) + (tab ? strlen(tab)
: 0);
- if (addTmpQuery)
- querylen *= 2;
+ querylen = 6000 + (sch ? strlen(sch) : 0) + (tab ? strlen(tab)
: 0);
query = malloc(querylen);
if (query == NULL)
goto nomem;
+ /* When there is a PK for the table we return the pkey columns.
+ * When there is No PK but there are multiple unique
constraints, we need to pick one.
+ * In the current implementation we return the first uc (lowest
sys.keys.id).
+ * When there is no PK or unique constraints and it is not a
+ * view, we return all the columns of the table.
+ *
+ * Instead of the first uc (in case of multiple) we could
potentially use the uc which has
+ * a) the least number of columns and
+ * b) the most efficient datatype (integers) or smallest
total(size in bytes).
+ * That's much more complex to do in SQL than the current
implementation.
+ * The current implementation (picking first uc) is fast and
+ * gives a correct result, hence preferred.
+ */
+
+ /* 1st cte: syskeys */
+ pos += strcpy_len(query + pos,
+ "with syskeys as ("
+ /* all pkeys */
+ "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\"
WHERE \"type\" = 0 "
+ "UNION ALL "
+ /* and first unique constraint of a table when table
has no pkey */
+ "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\"
WHERE \"type\" = 1 "
+ "AND \"table_id\" NOT IN (select \"table_id\" from
\"sys\".\"keys\" where \"type\" = 0) "
+ "AND (\"table_id\", \"id\") IN (select \"table_id\",
min(\"id\") from \"sys\".\"keys\" where \"type\" = 1 group by \"table_id\"))",
+ querylen - pos);
+ if (inclTmpKey) {
+ /* we must also include the primary key or unique
constraint of local temporary tables which are stored in tmp.keys */
+ /* 2nd cte: tmpkeys */
+ pos += strcpy_len(query + pos,
+ ", tmpkeys as ("
+ "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\"
WHERE \"type\" = 0 "
+ "UNION ALL "
+ "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\"
WHERE \"type\" = 1 "
+ "AND \"table_id\" NOT IN (select \"table_id\" from
\"tmp\".\"keys\" where \"type\" = 0) "
+ "AND (\"table_id\", \"id\") IN (select \"table_id\",
min(\"id\") from \"tmp\".\"keys\" where \"type\" = 1 group by \"table_id\"))",
+ querylen - pos);
+ }
+ /* 3rd cte: tableids */
+ pos += strcpy_len(query + pos,
+ ", tableids as ("
+ "SELECT t.\"id\" "
+ "FROM \"sys\".\"tables\" t "
+ "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" =
s.\"id\" "
+ "WHERE t.\"type\" NOT IN (1, 11)", /* exclude all
VIEWs and SYSTEM VIEWs */
+ querylen - pos);
+ /* add the selection condition */
+ if (NameLength1 > 0 && CatalogName != NULL) {
+ /* filtering requested on catalog name */
+ if (strcmp((char *) CatalogName, stmt->Dbc->dbname) !=
0) {
+ /* catalog name does not match the database
name, so return no rows */
+ pos += strcpy_len(query + pos, " and 1=2",
querylen - pos);
+ }
+ }
+ if (sch) {
+ /* filtering requested on schema name */
+ pos += snprintf(query + pos, querylen - pos, " and %s",
sch);
+ }
+ if (tab) {
+ /* filtering requested on table name */
+ pos += snprintf(query + pos, querylen - pos, " and %s",
tab);
+ }
+ /* 4th cte: cols, this unions 2 (or 4 when inclTmpKey == true)
select queries */
+ pos += strcpy_len(query + pos,
+ "), cols as ("
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", o.\"nr\" "
+ "FROM syskeys k "
+ "JOIN tableids t ON k.\"table_id\" = t.\"id\" "
+ "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" "
+ "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\")",
+ querylen - pos);
+ /* add an extra selection when SQL_NO_NULLS is requested */
+ if (Nullable == SQL_NO_NULLS) {
+ pos += strcpy_len(query + pos, " WHERE c.\"null\" =
false", querylen - pos);
+ }
+ if (inclTmpKey) {
+ /* we must also include the primary key or unique
constraint of local temporary tables
+ * which are stored in tmp.keys, tmp.objects,
tmp._tables and tmp._columns */
+ pos += strcpy_len(query + pos,
+ " UNION ALL "
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", o.\"nr\" "
+ "FROM tmpkeys k "
+ "JOIN tableids t ON k.\"table_id\" = t.\"id\" "
+ "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" "
+ "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" =
c.\"table_id\" AND o.\"name\" = c.\"name\")",
+ querylen - pos);
+ /* add an extra selection when SQL_NO_NULLS is
requested */
+ if (Nullable == SQL_NO_NULLS) {
+ pos += strcpy_len(query + pos, " WHERE
c.\"null\" = false", querylen - pos);
+ }
+ }
+ /* when there is No PK and No unique constraints, we should
return all columns of the table */
+ pos += strcpy_len(query + pos,
+ " UNION ALL "
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", c.\"number\" "
+ "FROM tableids t "
+ "JOIN \"sys\".\"_columns\" c ON t.\"id\" =
c.\"table_id\" "
+ "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM
\"sys\".\"keys\" WHERE \"type\" in (0, 1))",
+ querylen - pos);
+ /* add an extra selection when SQL_NO_NULLS is requested */
+ if (Nullable == SQL_NO_NULLS) {
+ pos += strcpy_len(query + pos, " AND c.\"null\" =
false", querylen - pos);
+ }
+ if (inclTmpKey) {
+ pos += strcpy_len(query + pos,
+ " UNION ALL "
+ "SELECT c.\"name\", c.\"type\", c.\"type_digits\",
c.\"type_scale\", c.\"number\" "
+ "FROM tableids t "
+ "JOIN \"tmp\".\"_columns\" c ON t.\"id\" =
c.\"table_id\" "
+ "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM
\"tmp\".\"keys\" WHERE \"type\" in (0, 1))",
+ querylen - pos);
+ /* add an extra selection when SQL_NO_NULLS is
requested */
+ if (Nullable == SQL_NO_NULLS) {
+ pos += strcpy_len(query + pos, " AND c.\"null\"
= false", querylen - pos);
+ }
+ }
+ /* the final select query */
/* Note: SCOPE is SQL_SCOPE_TRANSACTION */
/* Note: PSEUDO_COLUMN is SQL_PC_NOT_PSEUDO */
pos += snprintf(query + pos, querylen - pos,
- "with sc as ("
- "select t.id as table_id, k.type as type, "
- "cast(%d as smallint) as scope, "
- "c.name as column_name, "
+ ") SELECT "
+ "cast(%d AS smallint) AS \"SCOPE\", "
+ "c.\"name\" AS \"COLUMN_NAME\", "
DATA_TYPE(c) ", "
TYPE_NAME(c) ", "
COLUMN_SIZE(c) ", "
BUFFER_LENGTH(c) ", "
DECIMAL_DIGITS(c) ", "
- "cast(%d as smallint) as pseudo_column "
- "from sys.schemas s, "
- "sys._tables t, "
- "sys._columns c, "
- "sys.keys k, "
- "sys.objects kc "
- "where s.id = t.schema_id and "
- "t.id = c.table_id and "
- "t.id = k.table_id and "
- "c.name = kc.name and "
- "kc.id = k.id and "
- "k.type in (0, 1)", /* primary key (type =
0), unique key (type = 1) */
+ "cast(%d AS smallint) AS \"PSEUDO_COLUMN\" "
+ "FROM cols c "
+ "ORDER BY \"SCOPE\", c.\"nr\", \"COLUMN_NAME\"",
/* scope: */
SQL_SCOPE_TRANSACTION,
#ifdef DATA_TYPE_ARGS
@@ -255,168 +359,12 @@ MNDBSpecialColumns(ODBCStmt *stmt,
#endif
/* pseudo_column: */
SQL_PC_NOT_PSEUDO);
- assert(pos < 4300);
- /* TODO: improve the SQL to get the correct result:
- - only one set of unique constraint columns should be
- returned when multiple unique constraints are available
- for this table. Return the smallest/best one only.
- TODO: optimize SQL:
- - when no SchemaName is set (see above) also no
- filtering on SCHEMA NAME and join with table
- SCHEMAS is needed!
- */
-
- /* add the selection condition */
- if (NameLength1 > 0 && CatalogName != NULL) {
- /* filtering requested on catalog name */
- if (strcmp((char *) CatalogName, stmt->Dbc->dbname) !=
0) {
- /* catalog name does not match the database
name, so return no rows */
- pos += snprintf(query + pos, querylen - pos, "
and 1=2");
- }
- }
- if (sch) {
- /* filtering requested on schema name */
- pos += snprintf(query + pos, querylen - pos, " and %s",
sch);
- }
- if (tab) {
- /* filtering requested on table name */
- pos += snprintf(query + pos, querylen - pos, " and %s",
tab);
- }
-
- /* add an extra selection when SQL_NO_NULLS is requested */
- if (Nullable == SQL_NO_NULLS) {
- pos += strcpy_len(query + pos, " and c.\"null\" =
false", querylen - pos);
- }
-
- pos += strcpy_len(query + pos,
- "), "
- "tid as ("
- "select table_id as tid "
- "from sys.keys "
- "where type = 0"
- ") "
- , querylen - pos);
-
- if (addTmpQuery) {
- /* we must also include the primary key or unique
- constraint of local temporary tables which are stored
- in tmp.keys, tmp.objects, tmp._tables and
tmp._columns */
-
- /* Note: SCOPE is SQL_SCOPE_TRANSACTION */
- /* Note: PSEUDO_COLUMN is SQL_PC_NOT_PSEUDO */
- pos += snprintf(query + pos, querylen - pos,
- ", tmpsc as ("
- "select t.id as table_id, k.type as type, "
- "cast(%d as smallint) as scope, "
- "c.name as column_name, "
- DATA_TYPE(c) ", "
- TYPE_NAME(c) ", "
- COLUMN_SIZE(c) ", "
- BUFFER_LENGTH(c) ", "
- DECIMAL_DIGITS(c) ", "
- "cast(%d as smallint) as pseudo_column "
- "from sys.schemas s, "
- "tmp._tables t, "
- "tmp._columns c, "
- "tmp.keys k, "
- "tmp.objects kc "
- "where s.id = t.schema_id and "
- "t.id = c.table_id and "
- "t.id = k.table_id and "
- "c.name = kc.name and "
- "kc.id = k.id and "
- "k.type in (0, 1)", /* primary key
(type = 0), unique key (type = 1) */
- /* scope: */
- SQL_SCOPE_TRANSACTION,
-#ifdef DATA_TYPE_ARGS
- DATA_TYPE_ARGS,
-#endif
-#ifdef TYPE_NAME_ARGS
- TYPE_NAME_ARGS,
-#endif
-#ifdef COLUMN_SIZE_ARGS
- COLUMN_SIZE_ARGS,
-#endif
-#ifdef BUFFER_SIZE_ARGS
- BUFFER_SIZE_ARGS,
-#endif
-#ifdef DECIMAL_DIGITS_ARGS
- DECIMAL_DIGITS_ARGS,
-#endif
- /* pseudo_column: */
- SQL_PC_NOT_PSEUDO);
-
- /* add the selection condition */
- if (NameLength1 > 0 && CatalogName != NULL) {
- /* filtering requested on catalog name */
- if (strcmp((char *) CatalogName,
stmt->Dbc->dbname) != 0) {
- /* catalog name does not match the
database name, so return no rows */
- pos += snprintf(query + pos, querylen -
pos, " and 1=2");
- }
- }
- if (sch) {
- /* filtering requested on schema name */
- pos += snprintf(query + pos, querylen - pos, "
and %s", sch);
- }
- if (tab) {
- /* filtering requested on table name */
- pos += snprintf(query + pos, querylen - pos, "
and %s", tab);
- }
-
- /* add an extra selection when SQL_NO_NULLS is
requested */
- if (Nullable == SQL_NO_NULLS) {
- pos += strcpy_len(query + pos, " and c.\"null\"
= false", querylen - pos);
- }
-
- pos += strcpy_len(query + pos,
- "), "
- "tmptid as ("
- "select table_id as tid "
- "from tmp.keys "
- "where type = 0"
- ") "
- , querylen - pos);
- }
- assert(pos < (querylen - 500));
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
