UPD
This blog was launched thanks to
advix . This person gave an invite for fasting in the sandbox, for which I am sincerely grateful.
Good afternoon, dear habrasoobschestvu!
I want to submit to your court a tiny Qt library written to simplify the programming of database applications. Using it, I made one database to order, and although it was just a term paper, she helped me hone and think through many points. Now I am writing another database, already real, for serious organization. Little by little, I bring new features to the library. You look, sometime something good will turn out.
Preamble
Since the summer of 2009, I have been actively studying Qt. And you know, really happy to program in this environment. She solved all my major problems. For example, I did not know how to build the interface of the program, I was especially annoyed that each control in the same Builder needed to be leveled, dragged, adjusted in size and position. With Qt, you can forget about it - and engage exclusively in aesthetics without any routine. In addition, I like STL for its cunning approach (although I still don’t really know it), and in Qt it is, as well as its own containers, use it with pleasure. And “Kyut” is a very well-thought-out library, all such OOPs are high-tech and technologically advanced. And I really respect and respect the PLO, as well as the design patterns ...
At the end of the summer I get an order for the database. The question of what to write is not worth it. Of course, Qt! Practice will help to learn what is still hidden. I start working on the application. It seems that the beautiful forms are obtained, the code is convenient to write, everything is there ... And I should be happy, but here it turns out noodles from SQL-C ++ code. Mistaken, accompanied by a difficult, seemingly miserable ... How then do people see in their eyes and call themselves a programmer?
So was born the projector. Yes, a small project designed to save from all this disgrace. Fortunately, the path is well-trodden, and for all programming languages, for all platforms there are already similar things. Well, okay, that is. I want my own, especially under Qt I did not find anything sensible.
')
QST: QsT SQL Tools
This is a toolkit, or rather, a library, or even better, a set of classes, which saves the programmer from “SQL noodles” in the code. Naturally, using query generation, but not simple, but through special DFD descriptors, which gives many, many useful buns. Somehow: we refer to the fields of the database table by name, extract any data, have different DFD descriptors for different requests, work with representation models, encapsulate all of this into handler classes, and they still know a lot of things, they are inherited from AbstractModelHanlder ...
But - in order.
Dfd
I developed a concept called
DFD :
“Declarative Field Descriptor” . (I want to add L [anguage] to it, but does it pull on the language? ..) The concept of generating simple SQL queries from descriptors. The easiest way to explain it is by example. We want, then we create such a query:
SELECT [ID], LastName, FirstName, ParentName, SerialNumber, Number, DocType_ID
FROM tPersonalDocuments
WHERE
[ID] > 30
AND
DocType = 1
And it will look like this:
SqlBatch batch;
batch.addSource("tPersonalDocuments");
// 1
batch << SqlField("[ID]", fv_invisible, fr_id)
<< SqlField("LastName", fv_visible, fr_none, "", 120)
<< SqlField("FirstName", fv_visible, fr_none, "", 120)
<< SqlField("ParentName", fv_visible, fr_none, "", 120)
<< SqlField("SerialNumber", fv_visible, fr_none, "", 45)
<< SqlField("Number", fv_visible, fr_none, "", 70)
<< SqlField("DocType_ID", fv_invisible)
// 2
<< SqlField("[ID]", SqlValue(30, fo_greater), fp_where)
<< SqlField("DocType_ID", SqlValue(1), fp_where);
// – . 0 .
SqlQueryDescriptor queryDescriptor(batch, sql_select, 0);
As you can see, the list for
FROM is loaded using
SqlBatch :: addSource () , and then fields, fields, fields ... Everything is simple. The
SqlField class is loaded
into SqlBatch , which (section 1) is responsible for a specific field, the role of the field, its presence / absence in the Qt's view, the heading for the column in view and the width of this column. For example, the ID field is key, and this is an important point, and we give it the role of a key:
fr_id . And it should not be displayed in the view, i.e., we want to hide from the user what the key of the entry is. No, if we don’t want to hide, so please, no one will interfere ... In section 2 we describe filters for
WHERE , and to distinguish them from others, there is the
fp_where parameter belonging to
enum SqlFieldPurpose . For the filter, you must specify the field and the value - a special class
SqlValue . In the example, we see integer values; here:
batch << SqlField("[ID]", SqlValue(30, fo_greater), fp_where);
specified comparison functor "more", and here:
batch << SqlField("DocType_ID", SqlValue(1), fp_where);
the functor is not specified, so the default for integer is
fo_equal .
Yes, yes, everything is good, but what about the other types, especially with the string and dates? Everything is possible! These snatches of SQL query:
FirstName LIKE '%'
AND
Birthday BETWEEN convert(datetime '20.01.2009', 104) AND
convert(datetime '15.05.2009', 104)
with a flick of the wrist turn into elegant lines:
batch << SqlField("FirstName", SqlValue("", fo_like, fb_right), fp_where)
<< SqlField("Birthday", SqlValue(QDate(2009, 1, 20)), SqlValue(QDate(2009, 5, 15)));
(The date is automatically converted according to the pattern specified in the constant.)
Recall that in addition to the
WHERE clause in a simple
SELECT , there can also be
ORDER BY and
GROUP BY . The
SqlField class has constructors for these cases:
SqlField("Birthday", fp_order_by)
SqlField("Birthday", fp_group_by)
(However, for
GROUP BY, we should indicate aggregating functions and only those fields that participate in them - but we will leave it on the programmer’s conscience if he has a valid SQL query or not.)
The query itself is generated by the
SqlGen and
SqlQueryComposer classes . In fact, it generates only
SqlQueryComposer , and
SqlGen determines the order of the fields and provides a high-level interface for generation. (By the way, if you wish, you can rewrite the
SqlQueryComposer class under another SQL dialect.) Here’s how you can get a
SELECT query, passing it our
SqlBatch class filled with fields:
SqlGen gen;
QString selectQuery = gen.query(batch, sql_select);
And if we write something else instead of
sql_select , for example,
sql_update , the generator will try to generate an
UPDATE request, taking only those fields that are allowed for it. What kind? Well, those with the
SqlFieldPurpose parameter instead of
| along with others. Here is an example of such a field:
SqlField("Age", SqlValue(10), fp_where | fp_update | fp_insert)
Accordingly, with
sql_update there will be a picture like this: “SET Age = 10”, with
sql_insert something like this:
INSERT INTO … (Age)
VALUES (10)
and for all other cases - just a filter for
WHERE :
WHERE
Age = 10
The concept of DFD descriptors is starting to clear up, right? And it is true that it is very similar to other approaches. I was not eager to originate ...
But this is all bullshit. The question is - how to use our garbage. Here, in fact, where there are amenities for which you read everything on top.
AbstractModelHandler
In my library there is a certain abstract class
AbstractModelHandler . It contains a lot of good things, and nobody and nothing can work with DFD descriptors better than it. Suppose a programmer wants to encapsulate work with the table of branches of arms
tArmyTypes . To do this, it creates the heir class
h_ArmyTypesHandler :
const int ARMY_TYPES_QUERY = 7575;
class h_ArmyTypesHandler : public AbstractModelHandler
{
public:
h_ArmyTypesHandler();
private:
virtual SqlQueryDescriptor _selector(const SqlQueryModelTypes &modelType = mt_plain, const int &queryNumber = 0) const;
virtual SqlQueryDescriptor _inserter(const int &queryNumber = 0) const;
virtual SqlQueryDescriptor _updater(const int &queryNumber = 0) const;
virtual SqlQueryDescriptor _deleter(const int &queryNumber = 0) const;
virtual SqlQueryDescriptor _executor(const int &queryNumber = 0) const;
};
The virtual functions
_selector (), _inserter (), updater (), _deleter () and
_executor () are inherited from the base class. It is they who contain DFD descriptors and issue them at the request of other
AbstractModelHandler 's functions. For example, here is a typical redefinition of the
_selector () function:
SqlQueryDescriptor h_ArmyTypesHandler::_selector(const SqlQueryModelTypes &modelType, const int &queryNumber) const
{
SqlBatch batch;
batch.addSource("tArmyTypes");
if (queryNumber == ARMY_TYPES_QUERY)
{
batch << SqlField("ID", fv_invisible, fr_id)
<< SqlField("ShortName", fv_visible, fr_none, "", 90)
<< SqlField("Name", fv_visible, fr_none, " ", 100)
<< SqlField("ID", value(ID_VALUE), fp_where)
<< SqlField("Name", value("Name"), fp_where)
<< SqlField("ShortName", value("ShortName"), fp_where);
}
else
if (queryNumber == LAST_ID)
{
batch << SqlField("max(ID)", fv_visible, fr_none);
}
else
{
Q_ASSERT(false);
}
return SqlQueryDescriptor(batch, sql_select, queryNumber);
}
We see here something new: a certain
value function
() . Perhaps someone has already guessed that it returns by name the previously written
SqlValue () value, for example, passed to
h_ArmyTypesHandler somewhere in the form class:
h_ArmyTypesHandler th;
th.setValue("Name", SqlValue("__", fo_not_equal, fb_none));
That is, we save the value by the
setValue () function under the desired name, and then in the descriptor we retrieve it using the
value () function (both from the
AbstractModelHandler class). Now, when generating the query, we will have parameterized DFD descriptors.
The penultimate line of the example sheds light on the third parameter of the constructor of the
SqlQueryDescriptor class: this is the request number. With the same
_selector () function, you can generate different requests, at least two, at least ten: for each situation, your own request. And this is good. Once you need such fields, another time you need such; then you would like to call a query with aggregation functions, and when it’s just a query, not from that table at all. It is enough for you to determine the request number for each DFD descriptor, and voila!
A natural question arises how to generate and execute SQL queries in general. Well, for example, like this:
…
const int ARMY_TYPE_INSERT_QUERY = 5;
const int ARMY_TYPE_UPDATE_QUERY = 68;
…
h_ArmyTypesHandler th;
th.setValue("Name", SqlValue("__", fo_not_equal, fb_none));
th.Insert(ARMY_TYPE_INSERT_QUERY);
th.setValue(ID_VALUE, SqlValue(10));
th.Update(ARMY_TYPE_UPDATE_QUERY);
th.Delete(4);
At the same time, the corresponding functions are
called :
_inserter () ,
_updater () and
_deleter () , where the descriptors lie. Then the abstract parent of the handler generates SQL and executes it with Qt. It's simple.
However, more often you have to work with
SELECT queries, which you cannot fulfill into emptiness, because you need to see the result of the selection! We wish, for example, that the TableView displays a table of arms of service. No problems!
... But first, a retreat. Qt implements one type of
MVC pattern , which requires a data model for
QTableView . Those are in Qt, and it is possible and to make the. I admit honestly, I am not strong in programming my models, but for the library I wrote the simplest tree model, which I lacked so much -
SqlTreeModel . Yes, in Qt there is no tree model that meets my requirements, or I just don't know much. Anyway, in QST there are two models:
SqlTreeModel and
SqlQueryModel , the latter only slightly differs from
QSqlQueryModel , inheriting from it. Models are actively used in the
AbstractModelHandler class.
Now we need to delve into another concept of the QST library: data source. This is, in fact, a bunch of DFD - Model - View. Each data source is named, the name is set by the programmer. To display in our TableView the table of the types of troops, we create a data source in the form class:
…
h_ArmyTypesHandler _handler;
SqlQueryModel _model;
…
void f_ArmyTypesForm::loadArmyTypes()
{
_handler.reloadSource(ARMY_TYPES_SOURCE, ARMY_TYPES_QUERY, &_model);
_handler.setTableView(ARMY_TYPES_SOURCE, ui->TableView);
}
And that's all. Columns with specified widths and headers will appear in the TableView table. And these parameters are set, therefore, in the function
h_ArmyTypesHandler :: _ selector () . I will list the columns that appeared: “ShortName” with the name “Abbreviation” and 90 wide; “Name” with the name “Kind of Troops” and a width of 100. But the “ID” field will not be displayed because it has fv_invisible ... In other words, the
reloadSource () and
setTableView () functions will do the entire programmer’s routine for formatting the table.
ARMY_TYPES_SOURCE is the textual name of the new data source. However, if you call
loadArmyTypes () again, the source will be “overloaded”: the
_selector () function will be called again, a
SELECT query will be generated again, and the data model will delete old lines and take new ones from the database. When calling the following functions, we get two different sets of lines:
void f_ArmyTypesForm::someFunc1()
{
_handler.setValue(ID_VALUE, SqlValue(10));
loadArmyTypes();
}
void f_ArmyTypesForm::someFunc2()
{
_handler.setValue(ID_VALUE, SqlValue(111, fo_less));
loadArmyTypes();
}
Yeah, yeah, that's not enough for me.
AbstractModelHandler allows you to create multiple data sources (otherwise why would we give them names?!.). Suppose, in addition to the
TableView , we also have a
TreeView and a
ComboBox on the form, where we want to output the same. No problem! We write something like the following:
…
h_ArmyTypesHandler _handler;
SqlQueryModel _model;
SqlQueryModel _modelPlainForTreeView;
SqlQueryModel _modelForComboBox;
…
void f_ArmyTypesForm::loadArmyTypes()
{
_handler.reloadSource(ARMY_TYPES_SOURCE, ARMY_TYPES_QUERY, &_model);
_handler.setTableView(ARMY_TYPES_SOURCE, ui->TableView);
_handler.reloadSource(TREE_VIEW_SOURCE, ARMY_TYPES_QUERY, &_modelPlainForTreeView);
_handler.setTreeView(TREE_VIEW_SOURCE, ui->TreeView);
_handler.reloadSource(COMBO_BOX_SOURCE, ARMY_TYPES_QUERY, &_modelForComboBox);
_handler.setComboBox(COMBO_BOX_SOURCE, ui->ComboBox);
}
And so what, you ask. Like what? Are different models transmitted? Various. Therefore, there are also three data sources. Only the handler is shared, the queryNumber is the same - ARMY_TYPES_QUERY, therefore, the requests are generated the same. But we can do magic things! For example, such:
// ComboBox:
QVariant id = _handler.keyValueOfCurrent(ARMY_TYPES_SOURCE, ui->ComboBox);
// TreeView:
QVariant id2 = _handler.keyValueOfView(TREE_VIEW_SOURCE);
// Name ID == id2:
h_ArmyTypesHandler th;
th.setValue(ID_VALUE, SqlValue(id2));
QVariant name = th.SelectToValue("Name", ARMY_TYPES_QUERY);
// TableView ( ):
_handler.DeleteCurrent(ARMY_TYPES_SOURCE, ui->TableView);
And even select the data list:
h_ArmyTypesHandler th;
th.setValue(ID_VALUE, SqlValue(id2));
QVariantMap valMap = th.SelectToMap(QStringList() << "Name" << "ShortName" << "ID", ARMY_TYPES_QUERY);
ui->LineEdit_Name->setText(valMap["Name"].toString());
And not a single SQL query in the code! Beauty!!!
In conclusion…
Of course I do not disclose many details. Even in such a small library they are more than enough. Unfortunately, a lot of
QST can not. Strongly lacking a normal tree model; the one that is, maybe with errors, and it also behaves strangely when interacting with
QTreeView signals: it crashes under certain conditions, getting into someone else's memory. I can not find a mistake. I even have an example with which I addressed people in two Qt forums to help me, but so far no one has said anything.
While there is no place in the DFD and more complex queries. Now I am thinking about how to add an OR condition to the
WHERE section. Perhaps you should think about
JOIN 's, which are very necessary. Not that it is impossible to circumvent these limitations with the available means - it is possible, the SQL language is quite multifaceted, - however, it is also necessary to develop.
Additionally, the library includes a class for working with database connections (
DBConnection ), as well as a class that sets the codec for the
tr () function (
Cyrillic ). Well, a self-made
TreeItem with borrowed ways to work with nodes. I honestly do not like the class. And
SqlTreeModel does not like it. I do not like when
new and
delete are scattered by code. If we are working with memory, then through the principle of
"Getting a resource is initializing .
" You look, and rewrite everything in the mind. Someday.
I would really like this development to help someone. And I would not mind community help in identifying errors, in suggestions for improvement. The
QST sources are in the SourceForge archive,
here . License is free, GPL v3 and LGPL v3. Additional information can be found in
this topic .
I will be glad to answer questions interested.
Respectfully,
Granin Alexander