How to store enum in SQLite database

Question

I'm trying to store enum in SQLite database using QSql. I have following class:

partydao.h:

class PartyDao : public QObject
{
public:
  enum partyType { typeCompany, typePerson };

private:

  Q_OBJECT
  Q_ENUMS(partyType)
  Q_PROPERTY(partyType type READ type WRITE set_type)

  // other declarations
};

Q_DECLARE_METATYPE(PartyDao::partyType)

partydao.cpp:

#include "partydao.h"

static int id = qRegisterMetaType<PartyDao::partyType>("partyType");

I do insert like this:

PartyDao p;
p.setProperty("type", QVariant::fromValue(PartyDao::typePerson));

QSqlQuery query;
query.prepare("INSERT INTO party (type) values (:type)");
qDebug() << p.property("type").isNull();
query.bindValue(":type", p.property("type"));
query.exec();

Although qDebug() prints "false" (i.e. property is not null), null value is stored in db.

I've tried with column of type TEXT and INTEGER with no success.

Can you tell me what I'm doing wrong?

EDIT:

I've just checked and QVariant holding my enum claims it can't be converted to QString or int (canConvert<int>() and canConvert<QString>() return false). Is there a way to add this conversion?

zarzych zarzych · Accepted Answer · 2010-12-13T23:11:24

I didn't find any way to use auto conversion of QVariants of user type. Therefore I've created singleton storing rules for conversion.

struct DbConverter
{
  virtual ~DbConverter() {}

  virtual QVariant toDbValue(const QVariant &value) = 0;
  virtual QVariant fromDbValue(const QVariant &value) = 0;
};


class DbConversion
{
public:
  static QVariant toDbValue(const QVariant &value)
  {
    return instance()->m_converters.contains(value.userType()) ?
            instance()->m_converters[value.userType()]->toDbValue(value) :
            value;
  }

  static QVariant fromDbValue(int type, const QVariant &value)
  {
    return instance()->m_converters.contains(type) ?
            instance()->m_converters[type]->fromDbValue(value):
            value;
  }

  static int setConverter(int typeId, DbConverter *converter)
  {
    instance()->m_converters[typeId] = converter;
    return typeId;
  }

private:
  static DbConversion *instance()
  {
    static DbConversion *inst = new DbConversion;
    return inst;
  }

  QHash<int, DbConverter*> m_converters;
};

I register my custom types using value returned from qRegisterMetaType() and perform conversions on each app<->db transfer.

Please let me know if you find any more elegant solution and I'll be happy to accept your answer.

How to store enum in SQLite database

2 Answers