SQLiteDriver Class

Overview

The Kotchasan\Connection\SQLiteDriver class is a specialized driver for SQLite databases. It supports SQLite features such as file-based database operations, sequence management, and database-specific SQL functions.

Installation and Requirements

Requirements

• PHP 7.4 or higher
• PDO SQLite extension (pdo_sqlite)
• SQLite 3.7+

Connection Configuration

Basic Configuration

use Kotchasan\Connection\SQLiteDriver;

$config = [
    'database' => '/path/to/database.sqlite'
];

$driver = new SQLiteDriver();
$connected = $driver->connect($config);

In-Memory Database

$config = [
    'database' => ':memory:' // In-memory database
];

$driver = new SQLiteDriver();
$connected = $driver->connect($config);

SQLite-Specific Features

Database Optimization

// SQLite uses VACUUM
$success = $driver->optimizeTable('users');
// Executes: VACUUM (entire database);

Emptying Tables

// SQLite doesn't support TRUNCATE, uses DELETE instead
$success = $driver->emptyTable('temp_data');
// Executes: DELETE FROM "temp_data" and resets sqlite_sequence;

SQL Functions for SQLite

Date and Time Functions

// Extract year (uses strftime)
$yearFunc = $driver->formatSqlFunction('YEAR', ['column' => 'created_at'], 'year_created');
// Result: CAST(strftime('%Y', "created_at") AS INTEGER) AS "year_created"

// Current timestamp
$nowFunc = $driver->formatSqlFunction('NOW', [], 'current_time');
// Result: DATETIME() AS "current_time"

// Date formatting
$formatFunc = $driver->formatSqlFunction('DATE_FORMAT', [
    'column' => 'created_at',
    'format' => '%Y-%m-%d'
], 'formatted_date');
// Result: strftime('%Y-%m-%d', "created_at") AS "formatted_date"

String Functions

// String concatenation (uses ||)
$concatFunc = $driver->formatSqlFunction('CONCAT', [
    'fields' => ['first_name', 'last_name'],
    'separator' => ' '
], 'full_name');
// Result: ("first_name" || ' ' || "last_name") AS "full_name"

// Group concatenation
$groupConcatFunc = $driver->formatSqlFunction('GROUP_CONCAT', [
    'column' => 'tag_name',
    'separator' => ', '
], 'tags');
// Result: GROUP_CONCAT("tag_name", ', ') AS "tags"

Other Functions

// Random
$randFunc = $driver->formatSqlFunction('RAND', [], 'random_value');
// Result: RANDOM() AS "random_value"

// Handle NULL values
$ifnullFunc = $driver->formatSqlFunction('IFNULL', [
    'column1' => 'nickname',
    'column2' => 'first_name'
], 'display_name');
// Result: IFNULL("nickname", "first_name") AS "display_name"

// Date difference
$datediffFunc = $driver->formatSqlFunction('DATEDIFF', [
    'column1' => 'end_date',
    'column2' => 'start_date'
], 'duration');
// Result: (JULIANDAY("end_date") - JULIANDAY("start_date")) AS "duration"

Usage Examples

Creating New Database

$config = [
    'database' => '/var/data/new_app.sqlite'
];

$driver = new SQLiteDriver();

if ($driver->connect($config)) {
    // Create table
    $sql = "
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ";

    $stmt = $driver->prepare($sql);
    $stmt->execute();

    echo "Kotchasan\Database and table created successfully";
}

Working with Auto-increment

// INSERT and get last ID
$stmt = $driver->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['John Doe', 'john@example.com']);
$newId = $driver->lastInsertId(); // No sequence name needed;

Database Backup

function backupSQLiteKotchasan\Database($sourcePath, $backupPath) {
    // Copy database file
    if (copy($sourcePath, $backupPath)) {
        return true;
    }
    return false;
}

// Usage
$backupSuccess = backupSQLiteKotchasan\Database(
    '/var/data/app.sqlite',
    '/var/backups/app_' . date('Y-m-d_H-i-s') . '.sqlite'
);

Error Handling

try {
    $driver->connect($config);
} catch (\PDOException $e) {
    $message = $e->getMessage();

    if (strpos($message, 'unable to open database file') !== false) {
        echo "Unable to open database file";
    } elseif (strpos($message, 'database is locked') !== false) {
        echo "Kotchasan\Database is locked";
    } elseif (strpos($message, 'disk I/O error') !== false) {
        echo "Disk I/O error";
    } else {
        echo "SQLite error: " . $message;
    }
}

Performance Tuning

PRAGMA Settings

// Performance settings
$pragmas = [
    "PRAGMA journal_mode = WAL",           // Write-Ahead Logging
    "PRAGMA synchronous = NORMAL",         // Moderate safety
    "PRAGMA cache_size = 10000",           // Cache size
    "PRAGMA temp_store = MEMORY",          // Store temp in memory
    "PRAGMA mmap_size = 268435456"         // Memory-mapped I/O
];

foreach ($pragmas as $pragma) {
    $driver->prepare($pragma)->execute();
}

Using Indexes

// Create index
$sql = "CREATE INDEX IF NOT EXISTS idx_user_email ON users (email)";
$driver->prepare($sql)->execute();

// Check indexes
$sql = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='users'";
$stmt = $driver->prepare($sql);
$result = $stmt->execute();

Best Practices and Considerations

Best Practices

1. Use WAL Mode: Enable WAL mode for better performance
2. Backup Data: Regularly backup database files
3. Use Transactions: Use transactions for multi-step operations
4. Configure PRAGMA: Tune PRAGMA settings

Considerations

1. File Permissions: Check file access permissions
2. Concurrent Access: Be careful with concurrent access
3. Database Size: Watch out for oversized databases

Common Issues

1. Database Locked: Properly close connections
2. File Path: Verify database file path
3. Disk Space: Check available disk space

Related Classes

• Connection - Basic connection class
• DriverInterface - Driver interface
• MySQLDriver - MySQL driver