PHP code example of fabpot / amphp-sqlite3

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

fabpot / amphp-sqlite3 example snippets

use Fabpot\Amp\Sqlite\SqliteConfig;
use Fabpot\Amp\Sqlite\SqliteConnector;

$config = new SqliteConfig(__DIR__ . '/app.sqlite');
$connection = (new SqliteConnector())->connect($config);

try {
    $connection->query(<<<'SQL'
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        )
        SQL);

    $result = $connection->execute(
        'INSERT INTO users (name) VALUES (:name)',
        [':name' => 'Fabien'],
    );

    $userId = $result->getLastInsertId();

    foreach ($connection->query('SELECT id, name FROM users ORDER BY id') as $user) {
        echo $user['name'] . "\n";
    }
} finally {
    $connection->close();
}

use Fabpot\Amp\Sqlite\SqliteConfig;
use Fabpot\Amp\Sqlite\SqliteJournalMode;
use Fabpot\Amp\Sqlite\SqliteOpenMode;
use Fabpot\Amp\Sqlite\SqliteSynchronousMode;
use Fabpot\Amp\Sqlite\SqliteTransactionMode;

$config = (new SqliteConfig(__DIR__ . '/app.sqlite'))
    ->withOpenMode(SqliteOpenMode::ReadWriteCreate)
    ->withJournalMode(SqliteJournalMode::Automatic)
    ->withSynchronousMode(SqliteSynchronousMode::Automatic)
    ->withForeignKeys(true)
    ->withBusyTimeout(5_000) // milliseconds
    ->withTrustedSchema(false)
    ->withBatchSize(100)
    ->withTransactionMode(SqliteTransactionMode::Deferred)
    ->withExtendedResultCodes(true);

$config = $config->withPragma('cache_size', -8_000);

use Fabpot\Amp\Sqlite\SqliteConnectionPool;

$pool = new SqliteConnectionPool(
    new SqliteConfig(__DIR__ . '/app.sqlite'),
    maxConnections: 10,
    idleTimeout: 60,
);

try {
    foreach ($pool->query('SELECT id, name FROM users') as $user) {
        // Process the user
    }
} finally {
    $pool->close();
}

$database->query('CREATE INDEX users_name ON users (name)');

$result = $database->execute(
    'SELECT id, name FROM users WHERE name = :name',
    [':name' => 'Fabien'],
);

$database->executeScript(<<<'SQL'
    CREATE TABLE projects (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    );
    CREATE INDEX projects_name ON projects (name);
    SQL);

$result = $database->query('SELECT id, name FROM users ORDER BY id');

while ($user = $result->fetchRow()) {
    echo $user['name'] . "\n";
}

$result = $database->execute(
    'INSERT INTO users (name) VALUES (?)',
    ['Alice'],
);

$result->getRowCount();     // changed rows, including trigger changes
$result->getLastInsertId(); // inserted row ID, or null when no ID can be attributed
$result->getColumnCount();  // null for commands
$result->getColumnNames();  // null for commands

$result->close();

$statement = $database->prepare('INSERT INTO users (name) VALUES (?)');

try {
    $statement->execute(['Fabien']);
    $statement->execute(['Alice']);
} finally {
    $statement->close();
}

$transaction = $database->beginTransaction();

try {
    $transaction->execute(
        'INSERT INTO users (name) VALUES (?)',
        ['Bob'],
    );
    $transaction->commit();
} catch (\Throwable $error) {
    $transaction->rollback();

    throw $error;
}

$transaction = $database->beginTransaction();
$transaction->execute('INSERT INTO users (name) VALUES (?)', ['kept']);

$nested = $transaction->beginTransaction();
$nested->execute('INSERT INTO users (name) VALUES (?)', ['discarded']);
$nested->rollback();

$transaction->commit();

use Fabpot\Amp\Sqlite\SqliteBlob;

$database->execute(
    'INSERT INTO files (contents) VALUES (?)',
    [new SqliteBlob($bytes)],
);

use Fabpot\Amp\Sqlite\SqliteBlobMode;

$result = $database->query(
    'INSERT INTO files (contents) VALUES (zeroblob(1048576))',
);

$rowId = $result->getLastInsertId()
    ?? throw new \RuntimeException('SQLite did not report the inserted row ID.');

$blob = $database->openBlob(
    'files',
    'contents',
    $rowId,
    mode: SqliteBlobMode::ReadWrite,
);

try {
    while (($chunk = fread($source, 8192)) !== false && $chunk !== '') {
        $blob->write($chunk);
    }
} finally {
    $blob->close();
}

use function Amp\ByteStream\buffer;

$blob = $database->openBlob('files', 'contents', $rowId);

try {
    $bytes = buffer($blob);
} finally {
    $blob->close();
}

final class SqliteFunctions
{
    public static function reverse(string $value): string
    {
        return strrev($value);
    }
}

$config = (new SqliteConfig(__DIR__ . '/app.sqlite'))
    ->withFunction(
        'reverse',
        [SqliteFunctions::class, 'reverse'],
        argCount: 1,
        deterministic: true,
    );

$row = $database->query("SELECT reverse('Amp') AS value")->fetchRow();
// ['value' => 'pmA']

$database->backup(__DIR__ . '/snapshot.sqlite');

// Restore the snapshot later
$database->restore(__DIR__ . '/snapshot.sqlite');

$row = $database->query('PRAGMA wal_checkpoint(TRUNCATE)')->fetchRow();
// ['busy' => 0, 'log' => 0, 'checkpointed' => 0]

use Fabpot\Amp\Sqlite\SqliteQueryError;

try {
    $database->execute(
        'INSERT INTO users (id, name) VALUES (1, ?)',
        ['Duplicate'],
    );
} catch (SqliteQueryError $error) {
    $error->getResultCode();         // 19 (SQLITE_CONSTRAINT)
    $error->getExtendedResultCode(); // 1555 (SQLITE_CONSTRAINT_PRIMARYKEY)
    $error->getQuery();              // the failed SQL
}