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.
- Start Lazarus and open project SQLite03.
- Note that we have removed some buttons from the DBNavigators.
- 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.
- 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;
- 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.
- 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;
- 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;
- Buttons btnCancelMembers and btnCancelGuitars issue a "Cancel" command (this puts
the corresponding query back in "browse" mode) and hide the corresponding panel.
- 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;
|
|