Bob's Guides

Custom Database Tables

Introduction

This tutorial shows how to create the files necessary to use the MODX xPDO methods with your custom database tables (and how to create the tables themselves). It is for people who want a simple method that allows them to use xPDO with their tables. The xPDO package (installed automatically in any new MODX install), provides many convenient methods for interacting with MODX objects in the database (e.g., resources, templates, TVs, chunks, snippets, plugins, and your own custom objects).

If you've been here before, you'll notice that this page has been modified quite a bit, because the ClassExtender extra has been modified to assist with the process. This makes it much easier to create custom, xPDO-friendly classes. Don't forget to install the ClassExtender extra before using the methods described below.

Why Create Custom Tables and Classes?

Many MODX websites can be enhanced by creating custom database tables. With them, you get more efficient data storage, much faster searching and sorting, and ultimately, faster page loads. If you database tables are supported by xPDO-friendly classes, your classes will automatically get all the convenience of xPDO methods like get(), set(), save(), getObject(), getCollection(), and many more.

ClassExtender can also create other custom classes, and that's the topic of this page. In the sections below, we'll walk through the methods for creating custom database tables and the xPDO-friendly classes that support them.

Creating xPDO-friendly Classes

To use xPDO with a custom table, you need a set of class files that can be used to create an instance your class object. You can see the MODX class files in the core/model/modx/ directory (for MODX 2). MODX 3 puts the class files in the core/src/Revolution/ directory. ClassExtender puts them under the core/components/classextender/model directory.

In order to make your object classes xPDO-friendly, you need to create those classes in a particular way, and make them descendants of an existing xPDO-friendly class. Often, your custom class will extend xPDOObject or xPDOSimpleObject (see the difference here), but any of the existing MODX classes can be used, modChunk, modElement, etc. (which are descendants of an xPDO object already), as well as a number of controller and processor classes.

You can create your custom xPDO classes by hand. In fact, one part of the lengthy (and somewhat infamous for its difficulty) Doodles Tutorial shows how to do it.

Fortunately, there are now much easier ways to do it, as we'll see below.

Uninstalling ClassExtender Warnings

Important: Even though ClassExtender only needs to be run once to create the class and map files for the extended classes, DO NOT uninstall it unless you will not be using any of the extended classes. The class and map files, all chunks (including the 'My..." chunks), and the namespace, will be removed during the uninstall.

If you want to save the chunks or the class files, rename the chunks and move them out of the ClassExtender folder. Move the class files outside of the core/components/classextender directory.

To prevent the possible loss of critical data, the custom database tables are not removed. You can delete them manually in PhpMyAdmin.

Two Approaches

There are two ways to go about creating xPDO-friendly classes. If you are familiar with database schema files (or are willing to learn about them), you can create an XML schema for your custom tables, and run a MODX utility method to automatically write the xPDO-friendly class files. Once the schema exists, ClassExtender will handle the whole job for you in a few seconds.

This is the preferred method, since ClassExtender will not only write the class files, it will also translate them for MODX 3, and create the database tables, namespace objects, and extension package objects for you, all in one pass.

The second method is to create the DB table(s) first and have MODX extract the schema from database. ClassExtender will also handle this process for you.

If schema files are incomprehensible to you, and you're fluent in MySQL, you can create the database tables yourself, with all the desired fields and indexes. Then, you can use a simple snippet to create the schema for you. The snippet is included in the ClassExtender package. It's called "CreateSchema" and there's a matching resource with the same name containing the snippet tag. Once the snippet has run, you can use ClassExtender to create the class file(s) as described below, and also in the ClassExtender docs.

In this tutorial, we'll walk through both methods. We'll call them "Schema First" and "Table First."

Neither method will handle any related objects (like modUser and its related object modUserProfile). The script won't create aliases for your related objects, so you'll often have to learn a bit about schemas anyway. You'll need to edit the schema file to add the relationships, and then run ClassExtender to create the class files.

The two methods mentioned above will be described in more detail later in this article.

Project Overview

For the purposes of the tutorial, we'll create a simple database table that stores quotations with four fields: id, quote, topic, and author. We'll call the table bobs_quotation_data (note the table prefix: bobs_ — always end your prefix with an underscore).

Also, always include the word "data" in your table names and "Data" in your class names so the ClassExtender autoloader can find them.

Once the table is created, we'll see how to create the class that supports it, and how to use that class to interact with the database table.

If you'd like to see a finished example that uses this exact custom database table and the class that goes with it, see this page.

Tips

Here are some tips to keep in mind as you work through this tutorial

• Make sure your custom database table has a primary key called id. It must be a unique, autoincrement field. Extending xPDOSimpleObject will do that for you automatically. Extending xPDOObject will not.
• Important: Use a different table prefix than the one in your MODX database, so ClassExtender doesn't try to process every table in the MODX DB!
• If the first letter of your prefix comes earlier in the alphabet than the first letter of your MODX prefix, your custom table(s) will be a top of the grid in PhpMyAdmin, which is convenient during development.
• Important: ClassExtender will provide an autoloader for your files, but it will only work if your custom class names contain the word "Data. (e.g. UserData, or ResourceData), so it's important to always append Data to the class name and the table name.
• Use a singular noun for the table name, because the table name will be the name of your class.
• If a table already exists, ClassExtender will leave it alone.
• It's not a bad idea to create a separate localhost install of MODX for this tutorial since we'll be modifying the MODX DB, although the only modification of the DB will be the addition of the custom table. We'll have more to say about the table name when we discuss how the CreateSchema method creates the class name from the table name (this only applies if you are creating the table first, then having MODX create the schema from it).

File Locations

In both MODX 2 and MODX3, the MODX schema is in the core/model/schema/modx.mysql.schema.xml file if you'd like to take a look at it. It includes a wide variety of schema examples.

For our schema, ClassExtender will calculate a path for the schema file based on the package name. Our package name is "quotation" so the schema will end up in the core/components/classextender/model/schema/ directory.

ClassExtender will create our schema file with the name, quotation.mysql.schema.xml and will put it in that directory. We'll also include an argument with the prefix bobs_ so that writeSchema() will only process tables with that prefix. We only have one table, but if we had more tables with that prefix, writeSchema() would process all of them and put descriptions of them all in one schema file. The class files will be separate and there would be one for each DB table.

We'll have ClassExtender put our class files in the core/components/classextender/model/quotation/ directory. The decision about where to put the files was very difficult. They could have gone in the core/components/quotation directory, but the ClassExtender autoloader expects to find them under the classextender directory, and having them there makes it really easy to have the autoloader add a new custom class (more on this in a bit).

Putting them in the core/components/quotation directory might have broken things for previous users of ClassExtender. There's also some convenience in knowing that all your custom class files are in one location. Future versions of ClassExtender may provide an option to put the files in the core/components/{packagename}/ directory.

For previous users of CreateXpdoClasses snippet who have the class files in other directories, you can leave your classes where they are and continue using them, or you can just put your schema in a chunk and run ClassExtender (giving it the name of your package, and the chunk name), and the classes will be created in the core/components/classextender/model/{packagename} directory. That will let you use the ClassExtender autoloader for your classes. Be sure to check the spelling of the class names. The current PHP convention is to have the first letter of each word capitalized (Pascal Case).

Schema First

For the first method (creating the schema first), ClassExtender uses the MODX generator's parseSchema() method. It takes three pieces of information, presented in the ClassExtender form. The first tells ClassExtender the name of the package you're creating. The second tells it the name of the MODX chunk containing the schema file. The third is the table prefix to user in creating the DB table. You can also specify the directory permission, and the path to the CSS file to use for the form. The rest of the information ClassExtender needs will be pulled from the schema file.

The Schema Chunk

Create a chunk called MyQuotationSchema and paste this code into it:

<?xml version="1.0" encoding="UTF-8"?>
<model package="quotation" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" tablePrefix="bobs_" version="1.0.0">

    <object class="QuotationData" table="quotation_data" extends="xPDOSimpleObject">
        <field key="quote" dbtype="mediumtext" phptype="string" null="false" index="BTREE"/>
        <field key="topic" dbtype="varchar" precision="50" phptype="string" null="false" default="" index="index"/>
        <field key="author" dbtype="varchar" precision="50" phptype="string" null="false" default="" index="index"/>

        <index alias="quote" name="quote" primary="false" unique="true" type="BTREE">
            <column key="quote" length="" collation="A" null="false"/>
        </index>

        <index alias="topic" name="topic" primary="false" unique="false" type="BTREE">
            <column key="topic" length="" collation="A" null="false" />
        </index>

        <index alias="author" name="author" primary="false" unique="false" type="BTREE">
            <column key="author" length="" collation="A" null="false" />
        </index>

    </object>
</model>

Notice that there's no id field in the schema. Extending xPDOSimpleObject will provide that field for us automatically.

Also, you can see that the table name in the schema (table="quotation_data") does not include the table prefix, which we'll specify in the ClassExtender snippet properties.

Here's a typical version of the snippet tag used to turn the schema into a class file and a new database table:

[[!ClassExtender?
    &package=`quotation`
    &schemaTpl=`MyQuotationSchema`
    &tablePrefix =`bobs_`
    &cssFile =`[[++assets_url]]components/classextender/css/classextender.css`
    &dirPermission=`0755`
]]

The last two properties above are optional here, because they are the default values, but you may want to modify them.

Create a Resource to hold the snippet tag above (call it "Create Quotation Classes"). If you view the resource, you'll see the ClassExtender form. If you have put your schema in a chunk called MyQuotationSchema, you can click on the "Submit" button, and a few seconds later, you can find your class files in the core/components/classextender/model/quotation directory. The table, bobs_quotation_data, will be in the database with the fields in it. The class will be called QuotationData. Your schema will be in the core/components/classextender/model/schema/quotation.mysql.schema.xml file (translated for MODX 3 if necessary).

ClassExtender will also create the modNamespace and modExtensionPackage objects, and will update the ce_autoload_directories System Setting to tell the autoloader to look in that directory (the System Setting is ignored in MODX 3).

If your schema contains more than one object, multiple tables (all with the same table prefix) and class files will be created for you.

Important: Never edit the schema file. It will be overwritten the next time you run ClassExtender. If you need to change it, edit the MyQuotationSchema chunk, or whatever chunk is specified in the snippet tag.

In the core/components/classextender/model/ directory you'll also see a file called ce_autoload.php. This is the autoloader for your class. It looks for class packages automatically in the correct directory (or directories). The directories it looks in are listed in the ce_autoload_directories System Setting. The directory for classes created through ClassExtender are added to that setting automatically. This is only for MODX 2. In MODX 3, that setting will be ignored.

Whenever you want to use your class, just include that MODX_CORE_PATH/components/classextender/model/ce_autoload.php file.

In snippets or plugins that use your class files, you need to use the correct class name in any calls to xPDO methods (e.g., getObject(), getCollection(), etc.). If you're not sure what it is, look in the schema file. It will have a line for each object with something like this:

<object class="className" . . . .>
</object>

In MODX 2 the file will be the same as the listing in the schema chunk, but in MODX 3 the file will contain the correct values, translated for MODX 3 (in this case quotation\QuotationData) — the chunk may not.

In MODX 3, your package name (e.g. quotation) will be used as a namespace. So, for example, your fully qualified class name would be quotation\QuotationData. If you don't specify the full class name, MODX won't find the class, and the autoloader won't be able to find the file.

You can add namespace quotation; at the top of your script, but then that namespace will be applied to any MODX classes in your script, and they won't be found. The best way to avoid this, is to either use the fully qualified name, or leave out the namespace and add a use statement, like this:

  use quotation\QuotationData;
  $quote = $modx->newObject('QuotationData');
  

That won't affect any other classes in the script.

Database Table First

Although the method described above is recommended, there may be cases when you want to create the tables first, then generate the schema, then create the class files from the schema. Maybe you've already created the table or tables outside of MODX. Maybe you've created the tables in MODX, but they're already full of data. Maybe you're an expert at database design and creation, but schemas give you the vapors.

In this section, we'll look at how to create a schema from a database table. We'll continue to use our "QuotationData" example. The CreateSchema snippet and its corresponding resource are now included in the latest ClassExtender package. Be sure that's installed before going any further.

Creating the Table

If the bobs_quotation_data table doesn't exist yet, use PhpMyAdmin, open up your MODX database and click on the "SQL" tab. Paste in the following code and click on the "Go" button.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `bobs_quotation_data` (
    `id` int(25) unsigned NOT NULL auto_increment,
    `quote` mediumtext NOT NULL,
    `author` varchar(200) NOT NULL default '',
    `topic` varchar(20) NOT NULL default '',
    PRIMARY KEY (`id`),
    KEY `topic` (`topic`),
    KEY `author` (`author`),
    FULLTEXT KEY `quote` (`quote`,`author`,`topic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

We'll also need some quotations in the table in order to test our classes. Still in PhpMyAdmin, select the bobs_quotation_data table and click on the "Insert" tab at the top. Add a couple of quotations with a topic and an author by filling in the Value fields (leave the id field blank and don't mess with any of the other fields). Click on the "Go" button at the bottom. Add a few more if you like. If you click on the "Browse" tab, you should see your quotations in the table. Click on the little pencil icon next to one if you need to edit anything.

Creating the Snippet

This is no longer necessary, as I mentioned above, since the snippet is included in the latest ClassExtender extra. The snippet is called "CreateSchema." It will create a schema file (based on the DB table we created above or any other table).

Launching the Snippet

Open the CreateSchema Resource (not the snippet) for editing, it will be under the ClassExtender folder in the Resource Tree. You should see a snippet tag like this one:

[[!CreateSchema?
    &package = `quotation`
    &tablePrefix = `bobs_`
    &baseClass = `xPDOSimpleObject`
    &fileName = `quotation.schema.xml`
    &chunkName =`MyQuotationSchema`
    &dirPermission = `0755`
]]

The tag above is already set up for our "quotation" package. The properties are pretty easy to figure out:

• The package is our quotation package.
• Our table prefix isbobs_.
• xPDOSimpleObject is the MODX object we are extending, also called the "base class."
• The fileName is set to quotation.schema.xml.
• The chunkName (QuotationSchemeTpl) is where the schema will end up. It the chunk doesn't exist, it will be created. Classextender will add the prefix "My" to the chunk name. Remember this when you run ClassExtender
• The directory permission is set to 0755. This property is actually not needed here, since 0755 is the default. It's recommended that you leave it at that unless you host requires something different.

The specified chunk is what you edit if you want to change the schema after ClassExtender has created it. When you run ClassExtender to create the class files, MODX uses a file containing the schema when it makes the classes, but that file is created on each run by ClassExtender from the schema chunk's content, so editing the file will have no effect.

Launch the snippet by viewing the "Create Schema" Resource and double-check the values. Then, click on the "Submit" button. If it finishes with no errors, and you see the message that the schema was written to the chunk, you can move on to the steps below.

Here's a generic example you can modify to create a schema for any custom class:

[[!CreateSchema?
    &package = `{packagename (lowercase)}`
    &tablePrefix = `{your table prefix (e.g., bobs_}`
    &baseClass = `{The class you're extending (e.g., xPDOSimpleObject)`
    &fileName = `{packagename(lowercase)}.schema.xml`
    &chunkName =`My{PackageNameSchema (e.g., MyQuotationSchema}`
    &dirPermission = `0755`
    &cssFile =`[[++assets_url]]components/classextender/css/classextender.css`
]]

The CreateSchema snippet will put the schema in the specified chunk, but it will not create the class files.

Editing the Schema Chunk

If you intend to make changes to the schema chunk, now it the time to do it. Create a duplicate with the "My" prefix. The duplicate won't be altered if the CreateSchema snippet runs again. If you do that, be sure to remember to use the name of your duplicate chunk when you use ClassExtender to create the class files (more on this in a bit).

If you will have multiple classes, and they are related, you may want to add those other classes to the schema and provide aliases for them there. Be sure to do that in a duplicate of the original chunk. It's easy to make an error in the schema that will keep MODX from being able to parse it. You can always paste the original chunk's content into the duplicate if you need to start over.

If your multiple classes are related, for example a field in one of the class contains the id of the related object, you'll eventually want to create aliases for each object to point to the other, related object. Then, you'll want to run ClassExtender again to update the class files. See this page for information about aliases.

Creating the Classes

As we saw above, Create Schema will create the schema for you, but it will not create the classes.

Now that the schema chunk contains the schema, create a new resource called "Create Quotation Classes" with this snippet tag (if it's already there, you can just modify it):

[[!ClassExtender?
    &package=`quotation`
    &schemaTpl=`MyQuotationSchema`
    &tablePrefix=`bobs_`
    &dirPermission=`0755`
    &cssFile =`[[++assets_url]]components/classextender/css/classextender.css`
]]

View the resource and click on the "Submit" button. That's all there is to it.

Testing your classes

This section assumes that you have created the table, using either of the two methods described above, added some quotations to the table, and created the class file(s) with ClassExtender as described in the first section of above.

Create a new snippet called "ShowQuotes" with the following code:

<?php

/* Allow the code to run in both MODX 2 and MODX 3 */
$prefix = $modx->getVersionData()['version'] >= 3
    ? 'quotation\\'
    : '';

require_once "core/components/classextender/model/ce_autoload.php";

$quotes = $modx->getCollection($prefix . 'QuotationData');

$output .= '<p>Total: '. count($quotes) . '</p>';

foreach($quotes as $quote) {
    $output .= '<p>Topic: ' . $quote->get('topic');
    $output .= '<br/>Quote: ' . $quote->get('quote');
    $output .= '<br/>Author: ' . $quote->get('author') . '</p>';
}

return $output;

Now create a new resource called "ShowQuotes" and put the following snippet tag in the Resource Content field:

[[!ShowQuotes]]

When you preview your ShowQuotes resource, you should see your quotations. If that works, all the other xPDO methods will work on your table as well.

Try changing the getCollection() call to this:

getCollection($prefix . 'QuotationData', array('topic'=>'SomeTopic'));

Change 'SomeTopic' to one of your actual topics. For more information on using xPDO with your tables (and other MODX objects) look here.

Doing it With a Tpl Chunk

The method above is a quick and dirty way of testing things, but it would be better to put the HTML code in a Tpl Chunk and use a snippet to display it. That would do a better job of separating the form from the content and make maintaining and improving the site easier. Here's an example showing what that would look like:

Create a chunk called "ShowQuote" and put the following code in it:

    <p class="quotation">
    Topic: [[+topic]]<br/>
    Quotation: [[+quote]]<br/>
    Author: [[+author]]</p>

Change the last section of your ShowQuotes snippet to look like this:

    foreach($quotes as $quote) {
        $fields = $quote->toArray();
        $output .= $modx->getChunk('ShowQuote', $fields);
    }

The code above could use some explanation. The toArray() method just turns all fields of an xPDO object into a PHP associative array of fieldname/value pairs. It will work on any xPDO object. As you probably know, the getChunk() method returns the content of a MODX chunk. The first argument is the name of the chunk. The second (optional) argument to getChunk() is an associative array. When getting the chunk, MODX uses the associative array to replace any matching placeholders in the chunk. After calling toArray() on the $quote object, the $fields array looks something like this:

array(
    'topic' => 'some topic',
    'quote' => 'some quotation',
    'author' => 'some author
)

Notice that the fields on the left match the placeholders in the Tpl chunk. Those placeholders will be replaced with the values grabbed by toArray() during the getChunk() call. The fields on the right, will contain the data taken from the database.

Saving Data with a Form

The form you'd use to save a new quotation is a standard HTML form like this one:

<form method="post" action="[[~[[*id]]]]">
    <input type="hidden" name="quoteId" value="[[+id]]"/>
    <label for="topic">topic</label>
    <input type="text" name="topic" id="topic" value="[[+topic]]"/><br>
  <label for="quote">quote</label>
    <input type="text" name="quote" id="quote" value="[[+quote]]"/><br>
  <label for="author">author</label>
    <input type="text" name="author" id="author" value="[[+author]]"/><br>
  <input type="submit" name="submit" id="submit" value="MySubmitVar"/><br>
</form>

This form will work both for creating a new quotation and editing an existing one. The placeholder tags are there for editing existing quotes. Since they are not set when creating a new quotation, MODX will simply remove them.

The MySubmitVar value adds a little extra security, and makes sure that the data is coming from our form rather than some other form on the page, or some hacker's code.

Our form above has visible input fields for entering a quote, topic and author plus a submit button. The code to save the information to the DB would be this simple, thanks to xPDO:

$prefix = $modx->getVersionData()['version'] >= 3
    ? quotation]]//'
    : '';

$quote = $modx->newObject( $prefix . 'QuotationData');

/* the next three lines can be in any order */

$quote->set('topic', $_POST['topic']);
$quote->set('quote', $_POST['quote']);
$quote->set('author', $_POST['author']);

$quote->save();

Note that we're not setting the id field. MySQL will do that for us.

You can also send an associative array of fields as the second argument like this:

$fields = array(
    'topic' => $_POST['topic'],
    'quote' => $_POST['quote'],
    'author' => $_POST['author'],
);
$quote = $modx->newObject($prefix . 'QuotationData', $fields);
$quote->save();

The form itself can be done in a number of ways in MODX. Usually it's done with a Tpl Chunk and placeholders. Imagine that you have a standard HTML form like the example above in a chunk called quotationTpl. The page itself would just contain a snippet tag. Let's call the snippet CreateQuote.

[[!CreateQuote]]

The full snippet would look something like this:

/* CreateQuote Snippet */

$prefix = $modx->getVersionData()['version'] >= 3
    ? quotation]]//'
    : '';

 /* We need to load our autoloader so newObject() will work */
    include 'MODX_CORE_PATH/components/classextender/model/ce_autoload.php';

if (isset($_POST['submit']) &&
    ($_POST['submit'] == 'MySubmitVar')) {
    /* Form has been submitted */

    /* You might want to check for empty
     * required fields here */

    $fields = array(
        'topic' => $_POST['topic'],
        'quote' => $_POST['quote'],
        'author' => $_POST['author'],
    );
    $quote = $modx->newObject($prefix . 'QuotationData', $fields);
    if ( $quote->save()) {
        $output = "Quote Created";
    } else {
        $output = "Error";
    }

} else {
    /* Not a repost, just display the form */
    $output = $modx->getChunk('quotationTpl');
}
return $output;

Editing an Existing Quotation

This is very similar to the code above. The only difference is that you need to get the quote rather than creating a new one, and you need to display the current values of its fields. Otherwise, the code is the same. Let's call the snippet EditQuote and assume that you've sent the ID of the quote you want to change as a property called &quoteId in the EditQuote tag. So if you wanted to edit the quote with an ID of 12, you'd do this:

[[!EditQuote? &quoteId=`12`]]

For each field in the Tpl Chunk, you'd set a placeholder for the value like this:

<p>Author: <input type="text" value="[[+author]]"/></p>

Note that this code will fail unless you have an actual quote in the database and send its ID in the quotId property. Now the code would look something like this:

/* EditQuote Snippet */

$prefix = $modx->getVersionData()['version'] >= 3
    ? quotation]]//'
    : '';

 /* We need to load our autoloader so newObject() will work */
    include 'MODX_CORE_PATH/components/classextender/model/ce_autoload.php';

/* Get the existing Quote */
$quoteId = $scriptProperties['quoteId'];
$quote = $modx->getObject($prefix . 'QuotationData',
    array('id'=>$quoteId));

/* Show error message if quote is not found */
if (empty($quote)) {
    return ('Could not find Quote with ID: ' . $quoteId);
}

if (isset($_POST['submit']) &&
    ($_POST['submit'] == 'MySubmitVar')) {

    /* Form has been submitted */

    $quote->set('topic', $_POST['topic']);
    $quote->set('quote', $_POST['quote']);
    $quote->set('author', $_POST['author']);

    if ( $quote->save()) {
        $output = "Quote Created";
    } else {
        $output = "Error";
    }
} else {
    /* Not a repost, just display the form */

    /* The second argument to getChunk() tells MODX
     * to replace the placeholders with the existing
     * quote's values
     */

    /* This only works if the placeholders in the chunk
       match the database fields exactly. */
    $output = $modx->getChunk('quotationTpl',$quote->toArray() );
}
return $output;

More Information

Check out the links below for more information about using xPDO with MODX Revolution

• Custom Models
• Defining a Schema
• Generating the Schema
• Using Custom Database Tables in your 3rd Party Components
• Reverse Engineer xPDO Classes from Existing Database Table
• Doodles Tutorial

Copyright © 2011-2024
Bob Ray