The Bone Yard

PostgreSQL Progression

How I use libpqxx to help publish blog posts

One of the TO DO items I had after completing my blog platform was creating a mechanism through which I could publish posts. Like everything else, there are many many ways to slice this loaf of bread, including a server-based tool, a remote-accessible page, accepting e-mail and converting the text to an entry, and on and on. For the first few posts, I have been utilizing PostgreSQL's built-in file import facility, COPY FROM. Coming from a primarily SQL Server experience base (I've used lots of other DBMS systems, but SQL Server most recently), one finds plenty of pleasant surprises in the Postgres feature base, and this is surely one of them. "Copy from" is a handy tool that permits reading input from a flat file format within a psql session and inserting the data into DB tables. While this is helpful, what I needed was a bit more robust than sucking in text file lines as insertions into 1 specific table. Perhaps "copy from" has the ability to consume relational-formatted data sets and splitting them into different tables (while respecting identity=>FK requirements between the data sets), but I didn't devote much time exploring this path. Instead, as an immediate solution, I hacked together a terminal app in C++ that solicits input from the console, sanity checks it, and pushes it to Postgres.

To achieve this, I investigated libpqxx, which appears to be the canonical PostgreSQL client cpp binding library. As was the case about virtually everything else in this project, this was my initial foray into libpqxx. For the most part, I think this is a wonderful tool that plays nicely into the strengths of C++. The class hierarchy is nicely constructed to form useful abstractions (e.g. connection_base) that are then customized into concrete classes of varying purpose, allowing one to write generalized code that doesn't care if you are connecting sync vs async, immediately vs lazy, etc. The DB transaction/"unit of work" construct also nicely leverages RAII to rollback changes at scope's end, either with your blessing or if exceptional behavior is produced.

// Prepare your stmts
this->conn_.get()->prepare
(
  "insert_entry",
  "insert into blog.entry                                           \\
   (entry_id, entry_title,entry_subtitle,entry_datetime,entry_text) \\
   values(DEFAULT, $1, $2, $3, $4) RETURNING entry_id;"
);

this->conn_.get()->prepare
(
  "insert_tags",
  "insert into blog.entry_tags (entry_id, tag_id) \\
   values ($1, $2)"
 );

try
{
  pqxx::work w(*this->conn_.get());
  pqxx::result r =
     w.prepared("insert_entry")
     (entry.title_)
     (entry.subtitle_)
     (entry.entry_date_)
     (entry.entry_text_).exec();

  // Retrieve the entry PK for insertion into tags detail table 
  int entryId = 0;
  r[0][0].to(entryId);

  for (auto i = entry.tagList_.begin(); i != entry.tagList_.end(); ++i)
  {
     w.prepared("insert_tags")(entryId)(*i).exec();
  }

  w.commit();

  return true;
}
catch (const std::exception & e)
{
  // Whoops, handle the error. Our UoW was already aborted on destruction.
  std::cout << "Exception caught while completing publish transaction: '"
            << std::endl << e.what()
            << "'" << std::endl;
  return false;
}

The concept I find a bit unusual is that there doesn't appear to be a statement level abstraction. Most if not all other DB client bindings I've used seems to have separate objects for connections, statements (DML), and results. I could be missing this in the doc, but most of the examples I saw pointed to a two level process of setting up the connection, and preparing+executing the statement directly within the connection. Don't think this is necessarily better or worse, just different than what I've seen in other toolkits (think ADO).

Also of note, check out how the PK from the main table is pulled back in conjunction with insertion so you can turn around and use it as the FK in your detail tables. The RETURNING syntax from PSQL is nice and tidy; a nice shorthand for insertion followed by immediate select. I didn't notice anyplace where libpqxx documented how this was done, but my assumption that the framework will simply do the right thing and generate the RETURNING result set from the insertion DML proved accurate.

This exercise reinforced my sense that PostgreSQL has language capability measuring up to other DB platforms, and also, it's got terrific support scaffolding and libraries around it.