Persistence classes in Qt

Question

Hi, I'm porting a medium-sized CRUD application from .Net to Qt and I'm looking for a pattern for creating persistence classes. In .Net I usually created abstract persistence class with basic methods (insert, update, delete, select) for example:

public class DAOBase<T>
{
    public T GetByPrimaryKey(object primaryKey) {...}

    public void DeleteByPrimaryKey(object primaryKey) {...}

    public List<T> GetByField(string fieldName, object value) {...}

    public void Insert(T dto) {...}

    public void Update(T dto) {...}
}

Then, I subclassed it for specific tables/DTOs and added attributes for DB table layout:

[DBTable("note", "note_id", NpgsqlTypes.NpgsqlDbType.Integer)]
[DbField("note_id", NpgsqlTypes.NpgsqlDbType.Integer, "NoteId")]
[DbField("client_id", NpgsqlTypes.NpgsqlDbType.Integer, "ClientId")]
[DbField("title", NpgsqlTypes.NpgsqlDbType.Text, "Title", "")]
[DbField("body", NpgsqlTypes.NpgsqlDbType.Text, "Body", "")]
[DbField("date_added", NpgsqlTypes.NpgsqlDbType.Date, "DateAdded")]
class NoteDAO : DAOBase<NoteDTO>
{
}

Thanks to .Net reflection system I was able to achieve heavy code reuse and easy creation of new ORMs.

The simplest way to do this kind of stuff in Qt seems to be using model classes from QtSql module. Unfortunately, in my case they provide too abstract an interface. I need at least transactions support and control over individual commits which QSqlTableModel doesn't provide.

Could you give me some hints about solving this problem using Qt or point me to some reference materials?

---

Update:

Based on Harald's clues I've implemented a solution that is quite similar to the .Net classes above. Now I have two classes.

UniversalDAO that inherits QObject and deals with QObject DTOs using metatype system:

class UniversalDAO : public QObject
{
    Q_OBJECT

public:
    UniversalDAO(QSqlDatabase dataBase, QObject *parent = 0);
    virtual ~UniversalDAO();

    void insert(const QObject &dto);
    void update(const QObject &dto);
    void remove(const QObject &dto);
    void getByPrimaryKey(QObject &dto, const QVariant &key);
};

And a generic SpecializedDAO that casts data obtained from UniversalDAO to appropriate type:

template<class DTO>
class SpecializedDAO
{
public:
    SpecializedDAO(UniversalDAO *universalDao)
    virtual ~SpecializedDAO() {}

    DTO defaultDto() const { return DTO; }

    void insert(DTO dto) { dao->insert(dto); }
    void update(DTO dto) { dao->update(dto); }
    void remove(DTO dto) { dao->remove(dto); }
    DTO getByPrimaryKey(const QVariant &key);
};

Using the above, I declare the concrete DAO class as following:

class ClientDAO : public QObject, public SpecializedDAO<ClientDTO>
{
    Q_OBJECT

public:
    ClientDAO(UniversalDAO *dao, QObject *parent = 0) :
        QObject(parent), SpecializedDAO<ClientDTO>(dao)
    {}
};

From within ClientDAO I have to set some database information for UniversalDAO. That's where my implementation gets ugly because I do it like this:

QMap<QString, QString> fieldMapper;
fieldMapper["client_id"] = "clientId";
fieldMapper["name"] = "firstName";

/* ...all column <-> field pairs in here... */

dao->setFieldMapper(fieldMapper);
dao->setTable("client");
dao->setPrimaryKey("client_id");

I do it in constructor so it's not visible at a first glance for someone browsing through the header. In .Net version it was easy to spot and understand.

Do you have some ideas how I could make it better?

Answer 1

As far as I know there is nothing ready made that gives to this facility directly in qt. There are some possible approaches.

• Implement the fields as Q_PROPERTY, the are then reflected through the Metaclass system and can be used to implement generic DAO functionality

• You could still use the QSqlTableModel but encapsulate writes with transactions, if a transaction fails, refresh the model from the DB. Feasibility depends on the size of the data that you hold in the the model.

We currently use a TableModel/QSqlRecord based approach for reading and writing, there is no ORM mapping done in our system. I have been trying to engineer a more generic approach but the refactoring work that we would have to do to get there is to costly at the moment.

This link http://giorgiosironi.blogspot.com/2009/08/10-orm-patterns-components-of-object.html is not Qt related but a good overview of implementation patterns

Answer 2

Tegesoft has recently release a new version of its library named CAMP that provide C++ runtime reflexion as you are using in .Net. I think this will allow you to achieve your application like you have done in .Net.

Answer 3

There is also a new open source ORM C++ library : QxOrm. QxOrm is based on QtSql Qt module to communicate with database and boost::serialization to serialize your data with xml and binary format. The web site is in french but quick sample code and tutorial code is in english (a translation is in progress...).

Answer 4

If you want an ORM which only depends on Qt and builds upon Qt's Meta-Object System to provide instrospection, you might consider trying QDjango. On top of the basic create/update/delete operations at the model level, it provides a queryset template class (modeled after django's querysets) which allows to build fairly complex lookups. QtScript integration is also underway.

Answer 5

...And one more new Qt ORM: QST: QsT SQL Tools (latest stable version - 0.4.2a release). QST provides mechanism to generate simple SQL queries: SELECT, INSERT, DELETE, UNPDATE and EXECUTE. Version 0.4 uses T-SQL; new version - 0.5 - will use PostgreSQL by default. You will find, this ORM based on original, unusual conceptions. For example, it integrated with Qt Interview system, so you can setting up view representation (column widths, titles) much easy.

There are example projects for versions 0.3 and 0.4: TradeDB 0.3, TradeDB 0.4. TradeDB 0.4 should be useful to start learning QST.