Rutin patch database

Kesibukan “coding demi sesuap nasi” he he he , sempat membuat blog ini enggak keurus.

Untungnya ada beberapa pengunjung yang berbaik hati untuk meninggalkan komennya sehingga saya pun jadi rutin untuk mengecek blog ini.

Melakukan perubahan struktur database merupakan pekerjaan sampingan yang mau tidak mau harus kita terima dengan ikhlas jika membuat aplikasi terutama aplikasi masal, karena biasanya aplikasi tipe ini sering sekali terjadi perubahan untuk memenuhi kebutuhan klien.

Untuk contoh disini saya menggunakan database Firebird.

Firebird sendiri sudah menyediakan command line tool (isql) untuk mengeksekusi pernyataan DML (SELECT, INSERT, UPDATE dan DELETE), DDL (CREATE, DROP, ALTER) dan DCL (GRANT, REVOKE de el el).

Isql ini mirip dengan tool mysql, referensi lengkap tentang isql bisa dilihat disini.

Untuk menjalankan isql kita membutuhkan library fbclient.dll dan secara default kedua file ini biasanya ngumpul di folder “C:\Program Files\Firebird\Firebird_x_x\bin” jika Anda sudah menemukannya, copykan kedua file tersebut ke folder aplikasi Anda. Hal ini kita lakukan untuk menyederhanakan dalam penulisan kode program.

Kita ambil contoh kasus, misal pada saat rilis awal program kita mempunyai struktur tabel siswa seperti berikut :

kemudian terjadi perubahan struktur table siswa dengan adanya penambahan field NAMA_AYAH dan NAMA_IBU

perintah DDL yang kita gunakan untuk menambahkan kedua field tersebut adalah sebagai berikut :

ALTER TABLE SISWA ADD NAMA_AYAH VARCHAR(30);
ALTER TABLE SISWA ADD NAMA_IBU VARCHAR(30);

kemudian skrip diatas kita simpan dengan nama patchdb.sql

Sampai disini muncul masalah yaitu isql tidak bisa langsung mengeksekusi skrip diatas, agar skrip diatas bisa dieksekusi maka file patchdb.sql harus kita edit dengan menambahkan beberapa kode berikut :

SET SQL DIALECT 3;
CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey'; //masterkey adalah password default Firebird

sehingga isi akhir dari file patchdb.sql adalah sebagai berikut :

SET SQL DIALECT 3;
CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey'; //masterkey adalah password default Firebird

ALTER TABLE SISWA ADD NAMA_AYAH VARCHAR(30);
ALTER TABLE SISWA ADD NAMA_IBU VARCHAR(30);

Skrip diatas dengan asumsi lokasi database (DBSLS.FDB) satu folder dengan aplikasi utama. Oke sekarang saatnya kita mengeksekusi file patchdb.sql dengan memanfaatkan utility isql.

Untuk mencobanya tambahkan sebuah modul kemudian copy paste kode berikut selain itu tambahkan juga referensi Microsoft Scripting Runtime di project Anda :

Option Explicit

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Const SYNCHRONIZE   As Long = &H100000
Private Const INFINITE      As Long = &HFFFF
Private Const FILE_PATCH    As String = "patchdb.sql"

Dim fso As Scripting.FileSystemObject

Private Function fileExists(ByVal namaFile As String) As Boolean
    On Error GoTo errHandle

    If Not (Len(namaFile) > 0) Then fileExists = False: Exit Function

    Set fso = New Scripting.FileSystemObject
    fileExists = fso.fileExists(namaFile)
    Set fso = Nothing

    Exit Function
errHandle:
    fileExists = False
End Function

Private Sub patchDB()
    Dim ShellX  As Long
    Dim lPid    As Long
    Dim lHnd    As Long
    Dim lRet    As Long

    Dim cmd As String

    On Error GoTo errHandle

    cmd = Chr(34) & App.Path & "\isql.exe " & Chr(34) & " -q -i " & Chr(34) & App.Path & "\" & FILE_PATCH & Chr(34)

    'pastikan file isql.exe dan fbclient.dll ada di folder aplikasi
    If fileExists(App.Path & "\isql.exe") And fileExists(App.Path & "\fbclient.dll") Then

        'pastikan juga file patchdb.sql ada
        If fileExists(App.Path & "\" & FILE_PATCH) Then
            cmd = Chr(34) & App.Path & "\isql.exe " & Chr(34) & " -q -i " & Chr(34) & App.Path & "\" & FILE_PATCH & Chr(34)
            ShellX = Shell(cmd, vbHide)

            lPid = ShellX
            If lPid <> 0 Then
                lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)
                If lHnd <> 0 Then
                    lRet = WaitForSingleObject(lHnd, INFINITE)
                    CloseHandle (lHnd)
                End If
            End If
        End If
    End If

    Exit Sub
errHandle:
    MsgBox Err.Description, vbExclamation, "Warning"
End Sub

Public Sub Main()
    Call patchDB
End Sub

Pada contoh diatas penulisan kode :

CONNECT 'C:\Program Files\SmartLibrary\DBSLS.FDB' USER 'SYSDBA' PASSWORD 'masterkey';

masih hard-coding tentunya hal ini akan menyebabkan error jika aplikasi diinstall selain difolder “C:\Program Files\SmartLibrary”, jadi silahkan kembangkan sendiri untuk penyelesaiannya

Kesimpulannya dengan menggunakan metode sederhana seperti ini, setiap ada perubahan struktur database kita cukup mengirimkan file patchdb.sql yang berisi perubahan struktur database ke klien, gimana simple kan ?.

Selamat mencoba