การใช้ฐานข้อมูล SQLite กับ Lazarus ในเบื้องต้น (ตอนที่ 2)

• ตอนที่แล้วผม post ตัวโค๊ดทั้งหมด มาดูคำอธิบายตรงสาระที่สำคัญ

Declare ตัวแปรสำหรับ SQLite

• ที่ class ของ TfrmSetEllipsoid จะ declare เพื่อจัดการกับฐานข้อมูล SQLite ผม declare ทีส่วน private

  { TfrmSetEllipsoid }

  TfrmSetEllipsoid = class(TForm)
    cmdDelete: TButton;
    cmdNew: TButton;
    cmdApply: TButton;
    cmdOK: TButton;
    cmdCancel: TButton;
    dbtxtFlattening: TDBEdit;
    dbtxtSemiMajorAxis: TDBEdit;
    dbtxtEllipsoidCode: TDBEdit;
    DBNavigator1: TDBNavigator;
    dbcboEllipsoidName: TDBComboBox;
    GroupBox1: TGroupBox;
    Image1: TImage;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;

    procedure cmdApplyClick(Sender: TObject);
    procedure cmdCancelClick(Sender: TObject);
    procedure cmdDeleteClick(Sender: TObject);
    procedure cmdNewClick(Sender: TObject);
    procedure cmdOKClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { private declarations }
    FDBCon : TSQLite3Connection;
    FTrans : TSQLTransaction;
    FSql : TSQLQuery;
    FDatasource : TDatasource;
    FDatabaseFile : string;
    FNewRecordState : boolean;
  public
    { public declarations }
  end;

• มีตัวแปร FDBCon, FSql, FTrans เป็นตัวแปรในระดับ Dataset ตัวแปร FDBCon อยู่ล่างสุดทำหน้าที่ชี้ไปยัง Database ส่วน FTrans ทำหน้าที่เรื่อง Transaction ส่วน FSql เป็น Dataset ที่ทำหน้าที่ Query ข้อมูลจากฐานข้อมูลตามเงื่อนไขมาเก็บไว้ ส่วน FDatasource อยู่สูงขึ้นมาอีกเป็นตัวกลางระหว่าง Dataset กับวัตถุจำพวก data-aware ที่ทำหน้าที่แสดงฟิลด์ของข้อมูล ดังไดอะแกรมที่ผมแสดงไว้ตอนที่ 1 ความสัมพันธ์ระหว่างตัวแปรนี้เป็นไปดัง event ของ FormCreate

procedure TfrmSetEllipsoid.FormCreate(Sender: TObject);
begin
  //เปลี่ยน path ที่อยู่ของฐานข้อมูลให้ตรงกับโฟลเดอร์ที่เก็บ
  FDatabaseFile := 'e:\sourcecodes\lazarus\sqliteproj2\datums.s3db';
  //FDatabaseFile := '/media/PJ320GMISC/SourceCodes/Lazarus/SQLiteProj2/datums.s3db';

  FDBCon := TSQLite3Connection.Create(NIL);
  FTrans := TSQLTransaction.Create(NIL);
  FSql := TSQLQuery.Create(NIL);
  FDatasource := TDatasource.Create(NIL);

  FDBCon.DatabaseName := FDatabaseFile;
  FDBCon.Transaction := FTrans;
  FTrans.DataBase := FDBCon;
  FSql.DataBase := FDBCon;
  FSql.Transaction := FTrans;
  FDBCon.Connected := true;
  FTrans.Active := true;
  FDatasource.Dataset := FSql;
  FDatasource.AutoEdit := true;

  FSql.Close;
  FSql.SQL.Clear;
  FSql.SQL.Text := 'SELECT * FROM ELLIPSOIDS';
  FSql.Open;
  //FTrans.CommitRetaining; {This line does not work in win32. But OK in linux???}

  dbcboEllipsoidName.DataSource := FDatasource;
  dbcboEllipsoidName.DataField := 'NAME';
  dbtxtEllipsoidCode.DataSource := FDatasource;
  dbtxtEllipsoidCode.DataField := 'CODE';
  dbtxtSemiMajorAxis.DataSource := FDatasource;
  dbtxtSemiMajorAxis.DataField := 'A';
  dbtxtFlattening.DataSource := FDatasource;
  dbtxtFlattening.DataField := 'INV_F';
  DBNavigator1.DataSource := FDatasource;

  FNewRecordState := false;
end;

• เมื่อปิดโปรแกรมตรง event FormDestroy อย่าลืม Free ให้กับตัวแปรที่เรา Declare ไว้ เพื่อคืนความจำให้ระบบ

procedure TfrmSetEllipsoid.FormDestroy(Sender: TObject);
begin
  FTrans.Free;
  FSql.Free;
  FDatasource.Free;
  //Make sure TSQLite3Connection is the last.
  FDBCon.Free;
end;

ลบ Record

• การเลื่อน Record ทำได้โดยการลิกที่ปุ่มลูกศร ซ้ายและขวา
• มาดูการลบ record ที่ปุ่ม (button) “Delete…” สร้าง Event procedure ชี้ไปที่ cmdDeleteClick(Sender: TObject);

procedure TfrmSetEllipsoid.cmdDeleteClick(Sender: TObject);
var
  szECode : string;
  sql : TSQLQuery;

begin
  try
    sql := TSQLQuery.Create(nil);
    sql.Database := FDBCon;
    sql.Transaction := FTrans;
    szECode := dbtxtEllipsoidCode.Text;

    if (MessageDlg ('คำเตือน','ยืนยันว่าต้องการลบทรงรี', mtConfirmation,
               [mbOK, mbCancel],0) = mrOK) then
    begin
      sql.Close;
      sql.SQL.Text := 'DELETE FROM ELLIPSOIDS WHERE CODE = :CODE';
      sql.Params.ParamByName('CODE').AsString := szECode;
      sql.ExecSQL;
      FSql.ApplyUpdates;
      FTrans.CommitRetaining;
      FSql.Close;
      FSql.SQL.Clear;
      FSql.SQL.Text := 'SELECT * FROM ELLIPSOIDS';
      FSql.Open;
      FTrans.CommitRetaining;
  end;

  finally
    sql.Free;
  end;
end;

• จากโค๊ดด้านบน เนื่องจากเราไม่ได้ใช้ DBNavigator เราจะำทำการลบด้วยการดึงชื่อโค๊ดของทรงรีเข้าตัวแปร szECode  เพื่อมาใช้ Query “DELETE” ได้ถูก ก่อนจะลบให้ผู้ใช้ยีนยันด้วย MessageDlg

เพิ่มและแก้ไข Record

• การแก้ไขฟิลด์ของ record ที่มีอยู่แล้วใช้ SQL syntax “UPDATE” แต่การลบ record ใช้ “DELETE” จึงมีตัวแปร FNewRecordState คอยตรวจว่าเป็นการแก้ไขหรือการลบ record มาดูการเพิ่ม record ที่ event “cmdNewClick” ไม่มีอะไรมากสำหรับแค่เตรียมตัว data-aware ให้ว่างและจัดสถานะให้พร้อม พร้อมที่ผู้ใช้โปรแกรมป้อนค่าทรงรีรูปทรงใหม่เข้าไป การจะจัดเก็บเข้า Database ให้ผู้ใช้คลิกที่ปุ่ม “Apply” เพื่อยืนยัน

procedure TfrmSetEllipsoid.cmdNewClick(Sender: TObject);
begin
  //Make its to ready for editable.
  dbcboEllipsoidName.ReadOnly := false;
  dbtxtSemiMajorAxis.ReadOnly := false;
  dbtxtflattening.ReadOnly := false;
  dbtxtEllipsoidCode.ReadOnly := false;
  dbcboEllipsoidName.Text := '';
  dbtxtSemiMajorAxis.Text := '';
  dbtxtFlattening.Text := '';
  dbtxtEllipsoidCode.Text := '';
  cmdApply.Enabled := true;
  cmdNew.Enabled := false;
  DBNavigator1.BtnClick(nbInsert);
  FNewRecordState := true;
end;

UPDATE หรือ INSERT

• การ update ด้วยโปรแกรม demo ของผมได้แก่การแก้ไขค่าสัณฐานทรงรีเช่นชื่อของทรงรี, ค่า a, ค่า 1/f ยกเว้นไม่ให้แก้ไขคือค่าโค๊ดของทรงรี (CODE) ถ้าเพิ่มรูปทรงรีสามารถทำได้อิสระ เพียงแต่ค่า CODE ของทรงรีต้องไม่ซ้ำกับค่าที่มีอยู่แล้ว
• เมื่อคลิกปุ่ม “New…” ทำการป้อนรูปทรงรีใหม่เข้าไป หรือแก้ไขค่าทรงรีเดิม ฐานข้อมูลจะไม่มีการเปลี่ยนแปลงจนกระทั่ง ผู้ใช้โปรแกรมคลิกที่ปุ่ม “Apply” โปรแกรมจะทำตรวจสอบว่าผู้ใช้เพิ่ม record จากตัวแปร FNewRecordState ถ้าเป็นการ Update ก็สวิตท์ if clause ไปที่ else if
• โปรแกรมมีเงื่อนไขอีกนิดว่าค่า a ต้องมากกว่า 6377000 เมตร และค่า 1/f ต้องมากกว่า  293 ถ้าไม่ได้ให้รอป้อนจนป้อนค่าถูก

procedure TfrmSetEllipsoid.cmdApplyClick(Sender: TObject);
var
  szEName, szECode : string;
  dA, dB, dF : extended;
  f1, f2 : boolean;
  sql : TSQLQuery;
begin
  try
    sql := TSQLQuery.Create(nil);
    sql.Database := FDBCon;
    sql.Transaction := FTrans;
    szEName := trim(dbcboEllipsoidName.Text);
    szECode := trim(dbtxtEllipsoidCode.Text);
    if (szEName <> '') and (szECode <> '') then
    begin
      szECode := Trim(dbtxtEllipsoidCode.Text);
      f1 := trystrtofloat(dbtxtSemiMajorAxis.Text, dA);
      f2 := trystrtofloat(dbtxtFlattening.Text, dF);
      dB := dA - dA * 1 / dF;
      if (FNewRecordState) then
      begin
        if (f1 and (dA > 6377000)) and (f2 and (df > 293)) then
        begin
          sql.Close;
          sql.SQL.Clear;
          sql.SQL.Text := 'INSERT INTO ELLIPSOIDS (NAME,CODE,A,B,INV_F)'
                        + ' VALUES (:NAME,:CODE,:A,:B,:INV_F)';
          sql.Params.ParamByName('NAME').AsString := szEName;
          sql.Params.ParamByName('CODE').AsString := szECode;
          sql.Params.ParamByName('A').AsFloat := dA;
          sql.Params.ParamByName('B').AsFloat := dB;
          sql.Params.ParamByName('INV_F').AsFloat := dF;

          sql.ExecSQL;
          cmdApply.Enabled := False;
          cmdNew.Enabled := true;
          FTrans.CommitRetaining;
          FNewRecordState := false;
        end
        else if not(f1 and (dA > 6377000)) then
        begin
          MessageDlg ('Warning', 'Semi-major axis must be the number and its value must > 6,377,000 m.?', mtConfirmation,
                     [mbOK],0);
          dbtxtSemiMajorAxis.SetFocus;
        end
        else if not (f2 and (df > 297)) then
        begin
          MessageDlg ('Warning', 'Flattening must be the number and its value must > 297 m.?', mtConfirmation,
                     [mbOK],0);
          dbtxtFlattening.SetFocus;
        end;
      end
      else
      begin
        sql.Close;
        sql.SQL.Clear;
        sql.SQL.Text := 'UPDATE ELLIPSOIDS SET NAME=:NAME,'
                      + 'A=:A,B=:B,INV_F=:INV_F WHERE CODE=:CODE';
        sql.Params.ParamByName('NAME').AsString := szEName;
        sql.Params.ParamByName('CODE').AsString := szECode;
        sql.Params.ParamByName('A').AsFloat := dA;
        sql.Params.ParamByName('B').AsFloat := dB;
        sql.Params.ParamByName('INV_F').AsFloat := dF;

        sql.ExecSQL;
        cmdApply.Enabled := False;
        FTrans.CommitRetaining;

      end;
    end;
    finally
      sql.Free;
    end;
end;

• มาลองรันโปรแกรม ลองคลิกไปที่ลูกศรขวาจนถึงรูปทรงรี “WGS 72” ลองคลิกที่ปุ่ม “Delete…”  โปรแกรมจะถามยืนยันตอบ OK

• เมื่อลบเสร็จโปรแกรมจะเลื่อนไปที่ record แรกอัตโนมัติ ลองเลื่อนลูกศรไปดูว่า WGS 72 ยังอยู่หรือไม่ จะไม่เห็น ต่อไปลองมาแก้ไขค่ารูปทรงรีดู

• ลองปิดโปรแกรมแล้วรันอีกครั้งทดสอบว่าฐานข้อมูล update ไปตามต้องการแล้วหรือยัง ช่วงทดสอบโปรแกรมผมจะ backup ฐานข้อมูลไว้อีกชุดหนึ่ง ในความเป็นจริงรูปทรงรีที่เป็นมาตรฐาน บางโปรแกรมจะไม่ให้ทำการแกัไข ที่จะแก้ไขได้แก่รูปทรงรีที่ผู้ใช้เพิ่มเข้าไป (user defined) ฉะนั้นเวลาโปรแกรมกันจริงๆ ฐานข้อมูลเราจะเพิ่มฟิลด์ READONLY ไปอีกฟิลด์หนึ่ง ถ้าเป็นรูปทรงรีมาตรฐาน READONLY จะเป็น TRUE
• ฐานข้อมูลที่ทดสอบเป็นไปอย่างง่ายมีแค่ Table เดียว ถ้ามีหลาย Table เช่นการเขียนโปรแกรมแปลงค่าพิกัดที่ support หลายๆเส้นโครงแผนที่ (Map projection) ในฐานข้อมูลนี้จะจัดเก็บ datum ไว้อีก Table หนึ่งต่างหาก  ซึ่งจะมีค่าพารามิเตอร์ เช่น Translation แกน X, Y, Z และค่า Rotation แกน X, Y, Z และมึค่า Scale รวมแล้วเป็นเจ็ดพารามิเตอร์

Download sourcecode

• sourcecode ของ post ตอนนี้สามารถ download ได้ที่ SQLiteProj2.zip

ทิ้งท้ายก่อนจาก

• เป็นธรรมเนียมก่อนจะจบ post แต่ละตอนจะมีอะไรมาพูดคุยก่อนจะจาก ตอนที่แล้วผมพูดถึงไลบรารี ด้าน GIS คือ GDAL/OGR ที่ website จะเห็นมีตัว wrapper ด้วยภาษาอื่นๆที่ไม่ใช่ภาษา C++ เช่น Python, Ruby, Java, Perl และ C#/.Net การจะ wrapper ส่วนใหญ่จะตามมาตรฐานที่เรียกว่า SWIG (Simplified Wrapper and Interface Generator) มีกลุ่มโปรแกรมเมอร์ที่ตั้งมาตรฐานในการเขียนโปรแกรมเพื่อ interface กับ Library ที่เขียนด้วย C++ ด้วยภาษาอื่นๆที่ผมกล่าวมาแล้ว จะเห็นว่าขาด Delphi หรือ Lazarus และ VB (อีกแล้ว)
• มีโปรแกรมเมอร์ชื่อ Stefano Moratto เขียน interface GDAL ด้วย SWIG ที่เขาเขียนด้วยตัวเขาเอง แต่ปรากฎว่าตั้งแต่ปี 2008 คุณ Frank Warmerdam ยังตามตัว Stefano Moratto ไม่เจอ แหมน่าเสียดายมาก ถ้ามี SWIG ของ GDAL ด้วย Delphi ก็วิเศษเลย จะเป็นประโยชน์แก่โปรแกรมเมอร์ Delphi อีกมากรวมทั้ง Lazarus ด้วย ไม่เป็นไรตัว wrapper ที่เขียนด้วย VB ที่ผมแปลงโค๊ดเป็น Lazarus ก็พอไหว ตอนนี้แค่นี้ก่อนครับ