00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { MySQL Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { and Sergey Merkuriev }
00008 { }
00009 {*********************************************************}
00010
00011 {@********************************************************}
00012 { Copyright (c) 1999-2006 Zeos Development Group }
00013 { }
00014 { License Agreement: }
00015 { }
00016 { This library is distributed in the hope that it will be }
00017 { useful, but WITHOUT ANY WARRANTY; without even the }
00018 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00019 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00020 { Public License for more details. }
00021 { }
00022 { The source code of the ZEOS Libraries and packages are }
00023 { distributed under the Library GNU General Public }
00024 { License (see the file COPYING / COPYING.ZEOS) }
00025 { with the following modification: }
00026 { As a special exception, the copyright holders of this }
00027 { library give you permission to link this library with }
00028 { independent modules to produce an executable, }
00029 { regardless of the license terms of these independent }
00030 { modules, and to copy and distribute the resulting }
00031 { executable under terms of your choice, provided that }
00032 { you also meet, for each linked independent module, }
00033 { the terms and conditions of the license of that module. }
00034 { An independent module is a module which is not derived }
00035 { from or based on this library. If you modify this }
00036 { library, you may extend this exception to your version }
00037 { of the library, but you are not obligated to do so. }
00038 { If you do not wish to do so, delete this exception }
00039 { statement from your version. }
00040 { }
00041 { }
00042 { The project web site is located on: }
00043 { http:
00044 { http:
00045 { svn:
00046 { }
00047 { http:
00048 { http:
00049 { }
00050 { }
00051 { }
00052 { Zeos Development Group. }
00053 {********************************************************@}
00054
00055 unit ZDbcMySqlMetadata;
00056
00057 interface
00058
00059 {$I ZDbc.inc}
00060
00061 uses
00062 {$IFNDEF VER130BELOW}
00063 Types,
00064 {$ENDIF}
00065 Classes, SysUtils, ZClasses, ZSysUtils, ZDbcIntfs, ZDbcMetadata,
00066 ZDbcResultSet, ZDbcCachedResultSet, ZDbcResultSetMetadata,
00067 ZCompatibility, ZDbcConnection;
00068
00069 type
00070
00071 {** Implements MySQL Database Metadata. }
00072 TZMySQLDatabaseMetadata = class(TZAbstractDatabaseMetadata)
00073 private
00074 FDatabase: string;
00075 protected
00076 procedure GetCatalogAndNamePattern(const Catalog, SchemaPattern,
00077 NamePattern: string; out OutCatalog, OutNamePattern: string);
00078 procedure GetVersion(var MajorVersion, MinorVersion: integer);
00079 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00080 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00081
00082 function UncachedGetCatalogs: IZResultSet; override;
00083 function UncachedGetTableTypes: IZResultSet; override;
00084 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00085 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00086 function UncachedGetTablePrivileges(const Catalog: string; const SchemaPattern: string;
00087 const TableNamePattern: string): IZResultSet; override;
00088 function UncachedGetColumnPrivileges(const Catalog: string; const Schema: string;
00089 const Table: string; const ColumnNamePattern: string): IZResultSet; override;
00090 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00091 const Table: string): IZResultSet; override;
00092 function UncachedGetImportedKeys(const Catalog: string; const Schema: string;
00093 const Table: string): IZResultSet; override;
00094 function UncachedGetExportedKeys(const Catalog: string; const Schema: string;
00095 const Table: string): IZResultSet; override;
00096 function UncachedGetCrossReference(const PrimaryCatalog: string; const PrimarySchema: string;
00097 const PrimaryTable: string; const ForeignCatalog: string; const ForeignSchema: string;
00098 const ForeignTable: string): IZResultSet; override;
00099 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00100 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00101
00102
00103
00104
00105
00106
00107
00108 function UncachedGetVersionColumns(const Catalog: string; const Schema: string;
00109 const Table: string): IZResultSet; override;
00110 function UncachedGetTypeInfo: IZResultSet; override;
00111
00112
00113
00114 public
00115 constructor Create(Connection: TZAbstractConnection; Url: string; Info: TStrings);
00116 destructor Destroy; override;
00117
00118 function GetDatabaseProductName: string; override;
00119 function GetDatabaseProductVersion: string; override;
00120 function GetDriverName: string; override;
00121 function GetDriverMajorVersion: Integer; override;
00122 function GetDriverMinorVersion: Integer; override;
00123 function UsesLocalFilePerTable: Boolean; override;
00124 function StoresMixedCaseIdentifiers: Boolean; override;
00125 function GetIdentifierQuoteString: string; override;
00126 function GetSQLKeywords: string; override;
00127 function GetNumericFunctions: string; override;
00128 function GetStringFunctions: string; override;
00129 function GetSystemFunctions: string; override;
00130 function GetTimeDateFunctions: string; override;
00131 function GetSearchStringEscape: string; override;
00132 function GetExtraNameCharacters: string; override;
00133
00134 function SupportsOrderByUnrelated: Boolean; override;
00135 function SupportsGroupByUnrelated: Boolean; override;
00136 function SupportsGroupByBeyondSelect: Boolean; override;
00137 function SupportsIntegrityEnhancementFacility: Boolean; override;
00138 function GetSchemaTerm: string; override;
00139 function GetProcedureTerm: string; override;
00140 function GetCatalogTerm: string; override;
00141 function SupportsCatalogsInDataManipulation: Boolean; override;
00142 function SupportsCatalogsInTableDefinitions: Boolean; override;
00143 function SupportsSubqueriesInComparisons: Boolean; override;
00144 function SupportsUnionAll: Boolean; override;
00145 function SupportsOpenStatementsAcrossCommit: Boolean; override;
00146 function SupportsOpenStatementsAcrossRollback: Boolean; override;
00147
00148 function GetMaxBinaryLiteralLength: Integer; override;
00149 function GetMaxCharLiteralLength: Integer; override;
00150 function GetMaxColumnNameLength: Integer; override;
00151 function GetMaxColumnsInGroupBy: Integer; override;
00152 function GetMaxColumnsInIndex: Integer; override;
00153 function GetMaxColumnsInOrderBy: Integer; override;
00154 function GetMaxColumnsInSelect: Integer; override;
00155 function GetMaxColumnsInTable: Integer; override;
00156 function GetMaxConnections: Integer; override;
00157 function GetMaxCursorNameLength: Integer; override;
00158 function GetMaxIndexLength: Integer; override;
00159 function GetMaxCatalogNameLength: Integer; override;
00160 function GetMaxRowSize: Integer; override;
00161 function DoesMaxRowSizeIncludeBlobs: Boolean; override;
00162 function GetMaxStatementLength: Integer; override;
00163 function GetMaxStatements: Integer; override;
00164 function GetMaxTableNameLength: Integer; override;
00165 function GetMaxTablesInSelect: Integer; override;
00166 function GetMaxUserNameLength: Integer; override;
00167
00168 function GetDefaultTransactionIsolation: TZTransactIsolationLevel; override;
00169 function SupportsDataDefinitionAndDataManipulationTransactions: Boolean; override;
00170 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00171 end;
00172
00173 implementation
00174
00175 uses
00176 Math, ZMessages, ZDbcUtils, ZCollections, ZDbcMySqlUtils;
00177
00178 { TZMySQLDatabaseMetadata }
00179
00180 {**
00181 Constructs this object and assignes the main properties.
00182 @param Connection a database connection object.
00183 @param Url a database connection url string.
00184 @param Info an extra connection properties.
00185 }
00186 constructor TZMySQLDatabaseMetadata.Create(Connection: TZAbstractConnection;
00187 Url: string; Info: TStrings);
00188 var
00189 TempInfo: TStrings;
00190 Hostname, UserName, Password: string;
00191 Port: Integer;
00192 begin
00193 inherited Create(Connection, Url, Info);
00194
00195 TempInfo := TStringList.Create;
00196 try
00197 ResolveDatabaseUrl(Url, Info, HostName, Port, FDatabase,
00198 UserName, Password, TempInfo);
00199 finally
00200 TempInfo.Free;
00201 end;
00202 end;
00203
00204 {**
00205 Destroys this object and cleanups the memory.
00206 }
00207 destructor TZMySQLDatabaseMetadata.Destroy;
00208 begin
00209 inherited Destroy;
00210 end;
00211
00212
00213
00214
00215 {**
00216 What's the name of this database product?
00217 @return database product name
00218 }
00219 function TZMySQLDatabaseMetadata.GetDatabaseProductName: string;
00220 begin
00221 Result := 'MySQL';
00222 end;
00223
00224 {**
00225 What's the version of this database product?
00226 @return database version
00227 }
00228 function TZMySQLDatabaseMetadata.GetDatabaseProductVersion: string;
00229 begin
00230 Result := '3+';
00231 end;
00232
00233 {**
00234 What's the name of this JDBC driver?
00235 @return JDBC driver name
00236 }
00237 function TZMySQLDatabaseMetadata.GetDriverName: string;
00238 begin
00239 Result := 'Zeos Database Connectivity Driver for MySQL';
00240 end;
00241
00242 {**
00243 What's this JDBC driver's major version number?
00244 @return JDBC driver major version
00245 }
00246 function TZMySQLDatabaseMetadata.GetDriverMajorVersion: Integer;
00247 begin
00248 Result := 1;
00249 end;
00250
00251 {**
00252 What's this JDBC driver's minor version number?
00253 @return JDBC driver minor version number
00254 }
00255 function TZMySQLDatabaseMetadata.GetDriverMinorVersion: Integer;
00256 begin
00257 Result := 1;
00258 end;
00259
00260 {**
00261 Does the database use a file for each table?
00262 @return true if the database uses a local file for each table
00263 }
00264 function TZMySQLDatabaseMetadata.UsesLocalFilePerTable: Boolean;
00265 begin
00266 Result := True;
00267 end;
00268
00269 {**
00270 Does the database treat mixed case unquoted SQL identifiers as
00271 case insensitive and store them in mixed case?
00272 @return <code>true</code> if so; <code>false</code> otherwise
00273 }
00274 function TZMySQLDatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00275 begin
00276 Result := True;
00277 end;
00278
00279 {**
00280 What's the string used to quote SQL identifiers?
00281 This returns a space " " if identifier quoting isn't supported.
00282 A JDBC Compliant<sup><font size=-2>TM</font></sup>
00283 driver always uses a double quote character.
00284 @return the quoting string
00285 }
00286 function TZMySQLDatabaseMetadata.GetIdentifierQuoteString: string;
00287 begin
00288 Result := '`';
00289 end;
00290
00291 {**
00292 Gets a comma-separated list of all a database's SQL keywords
00293 that are NOT also SQL92 keywords.
00294 @return the list
00295 }
00296 function TZMySQLDatabaseMetadata.GetSQLKeywords: string;
00297 begin
00298 Result := 'AUTO_INCREMENT,BINARY,BLOB,ENUM,INFILE,LOAD,MEDIUMINT,OPTION,'
00299 + 'OUTFILE,REPLACE,SET,TEXT,UNSIGNED,ZEROFILL';
00300 { mdaems : added all reserved words indicated by mysql documentation (up to mysql 5.1)}
00301 Result := Result + 'ACCESSIBLE,ADD,ALL,ALTER,ANALYZE,AND,ASC,ASENSITIVE,'
00302 + ' BEFORE,BETWEEN,BIGINT,BOTH,CALL,CASCADE,CASE,CHANGE,CHARACTER,CHECK,'
00303 + 'COLLATE,COLUMN,CONDITION,CONSTRAINT,CONTINUE,CONVERT,CROSS,'
00304 + 'CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,'
00305 + 'DATABASE,DATABASES,DAY_HOUR,DAY_MICROSECOND,DAY_MINUTE,DAY_SECOND,'
00306 + 'DEC,DECIMAL,DECLARE,DEFAULT,DELAYED,DESC,DESCRIBE,DETERMINISTIC,'
00307 + 'DISTINCT,DISTINCTROW,DIV,DOUBLE,DUAL,EACH,ELSE,ELSEIF,ENCLOSED,'
00308 + 'ESCAPED,EXISTS,EXIT,EXPLAIN,FALSE,FETCH,FLOAT,FLOAT4,FLOAT8,FOR,'
00309 + 'FORCE,FOREIGN,FULLTEXT,GRANT,HIGH_PRIORITY,HOUR_MICROSECOND,'
00310 + 'HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,IN,INNER,INOUT,INSENSITIVE,INT,'
00311 + 'INT1,INT2,INT3,INT4,INT8,INTERVAL,ITERATE,JOIN,KEYS,KILL,LEADING,'
00312 + 'LEAVE,LEFT,LIKE,LIMIT,LINEAR,LINES,LOCALTIME,LOCALTIMESTAMP,LOCK,'
00313 + 'LONG,LONGBLOB,LONGTEXT,LOOP,LOW_PRIORITY,MASTER_SSL_VERIFY_SERVER_CERT,'
00314 + 'MATCH,MEDIUMBLOB,MEDIUMTEXT,MIDDLEINT,MINUTE_MICROSECOND,MINUTE_SECOND,'
00315 + 'MOD,MODIFIES,NATURAL,NOT,NO_WRITE_TO_BINLOG,NUMERIC,OPTIMIZE,'
00316 + 'OPTIONALLY,OR,OUT,OUTER,PRECISION,PROCEDURE,PURGE,RANGE,READ,READS,'
00317 + 'READ_ONLY,READ_WRITE,REAL,REFERENCES,REGEXP,RELEASE,RENAME,REPEAT,'
00318 + 'REQUIRE,RESTRICT,RETURN,REVOKE,RIGHT,RLIKE,SCHEMA,SCHEMAS,'
00319 + 'SECOND_MICROSECOND,SENSITIVE,SEPARATOR,SHOW,SMALLINT,SPATIAL,'
00320 + 'SPECIFIC,SQL,SQLEXCEPTION,SQLSTATE,SQLWARNING,SQL_BIG_RESULT,'
00321 + 'SQL_CALC_FOUND_ROWS,SQL_SMALL_RESULT,SSL,STARTING,STRAIGHT_JOIN,'
00322 + 'TERMINATED,THEN,TINYBLOB,TINYINT,TINYTEXT,TO,TRAILING,TRIGGER,'
00323 + 'TRUE,UNDO,UNION,UNIQUE,UNLOCK,USAGE,USE,USING,UTC_DATE,UTC_TIME,'
00324 + 'UTC_TIMESTAMP,VARBINARY,VARCHARACTER,VARYING,WHEN,WHILE,WITH,'
00325 + 'WRITE,X509,XOR,YEAR_MONTH,ACCESSIBLE,LINEAR,'
00326 + 'MASTER_SSL_VERIFY_SERVER_CERT,RANGE,READ_ONLY,READ_WRITE';
00327 end;
00328
00329 {**
00330 Gets a comma-separated list of math functions. These are the
00331 X/Open CLI math function names used in the JDBC function escape
00332 clause.
00333 @return the list
00334 }
00335 function TZMySQLDatabaseMetadata.GetNumericFunctions: string;
00336 begin
00337 Result := 'ABS,ACOS,ASIN,ATAN,ATAN2,BIT_COUNT,CEILING,COS,COT,DEGREES,EXP,'
00338 + 'FLOOR,LOG,LOG10,MAX,MIN,MOD,PI,POW,POWER,RADIANS,RAND,ROUND,SIN,SQRT,'
00339 + 'TAN,TRUNCATE';
00340 { mdaems : added all numeric functions indicated by mysql documentation (up to mysql 5.1)}
00341 Result := Result + 'BIT_COUNT,CEIL,CRC32,LN,LOG2,SIGN,UUID';
00342 end;
00343
00344 {**
00345 Gets a comma-separated list of string functions. These are the
00346 X/Open CLI string function names used in the JDBC function escape
00347 clause.
00348 @return the list
00349 }
00350 function TZMySQLDatabaseMetadata.GetStringFunctions: string;
00351 begin
00352 Result := 'ASCII,CHAR,CHAR_LENGTH,CHARACTER_LENGTH,CONCAT,ELT,FIELD,'
00353 + 'FIND_IN_SET,INSERT,INSTR,INTERVAL,LCASE,LEFT,LENGTH,LOCATE,LOWER,LTRIM,'
00354 + 'MID,POSITION,OCTET_LENGTH,REPEAT,REPLACE,REVERSE,RIGHT,RTRIM,SPACE,'
00355 + 'SOUNDEX,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER';
00356 { mdaems : added all string functions indicated by mysql documentation (up to mysql 5.1)}
00357 Result := Result + 'AES_DECRYPT,AES_ENCRYPT,BIN,BIT_LENGTH,CHARSET,'
00358 + 'COERCIBILITY,COLLATION,COMPRESS,CONCAT_WS,DECODE,DES_DECRYPT,DES_ENCRYPT,'
00359 + 'ENCODE,ENCRYPT,EXPORT_SET,FORMAT,HEX,LOAD_FILE,LPAD,MAKE_SET,MD5,OCT,ORD,'
00360 + 'QUOTE,RPAD,STRCMP,SHA,SHA1,SUBSTR,UNHEX,EXTRACTVALUE,UPDATEXML,'
00361 + 'UNCOMPRESS,UNCOMPRESSED_LENGTH';
00362 end;
00363
00364 {**
00365 Gets a comma-separated list of system functions. These are the
00366 X/Open CLI system function names used in the JDBC function escape
00367 clause.
00368 @return the list
00369 }
00370 function TZMySQLDatabaseMetadata.GetSystemFunctions: string;
00371 begin
00372 Result := 'DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,'
00373 + 'LAST_INSERT_ID,VERSION';
00374 { mdaems : added all system functions indicated by mysql documentation (up to mysql 5.1)}
00375 Result := Result + 'BENCHMARK,CONNECTION_ID,CURRENT_USER,DEFAULT,FOUND_ROWS,'
00376 + 'GET_LOCK,INET_ATON,INET_NTOA,IS_FREE_LOCK,IS_USED_LOCK,MASTER_POS_WAIT,'
00377 + 'NAME_CONST,OLD_PASSWORD,RELEASE_LOCK,ROW_COUNT,SCHEMA,SLEEP';
00378 end;
00379
00380 {**
00381 Gets a comma-separated list of time and date functions.
00382 @return the list
00383 }
00384 function TZMySQLDatabaseMetadata.GetTimeDateFunctions: string;
00385 begin
00386 Result := 'DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,'
00387 + 'QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,'
00388 + 'FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,'
00389 + 'CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,'
00390 + 'SEC_TO_TIME,TIME_TO_SEC';
00391 { mdaems : added all time and date functions indicated by mysql documentation (up to mysql 5.1)}
00392 Result := Result + 'ADDDATE,ADDTIME,CONVERT_TZ,CURRENT_TIMESTAMP,DATE_ADD,'
00393 + 'DATE_SUB,DATE,DATEDIFF,DAYOFWEEK,GET_FORMAT,LAST_DAY,LOCALTIME,'
00394 + 'LOCALTIMESTAMP,MAKEDATE,MAKETIME,MICROSECOND,STR_TO_DATE,SUBDATE,SUBTIME,'
00395 + 'TIMEDIFF,TIMESTAMP,TIMESTAMPADD,TIMESTAMPDIFF,UTC_DATE,UTC_TIME,'
00396 + 'UTC_TIMESTAMP,WEEKOFYEAR,YEARWEEK';
00397 end;
00398
00399 {**
00400 Gets the string that can be used to escape wildcard characters.
00401 This is the string that can be used to escape '_' or '%' in
00402 the string pattern style catalog search parameters.
00403
00404 <P>The '_' character represents any single character.
00405 <P>The '%' character represents any sequence of zero or
00406 more characters.
00407
00408 @return the string used to escape wildcard characters
00409 }
00410 function TZMySQLDatabaseMetadata.GetSearchStringEscape: string;
00411 begin
00412 Result := '\';
00413 end;
00414
00415 {**
00416 Gets all the "extra" characters that can be used in unquoted
00417 identifier names (those beyond a-z, A-Z, 0-9 and _).
00418 @return the string containing the extra characters
00419 }
00420 function TZMySQLDatabaseMetadata.GetExtraNameCharacters: string;
00421 begin
00422 Result := '';
00423 end;
00424
00425
00426
00427
00428 {**
00429 Can an "ORDER BY" clause use columns not in the SELECT statement?
00430 @return <code>true</code> if so; <code>false</code> otherwise
00431 }
00432 function TZMySQLDatabaseMetadata.SupportsOrderByUnrelated: Boolean;
00433 var
00434 MajorVersion: Integer;
00435 MinorVersion: Integer;
00436 begin
00437 GetVersion(MajorVersion, MinorVersion);
00438
00439 Result := MajorVersion >= 5;
00440 end;
00441
00442 {**
00443 Can a "GROUP BY" clause use columns not in the SELECT?
00444 @return <code>true</code> if so; <code>false</code> otherwise
00445 }
00446 function TZMySQLDatabaseMetadata.SupportsGroupByUnrelated: Boolean;
00447 begin
00448 Result := False;
00449 end;
00450
00451 {**
00452 Can a "GROUP BY" clause add columns not in the SELECT
00453 provided it specifies all the columns in the SELECT?
00454 @return <code>true</code> if so; <code>false</code> otherwise
00455 }
00456 function TZMySQLDatabaseMetadata.SupportsGroupByBeyondSelect: Boolean;
00457 begin
00458 Result := True;
00459 end;
00460
00461 {**
00462 Is the SQL Integrity Enhancement Facility supported?
00463 @return <code>true</code> if so; <code>false</code> otherwise
00464 }
00465 function TZMySQLDatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00466 begin
00467 Result := False;
00468 end;
00469
00470 {**
00471 What's the database vendor's preferred term for "schema"?
00472 @return the vendor term
00473 }
00474 function TZMySQLDatabaseMetadata.GetSchemaTerm: string;
00475 begin
00476 Result := '';
00477 end;
00478
00479 {**
00480 What's the database vendor's preferred term for "procedure"?
00481 @return the vendor term
00482 }
00483 function TZMySQLDatabaseMetadata.GetProcedureTerm: string;
00484 begin
00485 Result := '';
00486 end;
00487
00488 {**
00489 What's the database vendor's preferred term for "catalog"?
00490 @return the vendor term
00491 }
00492 function TZMySQLDatabaseMetadata.GetCatalogTerm: string;
00493 begin
00494 Result := 'Database';
00495 end;
00496
00497 {**
00498 Can a catalog name be used in a data manipulation statement?
00499 @return <code>true</code> if so; <code>false</code> otherwise
00500 }
00501 function TZMySQLDatabaseMetadata.SupportsCatalogsInDataManipulation: Boolean;
00502 var
00503 MajorVersion: Integer;
00504 MinorVersion: Integer;
00505 begin
00506 GetVersion(MajorVersion, MinorVersion);
00507 Result := ((MajorVersion = 3) and (MinorVersion >= 22)) or (MajorVersion > 3);
00508 end;
00509
00510 {**
00511 Can a catalog name be used in a table definition statement?
00512 @return <code>true</code> if so; <code>false</code> otherwise
00513 }
00514 function TZMySQLDatabaseMetadata.SupportsCatalogsInTableDefinitions: Boolean;
00515 begin
00516 Result := False;
00517 end;
00518
00519 {**
00520 Are subqueries in comparison expressions supported?
00521 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00522 @return <code>true</code> if so; <code>false</code> otherwise
00523 }
00524 function TZMySQLDatabaseMetadata.SupportsSubqueriesInComparisons: Boolean;
00525 begin
00526 Result := True;
00527 end;
00528
00529 {**
00530 Is SQL UNION ALL supported?
00531 @return <code>true</code> if so; <code>false</code> otherwise
00532 }
00533 function TZMySQLDatabaseMetadata.SupportsUnionAll: Boolean;
00534 var
00535 MajorVersion: Integer;
00536 MinorVersion: Integer;
00537 begin
00538 GetVersion(MajorVersion, MinorVersion);
00539 Result := MajorVersion >= 4;
00540 end;
00541
00542 {**
00543 Can statements remain open across commits?
00544 @return <code>true</code> if statements always remain open;
00545 <code>false</code> if they might not remain open
00546 }
00547 function TZMySQLDatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00548 begin
00549 Result := False;
00550 end;
00551
00552 {**
00553 Can statements remain open across rollbacks?
00554 @return <code>true</code> if statements always remain open;
00555 <code>false</code> if they might not remain open
00556 }
00557 function TZMySQLDatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00558 begin
00559 Result := False;
00560 end;
00561
00562
00563
00564
00565
00566
00567
00568 {**
00569 How many hex characters can you have in an inline binary literal?
00570 @return max binary literal length in hex characters;
00571 a result of zero means that there is no limit or the limit is not known
00572 }
00573 function TZMySQLDatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00574 begin
00575 Result := 16777208;
00576 end;
00577
00578 {**
00579 What's the max length for a character literal?
00580 @return max literal length;
00581 a result of zero means that there is no limit or the limit is not known
00582 }
00583 function TZMySQLDatabaseMetadata.GetMaxCharLiteralLength: Integer;
00584 begin
00585 Result := 16777208;
00586 end;
00587
00588 {**
00589 What's the limit on column name length?
00590 @return max column name length;
00591 a result of zero means that there is no limit or the limit is not known
00592 }
00593 function TZMySQLDatabaseMetadata.GetMaxColumnNameLength: Integer;
00594 begin
00595 Result := 64;
00596 end;
00597
00598 {**
00599 What's the maximum number of columns in a "GROUP BY" clause?
00600 @return max number of columns;
00601 a result of zero means that there is no limit or the limit is not known
00602 }
00603 function TZMySQLDatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00604 begin
00605 Result := 16;
00606 end;
00607
00608 {**
00609 What's the maximum number of columns allowed in an index?
00610 @return max number of columns;
00611 a result of zero means that there is no limit or the limit is not known
00612 }
00613 function TZMySQLDatabaseMetadata.GetMaxColumnsInIndex: Integer;
00614 begin
00615 Result := 16;
00616 end;
00617
00618 {**
00619 What's the maximum number of columns in an "ORDER BY" clause?
00620 @return max number of columns;
00621 a result of zero means that there is no limit or the limit is not known
00622 }
00623 function TZMySQLDatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00624 begin
00625 Result := 16;
00626 end;
00627
00628 {**
00629 What's the maximum number of columns in a "SELECT" list?
00630 @return max number of columns;
00631 a result of zero means that there is no limit or the limit is not known
00632 }
00633 function TZMySQLDatabaseMetadata.GetMaxColumnsInSelect: Integer;
00634 begin
00635 Result := 256;
00636 end;
00637
00638 {**
00639 What's the maximum number of columns in a table?
00640 @return max number of columns;
00641 a result of zero means that there is no limit or the limit is not known
00642 }
00643 function TZMySQLDatabaseMetadata.GetMaxColumnsInTable: Integer;
00644 begin
00645 Result := 512;
00646 end;
00647
00648 {**
00649 How many active connections can we have at a time to this database?
00650 @return max number of active connections;
00651 a result of zero means that there is no limit or the limit is not known
00652 }
00653 function TZMySQLDatabaseMetadata.GetMaxConnections: Integer;
00654 begin
00655 Result := 0;
00656 end;
00657
00658 {**
00659 What's the maximum cursor name length?
00660 @return max cursor name length in bytes;
00661 a result of zero means that there is no limit or the limit is not known
00662 }
00663 function TZMySQLDatabaseMetadata.GetMaxCursorNameLength: Integer;
00664 begin
00665 Result := 64;
00666 end;
00667
00668 {**
00669 Retrieves the maximum number of bytes for an index, including all
00670 of the parts of the index.
00671 @return max index length in bytes, which includes the composite of all
00672 the constituent parts of the index;
00673 a result of zero means that there is no limit or the limit is not known
00674 }
00675 function TZMySQLDatabaseMetadata.GetMaxIndexLength: Integer;
00676 begin
00677 Result := 128;
00678 end;
00679
00680 {**
00681 What's the maximum length of a catalog name?
00682 @return max name length in bytes;
00683 a result of zero means that there is no limit or the limit is not known
00684 }
00685 function TZMySQLDatabaseMetadata.GetMaxCatalogNameLength: Integer;
00686 begin
00687 Result := 32;
00688 end;
00689
00690 {**
00691 What's the maximum length of a single row?
00692 @return max row size in bytes;
00693 a result of zero means that there is no limit or the limit is not known
00694 }
00695 function TZMySQLDatabaseMetadata.GetMaxRowSize: Integer;
00696 begin
00697 Result := 2147483639;
00698 end;
00699
00700 {**
00701 Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
00702 blobs?
00703 @return <code>true</code> if so; <code>false</code> otherwise
00704 }
00705 function TZMySQLDatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
00706 begin
00707 Result := True;
00708 end;
00709
00710 {**
00711 What's the maximum length of an SQL statement?
00712 @return max length in bytes;
00713 a result of zero means that there is no limit or the limit is not known
00714 }
00715 function TZMySQLDatabaseMetadata.GetMaxStatementLength: Integer;
00716 begin
00717 Result := 65531;
00718 end;
00719
00720 {**
00721 How many active statements can we have open at one time to this
00722 database?
00723 @return the maximum number of statements that can be open at one time;
00724 a result of zero means that there is no limit or the limit is not known
00725 }
00726 function TZMySQLDatabaseMetadata.GetMaxStatements: Integer;
00727 begin
00728 Result := 0;
00729 end;
00730
00731 {**
00732 What's the maximum length of a table name?
00733 @return max name length in bytes;
00734 a result of zero means that there is no limit or the limit is not known
00735 }
00736 function TZMySQLDatabaseMetadata.GetMaxTableNameLength: Integer;
00737 begin
00738 Result := 64;
00739 end;
00740
00741 {**
00742 What's the maximum number of tables in a SELECT statement?
00743 @return the maximum number of tables allowed in a SELECT statement;
00744 a result of zero means that there is no limit or the limit is not known
00745 }
00746 function TZMySQLDatabaseMetadata.GetMaxTablesInSelect: Integer;
00747 begin
00748 Result := 256;
00749 end;
00750
00751 {**
00752 What's the maximum length of a user name?
00753 @return max user name length in bytes;
00754 a result of zero means that there is no limit or the limit is not known
00755 }
00756 function TZMySQLDatabaseMetadata.GetMaxUserNameLength: Integer;
00757 begin
00758 Result := 16;
00759 end;
00760
00761
00762
00763 {**
00764 What's the database's default transaction isolation level? The
00765 values are defined in <code>java.sql.Connection</code>.
00766 @return the default isolation level
00767 @see Connection
00768 }
00769 function TZMySQLDatabaseMetadata.GetDefaultTransactionIsolation:
00770 TZTransactIsolationLevel;
00771 begin
00772 Result := tiNone;
00773 end;
00774
00775 {**
00776 Are both data definition and data manipulation statements
00777 within a transaction supported?
00778 @return <code>true</code> if so; <code>false</code> otherwise
00779 }
00780 function TZMySQLDatabaseMetadata.
00781 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
00782 begin
00783 Result := True;
00784 end;
00785
00786 {**
00787 Are only data manipulation statements within a transaction
00788 supported?
00789 @return <code>true</code> if so; <code>false</code> otherwise
00790 }
00791 function TZMySQLDatabaseMetadata.SupportsDataManipulationTransactionsOnly: Boolean;
00792 begin
00793 case GetConnection.GetTransactionIsolation of
00794 tiReadUncommitted: Result := True;
00795 tiReadCommitted: Result := True;
00796 tiRepeatableRead: Result := True;
00797 tiSerializable: Result := True;
00798 else Result := False;
00799 end;
00800 end;
00801
00802 procedure TZMySQLDatabaseMetadata.GetCatalogAndNamePattern(const Catalog,
00803 SchemaPattern, NamePattern: string; out OutCatalog, OutNamePattern: string);
00804 begin
00805 if Catalog = '' then
00806 begin
00807 if SchemaPattern <> '' then
00808 OutCatalog := SchemaPattern
00809 else
00810 OutCatalog := FDatabase;
00811 end
00812 else
00813 OutCatalog := Catalog;
00814
00815 if NamePattern = '' then
00816 OutNamePattern := '%'
00817 else
00818 OutNamePattern := NamePattern;
00819 end;
00820
00821 {**
00822 Gets a description of tables available in a catalog.
00823
00824 <P>Only table descriptions matching the catalog, schema, table
00825 name and type criteria are returned. They are ordered by
00826 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
00827
00828 <P>Each table description has the following columns:
00829 <OL>
00830 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
00831 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
00832 <LI><B>TABLE_NAME</B> String => table name
00833 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
00834 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
00835 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
00836 <LI><B>REMARKS</B> String => explanatory comment on the table
00837 </OL>
00838
00839 <P><B>Note:</B> Some databases may not return information for
00840 all tables.
00841
00842 @param catalog a catalog name; "" retrieves those without a
00843 catalog; null means drop catalog name from the selection criteria
00844 @param schemaPattern a schema name pattern; "" retrieves those
00845 without a schema
00846 @param tableNamePattern a table name pattern
00847 @param types a list of table types to include; null returns all types
00848 @return <code>ResultSet</code> - each row is a table description
00849 @see #getSearchStringEscape
00850 }
00851 function TZMySQLDatabaseMetadata.UncachedGetTables(const Catalog: string;
00852 const SchemaPattern: string; const TableNamePattern: string;
00853 const Types: TStringDynArray): IZResultSet;
00854 var
00855 LCatalog, LTableNamePattern: string;
00856 begin
00857 Result := ConstructVirtualResultSet(TableColumnsDynArray);
00858
00859 GetCatalogAndNamePattern(Catalog, SchemaPattern, TableNamePattern,
00860 LCatalog, LTableNamePattern);
00861
00862 with GetConnection.CreateStatement.ExecuteQuery(
00863 Format('SHOW TABLES FROM %s LIKE ''%s''',
00864 [GetIdentifierConvertor.Quote(LCatalog), LTableNamePattern])) do
00865 begin
00866 while Next do
00867 begin
00868 Result.MoveToInsertRow;
00869 Result.UpdateString(1, LCatalog);
00870 Result.UpdateString(3, GetString(1));
00871 Result.UpdateString(4, 'TABLE');
00872 Result.InsertRow;
00873 end;
00874 Close;
00875 end;
00876
00877
00878 if not Result.First and (LTableNamePattern <> '%') then
00879 begin
00880 try
00881 EnterSilentMySQLError;
00882 try
00883 if GetConnection.CreateStatement.ExecuteQuery(
00884 Format('SHOW COLUMNS FROM %s.%s',
00885 [GetIdentifierConvertor.Quote(LCatalog),
00886 GetIdentifierConvertor.Quote(LTableNamePattern)])).Next then
00887 begin
00888 Result.MoveToInsertRow;
00889 Result.UpdateString(1, LCatalog);
00890 Result.UpdateString(3, LTableNamePattern);
00891 Result.UpdateString(4, 'TABLE');
00892 Result.InsertRow;
00893 end;
00894 finally
00895 LeaveSilentMySQLError;
00896 end;
00897 except
00898 on EZMySQLSilentException do ;
00899 on EZSQLException do ;
00900 end;
00901 end;
00902 end;
00903
00904 {**
00905 Gets the catalog names available in this database. The results
00906 are ordered by catalog name.
00907
00908 <P>The catalog column is:
00909 <OL>
00910 <LI><B>TABLE_CAT</B> String => catalog name
00911 </OL>
00912
00913 @return <code>ResultSet</code> - each row has a single String column that is a
00914 catalog name
00915 }
00916 function TZMySQLDatabaseMetadata.UncachedGetCatalogs: IZResultSet;
00917 begin
00918 Result := ConstructVirtualResultSet(CatalogColumnsDynArray);
00919
00920 with GetConnection.CreateStatement.ExecuteQuery('SHOW DATABASES') do
00921 begin
00922 while Next do
00923 begin
00924 Result.MoveToInsertRow;
00925 Result.UpdateString(1, GetString(1));
00926 Result.InsertRow;
00927 end;
00928 Close;
00929 end;
00930 end;
00931
00932 {**
00933 Gets the table types available in this database. The results
00934 are ordered by table type.
00935
00936 <P>The table type is:
00937 <OL>
00938 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
00939 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
00940 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
00941 </OL>
00942
00943 @return <code>ResultSet</code> - each row has a single String column that is a
00944 table type
00945 }
00946 function TZMySQLDatabaseMetadata.UncachedGetTableTypes: IZResultSet;
00947 begin
00948 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
00949 Result.MoveToInsertRow;
00950 Result.UpdateString(1, 'TABLE');
00951 Result.InsertRow;
00952 end;
00953
00954 {**
00955 Gets a description of table columns available in
00956 the specified catalog.
00957
00958 <P>Only column descriptions matching the catalog, schema, table
00959 and column name criteria are returned. They are ordered by
00960 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
00961
00962 <P>Each column description has the following columns:
00963 <OL>
00964 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
00965 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
00966 <LI><B>TABLE_NAME</B> String => table name
00967 <LI><B>COLUMN_NAME</B> String => column name
00968 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
00969 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
00970 for a UDT the type name is fully qualified
00971 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
00972 types this is the maximum number of characters, for numeric or
00973 decimal types this is precision.
00974 <LI><B>BUFFER_LENGTH</B> is not used.
00975 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
00976 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
00977 <LI><B>NULLABLE</B> int => is NULL allowed?
00978 <UL>
00979 <LI> columnNoNulls - might not allow NULL values
00980 <LI> columnNullable - definitely allows NULL values
00981 <LI> columnNullableUnknown - nullability unknown
00982 </UL>
00983 <LI><B>REMARKS</B> String => comment describing column (may be null)
00984 <LI><B>COLUMN_DEF</B> String => default value (may be null)
00985 <LI><B>SQL_DATA_TYPE</B> int => unused
00986 <LI><B>SQL_DATETIME_SUB</B> int => unused
00987 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
00988 maximum number of bytes in the column
00989 <LI><B>ORDINAL_POSITION</B> int => index of column in table
00990 (starting at 1)
00991 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
00992 does not allow NULL values; "YES" means the column might
00993 allow NULL values. An empty string means nobody knows.
00994 </OL>
00995
00996 @param catalog a catalog name; "" retrieves those without a
00997 catalog; null means drop catalog name from the selection criteria
00998 @param schemaPattern a schema name pattern; "" retrieves those
00999 without a schema
01000 @param tableNamePattern a table name pattern
01001 @param columnNamePattern a column name pattern
01002 @return <code>ResultSet</code> - each row is a column description
01003 @see #getSearchStringEscape
01004 }
01005 function TZMySQLDatabaseMetadata.UncachedGetColumns(const Catalog: string;
01006 const SchemaPattern: string; const TableNamePattern: string;
01007 const ColumnNamePattern: string): IZResultSet;
01008 var
01009 I, J: Integer;
01010 MySQLType: TZSQLType;
01011 TempCatalog, TempColumnNamePattern, TempTableNamePattern: string;
01012
01013 { TODO : TempStr is not set to a value in the whole method. => Length(TempStr) = 0 }
01014 TempStr: string;
01015 TempPos: Integer;
01016
01017 TypeInfoList: TStrings;
01018 TypeInfo, TypeInfoFirst, TypeInfoSecond: string;
01019 Nullable, DefaultValue: string;
01020 HasDefaultValue: Boolean;
01021 ColumnSize, ColumnDecimals: Integer;
01022 OrdPosition: Integer;
01023
01024 TableNameList: TStrings;
01025 TableNameLength: Integer;
01026 ColumnIndexes : Array[1..5] of integer;
01027 Res : IZResultset;
01028
01029 begin
01030 Res := ConstructVirtualResultSet(TableColColumnsDynArray);
01031
01032 GetCatalogAndNamePattern(Catalog, SchemaPattern, ColumnNamePattern,
01033 TempCatalog, TempColumnNamePattern);
01034
01035 TableNameLength := 0;
01036 TableNameList := TStringList.Create;
01037 TypeInfoList := TStringList.Create;
01038 try
01039 with GetTables(Catalog, SchemaPattern, TableNamePattern, nil) do
01040 begin
01041 while Next do
01042 begin
01043 TableNameList.Add(GetString(3));
01044 TableNameLength := Max(TableNameLength, Length(TableNameList[TableNameList.Count - 1]));
01045 end;
01046 Close;
01047 end;
01048
01049 for I := 0 to TableNameList.Count - 1 do
01050 begin
01051 OrdPosition := 1;
01052 TempTableNamePattern := TableNameList.Strings[I];
01053
01054 with GetConnection.CreateStatement.ExecuteQuery(
01055 Format('SHOW COLUMNS FROM %s.%s LIKE ''%s''',
01056 [GetIdentifierConvertor.Quote(TempCatalog),
01057 GetIdentifierConvertor.Quote(TempTableNamePattern),
01058 TempColumnNamePattern])) do
01059 begin
01060 ColumnIndexes[1] := FindColumn('Field');
01061 ColumnIndexes[2] := FindColumn('Type');
01062 ColumnIndexes[3] := FindColumn('Null');
01063 ColumnIndexes[4] := FindColumn('Extra');
01064 ColumnIndexes[5] := FindColumn('Default');
01065 while Next do
01066 begin
01067 {initialise some variables}
01068 ColumnSize := 0;
01069 TypeInfoFirst := '';
01070 TypeInfoSecond := '';
01071
01072 Res.MoveToInsertRow;
01073 Res.UpdateString(1, TempCatalog);
01074 Res.UpdateString(2, '');
01075 Res.UpdateString(3, TempTableNamePattern);
01076 Res.UpdateString(4, GetString(ColumnIndexes[1]));
01077
01078 TypeInfo := GetString(ColumnIndexes[2]);
01079 if StrPos(PChar(TypeInfo), '(') <> nil then
01080 begin
01081 PutSplitString(TypeInfoList, TypeInfo, '()');
01082 TypeInfoFirst := TypeInfoList.Strings[0];
01083 TypeInfoSecond := TypeInfoList.Strings[1];
01084 end else
01085 TypeInfoFirst := TypeInfo;
01086
01087 TypeInfoFirst := LowerCase(TypeInfoFirst);
01088 MySQLType := ConvertMySQLTypeToSQLType(TypeInfoFirst, TypeInfo);
01089 Res.UpdateInt(5, Ord(MySQLType));
01090 Res.UpdateString(6, TypeInfoFirst);
01091
01092 Res.UpdateInt(7, 0);
01093 Res.UpdateInt(9, 0);
01094 { the column type is ENUM}
01095 if TypeInfoFirst = 'enum' then
01096 begin
01097 PutSplitString(TypeInfoList, TypeInfoSecond, ',');
01098 for J := 0 to TypeInfoList.Count-1 do
01099 ColumnSize := Max(ColumnSize, Length(TypeInfoList.Strings[J]));
01100
01101 Res.UpdateInt(7, ColumnSize);
01102 Res.UpdateInt(9, 0);
01103 end
01104 else
01105 { the column type is decimal }
01106 if StrPos(PChar(TypeInfo), ',') <> nil then
01107 begin
01108 TempPos := FirstDelimiter(',', TypeInfoSecond);
01109 ColumnSize := StrToIntDef(Copy(TypeInfoSecond, 1, TempPos - 1), 0);
01110 ColumnDecimals := StrToIntDef(Copy(TypeInfoSecond, TempPos + 1,
01111 Length(TempStr) - TempPos), 0);
01112 Res.UpdateInt(7, ColumnSize);
01113 Res.UpdateInt(9, ColumnDecimals);
01114 end
01115 else
01116 begin
01117 { the column type is other }
01118 if TypeInfoSecond <> '' then
01119 ColumnSize := StrToIntDef(TypeInfoSecond, 0)
01120 else if TypeInfoFirst = 'tinyint' then
01121 ColumnSize := 1
01122 else if TypeInfoFirst = 'smallint' then
01123 ColumnSize := 6
01124 else if TypeInfoFirst = 'mediumint' then
01125 ColumnSize := 6
01126 else if TypeInfoFirst = 'int' then
01127 ColumnSize := 11
01128 else if TypeInfoFirst = 'integer' then
01129 ColumnSize := 11
01130 else if TypeInfoFirst = 'bigint' then
01131 ColumnSize := 25
01132 else if TypeInfoFirst = 'int24' then
01133 ColumnSize := 25
01134 else if TypeInfoFirst = 'real' then
01135 ColumnSize := 12
01136 else if TypeInfoFirst = 'float' then
01137 ColumnSize := 12
01138 else if TypeInfoFirst = 'decimal' then
01139 ColumnSize := 12
01140 else if TypeInfoFirst = 'numeric' then
01141 ColumnSize := 12
01142 else if TypeInfoFirst = 'double' then
01143 ColumnSize := 22
01144 else if TypeInfoFirst = 'char' then
01145 ColumnSize := 1
01146 else if TypeInfoFirst = 'varchar' then
01147 ColumnSize := 255
01148 else if TypeInfoFirst = 'date' then
01149 ColumnSize := 10
01150 else if TypeInfoFirst = 'time' then
01151 ColumnSize := 8
01152 else if TypeInfoFirst = 'timestamp' then
01153 ColumnSize := 19
01154 else if TypeInfoFirst = 'datetime' then
01155 ColumnSize := 19
01156 else if TypeInfoFirst = 'tinyblob' then
01157 ColumnSize := 255
01158 else if TypeInfoFirst = 'blob' then
01159 ColumnSize := MAXBUF
01160 else if TypeInfoFirst = 'mediumblob' then
01161 ColumnSize := 16277215
01162 else if TypeInfoFirst = 'longblob' then
01163 ColumnSize := High(Integer)
01164 else if TypeInfoFirst = 'tinytext' then
01165 ColumnSize := 255
01166 else if TypeInfoFirst = 'text' then
01167 ColumnSize := 65535
01168 else if TypeInfoFirst = 'mediumtext' then
01169 ColumnSize := 16277215
01170 else if TypeInfoFirst = 'enum' then
01171 ColumnSize := 255
01172 else if TypeInfoFirst = 'set' then
01173 ColumnSize := 255;
01174 Res.UpdateInt(7, ColumnSize);
01175 Res.UpdateInt(9, 0);
01176 end;
01177
01178 Res.UpdateInt(8, MAXBUF);
01179 Res.UpdateNull(10);
01180
01181 { Sets nullable fields. }
01182 Nullable := GetString(ColumnIndexes[3]);
01183 if Nullable <> '' then
01184 if Nullable = 'YES' then
01185 begin
01186 Res.UpdateInt(11, Ord(ntNullable));
01187 Res.UpdateString(18, 'YES');
01188 end
01189 else
01190 begin
01191 Res.UpdateInt(11, Ord(ntNoNulls));
01192 Res.UpdateString(18, 'NO');
01193 end
01194 else
01195 begin
01196 Res.UpdateInt(11, 0);
01197 Res.UpdateString(18, 'NO');
01198 end;
01199 Res.UpdateString(12, GetString(ColumnIndexes[4]));
01200
01201 if IsNull(ColumnIndexes[5]) then
01202 begin
01203
01204
01205
01206
01207 HasDefaultValue := false;
01208 DefaultValue := '';
01209 end else begin
01210 DefaultValue := GetString(ColumnIndexes[5]);
01211 if not (DefaultValue = '') then HasDefaultValue := true
01212 else begin
01213
01214
01215
01216
01217
01218
01219
01220 HasDefaultValue := false;
01221 if Pos('blob', TypeInfoFirst) > 0 then HasDefaultValue := true;
01222 if Pos('text', TypeInfoFirst) > 0 then HasDefaultValue := true;
01223 if Pos('char', TypeInfoFirst) > 0 then HasDefaultValue := true;
01224 if 'set' = TypeInfoFirst then HasDefaultValue := true;
01225 if 'enum' = TypeInfoFirst then begin
01226 HasDefaultValue := true;
01227 DefaultValue := Copy(TypeInfoSecond, 2,length(TypeInfoSecond)-1);
01228 DefaultValue := Copy(DefaultValue, 1, Pos('''', DefaultValue) - 1);
01229 end;
01230 end;
01231 end;
01232 if HasDefaultValue then
01233 begin
01234
01235
01236 if (MySQLType in [stString, stUnicodeString, stBinaryStream, stAsciiStream]) then begin
01237
01238
01239
01240 if DefaultValue <> 'CURRENT_TIMESTAMP' then
01241 DefaultValue := '''' + DefaultValue + ''''
01242 end else if (MySQLType in [stDate, stTime, stTimestamp]) then
01243 begin
01244 if DefaultValue <> 'CURRENT_TIMESTAMP' then
01245 DefaultValue := '''' + DefaultValue + ''''
01246 end
01247 else if (MySQLType = stBoolean) and (TypeInfoFirst = 'enum') then
01248 begin
01249 if (DefaultValue = 'y') or (DefaultValue = 'Y') then
01250 DefaultValue := '1'
01251 else DefaultValue := '0';
01252 end;
01253 end;
01254 Res.UpdateString(13, DefaultValue);
01255 Res.UpdateNull(14);
01256 Res.UpdateNull(15);
01257 Res.UpdateInt(17, OrdPosition);
01258
01259 Res.UpdateBoolean(19,
01260 Trim(LowerCase(GetString(ColumnIndexes[4]))) = 'auto_increment');
01261 Res.UpdateBoolean(20,
01262 GetIdentifierConvertor.IsCaseSensitive(
01263 GetString(ColumnIndexes[1])));
01264 Res.UpdateBoolean(21, True);
01265 Res.UpdateBoolean(22, True);
01266 Res.UpdateBoolean(23, True);
01267 Res.UpdateBoolean(24, False);
01268
01269 Inc(OrdPosition);
01270 Res.InsertRow;
01271 end;
01272 Close;
01273 end;
01274 end;
01275 finally
01276 TableNameList.Free;
01277 TypeInfoList.Free;
01278 end;
01279 Result := Res;
01280 end;
01281
01282 {**
01283 Gets a description of the access rights for a table's columns.
01284
01285 <P>Only privileges matching the column name criteria are
01286 returned. They are ordered by COLUMN_NAME and PRIVILEGE.
01287
01288 <P>Each privilige description has the following columns:
01289 <OL>
01290 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01291 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01292 <LI><B>TABLE_NAME</B> String => table name
01293 <LI><B>COLUMN_NAME</B> String => column name
01294 <LI><B>GRANTOR</B> => grantor of access (may be null)
01295 <LI><B>GRANTEE</B> String => grantee of access
01296 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01297 INSERT, UPDATE, REFRENCES, ...)
01298 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01299 to grant to others; "NO" if not; null if unknown
01300 </OL>
01301
01302 @param catalog a catalog name; "" retrieves those without a
01303 catalog; null means drop catalog name from the selection criteria
01304 @param schema a schema name; "" retrieves those without a schema
01305 @param table a table name
01306 @param columnNamePattern a column name pattern
01307 @return <code>ResultSet</code> - each row is a column privilege description
01308 @see #getSearchStringEscape
01309 }
01310 function TZMySQLDatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
01311 const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
01312 var
01313 I: Integer;
01314 LCatalog, LColumnNamePattern: string;
01315 Host, Database, Grantor, User, FullUser: string;
01316 AllPrivileges, ColumnName, Privilege: string;
01317 PrivilegesList: TStrings;
01318 begin
01319 Result := ConstructVirtualResultSet(TableColPrivColumnsDynArray);
01320
01321 GetCatalogAndNamePattern(Catalog, Schema, ColumnNamePattern,
01322 LCatalog, LColumnNamePattern);
01323
01324 PrivilegesList := TStringList.Create;
01325 try
01326 with GetConnection.CreateStatement.ExecuteQuery(
01327 Format('SELECT c.host, c.db, t.grantor, c.user, c.table_name,'
01328 + ' c.column_name, c.column_priv FROM mysql.columns_priv c,'
01329 + ' mysql.tables_priv t WHERE c.host=t.host AND c.db=t.db'
01330 + ' AND c.table_name=t.table_name AND c.db=''%s'''
01331 + ' AND c.table_name=''%s'' AND c.column_name LIKE ''%s''',
01332 [LCatalog, Table, LColumnNamePattern])) do
01333 begin
01334 while Next do
01335 begin
01336 Host := GetString(1);
01337 Database := GetString(2);
01338 Grantor := GetString(4);
01339 User := GetString(5);
01340 if User = '' then
01341 User := '%';
01342 if Host <> '' then
01343 FullUser := User + '@' + Host;
01344 ColumnName := GetString(6);
01345
01346 AllPrivileges := GetString(7);
01347 PutSplitString(PrivilegesList, AllPrivileges, ',');
01348
01349 for I := 0 to PrivilegesList.Count - 1 do
01350 begin
01351 Result.MoveToInsertRow;
01352 Privilege := Trim(PrivilegesList.Strings[I]);
01353 Result.UpdateString(1, LCatalog);
01354 Result.UpdateNull(2);
01355 Result.UpdateString(3, Table);
01356 Result.UpdateString(4, ColumnName);
01357 Result.UpdateString(5, Grantor);
01358 Result.UpdateString(6, FullUser);
01359 Result.UpdateString(7, Privilege);
01360 Result.UpdateNull(8);
01361 Result.InsertRow;
01362 end;
01363 end;
01364 Close;
01365 end;
01366 finally
01367 PrivilegesList.Free;
01368 end;
01369 end;
01370
01371 {**
01372 Gets a description of the access rights for each table available
01373 in a catalog. Note that a table privilege applies to one or
01374 more columns in the table. It would be wrong to assume that
01375 this priviledge applies to all columns (this may be true for
01376 some systems but is not true for all.)
01377
01378 <P>Only privileges matching the schema and table name
01379 criteria are returned. They are ordered by TABLE_SCHEM,
01380 TABLE_NAME, and PRIVILEGE.
01381
01382 <P>Each privilige description has the following columns:
01383 <OL>
01384 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01385 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01386 <LI><B>TABLE_NAME</B> String => table name
01387 <LI><B>GRANTOR</B> => grantor of access (may be null)
01388 <LI><B>GRANTEE</B> String => grantee of access
01389 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01390 INSERT, UPDATE, REFRENCES, ...)
01391 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01392 to grant to others; "NO" if not; null if unknown
01393 </OL>
01394
01395 @param catalog a catalog name; "" retrieves those without a
01396 catalog; null means drop catalog name from the selection criteria
01397 @param schemaPattern a schema name pattern; "" retrieves those
01398 without a schema
01399 @param tableNamePattern a table name pattern
01400 @return <code>ResultSet</code> - each row is a table privilege description
01401 @see #getSearchStringEscape
01402 }
01403 function TZMySQLDatabaseMetadata.UncachedGetTablePrivileges(const Catalog: string;
01404 const SchemaPattern: string; const TableNamePattern: string): IZResultSet;
01405 var
01406 I: Integer;
01407 LCatalog, LTableNamePattern: string;
01408 Host, Database, Table, Grantor, User, FullUser: string;
01409 AllPrivileges, Privilege: string;
01410 PrivilegesList: TStrings;
01411 begin
01412 Result := ConstructVirtualResultSet(TablePrivColumnsDynArray);
01413
01414 GetCatalogAndNamePattern(Catalog, SchemaPattern, TableNamePattern,
01415 LCatalog, LTableNamePattern);
01416
01417 PrivilegesList := TStringList.Create;
01418 try
01419 with GetConnection.CreateStatement.ExecuteQuery(
01420 Format('SELECT host,db,table_name,grantor,user,table_priv'
01421 + ' from mysql.tables_priv WHERE db=''%s'' AND table_name LIKE ''%s''',
01422 [LCatalog, LTableNamePattern])) do
01423 begin
01424 while Next do
01425 begin
01426 Host := GetString(1);
01427 Database := GetString(2);
01428 Table := GetString(3);
01429 Grantor := GetString(4);
01430 User := GetString(5);
01431 if User = '' then
01432 User := '%';
01433 if Host <> '' then
01434 FullUser := User + '@' + Host;
01435
01436 AllPrivileges := GetString(6);
01437 PutSplitString(PrivilegesList, AllPrivileges, ',');
01438
01439 for I := 0 to PrivilegesList.Count - 1 do
01440 begin
01441 Result.MoveToInsertRow;
01442 Privilege := Trim(PrivilegesList.Strings[I]);
01443 Result.UpdateString(1, Database);
01444 Result.UpdateNull(2);
01445 Result.UpdateString(3, Table);
01446 Result.UpdateString(4, Grantor);
01447 Result.UpdateString(5, FullUser);
01448 Result.UpdateString(6, Privilege);
01449 Result.UpdateNull(7);
01450 Result.InsertRow;
01451 end;
01452 end;
01453 Close;
01454 end;
01455 finally
01456 PrivilegesList.Free;
01457 end;
01458 end;
01459
01460 {**
01461 Gets a description of a table's primary key columns. They
01462 are ordered by COLUMN_NAME.
01463
01464 <P>Each primary key column description has the following columns:
01465 <OL>
01466 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01467 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01468 <LI><B>TABLE_NAME</B> String => table name
01469 <LI><B>COLUMN_NAME</B> String => column name
01470 <LI><B>KEY_SEQ</B> short => sequence number within primary key
01471 <LI><B>PK_NAME</B> String => primary key name (may be null)
01472 </OL>
01473
01474 @param catalog a catalog name; "" retrieves those without a
01475 catalog; null means drop catalog name from the selection criteria
01476 @param schema a schema name; "" retrieves those
01477 without a schema
01478 @param table a table name
01479 @return <code>ResultSet</code> - each row is a primary key column description
01480 @exception SQLException if a database access error occurs
01481 }
01482 function TZMySQLDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
01483 const Schema: string; const Table: string): IZResultSet;
01484 var
01485 KeyType: string;
01486 LCatalog: string;
01487 ColumnIndexes : Array[1..3] of integer;
01488 begin
01489 if Table = '' then
01490 raise Exception.Create(STableIsNotSpecified); //CHANGE IT!
01491 Result := ConstructVirtualResultSet(PrimaryKeyColumnsDynArray);
01492
01493 if Catalog = '' then
01494 begin
01495 if Schema <> '' then
01496 LCatalog := Schema
01497 else
01498 LCatalog := FDatabase;
01499 end
01500 else
01501 LCatalog := Catalog;
01502
01503 with GetConnection.CreateStatement.ExecuteQuery(
01504 Format('SHOW KEYS FROM %s.%s',
01505 [GetIdentifierConvertor.Quote(LCatalog),
01506 GetIdentifierConvertor.Quote(Table)])) do
01507 begin
01508 ColumnIndexes[1] := FindColumn('Key_name');
01509 ColumnIndexes[2] := FindColumn('Column_name');
01510 ColumnIndexes[3] := FindColumn('Seq_in_index');
01511 while Next do
01512 begin
01513 KeyType := UpperCase(GetString(ColumnIndexes[1]));
01514 KeyType := Copy(KeyType, 1, 3);
01515 if KeyType = 'PRI' then
01516 begin
01517 Result.MoveToInsertRow;
01518 Result.UpdateString(1, LCatalog);
01519 Result.UpdateString(2, '');
01520 Result.UpdateString(3, Table);
01521 Result.UpdateString(4, GetString(ColumnIndexes[2]));
01522 Result.UpdateString(5, GetString(ColumnIndexes[3]));
01523 Result.UpdateNull(6);
01524 Result.InsertRow;
01525 end;
01526 end;
01527 Close;
01528 end;
01529 end;
01530
01531 {**
01532 Gets a description of the primary key columns that are
01533 referenced by a table's foreign key columns (the primary keys
01534 imported by a table). They are ordered by PKTABLE_CAT,
01535 PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
01536
01537 <P>Each primary key column description has the following columns:
01538 <OL>
01539 <LI><B>PKTABLE_CAT</B> String => primary key table catalog
01540 being imported (may be null)
01541 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
01542 being imported (may be null)
01543 <LI><B>PKTABLE_NAME</B> String => primary key table name
01544 being imported
01545 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01546 being imported
01547 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01548 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01549 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01550 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01551 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01552 <LI><B>UPDATE_RULE</B> short => What happens to
01553 foreign key when primary is updated:
01554 <UL>
01555 <LI> importedNoAction - do not allow update of primary
01556 key if it has been imported
01557 <LI> importedKeyCascade - change imported key to agree
01558 with primary key update
01559 <LI> importedKeySetNull - change imported key to NULL if
01560 its primary key has been updated
01561 <LI> importedKeySetDefault - change imported key to default values
01562 if its primary key has been updated
01563 <LI> importedKeyRestrict - same as importedKeyNoAction
01564 (for ODBC 2.x compatibility)
01565 </UL>
01566 <LI><B>DELETE_RULE</B> short => What happens to
01567 the foreign key when primary is deleted.
01568 <UL>
01569 <LI> importedKeyNoAction - do not allow delete of primary
01570 key if it has been imported
01571 <LI> importedKeyCascade - delete rows that import a deleted key
01572 <LI> importedKeySetNull - change imported key to NULL if
01573 its primary key has been deleted
01574 <LI> importedKeyRestrict - same as importedKeyNoAction
01575 (for ODBC 2.x compatibility)
01576 <LI> importedKeySetDefault - change imported key to default if
01577 its primary key has been deleted
01578 </UL>
01579 <LI><B>FK_NAME</B> String => foreign key name (may be null)
01580 <LI><B>PK_NAME</B> String => primary key name (may be null)
01581 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
01582 constraints be deferred until commit
01583 <UL>
01584 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
01585 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
01586 <LI> importedKeyNotDeferrable - see SQL92 for definition
01587 </UL>
01588 </OL>
01589
01590 @param catalog a catalog name; "" retrieves those without a
01591 catalog; null means drop catalog name from the selection criteria
01592 @param schema a schema name; "" retrieves those
01593 without a schema
01594 @param table a table name
01595 @return <code>ResultSet</code> - each row is a primary key column description
01596 @see #getExportedKeys
01597 }
01598 function TZMySQLDatabaseMetadata.UncachedGetImportedKeys(const Catalog: string;
01599 const Schema: string; const Table: string): IZResultSet;
01600 var
01601 I: Integer;
01602 KeySeq: Integer;
01603 LCatalog: string;
01604 TableType, Comment, Keys: string;
01605 CommentList, KeyList: TStrings;
01606 ColumnIndexes : Array[1..2] of integer;
01607 begin
01608 if Table = '' then
01609 raise Exception.Create(STableIsNotSpecified);
01610 Result := ConstructVirtualResultSet(ImportedKeyColumnsDynArray);
01611
01612 if Catalog = '' then
01613 begin
01614 if Schema <> '' then
01615 LCatalog := Schema
01616 else
01617 LCatalog := FDatabase;
01618 end
01619 else
01620 LCatalog := Catalog;
01621
01622 KeyList := TStringList.Create;
01623 CommentList := TStringList.Create;
01624 try
01625 with GetConnection.CreateStatement.ExecuteQuery(
01626 Format('SHOW TABLE STATUS FROM %s LIKE ''%s''',
01627 [GetIdentifierConvertor.Quote(LCatalog), Table])) do
01628 begin
01629 ColumnIndexes[1] := FindColumn('Type');
01630 ColumnIndexes[2] := FindColumn('Comment');
01631 while Next do
01632 begin
01633 TableType := GetString(ColumnIndexes[1]);
01634 if (TableType <> '') and (LowerCase(TableType) = 'innodb') then
01635 begin
01636 Comment := GetString(ColumnIndexes[2]);
01637 if Comment <> '' then
01638 begin
01639 PutSplitString(CommentList, Comment, ';');
01640 KeySeq := 0;
01641
01642 if CommentList.Count > 4 then
01643 begin
01644 for I := 0 to CommentList.Count - 1 do
01645 begin
01646 Keys := CommentList.Strings[1];
01647 Result.MoveToInsertRow;
01648 PutSplitString(KeyList, Keys, '() /');
01649
01650 Result.UpdateString(5, LCatalog);
01651 Result.UpdateNull(6);
01652 Result.UpdateString(7, Table);
01653 Result.UpdateString(8, KeyList.Strings[0]);
01654
01655 Result.UpdateString(1, KeyList.Strings[2]);
01656 Result.UpdateNull(2);
01657 Result.UpdateString(3, KeyList.Strings[3]);
01658 Result.UpdateString(4, KeyList.Strings[4]);
01659 Result.UpdateInt(9, KeySeq);
01660 Result.UpdateInt(10, Ord(ikSetDefault));
01661 Result.UpdateInt(11, Ord(ikSetDefault));
01662 Result.UpdateNull(12);
01663 Result.UpdateNull(13);
01664 Result.UpdateInt(14, Ord(ikSetDefault));
01665 Inc(KeySeq);
01666 Result.InsertRow;
01667 end;
01668 end;
01669 end;
01670 end;
01671 end;
01672 Close;
01673 end;
01674 finally
01675 KeyList.Free;
01676 CommentList.Free;
01677 end;
01678 end;
01679
01680 {**
01681 Gets a description of the foreign key columns that reference a
01682 table's primary key columns (the foreign keys exported by a
01683 table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
01684 FKTABLE_NAME, and KEY_SEQ.
01685
01686 <P>Each foreign key column description has the following columns:
01687 <OL>
01688 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
01689 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
01690 <LI><B>PKTABLE_NAME</B> String => primary key table name
01691 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01692 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01693 being exported (may be null)
01694 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01695 being exported (may be null)
01696 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01697 being exported
01698 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01699 being exported
01700 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01701 <LI><B>UPDATE_RULE</B> short => What happens to
01702 foreign key when primary is updated:
01703 <UL>
01704 <LI> importedNoAction - do not allow update of primary
01705 key if it has been imported
01706 <LI> importedKeyCascade - change imported key to agree
01707 with primary key update
01708 <LI> importedKeySetNull - change imported key to NULL if
01709 its primary key has been updated
01710 <LI> importedKeySetDefault - change imported key to default values
01711 if its primary key has been updated
01712 <LI> importedKeyRestrict - same as importedKeyNoAction
01713 (for ODBC 2.x compatibility)
01714 </UL>
01715 <LI><B>DELETE_RULE</B> short => What happens to
01716 the foreign key when primary is deleted.
01717 <UL>
01718 <LI> importedKeyNoAction - do not allow delete of primary
01719 key if it has been imported
01720 <LI> importedKeyCascade - delete rows that import a deleted key
01721 <LI> importedKeySetNull - change imported key to NULL if
01722 its primary key has been deleted
01723 <LI> importedKeyRestrict - same as importedKeyNoAction
01724 (for ODBC 2.x compatibility)
01725 <LI> importedKeySetDefault - change imported key to default if
01726 its primary key has been deleted
01727 </UL>
01728 <LI><B>FK_NAME</B> String => foreign key name (may be null)
01729 <LI><B>PK_NAME</B> String => primary key name (may be null)
01730 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
01731 constraints be deferred until commit
01732 <UL>
01733 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
01734 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
01735 <LI> importedKeyNotDeferrable - see SQL92 for definition
01736 </UL>
01737 </OL>
01738
01739 @param catalog a catalog name; "" retrieves those without a
01740 catalog; null means drop catalog name from the selection criteria
01741 @param schema a schema name; "" retrieves those
01742 without a schema
01743 @param table a table name
01744 @return <code>ResultSet</code> - each row is a foreign key column description
01745 @see #getImportedKeys
01746 }
01747 function TZMySQLDatabaseMetadata.UncachedGetExportedKeys(const Catalog: string;
01748 const Schema: string; const Table: string): IZResultSet;
01749 var
01750 I: Integer;
01751 KeySeq: Integer;
01752 LCatalog: string;
01753 TableType, Comment, Keys: string;
01754 CommentList, KeyList: TStrings;
01755 ColumnIndexes : Array[1..3] of integer;
01756 begin
01757 if Table = '' then
01758 raise Exception.Create(STableIsNotSpecified); //CHANGE IT!
01759 Result := ConstructVirtualResultSet(ExportedKeyColumnsDynArray);
01760
01761 if Catalog = '' then
01762 begin
01763 if Schema <> '' then
01764 LCatalog := Schema
01765 else
01766 LCatalog := FDatabase;
01767 end
01768 else
01769 LCatalog := Catalog;
01770
01771 KeyList := TStringList.Create;
01772 CommentList := TStringList.Create;
01773 try
01774 with GetConnection.CreateStatement.ExecuteQuery(
01775 Format('SHOW TABLE STATUS FROM %s',
01776 [GetIdentifierConvertor.Quote(LCatalog)])) do
01777 begin
01778 ColumnIndexes[1] := FindColumn('Type');
01779 ColumnIndexes[2] := FindColumn('Comment');
01780 ColumnIndexes[3] := FindColumn('Name');
01781 while Next do
01782 begin
01783 TableType := GetString(ColumnIndexes[1]);
01784 if (TableType <> '') and (LowerCase(TableType) = 'innodb') then
01785 begin
01786 Comment := GetString(ColumnIndexes[2]);
01787 if Comment <> '' then
01788 begin
01789 PutSplitString(CommentList, Comment, ';');
01790 KeySeq := 0;
01791 if CommentList.Count > 4 then
01792 begin
01793 for I := 0 to CommentList.Count-1 do
01794 begin
01795 Keys := CommentList.Strings[1];
01796 Result.MoveToInsertRow;
01797 PutSplitString(KeyList, Keys, '() /');
01798
01799 Result.UpdateString(5, LCatalog);
01800 Result.UpdateNull(6);// FKTABLE_SCHEM
01801 Result.UpdateString(7, GetString(ColumnIndexes[3])); // FKTABLE_NAME
01802 Result.UpdateString(8, KeyList.Strings[0]); // PKTABLE_CAT
01803
01804 Result.UpdateString(1, KeyList.Strings[2]); // PKTABLE_CAT
01805 Result.UpdateNull(2); // PKTABLE_SCHEM
01806 Result.UpdateString(3, Table); // PKTABLE_NAME
01807 Result.UpdateInt(9, KeySeq); // KEY_SEQ
01808
01809 Result.UpdateInt(10, Ord(ikSetDefault)); // UPDATE_RULE
01810 Result.UpdateInt(11, Ord(ikSetDefault)); // DELETE_RULE
01811 Result.UpdateNull(12); // FK_NAME
01812 Result.UpdateNull(13); // PK_NAME
01813 Result.UpdateInt(14, Ord(ikSetDefault)); // DEFERRABILITY
01814 Inc(KeySeq);
01815 Result.InsertRow;
01816 end;
01817 end;
01818 end;
01819 end;
01820 end;
01821 Close;
01822 end;
01823 finally
01824 KeyList.Free;
01825 CommentList.Free;
01826 end;
01827 end;
01828
01829 {**
01830 Gets a description of the foreign key columns in the foreign key
01831 table that reference the primary key columns of the primary key
01832 table (describe how one table imports another's key.) This
01833 should normally return a single foreign key/primary key pair
01834 (most tables only import a foreign key from a table once.) They
01835 are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
01836 KEY_SEQ.
01837
01838 <P>Each foreign key column description has the following columns:
01839 <OL>
01840 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
01841 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
01842 <LI><B>PKTABLE_NAME</B> String => primary key table name
01843 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
01844 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
01845 being exported (may be null)
01846 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
01847 being exported (may be null)
01848 <LI><B>FKTABLE_NAME</B> String => foreign key table name
01849 being exported
01850 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
01851 being exported
01852 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
01853 <LI><B>UPDATE_RULE</B> short => What happens to
01854 foreign key when primary is updated:
01855 <UL>
01856 <LI> importedNoAction - do not allow update of primary
01857 key if it has been imported
01858 <LI> importedKeyCascade - change imported key to agree
01859 with primary key update
01860 <LI> importedKeySetNull - change imported key to NULL if
01861 its primary key has been updated
01862 <LI> importedKeySetDefault - change imported key to default values
01863 if its primary key has been updated
01864 <LI> importedKeyRestrict - same as importedKeyNoAction
01865 (for ODBC 2.x compatibility)
01866 </UL>
01867 <LI><B>DELETE_RULE</B> short => What happens to
01868 the foreign key when primary is deleted.
01869 <UL>
01870 <LI> importedKeyNoAction - do not allow delete of primary
01871 key if it has been imported
01872 <LI> importedKeyCascade - delete rows that import a deleted key
01873 <LI> importedKeySetNull - change imported key to NULL if
01874 its primary key has been deleted
01875 <LI> importedKeyRestrict - same as importedKeyNoAction
01876 (for ODBC 2.x compatibility)
01877 <LI> importedKeySetDefault - change imported key to default if
01878 its primary key has been deleted
01879 </UL>
01880 <LI><B>FK_NAME</B> String => foreign key name (may be null)
01881 <LI><B>PK_NAME</B> String => primary key name (may be null)
01882 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
01883 constraints be deferred until commit
01884 <UL>
01885 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
01886 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
01887 <LI> importedKeyNotDeferrable - see SQL92 for definition
01888 </UL>
01889 </OL>
01890
01891 @param primaryCatalog a catalog name; "" retrieves those without a
01892 catalog; null means drop catalog name from the selection criteria
01893 @param primarySchema a schema name; "" retrieves those
01894 without a schema
01895 @param primaryTable the table name that exports the key
01896 @param foreignCatalog a catalog name; "" retrieves those without a
01897 catalog; null means drop catalog name from the selection criteria
01898 @param foreignSchema a schema name; "" retrieves those
01899 without a schema
01900 @param foreignTable the table name that imports the key
01901 @return <code>ResultSet</code> - each row is a foreign key column description
01902 @see #getImportedKeys
01903 }
01904 function TZMySQLDatabaseMetadata.UncachedGetCrossReference(const PrimaryCatalog: string;
01905 const PrimarySchema: string; const PrimaryTable: string; const ForeignCatalog: string;
01906 const ForeignSchema: string; const ForeignTable: string): IZResultSet;
01907 var
01908 I: Integer;
01909 KeySeq: Integer;
01910 LForeignCatalog: string;
01911 TableType, Comment, Keys: string;
01912 CommentList, KeyList: TStrings;
01913 ColumnIndexes : Array[1..3] of integer;
01914 begin
01915 if PrimaryTable = '' then
01916 raise Exception.Create(STableIsNotSpecified);
01917 Result := ConstructVirtualResultSet(CrossRefColumnsDynArray);
01918
01919 if ForeignCatalog = '' then
01920 LForeignCatalog := FDatabase
01921 else
01922 LForeignCatalog := ForeignCatalog;
01923
01924 KeyList := TStringList.Create;
01925 CommentList := TStringList.Create;
01926 try
01927 with GetConnection.CreateStatement.ExecuteQuery(
01928 Format('SHOW TABLE STATUS FROM %s',
01929 [GetIdentifierConvertor.Quote(LForeignCatalog)])) do
01930 begin
01931 ColumnIndexes[1] := FindColumn('Type');
01932 ColumnIndexes[2] := FindColumn('Comment');
01933 ColumnIndexes[3] := FindColumn('Name');
01934 while Next do
01935 begin
01936 TableType := GetString(ColumnIndexes[1]);
01937 if (TableType <> '') and (LowerCase(TableType) = 'innodb') then
01938 begin
01939 Comment := GetString(ColumnIndexes[2]);
01940 if Comment = '' then
01941 begin
01942 PutSplitString(CommentList, Comment, ';');
01943 KeySeq := 0;
01944 if CommentList.Count > 4 then
01945 begin
01946 for I := 0 to CommentList.Count-1 do
01947 begin
01948 Keys := CommentList.Strings[1];
01949 Result.MoveToInsertRow;
01950 PutSplitString(KeyList, Keys, '() /');
01951
01952 Result.UpdateString(5, LForeignCatalog);
01953 if ForeignSchema = '' then
01954 Result.UpdateNull(6)
01955 else Result.UpdateString(6, ForeignSchema);
01956 if ForeignTable <> GetString(ColumnIndexes[3]) then
01957 Continue
01958 else
01959 Result.UpdateString(7, GetString(ColumnIndexes[3]));
01960
01961 Result.UpdateString(8, KeyList.Strings[0]);
01962
01963 Result.UpdateString(1, KeyList.Strings[2]);
01964 if PrimarySchema = '' then
01965 Result.UpdateNull(2)
01966 else Result.UpdateString(2, PrimarySchema);
01967
01968 if PrimaryTable = KeyList.Strings[3] then
01969 Continue;
01970
01971 Result.UpdateString(3, PrimaryTable);
01972 Result.UpdateString(4, KeyList.Strings[4]);
01973 Result.UpdateInt(9, KeySeq);
01974 Result.UpdateInt(10, Ord(ikSetDefault));
01975 Result.UpdateInt(11, Ord(ikSetDefault));
01976 Result.UpdateNull(12);
01977 Result.UpdateNull(13);
01978 Result.UpdateInt(14, Ord(ikSetDefault));
01979 Inc(KeySeq);
01980 Result.InsertRow;
01981 end;
01982 end;
01983 end;
01984 end;
01985 end;
01986 Close;
01987 end;
01988 finally
01989 KeyList.Free;
01990 CommentList.Free;
01991 end;
01992 end;
01993
01994 {**
01995 Gets a description of all the standard SQL types supported by
01996 this database. They are ordered by DATA_TYPE and then by how
01997 closely the data type maps to the corresponding JDBC SQL type.
01998
01999 <P>Each type description has the following columns:
02000 <OL>
02001 <LI><B>TYPE_NAME</B> String => Type name
02002 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02003 <LI><B>PRECISION</B> int => maximum precision
02004 <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
02005 (may be null)
02006 <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
02007 (may be null)
02008 <LI><B>CREATE_PARAMS</B> String => parameters used in creating
02009 the type (may be null)
02010 <LI><B>NULLABLE</B> short => can you use NULL for this type?
02011 <UL>
02012 <LI> typeNoNulls - does not allow NULL values
02013 <LI> typeNullable - allows NULL values
02014 <LI> typeNullableUnknown - nullability unknown
02015 </UL>
02016 <LI><B>CASE_SENSITIVE</B> boolean=> is it case sensitive?
02017 <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
02018 <UL>
02019 <LI> typePredNone - No support
02020 <LI> typePredChar - Only supported with WHERE .. LIKE
02021 <LI> typePredBasic - Supported except for WHERE .. LIKE
02022 <LI> typeSearchable - Supported for all WHERE ..
02023 </UL>
02024 <LI><B>UNSIGNED_ATTRIBUTE</B> boolean => is it unsigned?
02025 <LI><B>FIXED_PREC_SCALE</B> boolean => can it be a money value?
02026 <LI><B>AUTO_INCREMENT</B> boolean => can it be used for an
02027 auto-increment value?
02028 <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
02029 (may be null)
02030 <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
02031 <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
02032 <LI><B>SQL_DATA_TYPE</B> int => unused
02033 <LI><B>SQL_DATETIME_SUB</B> int => unused
02034 <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
02035 </OL>
02036
02037 @return <code>ResultSet</code> - each row is an SQL type description
02038 }
02039 function TZMySQLDatabaseMetadata.UncachedGetTypeInfo: IZResultSet;
02040 const
02041 MaxTypeCount = 33;
02042 TypeNames: array[1..MaxTypeCount] of string = (
02043 'BIT', 'BOOL', 'TINYINT', 'BIGINT', 'MEDIUMBLOB', 'LONG VARBINARY',
02044 'LONGBLOB', 'BLOB', 'TINYBLOB', 'VARBINARY', 'BINARY',
02045 'LONG VARCHAR', 'MEDIUMTEXT', 'LONGTEXT', 'TEXT', 'TINYTEXT',
02046 'CHAR', 'VARCHAR', 'NUMERIC', 'DECIMAL', 'INTEGER', 'INT',
02047 'MEDIUMINT', 'SMALLINT', 'DOUBLE', 'FLOAT', 'REAL', 'ENUM', 'SET',
02048 'DATE', 'TIME', 'DATETIME', 'TIMESTAMP');
02049 TypeCodes: array[1..MaxTypeCount] of TZSQLType = (
02050 stByte, stBoolean, stShort, stLong, stBinaryStream, stBinaryStream,
02051 stBinaryStream, stBinaryStream, stBinaryStream, stBytes, stBytes,
02052 stString, stAsciiStream, stAsciiStream, stAsciiStream, stAsciiStream,
02053 stString, stString, stBigDecimal, stBigDecimal, stInteger, stInteger,
02054 stInteger, stShort, stDouble, stFloat, stFloat, stString, stString,
02055 stDate, stTime, stTimestamp, stTimestamp);
02056 TypePrecision: array[1..MaxTypeCount] of Integer = (
02057 1, -1, 4, 16, 16777215, 16777215, MAXBUF, 65535, 255, 255, 255,
02058 16777215, 16777215, 2147483647, 65535, 255, 255, 255, 17, 17, 10, 10,
02059 7, 4, 17, 10, 10, 65535, 64, -1, -1, -1, -1);
02060 var
02061 I: Integer;
02062 Key: string;
02063 begin
02064 Key := GetTypeInfoCacheKey;
02065 Result := GetResultSetFromCache(Key);
02066 if Result = nil then
02067 begin
02068 Result := ConstructVirtualResultSet(TypeInfoColumnsDynArray);
02069
02070 for I := 1 to MaxTypeCount do
02071 begin
02072 Result.MoveToInsertRow;
02073
02074 Result.UpdateString(1, TypeNames[I]);
02075 Result.UpdateInt(2, Ord(TypeCodes[I]));
02076 if TypePrecision[I] >= 0 then
02077 Result.UpdateInt(3, TypePrecision[I])
02078 else Result.UpdateNull(3);
02079 if TypeCodes[I] in [stString, stBytes, stDate, stTime,
02080 stTimeStamp, stBinaryStream, stAsciiStream] then
02081 begin
02082 Result.UpdateString(4, '''');
02083 Result.UpdateString(5, '''');
02084 end
02085 else
02086 begin
02087 Result.UpdateNull(4);
02088 Result.UpdateNull(5);
02089 end;
02090 Result.UpdateNull(6);
02091 Result.UpdateInt(7, Ord(ntNullable));
02092 Result.UpdateBoolean(8, False);
02093 Result.UpdateBoolean(9, False);
02094 Result.UpdateBoolean(11, False);
02095 Result.UpdateBoolean(12, False);
02096 Result.UpdateBoolean(12, TypeNames[I] = 'INTEGER');
02097 Result.UpdateNull(13);
02098 Result.UpdateNull(14);
02099 Result.UpdateNull(15);
02100 Result.UpdateNull(16);
02101 Result.UpdateNull(17);
02102 Result.UpdateInt(18, 10);
02103
02104 Result.InsertRow;
02105 end;
02106
02107 AddResultSetToCache(Key, Result);
02108 end;
02109 end;
02110
02111 {**
02112 Gets a description of a table's indices and statistics. They are
02113 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
02114
02115 <P>Each index column description has the following columns:
02116 <OL>
02117 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
02118 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
02119 <LI><B>TABLE_NAME</B> String => table name
02120 <LI><B>NON_UNIQUE</B> boolean => Can index values be non-unique?
02121 false when TYPE is tableIndexStatistic
02122 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
02123 null when TYPE is tableIndexStatistic
02124 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
02125 tableIndexStatistic
02126 <LI><B>TYPE</B> short => index type:
02127 <UL>
02128 <LI> tableIndexStatistic - this identifies table statistics that are
02129 returned in conjuction with a table's index descriptions
02130 <LI> tableIndexClustered - this is a clustered index
02131 <LI> tableIndexHashed - this is a hashed index
02132 <LI> tableIndexOther - this is some other style of index
02133 </UL>
02134 <LI><B>ORDINAL_POSITION</B> short => column sequence number
02135 within index; zero when TYPE is tableIndexStatistic
02136 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
02137 tableIndexStatistic
02138 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
02139 "D" => descending, may be null if sort sequence is not supported;
02140 null when TYPE is tableIndexStatistic
02141 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
02142 this is the number of rows in the table; otherwise, it is the
02143 number of unique values in the index.
02144 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
02145 this is the number of pages used for the table, otherwise it
02146 is the number of pages used for the current index.
02147 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
02148 (may be null)
02149 </OL>
02150
02151 @param catalog a catalog name; "" retrieves those without a
02152 catalog; null means drop catalog name from the selection criteria
02153 @param schema a schema name; "" retrieves those without a schema
02154 @param table a table name
02155 @param unique when true, return only indices for unique values;
02156 when false, return indices regardless of whether unique or not
02157 @param approximate when true, result is allowed to reflect approximate
02158 or out of data values; when false, results are requested to be
02159 accurate
02160 @return <code>ResultSet</code> - each row is an index column description
02161 }
02162 function TZMySQLDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
02163 const Schema: string; const Table: string; Unique: Boolean;
02164 Approximate: Boolean): IZResultSet;
02165 var
02166 LCatalog: string;
02167 ColumnIndexes : Array[1..7] of integer;
02168 begin
02169 if Table = '' then
02170 raise Exception.Create(STableIsNotSpecified);
02171 Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);
02172
02173 if Catalog = '' then
02174 begin
02175 if Schema <> '' then
02176 LCatalog := Schema
02177 else
02178 LCatalog := FDatabase;
02179 end
02180 else
02181 LCatalog := Catalog;
02182
02183 with GetConnection.CreateStatement.ExecuteQuery(
02184 Format('SHOW INDEX FROM %s.%s',
02185 [GetIdentifierConvertor.Quote(LCatalog),
02186 GetIdentifierConvertor.Quote(Table)])) do
02187 begin
02188 ColumnIndexes[1] := FindColumn('Table');
02189 ColumnIndexes[2] := FindColumn('Non_unique');
02190 ColumnIndexes[3] := FindColumn('Key_name');
02191 ColumnIndexes[4] := FindColumn('Seq_in_index');
02192 ColumnIndexes[5] := FindColumn('Column_name');
02193 ColumnIndexes[6] := FindColumn('Collation');
02194 ColumnIndexes[7] := FindColumn('Cardinality');
02195 while Next do
02196 begin
02197 Result.MoveToInsertRow;
02198 Result.UpdateString(1, LCatalog);
02199 Result.UpdateNull(2);
02200 Result.UpdateString(3, GetString(ColumnIndexes[1]));
02201 if GetInt(ColumnIndexes[2]) = 0 then
02202 Result.UpdateString(4, 'true')
02203 else Result.UpdateString(4, 'false');
02204 Result.UpdateNull(5);
02205 Result.UpdateString(6, GetString(ColumnIndexes[3]));
02206 Result.UpdateInt(7, Ord(tiOther));
02207 Result.UpdateInt(8, GetInt(ColumnIndexes[4]));
02208 Result.UpdateString(9, GetString(ColumnIndexes[5]));
02209 Result.UpdateString(10, GetString(ColumnIndexes[6]));
02210 Result.UpdateString(11, GetString(ColumnIndexes[7]));
02211 Result.UpdateInt(12, 0);
02212 Result.UpdateNull(13);
02213 Result.InsertRow;
02214 end;
02215 Close;
02216 end;
02217 end;
02218
02219 {**
02220 Gets the MySQL version info.
02221 @param MajorVesion the major version of MySQL server.
02222 @param MinorVersion the minor version of MySQL server.
02223 }
02224 procedure TZMySQLDatabaseMetadata.GetVersion(var MajorVersion,
02225 MinorVersion: Integer);
02226 var
02227 VersionList: TStrings;
02228 Subversion : integer;
02229 begin
02230 DecodeSqlVersioning(GetConnection.GetHostVersion,MajorVersion,MinorVersion, Subversion);
02231 if (Majorversion < 4) or ((majorversion=4) and (Minorversion = 0)) then
02232 with GetConnection.CreateStatement.ExecuteQuery('SELECT VERSION()') do
02233 begin
02234 VersionList := SplitString(GetString(1), '.-');
02235 try
02236 if VersionList.Count >= 2 then
02237 begin
02238 MajorVersion := StrToIntDef(VersionList.Strings[0], 0);
02239 MinorVersion := StrToIntDef(VersionList.Strings[1], 0);
02240 end;
02241 finally
02242 VersionList.Free;
02243 end;
02244 Close;
02245 end;
02246 end;
02247
02248 {**
02249 Gets a description of a table's columns that are automatically
02250 updated when any value in a row is updated. They are
02251 unordered.
02252
02253 <P>Each column description has the following columns:
02254 <OL>
02255 <LI><B>SCOPE</B> short => is not used
02256 <LI><B>COLUMN_NAME</B> String => column name
02257 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02258 <LI><B>TYPE_NAME</B> String => Data source dependent type name
02259 <LI><B>COLUMN_SIZE</B> int => precision
02260 <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
02261 <LI><B>DECIMAL_DIGITS</B> short => scale
02262 <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
02263 like an Oracle ROWID
02264 <UL>
02265 <LI> versionColumnUnknown - may or may not be pseudo column
02266 <LI> versionColumnNotPseudo - is NOT a pseudo column
02267 <LI> versionColumnPseudo - is a pseudo column
02268 </UL>
02269 </OL>
02270
02271 @param catalog a catalog name; "" retrieves those without a
02272 catalog; null means drop catalog name from the selection criteria
02273 @param schema a schema name; "" retrieves those without a schema
02274 @param table a table name
02275 @return <code>ResultSet</code> - each row is a column description
02276 @exception SQLException if a database access error occurs
02277 }
02278 function TZMySQLDatabaseMetadata.UncachedGetVersionColumns(const Catalog, Schema,
02279 Table: string): IZResultSet;
02280 begin
02281 Result := ConstructVirtualResultSet(TableColVerColumnsDynArray);
02282
02283 Result.MoveToInsertRow;
02284 Result.UpdateNull(1);
02285 Result.UpdateString(2, 'ctid');
02286 // Result.UpdateInt(3, GetSQLType('tid')); //FIX IT
02287 Result.UpdateString(4, 'tid');
02288 Result.UpdateNull(5);
02289 Result.UpdateNull(6);
02290 Result.UpdateNull(7);
02291 Result.UpdateInt(4, Ord(vcPseudo));
02292 Result.InsertRow;
02293 end;
02294
02295 end.
02296
02297