generic sql [wasRe: The Gnucash database?]

[Derek Atkins]

Jean-David Beyer <jdbeyer at exit109.com> writes:

> 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++.
>
[snip]

Unfortunately this isn't portable to multiple databases.  It requires
a pre-compiler, which doesn't always exist..  And you still need to
handle per-db idiosyncracies of the SQL language.

> 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 <---<<<.

Unfortunately this isn't portable, which is REALLY the goal of the
exercise.  We want to write code that we can use on ANY (or most any)
database, without having to re-write the SQL generators on a per-db
platform.

> 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);

Yea, this is just modular programming.  But what
"database.alloc_data_cursor" does internally is irrelevant to the rest
of the code (as you well know).  However, I think it might be much easier
if we used an API that effectively did:

statement = build_sql_statement(...);
response = run_sql_statement(statement);
free_sql_statement(statement);
parse_response(response, ...);

We've still hidden the SQL from the rest of the code, but we're not
required to build special embedded-SQL statements for each processor.
We can just build the SQL string and run it.  The build, run, and
maybe even parse functions could theoretically be db-agnostic OR
db-knowledgable depending on what we want/need to do.

> and all the SQL stuff is hidden.

Agreed.  This is a major goal IMHO, to hide SQL from the rest of gnucash.

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available