HTTPS SSH

WordPress Database Extension (DBX) Plugin

This is a developer plugin that is meant to help you store "meta data" in extension tables which you can configure here rather than using the generic wp_postmeta mechanisms that come out of the box with WordPress.

Version: 0.6
Wordpress: 3.0+
PHP: 5.3+
WP Plugins: no dependencies
Admin GUI: Twitter Bootstrap ( for admin menu only )

This plug-in is covered by the GPL license and we'd be happy for you to use and/or contribute to its development. This plug-in is joint hosted on GitHub and BitBucket. Both locations have precisely the same material. For extended documentation (which at the moment is a bit out of date) you can go here: DBX Wiki. The documentation below will give a quick overview of the plugin as well as a defect list and change log.

Basic usage

The DBX plugin is developer plugin and activating alone will have no perceptable impact to your WordPress installation. It will, instead, provide capabilities through a global object called $wpdb_x. The idea with this plug-in is that you can activate it and then create your own plugin that is a single file configuration file. This helps to ensure that the plug-in code and your configuration are completely decoupled. If you prefer you can use your functions.php file instead of the plugin architecture but in both cases you will hook into the "wp_dbx_init" and "wp_dbx_mapping" hooks to do your configuration.

Hooking into DBX

As was mentioned above there are two key action events that are triggered by DBX that you'll want to hook into. This will look something like this:

add_action( 'wp_dbx_init' , 'initialise_database_extensions' );
add_action( 'wp_dbx_mapping' , 'initialise_post_mappings' );

Initalization Config

The first section that you'll configure is the "initialisation" section where you'll describe the Extension Tables that you need. This process is one part DDL and one part meta-sugar. The DDL is there so that the plug-in can facilitate creating the database tables for you. The meta-sugar is there to help these fields be handled in the best possible way. This sugar right now is most noticable in the admin interface where you can specify what type of HTML widget would best be able to display a particular custom field but this same information could be leveraged by front-end code via the API provided.

function initialise_database_extensions () {
    global $wpdb_x;

    // create extension table
    // ------------------------         
    $wpdb_x->add_extension_table ( 'activity');
    $wpdb_x->add_extension_cols ( 'activity' , array (
            'start_time' => array ( 'name' => 'Start Time' , 'type' => "datetime" , 'desc' => "start-time of the activity" ),
            'end_time' => array ( 'name' => 'End Time' , 'type' => 'datetime' , 'desc' => "end-time of the activity"),
            'primary_location' => array ( 'name' => 'Primary Location' , 'type' => 'bigint' , 'desc' => "the primary geographic location that this activity centred around"),
            'importance' => array ( 'name' => 'Importance' , 'type' => 'varchar(2)' , 'desc' => "The relative importance that this activity has"),
            'weather_type' => array ( 'name' => 'Weather' , 'type' => 'bigint' , 'desc' => "the type or category of weather when this event was recorded"),
            'app_id' => array ( 'name' => 'App' , 'type' => "bigint(20)" , 'desc' => "the application id that recorded this activity"),
            'app_uri' => array ( 'name' => 'App Detail' , 'type' => "varchar(150)" , 'desc' => "the URI for details of this activity")
    ));

    $wpdb_x->add_extension_table ( 'exercise' );
    $wpdb_x->add_extension_cols ( 'exercise' , array (
            'exercise_type' => array ( 'name' => 'Exercise Type' ,'type' => "varchar(40)" , 'desc' => "game, cardio, strength (def by Taxonomy)" ),
            'exercise_sub_type' => array ( 'name' => 'Sub Type' , 'type' => 'varchar(40)' , 'desc' => '"interval training", "long run", etc.')
    ));
    $wpdb_x->add_extension_attributes ( 'exercise' , array (
            'route_map' => array ( 'type' => 'linked-list' , 'desc' => 'A linked list of location way-points and associated meta data.' ),
            'score_tabular' => array ( 'type' => 'assoc-array' , 'desc' => 'A tabular scoring system for sports like tennis and baseball' ),
            'intervals' => array ( 'type' => 'assoc-array' , 'desc' => 'A way to segment the exercise into parts; each part than can have a bag of attributes associated with it.')
    ));

    // constraint system
    // -----------------------
    $wpdb_x->add_col_enum ( 'activity' , 'importance' , array (
                            'High' => 'H', 
                            'Medium' => 'M',
                            'Low' => 'L' ) 
    );
    // you can specify foreign keys ( primary key is done for you automatically )
    $wpdb_x->add_fk ( 'activity' , 'weather_type' , 'weather_type.id');
    // you can add a null constraint on a column
    $wpdb_x->add_not_null_constraint ( 'exercise' , array ( 'sport_type', 'exercise_type' ) );
    $wpdb_x->add_display_defaults ();

    // Execute DDL
    // -------------------------
    $wpdb_x->create_table ('activity');
    // note: this will not overwrite an existing extension table by the same name
}

Mapping

The next (and final) configuration section is the mapping section that maps Extension Tables to Custom Post Types (CPT). This would look something like the following:

function initialise_post_mappings () {
    global $wpdb_x;
    $wpdb_x->map_post_to_extension ( 'book' , array ( 'activity' ) );
    $wpdb_x->map_post_to_extension ( 'foobar' , array ( 'activity', 'exercise' ) );
}

Admin Panel

There is a admin menu now available which is becoming more interesting with each release. You'll find it under the WordPress Tools menu. The admin panel has three main tabs:

  1. Extension Tables
    • Column table listing (including all column constraints)
    • JSON Dictionary
    • Field Groupings
  2. Post-Type Relationships
    • Right now it just show which custom post types have been mapped to 1 (or more) extension tables.
    • In the future there will be other relationships attached to the "post-type" level which will slot in here
  3. About
    • basic info about the plugin including link back to git repository

Note: the admin screens use Twitter's Bootstrap for formatting (it will include the necessary JS and CSS into admin screens if it's not already being loaded).

At some point in the future we will add the ability to "create" the database table as an option on this page as well as providing "sync status" that talks about the variations (if any) between the DB Model and what is modelled in code. Currently it only checks if the database table exists and will display an exclamation mark icon if the table is missing.

Note: in the interim, you can just put a $wpdb_x->create_table(ext_table) for each table and it will then add the tables to the database if it doesn't exist (it will never over-write an existing table so sync issues can occur)

Advanced Usage

If you're using the basic functionality and want to extend the power of this plug-in there are some additional things that you can now do.

Field Groupings

The first advanced area is something called "Field Groupings" and within these "Field Groupings" are "Group Filters". The idea here is that you may want to indicate that a certain subset of fields in an extension table are related. This is a "Field Grouping". Why would you want to do this? For one, in the future the admin PostMetaBox will respond to these groupings by putting these fields into a visually distinguished group. Furthermore you may decide to build frontend GUI controls that know how to manipulate these groups. Of course if you feel you have no need for them you should feel free to ignore this feature. A group filter can be configured as follows:

$body_fat = $wpdb_x->add_grouping ( 'body_measurements' , 'discrete_body_fat', 'Body Fat (discrete body areas)');
$body_fat->add_fields ( array ( 'body_fat_bicep' , 'body_fat_tricep' , 'body_fat_subscapular' , 'body_fat_iliac_crest' )  );

In this situation, the familiar global $wpdb_x object serves as an object factor and returns an object of DBX_FieldGrouping to allow for further configuration. In the example above the first thing done was to add a set of fields to the group. For some use cases this is enough. It will (in a future release) immediately improve the PostMetaBox admin interface and your own code can access this group at any time by asking for the groups for a given extension table like this:

$field_grouping_array_of_objects = $wpdb_x->get_groupings ( $ext_table );

Having fun yet? If not there's more to get you excited. There is also a concept called "Group Filters". A filter represents a subset of fields in the overall group and can be overlapping with other filter subsets. This feature may at some point have a UI feature added to the PostMetaBox but it certainly can be leveraged by your frontend UI framework to switch between differnt filter sets. This is particularly useful if only certain subsets are relevant based on an external condition. A filter can be setup like this:

$body_fat->add_filter ( 'filter-name' , array ( 'body_fat_bicep' , 'body_fat_tricep' ) );

Known Defects / Feature Enhancements

  • FEATURE: The "implicit" mode where this plugin transparently intercepts all calls to postmeta has not been implemented yet (although it has been implemented in a way such that this shouldn't be too hard)
  • BUG: The date field does not include time information. It should include time info unless the activity is a full day activity.
  • BUG: When deleting an activity from the admin panel you get an error but it does actually delete the record.

Changelog

0.6

  • Added ability to add a column directly from the Admin screen when code and database are out of sync (and code has an additional column defined)
  • Added "nullable" contraint checking in the DB <-> Code sync checks
  • Small bug fixes

0.5

  • Added functionality to display enumerated values as drop downs in the admin post-type editor (postmetabox)
  • Started to add -- although not functionally complete -- ability to create "abstraction maps". This feature should be built out more completely if future releases.
  • Fixed a regression with the create_table function introduced earlier (v0.4.9?)

0.4.9

  • Added datepicker that has both time and date to PostMeta boxes(based off jQuery-UI plugin). Not fully tested yet so hence the odd version number.
  • Added ability to tag certain fields as "key fields" and then filter on these in the PostMeta box
  • Tightened up the CSS not to interfere with other aspects of the admin menu
  • This is not a fully stabilised release but core features should work just fine

0.4

  • Added ways to filter the field list when viewing the PostMetaBox in the admin panel
  • Broke PostMetaBoxWidget class out of PostMetaBox
  • Adjusted some badly behaving CSS from Bootstrap

0.3

  • Added JSON dictionary information to the Extension Tables tab
  • Added "Field Groupings" functionality and added to admin dashboard under Extension Table tab

0.2

  • Added admin page so you can view:
    • what extension tables have been setup
    • what relationships exist between CPT and Table Extensions

0.1

  • Initial release
  • It has been tested in a single location for a single set of requirements and works as intended
  • It only operates in "explicit" mode right now
  • Very basic documentation ... more to come