Database table transfer over TCP

DoughBoy · 21 Sept 2016, 19:08

Hello everyone,

I'm trying to share data between two platforms. One platform is collecting and analyzing data, the other platform displays the data (charts/graphs, etc). When the two platforms are connected, the data is shared freely. But when the connection is terminated, the displaying platform then needs to download all of the stored data from the other platform (once reconnected). This could be hours, days, or even months of data that needs to be downloaded.

Currently, I have all of the data stored into databases. When the displaying platform reconnects to the data platform, the software identifies the last written data record and sends the date/time stamp of that record to the data platform. The data platform then runs an SQL query to collect all of the records newer than the given date/time stamp. And... this is where I'm stuck.

What's the most practical approach to send this collected data? To me, the simplistic answer would be to serialize QStrings of each record into a huge data array to be sent to the displaying platform. Where this platform then inserts all that data into its local database. But I imagine this method could take a long time - something I'm not interested in doing. I have thought about serializing QSqlTableModel, but I don't think this is possible.

Has anyone implemented something similar to what I'm doing? Any help or guidance would be greatly appreciated!

mjsurette · wrote on 21 Sept 2016, 19:08

@DoughBoy

The first thing I would try in these circumstances would be to put the output of your query into a new SQLite database file. Compress that file and send it along.

This would have the advantage of being fairly straightforward.

Mike

DoughBoy · 22 Sept 2016, 16:50

@mjsurette
Thank you very much for your suggestion. A quick recap, just so we are on the same page:

Collect the targeted data rows that are needed to be sent to the remote platform [DONE]
Create a new database file, inserting those collected rows to the new database [Unsure of this]
Send the database file to the remote platform [This is straight forward]
Once the database file is downloaded, collect all the data within this database [straight forward]
Insert the downloaded data into the original database [Unsure of this]

My two "unsure" questions are the same - How could I do a mass insert of data into a database? All of my database knowledge is built on "INSERT INTO" statements for each data row. This could be cumbersome if there are thousands of records to insert. Any guidance on this would be greatly appreciated. I'll start searching for a solution to my questions and post back the answer if I find it before you respond.

Thanks again and I look forward to hearing from you.

kshegunov · wrote on 22 Sept 2016, 16:50

@DoughBoy
Well my advice is to just serialize the data out (but not in strings, rather binary) and send it to the client. When the client receives a piece it deserializes it and inserts it locally (this can be batched so while the client's waiting it'd be inserting in the local db).

All of my database knowledge is built on "INSERT INTO" statements for each data row. This could be cumbersome if there are thousands of records to insert.

Firstly, start a transaction. Inserts go much faster when doing it on a snapshot.
Secondly, you can use the compact multi-row insert:

INSERT INTO TableName (FieldName1, FieldName 2) VALUES (row1value1, row1value2), (row2value1, row2value2), (...)

If that's not practical you can use the driver's capabilities to bind values directly (QSqlQuery::bindValue), if it supports it, which should run quite rapidly.

Something like this:

QSqlQuery query(db);
db.transaction(); //< You should handle errors yourself, this is just an example
query.prepare("INSERT INTO TableName (Field1, FIeld2) VALUES (:boundName1, :boundName2)");

while (haveMoreData)  {
    deserializeData();
    // Bind new values and execute. This is the whole idea behind `prepare()`!
    query.bindValue(":boundName1", currentValue1);
    query.bindValue(":boundName2", currentValue2);
    query.exec();
}

db.commit();

Kind regards.

DoughBoy · 22 Sept 2016, 18:24

@kshegunov
Thanks for your reply. I found the same solution as you pointed out. http://www.sqlite.org/faq.html#q19

And thank you very much for the insertion examples. But now you've raised a question - serializing the data set. Can you give me an example of how I can convert the data collection into a byte array to be transmitted?

Thanks again!

kshegunov · wrote on 22 Sept 2016, 18:24

@DoughBoy said in Database table transfer over TCP:

Can you give me an example of how I can convert the data collection into a byte array to be transmitted?

Just use what QDataStream provides, it's exactly what it was invented for. For example (pseudo code):


QByteArray rowData;
QBuffer buffer(&rowData);
buffer.open(QIODevice::WriteOnly);

QDataStream out(&buffer);

int rows = 0;
while (moreRowsToFetchFromDb())  {
    fetchRow();

    QVariant value1, value2, value3;   //< This'd be the data extracted from the database (for a single row)
    out << value1 << value2 << value3; //< Write the values to the data stream

    rows++;
    if (rows % (batchSize + 1) == 0 || !moreRowsToFetchFromDb())  {
        packageUpAMessageBatchAndSend(rows, rowData);

        rows = 0; //< Reset batch counter

        buffer.close();
        buffer.open(QIODevice::WriteOnly | QIODevice::Truncate);
    }
}

DoughBoy · 22 Sept 2016, 18:39

@kshegunov
Thanks again for your assistance. I had a feeling you were going to suggest this. I was hoping for an easier method, but from this list (http://doc.qt.io/qt-4.8/datastreamformat.html) QSql-anything wasn't easily serializable.

So - I have two methods to try:

Export the SQL records to a database file and export the file
Export the SQL records, row by row into a QByteArray to be exported

Thank you again for all your help and guidance. I'll post back if I have any other questions.

mjsurette · wrote on 22 Sept 2016, 18:37

@DoughBoy
The easiest way to do this if you're running SQLite all around. Then all you need is an INSERT with a SELECT instead of the VALUES clause.

If you're not then things get a little more complicated, where you have to iterate through the rows of your SELECT and INSERT them in the new database.

Mike

kshegunov · wrote on 22 Sept 2016, 18:39

@DoughBoy said in Database table transfer over TCP:

QSql-anything wasn't easily serializable.

You get everything from the SQL database as QVariant which is directly serializeable! I don't see what's the issue here.

VRonin · wrote on 22 Sept 2016, 18:45

If your database driver has QSqlDriver::BatchOperations feature you can put each column of data in a QVariantList, bind it to an insert as you would with a normal insert and call execBatch. see http://doc.qt.io/qt-5/qsqlquery.html#execBatch

I have thought about serializing QSqlTableModel, but I don't think this is possible.

it is, see https://github.com/VSRonin/Qt-Model-Serialisation for a xml serialisation method. with that you can save the model directly on the QTcpSocket, have the receiver dump it all in a file and load it once the transmission is completed (I'm not, in any way, saying this is the most efficient way though)

DoughBoy · 22 Sept 2016, 19:02

@mjsurette
Thanks for the suggestion of using INSERT with a SELECT. I'll need to read more about this command strategy. It's a new method for me.

@kshegunov
There's no issue. As you pointed out my method would be:

//Create the output stream
QDataStream out(&buffer);
//Create the database query object
QSqlQuery dbQuery(dbData);
//Execute the SQL command to retrieve the targeted data rows
dbQuery.exec(selStr);
//Loop through all rows/columns to extract the collected data to the output stream
while(dbQuery.next()) {
    for(int i=0; i<dbQuery.record().count(); ++i)
        out << dbQuery.value(i);
}

I was hoping more for a "plug'n play" approach. Instead of looping through all columns and rows. (i.e. out << dbQuery);

@VRonin
Thank you very much for sharing your method for Serializing QT Models. I'll look more into this idea.

kshegunov · 22 Sept 2016, 19:08

@DoughBoy said in Database table transfer over TCP:

I was hoping more for a "plug'n play" approach. Instead of looping through all columns and rows. (i.e. out << dbQuery);

You can do that yourself:

while(dbQuery.next()) {
    out << dbQuery;
}

Where you define your own operator <<:

QDataStream & operator << (QDataStream & out, const QSqlQuery & query)
{
    if (!query.isValid() || !query.isActive())
         return out; //< Handle the error accordingly.

    QSqlRecord record = query.record();
    for(qint32 i = 0, count = record.count(); i < count; i++)
        out << record.value(i); //< You can even write the field names if you so desire.

    return out;
}

VRonin · 22 Sept 2016, 19:10

@kshegunov did you miss a loop?

QDataStream & operator << (QDataStream & out, const QSqlQuery & query)
{
    if (!query.isValid() || !query.isActive())
         return out; //< Handle the error accordingly.
while(query.next()){
    const QSqlRecord record = query.record();
    for(qint32 i = 0, count = record.count(); i < count; i++)
        out << record.value(i); //< You can even write the field names if you so desire.
}
    return out;
}

You probably need to serialise count too otherwise you wouldn't know how many columns there are

kshegunov · 22 Sept 2016, 19:10

@VRonin said in Database table transfer over TCP:

did you miss a loop?

Nope! See two lines above:

while(dbQuery.next())

:)

VRonin · 22 Sept 2016, 19:11

@kshegunov I need a coffee ;)

kshegunov · V VRonin 22 Sept 2016, 19:10

@VRonin said in Database table transfer over TCP:

I need a coffee ;)

@DoughBoy Sorry for the offtopic. I couldn't resist. ;)

DoughBoy · 23 Sept 2016, 01:23

@kshegunov
It's perfectly okay to get off topic. If you're not having fun, then you gota do something else!

And yes, you are correct, I could define my own operator << and operator >>. But as @VRonin pointed out I would need to serialize column count and row count to properly know how the data is getting disassembled - in order to reassemble the SQL data structure. I didn't make that point clear in my previous post. I was hoping to maintain that unknown structure with an actual bit-by-bit serialization of the QSqlQuery stored in memory. I actually believe it would be best to work with the QSqlQueryModel in that instance (maybe I'm wrong?).

For simplicity, it seems @mjsurette has offered the approach I'm looking for, exporting the data set to a database file then to export that file (bit-by-bit) to the remote platform where I can then handle the SQL data collection in that manner. I could be mistaken, but I believe this approach would be much faster than disassembling the SQL data then reassembling it. Would you agree?

mjsurette · wrote on 23 Sept 2016, 01:23

@DoughBoy

Like you've mentioned, my approach has the advantage of simplicity. It also keeps the data structure in the transferred file. Personally I'm a big believer in keeping things simple. It makes things more reliable, as in harder to mess up, and easier to troubleshoot if they go wrong.

The sqlite file format compresses fairly well. The Chinook_Sqlite.sqlite test database compresses from 1,067,008 bytes to 343,098 bytes using the default compression on my system.

As far as speed of the transfer, or any other parameter, modern systems are pretty fast and I would think that i/o would be the bottleneck. That loop might not be as bad as you think. So, I'd hate to assign any advantage to any method without first trying it.

Mike

kshegunov · wrote on 23 Sept 2016, 18:37

@DoughBoy said in Database table transfer over TCP:

But as @VRonin pointed out I would need to serialize column count and row count to properly know how the data is getting disassembled - in order to reassemble the SQL data structure.

Yes you need the number of columns, their names, number of rows and the table name for each dataset you send (the set of rows). A package could look something like this (pseudo binary):

[TableName: QString]
[ColumnsNumber: qint8]
ColumnsNumber x [ColumnName: QString]
[RowsNumber: qint32]
RowsNumber x ColumnsNumber x [FieldValue: QVariant]

I was hoping to maintain that unknown structure with an actual bit-by-bit serialization of the QSqlQuery stored in memory.

Not possible. At least not to my knowledge.

I actually believe it would be best to work with the QSqlQueryModel in that instance (maybe I'm wrong?).

I think my way best, but I'm biased. In any case, I advise to use whatever you find most easy/convenient to implement, which ultimately means most easy to maintain!

I believe this approach would be much faster than disassembling the SQL data then reassembling it

Probably not. Even with memory based SQLite table the overhead would be higher, but again it depends on how much higher ... if it's 10% I say the hell with it, if it's 100% I say think twice. The problem is you can't really tell before implementing both approaches and benchmarking them ...

DoughBoy · 26 Sept 2016, 23:31

Hello everyone!
I've tried to implement both methods, but I've ran into a slight snag.

@kshegunov
Your method is straight forward! With a QDataStream writing to a QByteArray, I first store the column headers (as QString) and all data values (as QVarient). Then to a parenting QByteArray I keep track of the Database name, Table name, number of rows, number of columns, and the data collection's QByteArray. It is then this parenting QByteArray I send to the remote platform for data extraction and storage. Working with 20 days of data, I was able to collect, transmit, and store the data in just about 4 minutes (not that this matters, but it's a way for me to compare the performance difference).

@mjsurette
I'm having a difficult time getting your strategy implemented. I'm able to populate a QSqlTableModel with my desired SQL query. I'm using the TableModel because it is said that this object can easily read/write to a database table. Sadly though, how do I then take the table's data and switch to a new QSqlDatabase object (this is defined in the QSqlTableModel's constructor)? The way we discussed this, I would have 2 different QSqlDatabase objects - 1) is the original data; 2) is the exported data. Could you offer some more guidance with how I can move data from one database to another?

Thanks and I look forward to hearing from you.