KAL
Menu

ATTACH dan Multi Basis Data

Query lintas basis data dengan ATTACH dan pola pemindahan data antar berkas.

Daftar isi

Pernyataan ATTACH DATABASE membuka lebih dari satu berkas basis data dalam satu koneksi, lalu memberi setiap berkas sebuah nama skema yang dipakai pada query. Dengan nama itu, satu pernyataan SQL dapat membaca tabel dari dua berkas sekaligus, memindahkan baris antarberkas, dan membungkus semuanya dalam satu transaksi. Topik ini memeragakan semuanya dengan dua basis data perpustakaan, yaitu katalog utama dan berkas arsipnya, beserta batas transaksi yang menyertainya.

Nama skema dan ATTACH#

Setiap koneksi SQLite selalu memiliki satu basis data utama dengan nama skema main, ditambah satu skema sementara bernama temp untuk tabel yang hidup hanya selama koneksi. ATTACH DATABASE menambah satu nama skema lagi yang menunjuk ke berkas lain, dan nama itu bebas dipilih selama belum dipakai. Referensi tabel kemudian menyebut skemanya secara eksplisit, misalnya main.buku untuk tabel pada berkas utama dan arsip.buku untuk tabel pada berkas yang baru di-ATTACH.

sql 
ATTACH ':memory:' AS arsip;
CREATE TABLE arsip.buku (judul TEXT);
INSERT INTO arsip.buku VALUES ('Anak Sungai');
SELECT judul FROM arsip.buku;
text Keluaran 
Anak Sungai

Contoh di atas meng-ATTACH basis data memori sebagai arsip, sehingga eksperimen skema lintas basis data dapat dicoba tanpa berkas. Nama skema hanya hidup pada koneksi yang membuatnya, sehingga dua proses dapat meng-ATTACH berkas yang sama dengan nama yang berbeda tanpa saling mengganggu. Apabila jalur berkas relatif dipakai pada ATTACH, jalurnya dibaca terhadap direktori kerja proses SQLite, bukan terhadap berkas basis data utamanya. Jumlah basis data yang dapat di-ATTACH juga terbatas, dengan bawaan sepuluh basis data tambahan per koneksi, dan keterikatan skema berlaku hanya sampai koneksi ditutup.

Dua basis data dan query lintas skema#

Dua berkas berikut meniru keadaan perpustakaan yang terbagi dua, yaitu katalog utama berisi dua buku dan berkas arsip berisi dua buku lama. Keduanya dibangun lewat dua proses sqlite3 yang terpisah agar benar-benar berdiri sendiri.

bash 
# katalog utama
sqlite3 perpustakaan.db <<'SQL'
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL
);
INSERT INTO buku (judul, penulis) VALUES
  ('Kisah Kota Tua', 'Sari Wulandari'),
  ('Anak Sungai', 'Rahmat Hidayat');
SQL
# berkas arsip yang terpisah
sqlite3 arsip.db <<'SQL'
CREATE TABLE buku (
  id      INTEGER PRIMARY KEY,
  judul   TEXT NOT NULL,
  penulis TEXT NOT NULL
);
INSERT INTO buku (judul, penulis) VALUES
  ('Musim di Ujung Jalan', 'Lina Marlina'),
  ('Lembah Kabut', 'Dewi Anggraini');
SQL

Blok berikut membuka perpustakaan.db, meng-ATTACH arsip.db sebagai skema arsip, lalu menyalin seluruh baris arsip ke katalog utama dalam satu pernyataan INSERT yang membaca dari arsip.buku.

bash 
# ATTACH dan penyalinan lintas basis data dalam satu koneksi
sqlite3 perpustakaan.db <<'SQL'
ATTACH DATABASE 'arsip.db' AS arsip;
INSERT INTO main.buku (judul, penulis)
  SELECT judul, penulis FROM arsip.buku;
SELECT id, judul FROM main.buku ORDER BY id;
SQL
text Keluaran 
1|Kisah Kota Tua
2|Anak Sungai
3|Musim di Ujung Jalan
4|Lembah Kabut

Perintah INSERT INTO main.buku SELECT FROM arsip.buku membaca dua baris arsip dan menuliskannya ke katalog utama dengan id otomatis 3 dan 4. Nilai id itu diisi sendiri oleh SQLite karena kolom id berupa INTEGER PRIMARY KEY, dan SQLite selalu mencari nilai rowid di atas nilai terbesar yang ada. Keluaran berupa mode list bawaan dengan pemisah | karena sesi CLI tidak diatur mode lainnya. ATTACH berlaku hanya selama koneksi itu hidup, sehingga ATTACH dan query lintas skema ditulis dalam sesi yang sama. Verifikasi pada berkas arsip menegaskan sifat INSERT SELECT, yaitu menyalin tanpa memindahkan, karena baris sumbernya tetap tinggal dua.

bash 
# baris sumber tidak ikut pindah
sqlite3 arsip.db "SELECT count(*) FROM buku;"
text Keluaran 
2

Pola pemindahan data#

Pemindahan sungguhan membutuhkan dua langkah yang terbungkus satu transaksi, yaitu menyalin baris ke tujuan terlebih dahulu, kemudian menghapusnya dari sumber. Urutan itu tidak boleh dibalik, karena menghapus dahulu berarti barisnya hilang bila salinan gagal dibuat. Pola ini lazim dipakai untuk mengarsipkan data lama agar katalog utama tetap ramping tanpa kehilangan riwayatnya. Kedua pernyataan ditulis dalam satu transaksi agar penyalinan dan penghapusan terjadi bersamaan atau sama-sama batal.

bash 
# pola arsipkan lalu hapus dalam satu transaksi
sqlite3 perpustakaan.db <<'SQL'
ATTACH DATABASE 'arsip.db' AS arsip;
BEGIN;
INSERT INTO arsip.buku (judul, penulis)
  SELECT judul, penulis FROM main.buku WHERE id = 1;
DELETE FROM main.buku WHERE id = 1;
COMMIT;
SELECT judul FROM main.buku ORDER BY id;
SQL
text Keluaran 
Anak Sungai
Musim di Ujung Jalan
Lembah Kabut

Setelah COMMIT dijalankan, katalog utama kehilangan Kisah Kota Tua yang id-nya 1, dan berkas arsip menjadi pemilik baris itu seperti terlihat pada verifikasi berikut. Kolom tambahan pada tabel arsip, misalnya penanda tanggal pengarsipan, dapat diisi pada INSERT yang sama dengan nilai yang ditetapkan program. Pola dua langkah ini juga berlaku untuk tabel dengan struktur berbeda, karena pernyataan SELECT sumber dapat menyaring dan mengubah kolom sebelum masuk ke tabel tujuan. Untuk baris yang jumlahnya besar, pemindahan dijalankan per kelompok id agar tiap transaksinya tetap pendek.

bash 
# baris yang diarsipkan kini berada pada berkas arsip
sqlite3 arsip.db "SELECT judul FROM buku ORDER BY id;"
text Keluaran 
Musim di Ujung Jalan
Lembah Kabut
Kisah Kota Tua

Transaksi lintas basis data dan DETACH#

SQLite mengamankan transaksi yang menyentuh beberapa basis data ter-ATTACH sekaligus. Commit terhadap transaksi semacam itu bersifat atomik bagi seluruh basis data yang terlibat, dengan syarat basis data utamanya bukan :memory: dan mode jurnalnya bukan WAL, dan proses commitnya mengurus berkas satu demi satu sehingga penulisannya tetap serial. Contoh berikut membungkus satu penulisan pada tiap berkas dalam transaksi yang kemudian dibatalkan.

bash 
# ROLLBACK membatalkan penulisan pada kedua basis data sekaligus
sqlite3 perpustakaan.db <<'SQL'
ATTACH DATABASE 'arsip.db' AS arsip;
BEGIN;
INSERT INTO main.buku (judul, penulis) VALUES ('Pelangi di Desa', 'Budi Santoso');
INSERT INTO arsip.buku (judul, penulis) VALUES ('Laut Tenang', 'Citra Kirana');
ROLLBACK;
SELECT
  (SELECT count(*) FROM main.buku)  AS buku_main,
  (SELECT count(*) FROM arsip.buku) AS buku_arsip;
SQL
text Keluaran 
3|3

Kedua hitungan kembali ke 3 karena ROLLBACK membatalkan penyisipan pada main dan pada arsip sekaligus, dan inilah makna atomik lintas berkas. Untuk melepaskan basis data sebelum koneksi ditutup, pernyataan DETACH DATABASE membuang nama skemanya, dan tabel pada skema itu langsung tidak terjangkau lagi.

bash 
# DETACH lalu aksesnya gagal
sqlite3 perpustakaan.db <<'SQL'
ATTACH DATABASE 'arsip.db' AS arsip;
DETACH DATABASE arsip;
SELECT judul FROM arsip.buku;
SQL
text Keluaran 
Parse error near line 3: no such table: arsip.buku

Error pada keluaran disengaja untuk menunjukkan bahwa setelah DETACH, SQLite tidak lagi mengenali skema arsip, meskipun berkas arsip.db sendiri tetap utuh dan dapat di-ATTACH kembali kapan pun. Dengan ATTACH, INSERT SELECT, dan transaksi lintas skema, perpindahan data antar berkas menjadi pekerjaan SQL biasa tanpa program penghubung, dan cadangan masing-masing berkas tetap mengikuti kaidah pada topik strategi backup.

Ringkasan#

  • ATTACH DATABASE menambah berkas basis data ke koneksi yang sedang hidup dengan nama skema pilihan, sedangkan basis data utama selalu bernama main.
  • Query lintas basis data menyebut skema secara eksplisit, misalnya INSERT INTO main.buku SELECT dari arsip.buku.
  • Transaksi yang menyentuh beberapa basis data ter-ATTACH tetap atomik selama main bukan :memory: dan mode jurnalnya bukan WAL, meskipun penulisannya dikerjakan serial.
  • DETACH DATABASE melepaskan skema sehingga tabelnya langsung tidak terjangkau, tanpa merusak berkasnya.

Referensi#