Home Products Services Codegen About Us Contact


Bonobo Framework's Code Generator
- User Manual -

Introduction Generator Setup in Detail Limitations Generated Code FindBy Functions ListBy Functions Related Data Extending Model Classes Table Name PrefixesMore Options Extending

Introduction

Hello and thank you for giving my first sourceforge.net project a chance. This open-source PHP code generator is what I use everyday as part of a larger PHP framework (which I may yet publish on sourceforge, someday). Its purpose is to quickly generate basic DB operations such as insert new row, delete, edit or retrieve exitsting row from DB, or an array of rows. Main focus is on PHP/MySQL combination.

Codegen itself is licenced under the GPL licence, but code it generates is yours to use as you wish.

Genereated code expects some variables (db parameters) and one define to be set when it is used, which is true for the framework that it is part of and will be detailed out later, however it is a small requirement (5 lines or so) and easy to do. It also expects you to have ADODB php library available since it will be used to collect the data about tables and their fields.

Generated code will have one class per database table, class's variables will be named after fields in the DB, and there is an extra option to pickup related data from another table, typicaly that in 1:n relationship with current one. There are lots of options for tweaking the generator output, and a facility to include db dump within generated files too, which is used to keep track of DB scheme in source control.

One nice feature is that generated files protect you from SQL injection. This is achieved transparently to programmer, ie when writing objects to db all string values will be escaped, all numeric values will be converted to ints etc. Data types are picked up from DB at time of creating db code. There is also a special debug mode that will write all sql queries to screen before executing them, so you can track what is going on with the script.

On sourceforge project is on this page, but newest copy is allways in sourceforge's subversion repository, accessible from same link.

A little history: codegen started out with making one single large file with all the classes, then we moved to model with multiple small classes. So there are now several generator classes but only the one with small classes is being actively developed and maintained. It is the one you'll want to use, since single-class file can easily grow to 300K that would have to be included and parsed for every single page request, with a high cost on performance. This document is about new generator.

About the Authors

Bogdan Ribic (aka Boban) - main author of the code & this help file. Freelance software developer living in Serbia with 15+ years of expirience in writing programs on various platforms and a proud member of Mensa Serbia. My homepage is on www.rbisoftware.com.

Igor Jovanovic (aka Iggy) - some help, lots of bug reports and feature requests, author of class for making DB dump part of generated model files. He is a programmer, Linux sysadmin and talented graphics designer with similar expirience. His homepage is on www.linuxstreet.net.

If you have feature requests. bug reports, questions etc, please contact us via sourceforge forums. We also might be available for hire, depending on the workload. You can find out via contact page.

Quick & Dirty Example :)

Suppose you have this table structure (MySQL format):

This is a simple DB with users and groups they can belong to. Put it in your mysql DB under name "codegen", which will make running examples here easier. For extra convenience make a user "username" with password "password" and give it full rights over this DB.

Note: beware of phpMyAdmin: If you have fields like password, status (SQL keywords) phpMyAdmin will capitalize them when executing your sql query unless field names are in backquotes. This will confuse code generator and you will get code that doesn't even parse.

Everything about code generation is done from code - you need to add PHP code to tell the generator for which tables in your DB you want to create DB functions. You will need to have ADODB files on your machine and in PHP_INCLUDE folder (or to edit line which inlcudes it).

Here is a script that will generate code for two of these tables and save the files to subfolder out-tmp. Please note that script expects codegen files to be in lib/ subfolder and that out-tmp subfolder already exists.

The code will generate several files. Two files will allways be created (class.db.php and dblib.php), plus one or two files per each table. Here is code generated for groups table:

class.db.php is class with main database class CDatabase, which will also create that object and call it $_db. dblib.php has functions that are used by generated classes, and it includes class.db.php.

Codegen makes several assumptions about how you will be using the files it makes. First, it assumes that parameters for connecting to db are written to $_SERVER[] superglobal before you try to use generated files. This requirement is obvious in generated class.db.php file as it will attempt to create db object when file is read. This is by design so that DB object and db connection will be openned only when you actualy need them, ie when you include a generated model file.

As you can see, code starts with a require line, which expects a constant to be defined to tell it in which folder all generated files are. There are two "model" classes, CUser and CGroup, which basically represent one row in their database tables. Class variables are named after fields in DB. Note: generator considers one-letter char fields to be boolean values, and will generate the code to work with them by writing "Y" for true and "N" for false. There is phpDoc code generated for all the model classes. Each model class has functions for creating, reading, updating, deleting and unmarshalling(turning array returned by DB functions to our object) data for all specified rows.

Example usage would be for you to create a file with configuration options, ie these lines:

$_SERVER['BFR_DB_USERNAME']  = 'username';
$_SERVER['BFR_DB_PASS'] = 'password';
$_SERVER['BFR_DB_DBNAME'] = 'codegen';
$_SERVER['BFR_DB_HOST'] = 'localhost';
$_SERVER['BFR_SQL_DEBUG'] = 0;
define ('CFG_MODEL_DIR', dirname(__FILE__) . '/some_folder/');

and after including that file something like:

require_once 'my_config_file.php'; // file with above options
require_once CFG_MODEL_DIR  . 'model.user.php';


$user = CUser::FindById(1);
var_dump($user);

First line would include configuration file, second one would include model file (which will itself create db object and connect to db). Fetching object from db is done with CUser::FindById(1) , which executes query similar to "SELECT * FROM `users` WHERE `id`=1".

Note this line:

$_SERVER['BFR_SQL_DEBUG'] = 0

If you put 1 instead of zero, code will use a debug mode where all queries are written out to screen before being executed. This can later be turned on or off for specific sections of code with 

$_db->debug = false/* or true */;

which is typicaly done while testing & debugging the script.

Quick demo how this class can be used:

$list = CUser::ListAll(); // List all rows from table
$total_count : CUser::Count(); // returns number of rows in the table, ie SELECT COUNT(*) FROM `users`
var_dump($list);

$jim = CUser::FindById(1701); // Find user with id 1701

$user = new CUser();
$user->username = 'joe';
$user->password = crypt('wookie');
$user->email_verified = true;
$user->Add(); // Add row to db, which will also update $user->id to PK for newly inserted row

$user->email='joe@example.com';
$user->Update(); // update the row in db

$user->Delete(); // in this form, delete will delete row for given object, using this object's id

CUser::Delete(11); // in this form, delete will delete row with id 11. id being primary key for the table

back to top

Generator Setup in Detail

We will now disect the code-generation script from our quick & dirty example.

All the files that generate the code are in lib subfolder. You need to include two of them - one that will gather the data about DB tables, and second that will then generate the code using data gathered by the first one. As of theis writing, only option for "gathering" is ADODB (open source PHP library for data abstraction, not Microsoft's ADO). Code that will be generated can be either mysql, or use the same ADODB library (and through that library all the databases it supports, which is quite a few).

require_once "adodb/adodb.inc.php";

This one is self explanatory, you need to include adodb's main include file.

require_once dirname(__FILE__) . "/lib/php-adodb-generator.php";
require_once dirname(__FILE__) . "/lib/php-mysql-generator-sc.php";

These will include gatherer and generator class. Next three lines are:

$data = new CDBGeneratorData();
$data->AddTableFull('users', 'User', 'user');
$data->AddTableFull('groups', 'Group', 'group');

These lines create an object that will hold data about what tables you wish to generate code for and options for code generator. AddTableFull() actually has seven parameters, but only the first is required, name of table in the DB.

Second argument for AddTableFull tells how the generated class will be called. This can be further adjusted by setting the $data->class_name_prefix to a string (default is "C") and then all model classes will have that name prefix, (which actually is handy for code completion). You can set $data->class_name_prefix to blank string, and you will have classes User, Group etc.

Third argument will define how object name for generated model classes will be called, which will be used in generated code and also as names of generated files. For groups table filename will be "model.group.php".

Now that we've defined for which tables we want code, we create object that will gather data about those tables (field names and types):

$conn = NewADOConnection('mysql://username:password@localhost/codegen');
$gatherer = new CAdoDbDataGatherer($conn);
$gatherer->GatherData($data);

ADODB gatherer requires an ADODB connection from which it will read table data. Simplest way is shown above. You need to put all the data for connection in an URL-like string, in this form:

mysql://DB_USERNAME:DB_PASSWORD@HOSTNAME_OR_IP_ADDRESS/DATABASE_NAME

Last two lines are straightforward, they just create gatherer object with given ADODB connection, and tell it to gather data for your $data object (and write gathered data in that object, too).

The rest is pretty simple, create a generator object, tell it where the output code goes and call function to actualy generate resulting code:

$generator = new CPhpMySQLCodeGenerator($data);
$generator->_output_folder = dirname(__FILE__) . '/out-tmp/';

$generator->Generate();

After the call to $generator->Generate(), results are written to the output folder.

If you want to inherit & extend model classes too (you will see examples of why you would want that later), you need to set their "prepare for inheritance" flag, which is seventh parameter in $data->AddTableFull call. This will result in model class names that are suffixed with "Base", ie CUserBase which you need to inherit to create CUser, but all the functions will still expect a CUser object.

back to top

Limitations

There are some limitations: generator will only work for tables that have primary key over only one column, of type integer and has autoincrement turned on. Also, field names in table will be directly mapped to class variable names in generated code, which means that field names must be valid php identifiers - cannot start with a number, for example. Dates are written in DB in "YYYY-MM-DD HH:MM:SS" form. Also, code generator works best if your tables and field names are lowercase with underscores, ie "user_id", as this will be used for functions like CBooking::FindByUserId().

Values that you pass to generated code must *not* be already escaped, which will be the case if php.ini directive magic_quotes_gpc is turned on. You can use this code to make sure: 

/**
 * calls stripslashes() on all elements of array, and recursively for sub-arrays
 *
 * @param array $arr
 */
function UnquoteArrayRecursive(&$arr) {
  $keys = array_keys($arr);
  foreach ($keys as $key) {
    if (is_array($arr[$key])) {
      UnQuoteArrayRecursive($arr[$key]);
    } else {
      $arr[$key] = stripslashes($arr[$key]);
    };
  };
};

if (get_magic_quotes_gpc() == 1) {
  UnquoteArrayRecursive($_COOKIE);
  UnquoteArrayRecursive($_GET);
  UnquoteArrayRecursive($_POST);
  UnquoteArrayRecursive($_REQUEST);
};

Function must be executed recursively over sub-arrays in case you were passing in fields from a multi-select list, for example. While you're at it, if you are worried that register globals poses a danger to your code, use this:

if (ini_get('register_globals')) {
  unset($v);
  foreach (array_diff(array_keys($GLOBALS), array('_GET', '_POST', '_REQUEST', 
  '_COOKIE', '_SESSION',  '_SERVER', '_ENV', '_FILES', '_GLOBALS', 
  'HTTP_GET_VARS', 'HTTP_POST_VARS', 'HTTP_COOKIE_VARS', 'HTTP_SERVER_VARS', 
  'HTTP_ENV_VARS', 'HTTP_POST_FILES')) as $v) {
    unset($$v);
  };
  unset($v);
};

back to top

Generated Code

All SQL functions are designed same way as in ADODB - they expect a string with query where all parameters are replaced with question marks, and parameter values passed in as a second argument in an array.

One complication we've made is that DB class will have names of all tables it uses as class variables, starting with "t_". This comes in handy when using table name prefixes, and also helps reduce typos if you have PHP editor with autocomplete. More on table prefixes later.

So, if you were writing a custom function to delete a user found by its username and password, your code wouldn't be:

Execute("DELETE FROM users WHERE user = 'username' AND password = 'password'");

Instead, you would write:

Execute("DELETE FROM $_db->t_users WHERE user = ? AND password = ?", array('username', 'password'));

Or, more likely:

$db->Execute("DELETE FROM $_db->t_users WHERE user = ? AND password = ?", array($username, $password));

All values that you enter as part of arguments are automagically escaped according to their type, in a matter consistent with ADODB behavior. All of this enables you to easilly port your code from MySQL to another database should you require that.

Code for accessing database is allways in generated model classes. For instance, CUser class will have functions like Add(), Delete(), FindBy[PRIMARY_KEY](), Update() and Unmarshall($assoc_array) functions. Last one, Unmarshall, will take an associative array (such as one returned by database functions) and write values to class vars, with all values type-casted correctly. If your tables have related data (ie, list of values from other tables that need to appear as an array in generated model classes), there will also be ReadRelatedData and WriteRelatedData functions. More on related data later.

back to top

FindBy Functions

FindBy functions are functions added to generated code which retrieve one row from DB converted to an object of corresponding model class. There is allways one of these generated, for the primary key of the table. In our example above, those are CUser::FindById() and CGroup::FindById(). Naming of he function is FindBy(field name or field names joined with word "And"). Field names in function name will be generated assuming that original field names are lowercase with underscores, and turned to uppercase camel caps. So, if you had field named last_name and wanted FindBy function for that, you will get a function named CUser::FindByLastName().

You can have several FindBy functions. This is done via find_by_functions array, fourth argument in AddTableFull. So, if we had this generator script:

Notice that only difference is this: 

$data->AddTableFull('users', 'User', 'user',
  array('username', 'email', array('username', 'password')));

We now have three more functions in generated code for CUser class (allways named mode.[object_name].php), which will retrieve an object from DB for given criteria. These ar: FindByUsername(), FindByEmail() and FindByUsernameAndPassword(). All generated functions will work via an "SELECT * FROM table_name WHERE field_name=value [ AND fieldname2=value2]" query. Also, all values will be cast to their type before being passed to SQL, which will protect you in some part against users entering invalid values.

Here is the generated code:

back to top

ListBy Functions

ListBy functions will retrieve an array of objects for given criteria, not a single object like FindBy functions. Other than that, usage is prety much the same; you can assign names of fields by which you wish to search same way as for FindBy. There is one difference - in case you want function to return all objects in the table, you would pass an empty array as one of elements of list_by_functions array. There will allways be one extra list function that allows you to list by arbitrary sql query, though from only one table.

Now, the command for users table looks like this: 

$data->AddTableFull('users', 'User', 'user',
  array('username', 'email', array('username', 'password')), 
  array('balance', array(), array('email_verified', 'status')));

The last line will create functions to find all users with a specified ballance, all users in the table (ie, no condition), and all users that meet two criteria, email_verified and status.

Generator code now looks like this:

And generated code like this:

You will notice that there are three pairs of functions, ie a pair of ListBYXXX() and ListByXXX_paged() for every entry you add to list_by_functions array. Second one, _paged, is used when you are displaying only a subset of data in the table, typically as a page with previous & next links, like Google result lists.

Also note that all list functions take two optional parameters, order_by and index_by. order_by decides how the data will be ordered. index_by is set to false by default, if you use a fieldname there then resulting array will be indexed on given field.

Here is generated code for CUser::ListByBalance() : 

    /**
     * @static
     *
     * @desc [STATIC] Returns array of CUser objects,
     *   searching by following field(s):
     *
     *    - balance
     *
     * @param float $balance
     * @param string $_order_by
     * @param mixed $_index_by
     *
     * @return array Array of CUser objects
     */
    function & ListByBalance($balance, $_order_by = "id", $_index_by = false) {
        global $_db;

        $_query = "SELECT * FROM $_db->t_users  WHERE
          (balance = ?) ORDER BY $_order_by";
        $_result = array();

        $_res = mysql_query_d(ProcessParams($_query, array($balance)));
        if ($_res == false) return array();
        while($_row = mysql_fetch_array($_res, MYSQL_ASSOC)) {
            if ($_index_by !== false) {
                $_result[$_row[$_index_by]] = new CUser($_row);
            } else {
                $_result[] = new CUser($_row);
            };
        };
        mysql_free_result($_res);
        return $_result;
    }

So to retreive all users with balance of zero sorted by username and indexed by primary key of table, id, you would use:

$deadbeats = CUser::ListByBalance(0, 'username', 'id');

and then from that array you can get a user with id=18 with:

$user = $deadbeats[18];

Make sure that you are indexing by field that is unique, like id or username since otherwise elements in result array will get overwritten.

For each ListBy function there will also be a CountBy function. For ListAll() there will be Count() that returns number of rows in whole table.

And, there will be ListWhere and CountWhere functions, which allow you to use a custom sql query. For safety reasons, query should be in our form, ie something like

CUser::ListWhere("username = ?", array($username));

and not just 

CUser::ListWhere("username = '$username'");

since previous form will properly escape and quote the string before sending it to db. Notice that ListWhere functions also have optional $index_by and $_order_by parameters, and there is even a ListWhere_paged.

back to top

Related Data

Sometimes you need data from other, related tables delivered in the object. In this case, we have two tables, users and groups. Each user can belong to a number of groups, and relationship is realized via user_groups table. You can have the generator create code that will collect group_ids from user_groups table, and put them in array "groups" in CUser object.

To generate related functions, you need to use sixth argument of AddTableFull, and this is a little more complicated than ListBy and FindBy. But not much more complicated :)

In this case, we want to fetch all group_id numbers from user_groups table where user_id is same as id of our CUser object, and have that as an array called "groups" in our CUser class.

SQL for this is something like : SELECT group_id FROM user_groups WHERE id = [OUR USER ID]

Command for users table now looks like this:

$data->AddTableFull('users', 'User', 'user',
  array('username', 'email', array('username', 'password')),
  array('username', 'email', array('username', 'password')),
  array(
    array(
    'varname'  => 'roles',
    'this_key' => 'id',
    'that_key' => 'user_id',
    'table'    => 'user_groups',
    'value'    => 'group_id',
    'order_by' => ''
    ), 
  ));

As you can see, we need to pass an assoc array where each key has a meaning.

  • varname: How will the variable in Model class (ie CUser) be called
  • this_key: field from users table that we will match against other field in related table, here ID of our CUser
  • that_key: field in related table that we will match against, here user_id field in user_groups
  • table: name of the related table, here user_groups table
  • value: name of field with value that we wish to fetch, here it is group_id
  • order_by: Unused as yet, intended to enable you to sort the resulting values

Generated code will optimize number of SQL queries for array of results, so you won't have a separate SQL query for each object. Suppose you are retrieving array of 50 users, and for each you also require id numbers of groups they belong to. Instead of fetching the array with CUser object and then for each of them executing a separate SQL query, generated code will construct another (somewhat longer) code to fetch group_id numbers for all user objects and then "distribute" them to their CUser objects, resulting in improved performance.

Generator code:

and the generated code:

back to top

Extending model classes

Generated model classes are limited in functionality, ie just dumb data holders. If you wish to inherit this class and add a function or a variable to it, you need to use 7th argument in AddTableFull, and set it to true. This will have the effect that generated model class will have "Base" as its name suffix, so out CUser class will get renamed to CUserBase. Also, generated model file will be named model.user.base.php, and codegen will generate anothe file mode.user.php from which it will include base file and inherit base class and that your changes are in a separate file and machine-generated one can be re-created as needed. Generator will create model.user.php (ie, inherited class) but will not overwrite it if it allready exists. End result is that you can easily switch between inherited and non-inherited model classes. Example code would be:

class CUser extends CUserBase {
  
  function GetProfileUrl() {
    return "/users/profile.php?user=$this->username";
  }
  
};

And if you need to read some data about the user that codegen cannot do itself, you can overrid ReadRelatedData for that class, or perhaps Unmarshall function.

You can generate CUser object from an associative array picked from db (or generated in your program), by doing $user = new CUser($assoc_array);

back to top

Table name prefix

Suppose your web application needs to share a database with other scripts. You can hope that the other script will use different table names than your app, or you can add some fixed prefix to all table names.

Code generated by Bonobo Framework's code generator will let you dynamically change the prefix at runtime (for example, after reading it from a configuration file). This is done via SetPrefix() function, or just via a call to CDBOperationsBase constructor, passing the prefix as second argument.

All tables that generated code uses (including tables that were mentioned within related data) will have a class variable in DB class, whose name will be t_table_name, in our case t_users, t_groups and t_user_groups. A call to SetPrefix will assign them names that all begin with given prefix. You can include other tables there, by adding their names to $generator->extra_table_names array. So if you had somewhere two more tables that you access from your own code, for instance user_settings and newsletter, you would add those like this: $generator->extra_table_names = array('user_settings', 'newsletter'); which will make DB class get two new variables, t_user_settings and t_newsletter. Both these variables will be included in prefix mechanism.

You can create two DB class instances, and have each of them access different tables, while you still use same code. This is typically done when you are doing upgrades of exports created with earlier versions of your scripts, or have multiple users where each of them needs to have his own data. However if you want to use two db objects: since generated code expects db var to be named $_db, you will need to manouever with references to allways have the right one in $_db global.

back to top

More Options

You can tweak code generation options to a fine degree. You can adjust number of lines code genrator will leave blank between functions (default is 4), you can set custom names (name prefixes, actually) for many of generated functions, decide if you weant all model class names to begin with a "C", with another letter or no prefix, indentation size of generated code... Some of this is done in $data bject, some in $generator.

  • $data->class_name_prefix = ""; - Class name prefix. You can get rid of that "C" for CUser here.
  • $data->enclose_names_in = "`" - If you wish all table and field names in generated code to be enclosed in backticks (recommended for mysql, but not portable).
  • $data->function_spacing = 1; - Controls spacing between functions, default is 2.
  • $data->indent_size = 4; - Controls indentation. Default is 4.
  • $data->table_name_prefix = 'myapp_'; - If you are reading data from DB where table names are already prefixed. Not that tables that you yourself add to $generator->extra_tables shouldn't have their names prefixed.
  • $generator->big_class_name = "AppBase"; - If you want DB class to have a different name from default CDatabase
  • $generator->delete_prefix = 'remove'; - function DeleteUser will now be called removeUser
  • $generator->find_prefix = 'get'; - function FindUserById will now be called getUserById
  • $generator->insert_prefix = 'AddNew'; - function AddUser will be called AddNewUser (Yes, old default name was "insert")
  • $generator->list_prefix = 'GetArrayOf'; - function listByXXX will be called GetArrayOfByXXX
  • $generator->read_prefix = 'Fetch'; - function ReadUserRelatedData will be called FetchUserRelatedData
  • $generator->update_prefix = 'Store'; - function UpdateUser will be called StoreUser

back to top

Extending Code generator

This section briefly covers code generator's internal structure.

Code is divided into three logical sections, meant to separate gathering of data, its storage and generation of code:

There is a class that holds the data, CDBGeneratorData, which is our $data object, one where we add the tables.

There is family of classes that will gather data for the tables. They are inherited from CDataGatherer, and only one at the time of this writing is CAdoDbDataGatherer. These classes will go through the tables of $data object, and for each table fetch the data about its fields. I will eventually add code that can read MySQL DB without ADODB library, or read from an MDB XML file. If you wish to implement your own gatherer class, you only need to reimplement GatherData method. Take a look at existing class to see how data should be packaged.

Third classes are generator classes, which actually generate the PHP code using data from $data object. There is a number of generator classes and base classes, but only one for 'smart classes' is being actively maintained. However, this three-part structure was intended for easy extensibility, so that a generator that will create Java, VB, Delphi, C++ etc code can be easilly plugged into the system.



Site powered by RBI Software's Bonobo Framework, version 2.0.4.