SQLite 3 Database Management and Usage

SQLite 3 is a lightweight embedded relational database management system widely used in mobile applications, embedded systems, and small desktop applications. SQLite 3 does not require an independent server process, and data is stored in a single file, making it easy to manage and use. This article will detail how to manage and use SQLite 3 databases in ServBay, including installation, configuration, backup, recovery, and performance optimization.

Installing and Configuring SQLite 3

SQLite 3 usually does not need to be installed separately because it is already included in most operating systems and programming environments. ServBay comes with SQLite 3, you just need to ensure it is available.

Verifying SQLite 3 Installation

You can verify if SQLite 3 is installed by using the following command:

sqlite3 --version

If version information is displayed, it means SQLite 3 is installed.

Creating and Connecting SQLite 3 Databases

SQLite 3 database is stored in a single file, the default path is /Applications/ServBay/db/sqlite. You can use the command line tool sqlite3 or the SQLite library of programming languages to create and connect to the database.

Connecting Using Command Line Tools

1. Create or connect to a database:

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

2. Exit SQLite 3:

   .exit

Connecting Using Programming Languages

PHP Example

Using the SQLite3 class to connect to SQLite 3:

<?php
// Connect to SQLite 3 database
$db = new SQLite3('/Applications/ServBay/db/sqlite/your_database.db');

// Create table
$db->exec("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT)");

// Insert data
$db->exec("INSERT INTO mytable (name) VALUES ('Alice')");

// Query data
$result = $db->query("SELECT * FROM mytable");
while ($row = $result->fetchArray()) {
    echo "ID: " . $row['id'] . " Name: " . $row['name'] . "\n";
}

// Close database connection
$db->close();
?>

Node.js Example

Using the sqlite3 library to connect to SQLite 3:

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

// Connect to SQLite 3 database
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.');
});

// Create table
db.run(`CREATE TABLE IF NOT EXISTS mytable (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
)`);

// Insert data
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}`);
});

// Query data
db.all(`SELECT * FROM mytable`, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.id, row.name);
  });
});

// Close the database connection
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});

Database Management

Basic Operations

1. Create table:

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

2. Insert data:

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

3. Query data:

   SELECT * FROM mytable;

4. Update data:

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

5. Delete data:

   DELETE FROM mytable WHERE id = 1;

Backup and Recovery

Backup Database

SQLite 3 database is stored in a single file, and backing it up only requires copying the file. It is recommended to store backup files in the following directory:

/Applications/ServBay/backup/sqlite

Use the command line tool to copy the database file for backup:

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

Restore Database

Copy the backup file back to the original location for recovery:

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

Performance Optimization

SQLite 3 provides various performance optimization options. Here are some common optimization methods.

Index Optimization

Make sure to create indexes for columns frequently queried. For example:

CREATE INDEX idx_name ON mytable(name);

Query Optimization

Use the EXPLAIN command to analyze query performance and make corresponding optimizations. For example:

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

Configuration Optimization

Adjust SQLite 3’s PRAGMA parameters to improve performance. For example, enable synchronous mode to increase write performance:

PRAGMA synchronous = OFF;

Security Management

Ensuring the security of the database is very important. Here are some security management recommendations.

File Permissions

Ensure the correct permission settings for SQLite 3 database files to prevent unauthorized access. For example:

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

Data Encryption

SQLite 3 supports database encryption, and you can use SQLite encryption extensions (such as SQLCipher) to encrypt the database.

Common Issues and Solutions

Unable to Connect to SQLite 3 Database

1. Check Database File Path: Ensure the database file path is correct and the file exists.

2. Check File Permissions: Ensure the database file permissions allow read and write operations.

Database Locked

1. Check Concurrent Access: Ensure no other processes are accessing the database. SQLite 3 locks the database during write operations.

2. Use WAL Mode: Enable WAL (Write-Ahead Logging) mode to improve concurrency performance:

   PRAGMA journal_mode = WAL;

Conclusion

SQLite 3 is a powerful and flexible embedded database management system. ServBay comes with SQLite 3, making database management and usage more convenient. Through this document, you can easily perform SQLite 3 installation, configuration, connection, management, backup, recovery, and performance optimization to ensure efficient operation and security of the database.