er2code

er2code is a tool to automatically generate code to make it easier to access a database from your program. The script takes a database description file as input and generates a directory full of database wrapper files.


The code is generated based on an XML file, and should be pretty database and language neutral. Right now, though, only PHP with a MySQL database is supported.

If you are lucky enough to be using Python for your application, i really recommend SQLObject over er2code, it is much cleaner and works with all kinds of databases.

The project is based on a Database Idea i once had, i thought it was revolutionary then, but i was later proved wrong, again, by the SQLObject guys.

Overview

The focus of this project is to take some of the boring work of off the programmers shoulders. Writing database wrappers is boring and tedious, and as such can be the course of many "sleeping-while-typing" errors.

But on the bright side, boring and tedious often means that the process can be automated, hence this tool.

The main idea of er2code is that each relation (also called table by some people) in the database, is mapped to a class of the same name. This class then contains a number of methods for manipulating the attributes contained in the relation.

The approach taken by er2code is to have a long list of methods (these are called Function in er2code), these functions have some requirements regarding an attribute that must be satisfied in order for the code of the function to generated.

The code of the function contains plain text mixed with identifiers. Each of these identifiers can generate their "value" (a text string) based on the specific attribute they are applied on.

Of course it is not as simple as that, we havent considered constructs, how to delete rows in the table, and how to handle foreign relations, e.g. 1-to-many.

The layout of the a file is a bit like C structs, and a bit like SQL.

Each relation is defined inside its own struct block:

User {
... attributes go here ...
}

This statement will cause a User class to be created.
Each attribute is defined by the name of the attribute, followed by one or more whitespace separated attribute type definers, and terminated by a semicolon.

User {
id serial;
username string(30) default(noname) sort unique index;
password string(40);
email string(100);
}

This will cause the User relation to contain four attributes:
id which is of type serial, an automatically incremented integer that is defined as the primary key.

username which is a 30-character long string, it is defined as sort which means that when a method which lists Users is called, the result should be sorted by this attribute. It also is of type index, which means that it should be possible to find a User based on the value of this attribute.

password is a 40-character string.
email is a 100-character string.

This is all er2code needs to know to create a class for the User relation, and the DataBase class, which is the one you use to connect to the database, and to list and find relations.

If the description above is used with er2code to generate PHP code, it should make it possible to write code like the following:

// Connect to the database.
$db = new DataBase ();

$halfdan = $db->findUserByUsername ( halfdan );
if ( $halfdan != null ) {
print $halfdan->getUsername() . < . $halfdan->getEmail() . >;
}

// List is automatically sorted by username
foreach ( $db->listUser() as $user ) {
print < a href="user.php?id= . $user->getId() . " > . $user->getName() . < /a >;
}