Introduction

Object-relational mapping (ORM) frameworks have been around for several years now and for some people, ORM is already outdated by now. As we have seen with other technologies and concepts before, PHP is not exactly what we call an early adopter among the programming languages. Thus it took some time for ORM to grow up in the PHP context.

There have been some frameworks before Doctrine 2 that implement ORM (remember e.g. Propel) specific tasks but most of them lack the required maturity to be used in large projects. With Doctrine 2, PHP takes a huge step into the right direction – Doctrine 2 is fast, extensible and easy to use.

This article will take you on a tour through the main concepts of Doctrine 2 in the first part and then explain how to use it in a real world application in the second part. Since at the time of writing Zend Framework 1.11.xx (ZF) is very popular, we will integrate Doctrine 2 into a ZF project.

Basic Concepts

To understand Doctrine 2, we have to take a look at some relevant terms (or in this case objects), study their behavior and practice their usage. We start with some introductory phrases on ORM systems and then go on to the concepts underlying Doctrine 2: Entity Objects, the Entity Manager, Repositories and Proxies.

Object-relational Mapping

Since the beginning of Object-Orientation, people had to manage the persistence of their application's state resp. their objects. In the context of Web Application Development, this usually involves a Database server which is being consulted using a Query Language. One example for this pattern is a PHP application that uses some kind of SQL server by sending SQL queries to it. Another one is an application using a CouchDB server by querying it via its REST API.
Due to the author's laziness, we will talk in terms of relational databases from now on. Keep in mind, that you can accomplish almost everything mentioned here with NoSQL databases, too.

ORM relates value objects that exist in an application's business logic to database records. Thus every object that should be persistent is saved in one row of a database table. The most common approach is to map classes to tables and the classes' objects to rows in the these tables.
Besides writing objects to a database, ORM systems are also intended to ease the process of finding data stored in the database. When talking in terms of ORM, finding data always means making the framework fetch one or many objects that meet a certain criteria.

Entity Objects

The objects that are being managed by an ORM system are called Entity Objects. Every entity object relates to one entry in a table. In Doctrine 2, the classes that represent entities do not have to fulfill special requirements like inheriting from a certain super class (as you might have seen in other database abstraction frameworks like Zend_Db). When creating a new entity class with Doctrine 2, all you have to do is to write down a regular PHP class with properties. Besides this, you have to provide some hints on how these attributes should be persisted. The information how entity attributes relate to columns in the DB is called Metadata. Metadata can be described in different ways: By default there are metadata drivers for descriptions in XML, YAML and PHP. The fourth and most popular driver is based on DocBlock annotations (since in PHP, annotations aren't a language feature as in Java (see Wikipedia), they are contained by the classes' and attributes' DocBlocks). We will use annotations to describe our entities metadata. To get an impression on how easy this is, take a look at the following example.

This example contains all it needs to tell Doctrine 2 about the new entity User. With this class, you can create, find, delete and modify user objects and persist their state to the underlying database. But keep in mind: as long as you don't need any persistence features, you can use your user objects just like any other objects!

The next two objects resp. object types we will describe are responsible for doing the ORM functionality: persisting and finding.

The Entity Manager

To use ORM functionality, the Entity Manager (Doctrine\ORM\EntityManager) is the main access point to Doctrine 2. The entity manager is responsible – as you might have guessed – for managing entities and for building a facade for the whole framework. To accomplish its tasks, the entity manager uses some helpers. The Unit of Work object for example collects entities that should be written back to the database and is capable of doing this in batches. This way, database operation can be executed with almost no overhead and therefore are really fast.

Another dependency of the entity manager is the Event Manager. To be as extensible as possible, Doctrine 2 comes with an event system that publishes all important state changes to the outside as events. You can register for such events and extend the life cycle of your entity objects at one single point.

The entity manager's API combines methods for managing entities (find, persist, contains, copy, detatch, merge, remove and refresh), methods that control the use of transations (beginTransaction, commit, flush, rollback and transactional) and some helper methods for creating custom queries and accessing some of the entity manager's dependencies.

The following example shows how to query an object from the entity manager, modify it and write the changes back into the database.

Creating a new persistent object is almost as easy as modifying it:

Repositories

For finding entities, Repositories are used. Every entity class has its own repository which is responsible for finding entities of that type. By default, repositories have some handy methods for fetching entities that match certain criteria:

• find: Finds an entity by its primary key / identifier
• findAll: Finds all entities of the repository's entity type
• findBy / findOneBy: Finds all resp. one entity that matches the passed criteria:
  
• findBy<attribute> / findOneBy<attribute>: Magic methods that ease the filtering by a single attribute:
  

To access a repository, all you have to do is ask the entity manager for one. If you have implemented your own repository, it will be returned by Doctrine\ORM\EntityManager::getRepository(). Otherwise, Doctrine 2 will provide a generic repository. The main reason to implement custom repository classes is to group custom queries for an entity type to make them reusable. For custom query logic, there are several mechanisms you can use: You can either use Doctrine's query builder that implements an API similar to Zend_Db_Select or queries written in the Doctrine Query Language (DQL) or you can even execute plain SQL queries. With these options, it is also possible to migrate old applications which use complex queries by just wrapping these queries into the methods of custom repositories.

Proxies

When traversing a graph of entity objects (which is required when entities are having relations to other entities), it would be very expensive (in the sense of “requiring many database queries”) to fetch every depending entity with an additional query. Therefore Doctrine 2 uses the concept of Proxy objects that represent regular entity objects which have not been populated with data from the database. Take a look at the following example where the entity Group aggregates a list of User objects in its member property. When accessing the members list, Doctrine 2 provides a collection of proxy objects instead of complete User objects. When an object of this collection is being asked for one of its properties, Doctrine loads the object's data from the database. This way, the users' data is not loaded until it is really needed.

Advanced Mapping Concepts

This section describes some advanced concepts that are required when mapping entity classes that have relationships to other entity classes. Possible relationship types are association and inheritance. Inheritance is the mechanism used for representing subtypes in object-oriented programming languages. An example would be a class User that implements methods every user of a software should have and a class Administrator inheriting from User that adds methods for determining the administrator's access rights.

Association is a weaker relation type. It means that an entity object can be related to other entity objects of other types. In terms of relational databases, there are three types of association which differ in the number of entities an object is related to: 1:1, 1:n and n:m relationships. n and m are placeholders and mean multiple.

Association

To put objects of an entity type into relation, you just have to mention this relation in the entity class' mapping information. The simplest case is a unidirectional 1:1 relationship. In the following example we describe a User entity which has its access information (user credentials) encapsulated into another entity class called UserCredential. Since every user has at most one credential object and every credential object may only be associated to one user object, this is a 1:1 relationship.

If the relationship should be bidirectional, include the OneToOne attribute in the other class, too, and add an attribute which denotes the attribute of the other entity that mapps the related object:

This way, you can access the user object from the credentials object, too.
Most of the times, developers have to deal with relationships which include many objects on at least one side. These relationships are called 1:n or n:m relationships. This means that either one or multiple entities are standing in relationship with an arbitrary number of entities of another type. To accomplish this, you have to use the mapping keywords OneToMany or ManyToMany when describing your entities. Besides that, the mapping works the exact same way as with 1:1 relationships.

There are however some tricks you should know when dealing with collections of associated entity objects. Consider the following relationship between the entity classes User and Group:

When a group has at least one member, the group object will have a collection of the type Doctrine\Common\Collections\ArrayCollection set as its members property. This collection contains all user objects (or proxy objects as we have seen before) and can be modified intuitively with the methods add and removeElement. To honor object-orientation, you might want to introduce custom methods for these tasks. If you do so, you get into trouble when the group object does not have any users associated. In this case, the collection will simply be set to null. To avoid checks whether the collection has already be initialized, you should to this by yourself in the entity class' constructor:

It is also important to notice that one entity has to update the other entity's state as well when a relationship between to objects is created or removed. Take care to do this only in one class to avoid endless recursion loops! This class is called the Owning Side of the relationship. When implementing a bidirectional relationship, the other class is called the Inverse Side. It is important to determine owning and inverse side and implement the the classes accordingly to avoid greater trouble during debugging.

There are some more features implemented by Doctrine 2 enabling developers to specify their entities' relationships including sorting, pre-fetching and indexing. These topics are not covered in this article but are explained very understandable in the Doctrine 2 documentation.

Inheritance

Subtyping can be implemented in different ways using Doctrine 2. The main difference between these implementations is how the inheritance is mapped to the database. The options are to have one table for every class (Class Table Inheritance), to have one table for all classes in a hierarchy (Single Table Inheritance) and to have a table for every specialized sub-class of a given super-class (Mapped Super Class).We will give a short overview on all three alternatives, you have to pick the right one yourself. This decision should be made based on how many common attributes there are in your sub-classes.

Mapped Superclasses

Introducing a mapped superclass is probably the easiest way for specifying inheritance but might lead to many duplicate columns in your database schema. The superclass of your entities is not being declared as an entity itself (and might also be declared abstract) but provides attributes and optionally methods that will be available in all subclasses. When creating the database schema, Doctrine 2 merges all attributes and relationships of the superclass into the definitions of the subclasses and processes them as regular entities.

After creating the database from this mapping information, your tables will look like this:

Single Table Inheritance

When having entities that are very similar besides some few attributes, you might want to store them together in one database table. This approach is called Single Table Inheritance. To distinguish between the different types, there is always a column marked as discriminator column and a discriminator map that tells Doctrine 2 which values in the discriminator indicate what entity types.

These definitions cause the existing of one single table called User with all the attributes declared inside the classes User and Administrator plus a column type – the discriminator column. When working with entities of these types, Doctrine will manage the type flag automatically for you.

The resulting database schema looks as illustrated by the following diagram:

Class Table Inheritance

Having each entity type stored in its own table is always good for keeping your schema extensible. When you have to create a new subtype, Doctrine 2 will just create a new table for this type and it can inherit the logic and common attributes of a superclass. The only overhead you have with this approach is that all tables that correspond to subtypes have to maintain a relationship to their supertype's table. Using class table inheritance, the example with the entities User and Administrator looks like this:

Besides the inheritance type, there is no difference to the example using single table inheritance. The outcome on the resulting database scheme is huge. Now you have to separate tables which store users and administrators. Every record in the table Administrator has a corresponding record in the User table.

---

This was the first part of this article. Stay tuned for part II which will be published tomorrow (on 6th of December 2011)! In the second part, we will integrate Doctrine 2 into a Zend Framework application and include a generic sandbox (ZF-)project with Doctrine 2!

PlanetMySQL Voting: Vote UP / Vote DOWN

The Danish "julehjerte" is apparently a Danish/Northern Europe Christmas tradition (at least according to Wikipedia). But hopefully people outside this region will also be able to enjoy this variant:

I have been doing "julehjerter" ever since I was a small kid, and every Christmas try to do something different with it. As seen above, this year I decided to combine the tradition with the MariaDB logo, and I am frankly quite pleased with the result :-)

PlanetMySQL Voting: Vote UP / Vote DOWN

ScaleDB is proud to announce the introduction of a database that takes data storage to a new level, and a new altitude. ScaleDB’s patent pending “molecular-flipping technology” enables low energy molecular flipping that changes selected water molecules from H20 to HOH, representing positive and negative states that mimic the storage mechanism used on hard drive disks.

“Because we act at the molecular level, we achieve massive storage density with minimal energy consumption, which is critical in today’s data centers, where energy consumption is the primary cost,” said Mike Hogan, ScaleDB CEO. “A single thimble of water vapor provides the same storage capacity as a high-end SAN.”

The technology does have one small challenge: persistence. Clouds are not known for their persistence. ScaleDB relies on the Cumulus formation, since it is far beefier than some of those wimpy cirrus clouds. However, when deployed in the data center, the dry heat can be particularly damaging to cloud maintenance. One of the company’s patents centers around using heavy water, which resists evaporation and is therefore far more persistent than its lighter brethren. The company has already received approval from the IAEA to commercialize this technique.

This new technology considerably improves ScaleDB’s “green cred”. By greatly reducing energy consumption in data centers, it cuts their carbon footprint, leaving little more than a toeprint. Once the cloud storage—which has a 3-year half-life—is worn out, you can release it into the atmosphere. There is mingles with natural clouds making them denser and more reflective. Leading IPCC climate scientists have modeled the effects of this mingling and the scientific consensus is that it will reduce global temperatures by 5-6 degrees centigrade within 20 years (+/- 10 degrees centigrade). The company is in negotiations with Al Gore to promote this new technology, but they cannot comment on these negotiations because the mere fact that such negotiations are in fact happening is covered by a strict NDA and the even more legally binding pinky promise.

ScaleDB set out to become THE cloud database company and today’s announcement really takes that to a whole new level. The tentative name for this new database is VaporWare.
PlanetMySQL Voting: Vote UP / Vote DOWN

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. Doug’s Oracle Blog has a series of posts about Hotsos. If all this talk about conferences has gotten you excited, Joshua Drake notes that 14 days and the hotel is almost full for postgresql conference east which is March 25th-28th in Philadelphia. And the Oracle database insider notes that the Oracle OpenWorld call for papers is now open.

According to Susan Visser this week (ending tomorrow) is also read an e-book week. So if you have not already done so, read an e-book! She links a coupon for an e-book in the post.

Craig Mullins notes that the mainframe is a good career choice in Mainframes: The Safe IT Career Choice. He notes that the mainframe is still not dead:

People having been predicting the death of the mainframe since the advent of client/server in the late 1980s. That is more than 20 years! Think of all the things that have died in that timespan while the mainframe keeps on chugging away: IBM’s PC business, Circuit City, Koogle peanut butter, public pay phones, Johnny Cash… the list is endless.

In other career-related news, Antonio Cangiano is looking for [2] top-notch student hackers for a 16-month internship at IBM in Toronto starting in May. All the details, including how to apply, are in Cangiano’s blog post.

Willie Favero wants to know how you “solve the batch dilemma” for issues like “shrinking your batch window, designing your batch to play nicely with … OLTP” in how’s your batch workload doing? Perhaps Favero should read the updated batch best practices posted by Anthony Shorten.

Bryan Smith surveys a more personal question by asking if you go both ways and “manage both DB2 for Linux, UNIX, and Windows and DB2 for z/OS” in don’t ask, don’t tell, bi-platform DBAs. This week’s Log Buffer editor admits to being a tri-platform DBA — she has tried many platforms, and in fact, many databases (MySQL, Oracle, DB2, SQL Server, Sybase, Postgres and Ingres)!

Hari Prasanna Srinivasan promotes a patching survey in Oracle really wants to hear from you! Patching Survey.

Henrik Loeser explains what a deadlock and a hot spot are by using a real life analogy taken from a police report in deadlock and hot spot in real life.

Jamie Thomson asks why do you abbreviate schema names?. Shlomi Noach tries to solve the issue that “there is no consistent convention as for how to write [about table aliases in] an SQL query” in proper sql table alias use conventions. Noach also gives us a tip: faster than truncate.

Leons Petrazickis reminds us that “rulesets are chains” and it is important to have your rulesets in the proper order in iptables firewall pitfall.

Anyone interested in the history of MySQL AB will be informed after reading Dries Buytaert’s article.
Gavin Towey shares his software that helps centrally manage 120 MySQL servers in qsh.pl: distributed query tool For those who want to learn more about column-oriented databases, particularly in MySQL, Robin Schumacher of the InfiniDB blog announces that there is a MySQL University session recording on MySQL column databases now available. MySQL join-fu expert Jay Pipes has moved his blog to www.joinfu.com and starts with An SQL Puzzle and of course a follow up on the sql puzzle.

Ivan Zoratti is happy that finally, slides posted for the MySQL DW breakfast. Venu Anuganti gives you tips on one of the most common MySQL frustrations: optimizing subqueries in how to improve subqueries derived tables performance. Justin Swanhart posts the way in which he Gets Linux performance information from your MySQL database without shell access and emulates a ‘top’ CPU summary using /proc/stat and MySQL using the same method.

The Oracle Apps blog has an introduction to Oracle user productivity kit (UPK). Even though in this editor’s opinion the article is very sales-pitchy, it has valuable information, and does indeed live up to its promise:

UPK is a software tool that can capture all the steps in a system process. It records every keystroke, every click of the mouse, each menu option chosen and each button pressed. All this is done in the UPK Recorder by going through the transaction and pressing “printscreen” after every user action. From this, without any further effort from the developer, UPK builds a number of valuable outputs.

Allen White gives a great tip on how to optimize queries in keep your data clean.

Mike Dietrich reminds you to remove “old” parameters and events from your init.ora when upgrading, “as keeping them will definitely slow down the database performance in the new release.” He shows evidence of slowness when this is not done. Dietrich also shows how you can be gathering workload statistics “to give the optimizer some good knowledge about how powerful your IO-system might be”, especially “a few days after upgrading to the new release…while a real workload is running.”

Brian Aker shows the exciting features coming soon in Drizzle in Drizzle, Cherry, Roadmap for our Next Release.

Maybe you are thinking of migrating, not upgrading…..The O’Reilly Radar shows how to asses an Oracle to MySQL migration in MySQL migration and risk management. Actually, that article interviews Ronald Bradford on the subject — Bradford has been prolific lately, updating free my.cnf advice series and “Don’t Assume”: MySQL for the Oracle DBA series. Nick Quarmby also talks about migrating Oracle, but not to a new database, just to a new platform, in his primer on migrating Oracle Applications to new platforms. And the big news comes from Carlos of dataprix that Twitter will migrate from MySQL to Cassandra DB.

Paul S. Randal explains his way of benchmarking: 1 Tb table population on SQL Server.

Pete Finnigan shares his slides from a webinar on how to secure oracle, and Denis Pilipchuk shares his approaches for discovering security vulnerabilities in software applications.

Jeff Davis shares his thoughts about scalability and the relational model. Robert Treat responds actually, the relational model doesn’t scale and Baron Schwartz counters with NoSQL doesn’t mean non-relational.

Buck Woody explains “whenever you want to know something about SQL Server’s configuration, whether that’s the Instance itself or a database, you have a few options” — and of course what those options are — in system variables, stored procedures or functions for meta data.

This week’s T-SQL Tuesday topic was I/O. There are many links to great blog posts in the comments; three random posts I chose to highlight: Michael Zilberstein talks about IO capacity planning, while Kalen Delaney talks about using STATISTICS IO in I/O, you know, and Merrill Aldrich chimes in with information on real world SSD’s. Aldrich also begs folks not to waste resources and make more work for developers and DBAs in dear ISV, you’re keeping me awake nights with your VARCHAR() dates.

And we end with a bit of fin: Paul Nielsen wants us all to have a bit of fun; he has posted an SQL limerick and asks readers to create there own in there once was in Dublin a query.

PlanetMySQL Voting: Vote UP / Vote DOWN

Playing chess within MySQL? Over the network? In the .. cloud? Yes!

This is a follow-up post of my 'A chessboard in MySQL' where we create and populate a chessboard. But pieces need to move, and a few wondered how. Easy!

As an example, white openes with 1.e4:

  mysql> BEGIN;
  mysql> UPDATE chessboard SET e='♙' WHERE x = 4;
  mysql> UPDATE chessboard SET e='' WHERE x = 2;
  mysql> COMMIT;

Pretty obvious. Now lets put it in a stored procedure (source included in post) so the next move is easier on the fingers and more fun to play. Lets do a 1...e5:

  mysql> CALL move_piece('e','7','e',5);

The result is the following:

mysql> SELECT * FROM chessboard;
+---+------+------+------+------+------+------+------+------+
| x | a    | b    | c    | d    | e    | f    | g    | h    |
+---+------+------+------+------+------+------+------+------+
| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    |
| 7 | ♟    | ♟    | ♟    | ♟    |      | ♟    | ♟    | ♟    |
| 6 |      |      |      |      |      |      |      |      |
| 5 |      |      |      |      | ♟    |      |      |      |
| 4 |      |      |      |      | ♙    |      |      |      |
| 3 |      |      |      |      |      |      |      |      |
| 2 | ♙    | ♙    | ♙    | ♙    |      | ♙    | ♙    | ♙    |
| 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |
+---+------+------+------+------+------+------+------+------+

Here is the stored procedure. It's very, very basic, and of course, one can add much more!

DROP PROCEDURE IF EXISTS move_piece;
delimiter //
CREATE PROCEDURE move_piece(
    psrcCol CHAR(1),
    psrcRow TINYINT,
    pdstCol CHAR(1),
    pdstRow TINYINT)
BEGIN
    SET @srcCol = psrcCol;
    SET @srcRow = psrcRow;
    SET @dstCol = pdstCol;
    SET @dstRow = pdstRow;
    SET @piece = 0;
    SET @blank = '';
    
    -- Get the piece we are moving
    SET @stmt = CONCAT('SELECT ',@srcCol,
        '+0 INTO @piece FROM chessboard WHERE x = ?');
    PREPARE preStmt FROM @stmt;
    EXECUTE preStmt USING @srcRow;
    DEALLOCATE PREPARE preStmt;
    
    IF ((@piece > 1 AND @piece <= 14) AND @piece is not NULL)
    THEN
        -- Move the piece
        SET @stmt = CONCAT('UPDATE chessboard SET ',
            @dstCol,'=? WHERE x = ?');
        PREPARE preStmt FROM @stmt;
        EXECUTE preStmt USING @piece,@dstRow;
        DEALLOCATE PREPARE preStmt;
        SET @stmt = CONCAT('UPDATE chessboard SET ',
            @srcCol,'=? WHERE x = ?');
        PREPARE preStmt FROM @stmt;
        EXECUTE preStmt USING @blank,@srcRow;
        DEALLOCATE PREPARE preStmt;
    ELSE
        SELECT "No piece found at given position." AS Error;
    END IF;
END;
//

Some thoughts for future expansion:

• You could save the moves in a separate table to record time it took.
• Implement some other movement notation.
• Lock a player after a move.
• Build some other stored routines to populate and reset the chessboard.
• Have multiple chessboard tables.
• Event scheduler can be used to implement the non-human player!

Anyway, this was all about fun and Unicode testing. There will be probably no follow-up on this post. If somebody is crazy enough to actually implement a chess game in MySQL: awesome!

(Disclaimer: I am not a chess player.)

PlanetMySQL Voting: Vote UP / Vote DOWN

Something to keep you warm during cold winter nights, or cool during hot summer days: a chessboard in MySQL!

Note: You should see chess pieces here below. If not, you're not watching it using UTF-8, or get yourself a good browser!

CREATE TABLE `chessboard` (
  `x` tinyint unsigned NOT NULL,
  `a` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `b` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `c` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `d` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `e` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `f` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `g` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `h` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟')
) DEFAULT CHARSET=utf8;

Populating it with Python using MySQL Connector/Python (only piece of script shown):

def create_board(db):
    c = db.cursor()
    
    table = """
        CREATE TABLE chessboard (
        x tinyint unsigned not null,
        a ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        b ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        c ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        d ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        e ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        f ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        g ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        h ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F')
        ) default charset='utf8'
    """
    c.execute("DROP TABLE IF EXISTS chessboard")
    c.execute(table)

def set_start_position(db):
    c = db.cursor()
    # Numbers correspondent to the ENUM fields
    wdata = { 'x' : 1,
        'a': 4, 'b': 6, 'c': 5,
        'd': 3, 'e': 2,
        'f': 5, 'g': 6, 'h': 4}
    bdata = { 'x' : 8,
        'a': 10, 'b': 12, 'c': 11,
        'd': 9, 'e': 8,
        'f': 11, 'g': 12, 'h': 10}
    
    stmt = """UPDATE chessboard SET a=%(a)s,b=%(b)s,c=%(c)s,
        d=%(d)s,e=%(e)s,f=%(f)s,g=%(g)s,h=%(h)s WHERE x = %(x)s"""
    c.executemany(stmt, [wdata,bdata])
    
    stmt = """UPDATE chessboard SET a=7,b=7,c=7,d=7,e=7,f=7,g=7,h=7
        WHERE x = 2"""
    c.execute(stmt)
    stmt = """UPDATE chessboard SET a=13,b=13,c=13,d=13,e=13,f=13,g=13,h=13
        WHERE x = 7"""
    c.execute(stmt)

Now you can select the chessboard:

mysql> select * from chessboard;
+---+------+------+------+------+------+------+------+------+
| x | a    | b    | c    | d    | e    | f    | g    | h    |
+---+------+------+------+------+------+------+------+------+
| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    |
| 7 | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    |
| 6 |      |      |      |      |      |      |      |      |
| 5 |      |      |      |      |      |      |      |      |
| 4 |      |      |      |      |      |      |      |      |
| 3 |      |      |      |      |      |      |      |      |
| 2 | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    |
| 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |
+---+------+------+------+------+------+------+------+------+

The possibilities.. oh yeah!

PlanetMySQL Voting: Vote UP / Vote DOWN

1. Page2Pub -- harvest wiki content and turn it into EPub and PDF. See also Sony dropping its proprietary format and moving to EPub. Open standards rock. (via oreillylabs on Twitter)
2. SQL Pie Chart -- an ASCII pie chart, drawn by SQL code. Horrifying and yet inspiring. Compare to PostgreSQL code to produce ASCII Mandelbrot set. (via jdub on Twitter and Simon Willison)
3. How SudokuGrab Works -- the computer vision techniques behind an iPhone app that solves Sudoku puzzles that you take a photo of. Well explained! These CV techniques are an essential part of the sensor web. (via blackbeltjones on Delicious)
4. Twitter by the Numbers -- massive dump of charts and stats on Twitter. I love that there's a section devoted to social media marketers, the Internet's head lice. (via Kevin Marks on Twitter)

PlanetMySQL Voting: Vote UP / Vote DOWN