Delphi Inspiration

Components and Applications

User Tools

Site Tools


wiki:sqlite3:index

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

wiki:sqlite3:index [2016/01/22 15:09] (current)
Line 1: Line 1:
 +====== DISQLite3: Wiki ======
  
 +{{page>:​products:​sqlite3:​header}}
 +{{page>:​wiki-header}}
 +
 +===== BLOB and TStream =====
 +
 +[[DISQLite3_Blobs_and_Streams|Here]] is a demo which shows how to write any TStream to a database BLOB and read it back. It also describes the advantages of the different methods. An up to date version of this demo is distributed with the DISQLite3 package.
 +
 +===== sqlite3_get_table() Example =====
 +
 +A simple example project which shows how to use sqlite3_get_table() is [[sqlite3_get_table|available here]]. An updated version of this demo is distributed with the DISQLite3 package!
 +
 +===== Compress Text and BLOBs =====
 +
 +Need to compress database text and BLOBs? You can easily add ZLib compression -- just like MySQL -- to DISQLite3 by registering the custom SQL functions provided in the [[DISQLite3ZLib.pas|DISQLite3ZLib.pas source code unit]]:
 +
 +  * **''​COMPRESS(text_or_blob_to_compress)''​** compresses text or BLOB. The compressed string or BLOB can be uncompressed with ''​UNCOMPRESS()''​. The argument'​s type is maintained, which can result in text with #0 characters.((DISQLite3 and [[products:​sqlitespy:​|SQLiteSpy]] have been well tested to handle in-text #0 characters, but some other SQLite managers are reported to have display problems :-(.)) If the argument can not be compressed, an error is returned. Any other argument type but text or BLOB is returned unchanged. Zero-length arguments are never compressed, the plain zlib compressed output is returned for all others.
 +
 +  * **''​UNCOMPRESS(compressed_text_or_blob)''​** uncompresses text or BLOB compresses by the ''​COMPRESS()''​ function. If the argument is not text or BLOB, it is returned unchanged. If the argument can not be uncompressed,​ an error is returned. Any other argument type but text or BLOB is returned unchanged.
 +
 +The ''​DISQLite3ZLib.pas''​ source code is distributed with the DISQLite3 package!
 +
 +==== Cast Compressed Results ====
 +
 +It is not required, but you can optionally CAST the ''​COMPRESS()''​ed text to a blob like this:
 +
 +<code sql>
 +SELECT CAST(COMPRESS('​Text to compress'​) AS BLOB);
 +</​code>​
 +
 +An ''​UNCOMPRESS()''​ed BLOB can of course also be CAST to text:
 +
 +<code sql>
 +SELECT CAST(UNCOMPRESS(Compressed_Blob) AS TEXT);
 +</​code>​
 +
 +==== Automatic Compression Using Views ====
 +
 +A simple view allows you to access compressed data as if it was uncompressed:​
 +
 +<code sql>
 +/* Table to store compressed data. */
 +CREATE TABLE IF NOT EXISTS Compressed (t TEXT);
 +
 +/* View to uncompress data on the fly. */
 +CREATE VIEW IF NOT EXISTS Uncompressed AS SELECT UNCOMPRESS(t) AS t FROM Compressed;
 +</​code>​
 +
 +If you also want to handle uncompressed inserts and updates via the view, then also create the following triggers:
 +
 +<code sql>
 +/* Trigger on view to insert compressed data */
 +CREATE TRIGGER Uncompressed_Insert INSTEAD OF INSERT ON Uncompressed
 +  BEGIN
 +    INSERT INTO Compressed (t) VALUES (COMPRESS(new.t));​
 +  END;
 +
 +/* Trigger on view to update compressed data */
 +CREATE TRIGGER Uncompressed_Update INSTEAD OF UPDATE ON Uncompressed
 +  BEGIN
 +    UPDATE Compressed SET t = COMPRESS(new.t) WHERE Compressed.RowID = old.RowID;
 +  END;
 +</​code>​
 +
 +===== SQL Transactions =====
 +
 +==== Transaction Handling in Delphi ====
 +
 +Delphi exceptions are an easy way to handle transactions. This is the basic structure:
 +
 +<code pascal>
 +{ Using the TDISQLite3Database component. }
 +uses
 +  DISQLite3Database;​
 +
 +procedure TDISQLite3Database_Transaction_Usage;​
 +var
 +  DB: TDISQLite3Database;​
 +begin
 +  { ... assume database is already opened ... }
 +  DB.StartTransaction;​
 +  try
 +    { ...a series of sql commands to update or insert ... }
 +    DB.Commit;
 +  except
 +    DB.Rollback;​
 +    raise;
 +  end;
 +end;
 +</​code>​
 +
 +<code pascal>
 +{ Directly using the DISQLite3 API. }
 +uses
 +  DISQLite3Api;​
 +
 +procedure DISQLite3_API_Transaction_Usage;​
 +var
 +  DB: sqlite3;
 +begin
 +  { ... assume database is already connected ... }
 +  sqlite3_exec_fast(DB,​ 'BEGIN TRANSACTION;'​);​
 +  try
 +    { ...a series of sql commands to update or insert ... }
 +    sqlite3_exec_fast(DB,​ '​COMMIT TRANSACTION;'​);​
 +  except
 +    sqlite3_exec_fast(DB,​ '​ROLLBACK TRANSACTION;'​);​
 +    raise;
 +  end;
 +end;
 +</​code>​
 +
 +Please take care that your code raises an exception wherever something can go wrong. ''​TDISQLite3Database''​ and ''​TDISQLite3Statement''​ automatically do so. Direct DISQLite3 API calls do not, but you can wrap them with ''​sqlite3_check()''​ to turn any error result into an exception.
 +
 +==== END [TRANSACTION] vs. COMMIT [TRANSACTION] ====
 +
 +There is no difference between the two -- END [TRANSACTION] is the same as COMMIT [TRANSACTION].
 +
 +===== TDISQLite3Database and sqlite3_create_collation() =====
 +
 +How can you use ''​sqlite3_create_collation()''​ with the ''​TDISQLite3Database''​ component? It is simple: Just pass the ''​TDISQLite3Database.Handle''​ property to ''​sqlite3_create_collation()'':​
 +
 +<code pascal>
 +sqlite3_create_collation(
 +  DB.Handle, // Handle to database.
 +  '​SYSTEM',​ // Name of the new collation.
 +  SQLITE_UTF16LE,​ // String encoding for function callback.
 +  nil, // User data.
 +  SQLite3_Compare_User_UTF16LE);​ // Function callback.
 +</​code>​
 +
 +''​sqlite3_create_collation()''​ requires DISQLite3 Pro, it is not available with DISQLite3 Personal.
 +
 +===== New component icons for DISQLite3 =====
 +
 +There are some new Delphi design-time icons available {{wiki:​sqlite3:​disqlite3_icon.zip|here}} which are based on the official "​feather"​ logo of SQLite. To use the new icon replace the file "​DISQLite3Reg.dcr"​ in the source folder of the DISQLite3 distribution with the new file in the archive and recompile the Delphi package.
 +
 +The GIMP source file that was used to generate the icons is included in the ZIP archive.
 +
 +===== Thanks for DISQLite3 =====
 +
 +  * Great and handy product with excellent support.
 +  * Without exaggeration I can say that DISQLite3 is one of the most useful Delphi DB component and useful introduction to SQLite DB.
 +  * Considering that there is DISQLite3 I seriously contemplate to use SQLite (with DISQLite3 of course) as embedded DB because of amazing performance. //Branko Burdian//
 +  * We are using DISQLite3 in several projects, and are very happy with it's performance and stability. Thanks! //Chris Kuske, Solid Oak Software, Inc.//
 +  * Software works like a dream! I have run your database under a service so that multiple applications can access the data though sockets, lightning fast. //Glen//
wiki/sqlite3/index.txt · Last modified: 2016/01/22 15:09 (external edit)