Line data Source code
1 : /*
2 : * SPDX-License-Identifier: MPL-2.0
3 : *
4 : * This Source Code Form is subject to the terms of the Mozilla Public
5 : * License, v. 2.0. If a copy of the MPL was not distributed with this
6 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
7 : *
8 : * Copyright 2024 MonetDB Foundation;
9 : * Copyright August 2008 - 2023 MonetDB B.V.;
10 : * Copyright 1997 - July 2008 CWI.
11 : */
12 :
13 : /*
14 : * This code was created by Peter Harvey (mostly during Christmas 98/99).
15 : * This code is LGPL. Please ensure that this message remains in future
16 : * distributions and uses of this code (that's about all I get out of it).
17 : * - Peter Harvey pharvey@codebydesign.com
18 : *
19 : * This file has been modified for the MonetDB project. See the file
20 : * Copyright in this directory for more information.
21 : */
22 :
23 : /**********************************************************************
24 : * SQLSpecialColumns()
25 : * CLI Compliance: X/Open
26 : *
27 : * Author: Martin van Dinther, Sjoerd Mullender
28 : * Date : 30 aug 2002
29 : *
30 : **********************************************************************/
31 :
32 : #include "ODBCGlobal.h"
33 : #include "ODBCStmt.h"
34 : #include "ODBCUtil.h"
35 : #include "ODBCQueries.h"
36 :
37 :
38 : #ifdef ODBCDEBUG
39 : static char *
40 10 : translateIdentifierType(SQLUSMALLINT IdentifierType)
41 : {
42 10 : switch (IdentifierType) {
43 : case SQL_BEST_ROWID:
44 : return "SQL_BEST_ROWID";
45 2 : case SQL_ROWVER:
46 2 : return "SQL_ROWVER";
47 0 : default:
48 0 : return "unknown";
49 : }
50 : }
51 :
52 : static char *
53 10 : translateScope(SQLUSMALLINT Scope)
54 : {
55 10 : switch (Scope) {
56 : case SQL_SCOPE_CURROW:
57 : return "SQL_SCOPE_CURROW";
58 0 : case SQL_SCOPE_TRANSACTION:
59 0 : return "SQL_SCOPE_TRANSACTION";
60 10 : case SQL_SCOPE_SESSION:
61 10 : return "SQL_SCOPE_SESSION";
62 0 : default:
63 0 : return "unknown";
64 : }
65 : }
66 :
67 : static char *
68 10 : translateNullable(SQLUSMALLINT Nullable)
69 : {
70 10 : switch (Nullable) {
71 : case SQL_NO_NULLS:
72 : return "SQL_NO_NULLS";
73 0 : case SQL_NULLABLE:
74 0 : return "SQL_NULLABLE";
75 0 : default:
76 0 : return "unknown";
77 : }
78 : }
79 : #endif
80 :
81 : static SQLRETURN
82 10 : MNDBSpecialColumns(ODBCStmt *stmt,
83 : SQLUSMALLINT IdentifierType,
84 : const SQLCHAR *CatalogName,
85 : SQLSMALLINT NameLength1,
86 : const SQLCHAR *SchemaName,
87 : SQLSMALLINT NameLength2,
88 : const SQLCHAR *TableName,
89 : SQLSMALLINT NameLength3,
90 : SQLUSMALLINT Scope,
91 : SQLUSMALLINT Nullable)
92 : {
93 10 : RETCODE rc;
94 :
95 : /* buffer for the constructed query to do meta data retrieval */
96 10 : char *query = NULL;
97 10 : size_t pos = 0;
98 10 : char *sch = NULL, *tab = NULL;
99 :
100 10 : fixODBCstring(CatalogName, NameLength1, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);
101 10 : fixODBCstring(SchemaName, NameLength2, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);
102 10 : fixODBCstring(TableName, NameLength3, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);
103 :
104 : #ifdef ODBCDEBUG
105 21 : ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\" %s %s\n",
106 : (int) NameLength1, CatalogName ? (char *) CatalogName : "",
107 : (int) NameLength2, SchemaName ? (char *) SchemaName : "",
108 : (int) NameLength3, TableName ? (char *) TableName : "",
109 : translateScope(Scope), translateNullable(Nullable));
110 : #endif
111 :
112 : /* check for valid IdentifierType argument */
113 10 : switch (IdentifierType) {
114 : case SQL_BEST_ROWID:
115 : case SQL_ROWVER:
116 10 : break;
117 0 : default:
118 : /* Column type out of range */
119 0 : addStmtError(stmt, "HY097", NULL, 0);
120 0 : return SQL_ERROR;
121 : }
122 :
123 : /* check for valid Scope argument */
124 10 : switch (Scope) {
125 : case SQL_SCOPE_CURROW:
126 : case SQL_SCOPE_TRANSACTION:
127 : case SQL_SCOPE_SESSION:
128 10 : break;
129 0 : default:
130 : /* Scope type out of range */
131 0 : addStmtError(stmt, "HY098", NULL, 0);
132 0 : return SQL_ERROR;
133 : }
134 :
135 : /* check for valid Nullable argument */
136 10 : switch (Nullable) {
137 : case SQL_NO_NULLS:
138 : case SQL_NULLABLE:
139 10 : break;
140 0 : default:
141 : /* Nullable type out of range */
142 0 : addStmtError(stmt, "HY099", NULL, 0);
143 0 : return SQL_ERROR;
144 : }
145 :
146 : /* check if a valid (non null, not empty) table name is supplied */
147 10 : if (TableName == NULL) {
148 : /* Invalid use of null pointer */
149 0 : addStmtError(stmt, "HY009", NULL, 0);
150 0 : return SQL_ERROR;
151 : }
152 10 : if (NameLength3 == 0) {
153 : /* Invalid string or buffer length */
154 0 : addStmtError(stmt, "HY090", NULL, 0);
155 0 : return SQL_ERROR;
156 : }
157 :
158 : /* SQLSpecialColumns returns a table with the following columns:
159 : SMALLINT SCOPE
160 : VARCHAR COLUMN_NAME NOT NULL
161 : SMALLINT DATA_TYPE NOT NULL
162 : VARCHAR TYPE_NAME NOT NULL
163 : INTEGER COLUMN_SIZE
164 : INTEGER BUFFER_LENGTH
165 : SMALLINT DECIMAL_DIGITS
166 : SMALLINT PSEUDO_COLUMN
167 : */
168 10 : if (IdentifierType == SQL_BEST_ROWID) {
169 8 : size_t querylen;
170 :
171 : /* determine if we need to add a query against the tmp.* tables */
172 16 : bool inclTmpKey = (SchemaName == NULL)
173 8 : || (SchemaName != NULL
174 8 : && (strcmp((const char *) SchemaName, "tmp") == 0
175 4 : || strchr((const char *) SchemaName, '%') != NULL
176 4 : || strchr((const char *) SchemaName, '_') != NULL));
177 :
178 : /* Select from the key table the (smallest) primary/unique key */
179 8 : if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
180 8 : if (NameLength2 > 0) {
181 8 : sch = ODBCParseOA("s", "name",
182 : (const char *) SchemaName,
183 : (size_t) NameLength2);
184 8 : if (sch == NULL)
185 0 : goto nomem;
186 : }
187 8 : if (NameLength3 > 0) {
188 8 : tab = ODBCParseOA("t", "name",
189 : (const char *) TableName,
190 : (size_t) NameLength3);
191 8 : if (tab == NULL)
192 0 : goto nomem;
193 : }
194 : } else {
195 0 : if (NameLength2 > 0) {
196 0 : sch = ODBCParseID("s", "name",
197 : (const char *) SchemaName,
198 : (size_t) NameLength2);
199 0 : if (sch == NULL)
200 0 : goto nomem;
201 : }
202 0 : if (NameLength3 > 0) {
203 0 : tab = ODBCParseID("t", "name",
204 : (const char *) TableName,
205 : (size_t) NameLength3);
206 0 : if (tab == NULL)
207 0 : goto nomem;
208 : }
209 : }
210 :
211 : /* construct the query */
212 8 : querylen = 6130 + (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0);
213 8 : query = malloc(querylen);
214 8 : if (query == NULL)
215 0 : goto nomem;
216 :
217 : /* When there is a PK for the table we return the pkey columns.
218 : * When there is No PK but there are multiple unique constraints, we need to pick one.
219 : * In the current implementation we return the first uc (lowest sys.keys.id).
220 : * When there is no PK or unique constraints and it is not a
221 : * view, we return all the columns of the table.
222 : *
223 : * Instead of the first uc (in case of multiple) we could potentially use the uc which has
224 : * a) the least number of columns and
225 : * b) the most efficient datatype (integers) or smallest total(size in bytes).
226 : * That's much more complex to do in SQL than the current implementation.
227 : * The current implementation (picking first uc) is fast and
228 : * gives a correct result, hence preferred.
229 : */
230 :
231 : /* 1st cte: syskeys */
232 8 : pos += strcpy_len(query + pos,
233 : "with syskeys as ("
234 : /* all pkeys */
235 : "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" = 0 "
236 : "UNION ALL "
237 : /* and first unique constraint of a table when table has no pkey */
238 : "SELECT \"id\", \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" IN (1, 3) "
239 : "AND \"table_id\" NOT IN (select \"table_id\" from \"sys\".\"keys\" where \"type\" = 0) "
240 : "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"sys\".\"keys\" where \"type\" IN (1, 3) group by \"table_id\"))",
241 : querylen - pos);
242 8 : if (inclTmpKey) {
243 : /* we must also include the primary key or unique constraint of local temporary tables which are stored in tmp.keys */
244 : /* 2nd cte: tmpkeys */
245 4 : pos += strcpy_len(query + pos,
246 : ", tmpkeys as ("
247 : "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" = 0 "
248 : "UNION ALL "
249 : "SELECT \"id\", \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" IN (1, 3) "
250 : "AND \"table_id\" NOT IN (select \"table_id\" from \"tmp\".\"keys\" where \"type\" = 0) "
251 : "AND (\"table_id\", \"id\") IN (select \"table_id\", min(\"id\") from \"tmp\".\"keys\" where \"type\" IN (1, 3) group by \"table_id\"))",
252 : querylen - pos);
253 : }
254 : /* 3rd cte: tableids */
255 8 : pos += strcpy_len(query + pos,
256 : ", tableids as ("
257 : "SELECT t.\"id\" "
258 : "FROM \"sys\".\"tables\" t "
259 : "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" "
260 : "WHERE t.\"type\" NOT IN (1, 11)", /* exclude all VIEWs and SYSTEM VIEWs */
261 : querylen - pos);
262 : /* add the selection condition */
263 8 : if (NameLength1 > 0 && CatalogName != NULL) {
264 : /* filtering requested on catalog name */
265 0 : if (strcmp((char *) CatalogName, msetting_string(stmt->Dbc->settings, MP_DATABASE)) != 0) {
266 : /* catalog name does not match the database name, so return no rows */
267 0 : pos += strcpy_len(query + pos, " and 1=2", querylen - pos);
268 : }
269 : }
270 8 : if (sch) {
271 : /* filtering requested on schema name */
272 8 : pos += snprintf(query + pos, querylen - pos, " and %s", sch);
273 : }
274 8 : if (tab) {
275 : /* filtering requested on table name */
276 8 : pos += snprintf(query + pos, querylen - pos, " and %s", tab);
277 : }
278 : /* 4th cte: cols, this unions 2 (or 4 when inclTmpKey == true) select queries */
279 8 : pos += strcpy_len(query + pos,
280 : "), cols as ("
281 : "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" "
282 : "FROM syskeys k "
283 : "JOIN tableids t ON k.\"table_id\" = t.\"id\" "
284 : "JOIN \"sys\".\"objects\" o ON k.\"id\" = o.\"id\" "
285 : "JOIN \"sys\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\")",
286 : querylen - pos);
287 : /* add an extra selection when SQL_NO_NULLS is requested */
288 8 : if (Nullable == SQL_NO_NULLS) {
289 8 : pos += strcpy_len(query + pos, " WHERE c.\"null\" = false", querylen - pos);
290 : }
291 8 : if (inclTmpKey) {
292 : /* we must also include the primary key or unique constraint of local temporary tables
293 : * which are stored in tmp.keys, tmp.objects, tmp._tables and tmp._columns */
294 4 : pos += strcpy_len(query + pos,
295 : " UNION ALL "
296 : "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", o.\"nr\" "
297 : "FROM tmpkeys k "
298 : "JOIN tableids t ON k.\"table_id\" = t.\"id\" "
299 : "JOIN \"tmp\".\"objects\" o ON k.\"id\" = o.\"id\" "
300 : "JOIN \"tmp\".\"_columns\" c ON (k.\"table_id\" = c.\"table_id\" AND o.\"name\" = c.\"name\")",
301 : querylen - pos);
302 : /* add an extra selection when SQL_NO_NULLS is requested */
303 4 : if (Nullable == SQL_NO_NULLS) {
304 4 : pos += strcpy_len(query + pos, " WHERE c.\"null\" = false", querylen - pos);
305 : }
306 : }
307 : /* when there is No PK and No unique constraints, we should return all columns of the table */
308 8 : pos += strcpy_len(query + pos,
309 : " UNION ALL "
310 : "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" "
311 : "FROM tableids t "
312 : "JOIN \"sys\".\"_columns\" c ON t.\"id\" = c.\"table_id\" "
313 : "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"sys\".\"keys\" WHERE \"type\" IN (0, 1, 3))",
314 : querylen - pos);
315 : /* add an extra selection when SQL_NO_NULLS is requested */
316 8 : if (Nullable == SQL_NO_NULLS) {
317 8 : pos += strcpy_len(query + pos, " AND c.\"null\" = false", querylen - pos);
318 : }
319 8 : if (inclTmpKey) {
320 4 : pos += strcpy_len(query + pos,
321 : " UNION ALL "
322 : "SELECT c.\"name\", c.\"type\", c.\"type_digits\", c.\"type_scale\", c.\"number\" "
323 : "FROM tableids t "
324 : "JOIN \"tmp\".\"_columns\" c ON t.\"id\" = c.\"table_id\" "
325 : "WHERE t.\"id\" NOT IN (SELECT \"table_id\" FROM \"tmp\".\"keys\" WHERE \"type\" IN (0, 1, 3))",
326 : querylen - pos);
327 : /* add an extra selection when SQL_NO_NULLS is requested */
328 4 : if (Nullable == SQL_NO_NULLS) {
329 4 : pos += strcpy_len(query + pos, " AND c.\"null\" = false", querylen - pos);
330 : }
331 : }
332 : /* the final select query */
333 : /* Note: SCOPE is SQL_SCOPE_TRANSACTION */
334 : /* Note: PSEUDO_COLUMN is SQL_PC_NOT_PSEUDO */
335 8 : pos += snprintf(query + pos, querylen - pos,
336 : ") SELECT "
337 : "cast(%d AS smallint) AS \"SCOPE\", "
338 : "c.\"name\" AS \"COLUMN_NAME\", "
339 : DATA_TYPE(c) ", "
340 : TYPE_NAME(c) ", "
341 : COLUMN_SIZE(c) ", "
342 : BUFFER_LENGTH(c) ", "
343 : DECIMAL_DIGITS(c) ", "
344 : "cast(%d AS smallint) AS \"PSEUDO_COLUMN\" "
345 : "FROM cols c "
346 : "ORDER BY \"SCOPE\", c.\"nr\", \"COLUMN_NAME\"",
347 : /* scope: */
348 : SQL_SCOPE_TRANSACTION,
349 : #ifdef DATA_TYPE_ARGS
350 : DATA_TYPE_ARGS,
351 : #endif
352 : #ifdef TYPE_NAME_ARGS
353 : TYPE_NAME_ARGS,
354 : #endif
355 : #ifdef COLUMN_SIZE_ARGS
356 : COLUMN_SIZE_ARGS,
357 : #endif
358 : #ifdef BUFFER_SIZE_ARGS
359 : BUFFER_SIZE_ARGS,
360 : #endif
361 : #ifdef DECIMAL_DIGITS_ARGS
362 : DECIMAL_DIGITS_ARGS,
363 : #endif
364 : /* pseudo_column: */
365 : SQL_PC_NOT_PSEUDO);
366 :
367 8 : if (sch)
368 8 : free(sch);
369 8 : if (tab)
370 8 : free(tab);
371 :
372 8 : if (pos >= querylen)
373 0 : fprintf(stderr, "pos >= querylen, %zu > %zu\n", pos, querylen);
374 8 : assert(pos < querylen);
375 : } else {
376 2 : assert(IdentifierType == SQL_ROWVER);
377 : /* The backend does not have such info available */
378 : /* create just a query which results in zero rows */
379 : /* Note: PSEUDO_COLUMN is SQL_PC_UNKNOWN is 0 */
380 2 : query = strdup("select cast(null as smallint) as \"SCOPE\", "
381 : "cast('' as varchar(1)) as \"COLUMN_NAME\", "
382 : "cast(1 as smallint) as \"DATA_TYPE\", "
383 : "cast('char' as varchar(4)) as \"TYPE_NAME\", "
384 : "cast(1 as integer) as \"COLUMN_SIZE\", "
385 : "cast(1 as integer) as \"BUFFER_LENGTH\", "
386 : "cast(0 as smallint) as \"DECIMAL_DIGITS\", "
387 : "cast(0 as smallint) as \"PSEUDO_COLUMN\" "
388 : "where 0 = 1");
389 2 : if (query == NULL)
390 0 : goto nomem;
391 2 : pos = strlen(query);
392 : }
393 :
394 : /* debug: fprintf(stdout, "SQLSpecialColumns query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */
395 :
396 : /* query the MonetDB data dictionary tables */
397 10 : rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos);
398 :
399 10 : free(query);
400 :
401 10 : return rc;
402 :
403 0 : nomem:
404 : /* note that query must be NULL when we get here */
405 0 : if (sch)
406 0 : free(sch);
407 0 : if (tab)
408 0 : free(tab);
409 : /* Memory allocation error */
410 0 : addStmtError(stmt, "HY001", NULL, 0);
411 0 : return SQL_ERROR;
412 : }
413 :
414 : SQLRETURN SQL_API
415 : SQLSpecialColumns(SQLHSTMT StatementHandle,
416 : SQLUSMALLINT IdentifierType,
417 : SQLCHAR *CatalogName,
418 : SQLSMALLINT NameLength1,
419 : SQLCHAR *SchemaName,
420 : SQLSMALLINT NameLength2,
421 : SQLCHAR *TableName,
422 : SQLSMALLINT NameLength3,
423 : SQLUSMALLINT Scope,
424 : SQLUSMALLINT Nullable)
425 : {
426 10 : ODBCStmt *stmt = (ODBCStmt *) StatementHandle;
427 :
428 : #ifdef ODBCDEBUG
429 12 : ODBCLOG("SQLSpecialColumns %p %s ",
430 : StatementHandle,
431 : translateIdentifierType(IdentifierType));
432 : #endif
433 :
434 10 : if (!isValidStmt(stmt))
435 : return SQL_INVALID_HANDLE;
436 :
437 10 : clearStmtErrors(stmt);
438 :
439 10 : return MNDBSpecialColumns(stmt,
440 : IdentifierType,
441 : CatalogName, NameLength1,
442 : SchemaName, NameLength2,
443 : TableName, NameLength3,
444 : Scope,
445 : Nullable);
446 : }
447 :
448 : SQLRETURN SQL_API
449 : SQLSpecialColumnsA(SQLHSTMT StatementHandle,
450 : SQLUSMALLINT IdentifierType,
451 : SQLCHAR *CatalogName,
452 : SQLSMALLINT NameLength1,
453 : SQLCHAR *SchemaName,
454 : SQLSMALLINT NameLength2,
455 : SQLCHAR *TableName,
456 : SQLSMALLINT NameLength3,
457 : SQLUSMALLINT Scope,
458 : SQLUSMALLINT Nullable)
459 : {
460 0 : return SQLSpecialColumns(StatementHandle,
461 : IdentifierType,
462 : CatalogName, NameLength1,
463 : SchemaName, NameLength2,
464 : TableName, NameLength3,
465 : Scope,
466 : Nullable);
467 : }
468 :
469 : SQLRETURN SQL_API
470 : SQLSpecialColumnsW(SQLHSTMT StatementHandle,
471 : SQLUSMALLINT IdentifierType,
472 : SQLWCHAR *CatalogName,
473 : SQLSMALLINT NameLength1,
474 : SQLWCHAR *SchemaName,
475 : SQLSMALLINT NameLength2,
476 : SQLWCHAR *TableName,
477 : SQLSMALLINT NameLength3,
478 : SQLUSMALLINT Scope,
479 : SQLUSMALLINT Nullable)
480 : {
481 0 : ODBCStmt *stmt = (ODBCStmt *) StatementHandle;
482 0 : SQLRETURN rc = SQL_ERROR;
483 0 : SQLCHAR *catalog = NULL, *schema = NULL, *table = NULL;
484 :
485 : #ifdef ODBCDEBUG
486 0 : ODBCLOG("SQLSpecialColumnsW %p %s ",
487 : StatementHandle,
488 : translateIdentifierType(IdentifierType));
489 : #endif
490 :
491 0 : if (!isValidStmt(stmt))
492 : return SQL_INVALID_HANDLE;
493 :
494 0 : clearStmtErrors(stmt);
495 :
496 0 : fixWcharIn(CatalogName, NameLength1, SQLCHAR, catalog,
497 : addStmtError, stmt, goto bailout);
498 0 : fixWcharIn(SchemaName, NameLength2, SQLCHAR, schema,
499 : addStmtError, stmt, goto bailout);
500 0 : fixWcharIn(TableName, NameLength3, SQLCHAR, table,
501 : addStmtError, stmt, goto bailout);
502 :
503 0 : rc = MNDBSpecialColumns(stmt,
504 : IdentifierType,
505 : catalog, SQL_NTS,
506 : schema, SQL_NTS,
507 : table, SQL_NTS,
508 : Scope,
509 : Nullable);
510 :
511 0 : bailout:
512 0 : if (catalog)
513 0 : free(catalog);
514 0 : if (schema)
515 0 : free(schema);
516 0 : if (table)
517 0 : free(table);
518 :
519 : return rc;
520 : }
|
