What is the best way to read/write objects in a file with the following conditions

FroZtiZ

Dear community,
I am willing to store objects in a database. The purpose is to be able to read / write these objects with the program. The requirements are the following:

Objects can be complex using Qt classes such as QList, QString ... or even can contain other objects that use QObjects
The database should not be readable or modified by human (no text file, and if I use sqlite database, it has to be encrypted in a way)
I should be able to remove, read an object by its name and count the number of objects in the database, without loading everything in the memory

I provide below a minimalist example to do this with a QDataStream . But it seems it is not the best way to proceed. Would you have some suggestions regarding the solutions that exist for this purpose?

I have tried the following but it does not fulfill the requirements:

Storing text in sqlite with QtSQL: but the data is accessible by using sqlitemanager for example, can be modified or removed by humans. Moreover, I have no idea regarding the way to store QList for example or other objects that I created and contain QObject (for example, 2 QList)
Storing binary data using QDataStream (example below): in this case, I cannot count the number of objects in my file, neither read or remove a specific object without loading the entire file in memory.

I would be grateful if you could give me some suggestions or provide example, even if the example is minimalist.

#include "QString"
#include "QFile"
#include "QDataStream"
#include "qdebug.h"

class User
{
protected:
QString name;
QList<QString> childrens;

public:
QString getName(){ return name;}
QList<QString> getChildrens(){ return childrens;}

void setName(QString x) {name = x;}
void setChildrens(QList<QString> x) {childrens = x;}

//I have no idea of how to get the number of users in "test.db"
int countDatabase()
{

}

//I would like to read the user named "pn" without putting all users in memory
void read(QString pn)
{
    QFile fileRead("test.db");
    if (!fileRead.open(QIODevice::ReadOnly)) {
        qDebug() << "Cannot open file for writing: test.db";
        return;
    }
    QDataStream in(&fileRead);
    in.setVersion(QDataStream::Qt_5_14);
    in>>*this;
}


void write()
{
    QFile file("test.db");
    if (!file.open(QIODevice::WriteOnly | QIODevice::Append)) {
        qDebug() << "Cannot open file for writing: test.db";
        return;
    }
    QDataStream out(&file);
    out.setVersion(QDataStream::Qt_5_14);
    out<<*this;
}

friend QDataStream &operator<<(QDataStream &out, const User &t)
{
    out << t.name << t.childrens;
    return out;
}

friend QDataStream &operator>>(QDataStream &in, User &t)
{
    QString inname;
    QList<QString> inchildrens;
    in >> inname >> inchildrens;
    t.name = inname;
    t.childrens = inchildrens;
    return in;
}

};


////////////////////////////////////////////////////////////////
int main()
{
    User u;
    u.setName("Georges");
    u.setChildrens(QList<QString>()<<"Jeanne"<<"Jean");
    u.write();

    User v;
    u.setName("Alex");
    u.setChildrens(QList<QString>()<<"Matthew");
    u.write();

    User w;
    w.setName("Mario"); // no children
    w.write();

    User to_read;
    to_read.read("Alex");

    qDebug()<<to_read.getName();
    return 0;
}

mrjj

Hi
Its a hard question to answer without knowing more about the app.
But lets look at the options.

Storing text in sqlite with QtSQL: but the data is accessible by using sqlitemanager for example, can be modified or removed by humans. Moreover, I have no idea regarding the way to store QList for example or other objects that I created and contain QObject (for example, 2 QList)

we could use https://www.zetetic.net/sqlcipher/ to protect the db.
however using sqlite, you must flatten the objects to tables yourself and read and write them as table data, not as binary objects.

So in this case you would first design the tables and then write the code to flatten your obejct to and from these tables.

Storing binary data using QDataStream (example below): in this case, I cannot count the number of objects in my file, neither read or remove a specific object without loading the entire file in memory.

Using QDataStream, you can reuse Qt ability to save most of its types.
And adding QDataStream &operator to your own classes would allow to stream them too.

To know how many children it has you can either find out what QList writes for header (it includes the item number)
or simply save your own header holding this info before saving the actual object. That way you can read part of the file and know how many objects.
Its also possible to seek around in file to read one object but that's potentially error prone.
Its not possible however to remove an object from file without rewriting it.

That leads me to the question. is the db really so big you cannot read it all ?

FroZtiZ

Hello mrjj, and thank you for your response.

For the first option, I can indead protect the SQL db. However, is it possible to store a QList inside a SQL db, even if from an object to another, QList length can be different? In this case, how to write a QList in a SQL.

For the second option, the list can be quite big. The User example does not reflect it because we generally dont have 100 childrens. But my real class contains x, y coordinates of a function that can be with many points. Hence, the db can be really big ;).
I tried to use seek option but objects store in binary file with QDataStream dont have all the same length.

I found some information on internet about storing BLOB in SQL database. It s a possible investigation.

JonB

@FroZtiZ said in What is the best way to read/write objects in a file with the following conditions:

I should be able to remove, read an object by its name and count the number of objects in the database, without loading everything in the memory

I think you're going to find this very hard to achieve!

As you say, the way to store Qt thingies in a SQL db is (probably) via QDataStream serialization and storing as a large binary BLOB (or TEXT if it comes out that way, but I don't think it does). BLOBs are not made for accessing bits of them as objects!

Even just as a natural Qt QDataStream, you can't just access some arbitrary member object somewhere inside it --- when the object you want is nested down inside some other object you have to access it by deserializing the ancestor hierarchy first (not to mention that you've probably got to deserialize your object's descendants too), which of course can be huge....

Not surprisingly, Qt's objects have been designed to be used in memory, not to be manipulated in a database.

Quite what you are trying to achieve with your code/approach I have no idea!

SGaist

Hi and welcome to devnet,

Are you looking for something like QxOrm ?

FroZtiZ

I finally found a solution, especially thanks to a stackoverflow user and thanks to the topic here: https://www.qtcentre.org/threads/65654-how-to-save-QVector-lt-float-gt-into-sqlite-as-BLOB

I haven't quite finalized, there is a small imperfection because I have to define an object of my user class that I don't use in order to call the readFromDB function to generate my object.

On the other hand, I get this error message "QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed" each time I call my database.

Anyway, it's a bit late now, and I think it might help some people so I post this imperfect code below. I'll post an update in the next few days.

Thanks again

#include "QString"
#include "QFile"
#include "QDataStream"
#include "qdebug.h"
#include "QtSql"
#include "QSqlDatabase"
#include "qmessagebox.h"

class User
{
protected:
QString name;
QList<QString> childrens;

public:
QString getName(){ return name;}
QList<QString> getChildrens(){ return childrens;}

void setName(QString x) {name = x;}
void setChildrens(QList<QString> x) {childrens = x;}



friend QDataStream &operator<<(QDataStream &out, const User &t)
{
    out << t.name << t.childrens;
    return out;
}

friend QDataStream &operator>>(QDataStream &in, User &t)
{
    QString inname;
    QList<QString> inchildrens;
    in >> inname >> inchildrens;
    t.name = inname;
    t.childrens = inchildrens;
    return in;
}
QByteArray object2blob( const User& user )
{
  QByteArray result;
  QDataStream bWrite( &result, QIODevice::WriteOnly );
  bWrite << user;

  return result;

}
User blob2object( const QByteArray& buffer )
{
  User result;
  QDataStream bRead( buffer );
  bRead >> result;

  return result;
}
int saveToDB( const User& user )
{
    QSqlDatabase myDB = QSqlDatabase::addDatabase("QSQLITE");
    myDB.setDatabaseName( "file.db");
    if (!myDB.open())
    {
        qDebug()<<"Failed to open SQL database of registered users";
    }
    else
    {
        qDebug()<<"Successfully opening SQL database of registered users";
        QSqlQuery query;

        query.prepare( "CREATE TABLE IF NOT EXISTS users (name TEXT, childrens BLOB)" );
          if( !query.exec() )
          {
            qDebug() << query.lastError();
          }
          else
          {
            qDebug() << "Table created!";
            query.prepare( "INSERT INTO users (name,childrens) VALUES (:name,:childrens)" );
            query.bindValue(":name", name);
            query.bindValue( ":childrens",  object2blob(user) );
            query.exec();
          }
        query.clear();
        myDB.close();
    }
    QSqlDatabase::removeDatabase("UserConnection");

    return 0;
}
User readFromDB( QString name )
{
    User result;
    QSqlDatabase myDB = QSqlDatabase::addDatabase("QSQLITE");
    myDB.setDatabaseName( "file.db");
    if (!myDB.open())
    {
        qDebug()<<"Failed to open SQL database of registered users";
    }
    else
    {
        QSqlQuery query;

        query.prepare( "SELECT * FROM users WHERE name ='"+ name +"'" );
        //query.bindValue( 0, name );

        if ( query.exec() && query.next() ) {
          result = blob2object( query.value( 1 ).toByteArray() );
        }
        query.clear();
        myDB.close();
    }
    QSqlDatabase::removeDatabase("UserConnection");
    qDebug()<<result.getChildrens();
    return result;
}
};


////////////////////////////////////////////////////////////////
int main()
{
    User u;
    u.setName("Georges");
    u.setChildrens(QList<QString>()<<"Jeanne"<<"Jean");
    u.saveToDB(u);

    User v;
    v.setName("Alex");
    v.setChildrens(QList<QString>()<<"Matthew");
    v.saveToDB(v);

    User w;
    w.setName("Mario");
    w.saveToDB(w);

    User to_read;//not perfect here
    User a = to_read.readFromDB("Georges");

    qDebug()<<a.getChildrens();
    return 0;
}

SGaist

You should take a look again at the QtSql examples. There's no use re-creating your database connection every time you use it especially since you have only one database and thus use the default connection.