SQLite Database Tutorial: GuitarClub, part 2

Structure of the database and the tables

Let's add a table for the guitars.

In addition to the table Members, the database GuitarClub.db now contains a second table named Guitars, with the details of each guitar. Its columns (aka "fields") are as follows:

Note: the last field ID points to the field ID in table Members. It is used for setting up the master/detail relationship between Members and Guitars.

GuitarClub project with 2 tables

For this project, create a folder FPlaz\SQLite02, download SQLite02.zip and unzip to this folder.

1. Start Lazarus and open project SQLite02.
2. Compile and run the application (press F9).
   
   
3. Play a while with the application: navigate through the grid by using the DBNavigator, modify records, add records, delete records.
4. Stop the program.

When you look at the source code, you'll see that it is a bit more explicit than before.

procedure TForm1.FormShow(Sender: TObject);
begin
  // Make sure that nothing is connected or active
  SQLite3Connection1.Connected := False;
  queryMembers.Active := False;
  SQLTransaction1.Active := False;
  queryGuitars.Active := False;

  // Prepare the components
  SQLite3Connection1.DatabaseName := 'guitarclub.db';
  queryMembers.Options :=
    [sqoKeepOpenOnCommit, sqoAutoApplyUpdates, sqoAutoCommit];
  queryMembers.SQL.Text :=
    'select * from members order by upper(lastname), upper(firstname)';
  queryGuitars.Options :=
    [sqoKeepOpenOnCommit, sqoAutoApplyUpdates, sqoAutoCommit];
  queryGuitars.DataSource := dsMembers; // for master-detail relationship!
  queryGuitars.SQL.Text :=
    'select * from guitars where guitars.memberid = :id';

  // Connect the database and activate the SQL-queries
  SQLite3Connection1.Open;
  SQLTransaction1.Active := True;
  queryMembers.Open;
  queryGuitars.Open;
end;

The relationship between the tables is defined in the where clause of this line:

     queryGuitars.SQL.Text := 'select * from Guitars where Guitars.memberid = :id'; 

The colon : at the start of :id means: this is a so-called foreign key, in other words: it references a field in another table, it references the field ID in table Members.