在C中创建一个postgresql数据库

This article is the first in a series covering the use of the PostgreSQL RDBMS with C using the official libpq library. In this post I will introduce the library with a simple program which creates a database and then adds a few tables and views. In future articles I’ll cover DML or CRUD: inserting, querying, updating, deleting data, and also reading database schemas.

本文是使用官方libpq库将PostgreSQL RDBMS与C结合使用的系列文章的第一篇。 在本文中，我将通过一个简单的程序介绍该库，该程序创建一个数据库，然后添加一些表和视图。 在以后的文章中，我将介绍DML或CRUD：插入，查询，更新，删除数据以及读取数据库模式。

目标观众 (Target Audience)

Relational database management systems and SQL are large and complex areas so I will not attempt to give an introduction to them here — this article will assume that you understand at least the basics of tables, column types, primary keys and foreign keys, as well as creating them using SQL. The code for this post assumes you have PostgreSQL installed, and ideally pgAdmin as well.

关系数据库管理系统和SQL是一个庞大而复杂的领域，因此在这里我不会尝试对其进行介绍-本文将假定您至少了解表，列类型，主键和外键的基本知识，以及使用SQL创建它们。 这篇文章的代码假设您已经安装了PostgreSQL ，理想情况下还应该安装pgAdmin 。

数据库架构 (The Database Schema)

For this project I will be creating a very simple database with the two tables shown in this diagram, complete with their primary key and foreign key constraints.

对于该项目，我将使用该图中所示的两个表创建一个非常简单的数据库，其中包括它们的主键和外键约束。

I will also create another table which has date and Boolean columns so that in a later post I can demonstrate inserting these data types. Finally I’ll create a view which joins the two tables in the diagram to show data from each.

我还将创建另一个具有date和Boolean列的表，以便在以后的文章中可以演示如何插入这些数据类型。 最后，我将创建一个视图，该视图将图中的两个表连接起来以显示每个表中的数据。

生成CREATE语句 (Generating the CREATE Statements)

The chances are your development database evolved over a period of weeks or months, with lots of fiddling about using a GUI and no definitive set of SQL scripts to recreate it. Fortunately it is easy to generate scripts to create a production database using pgAdmin.

可能是您的开发数据库在数周或数月的时间内发展了起来，并且对使用GUI充满了摆弄，而没有确定SQL脚本集来重新创建它。 幸运的是，使用pgAdmin生成脚本来创建生产数据库很容易。

To generate a CREATE script for an object such as a table, view or the database itself, right-click the object in the tree view, mouseover Scripts and click “CREATE Script”. This will show the SQL in a new window for you to copy/paste or save to a file.

要为诸如表，视图或数据库本身之类的对象生成CREATE脚本，请在树形视图中右键单击该对象，将鼠标悬停在Scripts上，然后单击“ CREATE Script”。 这将在新窗口中显示SQL，供您复制/粘贴或保存到文件。

You can also generate a script for all the database objects (but not the database itself) by right-clicking the database name and selecting Backup. Enter a filename and in the Format dropdown select Plain. Now click the Dump Options #1 tab and select the “Only schema” checkbox. Then click the Backup button followed by the Done button. This may take a few seconds if your database schema is large.

您还可以通过右键单击数据库名称并选择“备份”为所有数据库对象(而不是数据库本身)生成脚本。 输入文件名，然后在格式下拉列表中选择普通。 现在，单击“转储选项＃1”选项卡，然后选择“仅架构”复选框。 然后单击“备份”按钮，然后单击“完成”按钮。 如果您的数据库架构很大，则可能需要几秒钟。

安装libpq (Installing libpq)

If you use a Debian-based Linux distro you can install libpq with this command:

如果您使用基于Debian的Linux发行版，则可以使用以下命令安装libpq：

sudo apt-get install libpq-dev

sudo apt-get install libpq-dev

libpq函数 (libpq Functions)

The libpq library is comprehensively documented on the PostgreSQL site https://www.postgresql.org/docs/9.5/libpq.html and provides a large number of functions, constants and structs. They start with PQ or PG so are easy to spot, and my own functions start with CDPG.

PostgreSQL网站https://www.postgresql.org/docs/9.5/libpq.html上全面记录了libpq库，并提供了大量的函数，常量和结构。 它们以PQ或PG开头，因此很容易发现，而我自己的功能以CDPG开头。

代码 (The Code)

The code for this project consists of the following files:

该项目的代码包含以下文件：

• cdpgconnection.c
  cdpgconnection.c
• cdpgconnection.h
  cdpgconnection.h
• cdpgddl.h
  cdpgddl.h
• cdpgddl.c
  cdpgddl.c
• ddldemo.c
  ddldemo.c

which you can clone/download from the Github repository.

您可以从Github存储库克隆/下载该文件。

Let’s look at cdpgconnection.h and cdpgconnection.c first.

我们先来看cdpgconnection.h和cdpgconnection.c 。

These two functions open and close a connection for us, and are really only standins for something rather more sophisticated which you are likely to use in a real-world situation. Specifically what I am thinking of is a connection pool management system which maintains a number of open connections through the lifetime of the process, issuing free ones and then returning them as requested by the application. The reason for this is that opening a connection is resource-heavy and the connection pool pattern is therefore more efficient and widely used. For this demo though I’ll stick with this simple solution.

这两个功能为我们打开和关闭了一个连接，实际上仅代表您可能在现实世界中使用的更为复杂的功能。 我具体想到的是一个连接池管理系统，该系统在整个过程的生命周期内维护许多打开的连接，发出免费的连接，然后根据应用程序的要求返回它们。 这样做的原因是，打开连接会占用大量资源，因此连接池模式更加有效并且得到了广泛使用。 对于本演示，我将坚持使用这种简单的解决方案。

As you can see CDPGget_connection accepts a connection string and attempts to open a PGconn using the PQconnectdb function. If the attempt is unsuccessful we output the error message retrieved using PQerrorMessage (PostgreSQL has adopted the highly innovative practice of issuing helpful and informative error messages) and return NULL. If the connection is opened successfully it is returned.

如您所见， CDPGget_connection接受连接字符串，并尝试使用PQconnectdb函数打开PGconn 。 如果尝试失败，我们将输出使用PQerrorMessage检索到的错误消息(PostgreSQL采用了极富创新性的做法，即发布有用的和有用的错误消息)并返回NULL。 如果连接成功打开，则返回该连接。

CDPGclose_connection closes the connection with PQfinish, although as I implied above it could be regarded as a placeholder for a function which returns an open connection to the pool.

CDPGclose_connection关闭与PQfinish的连接，尽管正如我在上面暗示的那样，它可以被视为函数的占位符，该函数将打开的连接返回到池中。

Now let’s move on to the heart of this project: the functions which create the database and its objects.

现在，让我们继续该项目的核心：创建数据库及其对象的函数。

The CDPGcreatedb function will attempt to create an empty database and then, if successful, will call a separate function to create the tables and other objects.

CDPGcreatedb函数将尝试创建一个空数据库，然后，如果成功，将调用一个单独的函数来创建表和其他对象。

Firstly we create a string containing the SQL to create a database, and then call CDPGget_connection with. There are a few of points to note about the connection string:

首先，我们创建一个包含SQL的字符串以创建数据库，然后使用CDPGget_connection进行调用。 关于连接字符串，有几点要注意：

• You will need to edit user and password, obviously to a login with the necessary permissions.
  您将需要编辑用户名和密码，显然是具有必要权限的登录名。

• The connection string shown here has no host of hostaddr and will therefore try to use localhost/127.0.0.1. If you are using a database on a remote server you will need to set one of these properties. When deciding which to use you need to ask yourself “Is my sysadmin more likely to change the IP address and forget to tell me, or to forget to change the DNS settings?”

  此处显示的连接字符串没有hostaddr host ，因此将尝试使用localhost / 127.0.0.1 。 如果在远程服务器上使用数据库，则需要设置以下属性之一。 在决定使用哪个时，您需要问自己“我的系统管理员更可能是更改IP地址并忘记告诉我，还是忘记更改DNS设置？”

• You might also need to include port if it is not the PostgreSQL standard 5432.
  如果不是PostgreSQL标准5432，则可能还需要包含端口。
• There are many more optional parameters which are listed in the documentation linked above — I have just used the minimum required to get up and running.
  上面链接的文档中列出了更多可选参数-我刚刚使用了启动和运行所需的最低要求。

If we don’t get a NULL connection back we can attempt to run the SQL with the connection using PQexec. This returns a pointer to a PGresult which we can check with PQresultStatus. If something went wrong we output the error message: note this “belongs” to the connection not the result. If all went well we call CDPGcreateDBobjects.

如果没有返回NULL连接，则可以尝试使用PQexec通过连接运行SQL。 这将返回一个指向PGresult的指针，我们可以使用PQresultStatus进行检查。 如果出现问题，我们将输出错误消息：请注意，该“属于”连接而不是结果。 如果一切顺利，我们称之为CDPGcreateDBobjects 。

Finally we need to tidy up by calling PQclear on the result and CDPGclose_connection on the connection.

最后，我们需要通过调用整理PQclear的结果和CDPGclose_connection的连接。

CDPGcreateDBobjects works along the same lines but as we have more than one bit of SQL to run these are added to an array which is iterated in a for loop. In a real-world situation you are unlikely to hard code SQL; you would probably read it from a file.

CDPGcreateDBobjects工作原理相同，但是由于要运行多个SQL，因此将它们添加到在for循环中迭代的数组中。 在现实世界中，您不太可能对SQL进行硬编码。 您可能会从文件中读取它。

This time if a PQexec fails we break out of the loop. In this situation there really isn’t any point pressing on trying to create subsequent objects.

这次，如果PQexec失败，我们将退出循环。 在这种情况下，尝试创建后续对象确实没有任何意义。

That’s the code finished so let’s try it out.

代码完成了，让我们尝试一下。

This is very straightforward, after the #includes and heading text we just call CDPGcreatedb.

这非常简单，在#includes和标题文本之后，我们仅将其称为CDPGcreatedb 。

Now we can build and run the program with this command:

现在，我们可以使用以下命令来构建和运行程序：

gcc ddldemo.c cdpgconnection.c cdpgddl.c -std=c11 -I/usr/include/postgresql -lpq -o ddldemo ./ddldemo

gcc ddldemo.c cdpgconnection.c cdpgddl.c -std=c11 -I/usr/include/postgresql -lpq -o ddldemo ./ddldemo

I won’t bother showing the boring console output but if you open pgAdmin and expand the tree on the left you’ll see the newly created database, complete with these tables, columns and constraints. In the screenshot one of the table nodes is expanded to show the 6 columns and 2 constraints, ie. the primary key and foreign key. If you scroll down a bit you will also see the view.

我不会打扰显示无聊的控制台输出，但是如果您打开pgAdmin并在左侧展开树，您将看到新创建的数据库，其中包括这些表，列和约束。 在屏幕快照中，表节点之一被展开以显示6列和2个约束，即。 主键和外键。 如果向下滚动一点，您还将看到该视图。

下一步是什么？ (What’s Next?)

Having created the database, a few tables and a view we can then go on to insert, update and delete data, and also query the tables and view. This will be the topic of the next article.

创建数据库，几个表和一个视图之后，我们可以继续插入，更新和删除数据，还可以查询表和视图。 这将是下一篇文章的主题。

翻译自: https://medium.com/programming-in-c/creating-a-postgresql-database-in-c-8d4539851c57