Pengurusan & Penggunaan Pangkalan Data SQLite 3

SQLite 3 ialah sistem pengurusan pangkalan data hubungan tertanam yang sangat popular. Dengan ciri-ciri seperti ringan, tidak memerlukan proses pelayan yang berdiri sendiri serta penyimpanan data dalam satu fail tunggal, ia menjadi pilihan ideal untuk aplikasi mudah alih, sistem tertanam, aplikasi desktop berskala kecil dan juga persekitaran pembangunan tempatan. SQLite 3 mudah diurus dan digunakan. Artikel ini bertujuan membantu pengguna ServBay dengan panduan terperinci bagaimana mengurus serta menggunakan SQLite 3 secara berkesan di dalam persekitaran ServBay — meliputi pengesahan pemasangan, sambungan, operasi asas, sandaran & pemulihan data, pengoptimuman prestasi, serta langkah-langkah keselamatan.

Ringkasan

Sebagai persekitaran pembangunan web tempatan yang komprehensif, ServBay sudah sedia terbina dengan SQLite 3 dan pustaka pembangunan berkaitan. Anda boleh mengakses dan mengendalikannya terus melalui baris arahan, atau menggunakan bahasa pengaturcaraan yang disokong ServBay (seperti PHP, Node.js, Python, Go, dan lain-lain) — tanpa pemasangan tambahan. Ini sangat memudahkan penggunaan SQLite 3 dalam pembangunan tempatan.

Pemasangan dan Konfigurasi SQLite 3

ServBay secara lalai telah mengintegrasikan SQLite 3, jadi anda tidak perlu melakukan sebarang langkah pemasangan tambahan. Fail boleh laksana (executable) SQLite 3 serta pustaka berkaitan sudah termasuk dalam pakej perisian ServBay.

Sahkan Pemasangan SQLite 3

Anda boleh segera mengesahkan sistem anda boleh mengakses fail boleh laksana SQLite 3 yang disediakan ServBay serta melihat maklumat versi dengan jalankan arahan berikut di terminal:

sqlite3 --version

Jika arahan ini mengeluarkan nombor versi SQLite 3 (contohnya 3.41.2 2023-03-26 11:52:19 ...), ini bermakna SQLite 3 telah berjaya diintegrasikan dan boleh digunakan.

Mewujudkan & Menyambung ke Pangkalan Data SQLite 3

Pangkalan data SQLite 3 hakikatnya hanyalah satu fail tunggal. Secara lalai, ServBay mencadangkan anda meletakkan fail pangkalan data di direktori /Applications/ServBay/db/sqlite, namun ini bukanlah satu kemestian; anda boleh memilih lokasi lain mengikut keperluan projek anda. Penciptaan atau sambungan pangkalan data biasanya dilakukan melalui alat baris arahan sqlite3 atau pustaka SQLite dalam pelbagai bahasa pengaturcaraan.

Sambungan Menggunakan Alat Baris Arahan

Menggunakan alat baris arahan sqlite3 adalah cara paling terus untuk mengurus pangkalan data SQLite 3.

1. Cipta atau sambung pangkalan data: Jalankan arahan berikut di terminal. Jika fail di lokasi spesifik belum wujud, sqlite3 akan menciptakannya secara automatik. Jika fail sudah wujud, ia akan menyambung kepada pangkalan data tersebut.

   sqlite3 /Applications/ServBay/db/sqlite/servbay.db

   (Contoh pangkalan data dinamakan servbay.db untuk identiti jenama ServBay)

   Selepas memasuki prompt sqlite>, anda boleh menjalankan arahan SQL atau .commands.

2. Keluar dari SQLite 3: Di prompt sqlite>, masukkan arahan .exit untuk keluar dari alat baris arahan tersebut.

   .exit

Sambungan Menggunakan Bahasa Pengaturcaraan

Beberapa bahasa pengaturcaraan yang disokong ServBay telah menyediakan pustaka untuk menyambung dan mengendalikan pangkalan data SQLite 3. Berikut ialah contoh untuk PHP dan Node.js.

Contoh PHP

ServBay biasanya sudah menyediakan ekstensi SQLite 3 untuk PHP (php-sqlite3). Anda boleh menggunakan kelas terbina SQLite3 untuk menyambung ke pangkalan data.

<?php
// Tentukan laluan fail pangkalan data, disyorkan letakkan di direktori db/sqlite ServBay
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// Sambung ke pangkalan data SQLite 3
// Jika fail belum wujud, pembina SQLite3 akan menciptanya secara automatik
$db = new SQLite3($database_file);

if (!$db) {
    die("Tidak dapat menyambung ke pangkalan data SQLite 3: " . $db->lastErrorMsg());
}

echo "Berjaya menyambung ke pangkalan data SQLite 3: " . $database_file . "\n";

// Cipta jadual (jika belum wujud)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "Jadual 'users' disahkan atau telah dicipta.\n";

// Masukkan data
$name = 'ServBay Demo User';
$email = '[email protected]';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "Data berjaya dimasukkan.\n";
} else {
    echo "Kemasukan data gagal: " . $db->lastErrorMsg() . "\n";
}


// Dapatkan data
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "Hasil pertanyaan:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Nama: " . $row['name'] . ", Emel: " . $row['email'] . "\n";
    }
} else {
     echo "Pertanyaan gagal: " . $db->lastErrorMsg() . "\n";
}


// Tutup sambungan pangkalan data
$db->close();
unset($db); // Bebaskan sumber
echo "Sambungan pangkalan data telah ditutup.\n";

?>

Contoh Node.js

Untuk menggunakan SQLite 3 dalam Node.js, pasang dahulu pakej sqlite3 melalui npm. Buka terminal, masuk ke direktori projek anda, kemudian jalankan:

npm install sqlite3

Setelah itu, anda boleh menggunakan pustaka ini dalam kod Node.js untuk mengendalikan pangkalan data:

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// Tentukan laluan fail pangkalan data, gunakan path.join bagi keserasian merentas platform
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// Sambung ke pangkalan data SQLite 3
// Jika fail belum wujud, sqlite3.Database akan menciptanya
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('Tidak dapat menyambung ke pangkalan data SQLite 3:', err.message);
  } else {
    console.log('Berjaya menyambung ke pangkalan data SQLite.');
  }
});

// Laksanakan arahan secara berurutan untuk jaminan urutan
db.serialize(() => {
  // Cipta jadual (jika belum wujud)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('Penciptaan jadual gagal:', err.message);
    } else {
      console.log('Jadual "users" disahkan atau telah dicipta.');
    }
  });

  // Masukkan data
  const name = 'ServBay Demo User';
  const email = '[email protected]';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT adalah kod ralat kekangan unik
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`Pengguna '${name}' (${email}) sudah wujud, kemasukan diabaikan.`);
      } else {
         console.error('Kemasukan data gagal:', err.message);
      }
    } else {
      console.log(`Satu baris data berjaya dimasukkan, ID: ${this.lastID}`);
    }
  });

  // Dapatkan data
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('Hasil pertanyaan:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Nama: ${row.name}, Emel: ${row.email}`);
    });
  });
});

// Tutup sambungan pangkalan data
db.close((err) => {
  if (err) {
    console.error('Penutupan sambungan pangkalan data gagal:', err.message);
  } else {
    console.log('Sambungan pangkalan data telah ditutup.');
  }
});

Nota: Dalam aplikasi sebenar, pastikan laluan fail pangkalan data adalah selamat serta kawal ralat dan isu akses serentak (concurrency) dengan baik.

Pengurusan Pangkalan Data

Operasi SQL Asas

Setelah bersambung ke pangkalan data SQLite 3, anda boleh menjalankan arahan SQL standard untuk pengurusan data. Contohnya:

1. Cipta jadual: Takrifkan struktur data.

   CREATE TABLE products (
       product_id INTEGER PRIMARY KEY AUTOINCREMENT,
       product_name TEXT NOT NULL,
       price REAL DEFAULT 0.00
   );

2. Masukkan data: Tambahkan rekod baharu ke jadual.

   INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
   INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

3. Dapatkan data: Ambil rekod dari jadual.

   SELECT * FROM products;
   SELECT product_name, price FROM products WHERE price > 10.00;

4. Kemaskini data: Tukar rekod sedia ada.

   UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

5. Padam data: Keluarkan rekod.

   DELETE FROM products WHERE product_id = 1;
   DELETE FROM products; -- Padam semua data dari jadual

6. Padam jadual: Keluarkan keseluruhan struktur serta data jadual.

   DROP TABLE products;

Anda boleh menjalankan arahan-arahan ini terus dalam alat baris arahan sqlite3 atau dengan kaedah yang disediakan pustaka SQLite pada bahasa pengaturcaraan.

Sandaran & Pemulihan

Sandaran dan pemulihan untuk pangkalan data SQLite 3 amat mudah kerana seluruh pangkalan data hanya satu fail.

Sandaran Pangkalan Data

Kaedah paling asas untuk sandaran adalah dengan menyalin fail pangkalan data. ServBay menyarankan anda menyimpan fail sandaran di /Applications/ServBay/backup/sqlite demi pengurusan yang teratur.

Salin fail pangkalan data untuk tujuan sandaran melalui baris arahan:

# Cipta direktori sandaran (jika belum wujud)
mkdir -p /Applications/ServBay/backup/sqlite

# Salin fail pangkalan data untuk sandaran
# Disyorkan untuk sertakan tarikh/masa dalam nama fail untuk versi sandaran berbeza
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

Anda juga boleh menggunakan arahan .backup dalam alat baris arahan sqlite3 untuk sandaran 'hot' (sandaran semasa pangkalan data dibuka sambil digunakan), tetapi untuk pembangunan tempatan, salin fail secara langsung biasanya sudah cukup mudah.

Pemulihan Pangkalan Data

Untuk pulihkan pangkalan data, hanya perlu salin fail sandaran ke lokasi asal. Sebelum melakukan pemulihan, sangat disarankan untuk menghentikan sebarang aplikasi atau servis ServBay yang mungkin sedang mengakses pangkalan data tersebut bagi mengelak masalah kunci fail atau ketidakselarasan data.

# Contoh: memulihkan salinan sandaran terbaharu
# Cari fail sandaran terbaru, contoh: servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# Periksa sama ada fail sandaran ditemui
if [ -z "$LATEST_BACKUP" ]; then
  echo "Ralat: Tiada fail sandaran SQLite ditemui."
  exit 1
fi

echo "Fail sandaran yang akan dipulihkan: $LATEST_BACKUP"

# Berhenti servis atau aplikasi berkaitan... (bergantung pada konfigurasi ServBay anda)
# Contoh: jika anda ada aplikasi PHP yang akses DB ini, pastikan proses PHP tiada

# Salin fail sandaran untuk menimpa fail pangkalan data semasa
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "Pemulihan pangkalan data selesai."

# Mula semula servis atau aplikasi berkaitan...

Nota Penting: Kaedah salinan fail secara langsung boleh menyebabkan kerosakan data jika fail pangkalan data sedang ditulis aktif. Jika berlaku penulisan serentak, seeloknya hentikan semua servis dahulu, atau guna API sandaran 'hot' SQLite yang lebih canggih (lazimnya menerusi pustaka bahasa pengaturcaraan).

Pengoptimuman Prestasi

SQLite 3 menawarkan pelbagai arahan PRAGMA dan strategi indeks untuk membantu mengoptimumkan prestasi pangkalan data.

Pengoptimuman Indeks

Membina indeks pada lajur yang kerap digunakan untuk penapisan carian (WHERE), penyambungan (JOIN) atau penyusunan (ORDER BY) boleh menaikkan kecepatan pertanyaan.

-- Bina indeks unik pada lajur email jadual users
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Bina indeks biasa pada lajur product_name jadual products
CREATE INDEX idx_products_name ON products(product_name);

Pengoptimuman Pertanyaan

Gunakan arahan EXPLAIN QUERY PLAN untuk melihat bagaimana SQLite menjalankan arahan SQL anda — membantu mengenal pasti isu prestasi.

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

Analisa pelan pertanyaan sama ada indeks digunakan, atau berlaku 'SCAN TABLE' (imbasan seluruh jadual).

Pengoptimuman Konfigurasi (PRAGMA)

Arahan PRAGMA digunakan untuk mengawal konfigurasi masa jalan SQLite 3. Beberapa PRAGMA berkait prestasi yang penting:

• PRAGMA journal_mode; / PRAGMA journal_mode = mode;: Tentukan mod log. Mod WAL (Write-Ahead Logging) biasanya menawarkan kebolehan serentak dan prestasi tulis lebih baik, terutamanya apabila terdapat banyak pembaca dan tulis serentak. Mod lalai mungkin DELETE atau TRUNCATE. Tukar ke WAL boleh kurangkan masalah kuncian tulis.

  PRAGMA journal_mode = WAL;

• PRAGMA synchronous; / PRAGMA synchronous = level;: Tentukan tahap ketepatan penulisan sistem fail. Tetapan OFF (atau 0) memberikan prestasi tulis lebih tinggi tetapi transaksi terkini boleh hilang jika berlaku kegagalan sistem. Tetapan FULL (atau 1) atau NORMAL (atau 2) pula lebih selamat namun lambat. Untuk pembangunan tempatan, biasanya OFF sudah memadai.

  PRAGMA synchronous = OFF;

• PRAGMA cache_size; / PRAGMA cache_size = pages;: Tetapkan berapa banyak halaman pangkalan data yang disimpan dalam ingatan. Saiz cache lebih besar mengurangkan I/O cakera dan naikkan prestasi, tapi guna lebih banyak memori.

  PRAGMA cache_size = -20000; -- Set cache ke 20MB (nombor negatif = ukuran dalam KB)

Nota: Kebanyakan PRAGMA hanya berkesan untuk sesi sambungan sekarang. Untuk tetapan kekal, anda perlu tetapkan setiap kali sambungan dibuat atau set permulaan aplikasi.

Pengurusan Keselamatan

Walaupun SQLite 3 adalah pangkalan data berasaskan fail, keselamatan asas masih penting dalam persekitaran pembangunan tempatan.

Kebenaran Fail

Pastikan kebenaran sistem fail SQLite ditetapkan dengan betul — hanya pengguna yang menjalankan ServBay atau proses pembangunan yang berkaitan boleh membaca/menulis fail tersebut. Ini mencegah akses tanpa kebenaran.

# Contoh: Tukar pemilik fail ke pengguna semasa, hanya pemilik boleh baca/tulis
# Sila sesuaikan dengan pengguna sebenar bagi perkhidmatan ServBay
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

Penyulitan Data

SQLite 3 secara lalai tidak menawarkan penyulitan data terbina. Jika aplikasi pembangunan tempatan anda melibatkan data sensitif dan mahu fail pangkalan data disulitkan, boleh pertimbangkan pelanjutan SQLite yang menyokong penyulitan seperti SQLCipher. Ia memerlukan pustaka tambahan dan API tertentu untuk membuka serta mengendalikan fail pangkalan data yang disulitkan.

Soal Jawab & Penyelesaian Masalah Biasa

Tidak Dapat Menyambung ke Pangkalan Data SQLite 3

1. Semak laluan fail pangkalan data:
   Pastikan laluan fail dalam sambungan anda atau baris arahan adalah betul serta fail wujud di lokasi berkenaan.
2. Semak kebenaran fail:
   Sahkan pengguna ServBay atau terminal anda boleh baca/tulis fail tersebut. Guna ls -l /Applications/ServBay/db/sqlite/servbay.db untuk semakan, ubah suai dengan chmod atau chown jika perlu.
3. Adakah fail pangkalan data rosak?
   Cuba sambung menggunakan alat baris arahan sqlite3. Jika gagal atau keluar ralat, fail mungkin sudah rosak. Cubalah pulihkan dari sandaran.

Masalah Penguncian Pangkalan Data (Database is locked)

SQLite 3 akan mengunci fail pangkalan datanya semasa operasi tulis. Jika operasi tulis berlaku serentak atau kadang kala operasi baca tertentu (mengikut mod log), anda mungkin temui ralat penguncian.

1. Semak akses serentak:
   Pastikan tiada aplikasi, skrip, atau proses baris arahan lain sedang mengakses fail pangkalan data yang sama pada masa yang sama terutamanya untuk operasi tulis. Dalam pembangunan tempatan, ini biasanya berlaku jika anda jalankan beberapa pelayan pembangunan yang akses pangkalan data sama serentak.

2. Guna mod WAL:
   Mengaktifkan PRAGMA journal_mode = WAL; boleh memperbaiki prestasi baca-tulis serentak. Mode WAL benarkan bacaan serentak ketika sedang berlaku penulisan, dan mengurangkan konflik kunci secara signifikan. Tetapkan PRAGMA selepas sambungan dibuka sebagai amalan baik.

3. Tangani ralat penguncian:
   Dalam kod, apabila menghadapi ralat penguncian, jangan terus gagal; sebaliknya laksanakan mekanisme cubaan semula (retry) iaitu tunggu seketika sebelum mencuba semula operasi. Kebanyakan pustaka SQLite menyediakan ciri busy timeout agar ia cuba berkali-kali sebelum beri ralat.

   Sebagai contoh, dalam pustaka Node.js sqlite3 boleh tetapkan:

   db.configure("busyTimeout", 5000); // Tetapkan tempoh tunggu busy kepada 5000 ms (5 saat)

   Dalam kelas PHP SQLite3, tiada kaedah busyTimeout secara terus; anda mungkin perlukan logik retry pada aplikasi, atau gunakan fungsi sqlite_busy_timeout jika tersedia dan sesuai.

Rumusan

Sebagai persekitaran pembangunan web tempatan yang direka untuk pembangun, ServBay membawakan SQLite 3 yang mantap dan mudah digunakan secara terbina. Menerusi panduan terperinci dalam artikel ini, anda kini boleh mengesahkan pemasangan, menyambung, mengurus asas, menjalankan sandaran/pemulihan yang cekap, mengoptimumkan prestasi serta menjaga keselamatan pangkalan data SQLite 3 dalam ekosistem ServBay anda. SQLite 3, dengan kelebihan ringan dan mudah, adalah pilihan terbaik untuk pembangunan tempatan, ujian, dan projek berskala kecil. Digabungkan dengan alatan lain yang ditawarkan ServBay (PHP, Node.js, Python, Go, pelayan web serta pelbagai pangkalan data), anda berupaya membangun dan menguji aplikasi kompleks dengan lebih mudah.