Singularity on the Plane

Pada artikel sebelumnya, telah diperkenalkan SQLite yaitu mesin SQL yang kecil dan cepat. Kalau kamu memerlukan akses basis data pada program kamu, kemungkinan besar SQLite adalah pilihan terbaik sebab deployment-nya tidak rumit (SQLite akan menjadi bagian program kamu dan basis datanya disimpan pada 1 berkas).

SQLite memiliki binding untuk banyak bahasa, termasuk untuk bahasa-bahasa di lingkungan pemrograman .NET. Di artikel ini kita akan menggunakan C# (pada .NET 2.0) untuk membuat dan mengakses basis data SQLite.

(Tutorial ini spesifik Windows. Di lain waktu akan diposting cara yang bisa digunakan di Linux+Mono.)

Instalasi dan kompilasi

Ada banyak cara untuk menggunakan SQLite dari .NET, dan yang akan digunakan di tutorial ini adalah System.Data.SQLite.dll. Pertama-tama, unduh dan jalankan installer-nya. Setelah selesai, kamu akan menemukan berkas System.Data.SQLite.dll yang secara default ada di C:\Program Files\SQLite.NET\bin.

(Saat artikel ini ditulis, di halaman depan situs System.Data.SQLite terdapat pranala untuk mengunduh versi stabil dan alpha. Pembuatan artikel ini menggunakan versi stabilnya.)

Kalau kamu menggunakan IDE seperti SharpDevelop dan Visual C# Express, tambahkan referensi ke berkas tersebut pada proyek kamu (karena tidak di-install di GAC, kamu perlu mem-browse lokasinya. Kamu juga perlu menambahkan referensi ke System.Data.dll yang merupakan assembly bawaan .NET (ada di GAC). Ini contoh screenshot hasilnya pada SharpDevelop 2.1:

Kalau kamu melakukan kompilasi dari terminal, jangan lupa mereferensikan kedua assembly tersebut, misalnya:

csc /r:"C:\\Program Files\\SQLite.NET\\bin\\System.Data.SQLite.dll",System.Data.dll Test.cs

Dengan mereferensikan kedua assembly tersebut, kita bisa menggunakan kelas-kelas yang terdefinisi di dalamnya. Namun agar program kamu bisa berjalan, System.Data.SQLite.dll harus berada pada direktori yang sama dengan program kamu. IDE seperti SharpDevelop akan menyalin System.Data.SQLite.dll secara otomatis, namun pada kompilasi manual kamu harus menyalin berkasnya sendiri.

Cek lagi semua langkah yang diberikan di bagian ini kalau nanti menjumpai masalah saat mengkompilasi atau menjalankan program kamu.

Pengenalan ADO.NET

Dalam Framework .NET, ADO.NET adalah teknologi yang memungkinkan kita untuk berinteraksi dengan sumber data. Pada umumnya sumber data itu adalah basis data, namun bisa saja sumber datanya adalah berkas teks atau berkas spreadsheet.

Kelas yang digunakan pada ADO.NET tergantung sumber datanya. Misalnya, untuk berinteraksi dengan Microsoft SQL Server digunakan kelas seperti SqlConnection, SqlCommand, dan SqlDataReader. Untuk berinteraksi dengan basis data melalui ODBC, kelas-kelas yang digunakan misalnya OdbcConnection, OdbcCommand, dan OdbcDataReader. Walaupun nama kelasnya untuk tiap sumber data berbeda, cara penggunaanya sama karena kelas-kelas tersebut diturunkan dari kelas dasar yang sama di ADO.NET.

System.Data.SQLite.dll memberikan implementasi ADO.NET untuk mengakses basis data SQLite. Istilah teknisnya adalah "ADO.NET data provider". Kita akan mempelajari cara menggunakan kelas SQLiteConnection, SQLiteCommand, dan SQLiteDataReader untuk berinteraksi dengan basis data SQLite.

Membuat tabel dan mengisinya dengan data

Karena kelas-kelas pada System.Data.SQLite.dll terdapat di namespace System.Data.SQLite, kita akan menggunakan statement using di awal program:

using System.Data.SQLite;

Objek SQLiteConnection berfungsi untuk membuka suatu berkas basis data SQLite. Jika berkas yang ditentukan tidak ada, maka akan dibuat berkas baru. Kita menginstansiasi objek SQLiteConnection baru dengan memberinya string koneksi sebagai argumen. Di string tersebutlah kita tentukan nama berkasnya. Ini contohnya:

SQLiteConnection conn = new SQLiteConnection("Data Source=test.db3");

Setelah nanti koneksi dibuka dengan memanggil metode Open, kita bisa menggunakan objek SQLiteCommand untuk menjalankan perintah-perintah SQL. Secara umum urutan langkah saat menggunakan objek koneksi adalah:

1. Meninstansiasi objek SQLiteConnection.
2. Membuka koneksi dengan metode Open.
3. Objek lain, misalnya objek SQLiteCommand, menggunakan koneksi tersebut.
4. Menutup koneksi dengan metode Close.

Inilah contoh program di mana objek SQLiteCommand menggunakan koneksi yang sudah berjalan untuk membuat tabel:

using System;
using System.Data.SQLite;

class Test
{
   static void Main()
   {
      SQLiteConnection conn = null;
      try
      {
         conn = new SQLiteConnection("Data Source=test.db3");
         conn.Open();

         SQLiteCommand cmd =
            new SQLiteCommand("CREATE TABLE mahasiswa" +
               "(nim INTEGER PRIMARY KEY, " +
               "nama TEXT, prodi CHAR(2));",
               conn);
         cmd.ExecuteNonQuery();

         cmd.CommandText = "INSERT INTO mahasiswa(nama, prodi) " +
            "VALUES('Linus Torvalds', 'IK');";
         cmd.ExecuteNonQuery();

         cmd.CommandText = "INSERT INTO mahasiswa " +
            "VALUES(9374, 'Agro', 'IK');";
         cmd.ExecuteNonQuery();
      }
      finally
      {
         if(conn != null)
         {
            conn.Close();
         }
      }
   }
}

(string yang panjang sengaja dipecah agar tampil baik di situs ini)

Bisa dilihat bahwa setelah objek SQLiteConnection dibuat, yang pertama kali dilakukan adalah membuka koneksinya dengan metode Open. Melakukan operasi pada koneksi yang belum dibuka akan menghasilkan eksepsi.

Saat membuat objek SQLiteCommand, kita memberikan perintah SQL sebagai argumen pertama dan objek koneksi yang telah kita buat sebelumnya sebagai argumen kedua. Lalu kita menjalankan perintah SQL tersebut dengan memanggil metode ExecuteNonQuery. ExecuteNonQuery kita gunakan karena kita tidaklah mencari data pada basis data. Kalau operasi yang kita lakukan adalah pencarian data misalnya dengan perintah SQL SELECT, maka metode yang digunakan berbeda (akan dibahas belakangan).

Untuk menjalankan perintah SQL lain, kita tidak perlu membuat objek baru. Yang perlu dilakukan adalah mengubah perintahnya melalui properti CommandText dan memanggil ExecuteNonQuery kembali. Kita juga bisa merubah koneksi yang ingin digunakan melalui properti Connection jika diperlukan.

Perhatikan bahwa koneksi ditutup di blok finally. Ini adalah agar koneksi tetap ditutup walaupun terjadi eksepsi di blok try. Sebagai contoh, kalau program tersebut dijalankan untuk yang kedua kalinya maka akan terjadi eksepsi saat tabel akan dibuat. Ini karena tabel mahasiswa sudah ada (dibuat saat program pertama kali dijalankan). Walaupun terjadi eksepsi, blok finally tetap akan dijalankan sehingga koneksi bisa ditutup.

Sebelum koneksi ditutup, dicek apakah variabel conn bernilai null. Ini karena bisa saja eksepsi terjadi saat objek koneksi diinstansiasi (misal string koneksi ngawur), dan pada kasus tersebut variabel conn akan bernilai null (nilai awalnya). Mencoba memanggil Close pada variabel yang bernilai null akan melempar eksepsi yang lain lagi dan kita tidak ingin hal itu terjadi.

Melakukan query dengan perintah SELECT

Untuk melakukan query, kita akan berkenalan dengan dua metode baru pada kelas SQLiteCommand yaitu ExecuteScalar dan ExecuteReader. ExecuteScalar digunakan kalau hasil query-nya berupa sebuah nilai, misalnya jumlah baris yang ada di tabel. ExecuteReader digunakan kalau haisl query-nya berupa baris-baris pada basis data kita.

ExecuteReader akan mengembalikan suatu objek SQLiteDataReader. Dari objek tersebut kita bisa mengambil nilai kolom pada baris-baris hasil query. Contoh programnya yang diikuti penjelasannya adalah sebagai berikut:

using System;
using System.Data.SQLite;

class Test
{
   static void Main()
   {
      SQLiteConnection conn = null;
      SQLiteDataReader rdr = null;
      try
      {
         conn =
            new SQLiteConnection("Data Source=test.db3");
         conn.Open();

         SQLiteCommand cmd =
            new SQLiteCommand("SELECT COUNT(*) FROM mahasiswa;",
               conn);

         long jumlah = (long)cmd.ExecuteScalar();
         Console.WriteLine("Jumlah: " + jumlah);

         cmd.CommandText = "SELECT * FROM mahasiswa;";
         rdr = cmd.ExecuteReader();

         while(rdr.Read())
         {
            Console.WriteLine("===");
            Console.WriteLine("NIM: " + rdr[0]);
            Console.WriteLine(”Nama: ” + rdr["nama"]);
         }
      }
      finally
      {
         if(rdr != null)
         {
            rdr.Close();
         }
         if(conn != null)
         {
            conn.Close();
         }
      }
   }
}

Ini contoh outputnya:

Jumlah: 2
===
NIM: 1
Nama: Linus Torvalds
===
NIM: 9374
Nama: Agro

Pada query pertama di contoh tersebut (SELECT COUNT(*) FROM mahasiswa;), hasil query-nya adalah sebuah angka yang menunjukkan jumlah baris pada tabel mahasiswa. Karena hasil querynya berupa nilai tunggal, maka kita cukup memanggil ExecuteScalar. Dalam kasus ini, hasil kembaliannya adalah integer 64 bit (tipe long pada C#) yang dibungkus sebagai Object. Oleh karena itu, kita perlu menggunakan cast untuk menyimpannya sebagai variabel long.

Query berikutnya (SELECT * FROM mahasiswa;) mengembalikan baris-baris pada tabel mahasiswa, oleh karenanya digunakan metode ExecuteReader. Metode tersebut mengembalikan objek SQLiteDataReader yang disimpan di variabel rdr.

Nilai pada baris-baris hasil dibaca dalam perulangan while. Di sini digunakan metode Read pada SQLiteDataReader yang berfungsi untuk membaca baris berikutnya dan mengembalikan true jika berhasil. Jika semua baris sudah dibaca, metode tersebut akan mengembalikan false sehingga eksekusi program bisa keluar dari perulangan.

Untuk mengambil nilai kolom tertentu, kita bisa menggunakan indeksnya (misal rdr[0] untuk kolom pertama) maupun nama kolomnya (misal rdr["nama"]). Agar kodenya mudah dibaca disarankan untuk menggunakan nama kolomnya.

Sebagaimana koneksi, reader juga perlu ditutup kalau sudah selesai digunakan. Digunakan teknik yang sama seperti cara menutup koneksi, yaitu pada blok finally.

Penutup

SQLite adalah mesin SQL yang menyediakan binding untuk banyak bahasa, salah satunya untuk bahasa-bahasa .NET. Di tutorial ini telah dibahas penggunaan kelas-kelas pada System.Data.SQLite.dll yang merupakan penyedia data ADO.NET. Dengan pendekatan ini, yang perlu dilakukan saat mendistribusikan program hanyalah menyertakan System.Data.SQLite.dll, tanpa perlu meng-install server SQL di komputer klien.

Waktu dulu aku mengambil mata kuliah basis data, aku menginstall server SQL kelas berat yaitu MySQL. Padahal, yang dulu ingin kulakukan hanyalah mengenal dan bermain-main dengan perintah SQL.

Berkas zip MySQL 3 yang waktu itu kusalin dari teman ukurannya sekitar 12 MB. Installer Windows MySQL 5 “Essential” ukurannya sekitar 22 MB. Adakah alternatifnya?

Mari berkenalan dengan SQLite, sebuah mesin SQL yang kecil dan cepat. Program dengan lisensi public domain ini sudah mencapai versi 3 saat artikel ini ditulis. Pada dasarnya, SQLite adalah pustaka bahasa C untuk dilink oleh program yang membutuhkannya (sqlite3.dll untuk Windows dan sqlite-3.x.x.so untuk Linux). Tersedia juga binding untuk banyak bahasa lain (misalnya System.Data.SQLite.dll untuk .NET). Contoh program yang menggunakan SQLite adalah Firefox 3 untuk basis data bookmarknya dan Banshee (pemutar musik C#) untuk basis data lagunya. Jadi dia bukanlah program server yang berjalan sendiri dan menunggu koneksi dari program lain sebagaimana MySQL.

Seluruh database (definisi, tabel, indeks, dan data) pada SQLite disimpan dalam satu berkas. Berkas ini cross platform.

Tapi kembali ke permasalahan semula. Kita ingin bermain-main dengan perintah SQL, bukannya membuat program yang menggunakan basis data. Untuk keperluan ini, di halaman download SQLite disediakan program konsol yang bisa membuat dan membaca berkas basis data SQLite. Program konsol itu sendiri merupakan contoh program yang memanfaatkan pustaka SQLite (tapi pada program ini SQLitenya dicompile bersama, tidak berada di file pustaka terpisah). Ukurannya sangat kecil, misalnya sqlite3-3.3.17.bin.gz untuk Linux berukuran 173.46 KiB dan sqlite-3_3_17.zip berukuran 174.64 KiB!

Tutorial Program Konsol SQLite

PS: Diasumsikan pembaca sudah mengerti perintah-perintah SQL dasar sehingga tidak ada penjelasan panjang lebar tentangnya. Walaupun begitu, mungkin artikel ini juga bisa dimanfaatkan sebagai tutorial kotor tentang perintah SQL.

Programnya dijalankan dengan memberikan satu argumen berupa nama berkas basis data yang diinginkan. Jika berkasnya tidak ada, maka berkas baru akan dibuat. Ekstensi berkasnya bisa sembarang. Kita akan menggunakan db3 untuk menandakan bahwa itu adalah berkas database yang dibuat SQLite versi 3. Contohnya adalah:

$ sqlite3 test.db3

(Untuk pengguna Linux, ganti nama sqlite3-3.3.17.bin atau buat symbolic link seperlunya)

Setelah mengetikkan perintah tersebut kita akan dibawa ke konsol SQL!

SQLite version 3.3.17
Enter ".help" for instructions
sqlite>

Untuk melihat tabel-tabel yang ada pada basis data yang sedang digunakan, masukkan perintah .table:

sqlite> .table
sqlite>

Perintah yang diawali titik (seperti .table) adalah perintah khusus program konsol ini. Tentu saja, kalau basis datanya baru dibuat tidak akan ada tabel apa-apa.

Kita bisa membuat tabel baru dengan perintah SQL CREATE, misalnya:

sqlite> CREATE TABLE mahasiswa(
   ...> nim INTEGER PRIMARY KEY,
   ...> nama TEXT,
   ...> prodi CHAR(2));
sqlite> .table
mahasiswa
sqlite>

Perintah untuk membuatnya adalah “CREATE TABLE mahasiswa(nim INTEGER PRIMARY KEY, nama TEXT, prodi CHAR(2));“. Walaupun begitu, pada contoh penulisan perintahnya dipecah menjadi 3 baris. Perintah SQL diakhiri dengan titik koma, jadi selama kita belum menuliskan titik koma program tersebut masih menunggu input di baris berikutnya (ditandai dengan prompt “...>“). Dengan perintah .table, bisa dilihat bahwa tabelnya memang sudah tercipta.

Berikutnya adalah memasukkan data pada tabel tersebut dengan perintah SQL INSERT dan menampilkan isi tabel dengan perintah SQL SELECT:

sqlite> INSERT INTO mahasiswa(nama, prodi) VALUES('Linus Torvalds', 'IK');
sqlite> INSERT INTO mahasiswa(nama, prodi) VALUES('Richard Stallman', 'FI');
sqlite> INSERT INTO mahasiswa VALUES(9374, 'Agro', 'IK');
sqlite> INSERT INTO mahasiswa VALUES(9374, 'Wijaya', 'IK');
SQL error: PRIMARY KEY must be unique
sqlite> INSERT INTO mahasiswa(nama, prodi) VALUES('Ace Ventura', 'KH');
sqlite> SELECT * FROM mahasiswa;
1|Linus Torvalds|IK
2|Richard Stallman|FI
9374|Agro|IK
9375|Ace Ventura|KH
sqlite>

Bisa dilihat bahwa kalau kunci primer nim tidak ditentukan, maka nilainya akan ditentukan secara otomatis dimulai dari 1. Terlihat jelas juga bahwa tidak mungkin memasukkan dua baris dengan kunci primer yang sama.

Contoh perintah SELECT lain yang hanya menampilkan mahasiswa ilmu komputer (IK):

sqlite> SELECT nama,nim FROM mahasiswa WHERE prodi='IK';
Linus Torvalds|1
Agro|9374
sqlite>

Karena Richard Stallman ingin pindah dari prodi filsafat (FI) ke ilmu komputer, mari kita bantu dia dengan perintah SQL UPDATE:

sqlite> UPDATE mahasiswa SET prodi='IK' WHERE nim=2;
sqlite> SELECT * FROM mahasiswa;
1|Linus Torvalds|IK
2|Richard Stallman|IK
9374|Agro|IK
9375|Ace Ventura|KH
sqlite>

Sayangnya di ilmu komputer ada aturan bahwa rambut tidak boleh gondrong. Richard Stallman, orang yang keras kepala, lebih memilih dikeluarkan daripada harus mencukur rambutnya. Terpaksa perintah SQL DELETE dipakai:

sqlite> DELETE FROM mahasiswa WHERE nim=2;
sqlite> SELECT * FROM mahasiswa;
1|Linus Torvalds|IK
9374|Agro|IK
9375|Ace Ventura|KH
sqlite>

PS: Rambut Agro belum cukup gondrong untuk ditindak.

Dan terakhir, karena mahasiswanya tidak ada yang mampu membayar kuliah (terlalu mahal!), jadinya tidak ada yang membayar kuliah. Universitasnya pun bangkrut sehingga tabel mahasiswa tidak ada gunanya lagi. Perintah SQL DROP digunakan untuk menghapus tabel:

sqlite> DROP TABLE mahasiswa;
sqlite> .table
sqlite>

Untuk keluar dari program, gunakan perintah .q, .quit, atau .exit. Bisa dicek bahwa berkas test.db3 (atau nama lain yang dipilih) telah tercipta.

Bagaimana, keren kan? Setelah mengetahui tentang SQLite, kamu bisa merekomendasikan program ini pada teman-temanmu yang mengambil basis data.

Untuk tutorial mengenai hal-hal yang lebih kompleks (misal trigger), kunjungi tutorial di freshmeat. Untuk daftar perintah-perintah SQL yang didukung SQLite, kunjungi halaman di situs resminya. Ada benchmark (agak kuno) untuk melihat bahwa SQLite tidaklah lambat. Untuk penggunaan SQLite pada .NET (C#), mungkin kapan-kapan aku bakal bikin tutorialnya :)…