This tool creates a PHP class to interface to a given database table. The PHP class is a simple relational object mapper. It maps table columns to class attributes. It provides basic CRUD methods (Create, Read, Update, Delete) as well as many utility methods such as CSV import and export. The class can handle sequences. It is clever about quoting values.
Download latest version from SourceForge http://sourceforge.net/project/showfiles.php?group_id=139904.
The tool is written in Python. Yes, this is a PHP code generator written in Python. Use the right tool for the job, I always say.
The PHP class that is generated will allow you to easily connect to a database table. The class uses the PHP PEAR::DB library, so it should work with many databases. The generated class provides methods to support the following features:
1. map table columns as PHP class attributes.
2. basic database CRUD (Create, Read, Update, Delete)
3. import and export the table to CSV files.
4. return an array of rows that match a given WHERE clause.
5. return an array of a single column from rows that match a given WHERE clause.
6. Generally immune from schema changes that add columns; reorder columns;
change column data type as long as the new type follows the same
quoting conventions as the original type -- for example, changing an interger to a real.
Other schema changes such as column delete will require that the PHP class be regenerated.
The script reads in a very simple description of the table and spits out PHP to stdout. The table description is stored in a "map" file. Map files are very simple and easy to write. All it does it define a bit of metadata about the table. The following example best explains the map file. In this example, say you had a PostgreSQL table defined as follows:
CREATE SEQUENCE customer_customer_id_seq
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;
CREATE TABLE customer (
customer_id integer DEFAULT nextval('customer_customer_id_seq'::text) NOT NULL,
name character varying(80) NOT NULL,
password character varying(255),
phone character varying(20) DEFAULT '' NOT NULL,
email character varying(255) DEFAULT '' NOT NULL,
active boolean DEFAULT true NOT NULL,
createdate timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
);
Example map file:
TABLENAME = 'customer'
KEY_FIELD = 'customer_id'
SEQUENCE_FIELD = KEY_FIELD
SEQUENCE = 'customer_customer_id_seq'
COLUMNS = [
('customer_id', 'integer', 'nextval(\'customer_customer_id_seq\'::text)' , True),
('name', 'character', None, True),
('password', 'character', None, False),
('phone', 'character', '' , True),
('email', 'character', '' , True),
('active', 'boolean', 'true' , True),
('createdate', 'timestamp', 'now()', True)
]
The map file is really just Python code. If you don't know anything about Python you should still be able to read and write a map file from the example above. Note that the COLUMNS variable is an array of tuples where each tuple is in the form
("column_name", "data_type", "default_value", flag_is_not_null)
The flag_is_not_null is a boolean that tells if the field may NOT set to Null. This may seem backwards, but that's how SQL is.
From this map a PHP class called "db_customer" would be generated. The following example shows how to update an existing record with a customer_id primary key of 23:
$my_record = new db_customer("pgsql://db_username:db_password@db_server/db_name");
$my_record->find(23);
$my_record->name = "Noah Spurrier";
$my_record->email = "noah@example.com";
$my_record->active = TRUE;
$my_record->update ();
The following example shows how to insert a new record. The primary key uses the default sequence generated by the database:
$my_record = new db_customer("pgsql://db_username:db_password@db_server/db_name");
$my_record->customer_id = db_customer::sql_default();
$my_record->name = "Noah Spurrier";
$my_record->password = "$1$4zzzicqM$hdOepLekRNlD34vNglVpa0";
$my_record->email = "noah@example.com";
$my_record->active = FALSE;
$my_record->insert ();
The following example retrieves all inactive users and prints their customer_id and name:
$my_record = new db_customer("pgsql://db_username:db_password@db_server/db_name");
$inactive_users = $my_record->get_set ("active = FALSE");
foreach ($inactive_users as $u)
{
print $u->customer_id . " " . $u->username . "\n";
}
At one time this supported Oracle. The problem with Oracle is the recordset metadata always gets converted to UPPERCASE. This makes it a little harder to use PEAR record sets. The following PHP-PEAR code fails on Oracle:
$row = $this->db->getAll ("SELECT my_id, my_string, my_flag FROM my_table WHERE my_id=" . $key_id);
print $row['my_id'];
print $row['my_string'];
print $row['my_flag'];
Istead, you must access the row record dictionary like this:
print $row['MY_ID'];
print $row['MY_STRING'];
print $row['MY_FLAG'];
I solved this at one time by converting key names to all lowercase (using the PHP function array_change_key_case) before copying to the class attributes, but this was hard to maintain, so I dropped it. It wouldn't be too hard to add this back in, but I no longer have an Oracle server to test against.
This assumes that the database column names can be valid PHP variable names.
This isn't a big budget object-relational mapping system. Relationships are not mapped. You get one class per table -- no class hierarchies. You have to manage your own relationships.
The generated class has all table properties statically mapped. There is no run-time dynamic database schema mapping. If you change the database schema you must update the map file and regenerate the class. This is either a limitation or a feature depending on how you look at it.
Inline SQL strings should probably be parameterized prepared statements.
Noah Spurrier
2003 Public Domain