SQLite: what changed

Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		## SQLite and FPC/Lazarus support
2	2
3	3		SQLite is an embedded (non-server) single-user database that can be used in FPC and Lazarus applications. Various drivers can be used to access SQLite from FPC/Lazarus programs. All drivers do need the SQLite library/dll in the executable directory (which can be your project directory or e.g. (projectdir)/lib/architecture/ depending on your Lazarus project settings) (and distributed with your executable) in order to work.
4	4
5	5		This might also need to be included in you Lazarus IDE directory. See [1](https://forum.lazarus.freepascal.org/index.php?topic=46118.msg327940#msg327940) And read on especially for TSQLite3Dataset and TSQLiteDataset See below
6	6
7	7		Most Linux distributions have sqlite3 (eg libsqlite3.so.0) installed by default but Debian based distros, at least, also require the matching Dev package to get libsqlite3.so. Both should be install via system package manager and marked as a dependency rather than being distributed with your application.
8	8
9	9		Win64: please see warning [[Windows Programming Tips\|here]] on not using certain FPC/Lazarus Win64 versions.
10	10
11	11		### Direct access to SQLite
12	12
13	13		You can use an easy way to connect SQLite with Lazarus. Components you are named LiteDAC. SQLite Data Access Components (LiteDAC) is a library of components that provides native connectivity to SQLite from Lazarus (and Free Pascal) on Windows, macOS, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. LiteDAC is designed for programmers to develop truly cross-platform desktop and mobile SQLite database applications with no need to deploy any additional libraries. You can download a trial version of this commercial product at [Lazarus components](https://www.devart.com/litedac/download.html).
14	14
15	15		### Built-in SQLDB
16	16
17	17		FPC/Lazarus offers the built-in SQLDB components that include support for SQLite databases ([[TSQLite3Connection]]) from the [[SQLdb tab]] of the [[Component Palette]], which allow you to e.g. create GUIs with database components such as [[TDBGrid\|TDBGrids]]. The advantage of using SQLDB is that it is fairly easy to change to a different database such as Firebird or PostgreSQL without changing your program too much. See below for details.
18	18
19	19		#### Spatialite support
20	20
21	21		Spatialite are GIS extensions to SQLite which you can use from within SQLDB. See [[Spatialite]].
22	22
23	23		#### Support for SQLite encryption
24	24
25	25		In recent FPC versions (beginning with FPC 3.0.0, implemented March 2012), SQLDB included support for some extended versions of SQLite3 which encrypt the SQLite database file using the AES algorithm. Use the Password property to set the encryption key.
26	26
27	27		Examples:
28	28
29	29		- [SQLCipher](http://sqlcipher.net/): open source, e.g. Windows binaries not for free (you have to compile them yourself)
30	30		- [System.Data.SQLite](http://system.data.sqlite.org/): open source, Windows (32, 64, CE) binaries available, download e.g one of the Precompiled Binaries and rename SQLite.Interop.dll to sqlite3.dll (if you're using the Statically Linked ones, presumably you need to rename System.Data.SQLite.DLL to sqlite3.dll)
31	31		- [wxSQLite3](http://wxcode.sourceforge.net/docs/wxsqlite3/): open source, some binaries for Linux available (ex: <https://launchpad.net/ubuntu/oneiric/+package/libwxsqlite3-2.8-0>)
32	32
33	33		Depending on what you use it might also be necessary to enable the encryption separately [as written here](https://www.sqlite.org/see/doc/trunk/www/readme.wiki) at the point "5.0 C interface", where you need to invoke sqlite3_activate_see() with your product key before accessing an encrypted database.
34	34
35	35		#### sqlite3backup
36	36
37	37		sqlite3backup is a unit provided with FPC (not in Lazarus but can be used programmatically) that provides backup/restore functionality for SQLite3. It uses SQLDB's sqlite3conn.
38	38
39	39		### Zeos
40	40
41	41		[Zeos](https://sourceforge.net/projects/zeoslib/)
42	42
43	43		### SQLitePass
44	44
45	45		[SQLitePass](http://source.online.free.fr/) components. Last code update in 2010. Last forum activity in 2011.
46	46
47	47		### TSQLite3Dataset and TSQLiteDataset
48	48
49	49		There are also separate TSQLiteDataset (unit sqliteds) and TSQLite3Dataset (unit sqlite3ds) packages; see below for a description on how to use them. Visit the [sqlite4fpc homepage](http://sqlite4fpc.yolasite.com/) to find the API reference and more tutorials.
50	50
51	51		TSqliteDataset and TSqlite3Dataset are [[TDataSet]] descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. For new projects, you would presumably use TSQlite3Dataset as SQLite 3.x is the current version.
52	52
53	53		Below is a list of the principal advantages and disadvantages compared to other FPC/Lazarus SQLite drivers/access methods:
54	54
55	55		Advantages:
56	56
57	57		- Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
58	58
59	59		Disadvantages:
60	60
61	61		- Changing to other databases is more difficult than if you use the SQLDB or Zeos components
62	62
63	63		> Note: Given the above, many users will use SQLDB or Zeos due to the advantages unless they need lower-level access to the SQLite library
64	64
65	65		## Using the SQLdb components with SQLite
66	66
67	67		These instructions are focused on SQLDB (the TSQLite3Connection) specifics for SQLite. For a general overview, have a look at [[SqlDBHowto]] which has some useful information about the SQLdb components.
68	68
69	69		See [[SQLdb Tutorial1]] for a tutorial on creating a GUI database-enabled program that is written for SQLite/SQLDB (as well as for Firebird/SQLDB, PostgreSQL/SQLDB, basically any RDBMS SQLDB supports).
70	70
71	71		We will use a combination of three components from the Lazarus SQLdb tab: TSQLite3Connection, TSQLTransaction and TSQLQuery. The TSQLQuery acts as our TDataset; in the simplest case it just represents one of our tables. For the sake of simplicity: make sure you already have an existing SQLite database file and don't need to create a new one now. TSQLite3Connection can be found in the sqlite3conn unit, if you want to declare it yourself or are working in FreePascal.
72	72
73	73		The three components are connected with each other as usual: In the TSQLQuery set the properties Database and Transaction, in the TSQLTransaction set the property Database. There is not much to do in the Transaction and Connection components, most of the interesting things will be done in the TSQLQuery. Configure the components as follows:
74	74
75	75		TSQLite3Connection:
76	76
77	77		- DatabaseName: Set this property to the file name (absolute path!) of your SQLite file. Unfortunately, you cannot simply use a relative path that works unchanged at designtime and at runtime *is this still true? Can't you just copy the db file in a post-build shell script or symlink it?*. You should make sure that at application start the correct path to the file is always set programmatically, no matter what it contained at designtime.
78	78
79	79		Note: To set the full library path (if you place your sqlite dll/so/dylib in a place where the OS won't find it, like the application directory on Linux/OSX), you can set the SQLiteLibraryName property (BEFORE any connection is established e.g. in the OnCreate event of the main form), like this:
80	80
81	81		``` pascal
82	82		SQLiteLibraryName:='./sqlite3.so';
83	83		```
84	84
85	85		TSQLQuery:
86	86
87	87		- SQL: Set it to some simple select query on one of your tables. For example, if you have a table 'foo' and want this dataset to represent this table then just use the following:
88	88		``` SQL
89	89		SELECT * FROM foo
90	90		```
91	91
92	92		- Active: Set this to True from within the IDE to test whether it is all set up correctly. This will also automatically activate the transaction and the connection objects. If you receive an error then either the DatabaseName of the connection is not correct or the SQL query is wrong. Later, when we are done adding the fields (see below) set them all to inactive again, we don't want the IDE to lock the SQLite database (single user!) when testing the application.
93	93
94	94		- Probably not necessary for proper operation - will need to be checked (June 2012) Now we can add Fields to our TSQLQuery. While the components are still set to active do a right click and "edit fields...". Click the "+" button and add fields. It will list all fields your SQL query returned. Add every field you will need, you can also add lookup fields here; in this case just make sure you have already defined all needed fields in the other datasets before you start adding lookup fields that refer to them. If your table has many columns and you don't need them all you can just leave them out, you can also make your SQL a bit more specific.
95	95
96	96		- In your code you need to call SQLQuery.ApplyUpdates and SQLTransaction.Commit, TSQLQuery.AfterPost and AfterInsert events are a good place for this when using it with data aware controls but of course you can also postpone these calls to a later time. If you don't call them, the database will not be updated.
97	97
98	98		- "Database is locked": The IDE might still be locking the database (SQLite is a single user database), you probably forgot to set the components to inactive and disconnected again after you were done defining all the fields of your TSQLQuery objects. Use the Form's OnCreate event to set the path and activate the objects at runtime only. Most of the things you set in the TSQLQuery from within the IDE don't require (and some don't even allow) them to be active at design time, the only exception is defining the fields where it wants to read the table design, so inactive at design time should be the normal state.
99	99
100	100		- Your tables should all have a primary key and you must make sure that the corresponding field has pfInKey and nothing else in its PoviderFlags (these flags control how and where the field is used when automatically constructing the update and delete queries).
101	101
102	102		- If you are using lookup fields
103	103		- make sure the ProviderFlags for the lookup field is completely empty so it won't attempt to use its name in an update query. The lookup field itself is not a data field, it only acts on the value of another field, the corresponding key field, and only this key field will later be used in the update queries. You can set the key field to hidden because usually you don't want to see it in your DBGrid but it needs to be defined.
104	104		- LookupCache must be set to True. At the time of this writing for some reason the lookup field will not display anything otherwise (but still work) and strangely the exact opposite is the case when working with the TSQLite3Dataset or other TXXXDataset components, here it must be set to False. I'm not yet sure whether this is intended behavior or a bug.
105	105
106	106		- Usually with simple tables you won't need to set any of the InsertSQL, UpdateSQL and DeleteSQL properties, just leave them empty. If you have the ProviderFlags of all your fields set correctly it should be able to create the needed SQL on the fly. For more details on InsertSQL, UpdateSQL and DeleteSQL, see [[Working With TSQLQuery\|Working_With_TSQLQuery#InsertSQL.2C_UpdateSQL_and_DeleteSQL_-_Basic_use_of_parameters]].
107	107
108	108		After the above is all set up correctly, you should now be able to use the TSQLQuery like any other TDataset, either by manipulating its data programmatically or by placing a TDatasouce on the Form, connecting it to the TSQLQuery and then using data contols like TDBGrid etc.
109	109
110	110		### Creating a Database
111	111
112	112		The [TSQLite3Connection.CreateDB](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.createdb.html) method inherited from the parent class actually does nothing; to create a database if no file exists yet, you simply have to write table data as in the following example:
113	113
114	114		(Code extracted from sqlite_encryption_pragma example that ships with Lazarus 1.3 onwards)
115	115
116	116		``` pascal
117	117		var
118	118		newFile : Boolean;
119	119		begin
120	120
121	121		SQLite3Connection1.Close; // Ensure the connection is closed when we start
122	122
123	123		try
124	124		// Since we're making this database for the first time,
125	125		// check whether the file already exists
126	126		newFile := not FileExists(SQLite3Connection1.DatabaseName);
127	127
128	128		if newFile then
129	129		begin
130	130		// Create the database and the tables
131	131		try
132	132		SQLite3Connection1.Open;
133	133		SQLTransaction1.Active := true;
134	134
135	135		// Here we're setting up a table named "DATA" in the new database
136	136		SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
137	137		' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
138	138		' "Current_Time" DateTime NOT NULL,'+
139	139		' "User_Name" Char(128) NOT NULL,'+
140	140		' "Info" Char(128) NOT NULL);');
141	141
142	142		// Creating an index based upon id in the DATA Table
143	143		SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');
144	144
145	145		SQLTransaction1.Commit;
146	146
147	147		ShowMessage('Succesfully created database.');
148	148		except
149	149		ShowMessage('Unable to Create new Database');
150	150		end;
151	151		end;
152	152		except
153	153		ShowMessage('Unable to check if database file exists');
154	154		end;
155	155		end;
156	156		```
157	157
158	158		### Creating user defined collations
159	159
160	160		``` pascal
161	161		// utf8 case-sensitive compare callback function
162	162		function UTF8xCompare(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
163	163		var S1, S2: AnsiString;
164	164		begin
165	165		SetString(S1, data1, len1);
166	166		SetString(S2, data2, len2);
167	167		Result := UnicodeCompareStr(UTF8Decode(S1), UTF8Decode(S2));
168	168		end;
169	169
170	170		// utf8 case-insensitive compare callback function
171	171		function UTF8xCompare_CI(user: pointer; len1: longint; data1: pointer; len2: longint; data2: pointer): longint; cdecl;
172	172		var S1, S2: AnsiString;
173	173		begin
174	174		SetString(S1, data1, len1);
175	175		SetString(S2, data2, len2);
176	176		Result := UnicodeCompareText(UTF8Decode(S1), UTF8Decode(S2));
177	177		end;
178	178
179	179		// register collation using SQLite3 API (requires sqlite3dyn unit):
180	180		sqlite3_create_collation(SQLite3.Handle, 'UTF8_CI', SQLITE_UTF8, nil, @UTF8xCompare_CI);
181	181		// or using method of TSQLite3Connection:
182	182		CreateCollation('UTF8_CI',1,nil,@UTF8xCompare_CI);
183	183
184	184		// now we can use case-insensitive comparison in SQL like:
185	185		// SELECT * FORM table1 WHERE column1 COLLATE UTF8_CI = 'á'
186	186
187	187		// but this does not work for LIKE operator
188	188		// in order to support also LIKE operator we must overload default LIKE function using sqlite3_create_function()
189	189		// http://www.sqlite.org/lang_corefunc.html#like
190	190		```
191	191
192	192		### Creating user defined functions
193	193
194	194		``` pascal
195	195		// example overloading default LOWER() function with user supplied function
196	196		// to run this demo, you must add units 'sqlite3dyn' and 'ctypes' to your uses-clause
197	197		// and add a const 'SQLITE_DETERMINISTIC' with value $800
198	198
199	199		procedure UTF8xLower(ctx: psqlite3_context; N: cint; V: ppsqlite3_value); cdecl;
200	200		var S: AnsiString;
201	201		begin
202	202		SetString(S, sqlite3_value_text(V[0]), sqlite3_value_bytes(V[0]));
203	203		S := UTF8Encode(AnsiLowerCase(UTF8Decode(S)));
204	204		sqlite3_result_text(ctx, PAnsiChar(S), Length(S), sqlite3_destructor_type(SQLITE_TRANSIENT));
205	205		end;
206	206
207	207		// register function LOWER() using SQLite3 API (requires sqlite3dyn unit):
208	208		sqlite3_create_function(SQLite3.Handle, 'lower', 1, SQLITE_UTF8 or SQLITE_DETERMINISTIC, nil, @UTF8xLower, nil, nil);
209	209		```
210	210
211	211		### SQLite3 and Dates
212	212
213	213		- SQLite 3 doesn't store dates as a special DateTime value. It can stores them as strings, doubles or integers - see
214	214
215	215		<http://www.sqlite.org/datatype3.html#datetime>.
216	216
217	217		- In strings, the date separator is '-' as per SQL standard/ISO 8601. Thus, if you do an INSERT using the built-in DATE function, it will store it as something like 'YYYY-MM-DD'.
218	218		- Reading a DateTime value can cause problems for DataSets if they are stored as strings: the .AsDateTime qualifier can stall on an SQLite 'string date' but this can be overcome by using something like `strftime(``%d/%m/%Y``,recdate) AS sqlite3recdate` in your SQL SELECT statement, which forces SQLite3 to return the date record in a specified format. (the format string %d/%m/%d corresponds to your locale date format which .AsDateTime will understand) ==\> Please open a bug report with an example application demonstrating the problemif this is the case
219		-	- When comparing dates stored as strings (using for example the BETWEEN function) remember that the comparison will always be a <b>string</b> comparison, and will therefore depend on how you have stored the date value.
	219	+	- When comparing dates stored as strings (using for example the BETWEEN function) remember that the comparison will always be a string comparison, and will therefore depend on how you have stored the date value.
220	220
221	221		#### Default values in local time instead of UTC
222	222
223	223		CURRENT_TIME, CURRENT_DATE and CURRENT_TIMESTAMP return current UTC date and/or time. For local date and/or times we can use:
224	224
225	225		` DEFAULT (datetime('now','localtime')) for datetime values formated YYYY-MM-DD HH:MM:SS`\
226	226		` DEFAULT (date('now','localtime')) for date value formated YYYY-MM-DD`\
227	227		` DEFAULT (time('now','localtime')) for time value formated HH:MM:SS`
228	228
229	229		### SQLDB And SQLite troubleshooting
230	230
231	231		- Keep in mind that for designtime support to work (fields etc) Lazarus must find sqlite3.dll too.
232	232		- The same goes for the database filename. Always use absolute path if you use components to extract e.g. fieldnames at designtime. Otherwise the IDE will create an empty file in its directory. In case of trouble, check if the lazarus/ directory doesn't hold a zero byte copy of the database file.
233	233		- If you have master/detail relationship, you need to refresh master dataset after each insert, in order to get value for slave dataset foreign key field. You can do that in AfterPost event of the master dataset, by calling one of the following overloaded procedures:
234	234
235	235		``` pascal
236	236		interface
237	237		procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
238	238		procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;
239	239
240	240		implementation
241	241
242	242		procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
243	243		//This procedure refreshes a dataset and positions cursor to last record
244	244		//To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
245	245		var
246	246		vLastID: Integer;
247	247		vUpdateStatus : TUpdateStatus;
248	248		begin
249	249		vUpdateStatus := pDataset.UpdateStatus;
250	250		//Get last inserted ID in the database
251	251		pDataset.ApplyUpdates;
252	252		vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
253	253		//Now come back to respective row
254	254		if vUpdateStatus = usInserted then begin
255	255		pDataset.Refresh;
256	256		//Refresh and go back to respective row
257	257		pDataset.Locate(pKeyField,vLastID,[]);
258	258		end;
259	259		end;
260	260
261	261		procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
262	262		//This procedure refreshes a dataset and positions cursor to last record
263	263		//To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
264	264		var
265	265		vLastID: Integer;
266	266		vUpdateStatus : TUpdateStatus;
267	267		begin
268	268		vUpdateStatus := pDataset.UpdateStatus;
269	269		pDataset.ApplyUpdates;
270	270		vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
271	271		if vUpdateStatus = usInserted then begin
272	272		pDataset.Refresh;
273	273		//Dangerous!
274	274		pDataSet.Last;
275	275		end;
276	276		end;
277	277
278	278		procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
279	279		begin
280	280		RefreshADatasetAfterInsert(Dataset as TSQLQuery); //If your dataset is sorted by primary key
281	281		end;
282	282
283	283		procedure TDataModule1.SQLQuery2AfterPost(DataSet: TDataSet);
284	284		begin
285	285		RefreshADatasetAfterInsert(Dataset as TSQLQuery, 'ID'); //if you are not sure that the dataset is always sorted by primary key
286	286		end;
287	287		```
288	288
289	289		### Vacuum and other operations that must be done outside a transaction
290	290
291	291		SQLDB seems to always require a connection, but some operations like Pragma and Vacuum must be done outside a transaction. The trick is to end transaction, execute what you must and start transaction again (so that sqldb doesn't get confused:)
292	292
293	293		``` pascal
294	294		// commit any pending operations or use a "fresh" sqlconnection
295	295		Conn.ExecuteDirect('End Transaction'); // End the transaction started by SQLdb
296	296		Conn.ExecuteDirect('Vacuum');
297	297		Conn.ExecuteDirect('Begin Transaction'); //Start a transaction for SQLdb to use
298	298		```
299	299
300	300		## Using TSQLite3Dataset
301	301
302	302		This section details how to use the TSQLite2Dataset and TSQLite3Dataset components to access SQlite databases. by Luiz Américo luizmed(at)oi(dot)com(dot)br
303	303
304	304		#### Requirements
305	305
306	306		- For sqlite2 databases (legacy):
307	307		- FPC 2.0.0 or higher
308	308		- Lazarus 0.9.10 or higher
309	309		- SQLite runtime library 2.8.15 or above*
310	310
311	311		- Sqlite2 is not maintained anymore and the binary file cannot be found in the sqlite site
312	312
313	313		- For sqlite3 databases:
314	314		- FPC 2.0.2 or higher
315	315		- Lazarus 0.9.11 (svn revision 8443) or higher
316	316		- sqlite runtime library 3.2.1 or higer (get it from [www.sqlite.org](http://www.sqlite.org))
317	317
318	318		Before initiating a lazarus project, ensure that:
319	319
320	320		- the sqlite library is either
321	321		- in the system PATH or
322	322		- in the executable output directory and Lazarus (or current project) directories - this option might work on Windows only
323	323		- under Linux, put cmem as the first unit in uses clause of the main program
324	324		- In Debian, Ubuntu and other Debian-like distros, in order to build Lazarus IDE you must install the packages libsqlite-dev/libsqlite3-dev, not only sqlite/sqlite3 (Also applies to OpenSuSe)
325	325
326	326		#### How To Use (Basic Usage)
327	327
328	328		Install the package found at /components/sqlite directory (see instructions [[Install Packages\|here]])
329	329
330	330		At design time, set the following properties:
331	331
332	332		- FileName: path of the sqlite file \[required\]
333	333		- TableName: name of the table used in the sql statement \[required\]
334	334		- SQL: a SQL select statement \[optional\]
335	335		- SaveOnClose: The default value is false, which means that changes are not saved. One can change it to true. \[optional\]
336	336		- Active: Needs to be set at design time or at program startup. \[required\]
337	337
338	338		Creating a Table (Dataset)
339	339
340	340		Double-click the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button. A simple self-explaining table editor will be shown.
341	341
342	342		Here are all field types supported by TSqliteDataset and TSqlite3Dataset:
343	343
344	344		- Integer
345	345		- AutoInc
346	346		- String
347	347		- Memo
348	348		- Bool
349	349		- Float
350	350		- Word
351	351		- DateTime
352	352		- Date
353	353		- Time
354	354		- LargeInt
355	355		- Currency
356	356
357	357		Retrieving the data
358	358
359	359		After creating the table or with a previously created Table, open the dataset with the Open method. If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:
360	360
361	361		``` pascal
362	362		SQL := 'Select * from TABLENAME';
363	363		```
364	364
365	365		Applying changes to the underlying datafile
366	366
367	367		To use the ApplyUpdates function, the dataset must contain at least one field that fulfills the requirements for a Primary Key (values must be UNIQUE and not NULL)
368	368
369	369		It's possible to do that in two ways:
370	370
371	371		- Set PrimaryKey property to the name of a Primary Key field
372	372		- Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key)
373	373
374	374		If one of the two conditions is set, just call
375	375
376	376		``` pascal
377	377		ApplyUpdates;
378	378		```
379	379
380	380		> Note: If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.
381	381
382	382		> Note: Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.
383	383
384	384		##### Master/detail example
385	385
386	386		Various examples of master/detail relations (e.g. the relation between customer and orders):
387	387
388	388		- the generic SQLDB way of doing this using the `DataSource` property: [[MasterDetail]]
389	389		- TSQLite3 specific example using locate: [[TSqlite3 Master Detail Example]].
390	390
391	391		#### Remarks
392	392
393	393		- Although it has been tested with 10,000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (especially with Memo Fields).
394	394		- The same datafile (Filename property) can host several tables/datasets
395	395		- Several datasets (different combinations of fields) can be created using the same table simultaneously
396	396		- It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
397	397		- It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and i will give some hints how to do that
398	398		- Setting filename to a sqlite datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.
399	399
400	400		Generic examples can be found at fpc/fcl-db/src/sqlite SVN directory
401	401
402	402		## See also
403	403
404	404		- [[SQLdb Tutorial1]] Tutorial for Sqlite
405	405		- [[SqlDBHowto]] General information on SQLDB
406	406		- [[Working With TSQLQuery]] Information on SQLDB's TSQLQuery
407	407		- [SQLite site](http://www.sqlite.org)
408	408
409	409		---
410	410		From the Free Pascal wiki, page "SQLite", as it stood on 2022-06-05 after a change by Thierrybo, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history