Free Pascal
LAZARUS

SQLite Database Tutorial: GuitarClub, part 3

Until now, nothing ensures the integrity of the database, the accuracy and reliability of the data. We have to enforce rules on the data, before they are saved into the tables:

  • the field ID of every record in Members has to be unique;
  • the field Lastname in Members can not be empty;
  • the field MemberID in Guitars must refer to an existing record in Members;
  • the fields Brand or Type in Guitars can not be empty;
  • when a record of Members is deleted, the corresponding records of Guitars should be deleted.

Validation

For the validation of the data, we won't rely anymore on the automatic editing and inserting of records. We have to do the modifications of data in code. What we have to do:

  • remove some buttons of the DBNavigators and replace them with buttons of our own;
  • prevent editing and inserting directly in the grids;
  • add edit-components for the fields;
  • for new records, set the ID fields with the correct values;
  • disable and re-enable the grids and buttons at the right time.

GuitarClub version 3

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

  1. Start Lazarus and open project SQLite03.
  2. Note that we have removed some buttons from the DBNavigators.
  3. We added two panels with TDBEdit components for the fields of the tables. Both panels are hidden at the start (see FormShow).
    In the Object Inspector, have a look at the properties of in the TDBEdits.
  4. For editing of the records, we have added buttons btnEditMembers and btnEditGuitars. Note that these buttons also shows/hide panels with TDBEdit components.

    procedure TForm1.btnEditMembersClick(Sender: TObject);
    begin
      panMembers.Visible := True;
      UpdateUI;
      edLastname.SetFocus;
      queryMembers.Edit; // set query to "edit" mode
    end;


    procedure TForm1.btnEditGuitarsClick(Sender: TObject);
    begin
      panGuitars.Visible := True;
      UpdateUI;
      edBrand.SetFocus;
      queryGuitars.Edit; // set query to "edit" mode
    end;
  5. Procedure UpdateUI centralizes the necessary changes of the interface. It disables and re-enables the grids, the DBNavigators and the buttons.

    procedure TForm1.UpdateUI;
    begin
      gridMembers.Enabled        := (not panMembers.Visible) and
                                    (not panGuitars.Visible);
      DBNavMembers.Enabled       := gridMembers.Enabled;
      btnEditMembers.Enabled     := gridMembers.Enabled;
      btnInsertMembers.Enabled   := gridMembers.Enabled;
      gridGuitars.Enabled        := gridMembers.Enabled;
      DBNavGuitars.Enabled       := gridGuitars.Enabled;
      btnEditGuitars.Enabled     := gridGuitars.Enabled;
      btnInsertGuitars.Enabled   := gridGuitars.Enabled;
      btnExchangeGuitars.Enabled := gridGuitars.Enabled;
    end


    Without this procedure, some other procedures would be a lot more complicated. It also aids in updating your application later on.
  6. For inserting members, we have added btnInsertMembers.
    For starters, the OnClick handler looks up the hightest ID and adds 1 to that. The new ID will be used for the new record.
    Next, the query is set to "insert" mode and the application waits for a click on the post-button or the cancel-button.

    procedure TForm1.btnInsertMembersClick(Sender: TObject);
    var
      IDNr: integer;
      ID, OldSQL: string;
    begin
      // Get highest ID and add 1
      OldSQL := queryMembers.SQL.Text; // remember the SQL.Text
      queryMembers.Active := False; // must be inactive for a new SQL.Text

      // Order Member records by ID and go to the last record
      queryMembers.SQL.Text := 'select * from members order by ID';
      queryMembers.Active := True;
      queryMembers.Last;
      IDNr := StrToInt(queryMembers.FieldByName('ID').AsString) + 1;
      ID := IntToStr(IDNr);
      while Length(ID) < 4 do ID := '0' + ID;

      queryMembers.Active := False;
      queryMembers.SQL.Text := OldSQL;
      queryMembers.Active := True;
      panMembers.Visible := True;
      UpdateUI;
      queryMembers.Insert;
      edID.Text := ID;
      edLastname.SetFocus;
    end;
  7. The event handler for button btnPostMembers checks if something was entered in the DBEdit for field Lastname. If not, an error message is shown. Else, the record is posted.

    btnPostGuitars works in a similar way.

    procedure TForm1.btnPostMembersClick(Sender: TObject);
    begin
      if Trim(edLastName.Text) = '' then begin
        ShowMessage('LASTNAME can not be empty');
        edLastname.SetFocus;
      end
      else begin

        queryMembers.Post;
        panMembers.Visible := False;
        UpdateUI;
      end;
    end


    procedure TForm1.btnPostGuitarsClick(Sender: TObject);
    begin
      if Trim(edBrand.Text) = '' then begin
        ShowMessage('BRAND can not be empty');
        edBrand.SetFocus;
      end
      else if
    Trim(edModel.Text) = '' then begin
        ShowMessage('MODEL can not be empty');
        edModel.SetFocus;
      end
      else begin

        queryGuitars.Post;
        panGuitars.Visible := False;
        UpdateUI;
      end;
    end;
  8. Buttons btnCancelMembers and btnCancelGuitars issue a "Cancel" command (this puts the corresponding query back in "browse" mode) and hide the corresponding panel.
  9. The BeforeDelete event handler of queryMembers deletes all of the records of Guitars that correspond to the current Members record:

    procedure TForm1.queryMembersBeforeDelete(DataSet: TDataSet);
    begin
      queryGuitars.First;
      while not queryGuitars.EOF do   // EOF: End Of File
        queryGuitars.Delete;
    end;