Tutorial 01 - A (very) basic address book

Preparations

Create an empty application, put the following components, and change the properties according to the specified.

• 01 TSqlite3Dataset
  • Name: ContactsDataset
  • TableName: Contacts
• 01 TDataSource
  • Name: ContactsDataSource
  • Dataset: ContactsDataset
• 01 TDBGrid
  • DataSource: ContactsDataSource
  • Columns: Add 02 items and set the FieldName property of each item to 'Name' and 'Email'.
• 03 TButton
  • Name: AddButton / DeleteButton / SaveButton
  • Caption: Add / Delete / Save
    

Create and load the database

It's possible to create the database file that the application will use in three ways:

1) Use an external database manager. Has the disadvantages of have to ship the data file with the application and also possible compatibility issues between data types of the manager and how TSqlite*Dataset interprets them

2) Right-Click in the TSqlite3Component and use the built-in table manager. Has the disadvantages of having to ship the data file with the application

3) Create the data file at runtime. This is the preferred method because there's not the above disadvantages The tutorial will use this method.

Create an OnCreate event handler for the form and put the following code:

  ForceDirectoriesUTF8(GetAppConfigDirUTF8(False));
  with ContactsDataset do
  begin
    FileName := IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(False))+'data.db';
    if not TableExists then
    begin
      FieldDefs.Clear;
      FieldDefs.Add('Id', ftAutoInc);
      FieldDefs.Add('Name', ftString);
      FieldDefs.Add('Email', ftString);
      CreateTable;
    end;
    Open;
  end; 

Line by line:

  FileName := IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(False))+'data.db';

This is the path where the data file will be stored.
So why use GetAppConfigDirUTF8 and not put in the same directory (or in a subdirectory)?. This approach is OK for windows (before Vista) where all users have write permissions in the application folder. This is not valid in Linux and in Vista. For more info see the GetAppConfigDir* documentation in fpc site. Anyway is up to the programmer choose the location where the data file is stored.
* GetAppConfigDirUTF8 has the same behavior as GetAppConfigDir except that returns an UTF8 encoded string. Sqlite expects UTF8 encoded string so it's recommended to use the *UTF8 functions  found in unit FileUtil.

  if not TableExists then

Will check is the table "Contacts" (the TableName property value) already exists in the data file

   FieldDefs.Clear;
   FieldDefs.Add('Id', ftAutoInc);
   FieldDefs.Add('Name', ftString);
   FieldDefs.Add('Email', ftString);
   CreateTable;

If the table does not exists than will create a table with the field Id, Name and Email.

So why create Id field as AutoInc?
To the save function of TSqlite*Dataset (ApplyUpdates) work is necessary to have a primary key field, i.e., a not null unique field. An AutoInc field is the simplest way of getting a primary key under TSqlite*Dataset.

  Open;

This will put the dataset in active state. All the records saved in the data file will be loaded and will be show in the DBGrid.

Add and Remove records. Save the changes.

In the OnClick event of AddButton put:

  ContactsDataset.Append;
  ContactsDataset.FieldByName('Name').AsString := 'New Contact';
  ContactsDataset.FieldByName('Email').AsString := 'xxx@yyy.com';
  ContactsDataset.Post;  

Append will add a record at the end of the dataset. FieldByName calls will add default values for the new record. Post will tell the dataset that the editing is done. WARNING: Post does not saves the changes to the underlying data file.

In the OnClick event of DeleteButton put:

  if ContactsDataset.RecordCount > 0 then
    ContactsDataset.Delete;

The Delete functions removes the current record from the dataset. The RecordCount check is to avoid an exception in empty datasets.

In the OnClick event of SaveButton put

  ContactsDataset.ApplyUpdates; 

This will save to the data file all changes done in the dataset (Additions, deletions, value changes). Next time the application is opened the new values will appear. If ApplyUpdates is not called the changes done in the session will be lost.

So where is the SQL at all?

Note that in no moments a SQL command is used by the programmer. TSqlite*Dataset has the ability to manipulate datasets without SQL commands at the same time that provides methods to access all features of SQL/Sqlite. More on this later.

But internally, what TSqlite*Dataset does?
1) When Open is called with the Sql property empty (like in this tutorial), it loads the data using a Sql command with the following pattern 'Select * from [TableName]'. Here is necessary to warn that if Sql property is empty than TableName can not be empty.
2) ApplyUpdates creates SQL commands using the records modified through TDataset calls (Append, Delete, Edit). To work is necessary a primary key (the Id field in this tutorial) and a valid value in TableName.

The project file can be found here.