2
Licensed Materials - Property of IBM
3
DB2 Storage Engine Enablement
4
Copyright IBM Corporation 2007,2008
7
Redistribution and use in source and binary forms, with or without modification,
8
are permitted provided that the following conditions are met:
9
(a) Redistributions of source code must retain this list of conditions, the
10
copyright notice in section {d} below, and the disclaimer following this
12
(b) Redistributions in binary form must reproduce this list of conditions, the
13
copyright notice in section (d) below, and the disclaimer following this
14
list of conditions, in the documentation and/or other materials provided
15
with the distribution.
16
(c) The name of IBM may not be used to endorse or promote products derived from
17
this software without specific prior written permission.
18
(d) The text of the required copyright notice is:
19
Licensed Materials - Property of IBM
20
DB2 Storage Engine Enablement
21
Copyright IBM Corporation 2007,2008
24
THIS SOFTWARE IS PROVIDED BY IBM CORPORATION "AS IS" AND ANY EXPRESS OR IMPLIED
25
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
26
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT
27
SHALL IBM CORPORATION BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
28
EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
29
OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
30
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
31
CONTRACT, STRICT LIABILITY, OR TORT INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
32
IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY
38
#include "ha_ibmdb2i.h"
39
#include "db2i_safeString.h"
41
// This function is called when building the CREATE TABLE information for
42
// foreign key constraints. It converts a constraint, table, schema, or
43
// field name from EBCDIC to ASCII. If the DB2 name is quoted, it removes
44
// those quotes. It then adds the appropriate quotes for a MySQL identifier.
46
static void convNameForCreateInfo(THD *thd, SafeString& info, char* fromName, int len)
49
char cquote; // Quote character
50
char convName[MAX_DB2_FILENAME_LENGTH]; // Converted name
52
memset(convName, 0, sizeof(convName));
53
convFromEbcdic(fromName, convName, len);
54
quote = get_quote_char_for_identifier(thd, convName, len);
55
cquote = (char) quote;
58
if (convName[0] == '"') // If DB2 name was quoted, remove quotes
60
if (strstr(convName, "\"\""))
61
stripExtraQuotes(convName+1, len-1);
62
info.strncat((char*)(convName+1), len-2);
64
else // DB2 name was not quoted
65
info.strncat(convName, len);
71
Evaluate the parse tree to build foreign key constraint clauses
73
@parm lex The parse tree
74
@parm appendHere The DB2 string to receive the constraint clauses
75
@parm path The path to the table under consideration
76
@parm fields Pointer to the table's list of field pointers
77
@parm[in, out] fileSortSequenceType The sort sequence type associated with the table
78
@parm[in, out] fileSortSequence The sort sequence associated with the table
79
@parm[in, out] fileSortSequenceLibrary The sort sequence library associated with the table
81
@return 0 if successful; HA_ERR_CANNOT_ADD_FOREIGN otherwise
83
int ha_ibmdb2i::buildDB2ConstraintString(LEX* lex,
87
char* fileSortSequenceType,
88
char* fileSortSequence,
89
char* fileSortSequenceLibrary)
91
List_iterator<Key> keyIter(lex->alter_info.key_list);
92
char colName[MAX_DB2_COLNAME_LENGTH+1];
96
while (curKey = keyIter++)
98
if (curKey->type == Key::FOREIGN_KEY)
100
appendHere.append(STRING_WITH_LEN(", "));
102
Foreign_key* fk = (Foreign_key*)curKey;
104
char db2LibName[MAX_DB2_SCHEMANAME_LENGTH+1];
107
char db2FKName[MAX_DB2_FILENAME_LENGTH+1];
108
appendHere.append(STRING_WITH_LEN("CONSTRAINT "));
109
if (fk->ref_table->db.str)
111
convertMySQLNameToDB2Name(fk->ref_table->db.str, db2LibName, sizeof(db2LibName));
115
db2i_table::getDB2LibNameFromPath(path, db2LibName);
117
if (lower_case_table_names == 1)
118
my_casedn_str(files_charset_info, db2LibName);
119
appendHere.append(db2LibName);
121
appendHere.append('.');
123
convertMySQLNameToDB2Name(fk->name, db2FKName, sizeof(db2FKName));
124
appendHere.append(db2FKName);
127
appendHere.append(STRING_WITH_LEN(" FOREIGN KEY ("));
129
bool firstTime = true;
131
List_iterator<Key_part_spec> column(fk->columns);
132
Key_part_spec* curColumn;
134
while (curColumn = column++)
138
appendHere.append(',');
142
convertMySQLNameToDB2Name(curColumn->field_name, colName, sizeof(colName));
143
appendHere.append(colName);
145
// DB2 requires that the sort sequence on the child table match the parent table's
146
// sort sequence. We ensure that happens by updating the sort sequence according
147
// to the constrained fields.
148
Field** field = fields;
151
if (strcmp((*field)->field_name, curColumn->field_name) == 0)
153
int rc = updateAssociatedSortSequence((*field)->charset(),
154
fileSortSequenceType,
156
fileSortSequenceLibrary);
158
if (unlikely(rc)) return rc;
160
} while (*(++field));
165
appendHere.append(STRING_WITH_LEN(") REFERENCES "));
167
if (fk->ref_table->db.str)
169
convertMySQLNameToDB2Name(fk->ref_table->db.str, db2LibName, sizeof(db2LibName));
173
db2i_table::getDB2LibNameFromPath(path, db2LibName);
175
if (lower_case_table_names == 1)
176
my_casedn_str(files_charset_info, db2LibName);
177
appendHere.append(db2LibName);
178
appendHere.append('.');
180
char db2FileName[MAX_DB2_FILENAME_LENGTH+1];
181
convertMySQLNameToDB2Name(fk->ref_table->table.str, db2FileName, sizeof(db2FileName));
182
if (lower_case_table_names)
183
my_casedn_str(files_charset_info, db2FileName);
184
appendHere.append(db2FileName);
187
if (!fk->ref_columns.is_empty())
189
List_iterator<Key_part_spec> ref(fk->ref_columns);
190
Key_part_spec* curRef;
191
appendHere.append(STRING_WITH_LEN(" ("));
194
while (curRef = ref++)
198
appendHere.append(',');
202
convertMySQLNameToDB2Name(curRef->field_name, colName, sizeof(colName));
203
appendHere.append(colName);
206
appendHere.append(STRING_WITH_LEN(") "));
209
if (fk->delete_opt != Foreign_key::FK_OPTION_UNDEF)
211
appendHere.append(STRING_WITH_LEN("ON DELETE "));
212
switch (fk->delete_opt)
214
case Foreign_key::FK_OPTION_RESTRICT:
215
appendHere.append(STRING_WITH_LEN("RESTRICT ")); break;
216
case Foreign_key::FK_OPTION_CASCADE:
217
appendHere.append(STRING_WITH_LEN("CASCADE ")); break;
218
case Foreign_key::FK_OPTION_SET_NULL:
219
appendHere.append(STRING_WITH_LEN("SET NULL ")); break;
220
case Foreign_key::FK_OPTION_NO_ACTION:
221
appendHere.append(STRING_WITH_LEN("NO ACTION ")); break;
222
case Foreign_key::FK_OPTION_DEFAULT:
223
appendHere.append(STRING_WITH_LEN("SET DEFAULT ")); break;
225
return HA_ERR_CANNOT_ADD_FOREIGN; break;
229
if (fk->update_opt != Foreign_key::FK_OPTION_UNDEF)
231
appendHere.append(STRING_WITH_LEN("ON UPDATE "));
232
switch (fk->update_opt)
234
case Foreign_key::FK_OPTION_RESTRICT:
235
appendHere.append(STRING_WITH_LEN("RESTRICT ")); break;
236
case Foreign_key::FK_OPTION_NO_ACTION:
237
appendHere.append(STRING_WITH_LEN("NO ACTION ")); break;
239
return HA_ERR_CANNOT_ADD_FOREIGN; break;
251
/***********************************************************************
252
Get the foreign key information in the form of a character string so
253
that it can be inserted into a CREATE TABLE statement. This is used by
254
the SHOW CREATE TABLE statement. The string will later be freed by the
255
free_foreign_key_create_info() method.
256
************************************************************************/
258
char* ha_ibmdb2i::get_foreign_key_create_info(void)
260
DBUG_ENTER("ha_ibmdb2i::get_foreign_key_create_info");
262
char* infoBuffer = NULL; // Pointer to string returned to MySQL
263
uint32 constraintSpaceLength;// Length of space passed to DB2
264
ValidatedPointer<char> constraintSpace; // Space pointer passed to DB2
265
uint32 neededLen; // Length returned from DB2
266
uint32 cstCnt; // Number of foreign key constraints from DB2
268
constraint_hdr* cstHdr; // Pointer to constraint header structure
269
FK_constraint* FKCstDef; // Pointer to constraint definition structure
270
cst_name* fieldName; // Pointer to field name structure
271
char* tempPtr; // Temp pointer for traversing constraint space
274
/* Allocate space to retrieve the DB2 constraint information. */
276
if (!(share = get_share(table_share->path.str, table)))
279
constraintSpaceLength = 5000; // Try allocating 5000 bytes and see if enough.
283
constraintSpace.alloc(constraintSpaceLength);
284
rc = bridge()->expectErrors(QMY_ERR_NEED_MORE_SPACE)
285
->constraints(db2Table->dataFile()->getMasterDefnHandle(),
287
constraintSpaceLength,
291
if (unlikely(rc == QMY_ERR_NEED_MORE_SPACE))
293
constraintSpaceLength = neededLen; // Get length of space that's needed
294
constraintSpace.realloc(constraintSpaceLength);
295
rc = bridge()->expectErrors(QMY_ERR_NEED_MORE_SPACE)
296
->constraints(db2Table->dataFile()->getMasterDefnHandle(),
298
constraintSpaceLength,
303
/* If constraint information was returned by DB2, build a text string */
304
/* to return to MySQL. */
306
if ((rc == 0) && (cstCnt > 0))
309
infoBuffer = (char*) my_malloc(MAX_FOREIGN_LEN + 1, MYF(MY_WME));
310
if (infoBuffer == NULL)
316
SafeString info(infoBuffer, MAX_FOREIGN_LEN + 1);
318
/* Loop through the DB2 constraints and build a text string for each foreign */
319
/* key constraint that is found. */
321
tempPtr = constraintSpace;
322
cstHdr = (constraint_hdr_t*)(void*)constraintSpace; // Address first constraint definition
323
for (int i = 0; i < cstCnt && !info.overflowed(); ++i)
325
if (cstHdr->CstType[0] == QMY_CST_FK) // If this is a foreign key constraint
327
tempPtr = (char*)(tempPtr + cstHdr->CstDefOff);
328
FKCstDef = (FK_constraint_t*)tempPtr;
330
/* Process the constraint name. */
332
info.strncat(STRING_WITH_LEN(",\n CONSTRAINT "));
333
convNameForCreateInfo(thd, info,
334
FKCstDef->CstName.Name, FKCstDef->CstName.Len);
336
/* Process the names of the foreign keys. */
338
info.strncat(STRING_WITH_LEN(" FOREIGN KEY ("));
339
tempPtr = (char*)(tempPtr + FKCstDef->KeyColOff);
340
fieldName= (cst_name_t*)tempPtr;
341
for (fld = 0; fld < FKCstDef->KeyCnt; ++fld)
343
convNameForCreateInfo(thd, info, fieldName->Name, fieldName->Len);
344
if ((fld + 1) < FKCstDef->KeyCnt)
346
info.strncat(STRING_WITH_LEN(", "));
347
fieldName = fieldName + 1;
351
/* Process the schema-name and name of the referenced table. */
353
info.strncat(STRING_WITH_LEN(") REFERENCES "));
354
convNameForCreateInfo(thd, info,
355
FKCstDef->RefSchema.Name, FKCstDef->RefSchema.Len);
357
convNameForCreateInfo(thd, info,
358
FKCstDef->RefTable.Name, FKCstDef->RefTable.Len);
359
info.strncat(STRING_WITH_LEN(" ("));
361
/* Process the names of the referenced keys. */
363
tempPtr = (char*)FKCstDef;
364
tempPtr = (char*)(tempPtr + FKCstDef->RefColOff);
365
fieldName= (cst_name_t*)tempPtr;
366
for (fld = 0; fld < FKCstDef->RefCnt; ++fld)
368
convNameForCreateInfo(thd, info, fieldName->Name, fieldName->Len);
369
if ((fld + 1) < FKCstDef->RefCnt)
371
info.strncat(STRING_WITH_LEN(", "));
372
fieldName = fieldName + 1;
376
/* Process the ON UPDATE and ON DELETE rules. */
378
info.strncat(STRING_WITH_LEN(") ON UPDATE "));
379
switch(FKCstDef->UpdMethod)
381
case QMY_NOACTION: info.strncat(STRING_WITH_LEN("NO ACTION")); break;
382
case QMY_RESTRICT: info.strncat(STRING_WITH_LEN("RESTRICT")); break;
385
info.strncat(STRING_WITH_LEN(" ON DELETE "));
386
switch(FKCstDef->DltMethod)
388
case QMY_CASCADE: info.strncat(STRING_WITH_LEN("CASCADE")); break;
389
case QMY_SETDFT: info.strncat(STRING_WITH_LEN("SET DEFAULT")); break;
390
case QMY_SETNULL: info.strncat(STRING_WITH_LEN("SET NULL")); break;
391
case QMY_NOACTION: info.strncat(STRING_WITH_LEN("NO ACTION")); break;
392
case QMY_RESTRICT: info.strncat(STRING_WITH_LEN("RESTRICT")); break;
397
/* Address the next constraint, if any. */
401
tempPtr = (char*)cstHdr + cstHdr->CstLen;
402
cstHdr = (constraint_hdr_t*)(tempPtr);
407
/* Cleanup and return */
410
DBUG_RETURN(infoBuffer);
413
/***********************************************************************
414
Free the foreign key create info (for a table) that was acquired by the
415
get_foreign_key_create_info() method.
416
***********************************************************************/
418
void ha_ibmdb2i::free_foreign_key_create_info(char* info)
420
DBUG_ENTER("ha_ibmdb2i::free_foreign_key_create_info");
424
my_free(info, MYF(0));
429
/***********************************************************************
430
This method returns to MySQL a list, with one entry in the list describing
431
each foreign key constraint.
432
***********************************************************************/
434
int ha_ibmdb2i::get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list)
436
DBUG_ENTER("ha_ibmdb2i::get_foreign_key_list");
438
uint32 constraintSpaceLength; // Length of space passed to DB2
439
ValidatedPointer<char> constraintSpace; // Space pointer passed to DB2
440
uint16 rtnCode; // Return code from DB2
441
uint32 neededLen; // Bytes needed to contain DB2 constraint info
442
uint32 cstCnt; // Number of constraints returned by DB2
444
constraint_hdr* cstHdr; // Pointer to a cst header structure
445
FK_constraint* FKCstDef; // Pointer to definition of foreign key constraint
446
cst_name* fieldName; // Pointer to field name structure
449
char* tempPtr; // Temp pointer for traversing constraint space
452
if (!(share = get_share(table_share->path.str, table)))
455
// Allocate space to retrieve the DB2 constraint information.
456
constraintSpaceLength = 5000; // Try allocating 5000 bytes and see if enough.
458
constraintSpace.alloc(constraintSpaceLength);
459
rc = bridge()->expectErrors(QMY_ERR_NEED_MORE_SPACE)
460
->constraints(db2Table->dataFile()->getMasterDefnHandle(),
462
constraintSpaceLength,
466
if (unlikely(rc == QMY_ERR_NEED_MORE_SPACE))
468
constraintSpaceLength = neededLen; // Get length of space that's needed
469
constraintSpace.realloc(constraintSpaceLength);
470
rc = bridge()->expectErrors(QMY_ERR_NEED_MORE_SPACE)
471
->constraints(db2Table->dataFile()->getMasterDefnHandle(),
473
constraintSpaceLength,
478
/* If constraint information was returned by DB2, build a text string */
479
/* to return to MySQL. */
480
if ((rc == 0) && (cstCnt > 0))
482
tempPtr = constraintSpace;
483
cstHdr = (constraint_hdr_t*)(void*)constraintSpace; // Address first constraint definition
484
for (int i = 0; i < cstCnt; ++i)
486
if (cstHdr->CstType[0] == QMY_CST_FK) // If this is a foreign key constraint
488
FOREIGN_KEY_INFO f_key_info;
490
tempPtr = (char*)(tempPtr + cstHdr->CstDefOff);
491
FKCstDef = (FK_constraint_t*)tempPtr;
493
/* Process the constraint name. */
495
convFromEbcdic(FKCstDef->CstName.Name, convName,FKCstDef->CstName.Len);
496
if (convName[0] == '"') // If quoted, exclude quotes.
497
f_key_info.forein_id = thd_make_lex_string(thd, 0,
498
convName + 1, (uint) (FKCstDef->CstName.Len - 2), 1);
500
f_key_info.forein_id = thd_make_lex_string(thd, 0,
501
convName, (uint) FKCstDef->CstName.Len, 1);
503
/* Process the names of the foreign keys. */
506
tempPtr = (char*)(tempPtr + FKCstDef->KeyColOff);
507
fieldName = (cst_name_t*)tempPtr;
508
for (fld = 0; fld < FKCstDef->KeyCnt; ++fld)
510
convFromEbcdic(fieldName->Name, convName, fieldName->Len);
511
if (convName[0] == '"') // If quoted, exclude quotes.
512
name = thd_make_lex_string(thd, name,
513
convName + 1, (uint) (fieldName->Len - 2), 1);
515
name = thd_make_lex_string(thd, name, convName, (uint) fieldName->Len, 1);
516
f_key_info.foreign_fields.push_back(name);
517
if ((fld + 1) < FKCstDef->KeyCnt)
518
fieldName = fieldName + 1;
521
/* Process the schema and name of the referenced table. */
523
convFromEbcdic(FKCstDef->RefSchema.Name, convName, FKCstDef->RefSchema.Len);
524
if (convName[0] == '"') // If quoted, exclude quotes.
525
f_key_info.referenced_db = thd_make_lex_string(thd, 0,
526
convName + 1, (uint) (FKCstDef->RefSchema.Len -2), 1);
528
f_key_info.referenced_db = thd_make_lex_string(thd, 0,
529
convName, (uint) FKCstDef->RefSchema.Len, 1);
530
convFromEbcdic(FKCstDef->RefTable.Name, convName, FKCstDef->RefTable.Len);
531
if (convName[0] == '"') // If quoted, exclude quotes.
532
f_key_info.referenced_table = thd_make_lex_string(thd, 0,
533
convName +1, (uint) (FKCstDef->RefTable.Len -2), 1);
535
f_key_info.referenced_table = thd_make_lex_string(thd, 0,
536
convName, (uint) FKCstDef->RefTable.Len, 1);
538
/* Process the names of the referenced keys. */
540
tempPtr = (char*)FKCstDef;
541
tempPtr = (char*)(tempPtr + FKCstDef->RefColOff);
542
fieldName= (cst_name_t*)tempPtr;
543
for (fld = 0; fld < FKCstDef->RefCnt; ++fld)
545
convFromEbcdic(fieldName->Name, convName, fieldName->Len);
546
if (convName[0] == '"') // If quoted, exclude quotes.
547
name = thd_make_lex_string(thd, name,
548
convName + 1, (uint) (fieldName->Len -2), 1);
550
name = thd_make_lex_string(thd, name, convName, (uint) fieldName->Len, 1);
551
f_key_info.referenced_fields.push_back(name);
552
if ((fld + 1) < FKCstDef->RefCnt)
553
fieldName = fieldName + 1;
556
/* Process the ON UPDATE and ON DELETE rules. */
558
switch(FKCstDef->UpdMethod)
562
method = "NO ACTION";
574
f_key_info.update_method = thd_make_lex_string(
575
thd, f_key_info.update_method, method, methodLen, 1);
576
switch(FKCstDef->DltMethod)
586
method = "SET DEFAULT";
598
method = "NO ACTION";
610
f_key_info.delete_method = thd_make_lex_string(
611
thd, f_key_info.delete_method, method, methodLen, 1);
612
f_key_info.referenced_key_name= thd_make_lex_string(thd, 0, (char *)"", 1, 1);
613
FOREIGN_KEY_INFO *pf_key_info = (FOREIGN_KEY_INFO *)
614
thd_memdup(thd, &f_key_info, sizeof(FOREIGN_KEY_INFO));
615
f_key_list->push_back(pf_key_info);
618
/* Address the next constraint, if any. */
622
tempPtr = (char*)cstHdr + cstHdr->CstLen;
623
cstHdr = (constraint_hdr_t*)(tempPtr);
628
/* Cleanup and return. */
634
/***********************************************************************
635
Checks if the table is referenced by a foreign key.
636
Returns: 0 if not referenced (or error occurs),
638
***********************************************************************/
640
uint ha_ibmdb2i::referenced_by_foreign_key(void)
642
DBUG_ENTER("ha_ibmdb2i::referenced_by_foreign_key");
645
FILE_HANDLE queryFile = 0;
649
const char* libName = db2Table->getDB2LibName(db2i_table::ASCII_SQL);
650
const char* fileName = db2Table->getDB2TableName(db2i_table::ASCII_SQL);
653
query.append(STRING_WITH_LEN(" SELECT COUNT(*) FROM SYSIBM.SQLFOREIGNKEYS WHERE PKTABLE_SCHEM = '"));
654
query.append(libName+1, strlen(libName)-2); // parent library name
655
query.append(STRING_WITH_LEN("' AND PKTABLE_NAME = '"));
656
query.append(fileName+1, strlen(fileName)-2); // parent file name
657
query.append(STRING_WITH_LEN("'"));
659
SqlStatementStream sqlStream(query);
661
rc = bridge()->prepOpen(sqlStream.getPtrToData(),
666
IOReadBuffer rowBuffer(1, resultRowLen);
667
rc = bridge()->read(queryFile, rowBuffer.ptr(), QMY_READ_ONLY, QMY_NONE, QMY_FIRST);
668
if (!rc) count = *((uint32*)rowBuffer.getRowN(0));
669
bridge()->deallocateFile(queryFile);