INSERT and SQL joins in QOF

[Neil Williams]

I've implemented a way of handling the INSERT SQL command in QOF from a SQL 
command. I may also support UPDATE but I'm not sure if that's necessary - I 
don't have code that would use it but if someone has an idea of how it could 
be used, I'll work to that. DELETE is simple too - run as a select and call 
qof_entity_free() for each QofEntity in the returned GList.

I've also worked out (I think) a way of replicating a SQL join within QOF and 
it's a result of the work on partial QofBook's and QSF. 

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 but can be supported in application code.

I'd like to ask if others can see problems or limitations in this idea:

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;

(i.e. a rudimentary join based on a single value.)

When combined with a foreach callback on the value of param_id for each
ObjA 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'.

qof_object_foreach(ObjA->e_type, book, cb, session);

static void
cb (QofEntity *ent, gpointer data)
{
	GList *results;
	QofSession *session;
	QofBook *book;
	QofSqlQuery *q;

	session = (QofSession*)data;
	book = qof_session_get_book(session);
	q = qof_sql_query_new();
	// get the value of param_id from ent to use in char* sql
	// SELECT * from ObjA where param_id = value;
	qof_sql_query_set_book(q, book);
        results = qof_sql_query_run (q, sql);
        if(results != NULL) {
                qof_entity_copy_list(session, results);
	//SELECT * from ObjB where param_other_id = value;
	qof_sql_query_set_book(q, book);
        results = qof_sql_query_run (q, sql);
        if(results != NULL) {
                qof_entity_copy_list(session, results);
}

As an extra improvement, keep two GList pointers and delay copying the list 
from the first query until you know you've got a non-NULL list from the 
second query - that maintains the usual effect of the join - results from one 
table are not output if there are no matching results from the other table.

if((results_a != NULL)&&(results_b != NULL)) {
	qof_entity_copy_list(session, results_a);
	qof_entity_copy_list(session, results_b);
}

Comments?

I'm also considering handling this:
SELECT a,b,c FROM ObjA;
SELECT d,f,k FROM ObjB;
qof_object_new_instance(ObjC);
ObjC_set_a(value_c);
ObjC_set_b(value_k) 

The select a,b,c could just return a GList of QofParam - the entity itself 
still exists so we can use param->param_get_fcn() to access the data itself, 
using entities returned by an internal select * from ObjA; or just 
qof_object_foreach().

This could be used as an optional method to map objects between applications - 
a toolkit mini-application could register objects from BOTH applications and 
do the conversion, saving to whichever set of objects are required. This 
would be more powerful than XSLT or the XML maps that will also be available.

In essence, converting an object from one application to another could become 
a series of SQL statements.

Doing it this way means that the data is always within a usable QofObject, no 
data would exist outside an object and there would need to be a suitable 
object to handle the data. This is where qof-generator can help - generating 
the C code for whatever objects (intermediary or not) that may be necessary.
http://sourceforge.net/projects/qof-gen/

-- 

Neil Williams
=============
http://www.dcglug.org.uk/
http://www.nosoftwarepatents.com/
http://www.linux.codehelp.co.uk/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
Url : http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20050412/606c3ff6/attachment.bin