DB design document

[Jean-David Beyer]

linas@linas.org wrote:
> 
> It's been rumoured that Rob Browning said:
> >
> > linas@linas.org writes:
> >
> > > > >     (server) || <- - - - - -> || (gnc_engine <-> ui)
> > >
> > > here, the  dotted line is just sql.  Don't have to invent anything to
> > > make this work.
> >
> > But is this sufficient?  Overstating things a bit, this may rely on
> > every sql server we support being able to do everything we need
> > internally.
> 
> ?
> More accurately
> 
> (sql server) || <- - - - - -> || (sql-client-lib <-> gnc_engine <-> ui)
> 
> at some point or another, gnc-engine must convert whatever its doing
> to plain-old SQL statements.   The standard sql client side libraries
> just send these to the server via unix-dmoain sockets or tcp sockets.
> If its tcp sockets, you don't 'care' wheere the sql server is.  Think
> of it like using Xlib & getting xwindows network transparency
> 'automaticaly', with no extra work for the app writer.  Same idea.

OK in theory, but not the same in practice. If you use the IBM DB2 UDB,
for example, the client-side library sends gawdawful stuff through the
TCP/IP connection.

Here is an example of a tiny simple code fragment and how it was
converted:

/*
EXEC SQL
	DECLARE btdCursor CURSOR FOR
	SELECT btd_company_id, btd_tick_symbol, btd_price_closing
	   FROM btd_yield
	 ORDER BY btd_price_closing
	  FOR READ ONLY
	  OPTIMIZE FOR 5 ROWS;
*/
#line 11077 "btdDerived.i"
    
/*
EXEC SQL OPEN btdCursor;
*/
{
#line 11079 "btdDerived.i"
  sqlastrt(sqla_program_id, &sqla_rtinfo, &sqlca);
#line 11079 "btdDerived.i"
  sqlacall((unsigned short)26,5,0,0,0L);
#line 11079 "btdDerived.i"
  sqlastop(0L);
}


You can be pretty certain that sqlastrt(), sqlacall(), and sqlastop()
are not sending plain SQL through the TCP/IP channel. I assume all
implementations are different, but IBM, at least, has sort-of
precompiled the queries so that the server can do the those
optimizations that are handled dynamically more quickly, and perform
many optimizations at compile-time instead of at run time. Note that the
first EXEC SQL does not even cause any code to be generated, since that
information got built into the database at the time this program was
compiled to be more efficiently executed at run-time. The second EXEC
SQL that opened the cursor sends a bunch of tables to the server so that
the server need not fumble around parsing and optimizing SQL statements
at run time.

Of course only the implementor of the dbms API as presented to the
GnuCash developers needs to deal with any of this. Would not there be a
layer of gnc_dbms_libraries between the gnc_engine and the
sql-client-lib to handle the case where the dbms server cannot do
something? So that most developers of GnuCash would never see the SQL at
all?

-- 
 .~.  Jean-David Beyer           Registered Linux User 85642.
 /V\                             Registered Machine    73926.
/( )\ Shrewsbury, New Jersey
^^-^^ 4:50pm up 16 days, 1:37, 2 users, load average: 2.05, 2.12, 2.09