Amortize

From RMBwiki

Automatic Management of Relational Tables

A MySQL library for PHP with automatic table management. Written by Rich Bellamy.

Released under the LGPL

Contents 1 Overview/Features 1.1 Class Example 1.2 Setting Attributes on a Amortize object 1.3 Getting attributes from a Amortize object 1.4 A few notes on the attribs() function 1.5 Saving 1.6 Table creation 1.7 Modifying tables 1.8 Relational Tables 2 Download/Installation 2.1 Prerequisites 2.2 Copy Code to server 2.3 Configuration 2.4 Test/Learn 2.5 Start using 2.6 Configuration overrides 2.7 API 3 Advanced 3.1 Column Inheritance 3.2 Externals 3.3 Delayed Loading 4 Planned Features 5 Demo 6 License

Overview/Features

Amortize is a PHP library for accessing, saving, modifying and managing data using SQL. What makes Amortize special is that it creates and modifies database tables automatically as your application code base changes.

Using Amortize allows the developer to forget about SQL altogether (for most applications) while developing and instead concentrate on developing the application. Any PHP developer who is comfortable with Object-Oriented Programming can use Amortize merely by creating classes which extend the AmortizeInterface class.

Instances of classes made to extend the AmortizeInterface class:

• Are able to save themselves as a row into a table in the database
• Can load themselves out of a table in the database
• Can create that table if it doesn't exist (and do so automatically if needed when saving)
• Can modify the table (add columns, etc.) if the class code has been changed to the point where saves or loads are failing, and then try the save or load again.
• Can easily associate themselves with other instances of AmortizeInterface extensions through automatically-created mapping tables (many-to-many relational mapping)
• Protect against SQL injection attacks by filtering all incoming data
• Ignore attribute setting that doesn't match with the defined columns
• More

Class Example

Consider this code. It is a sample of a class definition extending AmortizeInterface:

class Book extends AmortizeInterface {
  protected $table_name = "books";  // The table name that this class saves and loads in
  protected $table_columns = array( // The columns with types that make up the table
    'isbn'   => "varchar(20)",
    'author' => "tinytext",
    'title'  => "tinytext"
  );
  protected $autoprimary = true;    // Allows you to omit defining a primary key in $columns
}

We now have a class called Book. Please note that the $columns definitions is not something you need to fill out to match an existing database table. $columns is something that you make up in whatever way fits your programming ideas.

Setting Attributes on a Amortize object

Two methods for setting attributes of a Book object.

// Method 1
$myBook = new Book;
$myBook->title  = "A Wrinkle in Time";
$myBook->author = "Madeleine L'Engle";
$myBook->isbn   = "0440498058";

//Method 2
$myBook = new Book;
$info = array('title' => "A Wrinkle in Time", 'author' => "Madeleine L'Engle", 'isbn' => "0440498058");
$myBook->attribs($info);

Please note that you don't see public $title=null; public $author=null; etc. in the class definition. It is not needed. Amortize uses __get and __set to avoid requiring the user to have to make those definitions.

Note further that $myBook->foo = "bar"; as well as $myBook->attribs(array('foo' => "bar")); will be ignored because 'foo' does not exist in the $columns array. Amortize filters out attributes that are not known members to their table columns list With that in mind, consider this code:

$myBook->attribs($_POST);

Yes, things really can be that easy.

Getting attributes from a Amortize object

This is very much like setting attributes, in reverse.

// Method 1
$author = $myBook->author;
$title  = $myBook->title;
echo "{$title}, written by {$author}.";

// Method 2
$info = $myBook->attribs();
echo "{$info['title']}, written by {$info['author']}.";

A few notes on the attribs() function

AmortizeInterface::attribs() can be used to set or get the entire collection of attributes at once, with filtering. Passing it an array of attributes is how you set attributes for the objects. Passing it nothing is the typical way of retrieving the attributes, however it always returns the new attributes, even when changing them, so something like this:

$myBook->attribs($_POST);   // Change the attributes for $mybook to what was posted
$info = $myBook->attribs(); // Get the new attributes for $myBook

Can be shortened thus:

$info = $myBook->attribs($_POST); // Set and get the new attributes for $myBook

AmortizeInterface::attribs() by default will NOT change any primary keys for an object, unless you include an optional second parameter set to true.

$myBook->attribs(array('ID'=>"100"));        // Won't work
$myBook->attribs(array('ID'=>"100"), true);  // Will work and will overwrite any existing ID=100 book in the database if saved
$myBook->attribs(array('ID'=>null), true);   // Will work and will create new book in the database if saved

Saving

This all is very straightforward and common OOP so far. The magic comes in when you want to insert your data into the database:

$myBook->save();

Assuming that Amortize has been configured correctly, calling the save method on the object above will generate a SQL command that looks something like this:

REPLACE INTO books
  (`isbn`, `author`, `title`)
  VALUES
  ("0440498058", "Madeleine L\'Engle", "A Wrinkle in Time")

Table creation

If, during a save or load operation, an error is generated because the table doesn't exist, Amortize creates the table:

CREATE TABLE `books` (
  `ID` bigint(20) unsigned NOT NULL  auto_increment,
  `isbn` varchar(20)   ,
  `author` tinytext   ,
  `title` tinytext   ,
  PRIMARY KEY (`ID`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1

And then tries again to save.

Modifying tables

If, during a save or load operation, an error is generated and the table does exist, the table structure is analyzed using the SQL command DESCRIBE. If the saving object's table definition does not match the actual table in the database, the database table will be modified to match what is hard-coded into the class, and the save will be re-attempted.

For example, lets say the library inventory program you are running needs to be updated to be able to store the published year for each book. You edit the Book class definition from above to look like this:

class Book extends AmortizeInterface {
  protected $tablename = "books";
  protected $needID = true;
  protected $columns = array(
    'isbn' => "tinytext",
    'author' => "tinytext",
    'title' => "tinytext",
    'pubyear' => "year"
  );
}

Now, what do you have to do to the database itself? Nothing. The first time you call save() on a Book object, the table will be modified for you.

Relational Tables

Amortize can also create relational tables automatically. This is outlined pretty well in the demo.

Example:

// Store and Product both extend AmortizeInterface
$store = new Store();
$product = new Product();

// This command will create a relational database table between the stores table and the products table, and insert the primary keys for each into a row.
$store->link($product);

// This command will return an array of all Product instances that were previously linked to this instance of Store.
$store->getLinks("Product");

// This command will return an array of all Store instances who previously linked to this instance of Product
$product->getBackLinks("Store");

Download/Installation

Amortize is available through GitHub:

GitHub page for Amortize

Prerequisites

• PHP 5 or higher
• MySQL

Copy Code to server

The source tree for Amortize looks like this:

amortize/
 |--class_AmortizeExecution.php
 |--class_AmortizeFeatures.php
 |--class_AmortizeInterface.php
 |--class_AmortizeLink.php
 |--class_AmortizeObject.php
 |--class_AmortizePreparation.php
 |--classes_Extras.php
 |--COPYING
 |--COPYING.LESSER
 |--amortizeconfig.php.tmpl
 |--amortize.php
 |--amortizeutils.php
 |--display_test.php
 |--Doxyfile
 |--example.php
 |--License.txt
 |--test.php

You can place it anywhere you like, standard place would be inside the DOCROOT but that is not necessary if you know what you are doing.

Configuration

Take the file called amortizeconfig.php.tmpl and edit it to your specifications. It looks like this:

<?php
        // Fill me in
        $sqlHost     = "";     // Hostname of your SQL server, or "localhost"
        $sqlUser     = "";     // SQL user name
        $sqlPass     = "";     // SQL password
        $sqlDatabase = "";     // Database name on SQL server
        $sqlPrefix   = "";     // A prefix to use on all table names, optional but it help you sort your tables
        $dbmDebug    = false;  // Enable debugging output
        $dbmAutoDrop = false;  // Automatically drop columns that you remove from your table_defs (SCARY)
        $dbmTableDrop = false; // Enable the AmortizeExecution::dropTable() function.

        // Allow config consts defined before this file. Needed to run test.php.  Possible security risk.
        $dbmAllowConfOverrides = true;

?>

Save the file as amortizeconfig.php one level up from the Amortize source files, or basically save it in the same place that the Amortize folder is saved. This is the default location for the configuration file and is where Amortize looks for it. Placing the configuration file one level up allows you to upgrade Amortize by simply replacing the Amortize folder with a new version.

If you want to place the configuration elsewhere (like outside your DOCROOT), you could make a false amortizeconfig.php in the default location which simply includes the real file outside the DOCROOT. Alternatively, you can modify Amortize to look elsewhere for the configuration: the pertinent code is at the top of class_AmortizeExecution.php.

Test/Learn

Once you have installed and configured, you can test that Amortize is working properly by opening yourserver.com/amortize/tests/ in your browser (if you installed in the DOCROOT).

The tests collection allows you to view example source code and that code's output side by side, with full debugging output enabled. So you can see the example code and all the sql statements being run, as well as a little insight into what is happening.

There is a test collection setup live here

Start using

To expose the classes of Amortize to your code, merely do

include_once 'amortize/amortize.php';

Configuration overrides

Individual scripts can override the configuration settings in amortizeconfig.php by setting specific globals before they include the amortize.php file.

There is a great example of this in the test.php file, where the debug option and table prefix options are overridden.

A full list of globals for configuration overrides can be gleaned from code at the top of the top of class_AmortizeExecution.php

API

Amortize comes almost fully-documented in the Doxygen style, and includes a Doxyfile in the source code so you can generate your own Documentation/API.

Install Doxygen on your server along with Dot and Graphviz, then run doxygen on a command line from the amortize folder.

Alternatively, you can use the online API

Advanced

Column Inheritance

Descendants of AmortizeInterface share their column definitions (and externals, when externals has been implemented) with their own descendants. Descendants can and should have a different table name than their parents.

Example:

class ComicBook extends Book {
  protected $tablename = "comics";
  protected $columns   = array(
    'artist'   => "varchar(50)",
    'colorist' => "varchar(50)"
  );
}

In the above example, the comics table would have isbn, author,title, pubyear, artist, and colorist.

This behavior can be blocked by setting the member variable $baseclass in the Class that should be the base for column definitions. most of the time, it will be

 protected $baseclass = __CLASS__; 

Externals

The externals feature allows you to define attributes of your class which are actually themselves instances of Amortize classes.

The format is similar to the table_columns array: an associative array where the key is the name of the attribute and the value describes the type of data stored in that attribute. In this case, the value is simply the name of the class that the attribute will be an instance of.

class Person extends AmortizeInterface {
  protected $autoprimary=true;
  protected $table_columns = array('firstname' => 'varchar(20)', 'lastname' => 'varchar(20)');
}
class Restaurant extends AmortizeInterface {
  protected $autoprimary=true;
  protected $table_columns = array('name' => 'varchar(50)', 'street_address' => 'tinytext');
  protected $externals = array('owner' => "Person");
}

In the example above, any instance $res of Restaurant will have an instance of Person that can be accessed via $res->owner or under the 'owner' key of the array returned by $res->attribs().

You can chain like this:

echo "{$res->owner->firstname} {$res->owner->lastname} owns {$res->name}";

Amortize modifies the table columns array transparently for the hosting Object, creating table columns capable of storing the primary key or keys of the external object. Amortize objects which have externals defined will automatically save the primary key(s) of the external classes into their own (automatically-generated) table columns, and therefore are able to recall identical instances of their external objects across save/load cycles.

External objects are not saved automatically when the holder is saved. This is to prevent cascading saves which could be disastrous if a linked list has been implemented using externals, especially a ringed list. Because of this, you need to save your externals manually:

$res->owner->save();

will work fine.

Delayed Loading

Previously, attributes for an object were loaded from the database upon object creation. Now, the data is only loaded when needed (when AmortizeFeatures::getAttribs() is called, for example). This will cuts down on unneeded database queries, especially when using externals.

Planned Features

• Convert the execution layer to the PDO library so we can support more than just MySQL
• Add some type of method for getting a database-valid primary key, without saving if it's not needed. Defer saving externals as well, to avoid cascading saves.
  • Currently, the method used to guarantee a valid primary key used by the linking and external functions is to force a save, then read the key.

Demo

See Amortize in action by viewing the tests script on a live installation: www.richbellamy.com/amortize/tests/

You can run these tests in a couple minutes on your own server, follow the README.quickstart guide.

License

This work is licensed under the GNU Lesser General Public License (LGPL)