65.9K
CodeProject 正在变化。 阅读更多。
Home

具备 IDE 的脚本化 SQL 查询生成框架：SQLpp (v1.4)

Jonathan de Halleux
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.98/5 (44投票s)

2003 年 7 月 23 日

11分钟阅读

viewsIcon

462060

downloadIcon

5372

用于在 C++ 和 Lua 中生成 SQL 查询的辅助框架。

Sample image

目录

引言

SQLpp

SQLpp 的目标是简化 SQL 查询的创建，尤其是在 C++ 中。 .NET 包装器正在开发中。

SQLpp 使用 Boost 图库 (BGL)，参见 [2]，来对表和约束进行内部图表示，以简化查询生成。

SQLpp 遵循 Andreas Saurwein 提出的文章 SQL WHERE - the easy way，参见 [1]。作者介绍了一个小型辅助类，用于方便地格式化 C++ 中的 where 子句。基于他的想法，SQLpp 的构建是为了简化繁琐且易出错的 SQL 查询编写。

最新信息和下载可在 http://sqlpp.sourceforge.net/ 获取。

文章

提供了完整的类文档 (使用 Doxygen)，因此我将不详细介绍类的成员函数，而是对 SQLpp 进行总体概述，并提供各种示例来说明。

在本文中，假设读者对 SQL 和 SQL 查询有基本了解。

SQLpp 仍处于开发阶段，我在此呈现它以获得**建设性**建议。未来 SQLpp 将被嵌入到一些 GUI 中...

特点

以下是 SQLpp 功能列表

  • 1.6
    • Dot (graphviz) 生成器
  • 1.5
    • 托管 C++ 包装器
    • C# 包装器生成器
  • 1.4
    • 完全重写了框架的核心，
    • 子查询
    • 在子查询中
    • CASE 语句
    • 类型常量
    • date, time, timestamp
    • 许多新的聚合函数
    • 添加了新的 join 方法来链接两个查询表
    • 多数据库支持
  • 1.1
    • 名称和别名处理，
    • 多字段主键、唯一约束和引用约束（外键），
    • 自引用支持，
    • inner join, outer join (left, right, full),
    • join 解析（如果可能）*，
    • 字段前缀助手，
    • 字段值比较，
    • 字段-字段比较，
    • and, or, xor, not,
    • is (not) null,
    • exists,
    • union, excepts, intersects
    • 聚合函数：count, avg, max, min, sum, mean
    • in 谓词，
    • 子查询，
    • SQL 创建字符串解析器（使用 Spirit）

* 给出两个表，SQLpp 会检查相应的引用约束。如果该约束是唯一的，SQLpp 会知道这是执行连接要使用的约束。

示例数据库

在详细介绍 SQLpp 之前，让我用一个简单的例子来说明。假设我们有以下数据库

有几点需要注意

  • 所有表都有主键，
  • EmployeeStores 有一个多字段主键，
  • 约束具有多个键（参见 Clients 中的 unique，或上面的注释），
  • Employees 包含一个自引用（ChiefID）

创建 SQLpp 数据库

为了帮助用户，SQLpp 必须**学习**数据库结构。这可以通过硬编码表、字段和约束来完成，或者更轻松地使用内置的 SQL 解析器（使用 Spirit 7.0 编写）。

1.4 版新增：您需要向构造函数提供一个**数据库适配器**。数据库适配器负责处理不同可用数据库之间的差异，例如日期格式、前向声明支持、子查询支持等。

  • C++
    //creating a database adaptor
adapters::database_adaptor_shared_ptr mysql
                   (new adapters::mysql_adaptor());

database_shared_ptr db = database::create(mysql);
if(!parse_sql( sql ))
...// could not parse the stringLua

备注

  • 还提供了 mssql_adaptor

这就是您需要做的。在内部，SQLpp 使用 BGL 构建了一个图，其中顶点是表，边是引用约束，如下图所示。

您还可以通过生成其 SQL 创建语句来验证数据库结构

  • C++
    cout<<db->get_create_statement()

多数据库支持

适配器用于匹配每个数据库引擎的特定性。它们有两个主要属性

  1. 转换方法，用于处理日期、字符串等的格式化，
  2. 支持标志，告知框架某个特定功能是否受支持（例如前向声明）

适配器位于 adaptors 命名空间中。

创建查询

一旦数据库构建完成，就可以用来创建查询。查询对象可以从数据库构造

select_query_shared_ptr q = db.create_query();

SQL 代码通过以下方式生成

cout<<q->get_sql();

一旦查询被分配，您需要执行以下步骤

  1. 添加和连接表，
  2. 添加字段（可选），
  3. 指定 where, group by, having 和 order by 条件（可选）。

以上步骤将通过一系列从非常简单到更复杂的 SQL 查询来阐明。

指定字段

一个简单的查询

我们要列出所有客户

  • 期望的 SQL
    SELECT * FROM Clients
  • C++
    q->add_table( db->get_table("Clients") );

备注

  1. database::get_table 检索 Clients 表（作为共享指针）。如果找不到 Clients 表，则会抛出异常。
  2. select_query::add_table 将 Clients 表添加到查询中，并返回查询表指针。查询表是表的实例，稍后用于指定字段归属。请注意，我们需要区分查询表和表以处理自引用的表。

指定一些字段

选择所有客户的姓 :

  • 期望的 SQL
    SELECT C.ClientLastName AS 'Last Name' FROM Clients AS C
  • C++
    query_table_shared_ptr tc=q->add_table( db->get_table("Clients"), "C" );
q->add_field( tc->get_field("ClientLastName"), "Last Name" );

备注

  1. C 是 Clients 表的别名
  2. query_table::get_field 检索 ClientLastName 字段（作为共享指针），
  3. select_query::add_field 添加 ClientLastName 字段，别名为 Last Name。别名是可选的。
  4. SQLpp 将自动在字段描述中添加表名或别名。

聚合函数

select count( * ) from clients :

  • 期望的 SQL
    SELECT COUNT(ClientID) FROM Clients
  • C++
    query_table_shared_ptr tc=q->add_table( db->get_table("Clients"));
q->add_field( count( tc ) );

备注

  1. count 接受任何 query_field 指针。如果为 NULL，则使用 *。
  2. sum, avg, max, min, sum 也可用
  3. 要获得 DISTINCT，请将 `true` 作为第二个参数传入
    q->add_field( count( tc->get_field("ClientID"), true ) );

值表达式

select product price * order quantity from orders and products:

  • 期望的 SQL
    SELECT O.OrderQuantity * P.ProductPrice 
FROM ...
  • C++
    query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "C" );
query_table_shared_ptr tp=q->join( to, db->get_table("Products"), "C" );

q->add_field( 
    to->get_field("OrderQuantity") 
  * tp->get_field("ProductPrice") 
);

备注

  1. join 将在后面解释。它执行 inner join。
  2. 这是两个查询字段之间的操作。
  3. 允许的运算符是：+, *, -, /
  4. Lua 中尚未实现

select product price * VAT from products :

  • 期望的 SQL
    SELECT P.ProductPrice * 1.21 FROM Products
  • C++
    double vat = 1.21;

q->add_field( tp->get_field("ProductPrice") * vat  );

备注

  1. 这是数值与查询字段之间的操作。
  2. 允许的数值是：float, double, int, uint, long, ulong,
  3. 当然，您可以混合所有这些来构建复杂的算术表达式

select sum( product price * order quantity * VAT ) from products and orders :

  • 期望的 SQL
    SELECT SUM( P.ProductPrice * O.OrderQuantity * 1.21 )  FROM ...
  • C++
    double vat = 1.21;

q->add_field( sum( 
    tp->get_field("ProductPrice")
  * tp->get_field("ProductPrice") 
  * vat )
  );

表连接

连接表

select orders date and client name:

  • 期望的 SQL
    SELECT C.ClientLastName AS 'Name', O.OrderDate 
FROM Orders AS O
INNER JOIN Clients AS C USING(ClientID)
  • C++
    query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "O" );
query_table_shared_ptr tc=q->join( to, db->get_table("Clients"), "C" );
q->add_field( tc->get_field("ClientLastName"), "Name" );
q->add_field( to->get_field("OrderDate"));

备注

  1. select_query::join 用于连接 Orders 表和 Clients 表。在内部，SQLpp 首先检查表之间是否存在引用约束，使用数据库图。如果不存在，则抛出异常。
  2. 使用 join 时，必须始终先指定源表（包含外键），然后是目标表（被引用的表），
  3. 在内部，select_query 构建查询表和 join 的图：查询表是顶点，join 是边，
  4. 我们没有指定连接类型，因此默认为 INNER JOIN，
  5. SQLpp 将检测是否可以使用 USING 子句（外键和主键名称相同），如果不能，它将切换到 ON。

连接具有同一表的多个实例的表

选择员工姓名及其各自的经理姓名（由 ChiefID 引用）:

  • 期望的 SQL
    SELECT E.EmployeeLastName, Ch.EmployeeLastName 
FROM Employees AS E
INNER JOIN Employees AS Ch ON E.ChiefID = Ch.EmployeeID
  • C++
    //let disable using
select_query::set_support_using(false);
query_table_shared_ptr te=
      q->add_table( db->get_table("Employees"), "E" );
query_table_shared_ptr tch=
      q->add_table( db->get_table("Employees"), "Ch" );
q->join(
   te, // joined table (contains the foreing key)
   tch // referenced table
 );
q->add_field( tc->get_field("LastName"));
q->add_field( tch->get_field("LastName"));

备注

  1. 在这个例子中，您可以看到为什么我们需要使用查询表：Employees 有一个自引用（ChiefID）。
  2. 我们添加了两个字段 EmployeeLastName，但将它们链接到不同的查询表。
  3. join 可用于连接已添加到查询中的表
  4. 您可以使用 select_query::get_table_joins() 显示表连接。
Employees AS E -> Employees AS Ch

复杂连接

select order date, product name, client name, vendor name and store address:

前面的两个例子展示了两个表之间的简单连接。当您有更多涉及多个表的复杂连接时会发生什么？会出现几个问题

  1. 所有表都连接了吗？
  2. 如何选择表顺序来执行连接？

这两个问题都可以通过图论和 BGL 轻松回答。例如，第二个问题可以转化为：如何找到一个路径来逐步遍历所有顶点（参见图片）？答案是使用 BGL 的 undirected_dfs。

  • 期望的 SQL
    SELECT O.OrderDate, P.ProductName, C.ClientLastName, 
E.EmployeeLastName, S.StoreAddress
FROM (((Orders AS O
INNER JOIN Clients AS C USING(ClientID))
INNER JOIN Products AS P USING(ProductID))
INNER JOIN Employees AS E USING(EmployeeID))
LEFT OUTER JOIN Stores AS S Using(StoreID)
  • C++
    query_table_shared_ptr to=q->add_table( db->get_table("Orders"), "O" );
query_table_shared_ptr tc=q->join( to, 
            db->get_table("Clients"), "C" );
query_table_shared_ptr tp=q->join( to, 
            db->get_table("Products"), "P" );
query_table_shared_ptr te=q->join( to, 
            db->get_table("Employee"), "E" );
query_table_shared_ptr ts=
      q->join( tp, db->get_table("Stores"), "S" , LeftOuterJoin );

q->add_field( to->get_field("OrderDate"));
q->add_field( tp->get_field("ProductName"));
q->add_field( tc->get_field("ClientLastName"));
q->add_field( te->get_field("EmployeeLastName"));
q->add_field( ts->get_field("StoreAddress"));

备注

  1. 添加了 LeftOuterJoin 来说明如何指定不同的连接类型。其他支持的类型有：LeftJoin, RightOuterJoin, FullJoin。

谓词

在之前的示例中，我们没有指定 where 条件。要做到这一点，只需执行

q->set_where( ... );

在下面的每个示例中，我们假设表已被添加并连接。

字段 - 值比较

select from clients where client first name = "John"

  • 期望的 SQL
    SELECT * FROM Clients
  • C++
    q->set_where(
    t_clients->get_field("ClientFirstName") == "John"
   );

备注

  1. == 运算符是一个模板函数，它创建保存字段和值的类
  2. 不进行类型检查。
  3. 我们可以简化操作，并将 ClientFirstName query_field 指针存储起来
    query_field_shared_ptr qf_cfn = t_clients->get_field("ClientFirstName");

字段字段比较

select from clients, orders where cliendid = order.clientid

  • 期望的 SQL
    SELECT * FROM Clients AS C, Orders AS O WHERE C.ClientID = O.ClientID
  • C++
    q->set_where(equal(
         qf_cfn, 
         t_orders->get_field("ClientID") 
         )
   );

备注

  1. 我们使用 equal 方法而不是 == 运算符，因为它与智能指针的 == 运算符冲突。
  2. 可以使用 not_equal 来表示 !=。

NULL：is null, is not null

select from clients where client first name is null

  • 期望的 SQL
    SELECT * FROM Clients WHERE ClientFirstName IS NOT NULL
  • C++
    q->set_where(is_not_null(qf_cfn) );

备注

  1. is_null 可用于生成 IS NULL

集合选择：in (...)

select from clients where client first name in ('John' or 'Bill')

  1. 期望的 SQL
    SELECT * FROM Clients WHERE ClientFirstName in ('John','Bill')
  2. C++
    vector<string> v;
v.push_back("John");
v.push_back("Bill");
q->set_where(in(
    qf_cfn,
    v.begin(), 
    v.end()
    )
 );

备注

  1. 方法 in 接受一个迭代器范围（`v.begin(), v.end())`。由于它是一个模板函数，它不依赖于容器类型（必须支持前向迭代器）。
  2. 容器可以包含任何（参见数据类型部分）类型的数据

集合子查询选择：in (select ...)

select from clients where client first name in (select client first name from clients where ClientID > 10)

  1. 期望的 SQL
    SELECT * FROM Clients WHERE 
     ClientFirstName in (SELECT ClientFirstName FROM 
     Clients WHERE ClientID > 10)
  2. C++
    // creating subquery
select_query_shared_ptr sub_q = 
 db::create_query();... t_sub_client= sub_q->add_table(
db->get_table("Clients")); sub_q->add_field( t_sub_client,
                    t_sub_client->get_field("ClientFirstName") );

// creating main query
q->set_where(in(
    qf_cfn,
    sub_q
    )
 );

范围选择：between ... and ...

select from clients where clientid between 10 and 50

  • 期望的 SQL
    SELECT * FROM Clients WHERE ClientID BETWEEN 10 AND 50
  • C++
    q->set_where(between( qf_cid,10,50 )  );

字符串匹配：like ...

select from clients where client first name like "John%"
  • 期望的 SQL
    SELECT * FROM Clients WHERE ClientFirstName LIKE 'John%'
  • C++
    q->set_where(like(qf_cfn, "John%"));

逻辑运算符：and, or, xor, not

select from clients where client first name = "John" and not client first like 'M%' "Martin"

  • 期望的 SQL
    SELECT * FROM Clients WHERE ClientFirstName = 'John' 
                 AND NOT ClientFirstName LIKE 'M%'
  • C++
    q->set_where(and(
   qf_cfnq == "John",
   not( like(qf_cfln, "M%") )
   )
 );

备注

  1. or 和 xor 也可用，
  2. not 是一个一元运算符。

CASE：case ... when ... else... end 或 case when ... else ... end（1.4 版新增）

select case clientID when 1 then 'found' else 'not found' end from clients

  • 期望的 SQL
    SELECT 
CASE C.ClientID
   WHEN 1 THEN 'found' 
   ELSE 'not found'
   END
FROM Clients AS C
  • C++
    q->add_field(
    case_( qc->get_field("ClientID") )
    ->when( to_expression(1) , to_expression("found") )
    ->else( to_expression("not found")
    );

备注

  1. case 的另一种形式也可用，使用 `case_ without` 参数。

集合运算：union, except, intersect

查询的 Union、difference 和 intersection 操作如下

query_shared_ptr q1, q2, q;
q=union_(q1,q2);
q=except(q1,q2);
q=intersect(q1,q2, true); // add ALL

备注

  1. union_ 有一个下划线，因为 union 是 C++ 的保留字，
  2. 您可以通过将第三个参数设置为 `true` 来指定 ALL

杂项 C++

智能指针

所有 SQLpp 对象都作为智能指针进行操作，使用 Boost 的 shared_ptr 实现。在内部，一些对象存储为 weak_ptr 以打破循环。

异常

当所需行为出现缺陷时，许多方法都会抛出异常：错误的字段名、错误的连接等。所有类都抛出 sqlpp_exception（派生自 std::exception）。

try
{
    // proted here the SQLpp code
}
catch (std::exception& e)
{
   cout<<e.what()<<endl;
}

SQL 解析器

SQL 解析器是一个“自制”解析器，使用 Spirit 1.7 构建。一如既往，**风险自负**。它支持

  • 前向表声明（外键可以引用尚未创建的表），
  • 多字段约束，
  • 约束命名，
  • 许多 SQL 类型
  • 内联主键（在字段定义中指定主键）
  • alter table ... add constraint ... 声明

包含 sqlpp/parsers/sql_parser.hpp 即可使用它。

在您的项目中进行使用

先决条件

  1. VC7.0 或 VC 7.1（更好）
  2. 已安装 Boost 1.30 并添加到包含目录，
  3. Spirit 1.7：您可以从 Spirit 网站下载并直接将文件复制到您的 boost 目录中。也可能与 1.6.1 兼容。

命名空间

命名空间模仿 C# 中的包含文件目录结构。所有 SQLpp 都位于 sqlpp 命名空间中，select_query 和 query_predicate 位于 queries 中，解析器位于 parsers 中。

历史

  • v1.6, 2003-08-09
    • 移除了 Lua 绑定，
    • 添加了托管 C++ 包装器
    • 添加了 Dot 和 C# 包装器输出
  • v 1.4, 2003-08-28，
    • 完全重写了框架的核心，
    • 子查询
    • 在子查询中
    • CASE 语句
    • 类型常量
    • date, time, timestamp
    • 许多新的聚合函数
    • 添加了新的 join 方法来链接两个查询表
    • 多数据库支持
  • v1.3.2, 2003-08-14，
    • 修复了计算查询 joins 时的错误（现在使用无向图）
    • 添加了 select_query::get_table_joins
  • v1.3.1, 2003-08-08
    • 添加了 vc7.0 项目
  • v1.3, 2003-08-04，
    • 添加了 LuaIDE
  • v1.2, 2003-07-29，
    • 添加了 Lua 脚本，尚未完成，但框架的一部分已经包装
  • v1.1, 2003-07-24，
    • 简化了 query_field 的语义：tc + tc->get_field 变为 tc->get_field，
    • 添加了值表达式，
    • 添加了聚合函数，
    • 修复了 in_predicate 中的错误，
    • 将测试分离到小型文件中
    • 将大量代码移至 cpp 文件
  • v1.0, 2003-07-23, 初始发布。

参考文献

© . All rights reserved.