从PHP应用程序中抽象数据库的最佳方法是什么？

My question is how does one abstract a database connection from the model layer of an application? The primary concern is to be able to easily change from different types of databases. Maybe you start with a flat file, comma-delimited database. Then you want to move to a SQL database. Then later you decide an LDAP implementation would be better. How can a person easily plan for something like this?

我的问题是如何从应用程序的模型层抽象数据库连接？主要关注点是能够轻松地从不同类型的数据库进行更改。也许你从一个平面文件，逗号分隔的数据库开始。然后，您想要移动到SQL数据库。然后您决定LDAP实现会更好。一个人如何轻松地计划这样的事情？

For a simple example, let's say you have a user with a first name, last name, and email. A very simple PHP class representing it might look like this (please ignore the problems with public instance variables):

举一个简单的例子，假设您有一个名字，姓氏和电子邮件的用户。表示它的非常简单的PHP类可能如下所示（请忽略公共实例变量的问题）：

<?php

class User {
  public $first;
  public $last;
  public $email;
}

?>

I have frequently seen where people have a DAO class which has the SQL embedded in it as such:

我经常看到人们有一个DAO类，其中嵌入了SQL，如下所示：

<?php

class UserDAO {
  public $id;
  public $fist;
  public $last;
  public $email;

  public function create( &$db ) {
    $sql = "INSERT INTO user VALUES( '$first', '$last', '$email' )";
    $db->query( $sql );
  }
}

?>

My problem with strategies like this is when you want to change your database, you have to change every DAO class' create, update, load, delete functions to deal with your new type of database. Even if you have a program to auto-generate them for you (which I am not particularly a fan of), you would have to edit this program to make it work now.

我这样的策略的问题是当你想要改变你的数据库时，你必须改变每个DAO类的创建，更新，加载，删除功能来处理你的新类型的数据库。即使你有一个程序为你自动生成它们（我不是特别喜欢它），你必须编辑这个程序才能使它现在正常工作。

What are your suggestions for how to handle this?

您对如何处理这个问题有什么建议？

My current idea is to create a super class for DAO objects with its own create, delete, update, load functions. However, these functions would take arrays of the attributes of the DAO and generate the query itself. In this manner, the only SQL is in the SuperDAO class rather than being scattered about several classes. Then if you wanted to change your database layer, you would only have to change how the SuperDAO class generates the queries. Advantages? Disadvantages? Foreseeable problems? The good, the bad, and the ugly?

我目前的想法是为DAO对象创建一个超类，它有自己的创建，删除，更新，加载功能。但是，这些函数将获取DAO属性的数组并生成查询本身。通过这种方式，唯一的SQL在SuperDAO类中，而不是分散在几个类中。然后，如果要更改数据库层，则只需更改SuperDAO类生成查询的方式。优点？缺点是什么？可预见的问题？黄金三镖客？

10 个解决方案

#1

Using an ORM is usually the preferred way of abstracting the database. An incomplete list of PHP implementations is available on Wikipedia.

使用ORM通常是抽象数据库的首选方式。*上提供了不完整的PHP实现列表。

#2

You can use various frameworks such as PDO, PEAR::MDB2 or Zend_Db, but to be honest in 12 years of PHP development, I've never had to transition from one type of data storage infrastructure to another.

您可以使用各种框架，如PDO，PEAR :: MDB2或Zend_Db，但老实说，在12年的PHP开发中，我从未必须从一种类型的数据存储基础架构过渡到另一种类型。

Its exceedingly rare to even go from something quite similar like Sqlite, to MySQL. If you did do more than that, you'd have far larger problems anyway.

甚至从像Sqlite这样非常相似的东西到MySQL也是极为罕见的。如果你确实做了更多，那么无论如何你都会遇到更大的问题。

#3

I came up with an interesting concept that would allow developer to create database-agnostic code, but unlike ORM will not sacrifice performance:

我提出了一个有趣的概念，允许开发人员创建与数据库无关的代码，但与ORM不同，它不会牺牲性能：

• simple to use (like ORM)
• 简单易用（如ORM）
• db-agnostic: works with SQL, NoSQL, files etc
• db-agnostic：适用于SQL，NoSQL，文件等
• always optimize queries if vendor allows (sub-select, map-reduce)
• 如果供应商允许，则始终优化查询（sub-select，map-reduce）

The result is Agile Data - database access framework (see video for detailed explanation).

结果是敏捷数据 - 数据库访问框架（有关详细说明，请参阅视频）。

Solving real-life task with DB-agnostic code and Agile Data

1. Start by describing business models.
2. 首先描述商业模式。
3. Create persistence driver $db (a fancy word for DB connection), which could be CSV file, SQL or LDAP.
4. 创建持久性驱动程序$ db（数据库连接的一个奇特的词），可以是CSV文件，SQL或LDAP。
5. Associate model with $db and express your Action
6. 将模型与$ db关联并表达您的Action
7. Execute Action
8. 执行动作

At this point framework will determine best strategy given the capabilities of the database, will map your fields declaration, prepare and execute queries for you, so that you don't have to write them.

此时，框架将根据数据库的功能确定最佳策略，将映射您的字段声明，为您准备和执行查询，以便您不必编写它们。

Code Example

My next code snippet solves a rather complex problem of determining what is the current total debt of all our VIP clients. Schema:

我的下一个代码片段解决了一个相当复杂的问题，即确定我们所有VIP客户当前的总债务是多少。架构：

Next is the vendor-independent code:

接下来是与供应商无关的代码：

$clients = new Model_Client($db);
// Object representing all clients - DataSet

$clients -> addCondition('is_vip', true);
// Now DataSet is limited to VIP clients only

$vip_client_orders = $clients->refSet('Order');
// This DataSet will contain only orders placed by VIP clients

$vip_client_orders->addExpression('item_price')->set(function($model, $query){
    return $model->ref('item_id')->fieldQuery('price');
});
// Defines a new field for a model expressed through relation with Item

$vip_client_orders->addExpression('paid')
  ->set(function($model, $query){
    return $model->ref('Payment')->sum('amount');
});
// Defines another field as sum of related payments

$vip_client_orders->addExpression('due')->set(function($model, $query){
    return $query->expr('{item_price} * {qty} - {paid}');
});
// Defines third field for calculating due

$total_due_payment = $vip_client_orders->sum('due')->getOne();
// Defines and executes "sum" action on our expression across specified data-set

The resulting query if $db is SQL:

结果查询，如果$ db是SQL：

select sum(
  (select `price` from `item` where `item`.`id` = `order`.`item_id` )
  * `order`.`qty`
  - (select sum(`payment`.`amount`) `amount` 
     from `payment` where `payment`.`order_id` = `order`.`id` )
) `due` from `order` 
where `order`.`user_id` in (
  select `id` from `user` where `user`.`is_client` = 1 and `user`.`is_vip` = 1 
)

For other data sources, the execution strategy might heavy-lift some more data, but would work consistently.

对于其他数据源，执行策略可能会重新提升更多数据，但可以保持一致。

I think my approach is a great way to abstract database and I'm working to implement it under MIT license:

我认为我的方法是抽象数据库的好方法，我正在努力在MIT许可下实现它：

https://github.com/atk4/data

https://github.com/atk4/data

#4

You should look into the PDO library.

您应该查看PDO库。

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

PDO提供了一个数据访问抽象层，这意味着，无论您使用哪个数据库，都可以使用相同的函数来发出查询和获取数据。

PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

PDO不提供数据库抽象;它不会重写SQL或模拟缺少的功能。如果需要该工具，则应使用完整的抽象层。

#5

The best way is to use an ORM (Object-relational mapping) library. There are plenty of them for PHP. I've personally used and can recommend doctrine orm (I've used it in combination with silex, which is a minimalistic php framework).

最好的方法是使用ORM（对象关系映射）库。它们有很多用于PHP。我个人使用并且可以推荐doctrine orm（我已经将它与silex结合使用，这是一个简约的php框架）。

Here is an * thread about PHP ORMs where you can find some alternatives if you like: Good PHP ORM Library?

这是一个关于PHP ORM的*线程，如果你愿意，你可以找到一些替代方案：好的PHP ORM库？

#6

It sounds good in theory but in all likelyhood YAGNI.

这在理论上听起来不错，但在所有可能性YAGNI。

You would be better off using an SQL library such as PDO and not worrying about LDAP until you get there.

你最好使用像PDO这样的SQL库，直到你到达那里时不要担心LDAP。

#7

Generally speaking, if you're going to the trouble of using a database then your application will benefit by using features specific to a "brand" of database, and will be a more solid app for it.

一般来说，如果您遇到使用数据库的麻烦，那么您的应用程序将通过使用特定于数据库“品牌”的功能而受益，并且将是一个更加可靠的应用程序。

It is very rare to move from one database system to another. The only time you might realistically consider that aa feature worth implementing is if you're writing some kind of loosely coupled system or framework intended for mass consumption (like Zend Framework, or Django).

从一个数据库系统转移到另一个数据库系统非常罕见。您唯一可能现实地认为值得实现的功能是，如果您正在编写某种用于大规模消费的松散耦合系统或框架（如Zend Framework或Django）。

#8

I always liked using ADOdb. From what I've seen, it looks like it's capable of switching between vastly different platforms.

我一直很喜欢使用ADOdb。从我所看到的情况来看，它看起来能够在截然不同的平台之间切换。

http://adodb.sf.net

http://adodb.sf.net

#9

In fact solution for the topic "where to implement data access logic?" is not complex. All you have to remember is that your model code must be separate from your data access code.

事实上，解决方案主题是“在哪里实现数据访问逻辑？”并不复杂。您需要记住的是，您的模型代码必须与您的数据访问代码分开。

Like:

喜欢：

Model layer with some business logic User::name() method

模型层具有一些业务逻辑User :: name（）方法

class User 
{
  public $first;
  public $last;
  public $email;
  public function name ()
  {
      return $this->first." ".$this->last;
  }
}

Data access layer:

数据访问层：

class Link
{
    $this->connection;
    public function __construct ()
    {
        $this->connection = PDO_Some_Connect_Function();
    }
    public function query ($query)
    {
        PDO_Some_Query ($this->connection, $query);
    }

}

class Database
{
    public $link;
    public function __construct ()
    {
        $this->link = new Link();
    }
    public function query ($query)
    {
        $this->link->query ($query);
    }
}

class Users
{
    public $database;
    public function __construct (&$database)
    {
         $this->database = &$database;
    } 
    public save ($user)
    {
        $this->database->link->query ("INSERT INTO user VALUES( '$user->first', '$user->last', '$user->email' ))";
    }

Usage:

用法：

$database = new Database();

$users = new Users();

$users->save (new User());

In this example it is obvious that you can always change your data access class Link which will run queries on whatever (It means you can save your users on any server as you change link).

在这个例子中，很明显你可以随时更改你的数据访问类链接，它将对任何内容运行查询（这意味着你可以在更改链接时将用户保存在任何服务器上）。

In the same time you have clean model layer code which lives independently and has no idea who and where is saving its objects.

与此同时，您拥有独立生活的清洁模型层代码，并且不知道谁在哪里保存其对象。

Also Database class here seems unnecessary but it in fact it can give birth to great ideas like collecting many links one instance for many db connections in one project.

此外，数据库类似乎没有必要，但实际上它可以产生很好的想法，例如在一个项目中为许多数据库连接收集许多链接。

Also there is single file simplest and almighty framework called db.php(http://dbphp.net) which is built on the pattern I described here and even creates tables automatically with ability to fully control its standard sql field/table settings and synch database structure to your model every time you wish.

还有一个名为db.php（http://dbphp.net）的单文件最简单，最全能的框架，它基于我在这里描述的模式构建，甚至自动创建表，能够完全控制其标准的sql字段/表设置和同步每次您希望模型的数据库结构。

#10

Axon ORM automatically detects changes in your schema without requiring you to rebuild your code.

Axon ORM可自动检测架构中的更改，而无需重建代码。