SQL Interface to Query
[Query Object FrameworkQuery: Querying for Objects]


Typedefs

typedef struct _QofSqlQuery QofSqlQuery

Enumerations

enum  QsqlStatementType {
  SQL_NONE = 0, SQL_CREATE, SQL_LOAD, SQL_WRITE,
  SQL_INSERT, SQL_DELETE, SQL_UPDATE
}

Functions

void qof_sql_entity_set_kvp_tablename (const gchar *name)
 Set a default KVP table name for each backend.
void qof_sql_entity_set_kvp_id (gulong id)
 Set the initial index value of the KVP table.
gulong qof_sql_entity_get_kvp_id (void)
 Get the index value of the KVP table after the operation(s).
void qof_sql_entity_set_kvp_exists (gboolean exist)
 Set or clear a flag that the KVP table exists or not.
gchar * qof_sql_entity_create_table (QofEntity *ent)
 Build a SQL 'CREATE' statement for this entity.
gchar * qof_sql_entity_insert (QofEntity *ent)
 Build a SQL 'INSERT' statement for this entity.
gchar * qof_sql_entity_update (QofEntity *ent)
 Build a SQL 'UPDATE' statement for the current entity parameter.
gchar * qof_sql_entity_update_kvp (QofEntity *ent)
 Build a SQL 'UPDATE' statement for the KVP data in this entity.
gchar * qof_sql_entity_update_list (QofEntity *ent, GList **params)
 Build a SQL 'UPDATE' statement for a list of parameters.
gchar * qof_sql_entity_delete (QofEntity *ent)
 Build a SQL 'DELETE' statement for this entity.
gchar * qof_sql_entity_drop_table (QofEntity *ent)
 Build a SQL 'DROP' statement for this entity type.
gchar * qof_sql_object_create_table (QofObject *obj)
 Build a SQL 'CREATE' statement for this object.
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 gchar *str)
 Perform the query, return the results.
void qof_sql_query_parse (QofSqlQuery *query, const gchar *str)
QofQueryqof_sql_query_get_query (QofSqlQuery *)
GList * qof_sql_query_rerun (QofSqlQuery *query)
void qof_sql_query_set_kvp (QofSqlQuery *, KvpFrame *)

Detailed Description

The qof_sql_entity* functions are private - only accessible to QOF backends. The purpose is to make it easier for SQL-based backends to pass SQL commands to the relevant database. There is currently no QOF support for reading the entities back from the backend as each backend has specialized methods for data retrieval (GDA has GdaDataModel, sqlite uses **columnNames etc.) Actually, it is generally easier to read data from a SQL based backend than it is to create, update or delete data.

Note:
qof_sql_entity_update relies on qof_util_param_edit and qof_util_param_commit which identify the particular parameter to be committed.
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. See qof_date_print

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:


Enumeration Type Documentation

Enumerator:
SQL_NONE  no operation defined. init value.
SQL_CREATE  Create a new database
SQL_LOAD  Load all data from existing database.
SQL_WRITE  Write / sync all data to the database.
SQL_INSERT  Run a single INSERT statement.
SQL_DELETE  Run a single DELETE statement.
SQL_UPDATE  Run a single UPDATE statement.

Definition at line 54 of file qofsql-p.h.

00055 {
00057     SQL_NONE = 0,
00059     SQL_CREATE,
00061     SQL_LOAD,
00063     SQL_WRITE,
00065     SQL_INSERT,
00067     SQL_DELETE,
00069     SQL_UPDATE
00070 } QsqlStatementType;


Function Documentation

gchar* qof_sql_entity_create_table ( QofEntity ent  ) 

Build a SQL 'CREATE' statement for this entity.

Prepares a SQL statement that will create a table for this entity.

Definition at line 1368 of file qofsql.c.

01369 {
01370     gchar * sql_str, * start;
01371     eas data;
01372 
01373     g_return_val_if_fail (ent, NULL);
01374     if (!kvp_table_name)
01375         kvp_table_name = g_strdup(QSQL_KVP_TABLE);
01376     ENTER ("create table for %s", ent->e_type);
01377     start = g_strdup_printf ("CREATE TABLE %s (", ent->e_type);
01378     data.ent = ent;
01379     data.str = g_strdup("");
01380     data.kvp_str = g_strdup("");
01381     qof_class_param_foreach (ent->e_type, string_param_foreach, &data);
01382     sql_str = g_strjoin ("", start, data.str, END_DB_VERSION, data.kvp_str, NULL);
01383     g_free (start);
01384     LEAVE ("sql_str=%s", sql_str);
01385     return sql_str;
01386 }

gchar* qof_sql_entity_delete ( QofEntity ent  ) 

Build a SQL 'DELETE' statement for this entity.

Prepares a SQL statement that will delete the row for this entity into the appropriate table (which must already exist). The data for the entity must already have been INSERTed into the table.

Also deletes all KVP data for this entity.

Definition at line 1535 of file qofsql.c.

01536 {
01537     gchar * gstr, * sql_str;
01538     ENTER (" %s", ent->e_type);
01539     gstr = g_strnfill (GUID_ENCODING_LENGTH + 1, ' ');
01540     guid_to_string_buff (qof_entity_get_guid (ent), gstr);
01541     sql_str = g_strconcat ("DELETE from ", ent->e_type, " WHERE ",
01542         QOF_TYPE_GUID, "='", gstr, "';", "DELETE from ", kvp_table_name, 
01543         " WHERE kvp_id ", "='", gstr, "';", NULL);
01544     g_free (gstr);
01545     return sql_str;
01546 }

gchar* qof_sql_entity_drop_table ( QofEntity ent  ) 

Build a SQL 'DROP' statement for this entity type.

Prepares a SQL statement that will DROP the table for this entity type. (This function is fairly obvious but exists for completeness.) The table must already exist.

Definition at line 1549 of file qofsql.c.

01550 {
01551     gchar * sql_str;
01552     ENTER (" drop table for '%s'", ent->e_type);
01553     sql_str = g_strdup_printf ("DROP TABLE %s;", ent->e_type);
01554     LEAVE ("sql_str=%s", sql_str);
01555     return sql_str;
01556 }

gulong qof_sql_entity_get_kvp_id ( void   ) 

Get the index value of the KVP table after the operation(s).

Each backend table has an ID number for KVP entries as one QofEntity can have multiple KvpFrames. The ID number is mapped to the GUID of the entity when reading data back from the table.

The ID is incremented after each call to qof_sql_entity_insert where qof_instance_get_slots does not return an empty frame.

Definition at line 1569 of file qofsql.c.

01570 {
01571     return kvp_id;
01572 }

gchar* qof_sql_entity_insert ( QofEntity ent  ) 

Build a SQL 'INSERT' statement for this entity.

Prepares a SQL statement that will insert data for this entity into the appropriate table (which must already exist).

Definition at line 1389 of file qofsql.c.

01390 {
01391     KvpFrame * slots;
01392     eas data;
01393     gchar * command, * fields, * values, *id, *gstr, *sql_str, *kvp;
01394 
01395     data.ent = ent;
01396     data.str = g_strdup("");
01397     if (!kvp_table_name)
01398         kvp_table_name = g_strdup(QSQL_KVP_TABLE);
01399     ENTER (" insert a single '%s'", ent->e_type);
01400     gstr = g_strnfill (GUID_ENCODING_LENGTH + 1, ' ');
01401     guid_to_string_buff (qof_instance_get_guid ((QofInstance *) ent), gstr);
01402     DEBUG (" guid=%s", gstr);
01403     command = g_strdup_printf ("INSERT into %s (guid ", ent->e_type);
01404     // store param list in fields
01405     qof_class_param_foreach (ent->e_type, create_param_list, &data);
01406     fields = g_strdup(data.str);
01407     // store param values in values
01408     g_free (data.str);
01409     data.str = g_strdup("");
01410     data.full_kvp_path = g_strdup("");
01411     qof_class_param_foreach (ent->e_type, create_sql_from_param_cb, &data);
01412     values = data.str;
01413     /* handle KVP */
01414     kvp = g_strdup("");
01415     slots = qof_instance_get_slots ((QofInstance *) ent);
01416     if (!kvp_frame_is_empty(slots))
01417     {
01418         id = g_strdup_printf ("%lu", kvp_id);
01419         g_free (kvp);
01420         kvp_frame_for_each_slot (slots, kvpvalue_to_sql_insert, &data);
01421         kvp = g_strconcat (" INSERT into ", kvp_table_name,
01422             "  (kvp_id, guid, type, path, value) VALUES ('", id, "', '", 
01423             gstr, "', ", data.str, ");", NULL);
01424         /* increment the index value of the KVP table */
01425         kvp_id++;
01426         g_free (data.str);
01427     }
01428     sql_str = g_strjoin ("", command, fields, ") VALUES ('", gstr, "' ", 
01429         values, ");", kvp, NULL);
01430     g_free (command);
01431     g_free (fields);
01432     g_free (gstr);
01433     g_free (values);
01434     g_free (data.full_kvp_path);
01435     LEAVE ("sql_str=%s", sql_str);
01436     return sql_str;
01437 }

void qof_sql_entity_set_kvp_exists ( gboolean  exist  ) 

Set or clear a flag that the KVP table exists or not.

The KVP table should only be created once per session - use this flag to indicate that the KVP table has been successfully created (or deleted).

qof_sql_entity_create_table will not attempt to create the KVP table if this flag is set. It is up to the backend to control this flag.

Definition at line 1574 of file qofsql.c.

01575 {
01576     kvp_table_exists = exist;
01577 }

void qof_sql_entity_set_kvp_id ( gulong  id  ) 

Set the initial index value of the KVP table.

Each backend table has an ID number for KVP entries as one QofEntity can have multiple KvpFrames. The ID number is mapped to the GUID of the entity when reading data back from the table.

The ID is incremented after each call to qof_sql_entity_insert where qof_instance_get_slots does not return an empty frame.

Definition at line 1564 of file qofsql.c.

01565 {
01566     kvp_id = id;
01567 }

void qof_sql_entity_set_kvp_tablename ( const gchar *  name  ) 

Set a default KVP table name for each backend.

Each backend can choose a different KVP table name by overwriting the default name (sql_kvp) with this function.

e.g. the SQLite backend uses 'sqlite_kvp'.

Definition at line 1558 of file qofsql.c.

01559 {
01560     g_return_if_fail (name);
01561     kvp_table_name = g_strdup(name);
01562 }

gchar* qof_sql_entity_update ( QofEntity ent  ) 

Build a SQL 'UPDATE' statement for the current entity parameter.

Prepares a SQL statement that will update a single parameter for this entity into the appropriate table (which must already exist). The data for the entity must already have been INSERTed into the table.

Definition at line 1460 of file qofsql.c.

01461 {
01462     gchar *gstr, * sql_str, * param_str;
01463     QofInstance * inst;
01464     const QofParam * param;
01465     inst = (QofInstance*)ent;
01466 
01467     if (!inst->param)
01468         return NULL;
01469     ENTER (" modified %s param:%s", ent->e_type, inst->param->param_name);
01470     gstr = g_strnfill (GUID_ENCODING_LENGTH + 1, ' ');
01471     guid_to_string_buff (qof_instance_get_guid (inst), gstr);
01472     param = inst->param;
01473     if (0 == safe_strcmp (param->param_type, QOF_TYPE_COLLECT))
01474     {
01475         gchar * name;
01476         QofCollection * coll;
01477 
01478         coll = param->param_getfcn (ent, param);
01479         name = g_strdup (param->param_name);
01480         qof_collection_foreach (coll, collect_kvp, name);
01481         g_free (name);
01482         return NULL;
01483     }
01484     param_str = qof_util_param_to_string (ent, inst->param);
01485     if (param_str)
01486         g_strescape (param_str, NULL);
01487     sql_str = g_strconcat ("UPDATE ", ent->e_type, " SET ",
01488         inst->param->param_name, " = '", param_str,
01489         "' WHERE ", QOF_TYPE_GUID, "='", gstr, "';", NULL);
01490     LEAVE ("sql_str=%s", sql_str);
01491     return sql_str;
01492 }

gchar* qof_sql_entity_update_kvp ( QofEntity ent  ) 

Build a SQL 'UPDATE' statement for the KVP data in this entity.

Prepares a SQL statement that will update the KVP data for this entity (if any) into the KVP table.

This is a separate function because the KVP data can be modified independently of other parameters and updating a parameter should not cause an unwanted SQL operation on unchanged KVP data. If you know that the KVP data has changed, concatenate the two SQL commands into one.

Note:
the WHERE condition tests the path and the GUID as each entity (one GUID) can have more than one KVP.

Definition at line 1495 of file qofsql.c.

01496 {
01497     eas data;
01498     gchar *gstr, * sql_str;
01499     QofInstance * inst;
01500     gchar * start;
01501     KvpFrame * slots;
01502     inst = (QofInstance*)ent;
01503 
01504     if (!inst->param)
01505         return NULL;
01506     sql_str = NULL;
01507     if (kvp_frame_is_empty (qof_instance_get_slots ((QofInstance*)ent)))
01508         return NULL;
01509 
01510     ENTER (" modified %s param:%s", ent->e_type, inst->param->param_name);
01511     gstr = g_strnfill (GUID_ENCODING_LENGTH + 1, ' ');
01512     guid_to_string_buff (qof_instance_get_guid (inst), gstr);
01513     data.str = g_strdup("");
01514     data.full_kvp_path = g_strdup("");
01515     slots = qof_instance_get_slots ((QofInstance*)ent);
01516     start = g_strjoin ("", "UPDATE ", kvp_table_name, " SET ", NULL);
01519     kvp_frame_for_each_slot (slots, kvpvalue_to_sql_update, &data);
01520     sql_str = g_strjoin ("", start, data.str, " guid='", gstr, "';", NULL);
01521     g_free (start);
01522     g_free (data.full_kvp_path);
01523     g_free (data.str);
01524     LEAVE ("sql_str=%s", sql_str);
01525     return sql_str;
01526 }

gchar* qof_sql_entity_update_list ( QofEntity ent,
GList **  params 
)

Build a SQL 'UPDATE' statement for a list of parameters.

Prepares a SQL statement that will update the specified parameters for this entity into the appropriate table (which must already exist). The data for the entity must already have been INSERTed into the table.

Bug:
unfinished function.

Definition at line 1529 of file qofsql.c.

01530 {
01531     return NULL;
01532 }

gchar* qof_sql_object_create_table ( QofObject obj  ) 

Build a SQL 'CREATE' statement for this object.

Prepares a SQL statement that will create a table for this object for those times when an entity does not yet exist.

Definition at line 1346 of file qofsql.c.

01347 {
01348     gchar * sql_str, * start;
01349     eas data;
01350 
01351     if (!kvp_table_name)
01352         kvp_table_name = g_strdup(QSQL_KVP_TABLE);
01353     ENTER ("create table for %s", obj->e_type);
01354     start = g_strdup_printf ("CREATE TABLE %s (", obj->e_type);
01355     data.ent = NULL;
01356     data.str = g_strdup("");
01357     data.kvp_str = g_strdup("");
01358     qof_class_param_foreach (obj->e_type, string_param_foreach, &data);
01359     sql_str = g_strjoin ("", start, data.str, END_DB_VERSION, data.kvp_str, NULL);
01360     g_free (start);
01361     g_free (data.kvp_str);
01362     g_free (data.str);
01363     LEAVE ("sql_str=%s", sql_str);
01364     return sql_str;
01365 }

QofQuery* qof_sql_query_get_query ( QofSqlQuery *   ) 

Return the QofQuery form of the previously parsed query.

Definition at line 103 of file qofsql.c.

00104 {
00105     if (!q)
00106         return NULL;
00107     return q->qof_query;
00108 }

QofSqlQuery* qof_sql_query_new ( void   ) 

Create a new SQL-syntax query machine.

Definition at line 75 of file qofsql.c.

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

void qof_sql_query_parse ( QofSqlQuery *  query,
const gchar *  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.

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 1044 of file qofsql.c.

01045 {
01046     GList *results;
01047 
01048     if (!query)
01049         return NULL;
01050 
01051     if (NULL == query->qof_query)
01052         return NULL;
01053 
01054     qof_query_set_book (query->qof_query, query->book);
01055 
01056     /* Maybe log this sucker */
01057     if (qof_log_check (log_module, QOF_LOG_DETAIL))
01058     {
01059         qof_query_print (query->qof_query);
01060     }
01061 
01062     results = qof_query_run (query->qof_query);
01063 
01064     return results;
01065 }

GList* qof_sql_query_run ( QofSqlQuery *  query,
const gchar *  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.

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 113 of file qofsql.c.

00114 {
00115     if (!q)
00116         return;
00117     q->book = book;
00118 }

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 123 of file qofsql.c.

00124 {
00125     if (!q)
00126         return;
00127     q->kvp_join = kvp;
00128 }


Generated on Mon Jul 13 05:15:16 2009 for QOF by  doxygen 1.5.9