libpqxx 3.0 tutorial 第1，2章英汉对照

按： postgre sql 数据库的资料很少。libpqxx的风格及设计理念堪称佳品。以下是对该库文档前两章的翻译——蹩脚英语。至于第3章，是具体的编程，含代码实例，有了前两章的知识，语言障碍已经消失，遂不准备继续进行翻译。

Version:1.0 StartHTML:0000000168 EndHTML:0000104053 StartFragment:0000000785 EndFragment:0000104036

---

0. About libpqxx

libpqxx™ is a C++ API to PostgreSQL™. It replaces the earlier libpq++™ with a more Standard Template Library (STL)-conformant interface and makes more extensive use of C++ language features such as exceptions, templates, and strings.

Since libpqxx™ builds on top of the C frontend libpq™, you will need a working libpq™ library on your system prior to getting started with libpqxx™. This is similar to the situation with the older libpq++™ interface.

See the libpqxx™ website for more information.

0. 关于libpqxx

libpqxx是一系列PostgreSQL的前端API。它携STL式接口和异常处理，模板，字符串等更多的现代C++语言特性替代早期的libpq++。

libpqxx建筑在PostgreSQL的C语言库libpq之上，因此，在构建libpqxx之前，系统中需要有正常工作的libpq。这与其前身libpq++是相似的。

更多信息请见libpqxx的主页。

Chapter 1. Why another C++ SQL library?

1. 为什么又开发了一个C++ SQL库？

Several open-source C++ wrapper libraries are available for various databases, as well as some database-independent ones; there's libpql++™, libpq++™,libsqlxx™ and so on.

So was libpqxx™ really necessary?

针对各种数据库管理系统，现在已经有很多开放源码的C++封装的数据库访问库了，其中更有一些通用数据库接口，如libpql++，libpq++，libsqlxx等。

那么libpqxx是否必须呢？

I think it is. The C++ Standard, and its standard library in particular, have stabilized over the mid-1990s; compilers and standard library implementations are still following suit at the time of writing. As a result, most older third-party libraries suffer from symptoms such as:

答案是肯定的。C++标准，尤其是其标准库，自1990年代中期才逐渐稳定，编译器和标准库的实现却还是老一套，这就导致那些较老的第三方接口库存在很多不便，如：

• Not using exceptions. Many libraries return a status code and expect the caller to check for errors "if he's interested." Of course he's interested. But checking each and every call is tedious and error prone.

• 没有使用异常处理机制。很多库都是直接返回状态码，期望着使用者检查错误——如果使用者愿意的话。“他当然愿意”，你可能会这么说，但是，每次调用都需要作错误检查是一件单调乏味的事情，并且潜藏着很多错误。

• Ad-hoc exception classes. Programs using C++ libraries need to deal with various incompatible exception class hierarchies, and error handling becomes a total nightmare with multiple essentially identical catch clauses for every try block.

• 泛滥的异常类。使用C++库的程序需要处理各种异常类继承的不兼容性，遍布每一个try块的catch子句的错误处理方式简直是个噩梦。

• Ad-hoc string classes. As one member of the C++ standards committee put it, if the C++ wasn't going to have a standard string class, "there will be blood in the streets." Luckily, the committee saw the wisdom in this, and civilization was spared.

• 泛滥的字符串处理类。正如C++标准委员会的一位成员所言，“如果C++再不对字符串类标准化的话，世界上的C++字符串类的景象将会如洪水淹没每条街道那样恐怖。”！幸运的是，C++标准委员会终于意识到了这一点，“文明世界就要到来”。

• "A Better C." Using only the oldest features of C++ as extensions to the C language really only complicates things, but there was a time when this was the only way to write portable C++.

• “更好的C”。只使用作为C的扩展的C++陈旧特性的编程方式只能带来事情的复杂化，但在当时，这也是唯一的编写可移植的C++代码的方式。

• Ad-hoc containers and algorithms. Inventing your own container APIs, interesting as it may be, in most cases is wasted development effort. Besides that, there's also the redundant effort of having to learn how they work.

• 泛滥的容器和算法。过度发明自己的容器类API，重复制造轮子，也许很有趣味，但这种开发确实是一种浪费。不仅如此，这样做往往还造成别人的浪费——弄懂它们是如何工作的。

For these reasons, third-party libraries have tended to grow into intrusive frameworks. Third-party code, and class libraries in particular, need to be rewritten to replace such ad-hockery with the rich and standardized functionality now provided by C++. This is what libpqxx™ intends to do for PostgreSQL™. The library was designed to be a forward-looking one, which means that ports to vendor X's compiler Y may have to wait until X improves its support of the Standard.

因为这些原因，很多第三方库呈现出了框架化的趋势。实际上，第三方类库和代码需要以丰富的已被标准化了的现代C++特性重写，以便解决上述问题。而这正是libpqxx要为PostgreSQL做的。libpqxx的设计具有前瞻性，这意味着，若想要将它移植到X供应商的Y编译器上，则X供应商必须改进Y编译器，使得它支持同期的C++标准。

But there were other reasons to design a new SQL frontend from scratch. C++ is a complex language, and the only things stopping a library writer from shooting himself ^[1^] in the foot are years of experience and an extensive idiom of good practices. To name a few typical symptoms of foot-shooting:

还有另一个从头设计新的SQL前端接口库的理由：C++是一门复杂的语言——唯一能防止一个库作者不小心搬起石头砸自己脚的方法就是多年在实践中的练习。我们来看一下典型的砸自己脚的症状：

• Poor language integration. Users need to learn myriads of function names when standard C++ operators can be overloaded instead to make code fit in more naturally with the core language.

• 较少考虑语言集成。用户需要记忆大量的函数名称，而标准C++操作符可以被重载以使得代码更自然的适应于核心语言。

• Overdoing overloading. It's also easy to go overboard with operator or function overloading, and many classes do. Some programmers use identical function names to mean very different things, or define operators whose semantics are not clearly derived from their conventional meanings.

• 过度重载。现实中，很容易走向过度重载函数和操作符的极端，很多类库已经是这个样子的了。一些程序员使用独具个性化的函数名却用来做了不相干的事情，或者定义了与其约定俗成的本意并不相关操作符。

• Resource management. In the complex world of C++, what the user really needs from a library is a little help in ensuring that objects are deleted at the proper time, pointers don't go off into never-never land, and so on. Perhaps the most compelling usage of C++ is the "resource acquisition is initialization" paradigm, but too few libraries apply it as yet.

• 资源管理。在C++的庞杂世界中，用户真正需要库所做的是能够提供一点诸如对象何时被正确释放，指针别成为野指针等此类的帮助。可能更多地强制C++遵循“资源获得即初始化”的原则，但能接受这样做的库还是太少太少了。

• Convoluted family trees. Class derivation is a beautiful thing, but it can be overused. Some libraries construe far-fetched relations between classes, or needlessly expose "convenience" relations using public inheritance.

• 费解的类体系。类继承是个美好的事情，但是却被过度使用。一些库的类间关系错综复杂，或者通过公有继承过度的暴露了类间自认为方便性的关系。

• Overweight classes. Some classes lump together different pieces of functionality with different lifetimes. For instance, some C++ SQL libraries integrate transaction bracketing into a connection class. Yet it is not unreasonable for an application to perform multiple consecutive transactions using the same connection.

• 过于重量级的类。一些类与一些处于不同生命周期的功能无机的杂糅在一起，举例来说，有些C++ SQL类库将事务处理类集成进了数据库连接类里，但是让一个应用程序使用同一个连接来进行多重连续事务的处理却是不合理的。

• Exposure of low-level interfaces. Some C++ wrappers for C-level SQL interface libraries (including libpq++™) don't hide the ugly details of the underlying API. Worse, some only provide "utility" classes that the user can mix in freely with C-level function calls.

• 暴露低层接口。一些C++封装的C语言SQL接口库（包括libpq++）并未隐藏丑陋的底层细节API。更有甚者，一些类仅仅提供了工具类，使得用户可以自由地混合调用C++的高层封装及C语言的函数。

Again, libpqxx™ is an attempt to do better. Hopefully this has resulted in a library that is easier to use than most alternatives while giving you more flexibility, that helps you avoid common pitfalls and reduces the need for debugging.

libpqxx尝试着做得更好。它有望在保持灵活性的同时比其它同类更具易用性，且帮助用户避开了常见的陷阱，减轻了调试的负担。

---

^[1^] Yes, himself. A "writer" is grammatically masculine, unless actual gender is known and definite.

Chapter 2. Class overview

2. 类概览

To work with libpqxx™, you need to be aware of at least the following classes. These are all introduced in a dedicated namespace, pqxx:

为了使用lipqxx，至少需要了解以下一些类，它们都存在于于pqxx名字空间内：

• connection represents a connection from your program to a PostgreSQL™ back-end server. Naturally your program may open multiple connections, simultaneous or not, to any number of databases.

• connection。代表着一个由应用程序到PostgreSQL后端服务器的连接。自然的，允许数据库应用程序同时打开多个到任意数量的数据库的连接。

• work, actually a typedef for the transaction class template, represents a transaction being executed in the context of a connection. This is a unit of work that must be completed as a whole by the database backend. If execution fails, everything done within that transaction is undone by the backend as if it had never happened. ^[2^] You may execute any number of transactions consecutively on a single connection, but only one at a time.

  I recommend that you do not set up your own transactions however, but have them managed for you by a transactor instead (see below).

• work。实质上是对transaction类模板进行的类型重定义，代表着在某连接上下文环境下的某事务执行过程。该事务必须作为一个整体被数据库后端服务器进行处理。如果执行过程失败，所有该事务中已经做了的处理将会被后端服务器撤销，就像什么都没发生过一样。你可以在单个连接里连续的执行任意数量的事务，但同一时刻只能执行其中的一个。我建议你不要创建自己的事务，取而代之，应该使用transaction类来管理查询操作。

• A result is a container holding the resulting data coming out of a query or command executed in a transaction. It behaves just like a standard C++ container as found in the STL, although its contents are not modifiable. The tuples held in the result in turn behave much like (non-modifiable) containers themselves.

• result。它是一个包含了事务中SQL查询或命令结果所返回的数据集合的容器。它的行为与标准C++模板库中的tuples容器相似，其内容也是不可更改的。

I would also like to use this opportunity to plug the invaluable esc function, which you should use whenever you want to include a variable as a string in your SQL (eg. insert it into a text field in a table). See below.

我想借此机会介绍下esc函数，它用在任何当你想在SQL语句字符串内包含变量的时候(例如，向表中插入记录时)。见下面：

There are other classes that may be of interest to you; these are either not essential to writing a simple program using libpqxx™, or are used in such a way that knowledge of them is not immediately necessary. You will probably want to look them up at some point when it becomes necessary to understand complex compiler warnings. Unfortunately C++ compilers are not yet very good at unravelling template-related errors and making them readable, so you may find some long and strange names when they happen.

Some of the classes you may become interested in fairly quickly are:

你可能会对下面的其他类感兴趣：这些类对于使用libpqxx来写简单的程序并不是必要的，而且即便是使用它们，也不需要事先深入了解它们。也许当你遭遇复杂的编译警告时才有必要去查找这些类的用法。不幸的是C++编译器的模板相关的错误信息并不友好，你可能会发现一些又长又奇怪的名字。

一些能快速引发你极大兴趣的类如下：

• broken_connection is an exception class that is thrown if libpqxx™ loses its connection to the back-end. It is derived from the standard C++ exceptionstd::runtime_error, and can generally be treated as such.

• broker_connection。当libpqxx失去与后端服务器的连接时触发的异常类。它继承自标准C++的 exceptionstd::runtime_error，可以以与之相同的方式使用。

• sql_error is an exception class that is thrown if a query to the back-end fails. Like broken_connection it is derived from std::runtime_error, but it also carries information about the failed query. This may be useful for debugging.

• sql_error。当一条向服务器的SQL查询失败时引发此异常类。像broken_connection类一样，它也继承自 std::runtime_error，但附加了查询失败的信息。这些信息对于调试大有帮助。

• icursorstream is a way of iterating over a query result using an SQL cursor. This can be useful for selectively retrieving parts of a query, or for retrieving and processing results in chunks so your program can do useful work on one chunk while the next is still coming in over a network connection. Or you may want to use this to give progress reports to the user between chunk transfers.

• icursorstream。用于使用SQL游标迭代操作查询结果。当选择部分查询的结果，或者处理大块的查询结果时，应用程序可以用它在后续查询结果尚未返回的时候先对已经返回的部分结果进行处理。还可以通过它来获取数据块传输的状态。

• in_doubt_error is an exception class to indicate a rare and difficult error condition. Explaining this is a bit painful, since it invalidates some of what was said above (and is commonly said in the database world).

  In theory, a "transaction" is an integral, atomic unit of work that either fails or succeeds. This is a beautiful idea, but in practice there is nothing that cannot be broken. In practice there is a tiny risk that, like Schroedinger's Cat, the transaction ends up in a third, indeterminate state. This happens when the connection to the backend is lost just when you were waiting for confirmation after completing your transaction. Your transaction may have succeeded (in fact it probably has), but if the connection is lost during just this small time window, it has no way to confirm this to your application.

  When this happens, libpqxx throws an in_doubt_error on the client side, which you may want to catch as a special case. If you do, make sure all alarm bells go off!

  See also the description for robusttransaction, which sacrifices some performance to minimize this risk.

• in_doubt_error。此异常类指示不常见且难以发生的错误情形。将它解释清楚真有点痛苦，因为它对以上所说的那些都有涉及(这在数据库世界中也是常见的)。

  理论上，事务是一个整体，一组操作作为一个原子，要么一起被成功执行，要么就失败。这是一个美好的想法，但实际情况却是任何事情都可能被打破。在实际中，这儿有一点风险，就像薛定谔的猫，事务以不能确定的第三种状态结束了。这种情况发生在当服务器执行完一个事务后正要给前端程序发完成确认时，数据库连接却刚好中断的时候。这个事务可能成功了(实际情况很可能如此)，但正是在这么小的时间窗口内，数据库连接中断了，没有办法向你的应用程序进行事务完整性确认。

  当这种情况发生时，libpqxx在客户端抛出一个in_doubt_error异常，而你正想捕获此异常。如果正好这么做了，当然所有的告警声音都消失了！

  有关这一点更多的信息，请参见下面robusttransaction类的介绍，它牺牲一点性能却最小化了上述风险。

• nontransaction is just like a regular transaction as far your code is concerned (except that it's not a template). The big difference is that where the latter opens a back-end transaction to keep your view and modifications of the database atomic, the former does nothing to maintain integrity. It just passes your queries directly to the database, with no enclosing transaction. This difference is expressed by the fact that the other transaction classes are derived from dbtransaction, an abstract class that expresses the presence of a real backend transaction, whereas nontransaction is not.

• nontransaction。在代码中使用它就像常规的transaction类一样(只是它不是一个模板类这点除外)。它们最大的区别是，后者打开一个后端服务器的事务来保持前端的视图和更改等原子操作，而前者则不做任何维持它们成一个整体的操作。它只是将查询直接递交给数据库，并不封装成为一个事务。这个区别实际上是由于transaction类继承自 代表了一个实际后端服务器事务的dbtransaction抽象类，而nontransaction类却不是。

  This may give you better performance if your transaction does not modify the database, but if it does, may have unforeseen implications if you expect certain levels of consistency in the database--especially if other programs (perhaps even other instances of your own) may be modifying the database at the same time. You'll probably only want to use this in the following cases:

  如果不需要进行写数据库的操作，这将会带来更好的性能。但如果需要写的话，对于你期待的某种级别的一致性将会变得不可预测——尤其是其他程序(甚至可能是你的程序的不同实例)在同一时刻写数据库时。你更可能仅仅在下面这些情况下使用此类：

  • If you only want to read from the database, not modify it, and you know that the data is not going to be updated while your program is running.

  • 仅仅想从数据库读取数据，并不修改，且已知数据在你的程序运行期间不会更改。

  • If you are interested in always getting the very latest information out of your database, even if that means that you may not get a temporally consistent view of the database.

  • 总是从数据库读取相当大的数据，即使这意味着不可能一次读取完整块的数据。

  • When making changes to the database schema; some operations may not be supported by the backend inside backend transactions.

  • 当需要更改数据库模式，但数据库后端服务器不支持内部事务时的一些操作。

• robusttransaction (a template just like transaction) is essentially the same as a transaction, ie. an atomic unit of work, except that it works harder to prevent in_doubt_errors from happening. This comes at the cost of some performance, which is one reason why this behaviour isn't the default. It also creates some extra tables in your database to keep track of what's happening.

  Use this class instead of plain old transaction if you get in_doubt_errors in your application--but only after thoroughly inspecting your code for bugs that might be causing the problem, or you'll only be hiding glitches that should be fixed instead.

• robusttransaction。也是个模板类。与transaction类基本相同，如原子操作等。所不同的是此类严防in_doubt_errors的发生。这将带来一些性能开销，这也是这种行为缺省不被使用的原因。为了追踪发生了些什么，它会在数据库中创建一些额外的表。

  如果在数据库应用程序中遇到了in_doubt_errors错误，请使用该类来替代简单的transaction类——但必须在对可能引发问题的代码做了通篇检查之后。

• tablestream provides simple, relatively direct access to a table for either reading (through its child class tablereader) or writing (through child classtablewriter) using PostgreSQL™'s COPY command. This is typically much faster than issuing SELECT or UPDATE queries.

• tablestream。此类提供简单的，近乎直接的使用PostgreSQL的COPY命令来对表进行读(通过它的子类tablereader)写(通过它的子类tablewriter)的功能。这通常比提交select和update查询速度更快。

• transaction_base defines the common public interface for the transaction-like classes: nontransaction, robusttransaction, and transaction itself. To look up methods available in these classes, see the definition (or documentation) for transaction_base instead.

  You'll usually want to refer to the default transaction type as work, a typedef that means "transaction with default (read-committed) isolation level."

• transaction_base定义了transaction系列类如nontransaction，robusttransaction，transaction等的公共接口。若要查看这几个派生类都有哪些接口函数，只需查看transaction_base的定义或者文档即可。

  通常，可以引用缺省的work类，transaction的类型定义，缺省支持隔离级别的读取提交。

• transactor<> provides a framework that makes it easier for you to write correct, robust transaction code to be executed in a transaction. You should generally try to write your code as a class derived from transactor<>, but this is not required.

  A transactor-derived class may select its own "quality of service" in transactional integrity by choosing a transaction type as its template argument, e.g. transactor<robusttransaction<> >. The default transactor<> is equivalent to transactor<work>. Within your class you can refer to the transaction type used as argument_type.

• transactor<>模板类提供了一个更易于写出正确，健壮的事务代码的框架。通常需要从该类派生一个类来实现自己的功能，但这并不是必须的。

  一个派生自transactor<>的类可以选择自己的QoS事务类型来作为模板参数，如transactor<robusttransaction<> >等，缺省的transactor<>等同于transactor<work>。在派生类的内部，可以引用事务类型用作参数类型。

• notify_listener is an instance of the Observer design pattern. Any code connected to a backend may flag an event using the PostgreSQL™ NOTIFY command. Frontends connected to that same backend database may be listening for an event of that name to occur. A frontend using libpqxx™ does this by registering an observer object derived from notify_listener, which will be invoked by libpqxx™ to handle the event when it occurs.

  Something every programmer using notification listeners in PostgreSQL should know, by the way, is that notifications are not delivered to your program while it is inside a back-end transaction. Nor are they sent until the notifying process has committed any transaction it may be in. This makes sense from a correctness point of view (otherwise your processing of the event might get rolled back just because the transaction you're in happens to fail), but may be a little confusing to the unaware programmer.

• notify_listener。这是观察者模式的一个实例。任何连接到后端服务器的代码都可以使用PostgreSQL的NOTIFY命令被标记为一个事件。当一个时间发生时，多个连接到同一个后端服务器的前端可以同时监听到这个事件。使用了libpqxx的前端是通过注册一个派生自notify_listener的观察者对象来做到如此的，当事件发生时，该对象将被libpqxx库调用。

  顺便说一句，在PostgreSQL中使用通知监听的程序员应该知道，在一个后端服务器事务期间产生的通知是不会被递交到前端程序中的。当等待通知的进程提交了任何事务的时候同样也不会发生。这可能与期望的情形有些出入(你的事件处理进程可能会因为事务失败而发生回滚操作)，可能会对不知内情的程序员造成困惑。

• result::tuple is a nested class defined within result. It refers to a tuple of data within a result object.

• result::tuple。定义在result类中的嵌套类。它是result对象内的一个元组。

• result::field, also nested within result, refers to one field within a tuple. A field contains one retrieved value such as a number or a string, and can be read into variables of various types such as ints or strings.

• Result::field。也是result类中的嵌套类，元组内的一个域。这个域包含一个如字符串数量等的返回值，并且可以被读取到各种类型的变量中，如整型和字符串等。

• result::const_iterator allows you to enumerate the tuples in a result in a similar way to the iterators defined by any standard C++ container.

• Result::const_iterator。可以像标准C++的迭代器那样遍历result类的元组。

• result::tuple::const_iterator similarly iterates over the fields in a tuple.

• result::tuple::const_iterator。在元组内部针对域的迭代器。

Finally, there are also some functions that you may want to know about, that live alone in the pqxx namespace without being part of any class:

最后，pqxx名字空间内还有些不属于任何类的函数也是需要了解的：

• esc, found in the transaction classes, is the canonical way to use strings in your SQL. This function escapes any quotes, backslashes, and other characters in your input string that would otherwise cause syntax errors (or worse, security holes) in your queries when included directly.

  Use these functions whenever you want to have a variable string in your SQL queries. If you don't, you'll be left with annoying bugs or possibly even security holes in your program. Use this function, use this function, use this function. Please. Make me proud.

• esc。用在transaction系列类中，为在SQL语句中使用字符串提供了便利性。该函数可以转义直接包含在你的查询输入字符串中的任何引号，反斜线等会引起语法错误(更甚者，安全漏洞)。

  任何时候，都应在含有可变字符串的SQL查询里使用这些函数。不然，将会面对讨厌的bug或者安全漏洞。请使用此函数，相信我，没错的，请让我自豪。

• to_string knows how to render many types of values as strings. This is used internally by libpqxx to convert values coming out of or going into the backend, and doesn't respect locale settings.

• to_string。可以将很多类型转换为字符串。在libpqxx内部，它被用来对后端服务器的输入输出做转换，而不用考虑本地化的具体细节设置。

• from_string is the inverse of to_string. It is used by eg. result::field::to() to convert the incoming field data (which is in text format) to the desired C++ type.

• from_string。to_string的逆向操作。举个例子，用于：result::field::to()转换输入域数据(文本类型)到期望的C++类型。

---

^[2^] Actually libpqxx™ provides three classes doing this at various levels of reliability, called nontransaction, transaction, and robusttransaction for no, standard, and best reliability respectively.