如何使用PDO PHP在Postgresql DB中设置与模式的连接

问题描述

如果我有一个具有多个架构的Postgresql数据库，并且我想使用PDO将与Web应用程序的连接设置为该数据库的其中一个Shemas(默认情况下不是公共架构)，那是最佳的方法呢? /p>

我目前与数据库的连接如下:

public static function Conecction(){

        $dbname = "start";
        $host = "localhost";
        $username = "user";
        $password = "123";
        return new PDO("pgsql:dbname=$dbname;host=$host", $username, $password);
    }

但是这里我只是指定数据库的名称，而不是在数据库内部使用模式

非常感谢！

解决方案

要设置默认架构，您必须执行以下查询:

$conn = Foo::Conecction();
$conn->exec('SET search_path TO yourschema');

或者，如果您想以更特定于用户的方式进行操作:

$conn->exec('ALTER USER user SET search_path TO yourschema');

作为旁注:请不要创建您的PDO实例(作为静态方法的返回值). PDO直接提供了一个干净的API.您不允许调用者确定要连接的数据库，而是对凭据进行硬编码.任何标准都认为这是不良做法.考虑将连接传递到您需要的任何地方，或者-如果您坚持要创建-创建至少要求调用者自己传递数据库凭据的方法.

我还建议您在您的DSN字符串中包含排序规则，并可能设置一些属性以使调试时的工作更轻松:

$pdo = new PDO(
    "pgsql:dbname=$dbname;host=$host;port=5432;charset=utf8",
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,//set PDO to throw exceptions on error
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,//NULL values are returned as PHP null's
    ]
);

查看其他属性并相应地使用.

最后一点: :前段时间PDO有时可能在解析localhost主机名时遇到麻烦.我怀疑此错误已得到修复，但以防万一它出现了(甚至:尚未修复)，我建议您尽可能使用ip地址.如果您已经知道 IP，那么不必麻烦DNS服务器来解析一个您知道该字符串仍会解析为127.0.0.1的字符串.

If I have a Postgresql DB with several Schemas and I want to set a connection with my Web App to one of this shemas (not public schema by default) of the DB using PDO, what will be the most optimal way?

The connection with the DB I do currently like this:

public static function Conecction(){

        $dbname = "start";
        $host = "localhost";
        $username = "user";
        $password = "123";
        return new PDO("pgsql:dbname=$dbname;host=$host", $username, $password);
    }

But here I just specified the name of the DB not with schema inside of the DB

Thx very much!

解决方案

To set the default schema, you'll have to execute a query like:

$conn = Foo::Conecction();
$conn->exec('SET search_path TO yourschema');

or, if you want to go about it in a more user-specific way:

$conn->exec('ALTER USER user SET search_path TO yourschema');

As a side-note: Please don't create your PDO instances like that (as the return value of a static method). PDO offers a clean API right out of the box. You're not allowing the caller to determine what DB to connect to, instead, you're hard coding the credentials. This is considered bad practice by any standard. Consider passing the connection to wherever you need it, or -if you insist- create a method that at least requires the caller to pass DB credentials themselves.

I'd also recommend you include the collation in your DSN string, and possibly set some attributes to make life easier when debugging:

$pdo = new PDO(
    "pgsql:dbname=$dbname;host=$host;port=5432;charset=utf8",
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,//set PDO to throw exceptions on error
        PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,//NULL values are returned as PHP null's
    ]
);

Check out other attributes and use accordingly.

Last thing: Some time ago PDO could occasionally run into trouble resolving the localhost host-name. I suspect this bug has been fixed already, but just in case it crops up (or even: hasn't been fixed), I'd recommend you use ip addresses whenever you can. If you know the IP already, there's no real point in having to bother the DNS server with resolving a string that you know will resolve to 127.0.0.1 anyway.

这篇关于如何使用PDO PHP在Postgresql DB中设置与模式的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！