generic sql [wasRe: The Gnucash database?]

[Jean-David Beyer]

Derek Atkins wrote:
> At this point I don't believe there is any hope of it working on
> DB2 without a lot of work.  While the SQL is pretty standard, the
> interface is still PG-specific, using the libpg library interface
> to the library.

I normally just use the embedded SQL in a C++ program. I assume the 
interface at that level is the same in C. For those not familiar with 
that, here is a trivial (real) invocation of some SQL stuff in C++.

bool ugDerived::
alloc_data_cursor(const db_dates& f_date,
		  const db_dates& l_date)
{
     EXEC SQL BEGIN DECLARE SECTION;
     char         ad_f_date[DB_DATE_SIZE + 1];
     char         ad_l_date[DB_DATE_SIZE + 1];
     EXEC SQL END DECLARE SECTION;

     [snip]

     EXEC SQL
	DECLARE ugCursor CURSOR FOR
	 SELECT I.ibd_company_id, T.tick_symbol,
		 I.ibd_eps, I.ibd_rs, I.ibd_acc_dst
	   FROM vl_ranks V, ibd I, tick T
           WHERE V.vl_timeliness = 1
             AND V.vl_date = :ad_f_date                <---<<<
             AND :ad_f_date <= I.ibd_date              <---<<<
             AND I.ibd_date < :ad_l_date               <---<<<
             AND I.ibd_company_id = V.vl_company_id
             AND I.ibd_company_id = T.tick_company_id
             AND T.tick_f_date <= :ad_f_date           <---<<<
             AND :ad_f_date <= T.tick_l_date           <---<<<
        ORDER BY I.ibd_eps DESC, I.ibd_rs DESC, I.ibd_acc_dst
	FOR READ ONLY
	OPTIMIZE FOR 5 ROWS;

It is not too important what this does. Only the form matters.

All embedded SQL statements start with the
EXEC SQL string and end with the first semicolon.

The stuff between
EXEC SQL BEGIN DECLARE SECTION;
and
EXEC SQL END DECLARE SECTION;
is used to notify the embedded SQL compiler that the variables declared 
there will be used in embedded SQL statements.

Then the stuff between the next EXEC SQL and ending with
OPTIMIZE FOR 5 ROWS;
declares a cursor for the query. The variables declared in the
DECLARE SECTION statements are referred to with a leading colon that I 
marked with <---<<<.

When you build a DB2 program, there is a precompiler (not the C or C++ 
one) that expands these embedded SQL things into some data structures and 
calls to the DB2 run time library. Precompiling, etc., are handled in the 
makefile.

There is another interface to DB2 that is better in some ways. I do not 
use it because I want stuff to be portable and the embedded SQL approach 
seems the closest way to achieve this (based on making my database stuff 
first run with postgreSQL (which was totally unsatisfactory in about 
1998), Informix (which was just barely OK in Red Hat Linux 5.0 days, but 
unsupported for Red Hat Linux 6.0), then DB2 V6.1, and now DB2 V8.1.6). 
Having to move from one dbms to another convinced me that the embedded SQL 
approach made the most sense for me. Of course there are little details 
(such as the colons and the EXEC SQL BEGIN DECLARE SECTION; stuff) that 
vary from one dbms to another, but those changes are pretty easy.

The issues of defining the databases (DDL stuff) can vary, but that stuff 
is pretty well isolated too. I have mine in shell scripts.

The way I program this stuff is to keep all the SQL in separate files 
anyway, separate from the main body of code. So in the main body of code, 
there is a line like this to prepare the query above:

result = database.alloc_data_cursor(dataDate, dataDate + oneWeek);

and all the SQL stuff is hidden.

> 
> Building Gnucash is pretty easy once you get all the build
> dependencies.  The hard part is getting all those -devel packages
> installed.  Building on RHEL is definitely going to be harder because
> they refused to distribute many of the gnome-1.4 packages that gnucash
> requires.
> 
> Building gnucash to separate it from your default install is pretty
> easy!  For example, I have three versions installed.  I just install
> them into /opt/gnucash-<version> using the --prefix=... configure
> option and all is well.
> 
> -derek
> 

-- 
   .~.  Jean-David Beyer           Registered Linux User 85642.
   /V\                             Registered Machine   241939.
  /( )\ Shrewsbury, New Jersey     http://counter.li.org
  ^^-^^ 09:35:00 up 5 days, 19:16, 3 users, load average: 4.16, 4.14, 4.09