라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (2) 데이터베이스

라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (1) 폼 디자인
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (2) 데이터베이스
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (3) 기타기능
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (4) 엑셀파일 출력 I
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (5) 엑셀파일 출력 II

3. SQLite

데이터베이스는 SQLite 를 사용한다. 한개의 dll 파일만 있으면 되고 별도의 설치 과정이 필요 없으므로 간단한 응용 프로그램을 만들기에 적합하다. SQLite 는 다음 사이트에서 다운로드 받는다.
https://www.sqlite.org/download.html

Precompiled Binaries for Windows 중에서 32비트 또는 64비트 환경에 맞는 것으로 다운로드 받는다. 예를 들어 자신이 64비트 윈도우를 사용하고 있다면 sqlite-dll-win64-x64-3360000.zip 을 다운로드 받는다. 압축을 풀면 sqlite3.dll 이 나온다. 이 라이브러리 파일 한개로 데이터베이스 관리를 할 수 있는 것이다. 별도의 경로를 지정하지 않았다면 sqlite3.dll 이 실행파일과 같은 폴더 내에 있어야 한다.

라자루스에서는 SQLite 를 사용하기 위한 컴포넌트를 제공하고 있다. 컴포넌트 팔렛트에서 SQLdb 탭을 선택한 후 TSQLite3Connection 컴포넌트를 폼 위에 올려 놓는다.

그리고 데이터베이스 관리를 위해서 TSQLQuery, TSQLTransation, Data Access 탭에서 TDataSource 를 선택하여 폼에 올려 놓는다. TSQLite3Connection 을 이용하여 SQLite 와 연결된다. 이 데이터를 가져와서 DBGrid 에서 가공하기 편하도록 변환시켜야 한다. DataSource 에서 이 역할을 한다. 데이터베이스에 SQL 명령문을 전달하기 위해서 SQLQuery 를 이용한다. 기본적인 원리는 SQLConnection 을 이용하여 SQLite 에 접근 -> 데이터베이스 생성 -> 자료입력 -> DataSource 로 데이터베이스 검색 결과 출력 -> DBGrid 화면으로 출력

각 컴포넌트의 Property 는 다음과 같이 지정한다.

(1) DataSource1 의  (Property)  Database : SQLite3Connection1
(2) SQLQuery1 의 (Property) Database  :  SQLite3Connection1  , 
Transation : SQLTransaction1
(3) SQLTransaction1 의 (Property) Database  : SQLite3Connection1
(4) SQLite3Connection1 의 (Property) Transaction : SQLTransaction1 
(5) DBGrid1 의 (Property) DataSource : DataSource1

FormShow 또는 FormCreate 이벤트 핸들러에 다음 문장을 삽입한다. SQLite 라이브러리 이름을 지정한다.

SQLiteLibraryName := 'sqlite3.dll'; 
SQLite3Connection1.DatabaseName := 'pass.db'; //이름을 정해주면 create 에서 자동 생성 

4. 데이터베이스(생성, 추가, 삭제, 수정, 검색, 백업)

비밀번호 관리를 위해서는 테이블이 필요하다. 테이블 내에는 항목을 나타내는 컬럼이 있다. 비밀번호는 웹사이트에 로그인하기 위해서 사용하는 것이 대부분이므로 웹사이트 주소를 나타내는 컬럼이 필요하다. 그외에 필요한 컬럼은

[0] Primary Key - PK : 각각의 레코드를 구별하는 유일한 키값
[1] 이름 (한글) - NameKorean : 필요한 웹사이트의 이름 한글로 표시
[2] 이름 (영문) - NameEnglish : 한글이름을 잘못 알고 있거나 영문으로만 알고 있을 때 사용
[3] 웹사이트 - WebSite : 웹사이트 주소를 표시 
[4] 아이디 - ID : 웹사이트 또는 다른 프로그램의 로그인 아이디
[5] 비밀번호 - PW : 로그인 비밀번호
[6] 기타메모 - Memo : 로그인 실패시에 웹사이트에서 확인하는 질문에 대한 대답 또는 주의사항

DBGrid 를 더블클릭하여 에디터를 실행시키고 Add 버튼을 눌러서 컬럼을 추가해 나간다.   DBGrid 와 데이터베이스의 컬럼명은 정확히 같아야 한다. 

화면에 표시되는 DBGrid1 의 컬럼 이름을 바꾸고 싶다면 Object Inspector 에서 각각의 컬럼을 선택하여 Title 의 Caption Property 를 바꾸면 된다. Title 왼쪽의 [>] 를 누르면 Title 에 부속된 Property 가 확장된다.

(1) 생성 (CREATE)
CREATE TABLE 을 사용한다. 같은 폴더내에 SQLite3 가 없으면 에러가 발생한다. pass.db 가 없으면 새로 만들어서 테이블을 생성한다. SQL 문장을 입력시에 SQLQuery1.SQL.Add 로 추가하고 SQLQuery1.ExecSQL; 로 실행한다. 트랜잭션은 SQLTransaction1.Commit; 으로 마무리하는 것을 잊지 말아야 한다.

procedure TMainForm.CreateDB(); 
var 
  NewFile: boolean; 
begin 
  SQLite3Connection1.Close(); //항상 닫았는지 확인 
  SQLQuery1.SQL.Clear; 

  try 
    NewFile := not FileExists(SQLite3Connection1.DatabaseName); 
    //이미 데이터베이스 있는가 확인 

    if NewFile then 
    begin 
      try 
        SQLite3Connection1.Open; 
        SQLTransaction1.Active := True; 

        SQLQuery1.SQL.Add('CREATE TABLE "DATA"('); 
        SQLQuery1.SQL.Add(' "PK" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'); 
        SQLQuery1.SQL.Add(' "NameKorean" VARCHAR(20) NOT NULL,'); 
        SQLQuery1.SQL.Add(' "NameEnglish" VARCHAR(20) ,'); 
        SQLQuery1.SQL.Add(' "WebSite" VARCHAR(30) ,'); 
        SQLQuery1.SQL.Add(' "ID" VARCHAR(20) NOT NULL,'); 
        SQLQuery1.SQL.Add(' "PW" VARCHAR(20) NOT NULL,'); 
        SQLQuery1.SQL.Add(' "Memo" VARCHAR(300) );'); 

        SQLQuery1.SQL.Add('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "PK" );'); 

        SQLQuery1.ExecSQL; 
        SQLTransaction1.Commit;   //꼭 넣을것 잊지말고 

        QuestionDlg('DB 생성', '데이터베이스 ' + SQLite3Connection1.DatabaseName + ' 가 만들어졌습니다.', mtCustom, [mrYes, '확인'], '') ; 
        btnClearClick(nil); 
      except 
        ShowMessage('데이터베이스를 만들 수 없습니다.'); 
      end; 
    end; 
  except 
    ShowMessage('파일이 존재하지 않습니다.'); 
  end; 
end;                                               

(2) 추가 (INSERT)
아무것도 입력되어 있지 않으면 경고창을 보여주고 재입력을 요구한다. SQLQuery1 을 이용하여 SQL 문장을 전달한다. ':' 로 파라메터를 대신 전달할 수 있다.  ':NameKorean' 으로 SQL 문장에 파라메터로 지정하면 이 파라메터에 다른 입력 컨트롤의 값을 대입할 수 있다.  파스칼의 대입문 ':=' 와 모양이 비슷하므로 착각하기 쉽다. '= :' 는 ':=' 와 다르다는 점을 기억하자.

 

procedure TMainForm.btnInsertClick(Sender: TObject); 
begin 
  if (EditNameKorean.Text = '') or (EditID.Text = '') or (EditPW.Text = '') then 
  begin 
    QuestionDlg('입력오류', '필수항목을 꼭 입력해 주세요.', mtCustom, [mrYes, '확인'], '') ; 
    btnClearClick(nil); 
    Exit; 
  end; 

  SQLite3Connection1.Close; 
  SQLQuery1.SQL.Clear; 

  try 
    SQLite3Connection1.Open; 
    SQLTransaction1.Active := True; 

    SQLQuery1.SQL.Add('INSERT INTO DATA (NameKorean, NameEnglish, WebSite, ID, PW, Memo)'); 
    SQLQuery1.SQL.Add(' VALUES (:NameKorean, :NameEnglish, :WebSite, :ID, :PW, :Memo)'); 

    SQLQuery1.Params.ParamByName('NameKorean').AsString := EditNameKorean.Text; 
    SQLQuery1.Params.ParamByName('NameEnglish').AsString := EditNameEnglish.Text; 
    SQLQuery1.Params.ParamByName('WebSite').AsString := EditWebSite.Text; 
    SQLQuery1.Params.ParamByName('ID').AsString := EditID.Text; 
    SQLQuery1.Params.ParamByName('PW').AsString := EditPW.Text; 
    SQLQuery1.Params.ParamByName('Memo').AsString := Memo.Lines.Text; 

    SQLQuery1.ExecSQL; 
    SQLTransaction1.Commit; 

    btnClearClick(nil); 
    UpdateGridAll('Last'); 
  except 
    ShowMessage('자료를 추가하지 못했습니다.'); 
  end; 
end;                            

(3) 삭제 (DELETE)

삭제를 위해서 현재 Row 값을 저장하는 Currow 을 멤버 변수로 지정한다. DataSource1 의 DataChange 이벤트 핸들러에 Currow := SQLQuery1.FieldByName('PK').Value; 를 추가하여 현재의 Row 값을 저장한다.

procedure TMainForm.btnDeleteClick(Sender: TObject);
begin
  if QuestionDlg('레코드 삭제', '정말 삭제하시겠습니까?', mtCustom,
  [mrYes, '예', mrNo, '아니요'], '') = mrNo then Exit;

  SQLite3Connection1.Close; //Close, Clear 시작전 꼭 확인
  SQLQuery1.SQL.Clear;      //sqlquery1.sql.clear 를 주의할것  sqlquery1.clear 가 아니다.

  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;

    SQLQuery1.SQL.Add('DELETE FROM DATA WHERE PK = :Currow'); //현재 위치의 레코드 삭제

    SQLQuery1.Params.ParamByName('Currow').Value := Currow;

    SQLQuery1.ExecSQL;
    SQLTransaction1.Commit;

    btnClearClick(nil);
    UpdateGridAll('First'); //삭제 후에는 처음 레코드로 이동한다.
  except
    ShowMessage('삭제 실패했습니다.');
  end;
end;                                    

(4) 수정 (UPDATE)
현재의 레코드를 기록한 후에 UPDATE 명령어로 수정해준다. 

procedure TMainForm.btnUpdateRowClick(Sender: TObject);
begin
  if QuestionDlg('레코드 수정', '정말 수정하시겠습니까?', mtCustom,
  [mrYes, '예', mrNo, '아니요'], '') = mrNo then Exit;

  SQLite3Connection1.Close();
  SQLQuery1.SQL.Clear;

  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;

    SQLQuery1.SQL.Add('UPDATE DATA SET NameKorean = :NameKorean,');
    SQLQuery1.SQL.Add('NameEnglish = :NameEnglish, ');
    SQLQuery1.SQL.Add('WebSite = :WebSite,');
    SQLQuery1.SQL.Add('ID = :ID, ');
    SQLQuery1.SQL.Add('PW = :PW, ');
    SQLQuery1.SQL.Add('Memo = :Memo ');
    SQLQuery1.SQL.Add(' WHERE PK = :Currow');

    SQLQuery1.Params.ParamByName('NameKorean').AsString := EditNameKorean.Text;
    SQLQuery1.Params.ParamByName('NameEnglish').AsString := EditNameEnglish.Text;
    SQLQuery1.Params.ParamByName('WebSite').AsString := EditWebSite.Text;
    SQLQuery1.Params.ParamByName('ID').AsString := EditID.Text;
    SQLQuery1.Params.ParamByName('PW').AsString := EditPW.Text;
    SQLQuery1.Params.ParamByName('Memo').AsString := Memo.Lines.Text;
    SQLQuery1.Params.ParamByName('Currow').Value := Currow;

    SQLQuery1.ExecSQL;
    SQLTransaction1.Commit;

    btnClearClick(nil);
    UpdateGridAll('Current');
  except
    ShowMessage('수정오류');
  end;
end;                                                  

(5) 검색 (SEARCH)
폼 화면 위에 ActionList 를 올려 놓는다. 단축키를 사용할 수 있게 해준다. ActionList 를 더블클릭하면 에디터가 팝업된다.

[+] 를 클릭하여 Action1, Action2 를 만들고 Object Inspector 에서 Action1 을 선택한다. Properties 탭에서 Name 은 'Search Action' 으로 변경하고 ShortCut 은 'Ctrl+F' 로 지정한다. Events 탭에서 OnExecute 이벤트를 더블 클릭하여 이벤트 핸들러를 작성한다. Ctrl+F 를 누르면 자동으로 검색에디트에 포커스가 가서 검색할 수 있게 된다.

procedure TMainForm.SearchActionExecute(Sender: TObject);
begin
  EditSearch.SetFocus;
end;                     

검색에디트에서는 한글이름 또는 영문이름 일부라도 입력되면 검색되도록 하였다. 

procedure TMainForm.btnSearchClick(Sender: TObject);
begin
  if (EditSearch.Text = '') then Exit;

  SQLite3Connection1.Close();
  SQLQuery1.SQL.Clear;

  try
    SQLite3Connection1.Open;

    SQLQuery1.SQL.Add('SELECT * FROM DATA WHERE ');
    SQLQuery1.SQL.Add('(NameKorean LIKE :NameKorean) OR ');
    SQLQuery1.SQL.Add('(NameEnglish LIKE :NameEnglish)');

    SQLQuery1.Params.ParamByName('NameKorean').Value := '%' + EditSearch.Text + '%';
    SQLQuery1.Params.ParamByName('NameEnglish').Value := '%' + EditSearch.Text + '%';

    SQLQuery1.Open;

    DataSource1.DataSet := SQLQuery1;
    DBGrid1.DataSource := DataSource1;
    DBGrid1.AutoFillColumns := True;
  except
    ShowMessage('검색할 수 없습니다.');
  end;
end;                                 

(6) 백업 (BACKUP)
비밀번호는 수정이 잘못 일어나면 곤란하므로 자주 백업을 해주어야 한다. 수정한 날자를 파일명에 삽입해서 이전에 기록한 비밀번호를 찾아갈 수 있도록 하였다. 
만약 특정 레코드를 실수로 삭제한후에 나중에서야 실수임을 발견했다면 백업한 DB 에서 단서를 찾을 수 있을 것이다.
프리파스칼에서 기본으로 제공하는 기능이므로 uses 에 Sqlite3backup 를 추가한다.

procedure TMainForm.BackupSQL(Conn: TSQLite3Connection; FileName: String);
var
  f: String;
  BK: TSQLite3Backup;
begin
  f := ChangeFileEXT(FileName, '') + ' [Backup ' +
    FormatDateTime('yyyy.mm.dd hh-nn-ss', Now) + '].db';
  BK := TSQLite3Backup.Create;
  BK.Backup(Conn, f);
  BK.Free;
  QuestionDlg('DB 백업', 'DB 가 ' + f + '  로 백업되었습니다.', mtCustom, [mrYes, '확인'], '');
end;                              

3편에서는 한글입력이 필요할때는 자동으로 입력모드를 한글로 바꾸는 방법과, 클립보드 복사, 인터넷 브라우저를 실행시켜 특정 웹사이트로 이동하는 방법을 구현해 본다.

3편에서 계속
https://appleii.tistory.com/176

라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (1) 폼 디자인
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (2) 데이터베이스
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (3) 기타기능
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (4) 엑셀파일 출력 I
라자루스로 만들어 보는 비밀번호 관리 프로그램 [뭐였더라] (5) 엑셀파일 출력 II