Database Magic

From RMBwiki

Contents 1 Overview 2 Setup 3 Examples 3.1 Creating Your Classes 3.1.1 Managed Tables 3.1.2 Unmanaged Tables 3.1.3 Which method to use? 3.2 Setting, Saving and Retrieving data 3.2.1 Setting 3.2.2 Saving 3.2.3 Retrieving 3.2.4 Modifying Data already saved in the Database 3.3 Relational Databases 3.3.1 Requirements 3.3.2 Solution 3.3.3 Re-ordering Relational Tables 4 Advanced Techniques 4.1 Table column data types ARE customizable 4.2 Defining your own primary keys 4.3 Extending Classes to inherit table definitions 5 Obtaining Source 6 License

Overview

Database Magic is a PHP library for accessing, saving, modifying and managing data using SQL. It allows the developer to forget about SQL while developing and instead concentrate on developing his web-based apps. All the SQL backend is taken care of by Database Magic, including table creation and modification, even as the code changes, Database Magic can modify the SQL database to accomodate. Database Magic can be used to create new tables automatically, or can be easily configured to use existing tables in your database. The result is faster development, simpler code, and instant code rollouts to users.

Setup

I will fill this in later, but basically you drop databasemagic in your php source tree, and fill in one configuration file with the host, username, database name and password, then you are good to go.

Examples

These examples assume you set up DbM correctly per the setup instructions.

Creating Your Classes

Programming with Database Magic involves defining a set of classes, each one extending the base class DatabaseMagicObject. Objects that you create as instances of these classes can be told to perform specific actions to the database with a list of simple methods like load(), save(), etc.

Each different class represents a table in your database. As such, the classes are differentiated from each other by you defining in the class code both the name of the table how the table and its columns are defined.

The only thing you need to do to define your class is to define a private member variable called $table_defs, which describes the database table that class will be using.

Managed Tables

Using this method of defining your classes, you will hard-code the database table definitions into your PHP source code. Database Magic will use that information to create and manage the database tables. If you ever make a change to the table definitions in your source code, Database Magic will automatically update the database table for you, either by adding or modifying columns in the table. More on that later.

This method is best when you are developing software from scratch and want to be able to forget about having to modify the database manually as the project grows and changes.

Here is a simple example. You want to create a database of books. You would like to save the ISBN, title, author, and published date in your database. You will store this information in a table called myBooks.

Using DbM, your Book class might look like this:

class Book extends PrimaryDatabaseMagicObject {
	protected $table_defs = array(
		'myBooks' => array(
			'isbn'   => "varchar(20)",
			'author' => "tinytext",
			'title'  => "tinytext",
			'pubyear' => "year"
		)
	);
}

Note: Users with MySQL experience will recognize varchar, tinytext and year as MySQL data types. They are included here because they dictate how the data is to be stored in the database. For example, when we get to saving a Book into the database, the ISBN info will be saved as a varchar(20). Users with no SQL experience need not worry too much about these things, as we will see later

Unmanaged Tables

Another way to use Database Magic is to simply tell it the name of the table in your database you want this particular class to load and save in. This works well if you have existing tables, perhaps tables created by third-party software. This also would work for you if you prefer managing your database tables yourself and want to do a minimum of customizing through PHP source.

You can tell Database Magic that you want to use a table that is already in the database like this:

class Book extends PrimaryDatabaseMagicObject {
	protected $table_defs = "myBooks";
}

When you define a class in this way, DatabaseMagic will use the SQL command DESCRIBE to teach itself how to interface with the table.

Which method to use?

Each method has its advantages and disadvantages. Which method you use could best be determined by the circumstances you find yourself in, as well as what you are trying to accomplish.

Using the Managed Tables method, DatabaseMagic will create your database tables for you the first time you try to save some data. That's right, you never have to manually go into the database to set it up for DbM. Also, DatabaseMagic will continue to keep your tables updated for you in the event that your project grows and changes.

For example, if your Book class needs a new field called "publisher", you only need to add

'publisher'=>"tinytext",

into the $table_defs array in your source code, and then go on your way writing code as if the myBooks table already has a publisher column, because it will as soon as you save some data.

This method is far and away the best method to use when building new database-driven apps.

Using the Unmanaged Tables method, you will need to have the tables you want to use already in your database, as DbM can't create them for you. Likewise, DbM won't be able to create new columns in existing tables. This method works pretty well if you are just creating quick classes to make it easier to manipulate tables that were generated by third-party apps. This method is not recommended for use in creating new apps.

Setting, Saving and Retrieving data

Setting

You have defined a Book. Books have these attributes: ISBN, Author, Title, Publish Year. You can set and get these attributes from the Book using the setAttribs() and getAttribs() functions of Book. See here:

$aBook = new Book();

$bookAttributes = array(
	'isbn'   => "0-7414-4456-9",
	'author' => "Larry Correia",
	'title'  => "Monster Hunter International",
	'pubyear' => "2007"
);

$aBook->setAttribs($bookAttributes);

$actualAttributes = $aBook->getAttribs();

/****** print_r will output the structure of this array  ******/
print_r($actualAttributes);

That code outputs this, generated by print_r:

Array
(
    [isbn] => 0-7414-4456-9
    [author] => Larry Correia
    [title] => Monster Hunter International
    [pubyear] => 2007
)

Attribute Filtering

setAttribs() will drop any attributes that are not explicitly allowed in the table_defs array. So for example, if you do this:

$book->setAttribs(array('publisher' => "RMB Publishing"));

It won't accomplish anything.

This comes in very handy for doing something like this:

$book->setAttribs($_GET);
$book->save();

Instant form processing, input filtering and database insertion (of course DbM does all the data scrubbing against SQL injection when it saves)!

Saving

Simple enough so far, right?

Let's make a simple change. I am going to insert this code in between the setAttribs() and getAttribs() function calls in the example above, and re-run the code:

$aBook->save();

Just one thing before I run the code though, I want you to first take a peek at my database:

mysql> show tables;
Empty set (0.00 sec)

mysql>

Desolate.

Okay. We run the code with the save() function call added in, we see that our output is slightly different:

Array
(
    [isbn] => 0-7414-4456-9
    [author] => Larry Correia
    [title] => Monster Hunter International
    [pubyear] => 2007
    [ID] => 1
)

Not only that, but look at our database:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| myBooks          |
+----------------+
1 row in set (0.00 sec)

Hmm, that's funny, there is a new table where before there was none. Let's see what's inside.

mysql> describe myBooks;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| isbn    | varchar(20)         | YES  |     | NULL    |                |
| author  | tinytext            | YES  |     | NULL    |                |
| title   | tinytext            | YES  |     | NULL    |                |
| pubyear | year(4)             | YES  |     | NULL    |                |
| ID      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

Look familiar? Lets see the data in the table:

mysql> select * from myBooks;
+---------------+---------------+------------------------------+---------+----+
| isbn          | author        | title                        | pubyear | ID |
+---------------+---------------+------------------------------+---------+----+
| 0-7414-4456-9 | Larry Correia | Monster Hunter International |    2007 |  1 |
+---------------+---------------+------------------------------+---------+----+
1 row in set (0.03 sec)

mysql> 

The book has been saved into the database. Not only that, but DbM created the table to save the book based on your $table_defs specifications!

Another thing that happened is, the Book received a new Attribute called "ID". ID is the name of the books table's Primary Key. if you are SQL-savvy, you will know all about primary keys. If you are not SQL-savvy, then suffice it to say that a primary key is just a unique identifier for each row in the database that allows you to always look up the row you want, as well as make reliable relational tables (which we will get to later). Primary keys usually auto-increment when saving a new row to the database, and are not re-used.

Now, lets save a second book into our database, to illustrate further the concept of a primary key.

$aBook = new Book();
$bookAttributes = array(
	'isbn'   => "9781581603071",
	'author' => "Jeff Cooper",
	'title'  => "The Art of the Rifle",
	'pubyear' => "2002"
);
$aBook->setAttribs($bookAttributes);
$aBook->save();
$actualAttributes = $aBook->getAttribs();
print_r($actualAttributes);

And we see this for our output:

Array
(
    [isbn] => 9781581603071
    [author] => Jeff Cooper
    [title] => The Art of the Rifle
    [pubyear] => 2002
    [ID] => 2
)

Notice that the primary key ("ID") is set to 2 this time. . . It matches what is in the database:

mysql> select * from myBooks;
+---------------+---------------+------------------------------+---------+----+
| isbn          | author        | title                        | pubyear | ID |
+---------------+---------------+------------------------------+---------+----+
| 0-7414-4456-9 | Larry Correia | Monster Hunter International |    2007 |  1 |
| 9781581603071 | Jeff Cooper   | The Art of the Rifle         |    2002 |  2 |
+---------------+---------------+------------------------------+---------+----+
2 rows in set (0.01 sec)

Retrieving

Now that Monster Hunter International is in our database, we can load it from the database at any time by simply passing its primary key value to the Book constructor:

$greatBook = new Book(1);
$actualAttributes = $greatBook->getAttribs();
print_r($actualAttributes);

Here is the output:

Array
(
    [isbn] => 0-7414-4456-9
    [author] => Larry Correia
    [title] => Monster Hunter International
    [pubyear] => 2007
    [ID] => 1
)

And of course this:

$greatBook = new Book(2);
$actualAttributes = $greatBook->getAttribs();
print_r($actualAttributes);

Will yield this:

Array
(
    [isbn] => 9781581603071
    [author] => Jeff Cooper
    [title] => The Art of the Rifle
    [pubyear] => 2002
    [ID] => 2
)

Modifying Data already saved in the Database

The Art of the Rifle wasn't actually published in 2002. I have the ISBN and publishing date of the special color edition in the database. I should probably fix the title:

$greatBook = new Book(2);
$greatBook->setAttribs(array('title' => "The Art of the Rifle: Special Color Edition"));
$greatBook->save();

That wasn't so hard.

Database before:

+---------------+---------------+------------------------------+---------+----+
| isbn          | author        | title                        | pubyear | ID |
+---------------+---------------+------------------------------+---------+----+
| 0-7414-4456-9 | Larry Correia | Monster Hunter International |    2007 |  1 |
| 9781581603071 | Jeff Cooper   | The Art of the Rifle         |    2002 |  2 |
+---------------+---------------+------------------------------+---------+----+

And after:

+---------------+---------------+---------------------------------------------+---------+----+
| isbn          | author        | title                                       | pubyear | ID |
+---------------+---------------+---------------------------------------------+---------+----+
| 0-7414-4456-9 | Larry Correia | Monster Hunter International                |    2007 |  1 |
| 9781581603071 | Jeff Cooper   | The Art of the Rifle: Special Color Edition |    2002 |  2 |
+---------------+---------------+---------------------------------------------+---------+----+

Relational Databases

DatabaseMagic really shines when it comes to relational databases. If you are not SQL-savvy, relational databases link more than one object of a different (or the same) type together.

Lets say that we want to allow users to write reviews of the books in our database. We can make a simple class for that easy enough:

class Review extends PrimaryDatabaseMagicObject {
	protected $table_defs = array(
		'bookReviews' => array(
			'reviewtext' => "text",
			'reviewer'   => "tinytext",
			'revdate'    => "datetime"
		)
	);
}

Requirements

As far as Books vs. Reviews are concerned, there are a few rules regarding their relationship.

• A Book exists on it's own merit, whereas a Review needs a Book for the Review to have any meaning at all
• A Book may have many reviews, whereas a Review is only about one Book
• You would normally get to a Review by going though a Book. "Here is a book and here are the reviews about the book."

What we need:

• When we create a Review, we need a way to "attach" it to the Book that it is about.
• We need to be able to attach Multiple Reviews to one book
• A Book should be able to retrieve a listing off all of its Reviews

Solution

All DbM-extended objects inherit a pair of functions that will solve these requirements: adopt() and getChildren(). Here is an example:

$greatBook = new Book(2);  // Loads Cooper's book from the database
$myReview = new Review;    // Create a new review
$myReview->setAttribs(
	array(
		'reviewtext' => "The late Col. Cooper does a good job explaining the basics of marksmanship in this fine book.",
		'reviewer'   => "Rich Bellamy",
		'revdate'    => "2008-02-03"
	)
);
 // Saves the review and uses the relational database to link
 // this review with this book (creating the relational databases as needed)
$greatBook->adopt($myReview);

Look at what is going on with the database!

mysql> show tables;
+----------------------------+
| Tables_in_test             |
+----------------------------+
| bookReviews                |
| map_myBooks_to_bookReviews |
| myBooks                    |
+----------------------------+
3 rows in set (0.00 sec)

mysql> select * from myBooks;
+---------------+---------------+------------------------------+---------+----+
| isbn          | author        | title                        | pubyear | ID |
+---------------+---------------+------------------------------+---------+----+
| 0-7414-4456-9 | Larry Correia | Monster Hunter International |    2007 |  1 |
| 9781581603071 | Jeff Cooper   | The Art of the Rifle         |    2002 |  2 |
+---------------+---------------+------------------------------+---------+----+
2 rows in set (0.00 sec)

mysql> select * from bookReviews;
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
| reviewtext                                                                                    | reviewer     | revdate             | ID |
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
| The late Col. Cooper does a good job explaining the basics of marksmanship in this fine book. | Rich Bellamy | 2008-02-03 00:00:00 |  1 |
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
1 rows in set (0.01 sec)

mysql> select * from map_myBooks_to_bookReviews;
+----------+---------+----------+
| parentID | childID | ordering |
+----------+---------+----------+
|        2 |       1 |        0 |
+----------+---------+----------+
1 rows in set (0.00 sec)

The bookReviews table was created as expected, and also a mystery table called map_myBooks_to_bookReviews was created when we did the adopt() proceedure. The row you see from that table means this: The Review with ID of 1 belongs to the Book with the ID of 2.

Here's what we get when I add two more reviews for "Art of the Rifle", a review for MHI, and then another review for AotR:

mysql> select * from bookReviews;
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
| reviewtext                                                                                    | reviewer     | revdate             | ID |
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
| The late Col. Cooper does a good job explaining the basics of marksmanship in this fine book. | Rich Bellamy | 2008-02-03 00:00:00 |  1 |
| Loved it!                                                                                     | Short Review | 2008-02-03 00:00:00 |  2 |
| Very good book tog et you into marksmanship.                                                  | Shooter      | 2008-02-03 00:00:00 |  3 |
| Awsome job, Larry!                                                                            | Rich Bellamy | 2008-02-03 00:00:00 |  4 |
| RIP, Colonel.  Thanks for the great book.                                                     | A guy        | 2008-02-03 00:00:00 |  5 |
+-----------------------------------------------------------------------------------------------+--------------+---------------------+----+
5 rows in set (0.01 sec)

mysql> select * from map_myBooks_to_bookReviews;
+----------+---------+----------+
| parentID | childID | ordering |
+----------+---------+----------+
|        2 |       1 |        0 |
|        2 |       2 |        0 |
|        2 |       3 |        0 |
|        1 |       4 |        0 |
|        2 |       5 |        0 |
+----------+---------+----------+
5 rows in set (0.00 sec)

mysql>

See what is happening here? The first three reviews are for AotR, which is book 2. The 4th review is for Book 1, MHI. The final review is another review for AotR. This is all reflected very clearly in map_myBooks_to_bookReviews.

getChildren()

getChildren() is what an object uses to retrieve from the database a collection of objects it has previously adopted. It only retrieves one type of object at a time. getChildren can be called in two ways: either with a string identical to the name of the class of objects being returned, or with a prototype object as an example. Both these will have the same result:

$book->getChildren("Review");
$book->getChildren(new Review());

getChildren returns an array of already-loaded objects.

$book = new Book(2);  // Load AotR
$bookReviews = $book->getChildren("Review");
foreach ( $bookReviews as $review ) {
	$info = $review->getAttribs();
	echo "Book Review by ".$info['reviewer']."\n";
	echo "\"".$info['reviewtext']."\"\n\n";
}

Gives us this great output:

Book Review by Rich Bellamy
"The late Col. Cooper does a good job explaining the basics of marksmanship in this fine book."

Book Review by Short Review
"Loved it!"

Book Review by Shooter
"Very good book tog et you into marksmanship."

Book Review by A guy
"RIP, Colonel.  Thanks for the great book."

Easy enough for ya?

Re-ordering Relational Tables

Advanced Techniques

Table column data types ARE customizable

In previous examples, we only set the data type of the table columns (varchar, tinytext, etc.). MySQL-savvy users will know that there are many more options that you can set on each column, and may wonder how that plays in to DbM. The truth is, you can set all the MySQL options by replacing the data type in the table_defs array with another array that defines the column. The previous example was just shorthand for accepting the defaults.

The format of the column-defining array is this:

array("Type", "Null", "Key", "Default", "Extra")

You may notice that it mimics the output of the mysql command "DESCRIBE". This comes in very handy if you want to quickly write a class that is custom designed to interface with an existing third-party database. Here is a brief description of what they mean.

• Type
  • The data type of the column as we used it before, varchar, tinytext, etc.
  • See the MySQL documentation for more help on this
• Null
  • Simple. Put either "YES" or "NO" here to indicate if NULL is an acceptable entry in this column
• Key
  • Set this to "PRI" if you want to use this column as a primary key.
  • See the MySQL documentation for more help on this
• Default
  • When a new row is created without the value for this column being specified, what is the default value to set to?
• Extra
  • This is where "auto_increment" and other extras goes.
  • See the MySQL documentation for more help on this

Here is an example of how to change the table defs for Book so that there is no nulls allowed in the ISBN, and the ISBN will default to "unknown" when a blank is inserted.

Original:

	protected $table_defs = array(
		'myBooks' => array(
			'isbn'   => "varchar(20)",
			'author' => "tinytext",
			'title'  => "tinytext",
			'pubyear' => "year"
		)
	);

More Specific:

	protected $table_defs = array(
		'myBooks' => array(
			'isbn'   => array("varchar(20)", "", "NO", "unknown", ""),
			'author' => "tinytext",
			'title'  => "tinytext",
			'pubyear' => "year"
		)
	);

Defining your own primary keys

If you don't want to use "ID" as your primary key, you need only to define one of your columns with "PRI" in the third parameter of the column definition array. For example, the ISBN column would have been an excellent candidate for being the primary key of our myBooks database, since the ISBN number of a book is unique to each book.

If we had just defined our Book class table_defs like this:

	protected $table_defs = array(
		'myBooks' => array(
			'isbn'   => array("varchar(20)", "NO", "PRI", "", ""),
			'author' => "tinytext",
			'title'  => "tinytext",
			'pubyear' => "year"
		)
	);

Then ID would never have made it into our table in the first place since we already had a primary key. Our table which now looks like this:

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| isbn    | varchar(20)         | YES  |     | NULL    |                |
| author  | tinytext            | YES  |     | NULL    |                |
| title   | tinytext            | YES  |     | NULL    |                |
| pubyear | year(4)             | YES  |     | NULL    |                |
| ID      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
+---------+---------------------+------+-----+---------+----------------+

Would instead look like this:

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| isbn    | varchar(20)         | NO   | PRI | NULL    |                |
| author  | tinytext            | YES  |     | NULL    |                |
| title   | tinytext            | YES  |     | NULL    |                |
| pubyear | year(4)             | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

Also, we could load a book by ISBN instead of our internal ID scheme:

$isbn = "0-7414-4456-9";
$book = new Book($isbn);

Now, there may be a time when you want to make a simple table with no primary key. . . but that pesky ID column keeps popping up. No problem. Just make a class that extends DatabaseMagicObject instead of PrimaryDatabaseMagicObject. PrimaryDatabaseMagicObject is identical to DatabaseMagicObject, except for the fact that PrimaryDatabaseMagicObject will inject the ID primary key into your table_defs if you don't specify a primary key yourself. DatabaseMagicObject doesn't do this.

Extending Classes to inherit table definitions

Obtaining Source

Source code for this work is available through subversion at:

svn://svn.richbellamy.com/DatabaseMagic/trunk

License

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