wiki:sqlite3:index
Delphi 12 Athens Updates Available!
To download, click your product: DIContainers, DIConverters, DICreole, DIFileFinder, DIGoogleReader, DIHtmlLabel, DIHtmlParser, DIMime, DIRegEx, DISQLite3, DITidy, DIUcl, DIUnicode, DIXml, YuBrotli, YuImage, YuNetSurf, YuOpenSSL, YuPcre2, YuPdf, YuStemmer, YuXmlSec, YuZip.
To download, click your product: DIContainers, DIConverters, DICreole, DIFileFinder, DIGoogleReader, DIHtmlLabel, DIHtmlParser, DIMime, DIRegEx, DISQLite3, DITidy, DIUcl, DIUnicode, DIXml, YuBrotli, YuImage, YuNetSurf, YuOpenSSL, YuPcre2, YuPdf, YuStemmer, YuXmlSec, YuZip.
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>: | ||
+ | {{page>: | ||
+ | |||
+ | ===== 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]]: | ||
+ | |||
+ | * **'' | ||
+ | |||
+ | * **'' | ||
+ | |||
+ | The '' | ||
+ | |||
+ | ==== Cast Compressed Results ==== | ||
+ | |||
+ | It is not required, but you can optionally CAST the '' | ||
+ | |||
+ | <code sql> | ||
+ | SELECT CAST(COMPRESS(' | ||
+ | </ | ||
+ | |||
+ | An '' | ||
+ | |||
+ | <code sql> | ||
+ | SELECT CAST(UNCOMPRESS(Compressed_Blob) AS TEXT); | ||
+ | </ | ||
+ | |||
+ | ==== 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; | ||
+ | </ | ||
+ | |||
+ | 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; | ||
+ | </ | ||
+ | |||
+ | ===== 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 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, | ||
+ | try | ||
+ | { ...a series of sql commands to update or insert ... } | ||
+ | sqlite3_exec_fast(DB, | ||
+ | except | ||
+ | sqlite3_exec_fast(DB, | ||
+ | raise; | ||
+ | end; | ||
+ | end; | ||
+ | </ | ||
+ | |||
+ | Please take care that your code raises an exception wherever something can go wrong. '' | ||
+ | |||
+ | ==== 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 '' | ||
+ | |||
+ | <code pascal> | ||
+ | sqlite3_create_collation( | ||
+ | DB.Handle, // Handle to database. | ||
+ | ' | ||
+ | SQLITE_UTF16LE, | ||
+ | nil, // User data. | ||
+ | SQLite3_Compare_User_UTF16LE); | ||
+ | </ | ||
+ | |||
+ | '' | ||
+ | |||
+ | ===== New component icons for DISQLite3 ===== | ||
+ | |||
+ | There are some new Delphi design-time icons available {{wiki: | ||
+ | |||
+ | 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