[gnome-db] Multiple queries in report stuff

[Santi Camps <santi gnome-db org>]

Hi all again,

I've been thinking about how data is processed in most report engines,
and I think we can easy make some improvements on it.  I will explain it
better with an example:

Imagine we want make a report with all account numbers of all costumers
of all offices of a bank.  Something like this:

BANK NAME
	OFFICE ID, NAME AND ADDRESS
		CUSTOMER ID, NAME AND ADDRESS	
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
		CUSTOMER ID, NAME AND ADDRESS	
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION

	OFFICE ID, NAME AND ADDRESS
		CUSTOMER ID, NAME AND ADDRESS	
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
		CUSTOMER ID, NAME AND ADDRESS	
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION
			ACCOUNT INFORMATION

The normal way to do this on most report engines will be to have one big
joined query with all the data:

SELECT ...
FROM   bank, office, customer, account
WHERE  ...

And then define groups in the report-engine.  In other words, we first
obtain a big plain data and then we need to structure it.  Bank
information appears once in the report but it should be in all the
records of the recordset.  More memory and more data traffic that are
not really needed.  And, more of this, AFAIK a big joined query will be
slower than some smaller queries in most database providers.

My proposal is to change this way of work, and define multiple nested
queries.  Something like this:

<sql_query id="query_bank">
	select * from bank
</sql_query>
<sql_query id="query_office">
	select * 
        from office 
        where bank_id=:query_bank.id_bank
</sql_query>
<sql_query id="query_customer">
	select * 
        from customer c, accounts a 
        where c.customer_id = a.customer_id 
        and a.office_id = :query_office.office_id
</sql_query>
<sql_query id="query_accounts>
	select * 
        from accounts 
	where customer_id = :query_customer.customer_id
	and office_id = :query_office.office_id
</sql_query>

<detail query="query_bank">
	some fields and labels

	<detail query="query_office">
		some fields and labels
		
		<detail query="query_office">
			some fields and labels

			<detail query="query_office">
				some fields and labels		
			</detail>
		</detail>
	</detail>
</detail>


This has the handicap that is a new way to work, so it could be rare for
new users.   

Do you think that this could be interesting, or it's better not to
reinvent the wheel and do the same that other engines ?

Cheers

Santi Camps