README

PgBabylon 是 PHP PDO 和 PDOStatement 原生类的用户空间扩展，帮助处理 PostgreSQL 类型，如 JSON、数组等。

它提供了 PHP 类型与 PostgreSQL 类型之间的转换，并简化了使用 SQL 子句如 IN 的操作。

// Example: insert a PHP array in a PostgreSQL JSON field

$arr = [
    "name" => "Mark",
    "age" => 39,
    "address" => "White street, 23",
    "town" => "London"
];

$s = $pdo->prepare("INSERT INTO my_table(json) VALUES (:json)");
$s->execute([
    ":json" => \PgBabylon\DataTypes\JSON($arr)
]);

入门指南

可以使用 composer 安装 PgBabylon

composer require rtshome/pgbabylon

PgBabylon 当前支持以下 PostgreSQL 数据类型

• 日期（PHP DateTime）
• 时间戳（PHP DateTime）
• JSON（PHP 数组）
• 文本、整数和浮点数单维数组（PHP 数组）

这些类型也可以与 IN 操作符一起使用（见下例）。

-- Sample table
CREATE TABLE person(
    id SERIAL NOT NULL PRIMARY KEY,
    data JSON NOT NULL,
    insertion_date DATE NOT NULL
);

<?php
use PgBabylon\PDO;
use PgBabylon\DataTypes;
use PgBabylon\Operators;

$pdo = new PDO("pgsql:dbname=testdb;host=127.0.0.1;user=myuser;pass=mypasswd");
$s = $pdo->prepare("INSERT INTO person(data, insertion_date) VALUES (:person, :ins_date) RETURNING *");

$person = [
    "name" => "Mark",
    "age" => 39,
    "address" => "White street, 23",
    "town" => "London"
];
$s->execute([
    ':person' => DataTypes\JSON($person),
    ':ins_date' => DataTypes\Date(new DateTime())
]);

// PgBabylon\PDOStatement::setColumnTypes() is the method that makes PgBabylon to recognize and convert from Pgsql types  
$s->setColumnTypes([
    'data' => PDO::PARAM_JSON,
    'insertion_date' => PDO::PARAM_DATE
]);
$r = $s->fetch(PDO::FETCH_ASSOC);

var_dump($r);

/* var_dump output

array(3) {
  'id' =>
  int(1)
  'data' =>
  array(4) {
    'name' =>
    string(4) "Mark"
    'age' =>
    int(39)
    'address' =>
    string(16) "White street, 23"
    'town' =>
    string(6) "London"
  }
  'insertion_date' =>
  class DateTime#4 (3) {
    public $date =>
    string(26) "2015-10-04 00:00:00.000000"
    public $timezone_type =>
    int(3)
    public $timezone =>
    string(11) "Europe/Rome"
  }
}

*/

$s = $pdo->prepare("SELECT data FROM person WHERE id IN :person_ids");
$s->execute([
    ':person_ids' => Operators\IN([1,2])
]);
$s->setColumnTypes([
    'data' => PDO::PARAM_JSON
]);

var_dump($r);

/* var_dump output
 
array(1) {
  'data' =>
  array(4) {
    'name' =>
    string(4) "Mark"
    'age' =>
    int(39)
    'address' =>
    string(16) "White street, 23"
    'town' =>
    string(6) "London"
  }
}

*/

与原始 PDO 类的兼容性

PgBabylon\PDO 与原始 PDO 类完全向后兼容。

您可以通过添加一个 use 语句从原始 PDO 类切换到 PgBabylon\PDO

<?php
// PDO usage with original class
/* Connect to a PgSQL database using driver invocation */
$dsn = 'pgsql:dbname=testdb;host=127.0.0.1;user=postgres;pass=mypasswd';
$dbh = new PDO($dsn);

$dbh->prepare("SELECT id FROM person WHERE id=:id");
$r = $dbh->execute([':id' => 1]);
?>

<?php
// Switch to PgBabylon\PDO!
use PgBabylon\PDO;

/* Connect to a PgSQL database using driver invocation */
$dsn = 'pgsql:dbname=testdb;host=127.0.0.1;user=postgres;pass=mypasswd';
$dbh = new PDO($dsn);

$dbh->prepare("SELECT id FROM person WHERE id=:id");
$r = $dbh->execute([':id' => 1]);
?>