DBOW - The Database Object Generator

 SourceForge.net Logo

DBOW is a database compiler-compiler or front-end. It takes table definitions in a meta language and prepares C, C++, PHP, Perl (etc) functions for manipulating the database. It will also produce SQL table data for MySQL.

DBOW allows a user to define a database table in a meta-language and produce SQL for generating the table. It also provides C, C++, PHP, Perl functions or methods for manipulating the table, such as insert/delete/search/update. The system is used to remove the grunt-work of writing class libraries or access functions for database tables and having to update several different source files if the SQL table format should change. The resultant code provides a language-native mechanism for manipulating the database.

Currently it supports C, C++, SQL and PHP (with Perl under way). It will be extended to support other languages and database constructs as required. It currently compiles/runs on FreeBSD/Linux. It doesn't require any other libraries or elements except YACC/Bison.

DBOW Home Page: http://dbow.sourceforge.net/
DBOW Sourceforge Page: http://www.sourceforge.net/projects/dbow/
Download DBOW v0.7: http://prdownloads.sourceforge.net/dbow/dbow-0.7.tar.gz?download
CVS Web: http://cvs.sourceforge.net/viewcvs.py/dbow/

The following is an example of a DBOW source file: 

#
# Put out a comment block (at the top of the file or in the include file)
# for the C code type.
#
%type C emit {
/*
 * $Id: sample.d,v 1.3 2004/01/28 13:53:57 dtynan Exp $
 *
 * This stuff appears at the end of the code file.
 */
%}

%type C proto {
/*
 * $Id: sample.d,v 1.3 2004/01/28 13:53:57 dtynan Exp $
 *
 * This stuff appears at the front of the C include file...
 * (new to v0.5)
 */
%}

%type C code {
/*
 * $Id: sample.d,v 1.3 2004/01/28 13:53:57 dtynan Exp $
 *
 * This stuff appears at the front of the C code file...
 * (new to v0.5)
 */
%}
#
# Define the table.
#
%table user {
	user_id mediumint(7) NOT NULL AUTO_INCREMENT primary key,
	fname varchar(254),
	lname varchar(254),
	a1 varchar(254),
	a2 varchar(254),
	a3 varchar(254),
	city varchar(254),
	state_county varchar(254),
	postcode_zip varchar(254),
	country_id mediumint(7) NOT NULL,
	contact_phone varchar(254),
	handle varchar(254) NOT NULL,
	password varchar(254) NOT NULL
%}

You'll notice that the definition is quite close to an SQL table definition. In fact, here's the SQL produced from the above. This was produced with the command

dbow -t mysql sample.d

There are some minor differences, most notably in the way you declare multiple primary keys. Another minor gotcha is the the SQL ability to overload column names and column types. For example, the construct 'date date NOT NULL' can be used in an SQL table definition but not in DBOW. Rename the column so it is not a reserved word.

When you ask dbow to produce a C source file, you can also ask for it to produce an include file. The way you do this is with the command

dbow -t c -h sample.h sample.d.

If you don't use the -h option, the struct definition is included with the C source.

You will notice that the C struct is similar to the SQL table in that SQL mediumint()s are represented by C ints and strings are represented by the C char * character pointer construct. The naming convention puts db_ in front of the table name to prevent any type of collision.

Taking a look at the prototypes produced in the include file, we find the following: 

/*
 * Prototypes
 */
struct db_user *db_useralloc();
void db_userfree(struct db_user *p);
struct db_user *db_runuserquery(dbow_conn *c, char *q);
struct db_user *db_finduserfirst(dbow_conn *c);
struct db_user *db_findusernext(dbow_conn *c, struct db_user *p);

int db_insertuser(dbow_conn *c, struct db_user *p);

int db_deleteuserbyuser_id(dbow_conn *c, int	);

struct db_user *db_finduserbyuser_id(dbow_conn *c, int	);

int db_updateuserbyuser_id(dbow_conn *c, struct db_user *p, int	);

We are given two basic functions for dealing with the C struct automatically. These are db_useralloc() and db_userfree(). For each table defined with DBOW, these functions will be automatically created. The alloc routine is little more than a call to malloc and memset to initialize memory to 0s. The free routine will free any character pointers within the structure before freeing the structure itself.

The function db_findusernext() is used to find the first/next row of a table following a search query. You give it a pointer to a dbow_conn, which is returned by dbow_init() and either an existing db_user pointer (which it will refill) or NULL (and it will allocate a new one and fill that).

The function db_runuserquery() runs a straightforward SQL query on the user table. It can be used for simple SELECT queries where there are no arguments. For example, db_runcustomerquery(dbp, "SELECT * FROM customer WHERE balance > 0.0"); will return the first db_customer struct that satisfies the search criteria (in this case, with a positive, non-zero balance). You can then use db_findcustomernext() to retrieve each additional record. db_findXXXnext() will return NULL when there are no more records.

The function db_runuserfirst() is simply a call to db_runuserquery(dbp, "SELECT * FROM user");. It returns the first (unsorted) user record it finds in the database.

The function db_insertuser() takes a db_user struct pointer and a dbow_conn pointer and inserts the data into the database. Following a dbow_init() call, you can call db_useralloc() to allocate an empty struct, fill the various elements of the struct with the appropriate data and then call db_insertuser() to insert this data into the database.

Note that for the MYSQL construct AUTO_INCREMENT, it is important that you set the field value to 0. Any other value will be posted as-is. Field values of 0 will be automatically incremented. As a side-effect, DBOW will retrieve all data types which have the AUTO_INCREMENT parameter. In the above example, this would mean that user_id was valid immediately after the call to db_insertuser()

The db_deleteuserbyuser_id() function will remove an entry from the database, specified (in this case) by the primary key. It is possible to add further search types using DBOW keywords. For example, adding the line

%search user fname

to the DBOW source file will add a new function called db_finduserbyfname() which will take a dbow_conn pointer and a char * pointer for 'fname'. To add a user-defined name to the function, use the DBOW command

%search user fname findfname

Which will create a routine called findfname, which performs the same function. This DBOW command can be used to add search functions for any column in the table. At this time, you cannot produce search functions for anything other than single-column equality queries. Using the low-level dbow_query function however, allows you to create custom queries of any size or description. If you expect more than one row returned by the query, call db_findusernext() for each successive row.

Finally, the db_updateuserbyuser_id() function will modify the table contents in the database to reflect those in the struct. Similar to db_insertuser(), the function takes a complete db_user struct which will be inserted into the database. However, it takes an additional argument which is similar to the argument to db_searchuserbyuser_id() which tells the database which record to replace. In this case, we are updating based on the user_id column.

(The above files were produced using version 0.7 of DBOW)