TIPS: 如遇兼容性问题,可提交github issue以获取帮助:https://github.com/cockroachdb/cockroach/issues/new

本小节将展示如何使用php-psql驱动进行编程交互。用户需要事先安装、配置CockroachDB。

使用php-psql驱动

Step 1: 安装php-psql驱动

用户可参考官方文档进行安装。

Step 2: 启动节点

以非安全模式启动节点:

cockroach start --insecure --store=hello-1 --host=localhost

Step 3: 创建用户

执行cockroach user命令创建maxroach用户:

cockroach user set maxroach --insecure

Step 4: 创建数据库并授权

root用户启动内置的SQL客户端,创建bank数据库并授权maxroach用户。

cockroach sql --insecure -e 'CREATE DATABASE bank'
cockroach sql --insecure -e 'GRANT ALL ON DATABASE bank TO maxroach'

Step 5: 创建表

cockroach sql --insecure --database=bank --user=maxroach -e 'CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)'

Step 6: 编写执行php代码

基本操作

php代码:

<?php
try {
  //非安全模式
  $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
    'maxroach', null, array(
      PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => true,
  ));
  //安全模式
  /* 
  * $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=require;sslrootcert=certs/ca.crt;sslkey=certs/client.maxroach.key;sslcert=certs/client.maxroach.crt',
  *  'maxroach', null, array(
  *    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
  *    PDO::ATTR_EMULATE_PREPARES => true,
  * )); 
  * */

  $dbh->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)');

  print "Account balances:\r\n";
  foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
      print $row['id'] . ': ' . $row['balance'] . "\r\n";
  }
} catch (Exception $e) {
    print $e->getMessage() . "\r\n";
    exit(1);
}
?>

执行输出:

Account balances:
1: 1000
2: 250

创建事务(带重试逻辑)

php代码:

<?php

function transferMoney($dbh, $from, $to, $amount) {
  try {
    $dbh->beginTransaction();
    // This savepoint allows us to retry our transaction.
    $dbh->exec("SAVEPOINT cockroach_restart");
  } catch (Exception $e) {
    throw $e;
  }

  while (true) {
    try {
      $stmt = $dbh->prepare(
        'UPDATE accounts SET balance = balance + :deposit ' .
        'WHERE id = :account AND (:deposit > 0 OR balance + :deposit >= 0)');

      // First, withdraw the money from the old account (if possible).
      $stmt->bindValue(':account', $from, PDO::PARAM_INT);
      $stmt->bindValue(':deposit', -$amount, PDO::PARAM_INT);
      $stmt->execute();
      if ($stmt->rowCount() == 0) {
        print "source account does not exist or is underfunded\r\n";
        return;
      }

      // Next, deposit into the new account (if it exists).
      $stmt->bindValue(':account', $to, PDO::PARAM_INT);
      $stmt->bindValue(':deposit', $amount, PDO::PARAM_INT);
      $stmt->execute();
      if ($stmt->rowCount() == 0) {
        print "destination account does not exist\r\n";
        return;
      }

      // Attempt to release the savepoint (which is really the commit).
      $dbh->exec('RELEASE SAVEPOINT cockroach_restart');
      $dbh->commit();
      return;
    } catch (PDOException $e) {
      if ($e->getCode() != '40001') {
        // Non-recoverable error. Rollback and bubble error up the chain.
        $dbh->rollBack();
        throw $e;
      } else {
        // Cockroach transaction retry code. Rollback to the savepoint and
        // restart.
        $dbh->exec('ROLLBACK TO SAVEPOINT cockroach_restart');
      }
    }
  }
}

try {
  //非安全模式
  $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
    'maxroach', null, array(
      PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_EMULATE_PREPARES => true,
  ));
  //安全模式
  /* 
  * $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=require;sslrootcert=certs/ca.crt;sslkey=certs/client.maxroach.key;sslcert=certs/client.maxroach.crt',
  *  'maxroach', null, array(
  *    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
  *    PDO::ATTR_EMULATE_PREPARES => true,
  * )); 
  * */

  transferMoney($dbh, 1, 2, 10);

  print "Account balances after transfer:\r\n";
  foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
      print $row['id'] . ': ' . $row['balance'] . "\r\n";
  }
} catch (Exception $e) {
    print $e->getMessage() . "\r\n";
    exit(1);
}
?>

执行输出:

Account balances after transfer:
1: 900
2: 350

执行查询:

cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank
+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

NOTE: 在默认的隔离级别SERIALIZABLE下,因读写冲突导致事务执行失败,需要用户主动地重新提交事务。用户也实现通用的retry函数在事务内部重新执行事务。