fastcgi++  3.1alpha
A C++ FastCGI/Web API
SQL

Here we'll be using the PostgreSQL facilities of fastcgi++ to do some simple SQL queries. To maximize throughput and scalability, we'll combine this with the asynchronous nature of fastcgi++'s request handling to ensure request handling threads aren't sitting idle waiting for SQL queries to complete. Since this is C++, we'll carry the strongly typed principles into parameter and result tuples reducing the probability of bugs and exploits. Rigorous error and exception handling will further bring down this "error floor". To summarize, we'll be:

  • Executing asynchronous SQL queries and pausing requests while waiting for results.
  • Communicating query parameters and results as strongly typed tuples to reduce bug and exploit potential.
  • Handling errors and exceptions associated with SQL queries and connections.

As always, the example can be build by:

make sql.fcgi

Make sure, however, that you had called CMake setting the SQL option eg:

cmake -DSQL=true ../fastcgi++

Of course in order for this example to work, the PostgreSQL server needs to be in a state we're expecting. Specifically the necessary username, password, database, and table needs to be setup beforehand. From the source tree, executing

examples/sql-start.sh

will do the necessary initialization under the assumption that the active user will have the capability of creating a new PostgreSQL user, database and table on the default server (PGHOST). Once you're done with the example, call

examples/sql-finish.sh

to clean everything up.

Walkthrough

First off, in order to define an SQL connection, we'll need to include this file.

Next we'll start defining our Request class as per the usual.

#include <random>
#include <iomanip>
class Database: public Fastcgipp::Request<char>
{
private:

Since we're going to be doing some inserts, this next bit of stuff is just for the purpose of randomly selecting a string to be inserted.

static const std::array<std::string, 8> s_strings;
static std::random_device s_device;
static std::uniform_int_distribution<unsigned> s_dist;

Now we're going to declare our SQL connection object. The object will manage the connections to the PostgreSQL server along with it's queries. It is static since it'll be shared between all request objects and often one might declare this in the global space so everything can share it.

static Fastcgipp::SQL::Connection s_connection;

Every SQL query should have an associated Results object that lives in a shared pointer owned by the request object. This thing will contain all status and error information about how the query went and rows can be retrieved from it as std::tuple objects. The template parameters define the types of the tuple returned in row retrieval.

This first Results object we're declaring is for an INSERT statement that will returns nothing. Thus we put no types in the template parameters.

std::shared_ptr<Fastcgipp::SQL::Results<>> m_insertResult;

This next Results object is for a SELECT statement that will return rows. Rows will contain a time stamp, an IP address and a string of sorts. Since this is C++, we wants our columns to be strongly typed, not some hand-wavy string representation. The types of these columns are passed as template parameters.

std::shared_ptr<Fastcgipp::SQL::Results<
std::chrono::time_point<std::chrono::system_clock>,
std::string>> m_selectResults;

We'll breeze forward a bit here now. Since this request will be returning multiple times we'll need to maintain state information, thus the m_state variable. Let's get the response() function started.

unsigned m_state;
public:
Database():
m_state(0)
{}
bool response()
{
switch(m_state)
{
case 0:
{

So here we are at m_state=0 (first call to response()). This first thing we're going to do is insert some data into the database. The results shared pointer for said INSERT query is currently sitting empty so one must always make sure to allocate the results object into the shared pointer before doing a query.

m_insertResult.reset(new Fastcgipp::SQL::Results<>);

Now we need to build the actual Query object for this data insertion. Query objects have four members.

  • The statement member is simply a pointer to a c-style string that contains the SQL statement itself. This must always be set.
  • The parameters member is a shared pointer to a strongly typed tuple-like object that contains parameters for the query. You can make one using the make_Parameters() function or initialize it manually and set individual parameters with std::get(). Make sure the types match up with those of the statement itself. If there are no parameters associated with the query, this member can be left uninitialized.
  • The results member must always be set and we just assign it from the shared pointer we've already set up earlier.
  • The callback member is a std::function object that should be called once the query is complete. We need to set this to our requests callback function if we want our response() function to be called again once the query is completed. If we don't want a callback (maybe we don't care how the query went), we can leave this uninitialized.
query.statement =
"INSERT INTO fastcgipp_example (stamp, address, string) "
"VALUES ($1, $2, $3);";
std::chrono::system_clock::now(),
environment().remoteAddress,
s_strings[s_dist(s_device)]);
query.results = m_insertResult;
query.callback = callback();

So the Query object is built and all we need to do is tell the connection object to queue it up for execution. Calling queue on a connection can return either true or false. If it returns true, the connection object accepted it for execution. If it returns false, the connection object is not yet fully connected with the PostgreSQL server. Make sure to check for and handle this possibility.

if(!s_connection.queue(query))
{
ERROR_LOG("Unable to queue up SQL insert query")
errorHandler();
return true;
}

So we're done with the first call to response(). Our INSERT query is queued up for execution and all this request can do now is wait for completion. Let's advance our state and return false giving up compute time to other requests.

++m_state;
return false;
}
case 1:
{

We're back! The INSERT must be done if we're back in response() so let's ensure our results are what we were expecting them to be.

The first thing we should always check after a query is complete is the result status. Since this query was a non-returning INSERT, our status should be "Command OK".

if(m_insertResult->status() != Fastcgipp::SQL::Status::commandOk)
{
ERROR_LOG("SQL insert gave unexpected status '" \
m_insertResult->status()) \
<< "' with error message '" \
<<m_insertResult->errorMessage() << '\'')
errorHandler();
return true;
}

Now let's verify that the template parameters we supplied for the result row tuples matches the types associated with each column. Since this is a non-returning statement and we supplied zero template parameters, this simply checks to ensure the SQL result had "zero" columns. If everything is good, this function will return 0. If the column count doesn't match, it returns -1. If a specific column type doesn't match, the 1-indexed column number will be returned.

if(m_insertResult->verify() != 0)
{
ERROR_LOG("SQL column verification failed: " << \
m_insertResult->verify())
errorHandler();
return true;
}

Next let's see how many rows were returned with this query. Since this query was non-returning, we'll just make sure the row count is zero.

if(m_insertResult->rows() != 0)
{
ERROR_LOG("SQL insert returned rows when it shouldn't have")
errorHandler();
return true;
}

Lastly we'll check on how many rows were affected by this query. Since we attempted to insert a single row, this should be 1.

if(m_insertResult->affectedRows() != 1)
{
ERROR_LOG("SQL insert should have affected 1 row but " \
"instead affected " \
<< m_insertResult->affectedRows() << '.')
errorHandler();
return true;
}

Thus the INSERT query returned to us exactly as expected so let's move on to the SELECT query. Just as before, we need to initialize the result object associated with the SELECT query and then queue it up. This time, however, we need template parameters associated with the result columns. The query object is built up just as before but we can leave the parameters member uninitialized as there aren't any.

m_selectResults.reset(new Fastcgipp::SQL::Results<
std::chrono::time_point<std::chrono::system_clock>,
std::string>);
query.statement =
"SELECT stamp, address, string FROM fastcgipp_example "
"ORDER BY stamp DESC LIMIT 20;";
query.results = m_selectResults;
query.callback = callback();
if(!s_connection.queue(query))
{
ERROR_LOG("Unable to queue up SQL select query")
errorHandler();
return true;
}

Alright! The query is queued up and, again, all this request has to do is wait for a reply so let's advance our state, return false and let another request use the compute resources. Beyond that, however, we'll also free up the result object associate with the INSERT query since we don't need it anymore.

++m_state;
m_insertResult.reset();
return false;
}
case 2:
{

Back again! This must mean that our SELECT query is done. Just like before, our first priority will be ensuring the validity of what we got back. Since we're expecting rows this time our status should be "Rows OK". Verification, again, must be called before any row retrieval to ensure tuple types match column types. Given the circumstances of what we're doing, we should return at least one row.

if(m_selectResults->status() != Fastcgipp::SQL::Status::rowsOk)
{
ERROR_LOG("SQL select gave unexpected status '" \
m_selectResults->status()) \
<< "' with message '" \
<< m_selectResults->errorMessage() << '\'')
errorHandler();
return true;
}
if(m_selectResults->verify() != 0)
{
ERROR_LOG("SQL column verification failed: " << \
m_selectResults->verify())
errorHandler();
return true;
}
if(m_selectResults->rows() == 0)
{
ERROR_LOG("SQL select didn't return rows when it should "\
"have")
errorHandler();
return true;
}

So the SELECT query went well, now we can start outputting rows.

out <<
"Content-Type: text/html; charset=iso-8859-1\r\n\r\n"
"<!DOCTYPE html>\n"
"<html lang='en'>"
"<head>"
"<meta charset='iso-8859-1' />"
"<title>fastcgi++: Database</title>"
"</head>"
"<body>"
"<table>"
"<thead>"
"<tr>"
"<th>Timestamp</th>"
"<th>IP Address</th>"
"<th>Random String</th>"
"</tr>"
"</thead>";
for(unsigned index=0; index != m_selectResults->rows(); ++index)
{

Here's were we retrieve actual rows. What we get back is an std::tuple with the types provided as the Result template parameters. Since we're only going to read data from the row, let's make it a constant reference to avoid any unnecessary copying of data. This row retrieval method should be quite computationally efficient. We access the columns of the row just as we access elements of an std::tuple.

const auto& row = m_selectResults->row(index);
const auto timestamp = std::chrono::system_clock::to_time_t(
std::get<0>(row));
out <<
"<tr>"
"<td>"
<< std::put_time(
std::localtime(&timestamp),
"%A, %B %e %Y at %H:%M:%S %Z") <<
"</td>"
"<td>" << std::get<1>(row) << "</td>"
"<td>"
<< Encoding::HTML << std::get<2>(row) << Encoding::NONE <<
"</td>"
"</tr>";
}
out <<
"</table>"
"</body>"
"</html>";
return true;
}
}
return true;
}

So we're done defining the response, but we still haven't actually initialized the database connection. We'll accomplish this all in a little function that'll be called from main(). Two member functions are called on the SQL connection object. The first one initializes the internal data itself while the second spins up the handling thread that manages all connections to the PostgreSQL server and dispatches the queries. We'll shoot for allowing 8 concurrent queries with the database. Make sure to check the reference docs for these two functions.

static void start()
{
s_connection.init(
"",
"fastcgipp_example",
"fastcgipp_example",
"fastcgipp_example",
8);
s_connection.start();
}

We also need something to stop the connection. The following little function first signals the connection handler thread to terminate and then waits until said termination is complete.

static void terminate()
{
s_connection.terminate();
s_connection.join();
}
};

Now we'll just define our statics.

const std::array<std::string, 8> Database::s_strings
{
"Leviathan Wakes",
"Caliban's War",
"Abaddon's Gate",
"Cibola Burn",
"Nemesis Games",
"Babylon's Ashes",
"Persepolis Rising",
"Tiamat's Wrath"
};
std::random_device Database::s_device;
std::uniform_int_distribution<unsigned> Database::s_dist(0,7);
Fastcgipp::SQL::Connection Database::s_connection;

And the last little bit. None of this should be of no surprise to you save the calls to start/terminate the database connection.

int main()
{
manager.setupSignals();
manager.listen();
Database::start();
manager.start();
manager.join();
Database::terminate();
return 0;
}

Full Source Code

#include <random>
#include <iomanip>
class Database: public Fastcgipp::Request<char>
{
private:
static const std::array<std::string, 8> s_strings;
static std::random_device s_device;
static std::uniform_int_distribution<unsigned> s_dist;
static Fastcgipp::SQL::Connection s_connection;
std::shared_ptr<Fastcgipp::SQL::Results<>> m_insertResult;
std::shared_ptr<Fastcgipp::SQL::Results<
std::chrono::time_point<std::chrono::system_clock>,
std::string>> m_selectResults;
unsigned m_state;
public:
Database():
m_state(0)
{}
bool response()
{
switch(m_state)
{
case 0:
{
m_insertResult.reset(new Fastcgipp::SQL::Results<>);
query.statement =
"INSERT INTO fastcgipp_example (stamp, address, string) "
"VALUES ($1, $2, $3);";
std::chrono::system_clock::now(),
environment().remoteAddress,
s_strings[s_dist(s_device)]);
query.results = m_insertResult;
query.callback = callback();
if(!s_connection.queue(query))
{
ERROR_LOG("Unable to queue up SQL insert query")
errorHandler();
return true;
}
++m_state;
return false;
}
case 1:
{
if(m_insertResult->status() != Fastcgipp::SQL::Status::commandOk)
{
ERROR_LOG("SQL insert gave unexpected status '" \
m_insertResult->status()) \
<< "' with error message '" \
<<m_insertResult->errorMessage() << '\'')
errorHandler();
return true;
}
if(m_insertResult->verify() != 0)
{
ERROR_LOG("SQL column verification failed: " << \
m_insertResult->verify())
errorHandler();
return true;
}
if(m_insertResult->rows() != 0)
{
ERROR_LOG("SQL insert returned rows when it shouldn't have")
errorHandler();
return true;
}
if(m_insertResult->affectedRows() != 1)
{
ERROR_LOG("SQL insert should have affected 1 row but " \
"instead affected " \
<< m_insertResult->affectedRows() << '.')
errorHandler();
return true;
}
m_selectResults.reset(new Fastcgipp::SQL::Results<
std::chrono::time_point<std::chrono::system_clock>,
Fastcgipp::Address,
std::string>);
Fastcgipp::SQL::Query query;
query.statement =
"SELECT stamp, address, string FROM fastcgipp_example "
"ORDER BY stamp DESC LIMIT 20;";
query.results = m_selectResults;
query.callback = callback();
if(!s_connection.queue(query))
{
ERROR_LOG("Unable to queue up SQL select query")
errorHandler();
return true;
}
++m_state;
m_insertResult.reset();
return false;
}
case 2:
{
if(m_selectResults->status() != Fastcgipp::SQL::Status::rowsOk)
{
ERROR_LOG("SQL select gave unexpected status '" \
m_selectResults->status()) \
<< "' with message '" \
<< m_selectResults->errorMessage() << '\'')
errorHandler();
return true;
}
if(m_selectResults->verify() != 0)
{
ERROR_LOG("SQL column verification failed: " << \
m_selectResults->verify())
errorHandler();
return true;
}
if(m_selectResults->rows() == 0)
{
ERROR_LOG("SQL select didn't return rows when it should "\
"have")
errorHandler();
return true;
}
out <<
"Content-Type: text/html; charset=iso-8859-1\r\n\r\n"
"<!DOCTYPE html>\n"
"<html lang='en'>"
"<head>"
"<meta charset='iso-8859-1' />"
"<title>fastcgi++: Database</title>"
"</head>"
"<body>"
"<table>"
"<thead>"
"<tr>"
"<th>Timestamp</th>"
"<th>IP Address</th>"
"<th>Random String</th>"
"</tr>"
"</thead>";
for(unsigned index=0; index != m_selectResults->rows(); ++index)
{
const auto& row = m_selectResults->row(index);
const auto timestamp = std::chrono::system_clock::to_time_t(
std::get<0>(row));
out <<
"<tr>"
"<td>"
<< std::put_time(
std::localtime(&timestamp),
"%A, %B %e %Y at %H:%M:%S %Z") <<
"</td>"
"<td>" << std::get<1>(row) << "</td>"
"<td>"
<< Encoding::HTML << std::get<2>(row) << Encoding::NONE <<
"</td>"
"</tr>";
}
out <<
"</table>"
"</body>"
"</html>";
return true;
}
}
return true;
}
static void start()
{
s_connection.init(
"",
"fastcgipp_example",
"fastcgipp_example",
"fastcgipp_example",
8);
s_connection.start();
}
static void terminate()
{
s_connection.terminate();
s_connection.join();
}
};
const std::array<std::string, 8> Database::s_strings
{
"Leviathan Wakes",
"Caliban's War",
"Abaddon's Gate",
"Cibola Burn",
"Nemesis Games",
"Babylon's Ashes",
"Persepolis Rising",
"Tiamat's Wrath"
};
std::random_device Database::s_device;
std::uniform_int_distribution<unsigned> Database::s_dist(0,7);
Fastcgipp::SQL::Connection Database::s_connection;
int main()
{
manager.setupSignals();
manager.listen();
Database::start();
manager.start();
manager.join();
Database::terminate();
return 0;
}
Fastcgipp::SQL::Status::commandOk
@ commandOk
Fastcgipp::Manager_base::setupSignals
static void setupSignals()
Configure the handlers for POSIX signals.
Definition: manager.cpp:100
Fastcgipp::Manager_base::start
void start()
Call from any thread to start the Manager.
Definition: manager.cpp:76
manager.hpp
Declares the Manager class.
Fastcgipp::SQL::Query
Structure to hold SQL query data.
Definition: connection.hpp:85
Fastcgipp::Manager
General task and protocol management class.
Definition: manager.hpp:321
Fastcgipp::SQL::Query::results
std::shared_ptr< Results_base > results
Results
Definition: connection.hpp:111
Fastcgipp::SQL::Connection::queue
bool queue(const Query &query)
Queue up a query.
Definition: connection.cpp:275
Fastcgipp::Request::m_state
Protocol::RecordType m_state
What the request is current doing.
Definition: request.hpp:383
Fastcgipp::Request::environment
const Http::Environment< charT > & environment() const
Const accessor for the HTTP environment data.
Definition: request.hpp:170
Fastcgipp::SQL::make_Parameters
std::shared_ptr< Parameters< Types... > > make_Parameters(const Types &... args)
Definition: parameters.hpp:482
Fastcgipp::SQL::Results<>
Specialization of Results for zero sized result sets.
Definition: results.hpp:277
connection.hpp
Declares the Fastcgipp::SQL::Connection class.
Fastcgipp::SQL::Query::parameters
std::shared_ptr< Parameters_base > parameters
Parameters
Definition: connection.hpp:108
Fastcgipp::Request::callback
const std::function< void(Message)> & callback() const
Callback function for dealings outside the fastcgi++ library.
Definition: request.hpp:231
Fastcgipp::Request::response
virtual bool response()=0
Response generator.
Fastcgipp::SQL::Query::callback
std::function< void(Message)> callback
Callback function to call when query is complete.
Definition: connection.hpp:114
Fastcgipp::Encoding
Encoding
Stream manipulator for setting output encoding.
Definition: webstreambuf.hpp:198
ERROR_LOG
#define ERROR_LOG(data)
Log any "errors" that can be recovered from.
Definition: log.hpp:107
Fastcgipp::Request
Request handling class
Definition: request.hpp:117
Fastcgipp::SQL::Status::rowsOk
@ rowsOk
Fastcgipp
Topmost namespace for the fastcgi++ library.
Definition: fcgistreambuf.cpp:34
Fastcgipp::SQL::Connection::join
void join()
Block until a stop() or terminate() is completed
Definition: connection.cpp:230
Fastcgipp::SQL::Connection::init
void init(const char *host, const char *db, const char *username, const char *password, const unsigned concurrency=1, const unsigned short port=5432, int messageType=5432, unsigned retryInterval=30)
Initialize the connection.
Definition: connection.cpp:249
log.hpp
Declares the Fastcgipp debugging/logging facilities.
Fastcgipp::Manager_base::join
void join()
Block until a stop() or terminate() is called and completed.
Definition: manager.cpp:91
Fastcgipp::SQL::statusString
const char * statusString(const Status status)
Returns a text description of the specified SQL query result status.
Definition: results.cpp:497
Fastcgipp::Address
Efficiently stores IPv6 addresses.
Definition: address.hpp:67
Fastcgipp::SQL::Connection
Handles low level communication with "the other side".
Definition: connection.hpp:113
Fastcgipp::SQL::Connection::terminate
void terminate()
Call from any thread to terminate the handler() thread.
Definition: connection.cpp:213
Fastcgipp::Manager_base::listen
bool listen()
Listen to the default Fastcgi socket.
Definition: manager.hpp:159
Fastcgipp::SQL::Connection::start
void start()
Call from any thread to start the handler() thread.
Definition: connection.cpp:219
Fastcgipp::SQL::Results
Holds SQL query result sets.
Definition: results.hpp:181
Fastcgipp::SQL::Query::statement
const char * statement
Statement.
Definition: connection.hpp:105
request.hpp
Declares the Request class.