Develop PostgreSQL using C / C
Zuo Jin (Zuo170@163.com) August 2003
This article describes the use of C / C combined with the libpq and libpq library to develop Linux to develop Linux to develop a named free database PostgreSQL, which describes how to use the development library to operate the data steps and processes.
PostgreSQL Database Introduction
1 Introduction
PostgreSQL is a relational database running on a UNIX and Linux operating system (in the NT platform can also run by using Cygnus) platforms. The earliest is developed by the University of California, Berkeley, started to be published as a demo system, but over time, gradually distributing, and has gradually popped up. The current version has developed to 7.3.3.
honor:
In 1999, he won the title "Best Database Product" of Linux World magazine. 2000 won the "Best Database" award for Linux Journal Magazine editing. In 2003, it was also a "Best Database" award for Linux Journal magazine editing again.
2. Main functions and features
Supports SQL. As a relational database, it supports SQL89 standards and supports most of the SQL92. Have a rich data type. Many data types are not available in some commercial databases. Object-oriented. It contains some object-oriented techniques such as inheritance and classes. Support big databases, which are different from a general desktop database, which can support almost unrestricted sizes, and stable performance. Convenient integrated web, some interfaces are convenient for PHP, Perl and other languages. Transaction processing. With some other free databases such as MySQL, he provides transaction processing to meet data needs in some business sectors.
3. Data type
PostgreSQL supports rich data types, many data types are unable to be available in the business database.
Numeric type. Includes integer, any precision, floating point type, and sequence type value. Currency type. Range-21474836.48 to 21474836.47. Character type. Divided into fixed length types, unproductively restricted types and unordered long-length types. Binary string. Date and time type Boolean type. Geometric type. Includes points, lines, squares, paths, polygons, and circles. Network address type. Bright string type. It is a string including 1 and 0. Object identifier type array type.
4. Database limit
Parameter Name Maximum Range Database Size Unlimited Table Size 16TB (All Systems) A Record Size 1.6GB Field Size 1GB Table Record Number No Restriction Table Field Number 250-1600 (depending on the data type of the field) Table index unlimited
In fact, there is no limit to the situation. A table that supports 16TB sizes is not a single file saved, but is divided into multiple files, so it is not limited to the operating system.
4. Download and install
We can download it at http://www.postgresql.org website, how to install me, don't explain it, you can watch the documentation, you can also see the documentation of the Chinese website in the reference. Here I will talk about several issues that need to pay attention to in installation.
note
You cannot log in to Linux in root to operate the database (except for database services), create a Postgres user as root, set the password, log in. Installed for PostgreSQL, you may need to initialize the database, you need to log in to the system with the Postgres user, run initdb -pqlib = / usr / lib / pqsql -pqdata = / var / lib / pqsal If your PQLIB directory is different, you need to modify according to your actual path This is also like this. Database users and Linux users are different concepts. Only database users can operate the database, log in to Linux with postgres, then add users, Dropuser to delete users, or use SQL command CREATE User to create. 5. Common command
PostgreSQL Database User Operation Command:
CreateDB Creating Database CreateUser Creating Database User Dropdb Deleting Database DROPUSER Deleting Database User PSQL Interactive PostgreSQL Front Tools, you can use it to operate database initdb initialization PostgreSQL database
It is not explained how to use the PostgreSQL database here, so you can see the documentation description and help for these commands. I am here to assume that the reader has installed and initializes the database and uses PSQL and skilled SQL to interact data.
PostgreSQL's C / C interface introduction
PostgreSQL offers a number of different languages, C, C , Perl, TCL, etc., which mainly introduces how C / C is connected to the database, add data to the data, query.
1.c language interface
The header file libqp-fe.h is connected to add connection parameters - LPQ is also connected to the libpq library. Create a database connection function
PgConn * pqconnectdb (const char * connInfo)
PgConn * pqsetdblogin (const char * pghost,
Const char * pgport,
Const char * pgoptions,
Const char * pgtty,
Const char * dbname,
Const char * login,
Const char * PWD)
Establish a non-blocking connection function
PgConn * pqconnectstart (const char * connInfo)
PostgrespollingStatustyPE * PQConnectPoll (PQCONN * CONN)
Execute a query function
PgResult * pqexec (pgconn * conn, const char * query) Returns the structure set of queries.
INT PQNTUPLES (const pgResult * res) Returns the number of records in the query result.
INT PQNFIELDS (const pgResult * res) Returns the number of fields in the record.
Char * pqfname (const pgResult * res, int field_index) Returns the name of a field in the structure.
Oid PqfType (const pgResult * res, int field_index) returns the data type of a field in the structure set.
Char * pqgetvalue (const pgResult * res, int tup_num, int field_index) Returns the value of a field in a certain field in a record in the structure.
There are only some of the most commonly used functions, and the details can be found in the documentation. Large object data operations mainly use the following functions to operate large object data in the SQL statement.
Oid LO_CREAT (PGConn * Conn, int MODE) Creates a new big object. Mode is a bit mask, describing the different attributes of the new object Oid Lo_IMPORT (PGCONN * CONN, Conn, Conn, Conn, Conn, Conn, Import files into large objects into database int Lo_export (Pgconn * conname) large object import Open an existing large object INT LO_RITE (PGCONN * CONN, OID LOBJID, INT MODE) INT LO_READ (PGCONN * CONN, INT FD, CHAR * BUF, SIZE_T LEN) From large object read data int Lo_lseek (pgconn * conn, " INT FD) Close the big object descriptor function defines in the fe-lobj.c file. For details, please refer to the specific introduction of the above functions. Please refer to the postgreSQL V7.0 document. Here, you can omit the state inspection function, see the reference document. Accessing the database using the C language In addition to the libpq library, there are other interfaces such as libpqeasy, ECPG.
2.c language interface
Header file libpq . H connection required list PQ , connection parameters are -LPQ libpq class inheritance chart profile
PgConnection member function PgConnection (const char * conninfo) parameters are the same under Connect (const char * conninfo) command and check whether state PGRES_COMMAND_OK ExecTuplesOk (const char with PQexec ExecCommandOk (const char * query) run with PQconnectdb Exec (const char * query) * Query) Run the query and check the status pgres_tuples_ok intertring (int N) digital conversion to string connectionbad () connection failed error message DBNAME () with PQDB
See the documentation
PgDatabase common member function PgDatabase (const char * conninfo) constructor is connected to a database, with Pqconnectdb same parameters Tuples are () with PQntuples (); CmdTuples () with PQcmdTuples Fields () with PQnfields FieldName (int field_num) with PQfname FieldNum (const char * field_name) with PQfnumber FieldType (int ield_num) FieldType (const char * field_name) with PQftype FieldSize (int field_num) FieldSize (const char * field_name) with PQfsize GetValue (int tup_num, int field_num) GetValue (int tup_num, const char * field_name) with PQgetvalue GetIsNull (int tup_num, int field_num) GetIsNull (int tup_num, const char * field_name) with PQgetisnull GetLength (int tup_num, int field_num) GetLength (int tup_num, const char * field_name) with PQgetlength GetLine (char * string, INT length) Use the PQPUTLINE ENDCOPY () with PQPUTLINE ENDCOPY () to use the PGLARGEOBJECT class to operate with PQEENDCOPY.
Example program
Log in to Linux with a POSTGRES user or a user with a database user privilege, first establish a database.
[zuojin @ itpark85 zuojin] $ createdb test_db
Then use PSQL to connect to the database
[zuojin @ itpark85 zuojin] $ PSQL Test_db
Connect to the database establishment table, as shown below
Insert the test data, as shown in the figure:
(Note: Enter the SQL statement in PSQL to execute with ";", the help command is "//?", Exiting the command is "// q").
1) C language example
#include
#include
Int main () {
PGCONN * CONN;
PgResult * res;
Char * pghost = NULL;
Char * pgport = NULL;
Char * pgoptions = NULL;
Char * pgtty = null;
Char * dbname = "test_db";
/**data storage name*/
INT i = 0, T = 0, S, K;
Conn = pqsetdb (pghost, pgport, pgoptions, pgtty, dbname);
IF (pqstatus (conn) == connection_bad) {
FPRINTF (stderr, "connection to database '% s' failed! /// n", dbname);
Pqfinish (conn);
ETURN 0;
}
Res = PQEXEC (CONN, "Select * from test");
/ ** Run the query command * /
IF (pqResultStatus (res)! = pgres_tuples_ok) {
FPRINTF (stderr, "exec query Fauled! // n"); PQCLEAR (RES);
Return 0;
}
i = pqntuples (res);
/ ** Number of records of the results of the query * /
T = pqnfields (res);
/ ** Number of fields * /
For (s = 0; s FOR (k = 0; k Printf ("% s", pqgetvalue (res, s, k)); Printf (""); } Printf ("// n"); } Pqfinish (conn); PQCLEAR (RES); Return 0; } The result is shown in the figure 2) C example #include #include Int main () { Char query_string [256] = "SELECT * from test;"; PGDATABASE DATA ("DBNAME = Test_DB"); IF (Data.ConnectionBAD ()) { Cout << "Connected Failed" << Endl; COUT << "Error IS" << Data.ErrorMessage () << endl; Exit (1); } IF (! Data.exectupLesok (query_string)) { Cout << "Query Failed!" << Endl; Exit (1); } For (int K = 0; k Cout << Data.fieldName (k); Cout << "; } Cout << Endl; For (int i = 0; i For (int K = 0; k Cout << Data.GetValue (i, k); Cout << "|"; } Cout << Endl; } Return 0; } The result is shown in the figure Sample code of this article: TestC.c, TestC . CPP. reference PostgreSQL website and Chinese website PostgreSQL document Red Hat Database About author Zuo Jin, in the Nansha Information Technology Park, love Linux, Java and blue sky and white clouds green mountain green water. You can contact him through Zuo170@163.com.
