PDOQuery: jQuery Style Database Access and Manipulation

I’ve been working on a new database library recently. It’s written in PHP, since that’s what I need, but nothing says it couldn’t be ported to other languages (possibly Java, Python, or Ruby). I’ve had a de-facto library that I’ve used for my projects for nearly six years, but it just isn’t cutting it anymore.

My old one is a relatively simple generic class with standard CRUD functions for batch and single primary-key-based operations. I wrote it myself when I first learned about object-oriented programming, and I’ve made a few tweaks here and there through the years. It’s just too heavy though, and not very intuitive. Anyone unfamiliar with my code would really have to study the library for a long time to figure out how to use certain parts—I am sure of this because even I have to really study it sometimes to refresh my memory. That’s a bad sign when the library in question is a simple database access layer.

Enter PDOQuery. PDOQuery is my new library. It’s modeled to a limited extent after jQuery syntax, though obviously not all the same kinds of functionality apply, and the language difference (PHP vs. Javascript) means that PDOQuery uses -> for chaining instead of a period. The query language is built in a similar fashion, only PDOQuery selects and manipulates a database instead of DOM elements.

It’s also meant to give you as much power as possible with as little pain as possible. Query modifications are built to return a PDOQuery object, just like jQuery, so you can link modifications together or apply a new selector to an existing object. The library takes full advantage of PHP to provide powerful object integration, making it extremely simple (and mostly automatic) to create a PHP class that directly represents a database table row. It also supports table references, so with a single line of PHP, you can select a user and all related tasks, for example.

Consider the following implementation code:

include 'PDOQuery.class.php';
PQ() -> connect("mysqli:dbname=testdatabase", "testuser", "testpass");

The PQ() function is a shortcut to the main PDOQuery class. It takes an optional selector argument and returns an instance of the class. Note that this single include and call to the connect() method are the only bits of code necessary to start using the library. Now for some usage:

$s = PQ("users.join:permissions[UserID]");
$users = $s -> select();

The first line above creates a PDOQuery object that parses the selector into appropriate MySQL query, but it doesn’t actually run the query. The second line is what runs it and stores the result. For the MySQL database type (the only one coded at this point), the default result returned is a native MySQL result. You can optionally request a numerically indexed array of rows, an associative array of rows, a numerically and associatively indexed array of rows, or an array of objects. The native result is the default because it has the smallest memory requirement.

You could also run the following code:

$s -> delete();

…though I wouldn’t recommend it. Because the selector in the $s object is parsed and stored in individual pieces (table name, criteria, joins, etc.), it doesn’t assume you’re going to just be selecting. The line above would use all of the selector parts applicable to a DELETE query (just the table name, in this case), ignoring all the rest (the JOIN option), and run the following MySQL query:

DELETE FROM `users`

You could also do the following:

$s2 = $s -> where("UserID=3");
$s2 -> delete();

This would delete only the user whose UserID is equal to 3. Much safer.

Currently, the PDOQuery library supports the following database-related methods:

The query() method is used by all the others, and can be used by you to run a raw query if you cannot accomplish it using other methods. It returns a native database result. The selectRow() method returns a single row, selectColumn() returns an array of values from a single column and multiple rows, and selectValue() returns the value of one column’s content from within a single row.

PDOQuery also has these chainable selection methods:

The outerjoin() method is a the exact same as leftouterjoin(). All of these return a PDOQuery object for further chaining or manipulation.

However, while this stuff is all important, it isn’t the best feature of the library. PDOQuery includes a discover() method which creates a map of fields, data types, and indexes of tables in the database. This information can then be used to generate code for you to inject into your object classes to enable many convenient shortcuts. You can add just a bit more information yourself to establish relationships between tables for even greater conveniences.

Consider a database with a users table and a projects table. A user may have zero or more projects. The following code is mostly generated by PDOQuery, except for the class names and the references index keys:

class User extends PDOQueryObject {
  
    public static $PDOQueryTable = "users";
    public static $PDOQueryStructure = array(
        "timestamp"=>"Fri, 25 Jun 2010 10:27:02 -0600",
        "fields"=>array(
            "UserId"=>array("type"=>"int(10) unsigned", "simpletype"=>"int", "null"=>false, "key"=>"PRI", "default"=>null, "ai"=>true, "indexes"=>array("PRIMARY"), "extra"=>"auto_increment"),
            "EmailAddress"=>array("type"=>"varchar(128)", "simpletype"=>"text", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "Password"=>array("type"=>"varchar(32)", "simpletype"=>"text", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "FirstName"=>array("type"=>"varchar(32)", "simpletype"=>"text", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "LastName"=>array("type"=>"varchar(32)", "simpletype"=>"text", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "LastLogin"=>array("type"=>"datetime", "simpletype"=>"datetime", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "Deleted"=>array("type"=>"tinyint(4)", "simpletype"=>"int", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>"")
            ),
        "indexes"=>array(
            "PRIMARY"=>array("unique"=>"", "collation"=>"A", "cardinality"=>"1", "fields"=>array("UserId")),
            ),
        "primarykey"=>"UserId",
        /* USER-SUPPLIED SETTINGS */
        "references"=>array(
            "projects"=>array("key"=>"UserId", "autojoin"=>false, "relationship"=>"multiple", "class"=>"Project", "local"=>"Projects"),
            )
        );
}

class Project extends PDOQueryObject {
  
    public static $PDOQueryTable = "projects";
    public static $PDOQueryStructure = array(
        "timestamp"=>"Fri, 25 Jun 2010 10:27:02 -0600",
        "fields"=>array(
            "ProjectId"=>array("type"=>"int(10) unsigned", "simpletype"=>"int", "null"=>false, "key"=>"PRI", "default"=>null, "ai"=>true, "indexes"=>array("PRIMARY"), "extra"=>"auto_increment"),
            "UserId"=>array("type"=>"int(10) unsigned", "simpletype"=>"int", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "Name"=>array("type"=>"varchar(64)", "simpletype"=>"text", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>""),
            "Deleted"=>array("type"=>"tinyint(4)", "simpletype"=>"int", "null"=>false, "key"=>"", "default"=>null, "ai"=>false, "indexes"=>array(), "extra"=>"")
            ),
        "indexes"=>array(
            "PRIMARY"=>array("unique"=>true, "collation"=>"A", "cardinality"=>"0", "fields"=>array("ProjectId"))
            ),
        "primarykey"=>"ProjectId",
        /* USER-SUPPLIED SETTINGS */
        "references"=>array(
            "users"=>array("key"=>"UserId", "autojoin"=>true, "relationship"=>"single", "class"=>"User", "local"=>"User")
            )
        );
}

Notice the extended PDOQueryObject class, which provides most of the convenience functions. Also notice the references key down at the bottom of each class definition. For each of these:

• key is the correlating field name
• autojoin determines whether to select this related information automatically
• relationship is either “single” or “multiple” depending on whether it is 1-to-1 or 1-to-many
• class is the name of the class that represents the joined table rows
• local is the name of the local variable to assign the joined data

This allows us to do something like this:

$user = new User(1);

Now, $user is an object that contains a copy of the data row from the users table that has UserID equal to 1. The fields are stored in respective member variables: UserID, EmailAddress, Password, etc. These can be modified directly, since they are public. If you want to make changes, simple assign new values to the fields, then run the following code:

$user -> update();

Voila! Because that update() method is running on an instance of a single User class, it knows to treat it as a database row, and it already has all of the table structure and primary key information handy. It even performs automatic data validation and formatting (most conveniently for DATE, TIME, and DATETIME fields). You could even assign a new UserID value (or set it to NULL) to your $user object and then run:

$newID = $user -> insert();

…and you’ve got a new row inserted, just like that. But wait, there’s more! Because of the references array value, we can also do this:

$user -> loadReference("projects");

…and now you have a new Projects member variable which contains an array of objects pulled from the database and instantiated using the PDOQueryObject child Project class. If the autojoin value had been set to true, this would have been done for us automatically. Now that is convenient.

I’m still working on a few more convenience functions (data table and entry form displays, and lambda functions for automating per-row operations), and I still have to finish the documentation. It’s going to be really, really useful at least for me, and hopefully for many others. I’ll release the code as soon as I’m satisfied with it.