Data manipulation (DML) queries

General words about queries
General structure of a query
Graphical conventions to represent queries
Query's parameters
Structural requirements for each type of query
Direct SQL queries
GnomeDbQuery - Represents any kind of DML query
GnomeDbTarget - Represents a target entity within a query.
Query fields
GnomeDbJoin - Represents a join between two different entities
GnomeDbCondition - Represents a condition within a query

General words about queries

Every DML (Data Manipulation Language) query is described as a single GnomeDbQuery object. The object is then specialized to represent all the possible DML queries:

  • selection queries: of type GNOME_DB_QUERY_TYPE_SELECT

  • modification queries: of type GNOME_DB_QUERY_TYPE_INSERT, GNOME_DB_QUERY_TYPE_UPDATE and GNOME_DB_QUERY_TYPE_DELETE

  • aggregation queries: of type GNOME_DB_QUERY_TYPE_UNION, GNOME_DB_QUERY_TYPE_INTERSECT and GNOME_DB_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 GNOME_DB_QUERY_TYPE_NON_PARSED_SQL

General structure of a query

The general structure of a query is the following one:

  • A list of targets (as GnomeDbTarget objects). Each GnomeDbTarget object represents an entity (= an object which implements the GnomeDbEntity interface, for example a database table or another query); A single entity can be represented several times through different GnomeDbTarget objects. In the example above there are two GnomeDbTarget objects representing the "person" and "title" entities.

    If a target that represents a GnomeDbQuery 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 GnomeDbTarget 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 GnomeDbJoin 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 GnomeDbJoin object to represent the "INNER JOIN" between the two GnomeDbTarget objects representing the "person" and "title" entities.

  • A list of fields (as GnomeDbQfield 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 GnomeDbQfValue query fields with the values "name1" and "name2".

  • A list of sub queries (also as GnomeDbQuery objects).

  • A condition on the application of the query (as a GnomeDbCondition 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.

Graphical conventions to represent queries

The GnomeDbGraphviz object can produce .dot files out of a GnomeDbQuery 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.

Sample SELECT query

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 GnomeDbTarget object is represented by an orange box (here "customers", "salesrep" and "locations").

  • Each GnomeDbJoin object is represented by an arc between the GnomeDbTarget 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 GnomeDbQfield 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 GnomeDbQfField, "Value" for a GnomeDbQfValue, 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 GnomeDbQfField object has an arc to the GnomeDbTarget in which it belongs.

  • The GnomeDbCondition object, if present, is represented by a yellow box labeled after the SQL representation of the condition, and with arcs to all the GnomeDbQfield objects it uses.

Query's parameters

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 GnomeDbQfValue query field, and providing a value for a parameter to a query is done using a GnomeDbParameter 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 GnomeDbQfValue 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 gnome_db_query_add_param_source() and similar methods).

The list of parameters required to run a query is obtained using the gnome_db_entity_get_parameters() method. This function returns a raw list of parameters. Usually however, it is better to use a GnomeDbDataSet object which stores the parameters, and organises the parameters in a convenient. The function to be used to obtain a GnomeDbDataSet object is gnome_db_entity_get_exec_context(). them

Structural requirements for each type of query

Selection queries

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

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 GnomeDbQfField 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 GnomeDbQfValue 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 GnomeDbQfAll fields.

Aggregation queries

Aggregation queries have the following structural restrictions:

  • There cannot be any target or join

  • There cannot be any condition

  • All the sub queries must be selection queries and have the same number of selected fields. Also for EXCEPT queries, there can be only two sub queries.

Direct SQL queries

As it is sometimes easier to define a query usong an SQL statement, a #GnomeDbQuery object can be defined from any SQL 1992 statement, using the gnome_db_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 #GnomeDbParameter object (obtained through a GnomeDbDataSet 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 GnomeDbServerDataType type of the value

  • ":isparam" tells if value is a variable (a parameter) or not (the default is that it is a parameter)

  • ":nullok" tells if value can have a NULL value