## Data manipulation (DML) queries

### General words about queries

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

- selection queries: of type GDA_QUERY_TYPE_SELECT

- modification queries: of type GDA_QUERY_TYPE_INSERT, GDA_QUERY_TYPE_UPDATE and GDA_QUERY_TYPE_DELETE

- aggregation queries: of type GDA_QUERY_TYPE_UNION, GDA_QUERY_TYPE_INTERSECT and GDA_QUERY_TYPE_EXCEPT

- any other SQL queries: when the DML query cannot be interpreted as one of the types above, its type is set to GDA_QUERY_TYPE_NON_PARSED_SQL; This type indicates that query parsing has failed, either because the SQL statement is wrong or because it uses unsupported SQL constructions and/or keywords.

The GdaQuery object adapts itself to the query it represents: if possible it will contain lists of objects (such as fields, joins, etc) and will make them available to the outside world, and if not (when the query type is GDA_QUERY_TYPE_NON_PARSED_SQL) it will simply store a string representing the SQL text to execute. The limit between the two is that this object cannot "understand" very complex SQL queries or "proprietary" SQL dialects.

Each GdaQuery object can be built either using the provided API to add individual elements, or using the gda_query_set_sql_text() method. Using the API presents the advantage of being faster since it spares the SQL parsing time and using the gda_query_set_sql_text() method is slower but will work for any SQL (for non "understood" SQL, it will set its type to GDA_QUERY_TYPE_NON_PARSED_SQL). See this section for some examples.

The GdaQuery object implements the GdaEntity interface, and all its fields, which inherit from GdaQueryField, implement the GdaEntityField interface.

#### General structure of a query

A query is composed of the following (each object is owned by the query object):

- A list of targets (as GdaQueryTarget objects). Each GdaQueryTarget object represents an entity (= an object which implements the GdaEntity interface, for example a database table or another query); A single entity can be represented several times by different GdaQueryTarget objects (for instance for queries which involve a same table multiple times).

  If a target that represents a GdaQuery 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 GdaQueryTarget 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 GdaQueryJoin objects) between the targets. This is useful only for selection queries where a GdaQueryJoin represents a SQL join between entities to avoid cross products. FIXME: In the example above there is one GdaQueryJoin object to represent the "INNER JOIN" between the two GdaQueryTarget objects representing the "person" and "title" entities.

- A list of fields (as GdaQueryField objects). There are several different types of GdaQueryField objects to represent the different uses of fields in data manipulation queries: a field can represent a table.field field, or a function. The different types of query fields are:

  - GdaQueryFieldAll
  - GdaQueryFieldField
  - GdaQueryFieldFunc
  - GdaQueryFieldAgg
  - GdaQueryFieldValue

  Query fields can be either 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). For instance in the “SELECT count (id) FROM mytable”, the query is composed of a GdaQueryTarget for the "mytable" table, of a GdaQueryField for the "count()" function, and of a last GdaQueryField for the "id" table field.

  Some types of 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: in a query such as “"INSERT INTO persons (firstname, lastname) VALUES ('name1', 'name2')"”, the query fields "firstname" and "lastname" have their "value_provider" property pointing to the GdaQueryFieldValue query fields with the values "name1" and "name2", respectively.

- A list of sub-queries (also as GdaQuery objects).

- A condition on the application of the query (as a GdaQueryCondition object). A condition object can contain several sub-conditions.

- Other attributes describing the grouping and ordering.

The following sections describe the requirements for the construction of each type of query. Note that these requirements are only really tested when the query is rendered into an executable statement, when errors may be returned.

#### Graphical conventions to represent queries

The GdaGraphviz object can produce .dot files out of a GdaQuery object, which can the processed using the GraphViz tool. This tool produced the internal representations of the 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 GdaQueryTarget object is represented by an orange box (here "customers", "salesrep" and "locations").

- Each GdaQueryJoin object is represented by an arc between the GdaQueryTarget 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 GdaQueryField 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 that, in order from left to right, display the real type of field ("Field" fo a GdaQueryFieldField, "Value" for a GdaQueryFieldValue, etc), the field visibility (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 GdaQueryFieldField object has an arc to the GdaQueryTarget to which it belongs.

- The GdaQueryCondition object, if present, is represented by a yellow box labeled with the SQL representation of the condition, and with arcs to all the GdaQueryField objects it uses.

#### Query Parameters

Any query can require some parameters to be given values before they can be executed. For example, in a query like “SELECT name FROM person WHERE age = <value>” (please note that this syntax is for illustration purpose only and is not recognised by the libgda's parser, see the section about the SQL queries for more information) "<value>" is a parameter whose value must be provided before the query can be executed. A parameter can only be represented in a query by a GdaQueryFieldValue query field. A GdaParameter object provides a value for the parameter to the query.

A query QU1 can specify that a given parameter QU1:QF1 has its value restricted to the values from a field in another "SELECT" query (QU2:QF1). In this case the GdaQueryFieldValue which is a parameter (QU1:QF1) has its "value_prov" property set to the QU2:QF1 field. The QU2 query can be managed by the QU1 query (using gda_query_add_param_source() and similar methods).

The raw list of parameters required to run a query is returned by the gda_query_get_parameters() method. However, it is usually better to use a GdaParameterList object which stores the parameters, and organises them in a convenient way. The function that returns a GdaParameterList object is gda_query_get_parameter_list().

#### 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 must be modifiable (e.g. it must not be another query or a database view).

- There cannot be any join.

- All the visible query fields must be GdaQueryFieldField fields.

- If the query is an insertion query, there must be at most one sub-query, and it must be a selection query. 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.

  If there is no sub-query, then all the fields which are value providers MUST be GdaQueryFieldValue 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. Hidden field may exist as part of a condition.

- If the query is an update query, then all the fields which are value providers MUST NOT be GdaQueryFieldAll 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.

#### SQL queries

Since it is sometimes easier to define a query using an SQL statement, a GdaQuery object can be constructed from any SQL 1992 statement, using the gda_query_set_sql_text() method. The SQL string passed as an argument must be a single SQL statement, not several statements separated by a colon.

To define variables in the SQL statement, just add a parameter definition (type, name, description,...) in C-style comments right after the value which must be a parameter, such as in the following SQL where the value "10" is in fact a parameter names var01 of type integer (the default value for the parameter will be 10):

    	SELECT 10 /* name:var01 type:integer */, id FROM customers

To avoid specifying a value altogether (the parameter will not have any default value), replace the "10" with "##" as:

    	SELECT ## /* name:var01 type:integer */, id FROM customers

So, in the first case when the query is 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. A GdaParameter object (obtained through a GdaParameterList object) can replace the "10" value with another value. See the gda_query_get_parameters() and gda_query_get_parameter_list() methods for more information. In the second case, if no value has been specified by a GdaParameter, then the query will be:

          "SELECT NULL, t1.id FROM customers AS t1"

The available parameter specification attributes are:

- "name:" fixes the value's name

- "descr:" fixes the value's description

- "type:" fixes the value's GType or GdaDictType

- "isparam:" tells if value is a variable (a parameter) or not (example: "isparam:FALSE"). The default is that it is a parameter.

- "nullok:" tells if value may be NULL (example: "nullok:FALSE")

Note that each attribute can contain more than one word if the attribute value is enclosed in single or double quotes, such as:

    	SELECT ## /* name:"This is quite a long name" type:integer */, id FROM customers
    	SELECT ## /* name:'This is quite a long name' type:integer */, id FROM customers

Currently, type inference is not reliably supported, so the use of the "type:" tag is strongly encouraged.

In order to simplify queries writing in some instances where the description of a parameter is not provided, a simpler notation is also available in the general form as:

    ##<param_name>[::<param_type>[::NULL]]

where the <param_type> part can be omitted and will thus default to string ("gchararray"), and the last "NULL" part, if present informs that the parameter can have a NULL value. Examples: "##name", "##name::gfloat", "##+2::gint", "##customers/@id", "##customers/#2".

#### Examples of GdaQuery object constructions

- A very simple SELECT query named "Select1":

      SELECT t1.id, t1.name FROM customers AS t1

- A very simple INSERT query named "Insert1" -- note the dotted arrows (for example from the "name" field to the "(john)" value field) which means that the "name" field will be assigned the contents of the "(john)" value field (namely the "value_provider" property of the "name" field is set to the "(john)" value field); note as well that the "(john)" and "(10)" value fields are hidden (no 'V' displayed):

      INSERT INTO customers (id, name) VALUES (10, 'john')

- A very simple DELETE query named "Delete1" -- note the condition object shown in the yellow box (which is the WHERE condition) and the 'P' in the (not shown) "Person Id" value field which denotes that the value field is a parameter (the parameter name and type are not shown); also note that all the query fields are hidden:

      DELETE FROM customers WHERE id=## /*type:gint name:"Person Id"*/

- A very simple UPDATE query named "Update1" -- note the condition object shown in the yellow box (which is the WHERE condition); also note first that all the query fields are hidden except for the "name" field which is the table field updated and second that the value for the update is assigned to the "name" field using a dotted arrow (see the INSERT example for more information):

      UPDATE customers SET name='Joe' WHERE id=10

- A more complex query named "Complex" which uses a sub-query as a target entity:

      SELECT t1.f2 FROM (SELECT t1.id AS f1, t1.name AS f2 FROM customers AS t1 ) AS t1 WHERE t1.f1=2

### Query fields

Each query can contain some fields which make the query's contents. There are different types of query fields, and each type corresponds to a class and inherits the GdaQueryField class.

Any query field can be visible or hidden. A visible field will appear in the entity corresponding to the query (in case of SELECTion queries anyway), and invisible fields are used by other query fields.

Also, any query field can be internal or not. An internal field is one used by the library itself, and should never be known to the user of the library (so it is just mentionned here for completeness).

Some fields can represent more than one value (usefull for IN operators for example).

The different types of query fields are:

- GdaQueryFieldAll: represents all the fields of an entity which itself is represented through a GdaQueryTarget (the SQL notation is "entity.*")

- GdaQueryFieldField: represents one field of an entity which itself is represented through a GdaQueryTarget (the SQL notation is "entity.field")

- GdaQueryFieldValue: represents a value, which can also be a parameter (in this case the parameter may or may not have a default value)

- GdaQueryFieldFunc: represents the result of a function (a GdaDictFunction object) applied to one or more query field object(s) of the same query

- GdaQueryFieldAgg: represents the result of an aggregate (a GdaDictAggregate object) applied to a query field object of the same query

- GnomeDbQfSelect: represents the result of the execution of a whole SELECTion query. It represents a list but may return a single value as well

- GnomeDbQfLink: represents one field of an entity, that entity not being represented through a GdaQueryTarget object. This kind of query field is used to link sub-queries to their parent query in complex queries

- GdaQueryFieldValue: represent a list of values, which cannot be parameters