![]() | ![]() | Libmergeant Reference Manual | ![]() |
---|
Every DML (Data Manipulation Language) query is described as a single MgQuery object. The object is then specialized to represent all the possible DML queries:
selection queries: of type MG_QUERY_TYPE_SELECT
modification queries: of type MG_QUERY_TYPE_INSERT, MG_QUERY_TYPE_UPDATE and MG_QUERY_TYPE_DELETE
aggregation queries: of type MG_QUERY_TYPE_UNION, MG_QUERY_TYPE_INTERSECT and MG_QUERY_TYPE_EXCEPT
direct SQL queries: when they can be interpreted, their type is set to one of the above types, and when parsing is not possible (either because the SQL statement is wrong or because the SQL statement uses some specific extensions), the type is set to MG_QUERY_TYPE_NON_PARSED_SQL
The general structure of a query is the following one:
A list of targets (as MgTarget objects). Each MgTarget object represents an entity (= an object which implements the MgEntity interface, for example a database table or another query); A single entity can be represented several times through different MgTarget objects. In the example above there are two MgTarget objects representing the "person" and "title" entities.
If a target that represents a MgQuery object is added to a query, then the represented query MUST BE a sub query of the query the target is added to. In the following query: ""SELECT firstname, lastname FROM (SELECT * FROM person WHERE type=1)"", there is one MgTarget object which represents the ""SELECT * FROM person WHERE type=1"" sub query and that sub query must be declared as a sub query of the complete query.
A list of joins (as MgJoin objects) between the targets. This is usefull only for selection queries where a join represents a SQL join between entities to avoid cross products. In the example above there is one MgJoin object to represent the "INNER JOIN" between the two MgTarget objects representing the "person" and "title" entities.
A list of fields (as MgQfield objects). Query fields can be of several different types to represent all the possibilities of data manipulation. In the example query, there are three query fields, all representing an entity's field: "firstname", "lastname" and "title".
Query fields can be visible or invisible (in which case they are only there to be used by other query fields, but they do not participate directly in the query).
Depending on their type, some query fields have a "value_provider" property which can point to another query field. In this case, when the query is executed, the query field which is pointed at is used to provide a value. This is particularly the case of modification queries: a query such as ""INSERT INTO persons (firstname, lastname) VALUES ('name1', 'name2')"", the query fields "firstname" and "lastname" have their "value_provider" property pointing respectively to the MgQfValue query fields with the values "name1" and "name2".
A list of sub queries (also as MgQuery objects).
A condition on the application of the query (as a MgCondition object). A condition object can contain several sub conditions.
Some other attributes describing the grouping and ordering.
The requirements in terms of query structuration for each type of query is explained in the following sections. Note that these requirements are only really tested when the query is rendered into an executable statement, where errors may be returned.
The MgGraphviz object can produce .dot files out of a MgQuery object, which can the processed using the GraphViz tool. This tool has been used to produce the internal represantations of queries in this documentation.
As an example, the following query "SELECT t1.id, t1.name, t2.name, t3.country, t3.city FROM customers AS t1 INNER JOIN salesrep AS t2 ON (t1.default_served_by=t2.id) LEFT JOIN locations AS t3 ON (t1.country=t3.country AND t1.city=t3.city) WHERE t1.id=12" has the internal structure as represented in the following figure.
Internal structure of the "SELECT t1.id, t1.name, t2.name, t3.country, t3.city FROM customers AS t1 INNER JOIN salesrep AS t2 ON (t1.default_served_by=t2.id) LEFT JOIN locations AS t3 ON (t1.country=t3.country AND t1.city=t3.city) WHERE t1.id=12" query.
Here is how to read the diagram:
Each MgTarget object is represented by an orange box (here "customers", "salesrep" and "locations").
Each MgJoin object is represented by an arc between the MgTarget objects it links. Here there is one join between "customers" and "salesrep" (INNER join), and one between "customers" and "locations" (LEFT join, note the empty circle on the side of the target where NULL values can be part of the join).
Each MgQfield object is represented by a blue box devided into two rows: the top one is the name of the field, and the bottom one is devided into 4 boxes which in order from the left display the real type of field ("Field" fo a MgQfField, "Value" for a MgQfValue, etc), if the field is visible (a "V" appears), if the field is internal (marked with a "I") and if the field is a query parameter (a "P").
Also, each blue box representing a MgQfField object has an arc to the MgTarget in which it belongs.
The MgCondition object, if present, is represented by a yellow box labeled after the SQL representation of the condition, and with arcs to all the MgQfield objects it uses.
Any query can require some parameters to be given values before they can be executed. That is the case for example for a query like "SELECT name FROM person WHERE age = <value>" where "<value>" is a parameter which value must be provided before the query can be executed. A parameter can only be represented as such in a query by a MgQfValue query field, and providing a value for a parameter to a query is done using a MgParameter object.
A query QU1 can specify that a given parameter QU1:QF1 have its value restrained by the values of another "SELECT" query (QU2:QF2). In this case the MgQfValue which is a parameter (QU1:QF1) has its "value_prov" property set to point to the QU2:QF1 field In this case, the QU2 query can be managed by the QU1 query (use the mg_query_add_param_source() and similar methods).
The list of parameters required to run a query is obtained using the mg_entity_get_parameters() method. This function returns a raw list of parameters. Usually however, it is better to use a MgContext object which stores the parameters, and organises the parameters in a convenient. The function to be used to obtain a MgContext object is mg_entity_get_exec_context(). them
A selection query can have all the possible structural elements, with the following restrictions:
All the sub queries must also be selection queries
Modification queries have the following structural restrictions:
There must be one and only one target object which represents the entity to be modified. Also the represented entity mus be modifiable (ie it cannot be another query or a database view for example)
There cannot be any join
All the visible query fields must be MgQfField fields
If the query is an insertion query, there must be at most one sub query, and in this case the sub query must be a selection query (and the insertion query will be interpreted as "INSERT INTO table SELECT ..."). As a consequence the sub query must render the same number of fields as the query itself.
Also, if there is no sub query, then all the fields which are value providers MUST be MgQfValue fields.
Insertion queries can't have any associated condition.
If the query is a deletion query, then there can't be any visible field at all (some hidden ones can exist to take part in a condition.
If the query is an update query, then all the fields which are value providers MUST NOT be MgQfAll fields.
As it is sometimes easier to define a query usong an SQL statement, a #MgQuery object can be defined from any SQL 1992 statement, using the mg_query_set_sql_text() method. The SQL passed as argument must be a single SQL statement (that is not several statements separated by a colon).
Some extensions are provided to be able to define variables from within SQL. The way of doing this is to use the following syntax right after a constant value in the SQL statement: [:attribute="value", ...]. For example the following SQL:
SELECT 10[:type="int2" :descr="this is a good' description" :isparam="TRUE" :nullok="TRUE"], id FROM customers
creates a query structure similar to "SELECT 10, t1.id FROM customers AS t1", but also specifies that the "10" value is in fact to be interpreted as of type "int2", with a description, that it is a variable for which the NULL value is acceptable.
So, when the query will be executed, the real executed SQL statement will be "SELECT 10, t1.id FROM customers AS t1" if the "10" value has not been replaced by another value. The replacement of that value will be possible through the usage of a #MgParameter object (obtained through a MgContext object).
The available extension "tags" are:
":name" fixes the name of the value's name
":descr" fixes the name of the value's description
":type" fixes the MgServerDataType type of the value
":isparam" tells if value is a variable (a parameter) or not
":nullok" tells if value can have a NULL value
<<< MgDbConstraint | MgQuery >>> |