500 Variables in class how to get/set/store in json efficiently?

StudentScripter

@SGaist @JonB So just wanted to add onto this, to get a short feedback on whether im on the right track or not.
This is how i create my database as a test. Is that a good way or is there a better practice on dynamically creating a database with SQLLite? :) Have a nice day.



MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
{


    QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName("C:/Users/Musik/Desktop/TestSQLPATH/database.db");

    if (!m_db.open()) {
        qDebug() << "Error: connection with database failed";
    } else {
        qDebug() << "Database: connection ok";
    }

    // Sample QVariantMap (replace this with your actual QVariantMap)
    QVariantMap dataMap;
    dataMap["name"] = "John";
    dataMap["age"] = 30;
    dataMap["city"] = "New York";
    dataMap["son"] = "Paul";

      QString tableName = "MyCustomTableName";  // Table name

    // Drop the table if it exists
    if (m_db.tables().contains(tableName)) {
        QSqlQuery dropQuery;
        if (!dropQuery.exec("DROP TABLE " + tableName)) {
            qDebug() << "Error dropping table:" << dropQuery.lastError();
        }
    }



    if (m_db.isOpen()) {  // Überprüfe, ob die Datenbank geöffnet ist



        QString createString = "CREATE TABLE " + tableName + "(";
        QString insertString1 = "INSERT INTO " + tableName + "(";
        QString insertString2 = " VALUES (";

        for (auto it = dataMap.begin(); it != dataMap.end(); ++it) {
            const QVariant& value = it.value();
            QString mykey = it.key();

            if (value.typeId() == QMetaType::Type::QString) {
                createString += mykey + " TEXT";
            } else if (value.typeId() == QMetaType::Type::Int) {
                createString += mykey + " INTEGER";
            } else if (value.typeId() == QMetaType::Type::Double) {
                createString += mykey + " DOUBLE";
            } // Add more types as needed



            insertString1 += mykey;
            insertString2 += ":" + mykey;



            if (it != --dataMap.end()) {
                createString += ", "; // Add comma if not the last element
                insertString1 += ", ";
                insertString2 += ", ";
            }

            qDebug() << "Key:" << it.key() << ", Value:" << it.value();
        }

        createString += ")";
        insertString1 += ")";
        insertString2 += ")";
        QString insertfullstring = insertString1 += insertString2;
        qDebug() << createString << insertfullstring;




        // Überprüfe, ob die Tabelle existiert, bevor du einfügen
        if (!m_db.tables().contains(tableName)) {
          QSqlQuery createQuery;

            if (!createQuery.exec(createString)) {
                qDebug() << "Error creating table:" << createQuery.lastError();
            }
        }



        QSqlQuery query;
        query.prepare(insertfullstring);


        bindValuesToQuery(query, dataMap);



        if (query.exec()) {
            qDebug() << "Person hinzugefügt";
        } else {
            qDebug() << "Fehler beim Hinzufügen einer Person:"
                     << query.lastError();
        }

   m_db.close();  // Close the database when done
    } else {
        qDebug() << "Datenbank ist nicht geöffnet.";
    }



}

MainWindow::~MainWindow() {}

void MainWindow::bindValuesToQuery(QSqlQuery &query, const QVariantMap &map)
{
    for (auto it = map.begin(); it != map.end(); ++it) {
        const QVariant& value = it.value();
        QString key = it.key();

        query.bindValue(":" +key, value);

    }
}

JonB

@StudentScripter
Your code itself is OK per se.

However, I am not at all sure you have thought through the content/architecture. You dynamically create a table to correspond to the column type in one QVariantMap. I guess that will be OK to store multiple rows if your have an array/list of QVariantMaps where each & every item/row has exactly the same number of columns holding values of the same type in the same order. But useless if that is not the case, one item/row has any kind of different data. Only you know whether that is indeed the case.

Without a lot of thought/planning a database does not have the flexibility that JSON has for an arbitrary number of properties/columns, possibly of varying data types. It is more suited when the number of columns is known in advance and each column will have a known, fixed data type.

I did suggest earlier that, given where you were at and your knowledge, you might test whether the JSON file worked OK/fast enough to stick with that.

Both @SGaist & I suggested that usually you do not try to save/restore visual widget attributes. Rather you have some model behind what the UI shows and that is known and can be serialized/deserialized. Your 1,000 items with up to 500 attributes each must "come from somewhere", it does not pop up in a vacuum. Something told you to create a pixmap, set its position, alter its frame size/bounds, etc. That is supposed to be your "model", from which if you save you can re-create corresponding widgets with properties.

Also, if you have 500 arbitrary variables to save as columns in each row of a database that is not great. SQLite may or may not let you create a table with 500 columns, I don't know, but it's probably not a good architecture.

I have a feeling you are now going to be asking all sorts of questions about databases. @SGaist may wish to answer further, but for my part I feel I must leave you to it. There is a lot to learn about databases, particularly on the architecture/design/concept side, if you are going to start using them.

SGaist

@StudentScripter please take a step back.

You seem to want to use SQLite as a drop-in replacement of your original JSON structure which is the wrong move.

As @JonB and I suggested on several occasions now: get back to the drawing board and do some scribbling to describe your your data, application, model the data itself, properly separate the business logic from the UI.
Without a clearer picture it's just going to be technology thrown around without purpose.

Even if it's only for learning purposes, getting the design first before coding is a crucial step.

StudentScripter

@SGaist @JonB
Well thanks for leading me the way. I really want to improve instead of annoy and so tried to listen what you guys where telling. But I wanted to confirm that i got it the right way now.

So here is what i do now:

I divided my GUI into smaller groups (classes), so every group consist of only 10-15 widgets
For every group on runtime i store the values into a map. So every map holds 10-15 values with a specific key per value.
i want to create a different SQLLite table for each group, so every table has around 10-15 columns --> i would than transfer the last values of the maps to the corresponding table [I somehow want to set as row the id of my qgraphicsitems, cause as said the data does not directly belong to the graphicsitem but has to be shown when the specific item is selected]
So everytime when i select a different graphicsitem the data of the old graphicsitem is saved/transfered into the correct rows of the tables and the data of the now selected graphicsitem gets read from the table and stored in the map (or maybe 2 different maps 1 for save one for load)

Well so these are my new thoughts on a so i think way better structural design of my project. But let me know what you think. Im always open for further suggestions and corrections. :)

Thanks for the time and have a nice evening.

SGaist

@StudentScripter there's still one crucial information that is missing: what are you modeling ? What is it that requires 10 to 15 widgets to manage ? Based on your initial numbers (500 * 1000), it means you might be trying to create around 50000 widgets in your application.

StudentScripter

@SGaist Well thats may a misunderstanding. These where approximations on the values i have to store, but in fact there will be only around 500 widgets that actually display the data. Not all 50000 values are needed at once only around 500 at a time.

As said i graphicsitems and when one item is clicked a specific set of 500 values has to be displayed in these widgets (lineedits, spinboxes...).

I divided as the interface into sections so around 15 of these widgets have there own class and sql tabel: (just a quick sketch in word on how i imagine it, all example no real data)

As you can see the items decode/are the key to retrieve the data but besides that they don't have a connection to the data. It's just that i want to show this data when a certain item is clicked.
So for example with 9 different widget groups, 1000 graphicsitems, and 15 Widgets per widget group i would have:

15 Tables with 1000 rows (filled with the graphicsitem ids) and 15 columns each.

Hope that helps to understand what i want to do. Would that be a good way to approach that or not? (All numbers are not facts but just approximations)

Thanks for helping me out.

JonB

@StudentScripter
Hi. @SGaist and I have asked you over and over the same question, but you do not respond to it, not once!

This is my last attempt to glean the following information from you: what is your "model" behind the widgets? For example, where does the "specific set of 500 values has to be displayed in these widgets" actually come from? Do you just hard-code these 500 values/widgets in your code?

We would expect the data for those 500 widgets to come from some kind of "model". Not just appear as 500 lines of code. Then why do you have to save all of these? You should be able to reconstruct the widgets from this "model" at a later date, not to have to save the widgets.

On a separate note: it is hard to imagine how or why there would be 500 widgets which need to be constructed/displayed when a graphics item is clicked. It's just too many. How do you even lay them out? A better interface would probably be: show the 500 widget values in a table of consecutive lines. Note this is showing the widgets' values (as text), not the actual widgets. When the user clicks on one of these rows to edit a value, at that point go into edit mode and create the suitable corresponding widget. QTableView editing works like this. For example, if a value is text you might create a QLineEdit, if it is a number you might create a QSpinBox, etc. So only one widget is ever actually displayed, and only so long as the user interacts with it to enter a value. Then the editing widget disappears and is replaced again by the non-editable text value in the row.

StudentScripter

@JonB Well i really appreciate your efforts but not answering this question wasn't meant rude or being ignorant, i really appreciate ever hint. I just did not understand what you meant/wanted exactly. So here my attempt to try to explain it again:

You know game engines like unity have these sidebars where you can add modules. Every module has lineedits, checkboxes and so on (like in this picture on the righthandside)

So when a user enters a values/edit it in the textfields it has to be saved somewhere (in my case the qVariantmap wich than should transfer the data to the sql database). These modules are different for graphicsitem, so when a item gets clicked other modules with other textfields ... get displayed where user can enter different values.

I stumbeld uppon the sqltablemodel class but i don't know if this would be more useful to save the data and than transfer it to sql?

Thanks again for you efforts to understand me @JonB. I really know to value that highly. Also english is not my mother tongue, so it's not alway super easy to understand everything the way intended.

SGaist

@StudentScripter Now we are getting somewhere. So for the moment, leave the database/json stuff on the side.

Now I'll get back to my suggestion: first get your pencil and paper and draw down the UIs you will need.

Once you have that, build them.

Once you have them built, you will have what you need to store and load their properties. There you can decide whether JSON or a database will be better suited.

Don't try to run before knowing how to walk.

StudentScripter

@SGaist I actually have some of them built already. Thats why i was asking what would be better/ if my approach with the database would be viable. :)
Have a nice easter.