Hi
I read the last postings about cacheing resultsets and optimize database
handling. I think it's the time to present my work:
I thought I can make you a christmas present, but I was not get around
to testing my changes :-) I wrote a dblayer for dbmail the last past
days and translated the db.c and db.h files. The main idea was to
simplify the interface, that other databases like Firebird, Oracle, ...
also can benefit from dbmail. An other advantage is, that you don't have
to store data in a cache-array for processing them, you can define own
resultsets. I will explain this in my poor english words.
I labled them dblayer.c and dblayer.h which will offer the following:
dbl_conn : a connection object (typedef)
dbl_transaction : a transaction object (typedef)
dbl_result : a resultset object (typedef)
//*********************************************************************
// check string for quotes
const char *dbl_quote(const char *statement);
/**
* \brief connect database
* \param server: servername or ip-address
* \param dbname: databasename
* \param user: username
* \param password: password
* \param parameters: additional parameters
* \return
* - NULL on error
* - dbl_conn on success
*/
dbl_conn dbl_connect(const char *server, const char *dbname,
const char *user, const char *password, const char *parameters);
/**
* \brief disconnect database
* \param conn: connection
* \return
* - -1 on error
* - 0 ok
*/
int dbl_disconnect(dbl_conn conn);
/**
* \brief start transaction
* \param conn: connection
* \return
* - NULL on error
* - dbl_transaction on success
*/
dbl_transaction dbl_transaction_start(dbl_conn conn);
/**
* \brief commit transaction
* \param transaction: transaction
* \return
* - -1 on error
* - 0 ok
*/
int dbl_transaction_commit(dbl_transaction transaction);
/**
* \brief rollback transaction
* \param transaction: transaction
* \return
* - -1 on error
* - 0 ok
*/
int dbl_transaction_rollback(dbl_transaction transaction);
/**
* \brief execute statement (no data return)
* \param conn: connection
* \param transaction: transaction
* \param statement: sql statement
* \return
* - -1 on error
* - 0 ok
*/
int dbl_execute(dbl_conn conn, dbl_transaction transaction,
const char *statement);
int dbl_executef(dbl_conn conn, dbl_transaction transaction,
const char *formatstr, ...);
/**
* \brief query data
* \param conn: connection
* \param transaction: transaction
* \param statement: sql statement
* \return
* - NULL on error
* - resultset on success
*/
dbl_result dbl_query(dbl_conn conn, dbl_transaction transaction,
const char *statement);
dbl_result dbl_queryf(dbl_conn conn, dbl_transaction transaction,
const char *formatstr, ...);
/**
* \brief move to next record
* \param result: resultset
* \return
* - -2 on error
* - -1 EOF (no record left)
* - 0 ok
*/
int dbl_result_next(dbl_result result);
/**
* \brief return field values
* \param result: resultset
* \param idx: column index (starting at 0)
* \return value
*/
const short dbl_result_get_short(dbl_result result,
unsigned short idx);
const long dbl_result_get_long(dbl_result result,
unsigned short idx);
const char *dbl_result_get_string(dbl_result result,
unsigned short idx);
...perhaps any others
/**
* \brief free a resultset
* \param result: resultset
* \return
* - -1 on error
* - 0 ok
*/
int dbl_result_free(dbl_result result);
/**
* \brief return sequence number
* \param conn: connection
* \param sequence: name of sequence
* \return
* - -1 on error
* - >0 sequence number
*/
static unsigned long dbl_get_seq(dbl_conn conn, const char *sequence);
//*********************************************************************
I will show you an example to doing this:
...
dbl_result res;
dbl_transaction tr = dbl_transaction_start(dbconn);
res = dbl_queryf(dbconn, tr,
"select x, y, z from table_a where id = %i", myid);
if (res == NULL) {
//trace error
dbl_transaction_rollback(tr);
return -1;
}
while (dbl_result_next(res) > -1) {
// process data
x = dbl_result_get_long(res, 0);
// or call a internal function with the same transaction
if (db_check_quotum_used(dbl_result_get_long(res, 0), ?, tr) < 0) {
// trace error
dbl_result_free(res);
dbl_transaction_rollback(tr);
return -2;
}
// or execute another statement
if (dbl_executef(dbconn, tr, "update table_b set xy = %i", x) < 0) {
// trace error
dbl_result_free(res);
dbl_transaction_rollback(tr);
return -3;
}
}
dbl_result_free(res);
dbl_transaction_commit(tr);
...
I realised transaction handling in db.c while I was in progress to
translate. I think this could be a configure flag (--with-transactions),
not? Or simply let the dbl_transaction_? functions empty for RDBMS
without transaction handling.
The driver for Firebird is already realized. If it would work, I can
also translate MySQL and PostrgeSQL drivers.
There was also an idea to compile multiple dbdrivers and define the used
driver in the config file, but as you would use database specific
statements it is better to do #if-statements within the db-functions,
not? Or should we do normal if statements (if driver = xy)? The
advantage for normal statements is, that package distribution is easier,
the disadvantage, a if statement more to proceed.
I know, I bluffed a few month ago to realise dbmail with libdbi, but
first is libdbi or odbc a source of error more and second it is not
really well working on FreeBSD. I think to manage the own drivers is a
better choice.
What do you mean about that?
I'm glad to hearing from you.
Dominik
