Setting Up Your First Table

Setting Up Your First Table

A queryable BerlinDB table usually has four small classes:

1. A Schema class that defines columns and indexes.
2. A Row class that shapes returned records.
3. A Query class that reads and writes records.
4. A Table class that creates, upgrades, and manages the physical database table.

The example below creates a widgets table for a plugin with the prefix acme.

Schema

namespace Acme\Database;

use BerlinDB\Database\Kern\Schema;

class WidgetSchema extends Schema {
	protected $columns = array(
		array(
			'name'       => 'id',
			'type'       => 'bigint',
			'length'     => 20,
			'unsigned'   => true,
			'primary'    => true,
			'extra'      => 'auto_increment',
			'in'         => true,
			'not_in'     => true,
			'cache_key'  => true,
			'sortable'   => true,
		),
		array(
			'name'       => 'uuid',
			'type'       => 'varchar',
			'length'     => 100,
			'uuid'       => true,
			'cache_key'  => true,
		),
		array(
			'name'       => 'name',
			'type'       => 'varchar',
			'length'     => 191,
			'default'    => '',
			'searchable' => true,
			'sortable'   => true,
		),
		array(
			'name'       => 'status',
			'type'       => 'varchar',
			'length'     => 20,
			'default'    => 'active',
			'in'         => true,
			'not_in'     => true,
		),
		array(
			'name'       => 'created',
			'type'       => 'datetime',
			'created'    => true,
			'date_query' => true,
			'sortable'   => true,
		),
		array(
			'name'       => 'modified',
			'type'       => 'datetime',
			'modified'   => true,
			'date_query' => true,
			'sortable'   => true,
		),
	);

	protected $indexes = array(
		array(
			'name'    => 'status',
			'type'    => 'key',
			'columns' => array( 'status' ),
		),
		array(
			'name'    => 'uuid',
			'type'    => 'unique',
			'columns' => array( 'uuid' ),
		),
	);
}

Row

namespace Acme\Database;

use BerlinDB\Database\Kern\Row;

class Widget extends Row {
	protected $primary_column = 'id';

	protected $casts = array(
		'id' => 'intval',
	);
}

The row object is where application-facing compatibility belongs. If a database column is renamed later, the row object can preserve old property names or formatting without leaking schema churn into the rest of the plugin.

Query

namespace Acme\Database;

use BerlinDB\Database\Kern\Query;

class WidgetQuery extends Query {
	protected $table_name       = 'widgets';
	protected $table_alias      = 'w';
	protected $table_schema     = WidgetSchema::class;
	protected $item_name        = 'widget';
	protected $item_name_plural = 'widgets';
	protected $item_shape       = Widget::class;
	protected $cache_group      = 'acme_widgets';
}

Use the query class in application code:

$query = new WidgetQuery();

$widget_id = $query->add_item(
	array(
		'name'   => 'Example Widget',
		'status' => 'active',
	)
);

$widget = $query->get_item( $widget_id );

Table

namespace Acme\Database;

use BerlinDB\Database\Kern\Table;

class WidgetsTable extends Table {
	protected $name           = 'widgets';
	protected $description    = 'Acme widgets';
	protected $prefix         = 'acme';
	protected $version        = '1.0.0';
	protected $db_version_key = 'acme_widgets_db_version';
	protected $schema         = WidgetSchema::class;
}

Create or upgrade the table from plugin bootstrap code:

add_action(
	'admin_init',
	static function () {
		$table = new Acme\Database\WidgetsTable();
		$table->maybe_upgrade();
	}
);

During the PHPUnit test suite, BerlinDB detects the test environment and can force upgrades immediately.

Naming Notes

• Table::$name is the unprefixed table name.
• Table::$prefix is your plugin or application prefix.
• WordPress' $wpdb->prefix is still prepended by the connection adapter.
• In the example above, the final table name is usually wp_acme_widgets.

Next

Read Writing Queries for filtering, sorting, CRUD, and parser-backed query vars.