A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://github.com/dmitigr/pgfe/ below:

GitHub - dmitigr/pgfe: PostgreSQL C++ driver

dmitigr::pgfe (PostGres FrontEnd) - is an advanced, feature rich and cross-platform PostgreSQL driver written in C++. The development is focused on easines and robustness of use with the performance in mind.

#include <dmitigr/pgfe/pgfe.hpp>

#include <cstdio>

namespace pgfe = dmitigr::pgfe;

int main() try {
  // Making the connection.
  pgfe::Connection conn{pgfe::Connection_options{}
    .set(pgfe::Communication_mode::net)
    .set_hostname("localhost")
    .set_database("pgfe_test")
    .set_username("pgfe_test")
    .set_password("pgfe_test")};

  // Connecting.
  conn.connect();

  // Using Pgfe's helpers.
  using pgfe::a;  // for named arguments
  using pgfe::to; // for data conversions

  // Executing statement with positional parameters.
  conn.execute([](auto&& r)
  {
    std::printf("Number %i\n", to<int>(r.data()));
  }, "select generate_series($1::int, $2::int)", 1, 3);

  // Execute statement with named parameters.
  conn.execute([](auto&& r)
  {
    std::printf("Range [%i, %i]\n", to<int>(r["b"]), to<int>(r["e"]));
  },"select :begin b, :end e", a{"end", 1}, a{"begin", 0});

  // Prepare and execute the statement.
  auto ps = conn.prepare("select $1::int i");
  for (int i{}; i < 3; ++i)
    ps.execute([](auto&& r){std::printf("%i\n", to<int>(r["i"]));}, i);

  // Invoking the function.
  conn.invoke([](auto&& r)
  {
    std::printf("cos(%f) = %f\n", .5f, to<float>(r.data()));
  }, "cos", .5f);

  // Provoking the syntax error.
  conn.execute("provoke syntax error");
 } catch (const pgfe::Server_exception& e) {
  assert(e.error().condition() == pgfe::Server_errc::c42_syntax_error);
  std::printf("Error %s is handled as expected.\n", e.error().sqlstate());
 } catch (const std::exception& e) {
  std::printf("Oops: %s\n", e.what());
  return 1;
 }
Quick usage as header-only library

Copy the contents of the src directory to a project directory which is under an include path of a compiler, for example, src/3rdparty/dmitigr.

Create hello.cpp:

#include "dmitigr/pgfe/pgfe.hpp"

int main()
{
  dmitigr::pgfe::Connection conn;
}

Compile hello.cpp:

g++ -std=c++17 -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lpq -ohello hello.cpp

Create build directory, configure, build and install (note, if libpq is in a non-standard location, then the path to it can be specified by using -DPq_ROOT as shown below):

cd pgfe
mkdir build && cd build
cmake -DPq_ROOT=/usr/local/pgsql .. # -DPq_ROOT is optional
cmake --build .
sudo cmake --install .

Create hello/hello.cpp:

#include "pgfe/pgfe.hpp"

int main()
{
  dmitigr::pgfe::Connection conn;
}

Create hello/CMakeLists.txt:

cmake_minimum_required(VERSION 3.16)
project(foo)
find_package(dmitigr_libs REQUIRED COMPONENTS pgfe)
set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)
add_executable(hello hello.cpp)
target_link_libraries(hello dmitigr_pgfe)

Compile hello/hello.cpp:

mkdir hello/build && cd hello/build
cmake ..
cmake --build .

Logically, Pgfe library consists of the following parts:

The API is defined in the namespace dmitigr::pgfe. In this tutorial all the names are not explicitly qualified by this namespace.

By using class Connection_options it's easy to specify the required connection options:

// Example 1. Making connection options.
auto make_options()
{
  return Connection_options{}
    .set(Communication_mode::net)
    .set_hostname("localhost")
    .set_database("db")
    .set_username("user")
    .set_password("password");
}

By using class Connection it's easy to connect to the PostgreSQL server:

// Example 2. Making ready-to-use connection.
auto make_connection(const Connection_options& opts = {})
{
  Connection conn{opts};
  conn.connect(); // connect synchronously (in blocking manner)
  return conn; // return the ready-to-use instance
}

Only extended query protocol is used under the hood of Pgfe. SQL commands can be executed and processed either synchronously or in non-blocking IO maner, i.e. without need of waiting for server response(-s). In the latter case the methods of the class Connection with the suffix _nio shall be used.

With Pgfe it's easy to execute single commands:

// Example 3. Executing single commands.
void foo(Connection& conn)
{
  conn.execute("begin");
  conn.execute("create temp table num(val integer not null)");
  conn.execute([](auto&& row)
  {
    using dmitigr::pgfe::to;    // see "Data conversions" section for details
    auto val = to<int>(row[0]); // converts the value of num.val to int
    std::cout << val << "\n";   // prints the just inserted integers
  }, "insert into num select generate_series(1,3) returning val");
  conn.execute("rollback");
}

Extended query protocol used by Pgfe is based on prepared statements. In Pgfe prepared statements can be parameterized with both positional and named parameters. The class Statement provides functionality for constructing SQL statements, providing support for named parameters, as well as functionality for direct parameters replacement with any SQL statement to generate complex SQL expressions dynamically.

In most cases unnamed statements prepared implicitly:

// Example 4. Preparing unnamed statements (`BEGIN`, `SELECT`, `ROLLBACK`) implicitly.
void foo(Connection& conn)
{
  conn.execute("begin");
  conn.execute([](auto&& row)
  {
    using dmitigr::pgfe::to;            // see "Data conversions" section for details
    auto val = to<std::string>(row[0]); // converts the retrieved value to std::string
    std::cout << val << "\n";           // prints "Hi!\n"
  }, "select 'Hi!'");
  conn.execute("rollback");
}

It's also easy to use named parameters:

// Example 5. Using named parameters in statements.
void foo(Connection& conn)
{
  // Please note, the sequence of the specified named parameters doesn't matter,
  // and that "end" parameter is specified before "begin" parameter.
  using dmitigr::pgfe::a;
  conn.execute([](auto&& row)
  {
    std::printf("Range [%i, %i]\n", to<int>(row["b"]), to<int>(row["e"]));
  },"select :begin b, :end e", a{"end", 1}, a{"begin", 0});
}

Of course, the statement (named or unnamed) can be prepared explicitly:

// Example 6. Preparing the unnamed statement parameterized by named parameters.
void foo(Connection& conn)
{
  conn.prepare("select generate_series(:inf::int, :sup::int) num")
    .bind("inf", 1)
    .bind("sup", 3)
    .execute([](auto&& row)
    {
      // Printing the just generated integers without type conversion
      std::printf("%s\n", row["num"].bytes());
    });
}
Invoking functions and calling procedures

Pgfe provides the convenient API for functions invoking or procedures calling: methods Connection::invoke(), Connection::invoke_unexpanded() and Connection::call() accordingly.

To illustrate the API the following function definition is used:

create function person_info(id integer, name text, age integer)
returns text language sql as
$$
  select format('id=%s name=%s age=%s', id, name, age);
$$;

Calling "person_info" by using positional notation:

// Example 7. Using positional notation.
void foo(Connection& conn)
{
  conn.invoke("person_info", 1, "Dmitry", 36);
  // ...
}

Calling "person_info" by using named notation:

// Example 8. Using named notation.
void foo(Connection& conn)
{
  using dmitigr::pgfe::a;
  conn.invoke("person_info", a{"name", "Dmitry"}, a{"age", 36}, a{"id", 1});
  // ...
}

Calling "person_info" by using mixed notation:

// Example 9. Using mixed notation.
void foo(Connection& conn)
{
  using dmitigr::pgfe::a;
  conn.invoke("person_info", 1, a{"age", 36}, a{"name", "Dmitry"});
  // ...
}

By default, Pgfe provides the support of the data conversions for fundamental and standard C++ types only. Conversions for special PostgreSQL types such as Date/Time Types aren't provided out of the box, since many implementations of these types are possible at the client side. Instead it's up to the application developers to decide what implementation to use. (If such conversions are needed at all.) For example, the template structure Conversions can be easily specialized to convert the data between PostgreSQL Date/Time Types and types from the Boost.Date_Time library.

The abstract class Data is designed to provide the interface for:

The template structure Conversions is used by:

Pgfe provides the partial specialization of the template structure Conversions to convert from/to PostgreSQL arrays (including multidimensional arrays!) representation to any combination of the STL containers out of the box! (At the moment, arrays conversions are only implemented for Data_format::text format.) In general, any PostgreSQL array can be represented as Container<Optional<T>>, where:

In case when all the elements of the array are not NULL, it can be represented as the container with elements of type T rather than Optional<T>. But in case when the source array (which comes from the PostgreSQL server) contain at least one NULL element a Client_exception will be thrown. Summarizing:

User-defined data conversions could be implemented by either:

Server responses can be retrieved:

To initiate retrieving the first response in non-blocking IO manner methods of the class Connection with the suffix _nio must be used. Otherwise, Pgfe will wait for the first response and if that response is error, an instance of type Server_exception will be thrown. This object provides access to the object of type Error, which contains all the error details.

Server responses are represented by the classes inherited from Response:

// Example 10. Catching the syntax error.
void foo(Connection& conn)
{
  try {
    conn.execute("provoke syntax error");
  } catch (const Server_exception& e) {
    assert(e.error().condition() == Server_errc::c42_syntax_error);
  }
}
// Example 11. Processing the rows.
void foo(Connection& conn)
{
  conn.execute([](auto&& row)
  {
    using dmitigr::pgfe::to;
    auto name = to<std::string>(row["name"]);
    std::printf("%s\n", name.data());
  }, "select name from usr where id = $1", 3); // where id = 3
}
// Example 12. Working with named prepared statement.
void foo(Connection& conn)
{
  // Prepare the named statement
  auto int_gen = conn.prepare("select generate_series($1::int, $2::int)", "int_gen");

  // Defining the row processor
  auto process = [](auto&& row)
  {
    using dmitigr::pgfe::to;
    auto n = to<int>(row[0]);
    std::printf("%i\n", n);
  };

  // Execute for the first time
  int_gen.bind(1).bind(2).execute(process);
  // Execute for the second time
  int_gen.bind(10).bind(20).execute(process);
}
// Example 13. Using completion info.
void foo(Connection& conn)
{
  auto completion = conn.execute("begin");
  std::printf("%s\n", completion.operation_name()); // prints "BEGIN"
}

Server signals are represented by classes Notice and Notification, inherited from class Signal. Signals can be handled by using the signal handlers (see Connection::set_notice_handler() and Connection::set_notification_handler()). Notifications can also be handled in non-blocking IO maner, by using the method Connection::pop_notification().

Signal handlers, being set, called by Pgfe automatically when signals are retrieved. (Usually it happens upon waiting a response.) If no notification handler is set, notifications will be queued to the internal storage until popped up by method Connection::pop_notification(). Be aware, that if notification are not popped up from the internal storage it may cause memory exhaustion!

The standard classes like std::string or std::ostringstream can be used to make SQL strings dynamically. However, in some cases it is more convenient to use the class Statement for this purpose. Consider the following statement:

select :expr::int, ':expr';

This SQL string has one named parameter expr and one string constant ':expr'. It's possible to replace the named parameters of the SQL string with another SQL string by using Statement::replace_parameter(), for example:

// Example 14. Extending the SQL statement.
void foo()
{
  Statement sql{"select :expr::int, ':expr'"};
  sql.replace_parameter("expr", "sin(:expr1::int), cos(:expr2::int)");
}

Now the original statement is modified and has two named parameters:

select sin(:expr1::int), cos(:expr2::int), ':expr'

Note, that the quoted string :expr is not affected by the replacement operation.

Working with SQL code separately of C++ code

This feature is based on the idea to store the SQL code in a separate place, such as a text file. Consider the following SQL input, which is consists of two SQL strings with an extra data specified by the dollar-quoted string constants in the related comments:

-- This is query 1
--
-- $id$plus-one$id$
select :n::int + 1, ';'; -- note, the semicolons in quotes are allowed!

/* This is query 2
 *
 * $id$minus-one$id$
 */
select :n::int - 1

These SQL strings can be easily accessed by using class Statement_vector:

// Example 15. Parsing file with SQL statements.

std::string read_file(const std::filesystem::path& path); // defined somewhere

void foo()
{
  auto input = read_file("bunch.sql");
  Statement_vector bunch{input};
  auto minus_pos = bunch.statement_index("id", "minus-one"); // select :n::int - 1
  auto plus_pos = bunch.statement_index("id",  "plus-one"); // select :n::int + 1, ';'
  // ...
}

Pgfe provides a simple connection pool implemented as class Connection_pool:

// Example 16. Using the connection pool.

Connection_options connection_options(); // defined somewhere.

int main()
{
  Connection_pool pool{2, connection_options()};
  pool.connect(); // open 2 connections
  {
    auto conn1 = pool.connection(); // 1st attempt to get the connection from pool
    assert(conn1);  // ok
    conn1.execute("select 1");
    auto conn2 = pool.connection(); // 2nd attempt to get the connection from pool
    assert(conn2);  // ok
    conn2.execute("select 2");
    auto conn3 = pool.connection(); // 3rd attempt to get the connection from pool
    assert(!conn3); // the pool is exhausted
  } // connections are returned back to the pool here
  auto conn = pool.connection();
  assert(conn); // ok
  pool.disconnect(); // done with the pool
}

Pgfe provides a convenient RAII-style facility for owning a transaction (or subtransaction) for the duration of a scoped block. When control leaves the scope in which the Transaction_guard object was created, the Transaction_guard is destructed and the transaction is rolled back. If the rollback failed, the connection is closed, to prevent further interaction with the database, since failed rollback might indicate a total mess.

// Example 17. Using the transaction guard.

void foo(Connection& conn)
{
  assert(conn.is_connected() && !conn.is_transaction_uncommitted());
  try {
    Transaction_guard tg{conn}; // begin transaction
    {
      Transaction_guard tg{conn}; // begin subtransaction (define savepoint 1)
      {
        Transaction_guard tg{conn}; // begin subtransaction (define savepoint 2)
        tg.commit(); // release savepoint 2
      }
      tg.commit(); // release savepoint 1
    }
    tg.commit_and_chain(); // commit transaction and immediately begin the next one
    {
      Transaction_guard tg{conn}; // begin subtransaction (define savepoint 1)
      throw 1; // oops, attempt to rollback the entire transaction
    }
  } catch (...) {
    assert(!conn.is_connected() || !conn.is_transaction_uncommitted());
  }
}

Pgfe itself may throw:

By default, if not explicitly documented, all functions and methods of Pgfe are not thread safe. Thus, in most cases, some of the synchronization mechanisms (like mutexes) must be used to work with the same object from several threads.

Pgfe is depends on the libpq library.

Please note:

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4