Quản lý và sử dụng cơ sở dữ liệu SQLite 3

SQLite 3 là một hệ thống quản lý cơ sở dữ liệu quan hệ nhúng nhẹ, được sử dụng rộng rãi trong các ứng dụng di động, hệ thống nhúng và ứng dụng máy tính nhỏ. SQLite 3 không cần quá trình máy chủ độc lập, dữ liệu được lưu trữ trong một tệp đơn, dễ quản lý và sử dụng. Bài viết này sẽ giới thiệu chi tiết cách quản lý và sử dụng cơ sở dữ liệu SQLite 3 trong ServBay, bao gồm cài đặt, cấu hình, sao lưu, khôi phục và tối ưu hóa hiệu suất.

Cài đặt và cấu hình SQLite 3

ServBay tích hợp sẵn SQLite 3, có thể sử dụng trực tiếp trong môi trường dòng lệnh, PHP, Node.js, v.v.

Xác minh cài đặt SQLite 3

Bạn có thể xác minh xem SQLite 3 đã được cài đặt hay chưa bằng lệnh sau:

sqlite3 --version

Nếu hiển thị thông tin phiên bản, nghĩa là SQLite 3 đã được cài đặt.

Tạo và kết nối cơ sở dữ liệu SQLite 3

Cơ sở dữ liệu SQLite 3 được lưu trữ trong một tệp đơn, đường dẫn mặc định là /Applications/ServBay/db/sqlite. Bạn có thể sử dụng công cụ dòng lệnh sqlite3 hoặc thư viện SQLite của ngôn ngữ lập trình để tạo và kết nối cơ sở dữ liệu.

Kết nối bằng công cụ dòng lệnh

1. Tạo hoặc kết nối cơ sở dữ liệu:

   sqlite3 /Applications/ServBay/db/sqlite/your_database.db

2. Thoát khỏi SQLite 3:

   .exit

Kết nối bằng ngôn ngữ lập trình

Ví dụ PHP

Kết nối SQLite 3 bằng lớp SQLite3:

<?php
// Kết nối đến cơ sở dữ liệu SQLite 3
$db = new SQLite3('/Applications/ServBay/db/sqlite/your_database.db');

// Tạo bảng
$db->exec("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT)");

// Chèn dữ liệu
$db->exec("INSERT INTO mytable (name) VALUES ('Alice')");

// Truy vấn dữ liệu
$result = $db->query("SELECT * FROM mytable");
while ($row = $result->fetchArray()) {
    echo "ID: " . $row['id'] . " Name: " . $row['name'] . "\n";
}

// Đóng kết nối cơ sở dữ liệu
$db->close();
?>

Ví dụ Node.js

Kết nối SQLite 3 bằng thư viện sqlite3:

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

// Kết nối đến cơ sở dữ liệu SQLite 3
let db = new sqlite3.Database('/Applications/ServBay/db/sqlite/your_database.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

// Tạo bảng
db.run(`CREATE TABLE IF NOT EXISTS mytable (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
)`);

// Chèn dữ liệu
db.run(`INSERT INTO mytable (name) VALUES (?)`, ['Alice'], function(err) {
  if (err) {
    return console.error(err.message);
  }
  console.log(`A row has been inserted with rowid ${this.lastID}`);
});

// Truy vấn dữ liệu
db.all(`SELECT * FROM mytable`, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.id, row.name);
  });
});

// Đóng kết nối cơ sở dữ liệu
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});

Quản lý cơ sở dữ liệu

Các thao tác cơ bản

1. Tạo bảng:

   CREATE TABLE mytable (id INTEGER PRIMARY KEY, name TEXT);

2. Chèn dữ liệu:

   INSERT INTO mytable (name) VALUES ('Alice');

3. Truy vấn dữ liệu:

   SELECT * FROM mytable;

4. Cập nhật dữ liệu:

   UPDATE mytable SET name = 'Bob' WHERE id = 1;

5. Xóa dữ liệu:

   DELETE FROM mytable WHERE id = 1;

Sao lưu và khôi phục

Sao lưu cơ sở dữ liệu

Cơ sở dữ liệu SQLite 3 được lưu trữ trong một tệp đơn, sao lưu chỉ cần sao chép tệp này. Đề xuất lưu tệp sao lưu trong thư mục sau:

/Applications/ServBay/backup/sqlite

Sử dụng công cụ dòng lệnh để sao chép tệp cơ sở dữ liệu nhằm sao lưu:

cp /Applications/ServBay/db/sqlite/your_database.db /Applications/ServBay/backup/sqlite/your_database_backup.db

Khôi phục cơ sở dữ liệu

Sao chép tệp sao lưu về vị trí gốc để khôi phục:

cp /Applications/ServBay/backup/sqlite/your_database_backup.db /Applications/ServBay/db/sqlite/your_database.db

Tối ưu hóa hiệu suất

SQLite 3 cung cấp nhiều tùy chọn tối ưu hóa hiệu suất, dưới đây là một số phương pháp tối ưu phổ biến.

Tối ưu hóa chỉ mục

Đảm bảo lập chỉ mục cho các cột trong truy vấn thường dùng. Ví dụ:

CREATE INDEX idx_name ON mytable(name);

Tối ưu hóa truy vấn

Sử dụng lệnh EXPLAIN để phân tích hiệu suất truy vấn và thực hiện tối ưu hóa tương ứng. Ví dụ:

EXPLAIN QUERY PLAN SELECT * FROM mytable WHERE name = 'Alice';

Tối ưu hóa cấu hình

Điều chỉnh tham số PRAGMA của SQLite 3 để nâng cao hiệu suất. Ví dụ, kích hoạt chế độ đồng bộ hóa để cải thiện hiệu suất ghi:

PRAGMA synchronous = OFF;

Quản lý bảo mật

Đảm bảo tính an toàn cho cơ sở dữ liệu rất quan trọng, dưới đây là một số gợi ý quản lý bảo mật.

Quyền hạn tệp

Đảm bảo quyền hạn của tệp cơ sở dữ liệu SQLite 3 được thiết lập đúng để ngăn chặn truy cập trái phép. Ví dụ:

chmod 600 /Applications/ServBay/db/sqlite/your_database.db

Mã hóa dữ liệu

SQLite 3 hỗ trợ mã hóa cơ sở dữ liệu, bạn có thể sử dụng tiện ích mở rộng mã hóa SQLite (như SQLCipher) để mã hóa cơ sở dữ liệu.

Câu hỏi thường gặp và giải pháp

Không thể kết nối đến cơ sở dữ liệu SQLite 3

1. Kiểm tra đường dẫn tệp cơ sở dữ liệu: Đảm bảo đường dẫn tệp cơ sở dữ liệu chính xác và tệp tồn tại.

2. Kiểm tra quyền hạn tệp: Đảm bảo quyền hạn của tệp cơ sở dữ liệu cho phép đọc, ghi.

Cơ sở dữ liệu bị khóa

1. Kiểm tra truy cập đồng thời: Đảm bảo không có tiến trình nào khác đang truy cập cơ sở dữ liệu, SQLite 3 sẽ khóa cơ sở dữ liệu trong khi thực hiện thao tác ghi.

2. Sử dụng chế độ WAL: Kích hoạt chế độ WAL (Write-Ahead Logging) để cải thiện hiệu suất đồng thời:

   PRAGMA journal_mode = WAL;

Tóm tắt

SQLite 3 là một hệ thống quản lý cơ sở dữ liệu nhúng mạnh mẽ và linh hoạt, ServBay tích hợp sẵn SQLite 3, giúp quản lý và sử dụng cơ sở dữ liệu dễ dàng hơn. Thông qua bài viết này, bạn có thể dễ dàng cài đặt, cấu hình, kết nối, quản lý, sao lưu, khôi phục và tối ưu hóa hiệu suất SQLite 3, đảm bảo cơ sở dữ liệu vận hành hiệu quả và an toàn.