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 ZDbcSqLite;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 ZCompatibility, Classes, SysUtils, ZDbcIntfs, ZDbcConnection,
00065 ZPlainSqLiteDriver, ZDbcLogging, ZTokenizer, ZGenericSqlAnalyser;
00066
00067 type
00068
00069 {** Implements SQLite Database Driver. }
00070 TZSQLiteDriver = class(TZAbstractDriver)
00071 private
00072 FSQLite28PlainDriver: IZSQLitePlainDriver;
00073 FSQLite3PlainDriver: IZSQLitePlainDriver;
00074 protected
00075 function GetPlainDriver(const Url: string): IZSQLitePlainDriver;
00076 public
00077 constructor Create;
00078 function Connect(const Url: string; Info: TStrings): IZConnection; override;
00079
00080 function GetSupportedProtocols: TStringDynArray; override;
00081 function GetMajorVersion: Integer; override;
00082 function GetMinorVersion: Integer; override;
00083
00084 function GetTokenizer: IZTokenizer; override;
00085 function GetStatementAnalyser: IZStatementAnalyser; override;
00086 end;
00087
00088 {** Represents a SQLite specific connection interface. }
00089 IZSQLiteConnection = interface (IZConnection)
00090 ['{A4B797A9-7CF7-4DE9-A5BB-693DD32D07D2}']
00091
00092 function GetPlainDriver: IZSQLitePlainDriver;
00093 function GetConnectionHandle: Psqlite;
00094 end;
00095
00096 {** Implements SQLite Database Connection. }
00097 TZSQLiteConnection = class(TZAbstractConnection, IZSQLiteConnection)
00098 private
00099 FCatalog: string;
00100 FPlainDriver: IZSQLitePlainDriver;
00101 FHandle: Psqlite;
00102
00103 protected
00104 procedure StartTransactionSupport;
00105
00106 public
00107 constructor Create(Driver: IZDriver; const Url: string;
00108 PlainDriver: IZSQLitePlainDriver; const HostName: string; Port: Integer;
00109 const Database: string; const User: string; const Password: string; Info: TStrings);
00110 destructor Destroy; override;
00111
00112 function CreateRegularStatement(Info: TStrings): IZStatement; override;
00113 function CreatePreparedStatement(const SQL: string; Info: TStrings):
00114 IZPreparedStatement; override;
00115
00116 procedure Commit; override;
00117 procedure Rollback; override;
00118
00119 procedure Open; override;
00120 procedure Close; override;
00121
00122 procedure SetCatalog(const Catalog: string); override;
00123 function GetCatalog: string; override;
00124
00125 procedure SetTransactionIsolation(Level: TZTransactIsolationLevel); override;
00126
00127 function GetPlainDriver: IZSQLitePlainDriver;
00128 function GetConnectionHandle: Psqlite;
00129
00130 function ReKey(const Key: string): Integer;
00131 function Key(const Key: string): Integer;
00132 end;
00133
00134 var
00135 {** The common driver manager object. }
00136 SQLiteDriver: IZDriver;
00137
00138 implementation
00139
00140 uses
00141 ZSysUtils, ZDbcUtils, ZDbcSqLiteStatement, ZSqLiteToken,
00142 ZDbcSqLiteUtils, ZDbcSqLiteMetadata, ZSqLiteAnalyser;
00143
00144 { TZSQLiteDriver }
00145
00146 {**
00147 Constructs this object with default properties.
00148 }
00149 constructor TZSQLiteDriver.Create;
00150 begin
00151 FSQLite28PlainDriver := TZSQLite28PlainDriver.Create;
00152 FSQLite3PlainDriver := TZSQLite3PlainDriver.Create;
00153 end;
00154
00155 {**
00156 Attempts to make a database connection to the given URL.
00157 The driver should return "null" if it realizes it is the wrong kind
00158 of driver to connect to the given URL. This will be common, as when
00159 the JDBC driver manager is asked to connect to a given URL it passes
00160 the URL to each loaded driver in turn.
00161
00162 <P>The driver should raise a SQLException if it is the right
00163 driver to connect to the given URL, but has trouble connecting to
00164 the database.
00165
00166 <P>The java.util.Properties argument can be used to passed arbitrary
00167 string tag/value pairs as connection arguments.
00168 Normally at least "user" and "password" properties should be
00169 included in the Properties.
00170
00171 @param url the URL of the database to which to connect
00172 @param info a list of arbitrary string tag/value pairs as
00173 connection arguments. Normally at least a "user" and
00174 "password" property should be included.
00175 @return a <code>Connection</code> object that represents a
00176 connection to the URL
00177 }
00178 function TZSQLiteDriver.Connect(const Url: string; Info: TStrings): IZConnection;
00179 var
00180 TempInfo: TStrings;
00181 HostName, Database, UserName, Password: string;
00182 Port: Integer;
00183 PlainDriver: IZSQLitePlainDriver;
00184 begin
00185 TempInfo := TStringList.Create;
00186 try
00187 PlainDriver := GetPlainDriver(Url);
00188 ResolveDatabaseUrl(Url, Info, HostName, Port, Database,
00189 UserName, Password, TempInfo);
00190 Result := TZSQLiteConnection.Create(Self, Url, PlainDriver, HostName, Port,
00191 Database, UserName, Password, TempInfo);
00192 finally
00193 TempInfo.Free;
00194 end;
00195 end;
00196
00197 {**
00198 Gets the driver's major version number. Initially this should be 1.
00199 @return this driver's major version number
00200 }
00201 function TZSQLiteDriver.GetMajorVersion: Integer;
00202 begin
00203 Result := 1;
00204 end;
00205
00206 {**
00207 Gets the driver's minor version number. Initially this should be 0.
00208 @return this driver's minor version number
00209 }
00210 function TZSQLiteDriver.GetMinorVersion: Integer;
00211 begin
00212 Result := 0;
00213 end;
00214
00215 {**
00216 Gets a SQL syntax tokenizer.
00217 @returns a SQL syntax tokenizer object.
00218 }
00219 function TZSQLiteDriver.GetTokenizer: IZTokenizer;
00220 begin
00221 if Tokenizer = nil then
00222 Tokenizer := TZSQLiteTokenizer.Create;
00223 Result := Tokenizer;
00224 end;
00225
00226 {**
00227 Creates a statement analyser object.
00228 @returns a statement analyser object.
00229 }
00230 function TZSQLiteDriver.GetStatementAnalyser: IZStatementAnalyser;
00231 begin
00232 if Analyser = nil then
00233 Analyser := TZSQLiteStatementAnalyser.Create;
00234 Result := Analyser;
00235 end;
00236
00237 {**
00238 Get a name of the supported subprotocol.
00239 For example: mysql, oracle8 or postgresql72
00240 }
00241 function TZSQLiteDriver.GetSupportedProtocols: TStringDynArray;
00242 begin
00243 SetLength(Result, 3);
00244 Result[0] := 'sqlite';
00245 Result[1] := FSQLite28PlainDriver.GetProtocol;
00246 Result[2] := FSQLite3PlainDriver.GetProtocol;
00247 end;
00248
00249 {**
00250 Gets plain driver for selected protocol.
00251 @param Url a database connection URL.
00252 @return a selected protocol.
00253 }
00254 function TZSQLiteDriver.GetPlainDriver(const Url: string): IZSQLitePlainDriver;
00255 var
00256 Protocol: string;
00257 begin
00258 Protocol := ResolveConnectionProtocol(Url, GetSupportedProtocols);
00259 if Protocol = FSQLite28PlainDriver.GetProtocol then
00260 Result := FSQLite28PlainDriver
00261 else if Protocol = FSQLite3PlainDriver.GetProtocol then
00262 Result := FSQLite3PlainDriver
00263 else Result := FSQLite28PlainDriver;
00264 Result.Initialize;
00265 end;
00266
00267 { TZSQLiteConnection }
00268
00269 {**
00270 Constructs this object and assignes the main properties.
00271 @param Driver the parent ZDBC driver.
00272 @param PlainDriver a SQLite plain driver.
00273 @param HostName a name of the host.
00274 @param Port a port number (0 for default port).
00275 @param Database a name pof the database.
00276 @param User a user name.
00277 @param Password a user password.
00278 @param Info a string list with extra connection parameters.
00279 }
00280 constructor TZSQLiteConnection.Create(Driver: IZDriver; const Url: string;
00281 PlainDriver: IZSQLitePlainDriver; const HostName: string; Port: Integer;
00282 const Database, User, Password: string; Info: TStrings);
00283 begin
00284 inherited Create(Driver, Url, HostName, Port, Database, User, Password, Info,
00285 TZSQLiteDatabaseMetadata.Create(Self, Url, Info));
00286
00287 { Sets a default properties }
00288 FPlainDriver := PlainDriver;
00289 AutoCommit := True;
00290 TransactIsolationLevel := tiNone;
00291
00292 Open;
00293 end;
00294
00295 {**
00296 Destroys this object and cleanups the memory.
00297 }
00298 destructor TZSQLiteConnection.Destroy;
00299 begin
00300 inherited Destroy;
00301 end;
00302
00303 {**
00304 Set encryption key for a database
00305 @param Key the key used to encrypt your database.
00306 @return error code from SQLite Key function.
00307 }
00308 function TZSQLiteConnection.Key(const Key: string):Integer;
00309 var
00310 ErrorCode: Integer;
00311 begin
00312 ErrorCode := FPlainDriver.Key(FHandle, PChar(Key), StrLen(PChar(Key)));
00313 Result := ErrorCode;
00314 end;
00315
00316 {**
00317 Reencrypt a database with a new key. The old/current key needs to be
00318 set before calling this function.
00319 @param Key the new key used to encrypt your database.
00320 @return error code from SQLite ReKey function.
00321 }
00322 function TZSQLiteConnection.ReKey(const Key: string):Integer;
00323 var
00324 ErrorCode: Integer;
00325 begin
00326 ErrorCode := FPlainDriver.ReKey(FHandle, PChar(Key), StrLen(PChar(Key)));
00327 Result := ErrorCode;
00328 end;
00329
00330 {**
00331 Opens a connection to database server with specified parameters.
00332 }
00333 procedure TZSQLiteConnection.Open;
00334 var
00335 ErrorCode: Integer;
00336 ErrorMessage: PChar;
00337 LogMessage: string;
00338 SQL: string;
00339 begin
00340 if not Closed then Exit;
00341 ErrorMessage := '';
00342
00343 LogMessage := Format('CONNECT TO "%s" AS USER "%s"', [Database, User]);
00344
00345 FHandle := FPlainDriver.Open(PChar(Database), 0, ErrorMessage);
00346 if FHandle = nil then
00347 begin
00348 CheckSQLiteError(FPlainDriver, SQLITE_ERROR, ErrorMessage,
00349 lcConnect, LogMessage);
00350 end;
00351 DriverManager.LogMessage(lcConnect, FPlainDriver.GetProtocol, LogMessage);
00352
00353 { Turn on encryption if requested }
00354 if StrToBoolEx(Info.Values['encrypted']) then
00355 begin
00356 ErrorCode := FPlainDriver.Key(FHandle, PChar(Password), StrLen(PChar(Password)));
00357 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcConnect, 'SQLite.Key');
00358 end;
00359
00360 try
00361 SQL := 'PRAGMA show_datatypes = ON';
00362 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL),
00363 nil, nil, ErrorMessage);
00364 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00365 {
00366 SQL := 'PRAGMA empty_result_callbacks = ON';
00367 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL),
00368 nil, nil, ErrorMessage);
00369 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00370 }
00371 StartTransactionSupport;
00372 except
00373 FPlainDriver.Close(FHandle);
00374 FHandle := nil;
00375 raise;
00376 end;
00377
00378 inherited Open;
00379 end;
00380
00381 {**
00382 Creates a <code>Statement</code> object for sending
00383 SQL statements to the database.
00384 SQL statements without parameters are normally
00385 executed using Statement objects. If the same SQL statement
00386 is executed many times, it is more efficient to use a
00387 <code>PreparedStatement</code> object.
00388 <P>
00389 Result sets created using the returned <code>Statement</code>
00390 object will by default have forward-only type and read-only concurrency.
00391
00392 @param Info a statement parameters.
00393 @return a new Statement object
00394 }
00395 function TZSQLiteConnection.CreateRegularStatement(Info: TStrings):
00396 IZStatement;
00397 begin
00398 if IsClosed then Open;
00399 Result := TZSQLiteStatement.Create(FPlainDriver, Self, Info, FHandle);
00400 end;
00401
00402 {**
00403 Creates a <code>PreparedStatement</code> object for sending
00404 parameterized SQL statements to the database.
00405
00406 A SQL statement with or without IN parameters can be
00407 pre-compiled and stored in a PreparedStatement object. This
00408 object can then be used to efficiently execute this statement
00409 multiple times.
00410
00411 <P><B>Note:</B> This method is optimized for handling
00412 parametric SQL statements that benefit from precompilation. If
00413 the driver supports precompilation,
00414 the method <code>prepareStatement</code> will send
00415 the statement to the database for precompilation. Some drivers
00416 may not support precompilation. In this case, the statement may
00417 not be sent to the database until the <code>PreparedStatement</code> is
00418 executed. This has no direct effect on users; however, it does
00419 affect which method throws certain SQLExceptions.
00420
00421 Result sets created using the returned PreparedStatement will have
00422 forward-only type and read-only concurrency, by default.
00423
00424 @param sql a SQL statement that may contain one or more '?' IN
00425 parameter placeholders
00426 @param Info a statement parameters.
00427 @return a new PreparedStatement object containing the
00428 pre-compiled statement
00429 }
00430 function TZSQLiteConnection.CreatePreparedStatement(const SQL: string;
00431 Info: TStrings): IZPreparedStatement;
00432 begin
00433 if IsClosed then Open;
00434 Result := TZSQLitePreparedStatement.Create(FPlainDriver, Self, SQL,
00435 Info, FHandle);
00436 end;
00437
00438 {**
00439 Starts a transaction support.
00440 }
00441 procedure TZSQLiteConnection.StartTransactionSupport;
00442 var
00443 ErrorCode: Integer;
00444 ErrorMessage: PChar;
00445 SQL: PChar;
00446 begin
00447 if TransactIsolationLevel <> tiNone then
00448 begin
00449 ErrorMessage := '';
00450 SQL := 'BEGIN TRANSACTION';
00451 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
00452 ErrorMessage);
00453 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00454 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00455 end;
00456 end;
00457
00458 {**
00459 Makes all changes made since the previous
00460 commit/rollback permanent and releases any database locks
00461 currently held by the Connection. This method should be
00462 used only when auto-commit mode has been disabled.
00463 @see #setAutoCommit
00464 }
00465 procedure TZSQLiteConnection.Commit;
00466 var
00467 ErrorCode: Integer;
00468 ErrorMessage: PChar;
00469 SQL: PChar;
00470 begin
00471 if (TransactIsolationLevel <> tiNone) and not Closed then
00472 begin
00473 ErrorMessage := '';
00474 SQL := 'COMMIT TRANSACTION';
00475 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
00476 ErrorMessage);
00477 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00478 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00479
00480 StartTransactionSupport;
00481 end;
00482 end;
00483
00484 {**
00485 Drops all changes made since the previous
00486 commit/rollback and releases any database locks currently held
00487 by this Connection. This method should be used only when auto-
00488 commit has been disabled.
00489 @see #setAutoCommit
00490 }
00491 procedure TZSQLiteConnection.Rollback;
00492 var
00493 ErrorCode: Integer;
00494 ErrorMessage: PChar;
00495 SQL: PChar;
00496 begin
00497 if (TransactIsolationLevel <> tiNone) and not Closed then
00498 begin
00499 ErrorMessage := '';
00500 SQL := 'ROLLBACK TRANSACTION';
00501 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
00502 ErrorMessage);
00503 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00504 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00505
00506 StartTransactionSupport;
00507 end;
00508 end;
00509
00510 {**
00511 Releases a Connection's database and JDBC resources
00512 immediately instead of waiting for
00513 them to be automatically released.
00514
00515 <P><B>Note:</B> A Connection is automatically closed when it is
00516 garbage collected. Certain fatal errors also result in a closed
00517 Connection.
00518 }
00519 procedure TZSQLiteConnection.Close;
00520 var
00521 LogMessage: string;
00522 begin
00523 if not Closed then
00524 begin
00525 FPlainDriver.Close(FHandle);
00526 FHandle := nil;
00527 LogMessage := Format('DISCONNECT FROM "%s"', [Database]);
00528 DriverManager.LogMessage(lcDisconnect, FPlainDriver.GetProtocol, LogMessage);
00529 end;
00530 inherited Close;
00531 end;
00532
00533 {**
00534 Gets a selected catalog name.
00535 @return a selected catalog name.
00536 }
00537 function TZSQLiteConnection.GetCatalog: string;
00538 begin
00539 Result := FCatalog;
00540 end;
00541
00542 {**
00543 Sets a new selected catalog name.
00544 @param Catalog a selected catalog name.
00545 }
00546 procedure TZSQLiteConnection.SetCatalog(const Catalog: string);
00547 begin
00548 FCatalog := Catalog;
00549 end;
00550
00551 {**
00552 Sets a new transact isolation level.
00553 @param Level a new transact isolation level.
00554 }
00555 procedure TZSQLiteConnection.SetTransactionIsolation(
00556 Level: TZTransactIsolationLevel);
00557 var
00558 ErrorCode: Integer;
00559 ErrorMessage: PChar;
00560 SQL: PChar;
00561 begin
00562 if (TransactIsolationLevel <> tiNone) and not Closed then
00563 begin
00564 ErrorMessage := '';
00565 SQL := 'ROLLBACK TRANSACTION';
00566 ErrorCode := FPlainDriver.Execute(FHandle, PChar(SQL), nil, nil,
00567 ErrorMessage);
00568 CheckSQLiteError(FPlainDriver, ErrorCode, ErrorMessage, lcExecute, SQL);
00569 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00570 end;
00571
00572 inherited SetTransactionIsolation(Level);
00573
00574 if not Closed then
00575 StartTransactionSupport;
00576 end;
00577
00578 {**
00579 Gets a reference to SQLite connection handle.
00580 @return a reference to SQLite connection handle.
00581 }
00582 function TZSQLiteConnection.GetConnectionHandle: Psqlite;
00583 begin
00584 Result := FHandle;
00585 end;
00586
00587 {**
00588 Gets a SQLite plain driver interface.
00589 @return a SQLite plain driver interface.
00590 }
00591 function TZSQLiteConnection.GetPlainDriver: IZSQLitePlainDriver;
00592 begin
00593 Result := FPlainDriver;
00594 end;
00595
00596 initialization
00597 SQLiteDriver := TZSQLiteDriver.Create;
00598 DriverManager.RegisterDriver(SQLiteDriver);
00599 finalization
00600 if DriverManager <> nil then
00601 DriverManager.DeregisterDriver(SQLiteDriver);
00602 SQLiteDriver := nil;
00603 end.
00604