Geekly Articles each Day
📜 ⬆️ ⬇️

Automating the exchange of Qt form data with a SQL database

This article describes a solution for performing routine procedures for filling and saving form data to / from an SQL database. The code is complicated. To understand it, you need to know the Qt framework in terms of QtGui, QtSql. And at least medium C ++.




Story


Once I had to work on a project written in Qt. Backend was the most common SQL database. The app resembled an address book. But there were about a hundred of all kinds of Qt forms and dialogs. All these forms were served by simple C ++ code. Which simply takes data from the database, simply pushes them along simple fields. And then, when you close the form, it simply saves to the base.


For example:
') 
Form::Form(QObject* parent) : QWidget(parent) { QSqlQuery query; query.prepare( "SELECT firstname " // +    " FROM persons WHERE id = ?" ); const bool right = query.exec() && query.first(); Q_ASSERT( right ); //   . if( right ) { ui->firstnameEdit->setText( query.valie( 0 ).toString() ); /*     */ } } ~Form::Form() { QSqlQuery query; query.prepare( "UPDATE persons SET firstname = :firstname " // +    " WHERE id = :id" ); query.bindValie( ":firstname", ui->firstnameEdit->text() ); /*    */ const bool right = query.exec(); Q_ASSERT( right ); //    . }

Everything was good for a while. But suspicions began to appear that something was wrong here. If only because the 'firstname' in the queries was mentioned two times. At first glance, nothing terrible. The second 'firstname' could be put into a static variable. But all this was not that. It seems the code works. But something here must be radically changed.


And so, they entrusted to me the creation of the next forms, with the next bunch of fields. Since the sadness of the code above has accumulated a lot. I decided to embody my wildest dreams and desires.

Dreams:

In general, everything had to be polished in the best traditions of the PLO. At the same time, I had no idea how it would be implemented. This code was received. Of course now this code brings me no less sadness than the example above. But he worked. He worked so well that he managed to forget how he worked. And I worked with this code like this. Made a form with all the input fields on it. And I added to each field a property in which the column name was stored in the database where the data from this field was stored. Then, in the designer of the form class, wrote magic lines like setupForm ("persons"); and fillForm (dbID); Those. figuratively spoke to the form “set on using the 'persons' table” and “fill it with data from the line stored under the key from the dbID variable”. The form did what they say. The rest were already problems of the base class, where these methods were implemented.


As you can guess, the implementation consisted of these two lines and the common code generated by QtCreator. The form with a hundred fields contained even less code in the implementation than in the example above. But sometimes you had to call fillForm () again. In order to fill the fields from the associated SQL tables. Type fillForm (anotherRecordId, "address").


Since my production code is available only to initiates. I had to write this decision again. And since over time we all gain experience. It turned out much better than the original. All code was issued as an application - address book. And flooded on github . Further, there will be a verbose explanation of how it works and works.



Test solution


For the test, you must have QtCreator with Qt not lower than 5.0. Personally, I built a project with Qt 5.5.0 using the gcc 5.3.1 compiler. Although the project will build and even work on Qt 4.8.1. However, some "special effects" make the program there is not very friendly.


Initial project setup:
  1. Download the git clone project github.com/stanislav888/AddressBook.git
  2. Change the current directory "cd AddressBook"
  3. Initialize the git submodule init submodule
  4. You load the submodule code into the git submodule update project
  5. Open and collect project
  6. Run the program
  7. If all is well, a window for selecting / creating a database file will appear. You can see what kind of program. To fill in the test data there is a button "Fill test data"
  8. Delete the created database with the “Delete DB file” button

Then you add the fields to the interface and the database:

  1. In AddressBookMainWindow :: createDb (), add any new column to the creation of the “persons” table (PERSONS_TABLE_NAME) . Something like '<< «middlename TEXT»' only with its own column name.
  2. Open the addressbookmainwindow.ui file in the form editor.
  3. Add a widget there to edit the new field. For starters, let it be QLineEdit.
  4. In the new widget, create a property with the name "c" (column), the value-name of the new column. Created in p.1
  5. Run the program. You should again be asked where to store the database file. If not. Delete the database and run the program again.
  6. See this field. Saving and filling the data there will be configured automatically. For the test, you can change the field and change the selection in the table on the left. To another line and back
  7. The following example. Adding a field from a related table.
    Add something like '<< "postcode TEXT"' to the creation of the "address" table (ADDRESS_TABLE_NAME) ibid.
  8. Then add again QLineEdit to the form addressdialog.ui property with the name "c" again with the value - the name of the column that you added in the previous step
  9. Also delete the file with the database and run the program. Specify the name of the new base file
  10. On the main form, click on “Choose address” and see the dialogue with your new QLineEdit . It should also be stored in the database as other fields.
  11. Fill in any address with your new field and select it (“Select address”)
  12. Close the program
  13. Open addressbookmainwindow.ui to edit it and absolutely add the same field with the same property to this file.
  14. Additionally, add the property "t" (table) with the value "address"
  15. Next, it was necessary to add a fill in the data from the "address" table in this form. In the void function, AddressBookMainWindow :: updateForm () . There is already such a code “m_widgetHelpers.fillForm (addressId, ADDRESS_TABLE_NAME);”
  16. We start, we check. The new field must be filled in and saved in both forms.

After successful experiments with QLineEdit, you can try any other widgets like QSpinBox, QDateEdit , etc. All in the same sequence. Unsupported widgets will display mountains of asserts.


Code review


Project files


File Link


I will say right away. I did not want to make this program for use by ordinary users. I did code that could be the basis of other projects. Therefore, sometimes non-ideal solutions can be used there. Just because it would maintain the versatility of WidgetHelpers and similar classes.



Project Classes


Widgethelpers


It contains all the "magic" functionality.


A good example of filling a form with data from several AddressBookMainWindow :: updateForm () tables. Such code will work correctly after calling WidgetHelpers :: setupForm ()



AddressBookMainWindow


The main window of the program. Starts looking for a base. If not found, asks the user where the file with the base. If you specify a file that does not exist, it creates a database and writes the path to the settings. It contains a table with contacts of the address book on the left and details for each entry on the right. The table uses QSqlTableModel as the data source. The form also takes data directly from the database through the WidgetHelpers functionality, saves the same. QSqlTableModel can save data to the database. It is strange that on the form separate data storage for the table and simple fields. QSqlTableModel sometimes works with errors. Then, it has a bit limited functionality. Therefore, you can spit and use QSqlQueryModel. Which already saves nothing, but it has no restrictions. In this case, the table will be read only. And the functional of saving simple fields in the right part will be needed. For those who really want to save via QSqlTableModel (m_personsModelPtr) . WidgetHelpers (m_widgetHelpers) throws out a dataChanged signal (QString tableName, QString columnName, QVariant id, QVariant value); Although there is also a need for logic that prevents saving in WidgetHelpers :: saveDataSlot () . It is not implemented as unnecessary. Also on the form there is another feature. Combobox ui-> countryCombo. It is only needed to show how to save and fill in data from linked SQL tables. Here it is the table “country” (COUNTRY_TABLE_NAME) linked through “address” (ADDRESS_TABLE_NAME) . Usually it is hidden from prying eyes. changing the country outside the rest of the address will be very strange. Who wants to open and experiment (ui-> countryCombo-> hide ();) .


Conceptually, this window (AddressBookMainWindow) should inherit from WidgetHelpers. But in fact, Qt developers have limited the standard C ++. And left a lot of rake for those wishing to use all the features of the language. Therefore, aggregation is applied here and further, so as not to confuse the already complex code. If you see the flaws of this approach. So you must see the way out in each case. If you do not see anything wrong. So while thinking about it is not worth it. The code works and that's enough.



AddressDialog


Dialog select address from the list. Works like AddressBookMainWindow. But he did not make absolutely all such code for the left table in WidgetHelpers. Tables are not yet our goal. But working with input widgets is also done.



How values ​​are set in widgets


As already noticed inquiring minds. WidgetHelpers has two interesting functions: static void setWidgetValie (const QVariant & valie, QObject * const inputBox); and static QVariant getWidgetValie (const QObject * const sourceBox); they take values ​​from any widget and write the value to any widget where it makes sense. In the case of simple QLineEdit, QSpinBox widgets , the solution from Qt is used. These are the functions "setters" and "getters" designated as "USER" . For example, in the file “qlineedit.h” there is a line “Q_PROPERTY (QString text READ text WRITE setText NOTIFY textChanged USER true)” There is even documentation of what this spell means.


In addition to simple cases there is, for example, the case of QComboBox. "Q_PROPERTY (QString currentText READ currentText WRITE setCurrentText NOTIFY currentTextChanged USER true)" . At first glance, there is nothing wrong there. But since combo boxes, as a rule transfers from DB. It would be more convenient for us to set the values ​​by the secondary key from the database, and not by the string visible to the user. Therefore, the logic here is changed. The value is set by key from the database. stored in a UserRole combo box model.


Then there is the case with a group of QRadioButton. Such forms can also be a listing from the database. And too lazy to install these switches by hand. Those. you must select the switch whose value is stored in the database. For this there is a property "v" (VALUE_PROP) . Contains the value stored in the database. It is configured very interesting. Logically related QRadioButtons must have a QButtonGroup assigned. But COLUMN_NAME_PROP, TABLE_NAME_PROP are set at any one QRadioButton. Just for the convenience of the developer. The code itself will figure out what to do with the group. VALUE_PROP are set for all switches different.


In AddressBookMainWindow , probably the most complicated case of a group of switches is implemented. To select the sex of the contact. A fake QRadioButton was added there , in case the NULL is stored in the database.



How to show the country in two different ways


Now, for better mastering, let us analyze the most terrible and confusing example. The form addressbookmainwindow.ui has a QComboBox ui-> countryCombo . To see it, you need to comment out ui-> countryCombo-> hide (); To fill it with data from the database. It is necessary to fill in the fields associated with the table "address". Then there is ui-> countryLabel . For it you need to fill in the fields associated with the table "country".


This is done like this:
 void AddressBookMainWindow::updateForm() { //    m_widgetHelpers.fillForm( id ); const QVariant addressId = m_widgetHelpers.getFieldValie( PERSONS_TABLE_NAME, ADDRESS_FK_COL_NAME ); setAddress( addressId ); m_widgetHelpers.fillForm( addressId, ADDRESS_TABLE_NAME ); const QVariant countryId( m_widgetHelpers.getFieldValie( ADDRESS_TABLE_NAME, COUNTRY_FK_COL_NAME ) ); m_widgetHelpers.fillForm( countryId, COUNTRY_TABLE_NAME ); }

  1. At first there is a call m_widgetHelpers.setupForm (this, PERSONS_TABLE_NAME); in the constructor.
  2. The first line is m_widgetHelpers.fillForm (id); fills the form with the default table data ( defaultTableName in setupForm () ). In our case, this is “persons” (PERSONS_TABLE_NAME). The same function adds (or replaces) the desired SQL string (QSqlRecord) to the cache ( m_tablesRecords ).
  3. Next, m_widgetHelpers.getFieldValie () retrieves the necessary “foreign key” from the cache to write to the “address” table (ADDRESS_TABLE_NAME).
  4. The resulting “addressId” is used in setAddress () . What does not matter yet.
  5. Then in m_widgetHelpers.fillForm (addressId, ADDRESS_TABLE_NAME); . This call fills all widgets where the property “t” is explicitly specified equal to the variable ADDRESS_TABLE_NAME Including ui-> countryCombo . The same call loads the row from the “address” table into the cache.
  6. Further, the “foreign key” (countryId) is also retrieved to the entry in the “country” table.
  7. And m_widgetHelpers.fillForm (countryId, COUNTRY_TABLE_NAME) fills in all the widgets where the property “t” is COUNTRY_TABLE_NAME (“country”). Those. ui-> countryLabel

The difference here is that the combo box itself works with the “foreign key”. The column name is “countryid” (COUNTRY_FK_COL_NAME) from the “address” table (ADDRESS_TABLE_NAME). Whereas for ui-> countryLabel, you need to add a line from the “country” table to the cache (which was done implicitly). And only then fill the widget with data from the cache. It turns out that fillForm (countryId, COUNTRY_TABLE_NAME); only needed for ui-> countryLabel . Of course, the two fields leading out the country are not needed in the neighborhood. Therefore, one of them was hidden.


ui-> countryLabel is filled with property “c” and “t”. These properties are not in the widget addressbookmainwindow.ui . They are set programmatically.

  ui->countryLabel->setProperty( WidgetHelpers::COLUMN_NAME_PROP, COUNTRY_NAME_COLUMN ); ui->countryLabel->setProperty( WidgetHelpers::TABLE_NAME_PROP, COUNTRY_TABLE_NAME );

There is no sense in this. Just an example that you can also fill in the property. You can comment out these lines and define them through the editor for the forms. This property setting makes sense only up to setupForm () .



Profit


Immediately after developing all this. Suddenly, new opportunities began to appear. One of them is the output of additional debugging information about the field in the tooltips to the widget. When you hover over the widget, something like “t = persons, c = skype, id = 3” appears. What does the table, column and primary key of the row mean by which this field was filled in? In this case, the original tips, if they are static. Do not go anywhere. Dynamic is also not a problem to fasten. I think it will be a “hand-made monument” to a developer for testers.



QDataWidgetMapper


Of course, this is my solution, it seems a bit cycling compared to using a solution based on QDataWidgetMapper. I admit trying to use this class. But he simply did not want to work. You can of course dig into the sources of Qt and find out what is there. In this case, the solution could be a patch to Qt or its own implementation of the mapper. That did not suit me at all. I just wanted to set the column names for the widget in QtDesigner. This is very visual and does not clutter up the form implementation file. QDataWidgetMapper is not exactly that. It is necessary to link the column in the model and the widget. At the same time I am obliged to use unreliable QSqlTableModel model . It works for me with SQLite. But in Qt, not one SQLite. It is not clear what tricks these models will throw out with other bases. And I wanted to make a universal solution. Then, QDataWidgetMapper does not want to support QComboBox and QRadioButton , as I do.





Conclusion


In my opinion, the bike was very successful. Write comments, kick me hard. After all, this is my first article here. If you like this topic. There is an idea to write about models (which are QAbstractItemModel ).

Source: https://habr.com/ru/post/328670/

More articles:


All Articles