Store custom QVariant type in database

Max13

I have a class called Schedule (which has quint8 and QMap<QString, QPair<QTime, QTime> >), successfully registered as a QMetaType, and I can use it with QVariant (tried using QVariant::fromValue(schedule).value<Schedule>()).

I want to insert a Schedule in a column in a database (currently sqlite but is it relevant?), when I bind a Schedule value to a prepared query, the column ends up empty in the database.

Then I wondered how Qt would be able to convert my value to a correct format for my database... I tried to look into the docs, google, forum... I can't find anywhere what to do to "tell" Qt how to convert my QVariant to even a raw binary representation of Schedule to insert it.

Did I miss something in the docs please?

JKSH

@Max13 said in Store custom QVariant type in database:

I want to insert a Schedule in a column in a database (currently sqlite but is it relevant?), when I bind a Schedule value to a prepared query, the column ends up empty in the database.

~~What is the return value of QSqlQuery::bindValue()? After you try to bind, what does QSqlQuery::lastError() return?~~

Then I wondered how Qt would be able to convert my value to a correct format for my database... I tried to look into the docs, google, forum... I can't find anywhere what to do to "tell" Qt how to convert my QVariant to even a raw binary representation of Schedule to insert it.

Did I miss something in the docs please?

See https://doc.qt.io/qt-5/sql-types.html for a list of supported types.

If you want to store binary data (SQL BLOB), serialize your Schedule class into a QByteArray first.

Max13

@JKSH Thanks for your answer.

You said in Store custom QVariant type in database:

What is the return value of QSqlQuery::bindValue()? After you try to bind, what does QSqlQuery::lastError() return?

If you are talking about QSqlQuery::boundValue() instead, it correctly returns my QVariant custom type (checked using QVariant::value<Schedule>()). There is no error after binding, nor after executing.

See https://doc.qt.io/qt-5/sql-types.html for a list of supported types.

If you want to store binary data (SQL BLOB), serialize your Schedule class into a QByteArray first.

I saw this doc, but it states:

This table shows the recommended data types for extracting data from the databases supported in Qt. Note that types used in Qt are not necessarily valid as input types to a specific database system.

I understood that it's only related to the extraction. If this doc is relevant for saving to DB, is there anything to do to make by type automatically cast to QByteArray then?

I'm trying to save multiple models automatically, and I would like to avoid checking in foreachs the QVariant types, if it's a custom type then call toByteArray() on it. Is there a magical (Qt) way to make a given QVariant converted to QByteArray using a method when saved to database?

I even tried to write a QDataStream::operator<<() function, because I thought maybe it may be automatically called 😓...

JKSH

@Max13 said in Store custom QVariant type in database:

If you are talking about QSqlQuery::boundValue() instead, it correctly returns my QVariant custom type (checked using QVariant::value<Schedule>()). There is no error after binding, nor after executing.

My apologies, I remembered QSqlQuery wrongly; please ignore that part of my post.

The bindings are stored in memory as QVariants; they are only converted at exec().

I saw this doc, but it states:

This table shows the recommended data types for extracting data from the databases supported in Qt. Note that types used in Qt are not necessarily valid as input types to a specific database system.

I understood that it's only related to the extraction.

You're right; the doc states that the information is about data extraction. However, it is still quite valid for data insertion.

Here is the code where the Qt converts your QVariant into a form that is understood by the SQLite: https://code.woboq.org/qt5/qtbase/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp.html#_ZN13QSQLiteResult4execEv

Notice that:

If the QVariant contains a QByteArray, Qt calls sqlite3_bind_blob()
If the QVariant contains an unrecognized type, Qt calls QVariant::toString() followed by sqlite3_bind_text16().

I even tried to write a QDataStream::operator<<() function, because I thought maybe it may be automatically called 😓...

...

is there anything to do to make by type automatically cast to QByteArray then?

...

Is there a magical (Qt) way to make a given QVariant converted to QByteArray using a method when saved to database?

As shown in the code above, the Qt SQLite driver does not use QDataStream and does not provide a magic way to convert your data into a QByteArray.

If you want to store your data as a BLOB, you must convert your data to a QByteArray before you bind it.

I'm trying to save multiple models automatically, and I would like to avoid checking in foreachs the QVariant types, if it's a custom type then call toByteArray() on it.

I'm afraid you cannot avoid these checks.

In fact, I would go even further and convert your data into table form, rather than squeeze it all into a single cell as a BLOB. For example, if I had 2 Schedules like this:

struct Schedule {
    quint8 id;
    QMap<QString, QPair<QTime, QTime>> intervals;
};

...

Schedule s1;
s1.id = 1;
s1.intervals["Init"]    = { QTime(0, 0), QTime(0, 30) }; 
s1.intervals["Run"]     = { QTime(2, 0), QTime(2, 30) };
s1.intervals["Cleanup"] = { QTime(4, 0), QTime(4, 30) }; 

Schedule s2;
s2.id = 2;
s2.intervals["Part A"] = { QTime(13, 0), QTime(14, 0) }; 
s2.intervals["Part B"] = { QTime(15, 0), QTime(16, 0) };

I would store them in a database table like this:

scheduleId	intervalName	intervalStart	intervalEnd
1	Init	00:00	00:30
1	Run	02:00	02:30
1	Cleanup	04:00	04:30
2	Part A	13:00	14:00
2	Part B	15:00	16:00

This is how a relational database is intended to be used.

Max13

@JKSH said in Store custom QVariant type in database:

You're right; the doc states that the information is about data extraction. However, it is still quite valid for data insertion.

Here is the code where the Qt converts your QVariant into a form that is understood by the SQLite: https://code.woboq.org/qt5/qtbase/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp.html#_ZN13QSQLiteResult4execEv

Notice that:

If the QVariant contains a QByteArray, Qt calls sqlite3_bind_blob()

If the QVariant contains an unrecognized type, Qt calls QVariant::toString() followed by sqlite3_bind_text16().

It would be great if, in a way, QSqlDriver could automatically call toString() or toByteArray() on the QVariant real type (I see it possible using QMetaType) when saving to DB. Qt's magical way.

As shown in the code above, the Qt SQLite driver does not use QDataStream and does not provide a magic way to convert your data into a QByteArray.

If you want to store your data as a BLOB, you must convert your data to a QByteArray before you bind it.

I will write a toJson() instead, as I'm loading the data from Json already.

I'm trying to save multiple models automatically, and I would like to avoid checking in foreachs the QVariant types, if it's a custom type then call toByteArray() on it.

I'm afraid you cannot avoid these checks.

In fact, I would go even further and convert your data into table form, rather than squeeze it all into a single cell as a BLOB. For example, if I had 2 Schedules like this:
struct Schedule {
    quint8 id;
    QMap<QString, QPair<QTime, QTime>> intervals;
};

...

Schedule s1;
s1.id = 1;
s1.intervals["Init"]    = { QTime(0, 0), QTime(0, 30) }; 
s1.intervals["Run"]     = { QTime(2, 0), QTime(2, 30) };
s1.intervals["Cleanup"] = { QTime(4, 0), QTime(4, 30) }; 

Schedule s2;
s2.id = 2;
s2.intervals["Part A"] = { QTime(13, 0), QTime(14, 0) }; 
s2.intervals["Part B"] = { QTime(15, 0), QTime(16, 0) };
I would store them in a database table like this:

scheduleId intervalName intervalStart intervalEnd

1 Init 00:00 00:30

1 Run 02:00 02:30

1 Cleanup 04:00 04:30

2 Part A 13:00 14:00

2 Part B 15:00 16:00

This is how a relational database is intended to be used.

Thanks for your advice. Indeed, it would be the correct way to implement this, in my situation I didn't think it would be necessary to implement it that way.

I'm updating some models from an API, and save them as a read-only cache in an sqlite because the desktop may be disconnected. So in my opinion, adding another table would make me write more queries and deal with relations when I can do that in a nasty way 😅

Thanks for your complete answer.

JKSH

@Max13 said in Store custom QVariant type in database:

I will write a toJson() instead, as I'm loading the data from Json already.

Sounds good.

Thanks for your advice. Indeed, it would be the correct way to implement this, in my situation I didn't think it would be necessary to implement it that way.

I'm updating some models from an API, and save them as a read-only cache in an sqlite because the desktop may be disconnected. So in my opinion, adding another table would make me write more queries and deal with relations when I can do that in a nasty way 😅

That's fair enough. Simplicity is often a good thing in code.

Thanks for your complete answer.

You're most welcome. Happy coding!