PHP code example of quasilyte / ksqlite

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

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

    

quasilyte / ksqlite example snippets

function exec(string $sql, array $params = []): bool

// Simple case: not bind params.
$query = 'CREATE TABLE IF NOT EXISTS languages(
  lang_id INTEGER PRIMARY KEY,
  lang_name TEXT NOT NULL
);'
if (!$db->exec($query)) {
  handle_error($db->getLastError());
}

// Exec with named params.
// Note: a var prefix (':', '@' or '$') should be consistent
// between the query and bind params array.
$query = 'INSERT INTO languages(lang_name) VALUES(:lang_name)';
$params = [':lang_name' => 'KPHP'];
if (!$db->exec($query, $params)) {
  handle_error($db->getLastError());
}

// Exec with positional params.
// Note: bind var numbers start from 1.
$query = 'DELETE FROM languages WHERE lang_name = ?1 OR lang_name = ?2';
$params = [1 => 'COBOL', 2 => 'Visual COBOL'];
if (!$db->exec($query, $params)) {
  handle_error($db->getLastError());
}

function execPrepared(string $sql, $bind_params_func): bool

// Execute several inserts with different bind var sets.
$values = [10, 20, 30, 40];
$query = 'INSERT INTO fav_numbers(num_value) VALUES(?1)';
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) use ($values) {
  if ($b->query_index >= count($values)) {
    return false; // No more rows to insert, stop now
  }
  // Bind ?1 to the specified value.
  // Use string keys, like ':num_value', to bind named params.
  $b->bind(1, $values[$b->query_index]);
  return true; // Parameters bound, execute the query
});
if (!$ok) {
  handle_error($db->getLastError());
}

// Execute 10 inserts without bind vars.
$query = "INSERT INTO fav_events(event_time) VALUES(time('now'))";
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) {
  return $b->query_index < 10;
});
if (!$ok) {
  handle_error($db->getLastError());
}

// Prepared statement API allows you to perform N queries
// using the same statement even if you don't know the exact
// N in advance.
$query = "INSERT INTO important_data(x, y) VALUES(:x, :y)";
$ok = $db->execPrepared($query, function(KSQLiteParamsBinder $b) use ($stream) {
  // Note: we're not even using $b->index here as our data stream is statefull
  // and it knows which item we're processing right now.
  if (!$stream->hasMore()) {
    return false;
  }
  $stream->next();
  foreach ($stream->keyValue() as $k => $v) {
    $b->bind($k, $v);
  }
  return true;
});
if (!$ok) {
  handle_error($db->getLastError());
}

function fetch(string $sql, array $params = [], $row_func = null): tuple(mixed, bool)

// The simplest case: no bind params, default mapping function, collecting all results.
// The result rows are arrays of [x, y].
$query = 'SELECT x, y FROM tab';
[$rows, $ok] = $db->fetch($query);
if (!$ok) {
  handle_error($db->getLastError());
}
foreach ($rows as $i => [$x, $y]) {
  var_dump([$i => "x=$x y=$y"]);
}

// Using the same query, but building the result with assoc arrays,
// like ['x' => $x, 'y' => $y].
[$rows, $ok] = $db->fetch($query, [], function(KSQLiteQueryContext $ctx) {
  return $ctx->rowDataAssoc();
});
if (!$ok) {
  handle_error($db->getLastError());
}
foreach ($rows as $i => $data) {
  var_dump([$i => "x=$data['x'] y=$data['y']"]);
}

// If you return a non-array value from fetch, you'll get a flat array in the final result.
$query = 'SELECT id, second_key FROM users WHERE age >= ?1';
$vars = [1 => 18];
[$ids, $ok] = $db->fetch($query, $vars, function(KSQLiteQueryContext $ctx) {
  return $ctx->rowDataAssoc()['id'];
});
if (!$ok) {
  handle_error($db->getLastError());
}
foreach ($ids as $i => $id) {
  var_dump([$i => "id=$id"]);
}

function fetchRow(string $sql, array $params = []): tuple(mixed[], bool)

$query = 'SELECT * FROM users WHERE user_id = :id';
[$user, $ok] = $db->fetchRow($query, [':id' => $id]);
if (!$ok) {
  handle_error($db->getLastError());
}

function fetchColumn(string $sql, array $params = []): tuple(mixed, bool)

$query = 'SELECT COUNT(*) FROM users';
[$num_users, $ok] = $db->fetchColumn($query);
if (!$ok) {
  handle_error($db->getLastError());
}

function query(string $sql, array $params, $row_func): bool

// Implementing a fetch-like operation via query.
$result = new KSQLiteArray();
$ok = $db->query($sql, $vars, function(SQLiteQueryContext $ctx) use ($result) {
  $result->values[] = $ctx->rowData();
});
if (!$ok) {
  handle_error($db->getLastError());
}
$handler->processData($result->values); // Work with unboxed [K]PHP array

function queryPrepared(string $sql, $bind_params_func, $row_func): bool

$ok = $db->queryPrepared(
  'SELECT * FROM fav_numbers WHERE num_id = :num_id',
  function(KSQLiteParamsBinder $b) use ($ids) {
    if ($b->query_index >= count($ids)) {
      return false;
    }
    $b->bind(':num_id', $ids[$b->query_index]);
    return true;
  },
  function(KSQLiteQueryContext $ctx) {
    // $ctx->query_index is 0 for the first prepared query execution.
    // The second execution will have $query_index=1 and so on.
    var_dump($ctx->query_index . '=>' . $ctx->rowDataAssoc()['num_value']);
  }
);
if (!$ok) {
  handle_error($db->getLastError());
}

bash
$ php -d opcache.enable_cli=1\
      -d opcache.preload=preload.php\
      -f ./examples/transactions.php