PostgreSQL/libpqxx Class Generator

• Download pgpqgen program and pqxxTransfer.h - 156 Kb
• Download source - 73.1 Kb

Introduction

I was a bit conflicted about where to put this code. I didn't know whether to put it into the Free Tools with Source section or here. I decided on here because, well, I already have an article here, and it's starting to feel like home.

This program generates C++ header files containing class definitions that make it easy to access fields as members of an object instead of as the result of function calls. Think of it as a CRecordset wizard applied to PostgreSQL via libpqxx.

I've also included a class template that the generated classes are derived from.

The program is written using WTL as the windowing framework, and demonstrates one possible way of implementing a wizard using WTL. It includes an implementation of the singleton design pattern for global data, use of the ATL::CRegKey class, use of the pqxx classes, and a reasonably generic use of SHBrowseForFolder.

There is a test program that uses a transfer class generated by pgpqgen, and it demonstrates one possible method of using the transfer classes, database connection pooling, WTL DDX, and a resizable dialog box using the CDialogResize class template.

Background

This is a program that grew out of efforts to write a database application for the mass valuation of all of the properties in a district or districts in New South Wales (a state in Australia, for you foreigners :)). I had originally written the program for a local company, using MySQL, but time has passed, and I want to make a more generic product that is a bit more up-to-date and uses SQL views and transactions. Yes, I know MySQL transactions can be implemented by using the BDB engine and views are coming, but I don't want to argue about it. I'm writing the product, and we're going with PostgreSQL, dammit :)

Just by the way, I do have a bunch of classes that lightly wrap the MySQL C library quite nicely, but it looks like there are already enough wrappers on this site, and I don't want to add to the confusion. If you are interested enough to download them, get them from here, but there is no article. They're part of a small application that does a "REPAIR TABLE" on each table in a given database, but you're on your own. Don't tell me if you can't make it work.

So I started writing, and found that there is quite a nice wrapper for PostgreSQL that is implemented in C++ and looks a lot like STL with containers and iterators, etc. So, following on from that, I thought it might be nice to have a field mapping facility so that I could just assign a result iterator to an object and then access the object's members. This would save having to invoke methods that require a field index or field name in order to use the row's values.

I implemented this as a template, and found that it worked pretty well. The only problem was that writing all of these different classes that were so similar to each other was a pain. So, in true engineer fashion, I dropped what I was building in order to build something that would build it better and faster. Ahem. In the original version of this article, I said that, at a rough guess, I would require something like another 1000 or table definitions to recoup the time spent on creating this tool, but that that wasn't the point. At the time of this update, I wouldn't be surprised to learn that the time I spent on the project has more than made up for the time that it's saved me.

Since that point, the program and the pqxxTransfer template has grown significantly.

Preparation and dependencies

Before you build this project from source or use the generated files, you must have PostgreSQL and libpqxx installed, built, and running on your computer. You can get them both starting from here. I'm using version 8.0.4 and 8.0.7 of PostgreSQL and version 2.4.4 of libpqxx.

Please, please, please ensure that you can build and run the tests provided with the libpqxx code. If you can't do that, forget about the rest of this article. The article assumes the following:

• the pqxx directory is in your include path. In the project provided with this article, it is ..\libpqxx-2.4.4\include. You may need to change it to suit your own installation and/or environment.
• the directory containing win32.h and libpq-fe.h is in your include path. In the pgpqgen project, this is $(PSQLSRC)\src\interfaces\libpq. You may need to change it to suit your own installation and/or environment. PSQLSRC is an environment variable that on my computer is set to C:\Documents and Settings\phil\My Documents\Projects\libs\postgresql-8.0.1.
• the libpq main include directory is in your include path. In the included pgpqgen project, this is $(PSQLSRC)\src\include. You may need to change it to suit your own installation and/or environment.
• libpqxx.lib is accessible to your linker. This is the output of the libpqxx project that I've included in the attached solution, and both of the other projects depend on this one. It is a VS7.1 project, so you may have to tinker a little if you have an earlier version. In my development tree, I have a libpqxx-2.4.4 directory sitting at the same level as my libpqxx, pgpqgen, and pgpqtest directories. I'd recommend that you do something similar for the purposes of this article.
• libpq.lib or libpqdll.lib is accessible to your linker. In the pgpqgen project, these live in ..\..\libs\Debug or ..\..\libs\Release. You may need to change it to suit your own installation and/or environment. If you're looking for them in the PostgreSQL source distribution, they will be in $(PSQLSRC)\src\interfaces\libpq\Release or $(PSQLSRC)\src\interfaces\libpq\Debug.

You will know that you have succeeded in the preparation outlined above if you can build and run the pgpqgen application.

Note 1: Both of the included projects #define _LIB. They do this so that the libpqxx code used by the projects link with libpqxx the way I want them to (statically).

Note 2: This is not a beginner level article. I have not included any of the PostgreSQL or LibPQXX code in the downloads above — these are your responsibility to find. If you ask me why the compiler fails with a message like:

"fatal error C1083: Cannot open include file: 
        'pqxx/pqxx': No such file or directory"

or the linker fails with a message like:

"pgpqgen.obj : error LNK2019: unresolved external symbol "public: void __thiscall 
        pqxx::connection_base::disconnect(void)" 
        (?disconnect@connection_base@pqxx@@QAEXXZ) referenced in function "private: 
        __thiscall GlobalData::~GlobalData(void)" (??1GlobalData@@AAE@XZ)"

I reserve the right to ignore you. Unless you want to pay me for configuring your development environment ...

Using the pgpqgen tool

Step 1: Connecting to the database

Enter all of the information you require to connect to the database. The first time through, the program assumes that you are going to use the public schema and port 5432. These are the PostgreSQL defaults. The rest you will have to enter. When you click the Next button, the program will store the values you entered in the registry (HKCU\Software\Pagaros Pty Limited\pgpqgen) so that you don't have to keep re-entering them. The program doesn't store the password. If you want, there is a section in PGWizConnect.h that looks like this:

#ifdef _DEBUG
    m_Password = "your development password";
#endif

You can put the development password directly into the program if you are going to be running through this process a lot.

OK, having done this, click the Next button to connect to the database.

Step 2: Specify your preferences

Here, you specify the directory into which the generated files should be put. The little ellipsis button at the end of the Target edit box is something that originated with Ilya Solnyshkin's CWtlEditBrowserCtl. I've added theme support to it, and clicking the button opens the Directory Browser shell dialog. For those of you who don't know about how to specify the starting directory, the PGWizTarget.h file contains code to do this. You're looking for the getFolderPath and BrowseCallbackProc functions.

The first checkbox lets you specify whether or not the class names should have a "C" prefix.

The next checkbox has to do with whether or not the program should generate a base class for the record set. The base class is just the data from the table, but organised into the struct. There is a constructor that ensures that all of the members are initialised to correct values, and an assignment operator. If you don't choose this, all of the data will be stored in the class derived from the CPqxxTransfer template.

The final checkbox lets you decide whether or not to drop the trailing "S" from the tables when making the base class name. In our example, we're using the "articles" table. So if this is checked, the class names generated will be CArticle and CArticlesRS.

Step 3: Choosing tables and views

For the sake of the demonstration, I've chosen to have the code generated only for the articles table. By default, all tables are checked. Click the "Untick All" button to remove the ticks from all tables.

Click on the name of a table to see the table's generated class name, file name, and columns. You can update the class and file names so that they fit your naming conventions. I've made it so that it uses a "C" prefix if you choose, it capitalizes the first letter of the table name, and then tacks on an "RS" indicating it's a result set. The file name is the same as the table name, followed by "rs.h". Note that PostgreSQL makes the table names lower case. At least, it did in my case, and I haven't looked into why.

If you use underscores in your table names, you might want to think about updating the class name generation code to strip underscores and capitalise the letter following the underscore. It's on line 303 of PGWizTables.h.

Once you're satisfied with the class and file names, you can choose which columns you want to have represented in your class as members. Tick or un-tick as appropriate. When you click on the name of a column, its PostgreSQL type (PG Type), proposed result set name (RS Name), and proposed result set type (RS Type) are displayed. You can change the result set values. The program makes reasonable guesses, but beware of the following:

1. dates, times, and timestamps all default to COleDateTime.
2. all floats (including float4) and numerics are going to be doubles.
3. all ints and oids (including int8) are going to be ints.
4. bool columns will be bools.
5. everything else is going to be a std::string.

If you want to change this behavior, find the ColumnInfo::guessMemberType method in TableInfo.h and fiddle with that. Oh yeah, in pqxx's util.cxx file, the libpqxx code is able to get a char or varchar field that contains a 'T' or 'F' and interpret it as a bool. If you have columns like that (such as archived above), then you can change the RS Type from std::string to bool.

In the example above, I've selected the id field. You can specify a particular field as an Index field. This will be used by the CPqxxTransfer template code to handle retrieval of individual records, and saving and deletion of individual records. In theory, you can specify more than one field as part of a key, but I've not had the need to test this, so I didn't.

Click Next when you are satisfied.

Step 4: Generating the class files

There is not a lot to do here. Once you get to this page, you will either have the files, or not. The main reason for not being able to generate the files at this point is not having the directory that is specified on the Preferences page, or not having access to that directory (or not having access to overwrite the files, etc.).

Here's what articlesrs.h looks like:

// $Id: article.htm 2082 2006-08-18 02:30:51Z phil $
// articlesrs.h: Declaration of the CArticle and CArticlesRS classes

// Generated by pgpqgen 17/08/2006 4:02:29 PM

#pragma once

#include "pqxxTransfer.h"

namespace Database
{
    class CArticle
    {
    public:
        CArticle()
            : archived(false)
            , id(NULLINT)
            , numwords(NULLINT)
            , weight(NULLDBL)
        {
            submitted.SetStatus(COleDateTime::invalid);
        }

        bool operator==(const CArticle& other)
        {
            return archived == other.archived
                && author == other.author
                && content == other.content
                && id == other.id
                && numwords == other.numwords
                && price == other.price
                && submitted == other.submitted
                && title == other.title
                && weight == other.weight;
        }

    public:
        bool archived;
        std::string author;
        std::string content;
        int id;
        int numwords;
        COleCurrency price;
        COleDateTime submitted;
        std::string title;
        double weight;

    };

    class CArticlesRS
        : public CPqxxTransfer<CArticlesRS>
        , public CArticle
    {
        typedef CPqxxTransfer<CArticlesRS> baseClass;

    public:
        CArticlesRS() : baseClass("articles") { initColDescs(); }
        CArticlesRS(const pqxx::result::const_iterator& src) : 
             baseClass("articles") { initColDescs(); xferValues(src); }

        static const std::string defaultQuery()
        {
            return "SELECT "
                "archived"
                ", author"
                ", content"
                ", id"
                ", numwords"
                ", price"
                ", submitted"
                ", title"
                ", weight"
                " FROM public.articles";
        }

        CArticlesRS& operator=(const CArticle& other)
        {
            initColDescs();

            archived = other.archived;
            author = other.author;
            content = other.content;
            numwords = other.numwords;
            price = other.price;
            submitted = other.submitted;
            title = other.title;
            weight = other.weight;

            return *this;
        }

        BEGIN_DB_MAP(CArticlesRS, 9)
            DB_BOOL_ENTRY(0, archived)
            DB_STRING_ENTRY(1, author)
            DB_STRING_ENTRY(2, content)
            DB_INT_INDEX(3, id)
            DB_INT_ENTRY(4, numwords)
            DB_CURR_ENTRY(5, price)
            DB_DATE_ENTRY(6, submitted)
            DB_STRING_ENTRY(7, title)
            DB_DOUBLE_ENTRY(8, weight)
        END_DB_MAP()
    };
}

By way of explanation, here's what the file is made up of.

1. File header stuff including the inclusion of "pqxxTransfer.h". See the next section.
2. Specify that the new class is in the Database namespace.
3. The start of class declaration of the base class.
4. The base class' default constructor. The generator initializes all ints to NULLINT, all bools to false, and all doubles to NULLDBL. std::strings, COleDateTimes, and COleCurrencys aren't mentioned in the initialiser list because they have their own default constructors. COleDateTimes are set to invalid, which corresponds to NULL.
5. The base class' comparison assignment operator. You can put these objects into some containers (such as vectors and as the value in maps), but if you want to use them in containers that require ordering, you will have to include your own "lessthan" method.
6. The base class' members. Yes, they are all public. I'm lazy.
7. The start of class declaration of the CPqxxTransfer template-derived class.
8. The transfer class' default constructor. This sets the table name, and initialises the column definitions.
9. A constructor that accepts a pqxx::result::const_iterator as an argument. This sets the table name, the column definitions, and then transfers the data in from the iterator.
10. A defaultQuery method. This method returns the query that is used to retrieve all of the records for this transfer class.
11. An assignment operator. This allows you to assign the contents of a base class object into this object. If you add any members to this class, you should alter this assignment operator to take this into account.
12. Finally, the database column-to-member map. Again, this is described in the next section.

Caveat

This program is guaranteed to overwrite the header files if they exist. If you have made changes to them, tough. You'll have to make them again. I'd suggest that you keep a special "generate" directory somewhere and merge the changes into an existing project. The program will remember the location to which you last generated your files.

pqxxTransfer.h and Database::CPqxxTransfer

Looking at the generated file above, you may have noticed that it includes a file called pqxxTransfer.h. What's that about? Well, it contains a class template called Database::CPqxxTransfer. Note that it lives inside of a namespace called Database. All of the generated classes get put in this namespace as well. If you'd rather not have the namespace used, then:

1. change the program so that it doesn't use it; or
2. if enough people ask, I'll put a checkbox on the Preferences page.

Classes that are derived from classes that instantiate this template are called "transfer classes". I chose this name because Recordset and Resultset had already been taken. Objects made from these classes are called "transfer objects".

Stepping through this file, we have the following chunks.

Inclusions

Well, not really inclusions. It's just that this file doesn't work unless there are a couple of files already included. These are <atlcomtime.h>, and everyone should have that to get COleDateTime. The other is <atlcurr.h>, and that's the one that I wrote to make up for the missing COleCurrency class in ATL/WTL. At least, I couldn't find one, so I wrote this one. You'll find <atlcurr.h> included in the demo project, and unless you have something else there, I'd suggest putting it in your wtl75 directory. Bother ... while writing this, I find CComCurrency, which should do much the same thing. Ah well ...

Some definitions

#define NULLINT 0x80000000
#define NULLDBL ((double)0xFFFFFFFFFFFFFFFF)

#if defined(USING_DATAMANAGER)
class dmwork;
#else
typedef pqxx::work dmwork;
#endif

These definitions are the null values for the int and double types. These are used in the reset methods described below. New mapped member types may require new definitions here.

The dmwork class is my own class that is derived from pqxx::basic_transaction. If you choose not to use the DataManager, this reverts to being an ordinary pqxx::work-style transaction.

Safe value methods

These methods are all inside the pqxx namespace. The safe value methods' job is to render values as safe for inclusion in SQL statements. There are methods for:

• std::string
• int
• double
• COleDateTime
• bool
• COleCurrency

There is an ugly little bit of code in the double method. Originally, I allocated a buffer of 20 bytes to accept the value of the double. Yup, sure enough, doubles can be way longer than that. So now, I calculate how big the buffer has to be before attempting to put the number in.

Reset methods

void reset(std::string& s) { s = ""; }
void reset(int& i) { i = NULLINT; }
void reset(double& f) { f = NULLDBL; }
void reset(COleDateTime& dt) { dt.m_dt = 0; 
           dt.m_status = COleDateTime::invalid; }
void reset(bool& b) { b = false; }
void reset(COleCurrency& cy) { cy.m_cur.int64 = 0; 
              cy.m_status = COleCurrency::invalid; }

These reset methods are used to reset the value of each data member before new values are copied in from the pqxx::result::const_iterator.

std::strings are set to empty strings, bools are set to false, and COleDateTimes have the m_dt member set to zero and the status set to COleDateTime::invalid. Remember the #defines from above? These are used to reset the values for ints and doubles.

If you find that the values I've given for NULLINT and NULLDBL are actually values that you are likely to use, I'd suggest that you change them.

If you need to introduce new mapped data types (such as __int64 or some such thing), then you will need to add another signature here.

COleDateTime and COleCurrency extraction methods

template<> inline void from_string(const char str[], COleDateTime &obj)
{
    std::string s = str;
    size_t dotPos = s.find('.');
    if (dotPos != std::string::npos)
    {
        s = s.substr(0, dotPos);
    }
    COleDateTime result;
    result.ParseDateTime(s.c_str());
    obj = result;
}
template<> inline void from_string(const char str[], COleCurrency &obj)
{
    COleCurrency result;
    result.ParseCurrency(str);
    obj = result;
}

This code implements a couple of from_string methods that the libpqxx library doesn't have. See util.cxx for a collection of the other ones that have been implemented. If you are interested in the different types of strings that can be converted into bool values, have a look at a third of the way down this file.

Required enum and struct

The BEGIN_DB_MAP/END_DB_MAP macros allow you to define the relationship between the values in your tuple and the data members in your class. In order to make this relationship workable, there are a few little bits and pieces that need to be put in order. The EPQXXCOLTYPE enum contains a list of the types that are available, and the PQXXCOLDESC is a column description that includes information about the field number, the field name, a pointer to some data, the type of the column, and whether or not the columns are part of an index.

CPqxxTransfer class template

The class template is the reason for this article. To start with, it has a constructor that requires a table name.

Accessor methods

void where(const std::string& where) { _where = where; }
const std::string& where() const { return _where; }
void whereIDEq(int idVal)
{
    std::string s("id=");
    where(s.append(pqxx::safe_value(idVal)));
}
void order(const std::string& order) { _order = order; }
const std::string& order() const { return _order; }
const std::string& table() const { return _table; }

The methods above are for setting and getting the WHERE and ORDER BY clause for the query used to get the data from the database. Note that you should not include the words "WHERE" and "ORDER BY". The whereIDEq method is a shorthand for generating a "WHERE id=99"-style WHERE clause.

Query generating method

This method is used to retrieve the query from the object. If you want all records from the default query and you don't care what order they come in, you should create your transfer object (call it tObj) and then pass tObj.query() to your transaction object.

This method builds the query out of the array of column descriptors, and tacks on the WHERE and ORDER BY clauses that may already have been specified. I don't include any HAVING or GROUP BY clauses because the transfer classes are for single tables or views, and if you're going to have views, you can include these clauses there. If you desperately want them, you can cheat by appending them to the ORDER BY clause.

Last inserted ID

int getInsertedID(dmwork& trx)
{
    std::string query("select currval('");
    query.append(table()).append("_id_seq')");
    int ret = NULLINT;
    pqxx::result res = trx.exec(query);
    pqxx::result::const_iterator rit = res.begin();
    if (rit != res.end())
    {
        rit->at(0).to(ret);
    }
    return ret;
}

Everyone wants to know the ID of the last record they inserted, and this method gives it to them. But it's protected! How does that work? When you tell an object to save and it's inserted (because the index is NULL), this method is called internally. So after you've saved, the new ID is available in the object.

Pure virtual methods

virtual size_t columnCount() const = 0;
virtual const PQXXCOLDESC* colDescs() const = 0;
virtual void initColDescs() = 0;

These methods count the number of columns, return a pointer to the first column description, and initialises the column descriptions (as if you couldn't figure that out). The thing is that these methods are implemented in the derived transfer class through the use of the BEGIN_DB_MAP/END_DB_MAP macros.

Data transfer methods

I'm not going to reproduce these here, but I'll tell you what they do.

xferValues picks information out of the source pqxx::result::const_iterator, and transfers the values from the tuple into the internal data members.

update builds and executes an UPDATE query, storing the information that is in the internal data members into the database. It uses the index values to build the WHERE clause.

insert builds and executes an INSERT query. All index fields are set to their NULL value, and the inserted ID is picked out of the database and put into the index field. Note that for this operation, the infrastructure assumes a single sequence value.

save is a public method, and it figures out whether or not the tuple should be inserted or updated. This is based on the values of the index values. If all index values are NULL, then the tuple will be inserted. Otherwise, it gets updated.

del is a public method, and it deletes a record. It uses the index values from the tuple to build the WHERE clause for the DELETE statement.

load is a public method, and it loads a tuple, given a single value for an "id" field.

BEGIN_DB_MAP macro

There are a set of macros that help you build the data transfer map for your table or query. Although they are explained here, you should be aware that using the pgpqgen tool means that you don't have to worry about these yourself.

#define BEGIN_DB_MAP(T, colCount) \
public: \
    T& operator=(const pqxx::result::const_iterator& src) \
    { \
        xferValues(src); \
        return *this; \
    } \
protected: \
    size_t columnCount() const { return colCount; } \
    const PQXXCOLDESC* colDescs() const { return _columnDescriptions; } \
private: \
    PQXXCOLDESC _columnDescriptions[colCount+1]; \
protected: \
    void initColDescs() \
    { \
        PQXXCOLDESC* pDesc;

This macro implements an assignment operator in the transfer class that allows the caller to assign a pqxx::result::const_iterator to a transfer object.

It contains the implementation for the two of the pure virtual methods mentioned above (columnCount, colDescs) and the start of the third (initColDescs).

DB_COLUMN_ENTRY macro

#define DB_COLUMN_ENTRY(fieldPos, member, epct, idx) \
    pDesc = _columnDescriptions + fieldPos; \
    pDesc->fieldNo = fieldPos; \
    pDesc->fieldName = #member; \
    pDesc->dataAddress = &member; \
    pDesc->colType = epct; \
    pDesc->isIndex = idx;

For each column that you are retrieving from the database, you specify the index number from the column list in the query, the name of the member to which you want to copy the value, the member's type, and whether or not it is an index field.

DB_xx_ENTRY and DB_xx_INDEX macros

#define DB_xx_ENTRY(fieldPos, member) 
        DB_COLUMN_ENTRY(fieldPos, member, EPCT_xx, false)
#define DB_xx_INDEX(fieldPos, member) 
        DB_COLUMN_ENTRY(fieldPos, member, EPCT_xx, true)

There is one of these macros for each of the types that we deal with. So xx is really going to be one of INT, STRING, BOOL, DOUBLE, DATA, or CURR.

END_DB_MAP macro

#define END_DB_MAP() \
    }

Real simple one, this. It just closes the initColDescs method.

Writing code that uses transfer classes

I've written a demo program that works with a table that it creates in a test database. It's included in the download, and I'd really recommend that you have a good look at it. It includes code to handle database versioning, connection pooling, and creating, updating, and deleting records using the transfer classes.

Important classes

DataManager (DataManager.h, DataManager.cpp)

This class manages connections to the database. It is a singleton class, and uses synchronisation classes to manage a connection pool. This is all very much behind the scenes, and you only use this class in constructing a transaction object.

The interesting stuff in this class is in the constructor (at least I think it is). There is a hard-coded connection string there, and to get this particular demo working, you would need to create a user on your PostgreSQL server with a username of "test" and a password of "testpwd". Then, you need to create a database called "test" which is owned by the "test" user. You will also have to update your connection string unless your server is running on localhost.

The DataManager is not only responsible for managing transactions and connections to the database, it also manages database versioning. It does the following:

1. Checks to see whether or not there is a "sysparams" table.
2. If there is no sysparams table, it creates it and initialises the database version value.
3. If there is a sysparams table, it retrieves the database version value.
4. It then checks the version number, and if it is less than "0.0.1.1", it creates an articles table and updates the version number to "0.0.1.1".
5. Following this, there is a commented out bit of code that will check and update to version "0.0.1.2". You can use this as a template for your next modification to the database. I've found this particularly useful when distributing updated versions of the program.

In safe environments (for an extremely high value of safe), you can distribute files that contain SQL statements, and step through them using the processSQLFile method instead of hard-coding all of your statements into the source code. You would make the file names something like "db-1.0.1.1.sql" and "db-1.0.1.2.sql", etc. This code might look like this:

std::string upgradeFile;
for (vn = 0x0001000000010001; vn <= CURRENTDBVERSION; vn++)
{
    Utils::versionToStr(vn, upgradeFile);
    upgradeFile = "db-" + upgradeFile + ".sql";
    if (versionNumber < vn)
    {
        processSQLFile(TRX, upgradeFile);
        versionNumber = vn;
    }
}

CArticle and CArticlesRS (articlesrs.h)

These classes are those that were generated by the pgpqgen tool. "Hang on a second!", I hear someone shout. "If the test program creates the database and the test program won't compile without articlesrs.h, you cheated! How could you have generated the transfer class without the database?" Ah yes, observant one. It was a bootstrapping process. I wrote the code to create the database and the table before I used pgpqgen to generate the code that gave me access to the table.

As this file is displayed further up the article, I will assume that you've already looked at it and the points that follow it describing what it's doing.

Singleton (Singleton.h), SyncObject (SyncObject.h), and SyncList (SyncColl.h)

These classes are part of the library that I use in my projects, and they live in the Pagaros namespace.

The Singleton class template is something that is used to implement the Singleton design pattern. See the DataManager class to see what you need to have it implemented as a Singleton. I forget where I got that one from.

The SyncObject class is something that I use as a convenient wrapper for mutexes and events. There are undoubtedly better implementations around, but this works for me.

The SyncList class template is derived from std::list and SyncObject, so it's a list to which you can serialise access. It's used in the DataManager to store database connections.

CArticleEditDlg (ArticleEditDlg.h, ArticleEditDlg.cpp)

This dialog class is only interesting because it has WTL DDX (CWinDataExchange) and automatic resizing (CDialogResize). If you haven't done this before, or you're interested in how it compares with MFC, look for the BEGIN_DDX_MAP and BEGIN_DLGRESIZE_MAP macros, respectively. I can certainly recommend Michael Dunn's WTL for MFC Programmers, Part IV - Dialogs and Controls and Using WTL's Built-in Dialog Resizing Class articles.

It also has a static variable that remembers where the dialog was placed and sized when it last closed, so you don't have to keep on resizing and moving the window whenever you open it. If you want, this information could be written to the registry so that the window position and size could be persistent across successive program instances as well.

CPgpqtestView (pgpqtestView.h, pgpqtestView.cpp)

Finally, this is where we talk about using the transfer class. The view is a CListViewCtrl, so the first thing I do is insert the columns. Then, back in the frame, after all the creation of the windows is complete, the view's Refresh method is called.

Refresh

Here's the code for the Refresh method, and we'll go through it step by step.

void CPgpqtestView::Refresh()
{
 1:    DeleteAllItems();
 2:
 3:    Database::CArticlesRS ars;
 4:    ars.order("author");
 5:    dmwork TRX(DataManagerS::instance().dbConn(), "TRX002");
 6:    pqxx::result dbr = TRX.exec(ars.query());
 7:    pqxx::result::const_iterator rsit;
 8:    int item = 0;
 9:    for (rsit = dbr.begin(); rsit != dbr.end(); rsit++)
10:    {
11:        ars = rsit;
12:        item = InsertItem(item, pqxx::safe_value(ars.id).c_str());
13:        SetItemText(item, 1, ars.author.c_str());
14:        SetItemText(item, 2, ars.title.c_str());
15:        SetItemText(item, 3, ars.submitted.Format("%d-%b-%Y"));
16:        SetItemText(item, 4, ars.price.Format("%.2f"));
17:        SetItemData(item, ars.id);
18:    }
}

• Line 1: Clear out the items that are already in the list.
• Line 3: Declare a CArticlesRS instance. This will initialise all the members to their NULL values.
• Line 4: State that we want the data to be retrieved in the author's name order.
• Line 5: Open a transaction, specifying the connection and a name.
• Line 6: Retrieve the results and put them into a result set.
• Line 7: Declare an iterator for the results.
• Line 8: Declare and initialise a counter for inserting items into the list.
• Line 9: Run the iterator through the result set.
• Line 11: Load the data from the iterator into the transfer object.
• Line 12: Insert the new item's ID into the list.
• Lines 13-16: Set the text of the various sub-items from the transfer object.
• Line 17: Set the item's data to be the transfer object's ID.

There are a few points to note here:

• I name the transactions because when you're dealing with a multithreaded application, or there are many transactions open simultaneously, it's helpful to see the name of the transaction if one screws up. I have a file with the next transaction name included in my larger projects.
• Using the transfer object for specifying the order, selection criteria, and ultimately the query, is kind of clumsy. There should be a query object specific to each table or view, but I haven't got around to doing that yet.
• Did you notice the use of the Database instance? That's about the only way it gets used.

Delete

Deletion is pretty easy. You pick the article ID out of the list, load the record to check it's still there, and then delete it. Here's the code:

try
{
    int articleId = GetItemData(item);
    Database::CArticlesRS ars;
    dmwork TRX(DataManagerS::instance().dbConn(), "TRX005");
    ars.load(TRX, articleId);
    ars.del(TRX);
    TRX.commit();
    Refresh();
}
catch (pqxx::sql_error& xsql)
{
    MessageBox(xsql.what(), "Delete Item", MB_ICONSTOP);
}

It would be OK to use ars.id = articleId instead of ars.load(TRX, articleId) if you don't really care whether or not the record was really there, since the del method only looks at the index fields.

You could also use this code to load the tuple:

pqxx::result dbr;
pqxx::result::const_iterator rsit;
ars.where("id=" + pqxx::safe_value(articleId));
dbr = TRX.exec(ars.query());
rsit = dbr.begin();
if (rsit != dbr.end())
{
    ars = rsit;
}

... but this is somewhat more clumsy than ars.load(TRX, articleId).

Edit

Editing a record consists of picking the article ID out of the list, reading the article from the database, loading it into the dialog box, and opening the dialog box. If the dialog box is closed because the user clicked the OK button, then we just reverse the process, putting the article information back into the transfer object. Then tell the transfer object to save itself. Here is the database work:

// all the stuff that loads and then updates the contents of ars ...
try
{
    dmwork TRX(DataManagerS::instance().dbConn(), "TRX004");
    ars.save(TRX);
    TRX.commit();
    Refresh();
}
catch (pqxx::sql_error& xsql)
{
    MessageBox(xsql.what(), "Edit Item", MB_ICONSTOP);
}

I'd like to point out that the Edit method has a pair of transactions involved. The first is used to load the article, the second is used to save the article. It would have been possible to use a single transaction to encompass both activities, but I'm naturally averse to having transactions open while waiting for user input.

The other point to make here is that if you want to save your changes to the database, you need to commit the transaction. If you don't need to save changes, a commit is unnecessary since the transaction closes itself when the transaction object goes out of scope. Having said that, if you want to run multiple transactions within the same scope, you must close a previous transaction before opening a new one. This means calling TRX.commit() even if you haven't made any updates.

New

New is pretty much a cut-down version of Edit. The dialog is left empty to start with (apart from setting the ID to "New"), and when the user clicks OK, the only data member of the transfer object that is not set is the id field. When a transfer object's index fields are all set to their NULL values, the transfer object's save() method does an INSERT instead of an UPDATE.

Again, the transaction has to be committed.

In Summary

I have made a couple of projects available: one is a program that generates transfer class definitions based on the structure of tables and views in a database, the other is a very simple program that uses one such transfer class.

My normal process for using this tool in my everyday work (yes, I do use it regularly) is:

1. Update my DataManager object so that it makes the appropriate structural changes to the database and updates the database version.
2. Run the new version of the program to update the database.
3. Run pgpqgen to generate the new or updated transfer class and store it a "generated" directory somewhere.
4. If the generated class is an updated class, I use Araxis Merge to compare the new with the original and merge the changes. Some of my transfer classes have extra methods, and some are derived from intermediate classes or class templates.

History

• January 4, 2005: Initial release.
• August 19, 2006: Included database updates, base transfer classes, and the test project.