Integrity Check dan Perbaikan

SQLite tergolong database yang sangat tahan banting, tetapi berkasnya tetap bisa rusak: listrik terputus di saat yang salah, disk gagal menulis, atau ada program lain yang menyunting berkas secara langsung. Kerusakan yang terdeteksi pagi ini jauh lebih murah daripada kerusakan yang ditemukan tiga bulan kemudian, karena kerusakan cenderung menyebar saat halaman rusak dipakai ulang. Bab ini membahas alat deteksi integrity_check, quick_check, dan foreign_key_check, lalu prosedur penyelamatan data dengan .recover.

PRAGMA integrity_check #

Pemeriksaan paling menyeluruh adalah PRAGMA integrity_check, yang memvalidasi struktur internal seluruh berkas:

sqlite3 toko.db "PRAGMA integrity_check;"

Output yang Dihasilkan:

ok

Database sehat menjawab satu baris: ok. Jika ada masalah, keluarannya menjadi daftar temuan — satu baris per masalah. Pemeriksaan yang dilakukan engine mencakup:

• Struktur B-tree setiap tabel dan index: halaman tersambung benar, sel berada di halaman yang tepat, dan urutan rowid serta kunci rapi.
• Kecocokan isi index dengan isi tabel — setiap baris tabel harus punya pasangan di index-nya.
• Batas ukuran payload setiap sel terhadap ukuran halaman.
• Keabsahan nilai terhadap constraint seperti NOT NULL.
• Konsistensi freelist dengan jumlah halaman berkas.

Pemeriksaan ini membaca seluruh database, sehingga pada berkas berukuran puluhan gigabyte butuh waktu dan I/O yang tidak kecil.

quick_check: Versi Cepat #

Untuk pemeriksaan rutin, PRAGMA quick_check menjalankan subset pemeriksaan yang sama dan mengembalikan hasil lebih cepat:

sqlite3 toko.db "PRAGMA quick_check;"

Output yang Dihasilkan:

ok

Dokumentasi SQLite menyebut satu pengorbanan utamanya: quick_check melewatkan verifikasi kecocokan isi index terhadap isi tabel, pemeriksaan termahal pada database yang kaya index.

foreign_key_check: Mendeteksi Pelanggaran FK #

Penghapusan baris induk tanpa peduli anak-anaknya meninggalkan baris yatim — dan karena enforcement foreign key hanya aktif per koneksi (bawaan CLI: mati), pelanggaran bisa masuk tanpa suara. PRAGMA foreign_key_check memindai semua relasi FK dan melaporkan baris yang induknya hilang.

Kita pakai skema dua tabel berikut:

CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  email TEXT NOT NULL
);
CREATE TABLE pesanan (
  id INTEGER PRIMARY KEY,
  pelanggan_id INTEGER REFERENCES pelanggan(id),
  total INTEGER NOT NULL
);
INSERT INTO pelanggan (nama, email) VALUES
  ('Budi','[email protected]'),('Siti','[email protected]'),('Ani','[email protected]');
INSERT INTO pesanan (pelanggan_id, total) VALUES (1, 150000), (2, 275000);

Karena PRAGMA foreign_keys mati secara bawaan, INSERT berikut lolos tanpa protes meski pelanggan 99 tidak ada:

sqlite3 toko.db "INSERT INTO pesanan (pelanggan_id, total) VALUES (99, 50000);"

Pemeriksaan FK lalu menemukannya:

sqlite3 toko.db "PRAGMA foreign_key_check;"

Output yang Dihasilkan:

pesanan|3|pelanggan|0

Empat kolomnya berurutan: tabel anak (pesanan), rowid baris bermasalah (3), tabel induk (pelanggan), dan nomor constraint dalam definisi tabel (0). Sebagai pembanding, dengan enforcement aktif INSERT seperti itu langsung ditolak:

sqlite3 toko.db "PRAGMA foreign_keys = ON; INSERT INTO pesanan (pelanggan_id, total) VALUES (99, 50000);"

Output yang Dihasilkan:

Error: stepping, FOREIGN KEY constraint failed (19)

Penyebab Umum Kerusakan #

Kerusakan hampir selalu berasal dari luar SQLite, bukan dari engine-nya:

• Listrik atau sistem mati di tengah penulisan, terutama bila PRAGMA synchronous = OFF melemahkan jaminan flush ke disk (trade-off ini dibahas pada chapter Journal Modes dan WAL).
• Proses dihentikan paksa di tengah transaksi — jarang berujung rusak karena journal/WAL memungkinkan recovery, tetapi tetap risiko bila digabung dengan synchronous yang longgar.
• Program lain menyunting berkas database secara langsung — editor teks, skrip yang menimpa byte, atau tool backup yang memotong berkas.
• Kegagalan hardware: sector disk rusak, bit rot pada media penyimpanan.

Menyelamatkan Data dengan .recover #

Ketika kerusakan sudah terjadi, prioritasnya bukan memperbaiki berkas, melainkan mengekstrak sebanyak mungkin data yang masih terbaca. Kita simulasikan kerusakan dengan menimpa 300 byte di tengah salinan berkas dengan nol:

cp toko.db rusak.db
dd if=/dev/zero of=rusak.db bs=1 seek=12000 count=300 conv=notrunc

Pemeriksaan integritas langsung melaporkan kerusakannya:

sqlite3 rusak.db "PRAGMA integrity_check;"

Output yang Dihasilkan:

*** in database main ***
Tree 3 page 3 cell 1: Rowid 0 out of order
Tree 3 page 3 cell 0: Rowid 0 out of order
Fragmentation of 17 bytes reported as 0 on page 3
NULL value in pesanan.total
NULL value in pesanan.total
NULL value in pesanan.total

Di sinilah .recover masuk: command ini memindai berkas halaman demi halaman, mengambil semua baris yang masih bisa dibaca, lalu menuliskannya ulang sebagai skrip SQL:

sqlite3 rusak.db .recover > pulih.sql
sqlite3 pulih.db ".read pulih.sql"
sqlite3 pulih.db "SELECT count(*) FROM pelanggan; SELECT count(*) FROM pesanan;"

Output yang Dihasilkan:

          defensive off
3
0

Baris defensive off berasal dari skrip pulih.sql sendiri: baris pertamanya, .dbconfig defensive off, melepas mode aman CLI agar skrip hasil .recover bisa menulis ulang skema sistem — biarkan saja dan tidak perlu ditiru di skrip sendiri. Hasilnya setengah berhasil: 3 baris pelanggan selamat karena halamannya tidak terkena timpaan nol, sedangkan seluruh baris pesanan hilang karena halamannya rusak. .recover memang penyelamat, bukan penyembuh — data pada halaman yang benar-benar hancur tidak bisa dikembalikan. Untuk kasus parah, opsi --lost-and-found TABLE mengumpulkan baris yang tabel asalnya sudah tak teridentifikasi ke satu tabel khusus agar bisa diaudit manual.

Dokumentasi resmi SQLite (sqlite.org/corruption) merangkum urutan penanganan yang bijak:

1. Hentikan semua penulisan ke berkas yang rusak segera — setiap tulis baru bisa memperparah.
2. Buat salinan berkas rusak sebelum mencoba apa pun, termasuk salinan cadangan kedua.
3. Coba .dump; jika berhenti di tengah, simpan hasil parsialnya.
4. Lanjutkan dengan .recover untuk halaman yang tidak terjangkau .dump.
5. Pulihkan hasil ke berkas baru, jalankan PRAGMA integrity_check, lalu bandingkan jumlah baris dengan sumber lain (backup, log aplikasi).