00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { SQLite Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZDbcSqLiteMetadata;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZDbcMetadata,
00065 ZCompatibility, ZDbcSQLiteUtils, ZDbcConnection;
00066
00067 type
00068
00069 {** Implements SQLite Database Metadata. }
00070 TZSQLiteDatabaseMetadata = class(TZAbstractDatabaseMetadata)
00071 private
00072 FDatabase: string;
00073 protected
00074 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00075 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00076
00077
00078 function UncachedGetTableTypes: IZResultSet; override;
00079 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00080 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00081
00082
00083
00084
00085 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00086 const Table: string): IZResultSet; override;
00087
00088
00089
00090
00091
00092
00093
00094 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00095 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00096
00097
00098
00099
00100
00101
00102
00103
00104
00105 function UncachedGetTypeInfo: IZResultSet; override;
00106
00107
00108 public
00109 constructor Create(Connection: TZAbstractConnection; Url: string;
00110 Info: TStrings);
00111 destructor Destroy; override;
00112
00113 function GetDatabaseProductName: string; override;
00114 function GetDatabaseProductVersion: string; override;
00115 function GetDriverName: string; override;
00116 function GetDriverMajorVersion: Integer; override;
00117 function GetDriverMinorVersion: Integer; override;
00118 function UsesLocalFilePerTable: Boolean; override;
00119 function SupportsMixedCaseIdentifiers: Boolean; override;
00120 function StoresUpperCaseIdentifiers: Boolean; override;
00121 function StoresLowerCaseIdentifiers: Boolean; override;
00122 function StoresMixedCaseIdentifiers: Boolean; override;
00123 function SupportsMixedCaseQuotedIdentifiers: Boolean; override;
00124 function StoresUpperCaseQuotedIdentifiers: Boolean; override;
00125 function StoresLowerCaseQuotedIdentifiers: Boolean; override;
00126 function StoresMixedCaseQuotedIdentifiers: Boolean; override;
00127 function GetSQLKeywords: string; override;
00128 function GetNumericFunctions: string; override;
00129 function GetStringFunctions: string; override;
00130 function GetSystemFunctions: string; override;
00131 function GetTimeDateFunctions: string; override;
00132 function GetSearchStringEscape: string; override;
00133 function GetExtraNameCharacters: string; override;
00134
00135 function SupportsExpressionsInOrderBy: Boolean; override;
00136 function SupportsOrderByUnrelated: Boolean; override;
00137 function SupportsGroupBy: Boolean; override;
00138 function SupportsGroupByUnrelated: Boolean; override;
00139 function SupportsGroupByBeyondSelect: Boolean; override;
00140 function SupportsIntegrityEnhancementFacility: Boolean; override;
00141 function GetSchemaTerm: string; override;
00142 function GetProcedureTerm: string; override;
00143 function GetCatalogTerm: string; override;
00144 function GetCatalogSeparator: string; override;
00145 function SupportsSchemasInDataManipulation: Boolean; override;
00146 function SupportsSchemasInProcedureCalls: Boolean; override;
00147 function SupportsSchemasInTableDefinitions: Boolean; override;
00148 function SupportsSchemasInIndexDefinitions: Boolean; override;
00149 function SupportsSchemasInPrivilegeDefinitions: Boolean; override;
00150 function SupportsCatalogsInDataManipulation: Boolean; override;
00151 function SupportsCatalogsInProcedureCalls: Boolean; override;
00152 function SupportsCatalogsInTableDefinitions: Boolean; override;
00153 function SupportsCatalogsInIndexDefinitions: Boolean; override;
00154 function SupportsCatalogsInPrivilegeDefinitions: Boolean; override;
00155 function SupportsPositionedDelete: Boolean; override;
00156 function SupportsPositionedUpdate: Boolean; override;
00157 function SupportsSelectForUpdate: Boolean; override;
00158 function SupportsStoredProcedures: Boolean; override;
00159 function SupportsSubqueriesInComparisons: Boolean; override;
00160 function SupportsSubqueriesInExists: Boolean; override;
00161 function SupportsSubqueriesInIns: Boolean; override;
00162 function SupportsSubqueriesInQuantifieds: Boolean; override;
00163 function SupportsCorrelatedSubqueries: Boolean; override;
00164 function SupportsUnion: Boolean; override;
00165 function SupportsUnionAll: Boolean; override;
00166 function SupportsOpenCursorsAcrossCommit: Boolean; override;
00167 function SupportsOpenCursorsAcrossRollback: Boolean; override;
00168 function SupportsOpenStatementsAcrossCommit: Boolean; override;
00169 function SupportsOpenStatementsAcrossRollback: Boolean; override;
00170
00171 function GetMaxBinaryLiteralLength: Integer; override;
00172 function GetMaxCharLiteralLength: Integer; override;
00173 function GetMaxColumnNameLength: Integer; override;
00174 function GetMaxColumnsInGroupBy: Integer; override;
00175 function GetMaxColumnsInIndex: Integer; override;
00176 function GetMaxColumnsInOrderBy: Integer; override;
00177 function GetMaxColumnsInSelect: Integer; override;
00178 function GetMaxColumnsInTable: Integer; override;
00179 function GetMaxConnections: Integer; override;
00180 function GetMaxCursorNameLength: Integer; override;
00181 function GetMaxIndexLength: Integer; override;
00182 function GetMaxSchemaNameLength: Integer; override;
00183 function GetMaxProcedureNameLength: Integer; override;
00184 function GetMaxCatalogNameLength: Integer; override;
00185 function GetMaxRowSize: Integer; override;
00186 function DoesMaxRowSizeIncludeBlobs: Boolean; override;
00187 function GetMaxStatementLength: Integer; override;
00188 function GetMaxStatements: Integer; override;
00189 function GetMaxTableNameLength: Integer; override;
00190 function GetMaxTablesInSelect: Integer; override;
00191 function GetMaxUserNameLength: Integer; override;
00192
00193 function GetDefaultTransactionIsolation: TZTransactIsolationLevel; override;
00194 function SupportsTransactions: Boolean; override;
00195 function SupportsTransactionIsolationLevel(Level: TZTransactIsolationLevel):
00196 Boolean; override;
00197 function SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
00198 override;
00199 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00200 function DataDefinitionCausesTransactionCommit: Boolean; override;
00201 function DataDefinitionIgnoredInTransactions: Boolean; override;
00202
00203 function SupportsResultSetType(_Type: TZResultSetType): Boolean; override;
00204 function SupportsResultSetConcurrency(_Type: TZResultSetType;
00205 Concurrency: TZResultSetConcurrency): Boolean; override;
00206 end;
00207
00208 implementation
00209
00210 uses
00211 ZDbcUtils;
00212
00213 { TZSQLiteDatabaseMetadata }
00214
00215 {**
00216 Constructs this object and assignes the main properties.
00217 @param Connection a database connection object.
00218 @param Url a database connection url string.
00219 @param Info an extra connection properties.
00220 }
00221 constructor TZSQLiteDatabaseMetadata.Create(
00222 Connection: TZAbstractConnection; Url: string; Info: TStrings);
00223 var
00224 TempInfo: TStrings;
00225 HostName, UserName, Password: string;
00226 Port: Integer;
00227 begin
00228 inherited Create(Connection, Url, Info);
00229
00230 TempInfo := TStringList.Create;
00231 try
00232 ResolveDatabaseUrl(Url, Info, HostName, Port, FDatabase,
00233 UserName, Password, TempInfo);
00234 finally
00235 TempInfo.Free;
00236 end;
00237 end;
00238
00239 {**
00240 Destroys this object and cleanups the memory.
00241 }
00242 destructor TZSQLiteDatabaseMetadata.Destroy;
00243 begin
00244 inherited Destroy;
00245 end;
00246
00247
00248
00249
00250 {**
00251 What's the name of this database product?
00252 @return database product name
00253 }
00254 function TZSQLiteDatabaseMetadata.GetDatabaseProductName: string;
00255 begin
00256 Result := 'SQLite';
00257 end;
00258
00259 {**
00260 What's the version of this database product?
00261 @return database version
00262 }
00263 function TZSQLiteDatabaseMetadata.GetDatabaseProductVersion: string;
00264 begin
00265 Result := '';
00266 end;
00267
00268 {**
00269 What's the name of this JDBC driver?
00270 @return JDBC driver name
00271 }
00272 function TZSQLiteDatabaseMetadata.GetDriverName: string;
00273 begin
00274 Result := 'Zeos Database Connectivity Driver for SQLite';
00275 end;
00276
00277 {**
00278 What's this JDBC driver's major version number?
00279 @return JDBC driver major version
00280 }
00281 function TZSQLiteDatabaseMetadata.GetDriverMajorVersion: Integer;
00282 begin
00283 Result := 1;
00284 end;
00285
00286 {**
00287 What's this JDBC driver's minor version number?
00288 @return JDBC driver minor version number
00289 }
00290 function TZSQLiteDatabaseMetadata.GetDriverMinorVersion: Integer;
00291 begin
00292 Result := 0;
00293 end;
00294
00295 {**
00296 Does the database use a file for each table?
00297 @return true if the database uses a local file for each table
00298 }
00299 function TZSQLiteDatabaseMetadata.UsesLocalFilePerTable: Boolean;
00300 begin
00301 Result := False;
00302 end;
00303
00304 {**
00305 Does the database treat mixed case unquoted SQL identifiers as
00306 case sensitive and as a result store them in mixed case?
00307 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return false.
00308 @return <code>true</code> if so; <code>false</code> otherwise
00309 }
00310 function TZSQLiteDatabaseMetadata.SupportsMixedCaseIdentifiers: Boolean;
00311 begin
00312 Result := True;
00313 end;
00314
00315 {**
00316 Does the database treat mixed case unquoted SQL identifiers as
00317 case insensitive and store them in upper case?
00318 @return <code>true</code> if so; <code>false</code> otherwise
00319 }
00320 function TZSQLiteDatabaseMetadata.StoresUpperCaseIdentifiers: Boolean;
00321 begin
00322 Result := False;
00323 end;
00324
00325 {**
00326 Does the database treat mixed case unquoted SQL identifiers as
00327 case insensitive and store them in lower case?
00328 @return <code>true</code> if so; <code>false</code> otherwise
00329 }
00330 function TZSQLiteDatabaseMetadata.StoresLowerCaseIdentifiers: Boolean;
00331 begin
00332 Result := False;
00333 end;
00334
00335 {**
00336 Does the database treat mixed case unquoted SQL identifiers as
00337 case insensitive and store them in mixed case?
00338 @return <code>true</code> if so; <code>false</code> otherwise
00339 }
00340 function TZSQLiteDatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00341 begin
00342 Result := True;
00343 end;
00344
00345 {**
00346 Does the database treat mixed case quoted SQL identifiers as
00347 case sensitive and as a result store them in mixed case?
00348 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return true.
00349 @return <code>true</code> if so; <code>false</code> otherwise
00350 }
00351 function TZSQLiteDatabaseMetadata.SupportsMixedCaseQuotedIdentifiers: Boolean;
00352 begin
00353 Result := True;
00354 end;
00355
00356 {**
00357 Does the database treat mixed case quoted SQL identifiers as
00358 case insensitive and store them in upper case?
00359 @return <code>true</code> if so; <code>false</code> otherwise
00360 }
00361 function TZSQLiteDatabaseMetadata.StoresUpperCaseQuotedIdentifiers: Boolean;
00362 begin
00363 Result := False;
00364 end;
00365
00366 {**
00367 Does the database treat mixed case quoted SQL identifiers as
00368 case insensitive and store them in lower case?
00369 @return <code>true</code> if so; <code>false</code> otherwise
00370 }
00371 function TZSQLiteDatabaseMetadata.StoresLowerCaseQuotedIdentifiers: Boolean;
00372 begin
00373 Result := False;
00374 end;
00375
00376 {**
00377 Does the database treat mixed case quoted SQL identifiers as
00378 case insensitive and store them in mixed case?
00379 @return <code>true</code> if so; <code>false</code> otherwise
00380 }
00381 function TZSQLiteDatabaseMetadata.StoresMixedCaseQuotedIdentifiers: Boolean;
00382 begin
00383 Result := True;
00384 end;
00385
00386 {**
00387 Gets a comma-separated list of all a database's SQL keywords
00388 that are NOT also SQL92 keywords.
00389 @return the list
00390 }
00391 function TZSQLiteDatabaseMetadata.GetSQLKeywords: string;
00392 begin
00393 Result := 'ALL,AND,AS,BETWEEN,BY,CASE,CHECK,COLLATE,COMMIT,CONSTRAINT,CREATE,'
00394 + 'DEFAULT,DEFERRABLE,DELETE,DISTINCT,DROP,ELSE,EXCEPT,FOREIGN,FROM,GLOB,'
00395 + 'GROUP,HAVING,IN,INDEX,INSERT,INTERSECT,INTO,IS,ISNULL,JOIN,LIKE,LIMIT,'
00396 + 'NOT,NOTNULL,NULL,ON,OR,ORDER,PRIMARY,REFERENCES,ROLLBACK,SELECT,SET,'
00397 + 'TABLE,THEN,TRANSACTION,UNION,UNIQUE,UPDATE,USING,VALUES,WHEN,WHERE,'
00398 + 'ABORT,AFTER,ASC,ATTACH,BEFORE,BEGIN,DEFERRED,CASCADE,CLUSTER,CONFLICT,'
00399 + 'COPY,CROSS,DATABASE,DELIMITERS,DESC,DETACH,EACH,END,EXPLAIN,FAIL,FOR,'
00400 + 'FULL,IGNORE,IMMEDIATE,INITIALLY,INNER,INSTEAD,KEY,LEFT,MATCH,NATURAL,'
00401 + 'OF,OFFSET,OUTER,PRAGMA,RAISE,REPLACE,RESTRICT,RIGHT,ROW,STATEMENT,'
00402 + 'TEMP,TEMPORARY,TRIGGER,VACUUM,VIEW';
00403 end;
00404
00405 {**
00406 Gets a comma-separated list of math functions. These are the
00407 X/Open CLI math function names used in the JDBC function escape
00408 clause.
00409 @return the list
00410 }
00411 function TZSQLiteDatabaseMetadata.GetNumericFunctions: string;
00412 begin
00413 Result := 'ABS,MAX,MIN,RANDOM,ROUND';
00414 end;
00415
00416 {**
00417 Gets a comma-separated list of string functions. These are the
00418 X/Open CLI string function names used in the JDBC function escape
00419 clause.
00420 @return the list
00421 }
00422 function TZSQLiteDatabaseMetadata.GetStringFunctions: string;
00423 begin
00424 Result := 'LENGTH,LIKE,LOWER,SOUNDEX,SUBSTRING,UPPER';
00425 end;
00426
00427 {**
00428 Gets a comma-separated list of system functions. These are the
00429 X/Open CLI system function names used in the JDBC function escape
00430 clause.
00431 @return the list
00432 }
00433 function TZSQLiteDatabaseMetadata.GetSystemFunctions: string;
00434 begin
00435 Result := 'LAST_INSERT_ROWID,SQLITE_VERSION,TYPEOF';
00436 end;
00437
00438 {**
00439 Gets a comma-separated list of time and date functions.
00440 @return the list
00441 }
00442 function TZSQLiteDatabaseMetadata.GetTimeDateFunctions: string;
00443 begin
00444 Result := '';
00445 end;
00446
00447 {**
00448 Gets the string that can be used to escape wildcard characters.
00449 This is the string that can be used to escape '_' or '%' in
00450 the string pattern style catalog search parameters.
00451
00452 <P>The '_' character represents any single character.
00453 <P>The '%' character represents any sequence of zero or
00454 more characters.
00455
00456 @return the string used to escape wildcard characters
00457 }
00458 function TZSQLiteDatabaseMetadata.GetSearchStringEscape: string;
00459 begin
00460 Result := '//';
00461 end;
00462
00463 {**
00464 Gets all the "extra" characters that can be used in unquoted
00465 identifier names (those beyond a-z, A-Z, 0-9 and _).
00466 @return the string containing the extra characters
00467 }
00468 function TZSQLiteDatabaseMetadata.GetExtraNameCharacters: string;
00469 begin
00470 Result := '';
00471 end;
00472
00473
00474
00475
00476 {**
00477 Are expressions in "ORDER BY" lists supported?
00478 @return <code>true</code> if so; <code>false</code> otherwise
00479 }
00480 function TZSQLiteDatabaseMetadata.SupportsExpressionsInOrderBy: Boolean;
00481 begin
00482 Result := False;
00483 end;
00484
00485 {**
00486 Can an "ORDER BY" clause use columns not in the SELECT statement?
00487 @return <code>true</code> if so; <code>false</code> otherwise
00488 }
00489 function TZSQLiteDatabaseMetadata.SupportsOrderByUnrelated: Boolean;
00490 begin
00491 Result := False;
00492 end;
00493
00494 {**
00495 Is some form of "GROUP BY" clause supported?
00496 @return <code>true</code> if so; <code>false</code> otherwise
00497 }
00498 function TZSQLiteDatabaseMetadata.SupportsGroupBy: Boolean;
00499 begin
00500 Result := True;
00501 end;
00502
00503 {**
00504 Can a "GROUP BY" clause use columns not in the SELECT?
00505 @return <code>true</code> if so; <code>false</code> otherwise
00506 }
00507 function TZSQLiteDatabaseMetadata.SupportsGroupByUnrelated: Boolean;
00508 begin
00509 Result := True;
00510 end;
00511
00512 {**
00513 Can a "GROUP BY" clause add columns not in the SELECT
00514 provided it specifies all the columns in the SELECT?
00515 @return <code>true</code> if so; <code>false</code> otherwise
00516 }
00517 function TZSQLiteDatabaseMetadata.SupportsGroupByBeyondSelect: Boolean;
00518 begin
00519 Result := True;
00520 end;
00521
00522 {**
00523 Is the SQL Integrity Enhancement Facility supported?
00524 @return <code>true</code> if so; <code>false</code> otherwise
00525 }
00526 function TZSQLiteDatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00527 begin
00528 Result := False;
00529 end;
00530
00531 {**
00532 What's the database vendor's preferred term for "schema"?
00533 @return the vendor term
00534 }
00535 function TZSQLiteDatabaseMetadata.GetSchemaTerm: string;
00536 begin
00537 Result := '';
00538 end;
00539
00540 {**
00541 What's the database vendor's preferred term for "procedure"?
00542 @return the vendor term
00543 }
00544 function TZSQLiteDatabaseMetadata.GetProcedureTerm: string;
00545 begin
00546 Result := '';
00547 end;
00548
00549 {**
00550 What's the database vendor's preferred term for "catalog"?
00551 @return the vendor term
00552 }
00553 function TZSQLiteDatabaseMetadata.GetCatalogTerm: string;
00554 begin
00555 Result := 'database';
00556 end;
00557
00558 {**
00559 What's the separator between catalog and table name?
00560 @return the separator string
00561 }
00562 function TZSQLiteDatabaseMetadata.GetCatalogSeparator: string;
00563 begin
00564 Result := '.';
00565 end;
00566
00567 {**
00568 Can a schema name be used in a data manipulation statement?
00569 @return <code>true</code> if so; <code>false</code> otherwise
00570 }
00571 function TZSQLiteDatabaseMetadata.SupportsSchemasInDataManipulation: Boolean;
00572 begin
00573 Result := True;
00574 end;
00575
00576 {**
00577 Can a schema name be used in a procedure call statement?
00578 @return <code>true</code> if so; <code>false</code> otherwise
00579 }
00580 function TZSQLiteDatabaseMetadata.SupportsSchemasInProcedureCalls: Boolean;
00581 begin
00582 Result := False;
00583 end;
00584
00585 {**
00586 Can a schema name be used in a table definition statement?
00587 @return <code>true</code> if so; <code>false</code> otherwise
00588 }
00589 function TZSQLiteDatabaseMetadata.SupportsSchemasInTableDefinitions: Boolean;
00590 begin
00591 Result := False;
00592 end;
00593
00594 {**
00595 Can a schema name be used in an index definition statement?
00596 @return <code>true</code> if so; <code>false</code> otherwise
00597 }
00598 function TZSQLiteDatabaseMetadata.SupportsSchemasInIndexDefinitions: Boolean;
00599 begin
00600 Result := False;
00601 end;
00602
00603 {**
00604 Can a schema name be used in a privilege definition statement?
00605 @return <code>true</code> if so; <code>false</code> otherwise
00606 }
00607 function TZSQLiteDatabaseMetadata.SupportsSchemasInPrivilegeDefinitions: Boolean;
00608 begin
00609 Result := False;
00610 end;
00611
00612 {**
00613 Can a catalog name be used in a data manipulation statement?
00614 @return <code>true</code> if so; <code>false</code> otherwise
00615 }
00616 function TZSQLiteDatabaseMetadata.SupportsCatalogsInDataManipulation: Boolean;
00617 begin
00618 Result := True;
00619 end;
00620
00621 {**
00622 Can a catalog name be used in a procedure call statement?
00623 @return <code>true</code> if so; <code>false</code> otherwise
00624 }
00625 function TZSQLiteDatabaseMetadata.SupportsCatalogsInProcedureCalls: Boolean;
00626 begin
00627 Result := False;
00628 end;
00629
00630 {**
00631 Can a catalog name be used in a table definition statement?
00632 @return <code>true</code> if so; <code>false</code> otherwise
00633 }
00634 function TZSQLiteDatabaseMetadata.SupportsCatalogsInTableDefinitions: Boolean;
00635 begin
00636 Result := False;
00637 end;
00638
00639 {**
00640 Can a catalog name be used in an index definition statement?
00641 @return <code>true</code> if so; <code>false</code> otherwise
00642 }
00643 function TZSQLiteDatabaseMetadata.SupportsCatalogsInIndexDefinitions: Boolean;
00644 begin
00645 Result := False;
00646 end;
00647
00648 {**
00649 Can a catalog name be used in a privilege definition statement?
00650 @return <code>true</code> if so; <code>false</code> otherwise
00651 }
00652 function TZSQLiteDatabaseMetadata.SupportsCatalogsInPrivilegeDefinitions: Boolean;
00653 begin
00654 Result := True;
00655 end;
00656
00657 {**
00658 Is positioned DELETE supported?
00659 @return <code>true</code> if so; <code>false</code> otherwise
00660 }
00661 function TZSQLiteDatabaseMetadata.SupportsPositionedDelete: Boolean;
00662 begin
00663 Result := False;
00664 end;
00665
00666 {**
00667 Is positioned UPDATE supported?
00668 @return <code>true</code> if so; <code>false</code> otherwise
00669 }
00670 function TZSQLiteDatabaseMetadata.SupportsPositionedUpdate: Boolean;
00671 begin
00672 Result := False;
00673 end;
00674
00675 {**
00676 Is SELECT for UPDATE supported?
00677 @return <code>true</code> if so; <code>false</code> otherwise
00678 }
00679 function TZSQLiteDatabaseMetadata.SupportsSelectForUpdate: Boolean;
00680 begin
00681 Result := False;
00682 end;
00683
00684 {**
00685 Are stored procedure calls using the stored procedure escape
00686 syntax supported?
00687 @return <code>true</code> if so; <code>false</code> otherwise
00688 }
00689 function TZSQLiteDatabaseMetadata.SupportsStoredProcedures: Boolean;
00690 begin
00691 Result := False;
00692 end;
00693
00694 {**
00695 Are subqueries in comparison expressions supported?
00696 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00697 @return <code>true</code> if so; <code>false</code> otherwise
00698 }
00699 function TZSQLiteDatabaseMetadata.SupportsSubqueriesInComparisons: Boolean;
00700 begin
00701 Result := True;
00702 end;
00703
00704 {**
00705 Are subqueries in 'exists' expressions supported?
00706 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00707 @return <code>true</code> if so; <code>false</code> otherwise
00708 }
00709 function TZSQLiteDatabaseMetadata.SupportsSubqueriesInExists: Boolean;
00710 begin
00711 Result := True;
00712 end;
00713
00714 {**
00715 Are subqueries in 'in' statements supported?
00716 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00717 @return <code>true</code> if so; <code>false</code> otherwise
00718 }
00719 function TZSQLiteDatabaseMetadata.SupportsSubqueriesInIns: Boolean;
00720 begin
00721 Result := True;
00722 end;
00723
00724 {**
00725 Are subqueries in quantified expressions supported?
00726 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00727 @return <code>true</code> if so; <code>false</code> otherwise
00728 }
00729 function TZSQLiteDatabaseMetadata.SupportsSubqueriesInQuantifieds: Boolean;
00730 begin
00731 Result := True;
00732 end;
00733
00734 {**
00735 Are correlated subqueries supported?
00736 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00737 @return <code>true</code> if so; <code>false</code> otherwise
00738 }
00739 function TZSQLiteDatabaseMetadata.SupportsCorrelatedSubqueries: Boolean;
00740 begin
00741 Result := True;
00742 end;
00743
00744 {**
00745 Is SQL UNION supported?
00746 @return <code>true</code> if so; <code>false</code> otherwise
00747 }
00748 function TZSQLiteDatabaseMetadata.SupportsUnion: Boolean;
00749 begin
00750 Result := True;
00751 end;
00752
00753 {**
00754 Is SQL UNION ALL supported?
00755 @return <code>true</code> if so; <code>false</code> otherwise
00756 }
00757 function TZSQLiteDatabaseMetadata.SupportsUnionAll: Boolean;
00758 begin
00759 Result := True;
00760 end;
00761
00762 {**
00763 Can cursors remain open across commits?
00764 @return <code>true</code> if cursors always remain open;
00765 <code>false</code> if they might not remain open
00766 }
00767 function TZSQLiteDatabaseMetadata.SupportsOpenCursorsAcrossCommit: Boolean;
00768 begin
00769 Result := False;
00770 end;
00771
00772 {**
00773 Can cursors remain open across rollbacks?
00774 @return <code>true</code> if cursors always remain open;
00775 <code>false</code> if they might not remain open
00776 }
00777 function TZSQLiteDatabaseMetadata.SupportsOpenCursorsAcrossRollback: Boolean;
00778 begin
00779 Result := False;
00780 end;
00781
00782 {**
00783 Can statements remain open across commits?
00784 @return <code>true</code> if statements always remain open;
00785 <code>false</code> if they might not remain open
00786 }
00787 function TZSQLiteDatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00788 begin
00789 Result := False;
00790 end;
00791
00792 {**
00793 Can statements remain open across rollbacks?
00794 @return <code>true</code> if statements always remain open;
00795 <code>false</code> if they might not remain open
00796 }
00797 function TZSQLiteDatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00798 begin
00799 Result := False;
00800 end;
00801
00802 //----------------------------------------------------------------------
00803 // The following group of methods exposes various limitations
00804 // based on the target database with the current driver.
00805 // Unless otherwise specified, a result of zero means there is no
00806 // limit, or the limit is not known.
00807
00808 {**
00809 How many hex characters can you have in an inline binary literal?
00810 @return max binary literal length in hex characters;
00811 a result of zero means that there is no limit or the limit is not known
00812 }
00813 function TZSQLiteDatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00814 begin
00815 Result := 0;
00816 end;
00817
00818 {**
00819 What's the max length for a character literal?
00820 @return max literal length;
00821 a result of zero means that there is no limit or the limit is not known
00822 }
00823 function TZSQLiteDatabaseMetadata.GetMaxCharLiteralLength: Integer;
00824 begin
00825 Result := 0;
00826 end;
00827
00828 {**
00829 What's the limit on column name length?
00830 @return max column name length;
00831 a result of zero means that there is no limit or the limit is not known
00832 }
00833 function TZSQLiteDatabaseMetadata.GetMaxColumnNameLength: Integer;
00834 begin
00835 Result := 0;
00836 end;
00837
00838 {**
00839 What's the maximum number of columns in a "GROUP BY" clause?
00840 @return max number of columns;
00841 a result of zero means that there is no limit or the limit is not known
00842 }
00843 function TZSQLiteDatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00844 begin
00845 Result := 0;
00846 end;
00847
00848 {**
00849 What's the maximum number of columns allowed in an index?
00850 @return max number of columns;
00851 a result of zero means that there is no limit or the limit is not known
00852 }
00853 function TZSQLiteDatabaseMetadata.GetMaxColumnsInIndex: Integer;
00854 begin
00855 Result := 0;
00856 end;
00857
00858 {**
00859 What's the maximum number of columns in an "ORDER BY" clause?
00860 @return max number of columns;
00861 a result of zero means that there is no limit or the limit is not known
00862 }
00863 function TZSQLiteDatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00864 begin
00865 Result := 0;
00866 end;
00867
00868 {**
00869 What's the maximum number of columns in a "SELECT" list?
00870 @return max number of columns;
00871 a result of zero means that there is no limit or the limit is not known
00872 }
00873 function TZSQLiteDatabaseMetadata.GetMaxColumnsInSelect: Integer;
00874 begin
00875 Result := 0;
00876 end;
00877
00878 {**
00879 What's the maximum number of columns in a table?
00880 @return max number of columns;
00881 a result of zero means that there is no limit or the limit is not known
00882 }
00883 function TZSQLiteDatabaseMetadata.GetMaxColumnsInTable: Integer;
00884 begin
00885 Result := 0;
00886 end;
00887
00888 {**
00889 How many active connections can we have at a time to this database?
00890 @return max number of active connections;
00891 a result of zero means that there is no limit or the limit is not known
00892 }
00893 function TZSQLiteDatabaseMetadata.GetMaxConnections: Integer;
00894 begin
00895 Result := 0;
00896 end;
00897
00898 {**
00899 What's the maximum cursor name length?
00900 @return max cursor name length in bytes;
00901 a result of zero means that there is no limit or the limit is not known
00902 }
00903 function TZSQLiteDatabaseMetadata.GetMaxCursorNameLength: Integer;
00904 begin
00905 Result := 0;
00906 end;
00907
00908 {**
00909 Retrieves the maximum number of bytes for an index, including all
00910 of the parts of the index.
00911 @return max index length in bytes, which includes the composite of all
00912 the constituent parts of the index;
00913 a result of zero means that there is no limit or the limit is not known
00914 }
00915 function TZSQLiteDatabaseMetadata.GetMaxIndexLength: Integer;
00916 begin
00917 Result := 0;
00918 end;
00919
00920 {**
00921 What's the maximum length allowed for a schema name?
00922 @return max name length in bytes;
00923 a result of zero means that there is no limit or the limit is not known
00924 }
00925 function TZSQLiteDatabaseMetadata.GetMaxSchemaNameLength: Integer;
00926 begin
00927 Result := 0;
00928 end;
00929
00930 {**
00931 What's the maximum length of a procedure name?
00932 @return max name length in bytes;
00933 a result of zero means that there is no limit or the limit is not known
00934 }
00935 function TZSQLiteDatabaseMetadata.GetMaxProcedureNameLength: Integer;
00936 begin
00937 Result := 0;
00938 end;
00939
00940 {**
00941 What's the maximum length of a catalog name?
00942 @return max name length in bytes;
00943 a result of zero means that there is no limit or the limit is not known
00944 }
00945 function TZSQLiteDatabaseMetadata.GetMaxCatalogNameLength: Integer;
00946 begin
00947 Result := 0;
00948 end;
00949
00950 {**
00951 What's the maximum length of a single row?
00952 @return max row size in bytes;
00953 a result of zero means that there is no limit or the limit is not known
00954 }
00955 function TZSQLiteDatabaseMetadata.GetMaxRowSize: Integer;
00956 begin
00957 Result := 0;
00958 end;
00959
00960 {**
00961 Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
00962 blobs?
00963 @return <code>true</code> if so; <code>false</code> otherwise
00964 }
00965 function TZSQLiteDatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
00966 begin
00967 Result := True;
00968 end;
00969
00970 {**
00971 What's the maximum length of an SQL statement?
00972 @return max length in bytes;
00973 a result of zero means that there is no limit or the limit is not known
00974 }
00975 function TZSQLiteDatabaseMetadata.GetMaxStatementLength: Integer;
00976 begin
00977 Result := 65535;
00978 end;
00979
00980 {**
00981 How many active statements can we have open at one time to this
00982 database?
00983 @return the maximum number of statements that can be open at one time;
00984 a result of zero means that there is no limit or the limit is not known
00985 }
00986 function TZSQLiteDatabaseMetadata.GetMaxStatements: Integer;
00987 begin
00988 Result := 0;
00989 end;
00990
00991 {**
00992 What's the maximum length of a table name?
00993 @return max name length in bytes;
00994 a result of zero means that there is no limit or the limit is not known
00995 }
00996 function TZSQLiteDatabaseMetadata.GetMaxTableNameLength: Integer;
00997 begin
00998 Result := 0;
00999 end;
01000
01001 {**
01002 What's the maximum number of tables in a SELECT statement?
01003 @return the maximum number of tables allowed in a SELECT statement;
01004 a result of zero means that there is no limit or the limit is not known
01005 }
01006 function TZSQLiteDatabaseMetadata.GetMaxTablesInSelect: Integer;
01007 begin
01008 Result := 0;
01009 end;
01010
01011 {**
01012 What's the maximum length of a user name?
01013 @return max user name length in bytes;
01014 a result of zero means that there is no limit or the limit is not known
01015 }
01016 function TZSQLiteDatabaseMetadata.GetMaxUserNameLength: Integer;
01017 begin
01018 Result := 0;
01019 end;
01020
01021 //----------------------------------------------------------------------
01022
01023 {**
01024 What's the database's default transaction isolation level? The
01025 values are defined in <code>java.sql.Connection</code>.
01026 @return the default isolation level
01027 @see Connection
01028 }
01029 function TZSQLiteDatabaseMetadata.GetDefaultTransactionIsolation:
01030 TZTransactIsolationLevel;
01031 begin
01032 Result := tiNone;
01033 end;
01034
01035 {**
01036 Are transactions supported? If not, invoking the method
01037 <code>commit</code> is a noop and the isolation level is TRANSACTION_NONE.
01038 @return <code>true</code> if transactions are supported; <code>false</code> otherwise
01039 }
01040 function TZSQLiteDatabaseMetadata.SupportsTransactions: Boolean;
01041 begin
01042 Result := True;
01043 end;
01044
01045 {**
01046 Does this database support the given transaction isolation level?
01047 @param level the values are defined in <code>java.sql.Connection</code>
01048 @return <code>true</code> if so; <code>false</code> otherwise
01049 @see Connection
01050 }
01051 function TZSQLiteDatabaseMetadata.SupportsTransactionIsolationLevel(
01052 Level: TZTransactIsolationLevel): Boolean;
01053 begin
01054 Result := True;
01055 end;
01056
01057 {**
01058 Are both data definition and data manipulation statements
01059 within a transaction supported?
01060 @return <code>true</code> if so; <code>false</code> otherwise
01061 }
01062 function TZSQLiteDatabaseMetadata.
01063 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
01064 begin
01065 Result := True;
01066 end;
01067
01068 {**
01069 Are only data manipulation statements within a transaction
01070 supported?
01071 @return <code>true</code> if so; <code>false</code> otherwise
01072 }
01073 function TZSQLiteDatabaseMetadata.
01074 SupportsDataManipulationTransactionsOnly: Boolean;
01075 begin
01076 Result := True;
01077 end;
01078
01079 {**
01080 Does a data definition statement within a transaction force the
01081 transaction to commit?
01082 @return <code>true</code> if so; <code>false</code> otherwise
01083 }
01084 function TZSQLiteDatabaseMetadata.DataDefinitionCausesTransactionCommit: Boolean;
01085 begin
01086 Result := True;
01087 end;
01088
01089 {**
01090 Is a data definition statement within a transaction ignored?
01091 @return <code>true</code> if so; <code>false</code> otherwise
01092 }
01093 function TZSQLiteDatabaseMetadata.DataDefinitionIgnoredInTransactions: Boolean;
01094 begin
01095 Result := False;
01096 end;
01097
01098 {**
01099 Gets a description of tables available in a catalog.
01100
01101 <P>Only table descriptions matching the catalog, schema, table
01102 name and type criteria are returned. They are ordered by
01103 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
01104
01105 <P>Each table description has the following columns:
01106 <OL>
01107 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01108 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01109 <LI><B>TABLE_NAME</B> String => table name
01110 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01111 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01112 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01113 <LI><B>REMARKS</B> String => explanatory comment on the table
01114 </OL>
01115
01116 <P><B>Note:</B> Some databases may not return information for
01117 all tables.
01118
01119 @param catalog a catalog name; "" retrieves those without a
01120 catalog; null means drop catalog name from the selection criteria
01121 @param schemaPattern a schema name pattern; "" retrieves those
01122 without a schema
01123 @param tableNamePattern a table name pattern
01124 @param types a list of table types to include; null returns all types
01125 @return <code>ResultSet</code> - each row is a table description
01126 @see #getSearchStringEscape
01127 }
01128 function TZSQLiteDatabaseMetadata.UncachedGetTables(const Catalog: string;
01129 const SchemaPattern: string; const TableNamePattern: string;
01130 const Types: TStringDynArray): IZResultSet;
01131 var
01132 WhereClause, SQL: string;
01133
01134 function IncludedType(TypeName: string): Boolean;
01135 var I: Integer;
01136 begin
01137 Result := False;
01138 for I := Low(Types) to High(Types) do
01139 Result := Result or (UpperCase(Types[I]) = TypeName);
01140 Result := Result or (Length(Types) = 0);
01141 end;
01142
01143 begin
01144 WhereClause := '';
01145 if IncludedType('TABLE') then
01146 WhereClause := 'TYPE=''table''';
01147 if IncludedType('VIEW') then
01148 begin
01149 if WhereClause <> '' then
01150 WhereClause := '(' + WhereClause + ' OR TYPE=''view'')'
01151 else WhereClause := 'TYPE=''view''';
01152 end;
01153
01154 SQL := 'SELECT ''' + Catalog + ''' AS TABLE_CAT, NULL AS TABLE_SCHEM,'
01155 + ' TBL_NAME AS TABLE_NAME, UPPER(TYPE) AS TABLE_TYPE, NULL AS REMARKS'
01156 + ' FROM ';
01157 if Catalog <> '' then
01158 SQL := SQL + Catalog + '.';
01159 SQL := SQL + 'SQLITE_MASTER WHERE ' + WhereClause
01160 + ' AND TBL_NAME LIKE ''' + ToLikeString(TableNamePattern) + '''';
01161
01162 Result := CopyToVirtualResultSet(
01163 GetConnection.CreateStatement.ExecuteQuery(SQL),
01164 ConstructVirtualResultSet(TableColumnsDynArray));
01165 end;
01166
01167 {**
01168 Gets the table types available in this database. The results
01169 are ordered by table type.
01170
01171 <P>The table type is:
01172 <OL>
01173 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01174 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01175 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01176 </OL>
01177
01178 @return <code>ResultSet</code> - each row has a single String column that is a
01179 table type
01180 }
01181 function TZSQLiteDatabaseMetadata.UncachedGetTableTypes: IZResultSet;
01182 const
01183 TableTypeCount = 2;
01184 Types: array [1..TableTypeCount] of string = ('TABLE', 'VIEW');
01185 var
01186 I: Integer;
01187 begin
01188 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
01189 for I := 1 to TableTypeCount do
01190 begin
01191 Result.MoveToInsertRow;
01192 Result.UpdateString(1, Types[I]);
01193 Result.InsertRow;
01194 end;
01195 end;
01196
01197 {**
01198 Gets a description of table columns available in
01199 the specified catalog.
01200
01201 <P>Only column descriptions matching the catalog, schema, table
01202 and column name criteria are returned. They are ordered by
01203 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
01204
01205 <P>Each column description has the following columns:
01206 <OL>
01207 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01208 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01209 <LI><B>TABLE_NAME</B> String => table name
01210 <LI><B>COLUMN_NAME</B> String => column name
01211 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01212 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
01213 for a UDT the type name is fully qualified
01214 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
01215 types this is the maximum number of characters, for numeric or
01216 decimal types this is precision.
01217 <LI><B>BUFFER_LENGTH</B> is not used.
01218 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
01219 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
01220 <LI><B>NULLABLE</B> int => is NULL allowed?
01221 <UL>
01222 <LI> columnNoNulls - might not allow NULL values
01223 <LI> columnNullable - definitely allows NULL values
01224 <LI> columnNullableUnknown - nullability unknown
01225 </UL>
01226 <LI><B>REMARKS</B> String => comment describing column (may be null)
01227 <LI><B>COLUMN_DEF</B> String => default value (may be null)
01228 <LI><B>SQL_DATA_TYPE</B> int => unused
01229 <LI><B>SQL_DATETIME_SUB</B> int => unused
01230 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
01231 maximum number of bytes in the column
01232 <LI><B>ORDINAL_POSITION</B> int => index of column in table
01233 (starting at 1)
01234 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
01235 does not allow NULL values; "YES" means the column might
01236 allow NULL values. An empty string means nobody knows.
01237 </OL>
01238
01239 @param catalog a catalog name; "" retrieves those without a
01240 catalog; null means drop catalog name from the selection criteria
01241 @param schemaPattern a schema name pattern; "" retrieves those
01242 without a schema
01243 @param tableNamePattern a table name pattern
01244 @param columnNamePattern a column name pattern
01245 @return <code>ResultSet</code> - each row is a column description
01246 @see #getSearchStringEscape
01247 }
01248 function TZSQLiteDatabaseMetadata.UncachedGetColumns(const Catalog: string;
01249 const SchemaPattern: string; const TableNamePattern: string;
01250 const ColumnNamePattern: string): IZResultSet;
01251 var
01252 Temp: string;
01253 Precision, Decimals: Integer;
01254 Temp_scheme: string;
01255 begin
01256 Result := ConstructVirtualResultSet(TableColColumnsDynArray);
01257 if SchemaPattern = '' then
01258 Temp_scheme := ''
01259 else
01260 Temp_scheme := SchemaPattern +'.';
01261
01262 with GetConnection.CreateStatement.ExecuteQuery(
01263 Format('PRAGMA %s table_info(''%s'')', [Temp_scheme, TableNamePattern])) do
01264 begin
01265 while Next do
01266 begin
01267 Result.MoveToInsertRow;
01268 if SchemaPattern <> '' then
01269 Result.UpdateString(1, SchemaPattern)
01270 else Result.UpdateNull(1);
01271 Result.UpdateNull(2);
01272 Result.UpdateString(3, TableNamePattern);
01273 Result.UpdateString(4, GetString(2));
01274 Result.UpdateInt(5, Ord(ConvertSQLiteTypeToSQLType(
01275 GetString(3), Precision, Decimals)));
01276
01277 { Defines a table name. }
01278 Temp := UpperCase(GetString(3));
01279 if Pos('(', Temp) > 0 then
01280 Temp := Copy(Temp, 1, Pos('(', Temp) - 1);
01281 Result.UpdateString(6, Temp);
01282
01283 Result.UpdateInt(7, Precision);
01284 Result.UpdateNull(8);
01285 Result.UpdateInt(9, Decimals);
01286 Result.UpdateInt(10, 0);
01287
01288 if GetInt(4) <> 0 then
01289 begin
01290 Result.UpdateInt(11, Ord(ntNoNulls));
01291 Result.UpdateString(18, 'NO');
01292 end
01293 else
01294 begin
01295 Result.UpdateInt(11, Ord(ntNullable));
01296 Result.UpdateString(18, 'YES');
01297 end;
01298
01299 Result.UpdateNull(12);
01300 if Trim(GetString(5)) <> '' then
01301 Result.UpdateString(13, GetString(5))
01302 else Result.UpdateNull(13);
01303 Result.UpdateNull(14);
01304 Result.UpdateNull(15);
01305 Result.UpdateNull(16);
01306 Result.UpdateInt(17, GetInt(1) + 1);
01307
01308 Result.UpdateBooleanByName('AUTO_INCREMENT',
01309 (GetInt(6) = 1) and (Temp = 'INTEGER'));
01310 Result.UpdateBooleanByName('CASE_SENSITIVE', False);
01311 Result.UpdateBooleanByName('SEARCHABLE', True);
01312 Result.UpdateBooleanByName('WRITABLE', True);
01313 Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
01314 Result.UpdateBooleanByName('READONLY', False);
01315
01316 Result.InsertRow;
01317 end;
01318 Close;
01319 end;
01320 end;
01321
01322 {**
01323 Gets a description of a table's primary key columns. They
01324 are ordered by COLUMN_NAME.
01325
01326 <P>Each primary key column description has the following columns:
01327 <OL>
01328 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01329 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01330 <LI><B>TABLE_NAME</B> String => table name
01331 <LI><B>COLUMN_NAME</B> String => column name
01332 <LI><B>KEY_SEQ</B> short => sequence number within primary key
01333 <LI><B>PK_NAME</B> String => primary key name (may be null)
01334 </OL>
01335
01336 @param catalog a catalog name; "" retrieves those without a
01337 catalog; null means drop catalog name from the selection criteria
01338 @param schema a schema name; "" retrieves those
01339 without a schema
01340 @param table a table name
01341 @return <code>ResultSet</code> - each row is a primary key column description
01342 @exception SQLException if a database access error occurs
01343 }
01344 function TZSQLiteDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
01345 const Schema: string; const Table: string): IZResultSet;
01346 var
01347 Index: Integer;
01348 Temp_scheme: string;
01349 begin
01350 Result := ConstructVirtualResultSet(PrimaryKeyColumnsDynArray);
01351
01352 if Schema = '' then
01353 Temp_scheme := '' // OR 'main.'
01354 else
01355 Temp_scheme := Schema +'.';
01356
01357 with GetConnection.CreateStatement.ExecuteQuery(
01358 Format('PRAGMA %s table_info(''%s'')', [Temp_scheme,Table])) do
01359 begin
01360 Index := 1;
01361 while Next do
01362 begin
01363 if GetInt(6) = 0 then
01364 Continue;
01365
01366 Result.MoveToInsertRow;
01367 if Schema <> '' then
01368 Result.UpdateString(1, Schema)
01369 else Result.UpdateNull(1);
01370 Result.UpdateNull(2);
01371 Result.UpdateString(3, Table);
01372 Result.UpdateString(4, GetString(2));
01373 Result.UpdateInt(5, Index);
01374 Result.UpdateNull(6);
01375
01376 Inc(Index);
01377
01378 Result.InsertRow;
01379 end;
01380 Close;
01381 end;
01382 end;
01383
01384 {**
01385 Gets a description of all the standard SQL types supported by
01386 this database. They are ordered by DATA_TYPE and then by how
01387 closely the data type maps to the corresponding JDBC SQL type.
01388
01389 <P>Each type description has the following columns:
01390 <OL>
01391 <LI><B>TYPE_NAME</B> String => Type name
01392 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
01393 <LI><B>PRECISION</B> int => maximum precision
01394 <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
01395 (may be null)
01396 <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
01397 (may be null)
01398 <LI><B>CREATE_PARAMS</B> String => parameters used in creating
01399 the type (may be null)
01400 <LI><B>NULLABLE</B> short => can you use NULL for this type?
01401 <UL>
01402 <LI> typeNoNulls - does not allow NULL values
01403 <LI> typeNullable - allows NULL values
01404 <LI> typeNullableUnknown - nullability unknown
01405 </UL>
01406 <LI><B>CASE_SENSITIVE</B> Boolean=> is it case sensitive?
01407 <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
01408 <UL>
01409 <LI> typePredNone - No support
01410 <LI> typePredChar - Only supported with WHERE .. LIKE
01411 <LI> typePredBasic - Supported except for WHERE .. LIKE
01412 <LI> typeSearchable - Supported for all WHERE ..
01413 </UL>
01414 <LI><B>UNSIGNED_ATTRIBUTE</B> Boolean => is it unsigned?
01415 <LI><B>FIXED_PREC_SCALE</B> Boolean => can it be a money value?
01416 <LI><B>AUTO_INCREMENT</B> Boolean => can it be used for an
01417 auto-increment value?
01418 <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
01419 (may be null)
01420 <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
01421 <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
01422 <LI><B>SQL_DATA_TYPE</B> int => unused
01423 <LI><B>SQL_DATETIME_SUB</B> int => unused
01424 <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
01425 </OL>
01426
01427 @return <code>ResultSet</code> - each row is an SQL type description
01428 }
01429 function TZSQLiteDatabaseMetadata.UncachedGetTypeInfo: IZResultSet;
01430 const
01431 MaxTypeCount = 22;
01432 TypeNames: array[1..MaxTypeCount] of string = (
01433 'BOOLEAN', 'TINYINT', 'SMALLINT', 'MEDIUMINT', 'INTEGER',
01434 'BIGINT', 'REAL', 'FLOAT', 'NUMERIC', 'DECIMAL', 'NUMBER',
01435 'DOUBLE', 'CHAR', 'VARCHAR', 'BINARY', 'VARBINARY', 'DATE',
01436 'TIME', 'DATETIME', 'TIMESTAMP', 'BLOB', 'TEXT');
01437 TypeCodes: array[1..MaxTypeCount] of TZSQLType = (
01438 stBoolean, stByte, stShort, stInteger, stInteger, stLong,
01439 stFloat, stFloat, stDouble, stDouble, stDouble, stDouble,
01440 stString, stString, stBytes, stBytes, stDate, stTime, stTimestamp,
01441 stTimestamp, stBinaryStream, stAsciiStream);
01442 TypePrecision: array[1..MaxTypeCount] of Integer = (
01443 -1, 2, 4, 9, 9, 16, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
01444 -1, -1, -1, -1, -1);
01445 var
01446 I: Integer;
01447 begin
01448 for I := 1 to MaxTypeCount do
01449 begin
01450 Result.MoveToInsertRow;
01451
01452 Result.UpdateString(1, TypeNames[I]);
01453 Result.UpdateInt(2, Ord(TypeCodes[I]));
01454 if TypePrecision[I] >= 0 then
01455 Result.UpdateInt(3, TypePrecision[I])
01456 else Result.UpdateNull(3);
01457 if TypeCodes[I] in [stString, stBytes, stDate, stTime,
01458 stTimeStamp, stBinaryStream, stAsciiStream] then
01459 begin
01460 Result.UpdateString(4, '''');
01461 Result.UpdateString(5, '''');
01462 end
01463 else
01464 begin
01465 Result.UpdateNull(4);
01466 Result.UpdateNull(5);
01467 end;
01468 Result.UpdateNull(6);
01469 Result.UpdateInt(7, Ord(ntNullable));
01470 Result.UpdateBoolean(8, False);
01471 Result.UpdateBoolean(9, False);
01472 Result.UpdateBoolean(11, False);
01473 Result.UpdateBoolean(12, False);
01474 Result.UpdateBoolean(12, TypeNames[I] = 'INTEGER');
01475 Result.UpdateNull(13);
01476 Result.UpdateNull(14);
01477 Result.UpdateNull(15);
01478 Result.UpdateNull(16);
01479 Result.UpdateNull(17);
01480 Result.UpdateInt(18, 10);
01481
01482 Result.InsertRow;
01483 end;
01484 end;
01485
01486 {**
01487 Gets a description of a table's indices and statistics. They are
01488 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
01489
01490 <P>Each index column description has the following columns:
01491 <OL>
01492 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01493 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01494 <LI><B>TABLE_NAME</B> String => table name
01495 <LI><B>NON_UNIQUE</B> Boolean => Can index values be non-unique?
01496 false when TYPE is tableIndexStatistic
01497 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
01498 null when TYPE is tableIndexStatistic
01499 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
01500 tableIndexStatistic
01501 <LI><B>TYPE</B> short => index type:
01502 <UL>
01503 <LI> tableIndexStatistic - this identifies table statistics that are
01504 returned in conjuction with a table's index descriptions
01505 <LI> tableIndexClustered - this is a clustered index
01506 <LI> tableIndexHashed - this is a hashed index
01507 <LI> tableIndexOther - this is some other style of index
01508 </UL>
01509 <LI><B>ORDINAL_POSITION</B> short => column sequence number
01510 within index; zero when TYPE is tableIndexStatistic
01511 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
01512 tableIndexStatistic
01513 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
01514 "D" => descending, may be null if sort sequence is not supported;
01515 null when TYPE is tableIndexStatistic
01516 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
01517 this is the number of rows in the table; otherwise, it is the
01518 number of unique values in the index.
01519 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
01520 this is the number of pages used for the table, otherwise it
01521 is the number of pages used for the current index.
01522 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
01523 (may be null)
01524 </OL>
01525
01526 @param catalog a catalog name; "" retrieves those without a
01527 catalog; null means drop catalog name from the selection criteria
01528 @param schema a schema name; "" retrieves those without a schema
01529 @param table a table name
01530 @param unique when true, return only indices for unique values;
01531 when false, return indices regardless of whether unique or not
01532 @param approximate when true, result is allowed to reflect approximate
01533 or out of data values; when false, results are requested to be
01534 accurate
01535 @return <code>ResultSet</code> - each row is an index column description
01536 }
01537 function TZSQLiteDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
01538 const Schema: string; const Table: string; Unique: Boolean;
01539 Approximate: Boolean): IZResultSet;
01540 var
01541 ResultSet: IZResultSet;
01542 Temp_scheme: string;
01543 begin
01544 Result := ConstructVirtualResultSet(IndexInfoColumnsDynArray);
01545
01546 if Schema = '' then
01547 Temp_scheme := '' // OR 'main.'
01548 else
01549 Temp_scheme := Schema +'.';
01550
01551 with GetConnection.CreateStatement.ExecuteQuery(
01552 Format('PRAGMA %s index_list(''%s'')', [Temp_scheme, Table])) do
01553 begin
01554 while Next do
01555 begin
01556 if (Pos(' autoindex ', GetString(2)) = 0)
01557 and ((Unique = False) or (GetInt(3) = 0)) then
01558 begin
01559 ResultSet := GetConnection.CreateStatement.ExecuteQuery(
01560 Format('PRAGMA %s index_info(''%s'')', [Temp_scheme,GetString(2)]));
01561 while ResultSet.Next do
01562 begin
01563 Result.MoveToInsertRow;
01564
01565 if Schema <> '' then
01566 Result.UpdateString(1, Schema)
01567 else Result.UpdateNull(1);
01568 Result.UpdateNull(2);
01569 Result.UpdateString(3, Table);
01570 Result.UpdateBoolean(4, GetInt(3) = 0);
01571 Result.UpdateNull(5);
01572 Result.UpdateString(6, GetString(2));
01573 Result.UpdateNull(7);
01574 Result.UpdateInt(8, ResultSet.GetInt(1) + 1);
01575 Result.UpdateString(9, ResultSet.GetString(3));
01576 Result.UpdateString(10, 'A');
01577 Result.UpdateInt(11, 0);
01578 Result.UpdateInt(12, 0);
01579 Result.UpdateNull(13);
01580
01581 Result.InsertRow;
01582 end;
01583 ResultSet.Close;
01584 end;
01585 end;
01586 Close;
01587 end;
01588 end;
01589
01590 {**
01591 Does the database support the given result set type?
01592 @param type defined in <code>java.sql.ResultSet</code>
01593 @return <code>true</code> if so; <code>false</code> otherwise
01594 }
01595 function TZSQLiteDatabaseMetadata.SupportsResultSetType(
01596 _Type: TZResultSetType): Boolean;
01597 begin
01598 Result := _Type = rtForwardOnly;
01599 end;
01600
01601 {**
01602 Does the database support the concurrency type in combination
01603 with the given result set type?
01604
01605 @param type defined in <code>java.sql.ResultSet</code>
01606 @param concurrency type defined in <code>java.sql.ResultSet</code>
01607 @return <code>true</code> if so; <code>false</code> otherwise
01608 }
01609 function TZSQLiteDatabaseMetadata.SupportsResultSetConcurrency(
01610 _Type: TZResultSetType; Concurrency: TZResultSetConcurrency): Boolean;
01611 begin
01612 Result := (_Type = rtForwardOnly) and (Concurrency = rcReadOnly);
01613 end;
01614
01615 end.