Yunqa • The Delphi Inspiration

Delphi Components and Applications

User Tools

Site Tools


wiki:sqlite3:index
no way to compare when less than two revisions

Differences

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


Last revision
wiki:sqlite3:index [2016/01/22 15:09] – created - external edit 127.0.0.1
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: 2023/01/16 15:29 by admin