fastcgi++
Database

Tutorial

Our goal here will be a FastCGI application that utilizes the SQL facilities of fastcgi++. This example will rely on the MySQL engine of the SQL facilities but can be easily changed to any other. We'll create a simple logging mechanism that stores a few values into a table and then pulls the results out of it.

The process that the SQL facilities use can take some getting used to as it places emphasis on a few key things that most people might not care about. For one, maintaining data types from start to finish is insured. Two, data types are standard data types, not some weird library types (unless you consider the boost::date_time stuff weird). Three, it works with data structures that you control the allocation of. This means the members can be auto-allocated should you want. Four, most importantly, queries can be done asynchronously.

In order to fulfil these requirements we lose a few things. No quick and easy queries based on textual representations of data. Those kinds of features are what we have PHP for. This means that every query has to be prepared and have appropriate data structures built for it's parameters. It was deemed that since a FastCGI script is typically contantly running the same queries over and over, they might as well be prepared.

All code and data is located in the examples directory of the tarball. You'll have to compile with: pkg-config –libs –cflags fastcgi++

Creating the database

The first thing we need to do is create a database and table to work with our script. For simplicity's sake, connect to you MySQL server and run these commands.

CREATE DATABASE fastcgipp;
USE fastcgipp;
CREATE TABLE logs (ipAddress VARBINARY(16) NOT NULL, timeStamp TIMESTAMP NOT NULL,
sessionId BINARY(12) NOT NULL UNIQUE, referral TEXT) CHARACTER SET="utf8";
GRANT ALL PRIVILEGES ON fastcgipp.* TO 'fcgi'@'localhost' IDENTIFIED BY 'databaseExample';

Note that if your not connecting to localhost, then make sure to change the above accordingly.

Error Logging

Our next step will be setting up an error logging system. Although requests can log errors directly to the HTTP server error log, I like to have an error logging system that's separate from the library when testing applications. Let's set up a function that takes a c style string and logs it to a file with a timestamp. Since everyone has access to the /tmp directory, I set it up to send error messages to /tmp/errlog. You can change it if you want to.

#include <fstream>
#include <vector>
#include <boost/date_time/posix_time/posix_time.hpp>
#include <boost/scoped_ptr.hpp>
#include <asql/mysql.hpp>
void error_log(const char* msg)
{
using namespace std;
using namespace boost;
static ofstream error;
if(!error.is_open())
{
error.open("/tmp/errlog", ios_base::out | ios_base::app);
error.imbue(locale(error.getloc(), new posix_time::time_facet()));
}
error << '[' << posix_time::second_clock::local_time() << "] " << msg << endl;
}

Query Data Structure

At this point we need to define the data structures that will contain the results and parameters for our queries. In this example the insert and select queries will both deal with the same set of fields so we only need to create one structure. To make a data structure usable with the SQL facilities it must define two functions internally as explained in ASql::Data::Set. By defining these function we turn the structure into a sort of container in the eyes of the SQL facilities.

struct Log
{

First let's define our actual data elements in the structure. Unless we are fetching/sending a binary structure we must use one of the typedefed types in ASql::Data. We use Fastcgipp::Http::Address and Fastcgipp::Http::SessionId as a fixed width fields matching up with a plain old data structure. It will be stored in the table as raw binary data.

As you can see, one of our values has the ability to contain null values. This capability comes from the ASql::Data::Nullable template class. See also ASql::Data::NullableArray.

Note we are in a wchar_t environment(). and we are accordingly using ASql::Data::WtextN instead of ASql::Data::TextN. Since our SQL table and connection is in utf-8, all data is code converted for you upon reception.

Our reason for using Fastcgipp::Http::SessionId is merely that it provides a good fixed binary data array that happens to have iostream inserter/extractor operators to/from base64. Also the default constructor randomly generates a value.

The following macro provides a method of indexing the data in our structure. It communicates type information, data location, and size to the SQL facilities. For most cases simply returning the object itself will suffice. This is accomplished through the appropriate constructor in ASql::Data::Index. Exceptional cases are when a fixed length char[] is returned as that requires a size parameter and any of the templated constructors as they merely read/write raw binary data with the table based on a field length matching the types size.

(ASql::Data::Index(ipAddress))\
(timestamp)\
(ASql::Data::Index(sessionId))\
(referral))
};

We also need to define a structure for the parameter of our update statement

struct IpAddress: public ASql::Data::Set
{
};

Be sure to read the documentation at ASql::Data::Set to fully understand what just happened.

Request Handler

Now we need to write the code that actually handles the request. This example will be a little bit more complicated than most, but for starters, let's decide on characters sets. Let's go with utf-8 with this one; so pass wchar_t as our template parameter.

class Database: public Fastcgipp::Request<wchar_t>
{

First things first, we are going to statically build our queries so that all requests can access them. Let's make some simple strings for our SQL queries.

static const char insertStatementString[];
static const char updateStatementString[];
static const char selectStatementString[];

Next we'll define our SQL connection object and two SQL statement objects. We'll define them from the ASql::MySQL namespace but they can easily be redefined from another SQL engine and maintain the same interface. This way if you change SQL engines, you needn't change your code much at all. Certainly all data types will remain consistent.

static ASql::MySQL::Connection sqlConnection;
static ASql::MySQL::Statement insertStatement;
static ASql::MySQL::Statement updateStatement;
static ASql::MySQL::Statement selectStatement;

Now we need a status indication variable for the request;

enum Status { START, FETCH } status;

We need a container to use for out Log objects.

typedef std::vector<Log> LogContainer;

Now we declare our response function as usual.

bool response();

The following object handles all data associated with the actual queries. It provides a mechanism for controlling the scope of all data required to execute the SQL statements. Be sure to read the documentation associated with the class.

The constructor sets our status indicator.

Database(): status(START) {}

Here we'll declare a static function to handle initialization of static data.

static void initSql();
};

Now we need to do some basic initialization of our static data. The strings are straightforward. The order of question marks must match exactly to the index order of the data structure they will be read from. In this case Log. For the connection and statement objects we'll call their basic constructors that don't really initialize them.

const char Database::insertStatementString[] = "INSERT INTO logs (ipAddress, timeStamp, sessionId, \
referral) VALUE(?, ?, ?, ?)";
const char Database::updateStatementString[] = "UPDATE logs SET timeStamp=SUBTIME(timeStamp, \
'01:00:00') WHERE ipAddress=?";
const char Database::selectStatementString[] = "SELECT SQL_CALC_FOUND_ROWS ipAddress, timeStamp, \
sessionId, referral FROM logs ORDER BY timeStamp DESC LIMIT 10";

The next line initializes the database connection and decides how many concurrent SQL queries can be operating. Keep in mind that this doesn't dictate how many queries can be queued up, but more how many queues there are. When you queue up a query to be executed it always puts it in the smallest queue.

ASql::MySQL::Connection Database::sqlConnection(4);
ASql::MySQL::Statement Database::insertStatement(Database::sqlConnection);
ASql::MySQL::Statement Database::updateStatement(Database::sqlConnection);
ASql::MySQL::Statement Database::selectStatement(Database::sqlConnection);

Now we'll declare the function to initialize our static data

void Database::initSql()
{

Since we didn't use the full constructor on our connection object, we need to actually connect it. See ASql::MySQL::Connection::connect() for details.

sqlConnection.connect("localhost", "fcgi", "databaseExample", "fastcgipp", 0, 0, 0, "utf8");

Now we initialize our insert and select statements. We pass the init functions any Log object (default constructed will do) wrapped by a SQL::Data::Set object so it can use the derived functions to build itself around it's structure. The ASql::Data::SetBuilder class is used to turn our Log class into a Data::Set derivation. ASql::Data::IndySetBuilder turns any data type into a Data::Set of size 1. We pass a null pointer to the associated parameters/results spot if the statement doesn't actually have any. Obviously an insert will always pass a null pointer to the results set whereas a select would often have both parameters and results. See ASql::MySQL::Statement::init() for details.

const IpAddress addy;
insertStatement.init(insertStatementString, sizeof(insertStatementString), &log, 0);
updateStatement.init(updateStatementString, sizeof(insertStatementString), &addy, 0);
selectStatement.init(selectStatementString, sizeof(selectStatementString), 0, &log);

By calling this function we initialize the thread/threads that will handle SQL queries.

sqlConnection.start();
}

Now for the actual response.

{
using namespace Fastcgipp;
switch(status)
{
case START:
{

Now we set up our ASql::Query object for use with the statement and run it. Again, be sure to read the doc for the class. We set the callback function to the callback object in the Fastcgipp::Request class.

m_query.createResults();
m_query.setCallback(boost::bind(callback(), Fastcgipp::Message(1)));
m_query.enableRows(true);
selectStatement.queue(m_query);

Now we set our status to indicate we are fetching the data from the SQL source. We return false to indicate that the request is not yet complete, just relinquishing the computer.

status=FETCH;
return false;
}

So now we have been called again and our query is complete, let's send'er to the client. The following should be pretty straight forward if you've done the other tutorials.

case FETCH:
{
out << "Content-Type: text/html; charset=utf-8\r\n\r\n\
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>\n\
<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>\n\
<head>\n\
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />\n\
<title>fastcgi++: SQL Database example</title>\n\
</head>\n\
<body>\n\
<h2>Showing " << selectSet->size() << " results out of " << m_query.rows() << "</h2>\n\
<table>\n\
<tr>\n\
<td><b>IP Address</b></td>\n\
<td><b>Timestamp</b></td>\n\
<td><b>Session ID</b></td>\n\
<td><b>Referral</b></td>\n\
</tr>\n";

Regarding the above, selectSet->size() will give us the number of results post LIMIT whereas *rows will tell us the results pre LIMIT due to the use of SQL_CALC_FOUND_ROWS.

Now we'll just iterate through our ASql::Data::SetContainer and show the results in a table.

for(LogContainer::iterator it(selectSet->begin()); it!=selectSet->end(); ++it)
{
out << "\
<tr>\n\
<td>" << it->ipAddress << "</td>\n\
<td>" << it->timestamp << "</td>\n\
<td>" << it->sessionId << "</td>\n\
<td>" << encoding(HTML) << it->referral << encoding(NONE) << "</td>\n\
</tr>\n";
}
out << "\
</table>\n\
<p><a href='database.fcgi'>Refer Me</a></p>\n\
</body>\n\
</html>";

So now one last thing to do in the response is log this request. The beauty of our ASql::Query is that we can queue the SQL insert statement up, return from here and have the request completed and destroyed before the SQL statement is even complete. We needn't worry about sefaulting.

So here we go, let's build a Log structure and insert it into the database. We're also going to make is so that if the referer is empty, we'll make the value is NULL instead of just an empty string. Keep in mind that the default constructor for sessionId was called and randomly generated one.

By calling reset on m_query we basically recycle it for use a second time around. Notice the call to keepAlive to ensure that the query is not cancelled when the query object goes out of scope.

insertQuery.createParameters();
insertQuery.keepAlive(true);
insertQuery.parameters()->data.ipAddress = environment().remoteAddress;
insertQuery.parameters()->data.timestamp = boost::posix_time::second_clock::universal_time();
if(environment().referer.size())
insertQuery.parameters()->data.referral = environment().referer;

Above we built a query object for our insert statement. Now let's build a fun little query for our update statement. This second query will take all timestamps associated with the clients address and subtract one hour from them.

updateQuery.createParameters().data = environment().remoteAddress;
updateQuery.keepAlive(true);

Now that we have a second query, we can put the two together into a transaction.

transaction.push(insertQuery, insertStatement);
transaction.push(updateQuery, updateStatement);
transaction.start();

Now let's get our of here. Return a true and the request is completed and destroyed.

return true;
}
}
return true;
}

Requests Manager

Now we need to make our main() function. Really all one needs to do is create a Fastcgipp::Manager object with the new class we made as a template parameter, then call it's handler. Let's go one step further though and set up a try/catch loop in case we get any exceptions and log them with our error_log function. As an extra this time around we will call Database::initSql() to initialize the static data.

#include <fstream>
#include <vector>
#include <boost/date_time/posix_time/posix_time.hpp>
#include <boost/scoped_ptr.hpp>
#include <asql/mysql.hpp>
void error_log(const char* msg)
{
using namespace std;
using namespace boost;
static ofstream error;
if(!error.is_open())
{
error.open("/tmp/errlog", ios_base::out | ios_base::app);
error.imbue(locale(error.getloc(), new posix_time::time_facet()));
}
error << '[' << posix_time::second_clock::local_time() << "] " << msg << endl;
}
struct Log
{
(ASql::Data::Index(ipAddress))\
(timestamp)\
(ASql::Data::Index(sessionId))\
(referral))
};
struct IpAddress: public ASql::Data::Set
{
};
class Database: public Fastcgipp::Request<wchar_t>
{
static const char insertStatementString[];
static const char updateStatementString[];
static const char selectStatementString[];
static ASql::MySQL::Connection sqlConnection;
static ASql::MySQL::Statement insertStatement;
static ASql::MySQL::Statement updateStatement;
static ASql::MySQL::Statement selectStatement;
enum Status { START, FETCH } status;
typedef std::vector<Log> LogContainer;
bool response();
public:
Database(): status(START) {}
static void initSql();
};
const char Database::insertStatementString[] = "INSERT INTO logs (ipAddress, timeStamp, sessionId, referral) VALUE(?, ?, ?, ?)";
const char Database::updateStatementString[] = "UPDATE logs SET timeStamp=SUBTIME(timeStamp, '01:00:00') WHERE ipAddress=?";
const char Database::selectStatementString[] = "SELECT SQL_CALC_FOUND_ROWS ipAddress, timeStamp, sessionId, referral FROM logs ORDER BY timeStamp DESC LIMIT 10";
ASql::MySQL::Connection Database::sqlConnection(4);
ASql::MySQL::Statement Database::insertStatement(Database::sqlConnection);
ASql::MySQL::Statement Database::updateStatement(Database::sqlConnection);
ASql::MySQL::Statement Database::selectStatement(Database::sqlConnection);
void Database::initSql()
{
sqlConnection.connect("localhost", "fcgi", "databaseExample", "fastcgipp", 0, 0, 0, "utf8");
const IpAddress addy;
insertStatement.init(insertStatementString, sizeof(insertStatementString)-1, &log, 0);
updateStatement.init(updateStatementString, sizeof(updateStatementString)-1, &addy, 0);
selectStatement.init(selectStatementString, sizeof(selectStatementString)-1, 0, &log);
sqlConnection.start();
}
bool Database::response()
{
using namespace Fastcgipp;
switch(status)
{
case START:
{
m_query.createResults();
m_query.setCallback(boost::bind(callback(), Fastcgipp::Message(1)));
m_query.enableRows();
selectStatement.queue(m_query);
status=FETCH;
return false;
}
case FETCH:
{
out << "Content-Type: text/html; charset=utf-8\r\n\r\n\
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Strict//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd'>\n\
<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en' lang='en'>\n\
<head>\n\
<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />\n\
<title>fastcgi++: SQL Database example</title>\n\
</head>\n\
<body>\n\
<h2>Showing " << m_query.results()->data.size() << " results out of " << m_query.rows() << "</h2>\n\
<table>\n\
<tr>\n\
<td><b>IP Address</b></td>\n\
<td><b>Timestamp</b></td>\n\
<td><b>Session ID</b></td>\n\
<td><b>Referral</b></td>\n\
</tr>\n";
for(LogContainer::iterator it(m_query.results()->data.begin()); it!=m_query.results()->data.end(); ++it)
{
out << "\
<tr>\n\
<td>" << it->ipAddress << "</td>\n\
<td>" << it->timestamp << "</td>\n\
<td>" << it->sessionId << "</td>\n\
<td>" << encoding(HTML) << it->referral << encoding(NONE) << "</td>\n\
</tr>\n";
}
out << "\
</table>\n\
<p><a href='database.fcgi'>Refer Me</a></p>\n\
</body>\n\
</html>";
insertQuery.createParameters();
insertQuery.keepAlive(true);
insertQuery.parameters()->data.ipAddress = environment().remoteAddress;
insertQuery.parameters()->data.timestamp = boost::posix_time::second_clock::universal_time();
if(environment().referer.size())
insertQuery.parameters()->data.referral = environment().referer;
updateQuery.createParameters().data = environment().remoteAddress;
updateQuery.keepAlive(true);
transaction.push(insertQuery, insertStatement);
transaction.push(updateQuery, updateStatement);
transaction.start();
return true;
}
}
}
int main()
{
try
{
Database::initSql();
fcgi.handler();
}
catch(std::exception& e)
{
error_log(e.what());
}
}