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 : * SQLForeignKeys()
25 : * CLI Compliance: ODBC (Microsoft)
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 :
36 : static SQLRETURN
37 5 : MNDBForeignKeys(ODBCStmt *stmt,
38 : const SQLCHAR *PKCatalogName,
39 : SQLSMALLINT NameLength1,
40 : const SQLCHAR *PKSchemaName,
41 : SQLSMALLINT NameLength2,
42 : const SQLCHAR *PKTableName,
43 : SQLSMALLINT NameLength3,
44 : const SQLCHAR *FKCatalogName,
45 : SQLSMALLINT NameLength4,
46 : const SQLCHAR *FKSchemaName,
47 : SQLSMALLINT NameLength5,
48 : const SQLCHAR *FKTableName,
49 : SQLSMALLINT NameLength6)
50 : {
51 5 : RETCODE rc;
52 :
53 : /* buffer for the constructed query to do meta data retrieval */
54 5 : char *query = NULL;
55 5 : size_t querylen;
56 5 : size_t pos = 0;
57 5 : char *psch = NULL, *ptab = NULL;
58 5 : char *fsch = NULL, *ftab = NULL;
59 :
60 : /* deal with SQL_NTS and SQL_NULL_DATA */
61 5 : fixODBCstring(PKCatalogName, NameLength1, SQLSMALLINT,
62 : addStmtError, stmt, return SQL_ERROR);
63 5 : fixODBCstring(PKSchemaName, NameLength2, SQLSMALLINT,
64 : addStmtError, stmt, return SQL_ERROR);
65 5 : fixODBCstring(PKTableName, NameLength3, SQLSMALLINT,
66 : addStmtError, stmt, return SQL_ERROR);
67 5 : fixODBCstring(FKCatalogName, NameLength4, SQLSMALLINT,
68 : addStmtError, stmt, return SQL_ERROR);
69 5 : fixODBCstring(FKSchemaName, NameLength5, SQLSMALLINT,
70 : addStmtError, stmt, return SQL_ERROR);
71 5 : fixODBCstring(FKTableName, NameLength6, SQLSMALLINT,
72 : addStmtError, stmt, return SQL_ERROR);
73 :
74 : #ifdef ODCBDEBUG
75 : ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\"\n",
76 : (int) NameLength1, PKCatalogName ? (char *) PKCatalogName : "",
77 : (int) NameLength2, PKSchemaName ? (char *) PKSchemaName : "",
78 : (int) NameLength3, PKTableName ? (char *) PKTableName : "",
79 : (int) NameLength4, FKCatalogName ? (char *) FKCatalogName : "",
80 : (int) NameLength5, FKSchemaName ? (char *) FKSchemaName : "",
81 : (int) NameLength6, FKTableName ? (char *) FKTableName : "");
82 : #endif
83 : /* dependent on the input parameter values we must add a
84 : variable selection condition dynamically */
85 :
86 5 : if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
87 5 : if (NameLength2 > 0) {
88 2 : psch = ODBCParseOA("pks", "name",
89 : (const char *) PKSchemaName,
90 : (size_t) NameLength2);
91 2 : if (psch == NULL)
92 0 : goto nomem;
93 : }
94 5 : if (NameLength3 > 0) {
95 3 : ptab = ODBCParseOA("pkt", "name",
96 : (const char *) PKTableName,
97 : (size_t) NameLength3);
98 3 : if (ptab == NULL)
99 0 : goto nomem;
100 : }
101 5 : if (NameLength5 > 0) {
102 2 : fsch = ODBCParseOA("fks", "name",
103 : (const char *) FKSchemaName,
104 : (size_t) NameLength5);
105 2 : if (fsch == NULL)
106 0 : goto nomem;
107 : }
108 5 : if (NameLength6 > 0) {
109 3 : ftab = ODBCParseOA("fkt", "name",
110 : (const char *) FKTableName,
111 : (size_t) NameLength6);
112 3 : if (ftab == NULL)
113 0 : goto nomem;
114 : }
115 : } else {
116 0 : if (NameLength2 > 0) {
117 0 : psch = ODBCParseID("pks", "name",
118 : (const char *) PKSchemaName,
119 : (size_t) NameLength2);
120 0 : if (psch == NULL)
121 0 : goto nomem;
122 : }
123 0 : if (NameLength3 > 0) {
124 0 : ptab = ODBCParseID("pkt", "name",
125 : (const char *) PKTableName,
126 : (size_t) NameLength3);
127 0 : if (ptab == NULL)
128 0 : goto nomem;
129 : }
130 0 : if (NameLength5 > 0) {
131 0 : fsch = ODBCParseID("fks", "name",
132 : (const char *) FKSchemaName,
133 : (size_t) NameLength5);
134 0 : if (fsch == NULL)
135 0 : goto nomem;
136 : }
137 0 : if (NameLength6 > 0) {
138 0 : ftab = ODBCParseID("fkt", "name",
139 : (const char *) FKTableName,
140 : (size_t) NameLength6);
141 0 : if (ftab == NULL)
142 0 : goto nomem;
143 : }
144 : }
145 :
146 : /* first create a string buffer (1300 extra bytes is plenty:
147 : we actually need just over 1100) */
148 5 : querylen = 1300 +
149 5 : (psch ? strlen(psch) : 0) + (ptab ? strlen(ptab) : 0) +
150 5 : (fsch ? strlen(fsch) : 0) + (ftab ? strlen(ftab) : 0);
151 5 : query = malloc(querylen);
152 5 : if (query == NULL)
153 0 : goto nomem;
154 :
155 : /* SQLForeignKeys returns a table with the following columns:
156 : VARCHAR PKTABLE_CAT
157 : VARCHAR PKTABLE_SCHEM
158 : VARCHAR PKTABLE_NAME NOT NULL
159 : VARCHAR PKCOLUMN_NAME NOT NULL
160 : VARCHAR FKTABLE_CAT
161 : VARCHAR FKTABLE_SCHEM
162 : VARCHAR FKTABLE_NAME NOT NULL
163 : VARCHAR FKCOLUMN_NAME NOT NULL
164 : SMALLINT KEY_SEQ NOT NULL
165 : SMALLINT UPDATE_RULE
166 : SMALLINT DELETE_RULE
167 : VARCHAR FK_NAME
168 : VARCHAR PK_NAME
169 : SMALLINT DEFERRABILITY
170 : */
171 :
172 5 : pos += snprintf(query + pos, querylen - pos,
173 : "select cast(null as varchar(1)) as \"PKTABLE_CAT\", "
174 : "pks.name as \"PKTABLE_SCHEM\", "
175 : "pkt.name as \"PKTABLE_NAME\", "
176 : "pkkc.name as \"PKCOLUMN_NAME\", "
177 : "cast(null as varchar(1)) as \"FKTABLE_CAT\", "
178 : "fks.name as \"FKTABLE_SCHEM\", "
179 : "fkt.name as \"FKTABLE_NAME\", "
180 : "fkkc.name as \"FKCOLUMN_NAME\", "
181 : "cast(fkkc.nr + 1 as smallint) as \"KEY_SEQ\", "
182 : /* logic for "action" value interpretation pulled from clients/mapiclient/dump.c dump_foreign_keys() */
183 : /* for "action" code values meaning see table: sys.fkey_actions */
184 : "cast(CASE ((fkk.\"action\" >> 8) & 255)"
185 : " WHEN 0 THEN %d WHEN 1 THEN %d WHEN 2 THEN %d"
186 : " WHEN 3 THEN %d WHEN 4 THEN %d ELSE %d END"
187 : " AS smallint) as \"UPDATE_RULE\", "
188 : "cast(CASE (fkk.\"action\" & 255)"
189 : " WHEN 0 THEN %d WHEN 1 THEN %d WHEN 2 THEN %d"
190 : " WHEN 3 THEN %d WHEN 4 THEN %d ELSE %d END"
191 : " AS smallint) as \"DELETE_RULE\", "
192 : "fkk.name as \"FK_NAME\", "
193 : "pkk.name as \"PK_NAME\", "
194 : "cast(%d as smallint) as \"DEFERRABILITY\" "
195 : "from sys.keys as fkk, sys.objects fkkc, sys.tables fkt, sys.schemas fks, "
196 : "sys.keys as pkk, sys.objects pkkc, sys.tables pkt, sys.schemas pks "
197 : "where fkk.rkey > 0 and " /* exclude invalid rkey references, such as -1 first */
198 : "fkk.rkey = pkk.id and "
199 : "fkk.id = fkkc.id and "
200 : "pkk.id = pkkc.id and "
201 : "fkkc.nr = pkkc.nr and "
202 : "fkt.id = fkk.table_id and "
203 : "pkt.id = pkk.table_id and "
204 : "fks.id = fkt.schema_id and "
205 : "pks.id = pkt.schema_id",
206 : SQL_NO_ACTION, SQL_CASCADE, SQL_RESTRICT, SQL_SET_NULL, SQL_SET_DEFAULT, SQL_NO_ACTION,
207 : SQL_NO_ACTION, SQL_CASCADE, SQL_RESTRICT, SQL_SET_NULL, SQL_SET_DEFAULT, SQL_NO_ACTION,
208 : SQL_NOT_DEFERRABLE);
209 5 : assert(pos < 1200);
210 :
211 : /* Construct the selection condition query part */
212 5 : if (NameLength1 > 0 && PKCatalogName != NULL) {
213 : /* filtering requested on catalog name */
214 0 : if (strcmp((char *) PKCatalogName, msetting_string(stmt->Dbc->settings, MP_DATABASE)) != 0) {
215 : /* catalog name does not match the database name, so return no rows */
216 0 : pos += snprintf(query + pos, querylen - pos, " and 1=2");
217 : }
218 : }
219 5 : if (psch) {
220 : /* filtering requested on schema name */
221 2 : pos += snprintf(query + pos, querylen - pos, " and %s", psch);
222 2 : free(psch);
223 : }
224 5 : if (ptab) {
225 : /* filtering requested on table name */
226 3 : pos += snprintf(query + pos, querylen - pos, " and %s", ptab);
227 3 : free(ptab);
228 : }
229 5 : if (NameLength4 > 0 && FKCatalogName != NULL) {
230 : /* filtering requested on catalog name */
231 0 : if (strcmp((char *) FKCatalogName, msetting_string(stmt->Dbc->settings, MP_DATABASE)) != 0) {
232 : /* catalog name does not match the database name, so return no rows */
233 0 : pos += snprintf(query + pos, querylen - pos, " and 1=2");
234 : }
235 : }
236 5 : if (fsch) {
237 : /* filtering requested on schema name */
238 2 : pos += snprintf(query + pos, querylen - pos, " and %s", fsch);
239 2 : free(fsch);
240 : }
241 5 : if (ftab) {
242 : /* filtering requested on table name */
243 3 : pos += snprintf(query + pos, querylen - pos, " and %s", ftab);
244 3 : free(ftab);
245 : }
246 :
247 : /* add the ordering */
248 : /* if PKTableName != NULL, selection on primary key, order
249 : on FK output columns, else order on PK output columns */
250 : /* MvD: added additional ordering on FK_NAME or PK_NAME to get proper ordering
251 : for multiple multi-column fks to the same multi-column pk from one table */
252 5 : pos += snprintf(query + pos, querylen - pos,
253 : " order by \"%sTABLE_SCHEM\", \"%sTABLE_NAME\", \"%s_NAME\", \"KEY_SEQ\"",
254 : PKTableName != NULL ? "FK" : "PK",
255 : PKTableName != NULL ? "FK" : "PK",
256 : PKTableName != NULL ? "FK" : "PK");
257 5 : assert(pos < querylen);
258 :
259 : /* debug: fprintf(stdout, "SQLForeignKeys query (pos: %zu, len: %zu):\n%s\n\n", pos, strlen(query), query); */
260 :
261 : /* query the MonetDB data dictionary tables */
262 5 : rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos);
263 :
264 5 : free(query);
265 :
266 5 : return rc;
267 :
268 0 : nomem:
269 0 : if (psch)
270 0 : free(psch);
271 0 : if (ptab)
272 0 : free(ptab);
273 0 : if (fsch)
274 0 : free(fsch);
275 0 : if (ftab)
276 0 : free(ftab);
277 0 : if (query)
278 : free(query);
279 : /* Memory allocation error */
280 0 : addStmtError(stmt, "HY001", NULL, 0);
281 0 : return SQL_ERROR;
282 : }
283 :
284 : SQLRETURN SQL_API
285 : SQLForeignKeys(SQLHSTMT StatementHandle,
286 : SQLCHAR *PKCatalogName,
287 : SQLSMALLINT NameLength1,
288 : SQLCHAR *PKSchemaName,
289 : SQLSMALLINT NameLength2,
290 : SQLCHAR *PKTableName,
291 : SQLSMALLINT NameLength3,
292 : SQLCHAR *FKCatalogName,
293 : SQLSMALLINT NameLength4,
294 : SQLCHAR *FKSchemaName,
295 : SQLSMALLINT NameLength5,
296 : SQLCHAR *FKTableName,
297 : SQLSMALLINT NameLength6)
298 : {
299 5 : ODBCStmt *stmt = (ODBCStmt *) StatementHandle;
300 :
301 : #ifdef ODBCDEBUG
302 5 : ODBCLOG("SQLForeignKeys %p ", StatementHandle);
303 : #endif
304 :
305 5 : if (!isValidStmt(stmt))
306 : return SQL_INVALID_HANDLE;
307 :
308 5 : clearStmtErrors(stmt);
309 :
310 5 : return MNDBForeignKeys(stmt, PKCatalogName, NameLength1,
311 : PKSchemaName, NameLength2,
312 : PKTableName, NameLength3,
313 : FKCatalogName, NameLength4,
314 : FKSchemaName, NameLength5,
315 : FKTableName, NameLength6);
316 : }
317 :
318 : SQLRETURN SQL_API
319 : SQLForeignKeysA(SQLHSTMT StatementHandle,
320 : SQLCHAR *PKCatalogName,
321 : SQLSMALLINT NameLength1,
322 : SQLCHAR *PKSchemaName,
323 : SQLSMALLINT NameLength2,
324 : SQLCHAR *PKTableName,
325 : SQLSMALLINT NameLength3,
326 : SQLCHAR *FKCatalogName,
327 : SQLSMALLINT NameLength4,
328 : SQLCHAR *FKSchemaName,
329 : SQLSMALLINT NameLength5,
330 : SQLCHAR *FKTableName,
331 : SQLSMALLINT NameLength6)
332 : {
333 0 : return SQLForeignKeys(StatementHandle, PKCatalogName, NameLength1,
334 : PKSchemaName, NameLength2,
335 : PKTableName, NameLength3,
336 : FKCatalogName, NameLength4,
337 : FKSchemaName, NameLength5,
338 : FKTableName, NameLength6);
339 : }
340 :
341 : SQLRETURN SQL_API
342 : SQLForeignKeysW(SQLHSTMT StatementHandle,
343 : SQLWCHAR *PKCatalogName,
344 : SQLSMALLINT NameLength1,
345 : SQLWCHAR *PKSchemaName,
346 : SQLSMALLINT NameLength2,
347 : SQLWCHAR *PKTableName,
348 : SQLSMALLINT NameLength3,
349 : SQLWCHAR *FKCatalogName,
350 : SQLSMALLINT NameLength4,
351 : SQLWCHAR *FKSchemaName,
352 : SQLSMALLINT NameLength5,
353 : SQLWCHAR *FKTableName,
354 : SQLSMALLINT NameLength6)
355 : {
356 0 : ODBCStmt *stmt = (ODBCStmt *) StatementHandle;
357 0 : SQLCHAR *PKcatalog = NULL, *PKschema = NULL, *PKtable = NULL;
358 0 : SQLCHAR *FKcatalog = NULL, *FKschema = NULL, *FKtable = NULL;
359 0 : SQLRETURN rc = SQL_ERROR;
360 :
361 : #ifdef ODBCDEBUG
362 0 : ODBCLOG("SQLForeignKeysW %p ", StatementHandle);
363 : #endif
364 :
365 0 : if (!isValidStmt(stmt))
366 : return SQL_INVALID_HANDLE;
367 :
368 0 : clearStmtErrors(stmt);
369 :
370 0 : fixWcharIn(PKCatalogName, NameLength1, SQLCHAR,
371 : PKcatalog, addStmtError, stmt, goto bailout);
372 0 : fixWcharIn(PKSchemaName, NameLength2, SQLCHAR,
373 : PKschema, addStmtError, stmt, goto bailout);
374 0 : fixWcharIn(PKTableName, NameLength3, SQLCHAR,
375 : PKtable, addStmtError, stmt, goto bailout);
376 0 : fixWcharIn(FKCatalogName, NameLength4, SQLCHAR,
377 : FKcatalog, addStmtError, stmt, goto bailout);
378 0 : fixWcharIn(FKSchemaName, NameLength5, SQLCHAR,
379 : FKschema, addStmtError, stmt, goto bailout);
380 0 : fixWcharIn(FKTableName, NameLength6, SQLCHAR,
381 : FKtable, addStmtError, stmt, goto bailout);
382 :
383 0 : rc = MNDBForeignKeys(stmt, PKcatalog, SQL_NTS, PKschema, SQL_NTS,
384 : PKtable, SQL_NTS, FKcatalog, SQL_NTS,
385 : FKschema, SQL_NTS, FKtable, SQL_NTS);
386 :
387 0 : bailout:
388 0 : if (PKcatalog)
389 0 : free(PKcatalog);
390 0 : if (PKschema)
391 0 : free(PKschema);
392 0 : if (PKtable)
393 0 : free(PKtable);
394 0 : if (FKcatalog)
395 0 : free(FKcatalog);
396 0 : if (FKschema)
397 0 : free(FKschema);
398 0 : if (FKtable)
399 0 : free(FKtable);
400 :
401 : return rc;
402 : }
|
