KAL
Menu

Pengelolaan Skema

CREATE TABLE, indeks, dan migration manual.

Daftar isi

Skema adalah struktur yang menampung data, dan skema yang baik ditetapkan sebelum data pertama disisipkan. Topik ini membahas penyusunan tabel dengan CREATE TABLE beserta batasan PRIMARY KEY, NOT NULL, DEFAULT, dan FOREIGN KEY, serta pembuatan indeks dengan CREATE INDEX. Pembahasan juga mencakup migration manual yang mencatat setiap perubahan skema dalam daftar berurutan. Seluruh contoh dijalankan sebagai satu skrip yang dapat dieksekusi berulang dengan hasil yang sama.

Menyusun tabel dengan CREATE TABLE#

Pernyataan DDL (Data Definition Language) seperti CREATE TABLE dieksekusi melalui exec() karena tidak mengembalikan baris. SQLite menerima nama tipe yang luas dan mengelompokkannya ke dalam lima afinitas penyimpanan, yaitu NULL, INTEGER, REAL, TEXT, dan BLOB, sehingga penulisan VARCHAR(100) tetap sah meskipun diperlakukan seperti TEXT. Batasan kolom menegakkan aturan data langsung pada definisi tabel, yaitu NOT NULL untuk menolak baris tanpa nilai, DEFAULT untuk mengisi nilai yang tidak diberikan, dan CHECK untuk menolak nilai di luar syarat.

Kolom kunci utama ditulis INTEGER PRIMARY KEY, yang sekaligus menjadi alias rowid milik SQLite. Nilainya diisi otomatis dengan bilangan yang naik apabila penyisipan tidak menyebutnya, sehingga kata kunci AUTOINCREMENT tidak diperlukan untuk kasus umum. Antartabel, batasan REFERENCES menautkan kolom ke kunci utama tabel lain dan membentuk foreign key, misalnya buku_id INTEGER NOT NULL REFERENCES buku (id) pada tabel peminjaman.

Relasi yang dipakai sepanjang topik ini memuat dua tabel, yaitu buku sebagai tabel induk dan peminjaman sebagai tabel anak yang merujuk buku. Setiap baris peminjaman menyimpan buku_id agar bukunya dapat dilacak, dan tanggal_kembali sengaja dibolehkan bernilai NULL karena buku yang masih dipinjam belum memiliki tanggal kembali.

bukuidPRIMARY KEYjudulpenulisstokpeminjamanidPRIMARY KEYbuku_idFOREIGN KEYpeminjamtanggal_pinjamtanggal_kembaliREFERENCESSatu buku dapat memiliki banyak baris peminjaman
Relasi satu-ke-banyak antara tabel buku dan peminjaman

Indeks untuk mempercepat pencarian#

Indeks adalah struktur terurut terpisah yang menyimpan nilai satu kolom beserta alamat barisnya, sehingga pencarian atas kolom tersebut tidak perlu memeriksa seluruh tabel. Indeks dibuat dengan CREATE INDEX, misalnya CREATE INDEX idx_peminjaman_buku ON peminjaman (buku_id) untuk mempercepat pencarian peminjaman atas satu buku tertentu. Kunci utama dan batasan UNIQUE sudah membawa indeksnya sendiri, sehingga indeks tambahan diperuntukkan bagi kolom yang sering muncul pada WHERE, JOIN, atau ORDER BY tanpa menjadi kunci utama.

Indeks mempercepat pembacaan dengan menambah biaya penulisan, karena setiap penyisipan dan pembaruan pada tabelnya juga harus memperbarui setiap indeksnya. Kolom yang jarang dicari tetapi sering ditulis sebaiknya tidak diindeks, sedangkan kolom rujukan seperti buku_id pada contoh ini memenuhi kedua syaratnya, yaitu sering dicari dan jarang berubah. Indeks juga dapat mencakup lebih dari satu kolom untuk pola pencarian yang selalu berpasangan, misalnya kolom buku_id bersama tanggal_pinjam pada daftar peminjaman terbaru satu buku. Daftar indeks yang terbentuk dapat dibaca dari tabel sistem sqlite_master, sebagaimana dilakukan skrip pada bagian berikutnya.

Migration manual yang dapat diulang#

Migration adalah langkah bernomor yang mengubah skema dari satu keadaan ke keadaan berikutnya, dan seluruh langkah yang sudah diterapkan membentuk sejarah skema. Bentuk manualnya berupa daftar langkah terurut dalam skrip, ditambah satu tabel pencatat bernama migrations yang menyimpan nama langkah beserta waktu penerapannya. Setiap eksekusi membandingkan daftar langkah dengan catatan yang ada, lalu menerapkan hanya langkah yang belum tercatat, sehingga skrip yang sama dapat dijalankan berulang tanpa efek ganda.

Skrip berikut menerapkan tiga langkah, yaitu membuat tabel buku, membuat tabel peminjaman, lalu membuat indeks pada peminjaman. Setiap langkah dibungkus transaksi agar pembuatan skema dan pencatatannya berjalan sebagai satu kesatuan, dan nama langkah yang disimpan mencatat urutan sejarah.

php migrasi.php 
<?php
declare(strict_types=1);

$pdo = new PDO('sqlite:perpustakaan.sqlite', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('PRAGMA foreign_keys = ON');

$pdo->exec('CREATE TABLE IF NOT EXISTS migrations (
    nama TEXT PRIMARY KEY,
    dijalankan_pada TEXT NOT NULL
)');

$migrasi = [
    '0001_buat_tabel_buku' => 'CREATE TABLE buku (
        id INTEGER PRIMARY KEY,
        judul TEXT NOT NULL,
        penulis TEXT NOT NULL,
        tahun_terbit INTEGER NOT NULL,
        stok INTEGER NOT NULL DEFAULT 0
    )',
    '0002_buat_tabel_peminjaman' => 'CREATE TABLE peminjaman (
        id INTEGER PRIMARY KEY,
        buku_id INTEGER NOT NULL REFERENCES buku(id),
        peminjam TEXT NOT NULL,
        tanggal_pinjam TEXT NOT NULL,
        tanggal_kembali TEXT
    )',
    '0003_indeks_peminjaman_buku' => 'CREATE INDEX idx_peminjaman_buku ON peminjaman (buku_id)',
];

$sudah = $pdo->query('SELECT nama FROM migrations')->fetchAll(PDO::FETCH_COLUMN);
$diterapkan = 0;

foreach ($migrasi as $nama => $sql) {
    if (in_array($nama, $sudah, true)) {
        continue;
    }

    $pdo->beginTransaction();
    $pdo->exec($sql);
    $stmt = $pdo->prepare('INSERT INTO migrations (nama, dijalankan_pada) VALUES (:nama, :waktu)');
    $stmt->execute([':nama' => $nama, ':waktu' => date('Y-m-d H:i:s')]);
    $pdo->commit();
    $diterapkan++;
    echo "Diterapkan: $nama\n";
}

if ($diterapkan === 0) {
    echo "Tidak ada migrasi baru, skema sudah terkini\n";
}

$tabel = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name")->fetchAll(PDO::FETCH_COLUMN);
echo 'Tabel: ', implode(', ', $tabel), "\n";

$indeks = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'index' AND name NOT LIKE 'sqlite_%' ORDER BY name")->fetchAll(PDO::FETCH_COLUMN);
echo 'Indeks: ', implode(', ', $indeks), "\n";

Eksekusi pertama pada direktori yang belum memuat berkas basis data menerapkan ketiga langkah sekaligus, lalu menampilkan isi sqlite_master sebagai bukti struktur skema.

text Keluaran 
Diterapkan: 0001_buat_tabel_buku
Diterapkan: 0002_buat_tabel_peminjaman
Diterapkan: 0003_indeks_peminjaman_buku
Tabel: buku, migrations, peminjaman
Indeks: idx_peminjaman_buku

Daftar tabel memuat migrations karena tabel pencatat itu sendiri adalah bagian skema, dan kolom dijalankan_pada menyimpan waktu penerapan setiap langkahnya. Eksekusi kedua pada berkas yang sama tidak menemukan langkah yang belum tercatat, sehingga skrip hanya melaporkan bahwa skema sudah terkini.

bash 
# jalankan ulang skrip migrasi yang sama
php migrasi.php
text Keluaran 
Tidak ada migrasi baru, skema sudah terkini
Tabel: buku, migrations, peminjaman
Indeks: idx_peminjaman_buku
bash 
# hapus berkas database contoh setelah eksperimen selesai
rm -f perpustakaan.sqlite

Perubahan skema berikutnya ditambahkan sebagai langkah baru bernomor lanjutan, misalnya 0004_tambah_kolom_rak. Definisi langkah yang sudah diterapkan tidak pernah diubah, karena catatan pada tabel migrations akan tidak cocok lagi dengan skema yang nyata.

Menegakkan foreign key dengan PRAGMA#

SQLite mem-parsing batasan REFERENCES dan menyimpan definisinya, tetapi hanya menegakkannya apabila PRAGMA foreign_keys = ON diaktifkan pada koneksi yang menjalankan pernyataan tersebut. Tanpa pengaturan ini, baris dalam tabel peminjaman yang merujuk pada buku_id yang tidak ada tetap dapat disimpan. Akibatnya, tabel dapat berisi banyak baris dengan referensi yang tidak valid tanpa menghasilkan peringatan apa pun. Skrip migrasi.php mengaktifkan pragma tersebut pada bagian awal, segera setelah koneksi berhasil dibuat.

Pengaturan ini berlaku per koneksi dan tidak diwarisi oleh koneksi lain, sehingga setiap skrip yang membuka koneksi baru menyetelnya kembali. Pragma juga tidak berlaku di dalam transaksi yang sudah terbuka, sehingga penyetelannya diletakkan sebelum transaksi pertama dimulai. Aksi tambahan seperti ON DELETE CASCADE menghapus baris anak ketika baris induknya dihapus. Aksi semacam ini dituliskan pada batasan kolom dan berjalan dengan syarat yang sama, yaitu penegakan foreign key yang aktif.

Kesalahan umum#

  • Mengubah langkah migration yang sudah diterapkan. Catatan pada tabel migrations menandai langkah itu selesai, sehingga perubahannya tidak pernah dijalankan dan skema nyata tidak sesuai sejarahnya. Tambahkan langkah baru bernomor lanjutan untuk mengoreksi skema.
  • Melupakan PRAGMA foreign_keys = ON pada koneksi baru. Batasan REFERENCES tetap tersimpan dalam definisi tabel, tetapi tidak ditegakkan sehingga data dengan referensi yang tidak valid dapat masuk tanpa peringatan. Aktifkan pragma tersebut pada setiap koneksi yang menggunakan foreign key.
  • Menambahkan AUTOINCREMENT tanpa alasan. Kolom INTEGER PRIMARY KEY sudah diisi naik secara otomatis, sedangkan AUTOINCREMENT membuat tabel tambahan dan melarang pemakaian ulang id yang barisnya telah dihapus. Gunakan kata kunci itu hanya bila larangan pemakaian ulang memang disyaratkan.
  • Menyebarkan pernyataan DDL di banyak tempat kode. CREATE TABLE yang tersebar di beberapa berkas membuat keadaan skema tidak dapat ditelusuri dari satu daftar, dan tabel baru berisiko tidak terbentuk pada pemasangan yang bersih. Kumpulkan seluruh perubahan skema ke dalam langkah migration yang tercatat agar setiap pemasangan menghasilkan struktur yang sama.

Ringkasan#

  • CREATE TABLE melalui exec() menetapkan struktur beserta batasannya, dan INTEGER PRIMARY KEY sekaligus menjadi alias rowid yang terisi naik secara otomatis.
  • CREATE INDEX mempercepat pencarian pada kolom yang sering difilter atau dirujuk, dengan biaya penulisan tambahan pada tabel yang diindeksnya.
  • Migration manual menyimpan langkah skema dalam daftar terurut dan tabel migrations, sehingga skripnya dapat dieksekusi berulang dan hanya langkah yang belum tercatat yang diterapkan.
  • Penegakan foreign key pada SQLite menuntut PRAGMA foreign_keys = ON pada setiap koneksi yang memakainya.

Referensi#