Writing xPDO Schema

This is Part 2 of a 3-part series. If you haven't read Part 1, I'd suggest starting there for background info that's relevant to this post.

Designing an optimized and performant data model, and structuring properly indexed tables, is a craft unto itself. For this project, I relied on the work of the maintainers of the oauth2-server-php library. It was enough of a learning curve to recreate their data model using xPDO's XML schema syntax, without also trying to learn the intricacies of database optimization—especially because I have no reason (yet) to believe that their data model needs optimizing.

xPDO has methods to generate the class files necessary for your app, in this case MODX, to interact with the database objects. I'm not too familiar with those methods, but John Peca's utility Git Package Management executes them, based on the XML schema you provide.

So, at the heart of this phase of the project, was this XML doc—and actually xPDO makes it fairly straightforward to create these. Essentially there's three levels of nodes:

defines the package, to which all child object nodes belong, and according to the xPDO docs:

The "model" tag is a representation of the database itself.
defines a database table, and thus, via xPDO, a PHP class of object.
defines a column in the table, which is defined by the parent object.

NOTE: I had the benefit of referencing an existing XML schema document written by John "TheBoxer" for a package with a similar model. I'll try to elucidate "why" things are done the way they are, but admittedly I'm no every little detail.

Writing the model node was a copy/paste operation, except for the "package" attributes. You can omit the "tablePrefix" and xPDO will use the prefix defined in your MODX config. The one attribute that caused some grief was the "version".

The documentation shows examples of using an index node as child of the field node, which requires the "version" attribute of the model to be "1.1". However, when I tried that, Git Package Management would not create the tables. Using a previous version, the index is defined with an "index" attribute on the field nodes. This worked, so I used that.

Curiously, in John's example, he specifies version "0.1" which isn't documented, but worked. That's what I used but I'll have to ask him for details about it. For now, let's move on.

The object node has three attributes:

class
table
extends

The first 2 are fairly self-explanatory and well-documented.

The third specifies an object class to extend. The examples in the docs, and John's example, use "xPDOSimpleObject" but the oauth2-server-php library requires specific primary keys for each of its tables, so the auto-incrementing ID that comes with "xPDOSimpleObject" wouldn't work. (I tried.) For those tables I used "xPDOObject", which worked nicely.

The field nodes support quite a few options, which are well-documented, but I'll highlight a few gotchas here:

If you omit the "null" and "default" attributes, then the field can be NULL and the default value will be NULL. Your application logic may require this, or it may choke on it. Be sure to know what's needed, or test against it. xPDO offers validation methods, but I haven't dived into these yet. I speculate they'll be very useful.
If the model uses "version" index="pk" to the field you want to be indexed as the primary key.
For timestamp fields, here are some example attributes that work:

dbtype="timestamp" phptype="timestamp" default="CURRENT_TIMESTAMP" attributes="ON UPDATE CURRENT_TIMESTAMP"
I couldn't find an example of a boolean type, so I used:
dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer"

In the Git Package Management UI, there's a context-menu item "Create classes with schema". Selecting it will generate the xPDO class files. To use the classes in your code, you call the $modx->addPackage method, documented here. I call it in my class constructor, but that's not necessarily always the best place—I just found it convenient.

NOTE: some pages in the documentation advise not to specify the 3rd argument to addPackage(), for table_prefix, but I found my classes wouldn't load without it.

One of the gotchas I ran into was that instantiating my class from inside a custom FormIt hook, I inadvertently overrode my class's default config with FormIt's config.

Take-away: do NOT pass the $scriptProperties array to your constructor, via getService() or any other method, when writing a FormIt hook. In a standalone Snippet, it exposes options to be configured at run-time, which can be handy, but in a FormIt hook, it has all of FormIt's properties, which wreak havoc with those of the class you're instantiating.

Another gotcha, in my particular use case, was ensuring my model was fully compatible with the oauth2-server-php library. I had Sequel Pro on one monitor, and referenced the table structure as I wrote the XML, in Coda, on the other monitor. The library seems to be pretty specific about its needs, failing until my xPDO-created tables were identical to the ones created by the SQL statements in their documentation (with the exception of the boolean field type). If you're developing a custom component from scratch, you probably won't have this issue.

Take-away: don't try to skimp on the model.

While slightly tedious, this whole schema-writing affair has great benefits, both for learning and subsequent convenience. Now, after adding my class, I can access my Extra's tables as easily as any MODX object:

$modx->getObject('OAuth2ServerClients', array('client_id' => 'exampleClientId'));

SO awesome.

And yes, I named my object class in the plural form. That's probably gonna have to change, otherwise it'll drive me nuts.

Now that I could interact with my objects programmatically, the next step was to expose CRUD functionality to the end user. In MODX, this means a CMP, which means ExtJS, another weak point for me.