SQL Interface to Query
[Query: Querying for Objects]

Detailed Description

The types of SQL queries that are allowed at this point are very limited. In general, only the following types of queries are supported: SELECT * FROM SomeObj WHERE (param_a < 10.0) AND (param_b = "asdf") SORT BY param_c DESC; INSERT INTO SomeObj (param_a, param_b, param_c) VALUES ("value_a", true, "0/1");

For SELECT, the returned list is a list of all of the instances of 'SomeObj' that match the query. The 'SORT' term is optional. The 'WHERE' term is optional; but if you don't include 'WHERE', you will get a list of all of the object instances. The Boolean operations 'AND' and 'OR' together with parenthesis can be used to construct arbitrarily nested predicates.

For INSERT, the returned list is a list containing the newly created instance of 'SomeObj'.

Joins are not supported directly. SELECT * FROM ObjA,ObjB WHERE (ObjA.param_id = ObjB.param_other_id); The problem with the above is that the search requires a nested search loop, aka a 'join', which is not currently supported in the underlying QofQuery code.

However, by repeating queries and adding the entities to a new session using qof_entity_copy_list, a series of queries can be added to a single book. e.g. You can insert multiple entities and save out as a QSF XML file or use multiple SELECT queries to build a precise list - this can be used to replicate most of the functionality of a SQL join.

SELECT * from ObjA where param_id = value; SELECT * from ObjB where param_other_id = value;

Equivalent to: SELECT * from ObjA,ObjB where param_id = param_other_id and param_id = value;

When combined with a foreach callback on the value of param_id for each entity in the QofBook, you can produce the effect of a join from running the two SELECT queries for each value of param_id held in 'value'.

See QofEntityForeachCB and qof_object_foreach.

Date queries handle full date and time strings, using the format exported by the QSF backend. To query dates and times, convert user input into UTC time using the QOF_UTC_DATE_FORMAT string. e.g. set the UTC date format and call qof_print_time_buff with a time_t obtained via timespecToTime_t.

If the param is a KVP frame, then we use a special markup to indicate frame values. The markup should look like /some/kvp/path:value. Thus, for example, SELECT * FROM SomeObj WHERE (param_a < '/some/kvp:10.0') will search for the object where param_a is a KVP frame, and this KVP frame contains a path '/some/kvp' and the value stored at that path is floating-point and that float value is less than 10.0.

The following are types of queries are NOT supported: SELECT a,b,c FROM ... I am thinking of implementing the above as a list of KVP's whose keys would be a,b,c and values would be the results of the search. (Linas)

XXX (Neil W). Alternatively, I need to use something like this when converting QOF objects between applications by using the returned parameter values to create a second object. One application using QOF could register objects from two applications and convert data from one to the other by using SELECT a,b,c FROM ObjA; SELECT d,f,k FROM ObjB; qof_object_new_instance(); ObjC_set_a(value_c); ObjC_set_b(value_k) etc. What's needed is for the SELECT to return a complete object that only contains the parameters selected.

Also unsupported: UPDATE.

Certain SQL commands can have no QOF equivalent and will generate a runtime parser error:


Typedefs

typedef _QofSqlQuery QofSqlQuery

Functions

QofSqlQuery * qof_sql_query_new (void)
void qof_sql_query_destroy (QofSqlQuery *)
void qof_sql_query_set_book (QofSqlQuery *q, QofBook *book)
GList * qof_sql_query_run (QofSqlQuery *query, const char *str)
 Perform the query, return the results.
void qof_sql_query_parse (QofSqlQuery *query, const char *str)
QofQuery * qof_sql_query_get_query (QofSqlQuery *)
GList * qof_sql_query_rerun (QofSqlQuery *query)
void qof_sql_query_set_kvp (QofSqlQuery *, KvpFrame *)

Function Documentation

QofQuery* qof_sql_query_get_query (  QofSqlQuery *   ) 
 

Return the QofQuery form of the previously parsed query.

Definition at line 99 of file qofsql.c.

00100 {
00101         if (!q) return NULL;
00102         return q->qof_query;
00103 }

QofSqlQuery* qof_sql_query_new (  void   ) 
 

Create a new SQL-syntax query machine.

Definition at line 72 of file qofsql.c.

00073 {
00074         QofSqlQuery * sqn = (QofSqlQuery *) g_new0 (QofSqlQuery, 1);
00075         
00076         sqn->qof_query = NULL;
00077         sqn->parse_result = NULL;
00078         sqn->book = NULL;
00079         sqn->single_global_tablename = NULL;
00080         sqn->kvp_join = NULL;
00081 
00082         return sqn;
00083 }

void qof_sql_query_parse (  QofSqlQuery *  query,
const char *  str
) 
 

Same qof_sql_query_run, but just parse/pre-process the query; do not actually run it over the dataset. The QofBook does not need to be set before calling this function.

Definition at line 814 of file qofsql.c.

00815 {
00816         GList *tables;
00817         char *buf;
00818         sql_select_statement *sss;
00819         sql_where *swear;
00820         
00821         if (!query) return;
00822 
00823         /* Delete old query, if any */
00824         if (query->qof_query)
00825         {
00826                 qof_query_destroy (query->qof_query);
00827                 sql_destroy(query->parse_result);
00828                 query->qof_query = NULL;
00829         }
00830 
00831         /* Parse the SQL string */
00832         buf = g_strdup(str);
00833         query->parse_result = sql_parse (buf);
00834         g_free(buf);
00835 
00836         if (!query->parse_result) 
00837         {
00838                 PWARN ("parse error"); 
00839                 return;
00840         }
00841 
00842         if ((SQL_select != query->parse_result->type)&&(SQL_insert != query->parse_result->type))
00843         {
00844                 PWARN("currently, only SELECT or INSERT statements are supported, "
00845                          "got type=%s", sql_type_as_string(query->parse_result->type));
00846                 return;
00847         }
00848 
00849         /* If the user wrote "SELECT * FROM tablename WHERE ..."
00850          * then we have a single global tablename.  But if the 
00851          * user wrote "SELECT * FROM tableA, tableB WHERE ..."
00852          * then we don't have a single unique table-name.
00853          */
00854         tables = sql_statement_get_tables (query->parse_result);
00855         if (1 == g_list_length (tables))
00856         {
00857                 query->single_global_tablename = tables->data;
00858         }
00859         /* if this is an insert, we're done with the parse. */
00860         if(SQL_insert == query->parse_result->type) {
00861                 query->qof_query = qof_query_create();
00862                 return;
00863         }
00864         sss = query->parse_result->statement;
00865         swear = sss->where;
00866         if (swear)
00867         {
00868                 /* Walk over the where terms, turn them into QOF predicates */
00869                 query->qof_query = handle_where (query, swear);
00870                 if (NULL == query->qof_query) return;
00871         }
00872         else
00873         {
00874                 query->qof_query = qof_query_create();
00875         }
00876         /* Provide support for different sort orders */
00877         handle_sort_order (query, sss->order);
00878 
00879         /* We also want to set the type of thing to search for.
00880          * If the user said SELECT * FROM ... then we should return
00881          * a list of QofEntity.  Otherwise, we return ... ?
00882          * XXX all this needs fixing.
00883          */
00884         qof_query_search_for (query->qof_query, query->single_global_tablename);
00885 }

GList* qof_sql_query_rerun (  QofSqlQuery *  query  ) 
 

Run the previously parsed query. The QofBook must be set before this function can be called. Note, teh QofBook can be changed between each successive call to this routine. This routine can be called after either qof_sql_query_parse() or qof_sql_query_run() because both will set up the parse.

Definition at line 912 of file qofsql.c.

00913 {
00914         GList *results;
00915 
00916         if (!query) return NULL;
00917 
00918         if (NULL == query->qof_query) return NULL;
00919 
00920         qof_query_set_book (query->qof_query, query->book);
00921 
00922         // qof_query_print (query->qof_query);
00923         results = qof_query_run (query->qof_query);
00924 
00925         return results;
00926 }

GList* qof_sql_query_run (  QofSqlQuery *  query,
const char *  str
) 
 

Perform the query, return the results.

The book must be set in order to be able to perform a query.

The returned list will have been sorted using the indicated sort order, (by default ascending order) and trimmed to the max_results length. Do NOT free the resulting list. This list is managed internally by QofSqlQuery.

Definition at line 890 of file qofsql.c.

00891 {
00892         GList *results;
00893         
00894         if (!query) return NULL;
00895 
00896         qof_sql_query_parse (query, str);
00897         if (NULL == query->qof_query) return NULL;
00898 
00899         qof_query_set_book (query->qof_query, query->book);
00900         if(SQL_insert == query->parse_result->type){
00901                 results = NULL;
00902                 results = g_list_append(results, qof_query_insert(query));
00903                 return results;
00904         }
00905         qof_query_print (query->qof_query);
00906         results = qof_query_run (query->qof_query);
00907 
00908         return results;
00909 }

void qof_sql_query_set_book (  QofSqlQuery *  q,
QofBook *  book
) 
 

Set the book to be searched (you can search multiple books) If no books are set, no results will be returned (since there is nothing to search over).

Definition at line 108 of file qofsql.c.

00109 {
00110         if (!q) return;
00111         q->book = book;
00112 }

void qof_sql_query_set_kvp (  QofSqlQuery *  ,
KvpFrame * 
) 
 

Set the kvp frame to be used for formulating 'indirect' predicates.

Although joins are not supported (see above), there is one special hack that one can use to pass data indirectly into the predicates. This is by using a KVP key name to reference the value to be used for a predicate. Thus, for example, SELECT * FROM SomeObj WHERE (param_a = KVP:/some/key/path); will look up the value stored at '/some/key/path', and use that value to form the actual predicate. So, for example, if the value stored at '/some/key/path' was 2, then the actual query run will be SELECT * FROM SomeObj WHERE (param_a = 2); The lookup occurs at the time that the query is formulated.

The query does *not* take over ownership of the kvp frame, nor does it copy it. Thus, the kvp frame must exist when the query is formulated, and it is the responsibility of the caller to free it when no longer needed.

Note that because this feature is a kind of a hack put in place due to the lack of support for joins, it will probably go away (be deprecated) if/when joins are implemented.

Definition at line 117 of file qofsql.c.

00118 {
00119         if (!q) return;
00120         q->kvp_join = kvp;
00121 }

Generated on Fri Oct 21 15:50:00 2005 for QOF by  doxygen 1.4.5