数据库抽象 - 支持多种语法
在我正在开发的 PHP 项目中，我们需要创建一些 DAL 扩展来支持多个数据库平台。 我们遇到的主要缺陷是不同的平台有不同的语法 - 值得注意的是 MySQL 和 MSSQL 有很大不同。
对此最好的解决方案是什么？
以下是我们讨论过的几个：
基于类的 SQL 构建
这将涉及创建一个允许您逐位构建 SQL 查询的类。 例如：
$stmt = new SQL_Stmt('mysql');
$stmt->set_type('select');
$stmt->set_columns('*');
$stmt->set_where(array('id' => 4));
$stmt->set_order('id', 'desc');
$stmt->set_limit(0, 30);
$stmt->exec();
不过，单个查询确实涉及相当多的行。
SQL 语法重新格式化
这个选项更加简洁 - 它会读取 SQL 代码并根据输入和输出语言重新格式化它。 然而，就解析而言，我可以看到这是一个慢得多的解决方案。
In a PHP project I'm working on we need to create some DAL extensions to support multiple database platforms. The main pitfall we have with this is that different platforms have different syntaxes - notable MySQL and MSSQL are quite different.
What would be the best solution to this?
Here are a couple we've discussed:
Class-based SQL building
This would involve creating a class that allows you to build SQL querys bit-by-bit. For example:
$stmt = new SQL_Stmt('mysql');
$stmt->set_type('select');
$stmt->set_columns('*');
$stmt->set_where(array('id' => 4));
$stmt->set_order('id', 'desc');
$stmt->set_limit(0, 30);
$stmt->exec();
It does involve quite a lot of lines for a single query though.
SQL syntax reformatting
This option is much cleaner - it would read SQL code and reformat it based on the input and output languages. I can see this being a much slower solution as far as parsing goes however.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我建议基于类的 SQL 构建，并推荐 Doctrine、Zend_Db 或 MDB2。 是的，如果需要更多行来编写简单的选择，但至少您可以依赖解析器并且不需要重新发明轮子。
使用任何 DBAL 都是速度上的权衡，不仅仅是数据库执行，而且第一次使用其中任何一个都会比真正熟悉它时更痛苦。 另外，我几乎 100% 确定生成的代码不是最快的 SQL 查询，但这就是我之前所说的权衡。
最终取决于您，所以即使我不会这样做并且这肯定不是不可能的，但问题仍然是您是否可以通过实现自己的 DBAL 来真正节省时间和资源（从长远来看）。
I'd recommend class-based SQL building and recommend Doctrine, Zend_Db or MDB2. And yeah, if it requires more lines to write simple selects but at least you get to rely on a parser and don't need to re-invent the wheel.
Using any DBAL is a trade-off in speed, and not just database execution, but the first time you use either of those it will be more painful than when you are really familiar with it. Also, I'm almost a 100% sure that the code generated is not the fastest SQL query but that's the trade-off I meant earlier.
In the end it's up to you, so even though I wouldn't do it and it sure is not impossible, the question remains if you can actually save time and resources (in the long run) by implementing your own DBAL.
解决方案可能是为不同的平台设置不同的查询集，其 ID 类似于
MySql: GET_USERS = "SELECT * FROM users"
MsSql: GET_USERS = ...
PgSql: GET_USERS = ...
然后在启动时加载所需的集查询并引用然后
Db::loadQueries(platform):
$users = $db->query(GET_USERS)
A solution could be to have different sets of queries for different platforms with ID's something like
MySql: GET_USERS = "SELECT * FROM users"
MsSql: GET_USERS = ...
PgSql: GET_USERS = ...
Then on startup you load the needed set of queries and refers then
Db::loadQueries(platform):
$users = $db->query(GET_USERS)
这样的方案不会考虑 SQL 提供的所有丰富性，因此您最好为每个数据库的所有表使用代码生成的存储过程。
即使您使用更多数据库模型感知的参数化存储过程（即它们进行连接或用户感知，因此针对每个供应商进行了优化），这仍然是一个很好的方法。 我始终认为数据库接口层不仅仅为应用程序提供简单的表，因为这种方法可能会占用大量带宽并且浪费往返时间。
Such a scheme would not take account of all the richness which SQL offers, so you would be better off with code-generated stored procs for all your tables for each DB.
Even if you use parametrized stored procs which are more database model-aware (i.e. they do joins or are user-aware and so are optimized for each vendor), that's still a great approach. I always view the database interface layer as providing more than just simple tables to the application, because that approach can be bandwidth-intensive and roundtrip wasteful.
如果您有一组支持它的后端，我同意生成存储过程来形成合同是最好的方法。 但是，如果您的后端在存储过程方面的能力有限，则这种方法不起作用，在这种情况下，您需要构建一个抽象层来实现 SQL 或基于抽象/有限的 sql 语法生成目标特定的 sql。
if you have a set of backends that support it, I would agree that generating stored procedures to form a contract is the best approach. This approach, however, doesnt work if you have a backend that is limited in capabilty with regards to stored procedures in which case you build an abstaction layer to implement SQL or generate target specific sql based on an abstract/limited sql syntax.