QSql classes architecture



  • Hello.
    I am in the process of expirementing with Qt but i seem to have some issues regarding the software architecture of a database class.
    For my current program i have hundreds of queries, so i created a class for each table. These classes inherit from a base class called Database.

    Now, i'm not a very experience programmer, but i have around 30 tables to work with and i don't think is a good idea to have 30 classes just for the queries.
    What do you suggest i do ?
    (The solution that i need to find, must be avoiding the "QSqlDatabasePrivate::removeDatabase: connection 'FindItem' is still in use, all queries will cease to work." type of warnings).


  • Qt Champions 2016

    Hi and welcome
    It sounds like a big setup. Could you perhaps tell a bit about what the
    queries do and maybe show 1 example of "class for each table"
    and what the program does to all this data?
    This way people here can much better suggest a design.


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    To add to @mrjj, are all your tables in a different database ?



  • Hello,
    Thank you for your response !
    @SGaist No, everything is on the same database.

    @mrjj So, for every form (QT window) that needs to have access to the data, i have 2 classes. One to setup the interface/tables/lineEdits e.t.c and the other class contains the database stuff (db info, queries ).

    This is an example of how my setup works for adding an entry to the table :

    void Instruments::addInstrument(){
        QSqlQuery query(QSqlDatabase::database(connectionName));
    
        query.prepare("INSERT INTO " + databaseName + ".lab_instruments(id, serial_no, manufacturer, model, description, "
                      "comm_port, comm_baud_rate, comm_data_bits, comm_parity, comm_stop_bits, installation_date, "
                      "last_service, next_service) "
                      "VALUES(:id, :serial_no, :manufacturer, :model, :description, :comm_port, :comm_baud_rate, :comm_data_bits, "
                      ":comm_parity, :comm_stop_bits, :installation_date, :last_service, :next_service )");
    
        query.bindValue(":id", regNo);
        query.bindValue(":serial_no", serialNo);
        query.bindValue(":manufacturer", manufacturer);
        query.bindValue(":model", model);
        query.bindValue(":description", description);
        query.bindValue(":comm_port", commPort);
        query.bindValue(":comm_baud_rate", baudRate);
        query.bindValue(":comm_data_bits", dataBits);
        query.bindValue(":comm_parity", parity);
        query.bindValue(":comm_stop_bits", stopBits);
        query.bindValue(":installation_date", installDate);
        query.bindValue(":last_service", lastService);
        query.bindValue(":next_service", nextService);
    
        if(!query.exec()) { qDebug()<<query.lastError(); }
    
    }
    
    

    And this is the Qt method that handles the interface and calls this method :

    
    void InstrumentsScreen::on_btnAdd_clicked()
    {
        Instruments* instrument = new Instruments("addInstrument");
    
        //Extract data from text fields
        *instrument= updateValues(*instrument);
        //Update database
        instrument->connect();
        instrument->addInstrument();
        instrument->disconnect();
        QSqlDatabase::removeDatabase("addInstrument");
        initTable();
        on_btnClear_clicked();
    
    }
    
    Instruments InstrumentsScreen::updateValues(Instruments instrument){
    
        instrument.setRegNo(ui->txtRegNo->text());
        instrument.setSerialNo(ui->txtSerialNo->text());
        instrument.setManufacturer(ui->txtManufacturer->text());
        instrument.setModel(ui->txtModel->text());
        instrument.setDescription(ui->txtDescription->text());
        instrument.setCommPort(ui->txtCommPort->text());
        instrument.setBaudRate(ui->txtBaudRate->text());
        instrument.setDataBits(ui->txtDataBits->text());
        instrument.setParity(ui->cboxParity->currentText());
        instrument.setStopBits(ui->txtStopBits->text());
        instrument.setInstallationDate(ui->txtInstallDate->text());
        instrument.setLastService(ui->txtLastService->text());
        instrument.setNextService(ui->txtNextService->text());
    
    }
    

    So this is just for a button on one of the forms.

    P.S: The reason for having each query in a method is because i feel like is bad programming having queries thrown all over the place in an interface class.


  • Lifetime Qt Champion

    You should take a look a QDataWidgetMapper

    That will simplify your code.



  • @SGaist Wow ! that indeed will simplify my code ! From the first look, i can replace my set/get methods with these models right ?


  • Lifetime Qt Champion

    Yes, the Book Demonstration Example shows you how to use it.



  • @SGaist Thank you very much.



  • Do you think it will be a bad practice to use different class for each table ?
    Since each table has different columns and thus different queries.


  • Moderators

    How do you want to handle queries which affects more than one table with such approach?
    I would not use one class for each table, because then your design is affected by the number of tables in the database.
    What if you later add one table and remove two? You would need to write one new class and remove 2.
    I would use one class and implement methods to do specific tasks. This way in your code which uses this class you do not have to know how the data is stored (in which tables).



  • @jsulm I generaly use Inner join for queries affecting more than one table.
    When it comes to Inserting and Updating, i don't want the user to be able to add new data for an instrument for example, from a form about instrument methods.

    In the scenario you gave, i would still have to write a couple of methods for each of the tables and delete the unused method right ? I mean each table will still need methods for the queries.

    Also what about security ? I am not really sure, but isn't easier for someone to have access to an admin table data (when he shouldn't), when these data are stored in a class that he already has access in ?


  • Moderators

    I don't understand why you want to write something specific for each and every table?
    The methods I mentioned should have generic names like: void addSomething(const Something&).
    "Something" could be stored in one or even more tables, the caller does simply not care about such details.
    So, the interface should not reflect your database design.

    It is not clear to me what you mean with security? The interface should not provide access to data which should be hidden and it does not matter how the interface is designed (whether you use one class per table or not). And who is "someone"? A developer using your interface or a real application user?



  • I currently have a method to handle each query.
    So instead you suggest having something like this to be used for all the tables ?

    void addSomething(QString queryString){
             query.exec(queryString);
    

    And for SELECT queries i should use your solution with QDataWidgetMapper and QModel ?


  • Moderators

    What I suggest is: decouple your interface from the database design. You can have a method for one query, you can have a method which executes several queries,... SQL database is an implementation detail and should not influence the interface too much.
    The user of the interface should not care about databases and how they are designed. They just use the interface to do what ever needs to be done. Ideally it should be possible to replace your SQL database with, for example, files or a non SQL database without changing the interface, or to change the database design without changing the interface (or with minor changes).
    Example: if you use QPushButton you don't care how a button is implemented on Windows, MacOS, Linux,... For you it is just a button.



  • @jsulm I see ! It is clear what you suggest.
    I think indeed this is a better way to proceed .
    Thank you for your time !


  • Moderators

    You're welcome!


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.