Custom Database Tables

Note: This tutorial is only for MODx Revolution. MODx Evolution does not have xPDO methods so it would be useless in that version. Please update your MODx install to at least Version 2.0.4-pl or newer before running the script.

This tutorial shows how to create the files necessary to use the MODx xPDO methods with your custom database tables. It is for people who want a simple method that allows them to use xPDO with their tables, but will never create a Transport Package to share their classes with the world. If you may want to distribute a package containing your classes, see one of the existing tutorials on creating a package with custom tables (e.g., the Doodles or Storefinder tutorials). The Doodles tutorial also includes information on creating a Custom Manager Page (CMP) to manage your database tables. It's recommended that you work through this tutorial first, though, to get a feel for how the process works.

Best Practices

This tutorial assumes that you've imported your custom tables into the MODx database. It's possible to access tables in another database and you'd still follow the steps in this tutorial, but in snippets that use the classes, you'd create a new instance of xPDO. That would look something like this:

$xpdo = new xPDO('mysql:host=localhost;dbname=mydatabase', $database_user,$database_password,$table_prefix);

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 (note the table prefix: 'bobs_' — always end your prefix with an underscore). It's a good practice to use a different table prefix than the one in your MODx database (in fact, it's required with the current version of writeSchema(), though that may change) and to use a singular noun for the table name because the table name will be the name of your class. It's not a bad idea to create a separate localhost install of MODx for the tutorial since we'll be modifying the MODx DB, although the only modification will be the addition of the custom table. The CreateXpdoClasses snippet does not modify the database in any way &mdash it simply reads it and writes files under the core/components/ directory.

Overview

We're going to create a "package" called "quotes" with the classes necessary to use xPDO with our bobs_quotation table. In order to use xPDO with a custom table, you need a set of class and map files that will be used to create the necessary classes. MODx itself has these in the core/model/modx/ directory. We'll put ours under the core/components/ directory. That's where add-on components usually put theirs. In any snippet that uses the classes, you need to tell xPDO where the files are in a call to addPackage(). To create the files, we'll use two methods of the xpdogenerator class: writeSchema() and parseSchema().

The first one, writeSchema(), writes the schema XML file based on the table(s) in the database that we specify. That file contains a formal description of the database table(s) the xPDO methods will use to access each table. The MODx schema is in the core/model/schema/modx.mysql.schema.xml file if you'd like to take a look at it.

For our schema, the CreateXpdoClasses snippet will calculate a path for the schema file based on the package name. Our package name is "quotes" so the path will be core/components/quotes/schema/. Our schema file will be called "quotes.mysql.schema.xml" and will end up 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 with that prefix, writeSchema() would process all of them and put descriptions of them all in the schema file. The location of the schema file is arbitrary, but we're putting it in a fairly standard location.

The second method, parseSchema(), also doesn't care where the schema file is. It takes two arguments, the first tells it where the schema file is, the second tells it where to put the class and map files (also an arbitrary location). The CreateXpdoClasses snippet automatically uses the path we gave to writeSchema() to find the schema file and calculates the location for the class a map files based on the package name. That second path will be core/components/quotes/model/quotes/. That's the path you use in snippets that make use of the class and map files to tell $modx->addPackage() where they are. The addPackage() method also doesn't care where the files are as long as you give it the correct path to them.

Once the CreateXpdoClasses snippet has run, you should have the following structure under your core/components/quotes/ directory:

quotes model quotes mysql quotation.class.php quotation.map.inc.php quotation.class.php schema quotes.mysql.schema.xml

In snippets or plugins that use the 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 <object class="className".

Creating the Table

Using 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` (
`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 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) and 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 next to one if you need to edit it.

Installing the CreateXpdoClasses snippet

The snippet may be available as a Transport Package you can download and install using Package Manager's "Download Extras" button. If not, you can paste the code at the bottom of this page or from the downloadable ZIP file here into a new snippet called 'CreateXpdoClasses'.

Creating the Files

Create a new resource called "CreateClasses" and put the following snippet tag in the Resource Content field:

[[!CreateXpdoClasses? &myPackage=`quotes` &myPrefix=`bobs_`]]

When you preview your CreateClasses resource, you should see messages telling you that the schema and class files are being created. You only need to do this once. If you'd like to look at the files, they will be under the /core/components/quotes/ directory.

Note that there are some other, optional properties that control the snippet. The &createSchema and &createClasses properties control whether writeSchema() and parseSchema() are called. Both default to true so you can usually leave them out. If you want to create your schema file manually, however, you can use &createSchema=`0` to skip that step. The &useCustomTemplates property defaults to false. It allows you to use the custom templates at the end of the CreateXpdoClasses code for your class and map files. The ones provided add PhpDoc comments to the top of the files but you could alter them to create your own custom classes. This is somewhat risky and very seldom necessary.

Testing your classes

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

<?php
$path = MODX_CORE_PATH . 'components/quotes/';
$result = $modx->addPackage('quotes',$path . 'model/','bobs_');
if (! $result) {
  return 'failed to add package';
} else {
  $output = '<p>added Package</p>';
}

$quotes = $modx->getCollection('Quotation'); 

$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') . '<br /></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('Quotation',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 how to do that.

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);
}

Saving Data From a Form

This example assumes that you already know how to create a form in HTML (which you can find many tutorials for on the web). Say that you have a quotation form with input fields for entering a quote, topic and author and submit it, the code to save it to the DB would be this simple, thanks to xPDO:

$quote = $modx->newObject('Quotation');

/* 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();

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('Quotation', $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 in a chunk called quotationTpl. The page itself would just contain a snippet tag. Let's call the snippet CreateQuote. It would look something like this:

/* CreateQuote Snippet */

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('Quotation', $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 valued 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. 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 */

/* Get the existing Quote */
$quoteId = $scriptProperties['quoteId'];
$quote = $modx->getObject('Quotation', 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
     */

    $output = $modx->getChunk('quotationTpl',$quote->toArray() );
}
return $output;

Code for CreateXpdoClasses

Here's the code. You shouldn't need to modify it at all to use this tutorial.

<?php
/**
* @package = CreateXpdoClasses
*
* Create Xpdo Classes script
*
* This script creates xPDO-ready classes from existing custom
* database tables. It only needs to be run once.
*
* It assumes that your custom tables have been imported into
* the MODx DB and use a different table prefix than the MODx tables.

* In theory, you can use a separate DB but this has not been tested
* and the process of using the classes would be more complicated.
* To do this, you would need to set the following variables after
* including the config file, but before creating the $modx object:
* 
* $database_server = 'yourDbServer';
* $database_type = 'mysql';
* $dbase = 'yourDbName';
* $database_user = 'user';
* $database_password = 'password';
* $table_prefix = 'yourPrefix_';
*
* In the snippets that use the classes, you'd need to instantiate
* a new $xpdo object, and use $xpdo-&gt; rather than $modx-&gt; to call
* the methods.
* $xpdo = new xPDO('mysql:host=localhost;dbname=yourDbName',
*   $database_user,$database_password,$table_prefix);
*
* Note: If you are running this outside of MODx and testing it
* in the Manager, it will log you out when it runs, even though
* the Manager screen will still be visible. Actions taken in the
* Manager (e.g., saving a snippet) will not be successful. After
* running the script, reload the Manager page in your browser
* and you will be prompted to log back in.
*
*
*/
/* assume we're in a snippet */
$outsideModx = false;

 if (!defined('MODX_CORE_PATH')) {
     $outsideModx = true;
    /* put the path to your core in the next line to run outside of MODx */
    define(MODX_CORE_PATH, 'c:/xampp/htdocs/test/core/');
    include_once MODX_CORE_PATH . '/model/modx/modx.class.php';
    $modx= new modX();
    $modx->initialize('mgr');
}

/* set these if running outside of MODx */

if ($outsideModx) {
    $myPackage = 'mypackage';

    /* table prefix; must match the prefix of the tables to process */
    $myPrefix  = 'bobs_';

    /* optional -- only if your table prefix is the same as the modx prefix */
    // $myTables = 'bobs_quotation';
}



/* These two switches let you write the schema and/or create the classes;
 * useful for debugging and for leaving a manually edited schema file alone*/

$createSchema = $modx->getOption('createSchema',$scriptProperties,true); 
$createClasses = $modx->getOption('createClasses',$scriptProperties,true);

/* Used to include the phpDoc templates below or other custom templates */
$includeCustomTemplates = empty($includeCustomTemplates)? 
    false : $includeCustomTemplates;

/* $myPackage is the name of your package. Use this in your
 * addPackage() call to load all classes created by this script.
 *  The class files will be created under the
 * /core/components/$myPackage/model/ 
 * directory.
 * Example:
 *     $myPackage = 'quotes';
 *     $myPrefix = 'bobs_';
 *     $path = MODX_CORE_PATH . 'components/' . $myPackage . '/';
 *     $result = $modx->addPackage($myPackage, $path . 'model/', $myPrefix);
 *     if (! $result) {
 *         return('Failed to add package');
 *     }
 */
$myPackage = empty($myPackage)? 'mypackage' : $myPackage;

/* table prefix; must match the prefix of the tables you want to process */
$myPrefix = empty ($myPrefix)? '' : $myPrefix;

/* Table names to process -- this is only necessary if your table prefix is
 * the same as that of the MODx tables. You can send a comma-separated list
 * of full table names. In that case the class name will be the table name 
 * minus the prefix with any underscores removed and any letter after an 
 * underscore in upper case.
 *
 * You can also send an array of arrays of tableName =&gt; className, 
 * which allows you to specify the exact class name rather then letting
 * MODx create it from the table name. Each inner array specifies a full
 * table name and the class name to use.
 *
 * NOTE: This feature may not be implemented yet.
 *
 * Examples:

 $myTables = 'bobs_quotation';

 $myTables = array(
     array(
        'bobs_quotation'=>'bobQuotation'
     )
 );

*/
$myTables = empty($myTables)? '' : $myTables;

/* You shouldn't need to modify the code beyond this point
********************************************************** */

$sources = array(
    'config' => MODX_CORE_PATH . 'config/config.inc.php',
    'package' => MODX_CORE_PATH . 'components/' . $myPackage . '/',
    'model' => MODX_CORE_PATH. 'components/' . $myPackage . '/model/',
    'schema' => MODX_CORE_PATH . 'components/' . $myPackage . '/schema/',
    );

if (! file_exists($sources['package'])) {
    mkdir($sources['package'],0777);
}

if (! file_exists($sources['model'])) {
    mkdir($sources['model'],0777);
}
if (! file_exists($sources['schema'])) {
    mkdir($sources['schema'],0777);
}

$modx->setLogLevel(modX::LOG_LEVEL_INFO);
$modx->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');

$manager = $modx->getManager();
$generator = $manager->getGenerator();

if ($includeCustomTemplates) {
    customTemplates($generator);
}


$file = $sources['schema'] . $myPackage . '.mysql.schema.xml';

// echo '<br />File: ' . $file;

/* boolean writeSchema (
 *   string $schemaFile,  // Full path to the schema file you want to write
 *   [string $package = ''], //  Name of your component
 *   [string $baseClass = ''], // xPDO base class to use; 
 *                                 send '' if using args below
 *   [string $tablePrefix = ''], // Table prefix (of tables to process)
 *   [boolean $restrictPrefix = false]), // Process only tables 
 *                                          with $tablePrefix
 *   [mixed $tableList = '' // Array of arrays of 
 *                             full-table-name=>className or 
 *                             a string with a comma-separated 
 *                             list of full table names; 
 *                             if you send the string the table
 *                             name will be used as the class name.
*/

if ($createSchema) {

    $xml= $generator->writeSchema($file,
                $myPackage, '',$myPrefix,true,$myTables);

    if ($xml) {
       $modx->log(modX::LOG_LEVEL_INFO, 
           'Schema file written to ' . $file);
    } else {
        $modx->log(modX::LOG_LEVEL_INFO, 
           'Error writing schema file');
    }
}

if ($createClasses) {
     if ($generator->parseSchema($file, $sources['model'])) {
         $modx->log(modX::LOG_LEVEL_INFO,
            'Schema file parsed -- Files written to '. $sources['model']);
     } else {
         $modx->log(modX::LOG_LEVEL_INFO, 'Error parsing schema file');
     }
}
 $modx->log(modX::LOG_LEVEL_INFO, 'FINISHED');
 exit();

 function customTemplates($generator) {
$generator->classTemplate= <<<EOD
<?php
/**
 * [+phpdoc-package+]
 * [+phpdoc-subpackage+]
 */
class [[+class]] extends [[+extends]] {
}
?>
EOD;
$generator->platformTemplate= <<<EOD
<?php
/**
 * [+phpdoc-package+]
 * [+phpdoc-subpackage+]
 */
require_once (dirname(dirname(__FILE__)) . '/[+class-lowercase+].class.php');
class [+class+]_[+platform+] extends [+class+] {
}
?>
EOD;
$generator->mapHeader = <<<EOD
<?php
/**
 * [+phpdoc-package+]
 * [+phpdoc-subpackage+]
 */
EOD;
}

Related Objects

While the schema files created by this tutorial will work fine with standard xPDO get(), set(), getObject() and getCollection() calls, they won't contain information about the relationships between tables containing related objects. In order to deal with related objects (e.g., getOne(), getMany(), getObjectGraph, etc.) and any joins, you'll have to edit the schema file manually to define any aggregate or composite relationships (and then regenerate the maps) to tell xPDO how to join the tables. Be sure to back up your schema files first and add &createSchema=`0` when you re-run the snippet so it won't overwrite your manually edited schema file.

The best way to learn about how related objects are specified in the schema file is to look at the MODx schema file (core/modx/schema/modx.mysql.schema.xml) and at the end of this page: Revolution Objects. The modResource object section provides good examples.

Many-to-one Relationships

A resource can have only one parent, so in the modResource class object definition in the schema, you'll see this line below the field and index specifications:

<aggregate alias="Parent" class="modResource" local="parent" foreign="id" cardinality="one" owner="foreign" />

It is an 'aggregate alias' because it is a one-to-many or many-to-one relationship. The 'alias' is how the other objects will be referred to in a related objects function call (e.g. $resource->getOne('Parent'). The 'class' is the type of object that call will return. In this case, it's 'modResource' because the parent object is also a resource. The 'local' term specifies the name of the local field that points to the related object (in this case, it's the 'parent' field). The 'foreign' term refers to the field in the related object that the local field points to (usually the 'id' field). The 'cardinality' is 'one' because a resource can have only one parent. The owner of the 'id' field is the foreign object (the parent) so 'owner' is set to 'foreign'.

You could retrieve the foreign (parent) related object with this xPDO code:

$parentObject = $modx->resource->getOne('Parent');

One-to-Many Relationships

A resource can have more than one child, so there are a couple of differences in the specification for the Children alias.

<aggregate alias="Children" class="modResource" local="id" foreign="parent" cardinality="many" owner="local" />

It's still an 'aggregate alias' that points to a 'modResource object'. The 'local' field that connects the two tables, however, is the 'id' field. The field in the related (foreign) object (the child object in this case) that connects the two is the 'parent' field. The 'cardinality' is 'many' because we're in the modResource object and it can have many children. The 'owner' of the 'id' field is the resource so the owner is 'local'.

You could retrieve the foreign (children) related objects with this xPDO code:

$children = $modx->resource->getMany('Children');

Many-to-Many Relationships

A resource can belong to many resource groups and a resource group can have many related resources. That means they must be related by an intermediate (or 'intersect') object. In this case, the intersect object is called modResourceGroupResource. Each modResourceGroupResource object represents a single row in the intersection table. The row contains the ID of a resource group and the ID of a resource (the two actual fields are called 'document_group' and 'document'). In the schema, the relationship is expressed like this:

<composite alias="ResourceGroupResources" class="modResourceGroupResource" local="id" foreign="document" cardinality="many" owner="local" />

Because it involves a many-to-many relationship, it is a 'composite' rather than an 'aggregate' alias. The object pointed to by the 'alias' is the intersect object, a 'modResourcGroupResource' object, and it is related to the 'id' of the modResource object. In the foreign object the field relating to the object we're in (modResource) is the 'document' field. The 'cardinality' is 'many' because the resource can have many related modResourceGroupResource objects (each representing a resource group the resource belongs to). Like the Children alias, the modResource is the one with the ID so the 'owner' is 'local'.

If you look at the modResourceGroup object, you'll find a similar composite alias that also points to the ResourceGroupResources table. It looks like this:

<composite alias="ResourceGroupResources" class="modResourceGroupResource" local="id" foreign="document_group" cardinality="many" owner="local" />

Notice that is it the same except for the name of the 'foreign' field.

As you would expect, in the modResourceGroupResource intersect object specification, you'll find two aggregate aliases pointing to the modResource and the modResourceGroup objects. They are aggregate aliases rather than composite aliases because each modResourceGroupResource object is related to only one resource and only one resource group. Here are the two specifications:

<aggregate alias="ResourceGroup" class="modResourceGroup" key="id" local="document_group" foreign="id" cardinality="one" owner="foreign" />

<aggregate alias="Resource" class="modResource" key="id" local="document" foreign="id" cardinality="one" owner="foreign" />

Notice that these also have a 'key' entry specifying the 'id' field as the key.

More Information

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

Creating a Model With xPDO

Using Your xPDO Model

Using Custom Database Tables in your 3rd Party Components

Reverse Engineer xPDO Classes from Existing Database Table

Developing an Extra in MODX Revolution

 

	Enter your search terms Submit search form
	Web bobsguides.com