llms.txt

# PhobosFramework SQLite Driver

This is the **PhobosFramework SQLite Driver** - a database driver implementation for the PhobosFramework Database Layer that provides SQLite connectivity (file-based and in-memory). This is a **library package** meant to be used via Composer alongside `mongoose-studio/phobos-framework-database`.

The driver extends `AbstractDriver` from the database layer and implements SQLite-specific functionality including:
- DSN generation for file databases and `:memory:` databases
- Post-connection configuration via PRAGMAs (foreign keys, journal mode, busy timeout, synchronous)
- Standard-SQL identifier quoting (double quotes)
- Savepoint support for nested transactions
- SQLite-appropriate isolation-level handling (only SERIALIZABLE and READ UNCOMMITTED exist)

It is the recommended driver for **testing** (in-memory SQLite is fast and isolated), prototyping, and embedded/desktop applications.

## Architecture

### Driver Implementation

**SQLiteDriver** (`src/Drivers/SQLite/SQLiteDriver.php`)

This is the only class in this package. It implements `DriverInterface` (via `AbstractDriver`) from the database layer and provides:

1. **Connection Configuration**:
   - `getDSN(array $config)`: Builds the SQLite DSN. `:memory:` → `sqlite::memory:`; any other value → `sqlite:<path>`. Throws `ConfigurationException` if `database` is missing/empty.
   - `getPDOOptions(array $config)`: Inherited from `AbstractDriver` (safe defaults: exception error mode, associative fetch, native prepares). No SQLite-specific overrides needed.
   - `configure(PDO $pdo, array $config)`: Applies PRAGMAs after connecting. Does **not** call `parent::configure()` because SQLite has no connection charset (no `SET NAMES`).

2. **Driver Identification**:
   - `getName()`: Returns 'sqlite'
   - `supportsSavepoints()`: Returns true (SQLite supports nested transactions)

3. **SQL Generation**:
   - `quoteIdentifier(string $identifier)`: Wraps identifiers in double quotes, escaping internal `"` by doubling
   - `getSetIsolationLevelSQL(string $level)`: Maps SERIALIZABLE → `PRAGMA read_uncommitted = 0`, READ UNCOMMITTED → `PRAGMA read_uncommitted = 1`; throws `InvalidArgumentException` for READ COMMITTED / REPEATABLE READ (SQLite does not support them)

4. **Insert Identity**:
   - `getLastInsertId(PDO $pdo, ?string $sequence = null)`: Returns the ROWID of the last insert. The sequence argument is ignored (SQLite has no sequences).

### Configuration Structure

The driver expects this configuration format (typically in `config/database.php`):

```php
[
    'driver' => 'sqlite',
    'database' => '/var/data/app.sqlite',  // Required. File path, or ':memory:'
    'foreign_keys' => true,                // Optional, defaults to true (PRAGMA foreign_keys = ON)
    'journal_mode' => 'WAL',               // Optional: DELETE|TRUNCATE|PERSIST|MEMORY|WAL|OFF
    'busy_timeout' => 5000,                // Optional, milliseconds (non-negative int)
    'synchronous' => 'NORMAL',             // Optional: OFF|NORMAL|FULL|EXTRA (or 0|1|2|3)
    'options' => [                         // Optional, additional PDO attributes
        PDO::ATTR_TIMEOUT => 5,
    ],
]
```

Unlike MySQL, SQLite config has **no** `host`, `port`, `username`, `password`, `charset`, or `collation`. The only required key is `database`.

### Integration with Database Layer

This driver is registered in the database layer's configuration:

```php
// config/database.php
[
    'drivers' => [
        'sqlite' => \PhobosFramework\Database\Drivers\SQLite\SQLiteDriver::class,
    ],
    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => ':memory:',
        ],
    ],
]
```

## Common Development Commands

### Composer Operations

```bash
# Install dependencies
composer install

# Run the full test suite (unit + integration)
composer test

# Run only unit tests
composer test-unit

# Run only integration tests (real in-memory SQLite, no server needed)
composer test-integration
```

### Code Quality

```bash
# PHP syntax check
php -l src/Drivers/SQLite/SQLiteDriver.php

# Check PSR-4 autoloading
composer dump-autoload --optimize
```

## Important Implementation Details

### DSN Generation

```php
['database' => ':memory:']            => 'sqlite::memory:'
['database' => '/var/data/app.sqlite'] => 'sqlite:/var/data/app.sqlite'
['database' => '']                     => throws ConfigurationException
```

### Foreign Keys Are On by Default

SQLite ships with foreign-key enforcement **disabled**. This driver enables it in `configure()` unless `foreign_keys => false` is passed. This is the single most surprising SQLite default, so the driver flips it to the safe choice.

```php
// configure() always runs one of:
PRAGMA foreign_keys = ON   // default
PRAGMA foreign_keys = OFF  // when 'foreign_keys' => false
```

### PRAGMA Value Validation

`journal_mode` and `synchronous` values are validated against a case-insensitive whitelist and normalized to their canonical form before being interpolated into the PRAGMA statement. Invalid values raise `ConfigurationException` instead of producing an injectable statement. `busy_timeout` must be a non-negative integer.

### In-Memory Databases

Each new PDO connection to `:memory:` is a **separate** database. As long as the same connection is reused (the `ConnectionManager` caches connections), the data persists for the life of the process. This makes `:memory:` ideal for test suites: one clean database per test run, discarded automatically.

### Identifier Quoting

SQLite uses double quotes for identifiers (standard SQL). The driver escapes internal double quotes by doubling them:

```php
"users"
"weird ""column"""   // weird "column"
```

SQLite also accepts backticks for MySQL compatibility, so the core Query Builder (which emits backticks) works unchanged against this driver.

### Isolation Levels

SQLite is SERIALIZABLE natively and offers no intermediate SQL isolation levels. The driver maps:
- `SERIALIZABLE` → `PRAGMA read_uncommitted = 0`
- `READ UNCOMMITTED` → `PRAGMA read_uncommitted = 1` (only effective in shared-cache mode)
- `READ COMMITTED`, `REPEATABLE READ` → `InvalidArgumentException`

### Savepoints

The inherited `getSavepointSQL()` / `getRollbackSavepointSQL()` / `getReleaseSavepointSQL()` from `AbstractDriver` work as-is, because SQLite accepts `SAVEPOINT "name"`, `ROLLBACK TO SAVEPOINT "name"`, and `RELEASE SAVEPOINT "name"` with double-quoted names.

## Namespace Convention

All code uses the namespace: `PhobosFramework\Database\Drivers\SQLite\`

## Dependencies

- PHP 8.4+ (uses typed constants, `match` expressions, typed properties)
- `ext-pdo`: Required for PDO connections
- `ext-pdo_sqlite`: Required for SQLite connectivity
- `mongoose-studio/phobos-framework`: ^3.1 (parent framework)
- `mongoose-studio/phobos-framework-database`: ^3.2 (database layer providing AbstractDriver)

## Related Packages

This driver is part of the Phobos Framework ecosystem:

- **phobos-framework**: Core framework with DI container, routing, HTTP layer
- **phobos-framework-database**: Abstract database layer with query builder, entities, connection management
- **phobos-framework-database-mysql**: MySQL/MariaDB driver implementation
- **phobos-framework-database-sqlite**: This package - SQLite driver implementation

## Code Style Notes

- Follow PSR-4 autoloading
- Use type hints for all parameters and return types
- Document public methods with PHPDoc comments
- Spanish comments are acceptable (matches parent framework conventions)
- Code header includes MIT license notice and author attribution

## Testing Notes

Because SQLite needs no server, the integration tests run everywhere `ext-pdo_sqlite` is present — no environment variables or external services required. They exercise the driver against a real in-memory engine: foreign-key enforcement, savepoint rollback, CRUD round-trips, and PRAGMA application are all verified against actual SQLite behavior, not just generated SQL strings.

This driver is also what powers the `EntityCRUDTest` integration suite in `phobos-framework-database`: the full Active Record layer (create/read/update/delete, change tracking, nested transactions) is validated end-to-end on SQLite.