postgresql-plugin

插件安装

composer require esd/postgresql-plugin

插件用法

PostgreSQL插件的用法，力求与MySQL用法一样。如果你已熟用过 esd/mysql-plugin，可以忽略以下内容。

1. 启用插件

src/Application.php

<?phpnamespaceapp;
useESD\Go\GoApplication;
useESD\Plugins\Postgresql\PostgresqlPlugin;
class Application
{
/** * @throws \DI\DependencyException * @throws \DI\NotFoundException * @throws \ESD\Core\Exception * @throws \ESD\Core\Plugins\Config\ConfigException * @throws \ReflectionException */publicstaticfunctionmain()
{
$application = newGoApplication();
$application->addPlug(newPostgresqlPlugin());
$application->run();
}
}

2. 使用插件

有两种方法在对象中使用。

方法1

使用 use trait，然后通过 $this->postgresql() 获取数据库连接。

useGetPostgresql;

publicfunctiontest()
{
$this->postgresql()->query("select * from pg_stat_activity");
return$res;
}

如果需要切换 postgresql配置为test，可按照如下方法修改。

publicfunctiontest()
{
$this->postgresql('test')->query("select * from pg_stat_activity");
return$res;
}

方法2

在对象中进行注入postgresql对象

useDI\Annotation\Inject;
/** * @Inject() * @var \ESD\Plugins\Postgresql\PostgresDb */protected$postgresql;
publicfunctiontest(){
$res = $this->postgresql->query("select * from pg_stat_activity");
return$res;
}

通过注入的方法，目前还不支持切换 postgresql 连接配置。

PostgreSQL 对象的使用方法

数据表例子：

CREATETABLEp_customer (
"user_name"varchar(200),
"contact" jsonb
)

user_name	contact
张三	{"QQ": 120012, "tel": 6768456, "phone": 15838381234, "Wechat": "风的季节"}
李四	{"tel": 8661235545}

Insert Query

$res = $this->postgresql()->insert("p_customer", [
"user_name" => "奥里",
"contact" => json_encode([
"QQ" => "123456",
"Wechat" => "黑暗森林"
], JSON_UNESCAPED_UNICODE)
]);

Select Query

$res = $this->postgresql()
->where("contact->>'phone'", '15838381234')
->get("p_customer");

Update Query

$res = $this->postgresql()
->where("contact->>'phone'", '15838381234')
->update("p_customer", [
"user_name"=>"钱三强"
]);

Delete Query

$res = $this->postgresql()
->where("contact->>'phone'", '15838381234')
->delete("p_customer");

Ordering method

$res = $this->postgresql()
->orderByd("contact->>'QQ'", "ASC")
->get("p_customer");

Grouping method

$res = $this->postgresql()
->groupBy("contact->>'QQ'")
->get("p_customer");

JOIN method

$res = $this->postgresql()
->join("p_customer_qq cq", "c.contact->>'QQ' = cq.qq_number", "LEFT")
->where("c.contact->>'phone'", '15838381234')
->get("p_customer c", NULL, "c.*, cq.qq_avator");

Has method

A convenient function that returns TRUE if exists at least an element that satisfy the where condition specified calling the "where" method before this one.

$res = $this->postgresql()
->where("contact->>'phone'", '15838381234')
->has("p_customer");

Helper methods

Get last executed SQL query

$sql = $this->postgresql()->getLastQuery();

Check if table exists

$exist = $this->postgresql()->tableExists("p_customer");

Transaction helpers

$db = $this->postgresql();
$db->startTransaction();
...if (!$db->insert ('myTable', $insertData)) {
//Error while saving, cancel new record$db->rollback();
} else {
//OK$db->commit();
}

Error helpers

$error = $this->postgresql()->getLastError();

Pagination

$db = $this->postgresql();
$page = 1;
// set page limit to 2 results per page. 20 by default$db->pageLimit = 2;
$res = $db->paginate("p_customer", $page);

loadData、XML、loadXML、insertMulti、subQuery 等MySQL插件的功能，暂时没有实现。

Running raw SQL queries

$res = $this->postgresql()
->query('SELECT * from p_customer where contact->>'phone' = ?', Array('15838381234'));