IronBound DB

Complex_Query.php

<?php
/**
 * API for making complex queries.
 *
 * @author      Iron Bound Designs
 * @since       1.0
 * @copyright   2015 (c) Iron Bound Designs.
 * @license     GPLv2
 */

namespace IronBound\DB\Query;

use IronBound\Cache\Cache;
use IronBound\DB\Manager;
use IronBound\DB\Model;
use IronBound\DB\Query\Tag\Limit;
use IronBound\DB\Query\Tag\Order;
use IronBound\DB\Query\Tag\Select;
use IronBound\DB\Query\Tag\Where;
use IronBound\DB\Table\Table as Table;

/**
 * Class Base
 *
 * @package IronBound\DB\Query
 */
abstract class Complex_Query {

	/**
	 * @var array
	 */
	protected $args = array();

	/**
	 * @var \IronBound\DB\Query\Simple_Query|null
	 */
	protected $db_query;

	/**
	 * @var Table
	 */
	protected $table;

	/**
	 * @var string
	 */
	protected $sql;

	/**
	 * @var int|null
	 */
	protected $total_items = null;

	/**
	 * @var array
	 */
	protected $results = array();

	/**
	 * Constructor.
	 *
	 * @param Table $table
	 * @param array $args
	 */
	public function __construct( Table $table, array $args = array() ) {

		$this->table    = $table;
		$this->db_query = Manager::make_simple_query_object( $table->get_slug() );

		$this->args = wp_parse_args( $args, $this->get_default_args() );

		if ( $this->args['items_per_page'] != '-1' && $this->args['sql_calc_found_rows'] === null ) {
			$this->args['sql_calc_found_rows'] = true;
		}

		$this->sql = $this->build_sql();

		$this->query();
	}

	/**
	 * Get the total items found ignoring pagination.
	 *
	 * If sql_calc_found_rows is set to false, the return value will be null.
	 *
	 * @since 1.0
	 *
	 * @return int|null
	 */
	public function get_total_items() {
		return $this->total_items;
	}

	/**
	 * Retrieve the queried results.
	 *
	 * @since 1.0
	 *
	 * @return Model[]|\stdClass[]|mixed[]
	 */
	public function get_results() {
		return $this->results;
	}

	/**
	 * Debug function.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public function _get_sql() {
		return $this->sql;
	}

	/**
	 * Get the default args.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	protected function get_default_args() {
		return array(
			'order'               => array(),
			'items_per_page'      => - 1,
			'page'                => 1,
			'sql_calc_found_rows' => null,
			'return_value'        => 'object',
			'distinct'            => false
		);
	}

	/**
	 * Get a default arg.
	 *
	 * @since 1.0
	 *
	 * @param string $arg
	 *
	 * @return mixed
	 */
	protected function get_default_arg( $arg ) {

		$args = $this->get_default_args();

		if ( isset( $args[ $arg ] ) ) {
			return $args[ $arg ];
		} else {
			throw new \InvalidArgumentException();
		}
	}

	/**
	 * Query the database and store the results.
	 *
	 * @since 1.0
	 */
	protected function query() {
		$results = $GLOBALS['wpdb']->get_results( $this->sql );

		// we query for found rows first to prevent instantiation of record objects from interfering with the count
		if ( $this->args['sql_calc_found_rows'] ) {

			$count_results     = $GLOBALS['wpdb']->get_results( "SELECT FOUND_ROWS() AS COUNT" );
			$this->total_items = $count_results[0]->COUNT;
		} elseif ( $this->args['return_value'] == 'count' ) {
			$this->total_items = $results[0]->COUNT;
		}

		$this->results = $this->parse_results( $results );
	}

	/**
	 * Parse the results returned from the DB.
	 *
	 * @since 1.0
	 *
	 * @param array $results
	 *
	 * @return array
	 */
	protected function parse_results( $results ) {

		if ( is_array( $this->args['return_value'] ) ) {
			return $results;
		} elseif ( $this->args['return_value'] == 'count' ) {
			return $results[0]->COUNT;
		} elseif ( $this->args['return_value'] != 'object' ) {
			$values = array();
			$field  = $this->args['return_value'];

			foreach ( $results as $result ) {
				$values[] = $result->$field;
			}

			return $values;
		} else {
			$records = array();

			foreach ( $results as $result ) {

				$object = $this->make_object( $result );
				Cache::update( $object );

				$records[ $result->{$this->table->get_primary_key()} ] = $object;
			}

			return $records;
		}
	}

	/**
	 * Build the sql query.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	protected abstract function build_sql();

	/**
	 * Convert data to its object.
	 *
	 * @since 1.0
	 *
	 * @param \stdClass $data
	 *
	 * @return Model
	 */
	protected abstract function make_object( \stdClass $data );

	/**
	 * Build the select query.
	 *
	 * @since 1.0
	 *
	 * @param string $alias
	 *
	 * @return Select
	 */
	protected function parse_select( $alias = 'q' ) {

		if ( is_array( $this->args['return_value'] ) ) {
			$select = new Select( null );

			foreach ( $this->args['return_value'] as $column ) {
				$select->also( "$alias.$column" );
			}
		} elseif ( $this->args['return_value'] == 'count' ) {
			$select = new Select( 'COUNT(1)', 'COUNT' );
		} elseif ( $this->args['return_value'] != 'object' ) {
			$select = new Select( "$alias." . $this->args['return_value'] );
		} else {
			$select = new Select( "$alias.*" );
		}

		if ( $this->args['sql_calc_found_rows'] ) {
			$select->calc_found_rows();
		}

		$select->filter_distinct( $this->args['distinct'] );

		return $select;
	}

	/**
	 * Generic Where builder for queries that follow the in/not_in pattern.
	 *
	 * @since 1.0
	 *
	 * @param string $column
	 * @param array  $in
	 * @param array  $not_in
	 *
	 * @return Where|null
	 */
	protected function parse_in_or_not_in_query( $column, array $in, array $not_in ) {

		if ( ! empty( $in ) ) {

			foreach ( $in as $key => $value ) {
				$in[ $key ] = $this->db_query->escape_value( $column, $value );
			}

			$in_where = new Where( "q.$column", true, $in );
		}

		if ( ! empty( $not_in ) ) {

			foreach ( $not_in as $key => $value ) {
				$not_in[ $key ] = $this->db_query->escape_value( $column, $value );
			}

			$not_where = new Where( "q.$column", false, $not_in );

			if ( isset( $in_where ) ) {
				$in_where->qAnd( $not_where );

				return $in_where;
			} else {
				return $not_where;
			}
		}

		if ( isset( $in_where ) ) {
			return $in_where;
		} else {
			return null;
		}
	}

	/**
	 * Parse the orderby query. There is always a default or.
	 *
	 * @since 1.0
	 *
	 * @param string $alias
	 *
	 * @return Order
	 */
	protected function parse_order( $alias = 'q' ) {

		if ( ! is_array( $this->args['order'] ) && $this->args['order'] === 'rand' ) {
			return new Order( Order::RAND );
		} elseif ( ! is_array( $this->args['order'] ) ) {
			throw new \InvalidArgumentException( "Order must either be 'rand' or an array of columns to directions." );
		}

		$white_list = $this->table->get_columns();

		foreach ( $this->args['order'] as $column => $direction ) {

			$direction = strtoupper( $direction );

			if ( ! in_array( $direction, array( Order::ASC, Order::DESC ) ) ) {
				throw new \InvalidArgumentException( "Invalid order direction $direction for column $column." );
			}

			$column = $this->translate_order_by_to_column_name( $column );

			if ( ! isset( $white_list[ $column ] ) ) {
				throw new \InvalidArgumentException( "Invalid order column $column." );
			}

			$column = "{$alias}.$column";

			if ( ! isset( $order ) ) {
				$order = new Order( $column, $direction );
			} else {
				$order->then( $column, $direction );
			}
		}

		if ( isset( $order ) ) {
			return $order;
		} else {
			return new Order( "{$alias}.{$this->table->get_primary_key()}", Order::ASC );
		}
	}

	/**
	 * Translate a human given order by, to its corresponding column name.
	 *
	 * @since 1.0
	 *
	 * @param string $order_by
	 *
	 * @return string
	 */
	protected function translate_order_by_to_column_name( $order_by ) {

		return $order_by;
	}

	/**
	 * Parse the pagination query.
	 *
	 * @since 1.0
	 *
	 * @return Limit|null
	 */
	protected function parse_pagination() {

		if ( $this->args['items_per_page'] == - 1 ) {
			return null;
		}

		if ( $this->args['page'] < 1 ) {
			throw new \InvalidArgumentException( "page parameter must be at least 1." );
		}

		$per_page = absint( $this->args['items_per_page'] );
		$page     = absint( $this->args['page'] );

		$count  = $per_page;
		$offset = $per_page * ( $page - 1 );

		return new Limit( $count, $offset );
	}
}

Complex_Query_Releases.php

<?php
/**
 * Query releases records.
 *
 * @author Iron Bound Designs
 * @since  1.0
 */

namespace ITELIC_API\Query;
use IronBound\DB\Model;
use IronBound\DB\Query\Complex_Query;
use ITELIC\Release;
use IronBound\DB\Manager;
use IronBound\DB\Query\Builder;
use IronBound\DB\Query\Tag\From;
use IronBound\DB\Query\Tag\Where;
use IronBound\DB\Query\Tag\Where_Date;

/**
 * Class Releases
 * @package ITELIC_API\Query
 */
class Releases extends Complex_Query {

	/**
	 * Constructor.
	 *
	 * @param array $args
	 */
	public function __construct( array $args = array() ) {
		parent::__construct( Manager::get( 'itelic-releases' ), $args );
	}

	/**
	 * Get the default args.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	protected function get_default_args() {
		$existing = parent::get_default_args();

		$new = array(
			'product'          => '',
			'product__in'      => array(),
			'product__not_in'  => array(),
			'download'         => '',
			'download__in'     => array(),
			'download__not_in' => array(),
			'status'           => 'any',
			'type'             => 'any',
			'version_search'   => '',
			'changelog_search' => '',
			'start_date'       => '',
		);

		return wp_parse_args( $new, $existing );
	}

	/**
	 * Convert data to its object.
	 *
	 * @since 1.0
	 *
	 * @param \stdClass $data
	 *
	 * @return Model
	 */
	protected function make_object( \stdClass $data ) {
		return new Release( $data );
	}

	/**
	 * Build the sql query.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	protected function build_sql() {

		$builder = new Builder();

		$select = $this->parse_select();
		$from   = new From( $this->table->get_table_name( $GLOBALS['wpdb'] ), 'q' );

		$where = new Where( 1, true, 1 );

		if ( ( $product = $this->parse_product() ) !== null ) {
			$where->qAnd( $product );
		}

		if ( ( $download = $this->parse_download() ) !== null ) {
			$where->qAnd( $download );
		}

		if ( ( $status = $this->parse_status() ) !== null ) {
			$where->qAnd( $status );
		}

		if ( ( $type = $this->parse_type() ) !== null ) {
			$where->qAnd( $type );
		}

		if ( ( $version_search = $this->parse_version_search() ) !== null ) {
			$where->qAnd( $version_search );
		}

		if ( ( $changelog_search = $this->parse_changelog_search() ) !== null ) {
			$where->qAnd( $changelog_search );
		}

		if ( ( $start_date = $this->parse_start_date() ) !== null ) {
			$where->qAnd( $start_date );
		}

		$order = $this->parse_order();
		$limit = $this->parse_pagination();

		$builder->append( $select )->append( $from );

		$builder->append( $where );
		$builder->append( $order );

		if ( $limit !== null ) {
			$builder->append( $limit );
		}

		return $builder->build();
	}

	/**
	 * Parse the product where.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_product() {

		if ( ! empty( $this->args['product'] ) ) {
			$this->args['product__in'] = array( $this->args['key'] );
		}

		return $this->parse_in_or_not_in_query( 'product', $this->args['product__in'], $this->args['product__not_in'] );
	}

	/**
	 * Parse the download where.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_download() {

		if ( ! empty( $this->args['download'] ) ) {
			$this->args['download__in'] = array( $this->args['download'] );
		}

		return $this->parse_in_or_not_in_query( 'download', $this->args['download__in'], $this->args['download__not_in'] );
	}

	/**
	 * Parse the status query.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_status() {
		if ( $this->args['status'] === 'any' ) {
			return null;
		} else {
			$white_list = Release::get_statuses();
			$statuses   = (array) $this->args['status'];

			foreach ( $statuses as $status ) {
				if ( ! isset( $white_list[ $status ] ) ) {
					throw new \InvalidArgumentException( "Invalid status $status" );
				}
			}

			return new Where( 'status', true, (array) $this->args['status'] );
		}
	}

	/**
	 * Parse the status query.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_type() {
		if ( $this->args['type'] === 'any' ) {
			return null;
		} else {
			$white_list = Release::get_types();
			$types   = (array) $this->args['type'];

			foreach ( $types as $type ) {
				if ( ! isset( $white_list[ $type ] ) ) {
					throw new \InvalidArgumentException( "Invalid type $type" );
				}
			}

			return new Where( 'type', true, (array) $this->args['type'] );
		}
	}

	/**
	 * Parse the version search.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_version_search() {

		if ( empty( $this->args['version_search'] ) ) {
			return null;
		}

		return new Where( 'q.version', 'LIKE', esc_sql( $this->args['version_search'] ) );
	}

	/**
	 * Parse the changelog search.
	 *
	 * @since 1.0
	 *
	 * @return Where|null
	 */
	protected function parse_changelog_search() {

		if ( empty( $this->args['changelog_search'] ) ) {
			return null;
		}

		return new Where( 'q.changelog', 'LIKE', esc_sql( $this->args['changelog_search'] ) );
	}

	/**
	 * Parse the start date query.
	 *
	 * @since 1.0
	 *
	 * @return Where_Date|null
	 */
	protected function parse_start_date() {
		if ( ! empty( $this->args['start_date'] ) ) {
			$date_query = new \WP_Date_Query( $this->args['start_date'], 'q.start_date' );

			return new Where_Date( $date_query );
		} else {
			return null;
		}
	}

}

Model.php

<?php
/**
 * Abstract model class for models built upon our DB table.
 *
 * @author      Iron Bound Designs
 * @since       1.0
 * @copyright   2015 (c) Iron Bound Designs.
 * @license     GPLv2
 */

namespace IronBound\DB;

use IronBound\Cache\Cacheable;
use IronBound\Cache\Cache;
use IronBound\DB\Table\Table;

/**
 * Class Model
 *
 * @package IronBound\DB;
 */
abstract class Model implements Cacheable, \Serializable {

	/**
	 * Retrieve this object.
	 *
	 * @since 1.0
	 *
	 * @param int|string $pk Primary key of this record.
	 *
	 * @returns self|null
	 */
	public static function get( $pk ) {

		$data = self::get_data_from_pk( $pk );

		if ( $data ) {

			$class  = get_called_class();
			$object = new $class( (object) $data );

			if ( ! self::is_data_cached( $pk ) ) {
				Cache::update( $object );
			}

			return $object;
		} else {
			return null;
		}
	}

	/**
	 * Get data for a primary key.
	 *
	 * @since 1.0
	 *
	 * @param int|string $pk Primary key for this record.
	 *
	 * @return \stdClass|null
	 */
	protected static function get_data_from_pk( $pk ) {

		$data = Cache::get( $pk, static::get_cache_group() );

		if ( ! $data ) {

			$table = static::get_table();
			$db    = Manager::make_simple_query_object( $table->get_slug() );

			$data = $db->get( $pk );
		}

		return $data ? (object) $data : null;
	}

	/**
	 * Check if data is cached.
	 *
	 * @since 1.0
	 *
	 * @param int|string $pk Primary key for this record.
	 *
	 * @return bool
	 */
	protected static function is_data_cached( $pk ) {

		$data = Cache::get( $pk, static::get_cache_group() );

		return ! empty( $data );
	}

	/**
	 * Init an object.
	 *
	 * @since 1.0
	 *
	 * @param \stdClass $data
	 */
	protected abstract function init( \stdClass $data );

	/**
	 * Get the table object for this model.
	 *
	 * @since 1.0
	 *
	 * @returns Table
	 */
	protected static function get_table() {
		// override this in child classes.
		throw new \UnexpectedValueException();
	}

	/**
	 * Update a certain value.
	 *
	 * @since 1.0
	 *
	 * @param string $key   DB column to update.
	 * @param mixed  $value New value.
	 *
	 * @return bool
	 */
	protected function update( $key, $value ) {

		$table = static::get_table();
		$db    = Manager::make_simple_query_object( $table->get_slug() );

		$data = array(
			$key => $value
		);

		$res = $db->update( $this->get_pk(), $data );

		if ( $res ) {
			Cache::update( $this );
		}

		return $res;
	}

	/**
	 * Delete this object.
	 *
	 * @since 1.0
	 *
	 * @throws Exception
	 */
	public function delete() {

		$table = static::get_table();
		$db    = Manager::make_simple_query_object( $table->get_slug() );

		$db->delete( $this->get_pk() );

		Cache::delete( $this );
	}

	/**
	 * Get the data we'd like to cache.
	 *
	 * This is a bit magical. It iterates through all of the table columns,
	 * and checks if a getter for that method exists. If so, it pulls in that
	 * value. Otherwise, it will pull in the default value. If you'd like to
	 * customize this you should override this function in your child model
	 * class.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public function get_data_to_cache() {

		$data = array();

		foreach ( static::get_table()->get_column_defaults() as $col => $default ) {

			if ( method_exists( $this, 'get_' . $col ) ) {
				$method = "get_$col";

				$val = $this->$method();

				if ( is_object( $val ) ) {

					if ( $val instanceof Model ) {
						$val = $val->get_pk();
					} else if ( $val instanceof \DateTime ) {
						$val = $val->format( 'Y-m-d H:i:s' );
					} else if ( isset( $val->ID ) ) {
						$val = $val->ID;
					} else if ( isset( $val->id ) ) {
						$val = $val->id;
					} else if ( isset( $val->term_id ) ) {
						$val = $val->term_id;
					} else if ( isset( $val->comment_ID ) ) {
						$val = $val->comment_ID;
					}
				}

				$data[ $col ] = $val;
			} else {
				$data[ $col ] = $default;
			}
		}

		return $data;
	}

	/**
	 * Get the cache group for this record.
	 *
	 * By default this returns a string in the following format
	 * "df-{$table_slug}".
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public static function get_cache_group() {
		return static::get_table()->get_slug();
	}

	/**
	 * (PHP 5 &gt;= 5.1.0)<br/>
	 * String representation of object
	 *
	 * @link http://php.net/manual/en/serializable.serialize.php
	 * @return string the string representation of the object or null
	 */
	public function serialize() {
		return serialize( array(
			'pk' => $this->get_pk()
		) );
	}

	/**
	 * (PHP 5 &gt;= 5.1.0)<br/>
	 * Constructs the object
	 *
	 * @link http://php.net/manual/en/serializable.unserialize.php
	 *
	 * @param string $serialized <p>
	 *                           The string representation of the object.
	 *                           </p>
	 *
	 * @return void
	 */
	public function unserialize( $serialized ) {
		$data = unserialize( $serialized );

		$this->init( self::get_data_from_pk( $data['pk'] ) );
	}
}

Release.php

<?php
/**
 * Represents release objects.
 *
 * @author Iron Bound Designs
 * @since  1.0
 */

namespace ITELIC;

use IronBound\Cache\Cache;
use IronBound\DB\Model;
use IronBound\DB\Table\Table;
use IronBound\DB\Manager;
use IronBound\DB\Exception as DB_Exception;

/**
 * Class Release
 *
 * @package ITELIC
 */
class Release extends Model {

	/**
	 * @var int
	 */
	private $ID;

	/**
	 * @var \IT_Exchange_Product
	 */
	private $product;

	/**
	 * @var int
	 */
	private $download;

	/**
	 * @var string
	 */
	private $version;

	/**
	 * @var string
	 */
	private $status;

	/**
	 * @var string
	 */
	private $type;

	/**
	 * @var string
	 */
	private $changelog;

	/**
	 * @var \DateTime|null
	 */
	private $start_date;

	/**
	 * Constructor.
	 *
	 * @param \stdClass $data
	 */
	public function __construct( \stdClass $data ) {
		$this->init( $data );
	}

	/**
	 * Init an object.
	 *
	 * @since 1.0
	 *
	 * @param \stdClass $data
	 */
	protected function init( \stdClass $data ) {
		$this->ID      = $data->ID;
		$this->product = it_exchange_get_product( $data->product );

		if ( ! $this->product ) {
			throw new \InvalidArgumentException( "Invalid product." );
		}

		$this->download = (int) $data->download;
		$this->version  = $data->version;

		if ( array_key_exists( $data->status, self::get_statuses() ) ) {
			$this->status = $data->status;
		} else {
			throw new \InvalidArgumentException( "Invalid status." );
		}

		if ( array_key_exists( $data->type, self::get_types() ) ) {
			$this->type = $data->type;
		} else {
			throw new \InvalidArgumentException( "Invalid type." );
		}

		$this->changelog = $data->changelog;

		if ( $data->start_date && $data->start_date != '0000-00-00 00:00:00' ) {
			$this->start_date = new \DateTime( $data->start_date );
		}
	}

	/**
	 * Create a new release record.
	 *
	 * If status is set to active, the start date will automatically be set to
	 * now.
	 *
	 * @since 1.0
	 *
	 * @param \IT_Exchange_Product $product
	 * @param \WP_Post             $file Attachment of the download
	 * @param string               $version
	 * @param string               $type
	 * @param string               $status
	 * @param string               $changelog
	 *
	 * @return Release|null
	 * @throws DB_Exception
	 */
	public static function create( \IT_Exchange_Product $product, \WP_Post $file, $version, $type, $status = '', $changelog = '' ) {

		if ( empty( $status ) ) {
			$status = self::STATUS_DRAFT;
		}

		if ( ! array_key_exists( $status, self::get_statuses() ) ) {
			throw new \InvalidArgumentException( "Invalid status." );
		}

		if ( ! array_key_exists( $type, self::get_types() ) ) {
			throw new \InvalidArgumentException( "Invalid type." );
		}

		if ( get_post_type( $file ) != 'attachment' ) {
			throw new \InvalidArgumentException( "Invalid update file." );
		}

		if ( ! it_exchange_product_has_feature( $product->ID, 'licensing' ) ) {
			throw new \InvalidArgumentException( "Product given does not have the licensing feature enabled." );
		}

		$data = array(
			'product'   => $product->ID,
			'download'  => $file->ID,
			'version'   => $version,
			'type'      => $type,
			'status'    => $status,
			'changelog' => wp_kses_post( $changelog )
		);

		$db = Manager::make_simple_query_object( 'itelic-releases' );
		$ID = $db->insert( $data );

		$release = self::get( $ID );

		if ( $release ) {

			Cache::add( $release );
		}

		return $release;
	}

	/**
	 * Pause this release.
	 *
	 * @since 1.0
	 */
	public function pause() {

		if ( $this->status != 'paused' ) {
			$this->status = 'paused';
			$this->update( 'status', 'paused' );
		}

		$prev_download = get_post_meta( $this->product->ID, '_itelic_prev_download', true );
		$prev_version  = get_post_meta( $this->product->ID, '_itelic_prev_version', true );

		$download_id             = it_exchange_get_product_feature( $this->product->ID, 'licensing', array( 'field' => 'update-file' ) );
		$download_data           = get_post_meta( $download_id, '_it-exchange-download-info', true );
		$download_data['source'] = $prev_download;
		update_post_meta( $download_id, '_it-exchange-download-info', $download_data );

		it_exchange_update_product_feature( $this->get_product()->ID, 'licensing', array(
			'version' => $prev_version
		) );

		/**
		 * Fires when a release is paused.
		 *
		 * @since 1.0
		 *
		 * @param Release $this
		 */
		do_action( 'itelic_pause_release', $this );
	}

	/**
	 * Get the unique pk for this record.
	 *
	 * @since 1.0
	 *
	 * @return mixed (generally int, but not necessarily).
	 */
	public function get_pk() {
		return $this->ID;
	}

	/**
	 * Retrieve the ID of this release.
	 *
	 * @since 1.0
	 *
	 * @return int
	 */
	public function get_ID() {
		return $this->get_pk();
	}

	/**
	 * Get the product this release corresponds to.
	 *
	 * @since 1.0
	 *
	 * @return \IT_Exchange_Product
	 */
	public function get_product() {
		return $this->product;
	}

	/**
	 * Get the attachment file post.
	 *
	 * @since 1.0
	 *
	 * @return \WP_Post
	 */
	public function get_download() {
		return get_post( $this->download );
	}

	/**
	 * Change the download this release corresponds to.
	 *
	 * @since 1.0
	 *
	 * @param int $download
	 */
	public function set_download( $download ) {

		if ( get_post_type( $download ) != 'attachment' ) {
			throw new \InvalidArgumentException( "Invalid post type for download." );
		}

		$this->download = $download;

		$this->update( 'download', $download );
	}

	/**
	 * Get the status of this Release.
	 *
	 * @since 1.0
	 *
	 * @param bool $label
	 *
	 * @return string
	 */
	public function get_status( $label = false ) {

		if ( $label ) {
			$labels = self::get_statuses();

			return $labels[ $this->status ];
		}

		return $this->status;
	}

	/**
	 * Set the status of this release.
	 *
	 * @since 1.0
	 *
	 * @param string $status
	 */
	public function set_status( $status ) {

		if ( ! array_key_exists( $status, self::get_statuses() ) ) {
			throw new \InvalidArgumentException( "Invalid status." );
		}

		if ( $this->status == 'draft' || $this->status == 'paused' && $status == 'active' ) {
			$this->activate();
		}

		if ( $this->status == 'active' && $status == 'paused') {
			$this->pause();
		}

		$this->status = $status;
		$this->update( 'status', $status );
	}

	/**
	 * Get a list of the various statuses.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public static function get_statuses() {
		return array(
			'draft'    => __( "Draft", Plugin::SLUG ),
			'active'  => __( "Active", Plugin::SLUG ),
			'paused'   => __( "Paused", Plugin::SLUG ),
			'archived' => __( "Archived", Plugin::SLUG )
		);
	}

	/**
	 * Get the table object for this model.
	 *
	 * @since 1.0
	 *
	 * @returns Table
	 */
	protected static function get_table() {
		return Manager::get( 'itelic-releases' );
	}
}

Releases.php

<?php
/**
 * DB table managing releases.
 *
 * @author Iron Bound Designs
 * @since  1.0
 */

namespace ITELIC\DB\Table;
use IronBound\DB\Table\Table;

/**
 * Class Releases
 * @package ITELIC\DB\Table
 */
class Releases implements Table {

	/**
	 * Retrieve the name of the database table.
	 *
	 * @since 1.0
	 *
	 * @param \wpdb $wpdb
	 *
	 * @return string
	 */
	public function get_table_name( \wpdb $wpdb ) {
		return $wpdb->prefix . 'itelic_releases';
	}

	/**
	 * Get the slug of this table.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public function get_slug() {
		return 'itelic-releases';
	}

	/**
	 * Columns in the table.
	 *
	 * key => sprintf field type
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public function get_columns() {
		return array(
			'ID'         => '%d',
			'product'    => '%d',
			'download'   => '%d',
			'version'    => '%s',
			'status'     => '%s',
			'type'       => '%s',
			'changelog'  => '%s',
			'start_date' => '%s'
		);
	}

	/**
	 * Default column values.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public function get_column_defaults() {
		return array(
			'ID'         => '',
			'product'    => '',
			'download'   => '',
			'version'    => '',
			'status'     => 'draft',
			'type'       => 'major',
			'changelog'  => '',
			'start_date' => ''
		);
	}

	/**
	 * Retrieve the name of the primary key.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public function get_primary_key() {
		return 'ID';
	}

	/**
	 * Get creation SQL.
	 *
	 * @since 1.0
	 *
	 * @param \wpdb $wpdb
	 *
	 * @return string
	 */
	public function get_creation_sql( \wpdb $wpdb ) {
		$tn = $this->get_table_name( $wpdb );

		return "CREATE TABLE {$tn} (
		ID bigint(20) unsigned NOT NULL auto_increment,
		product bigint(20) unsigned NOT NULL,
		download bigint(20) unsigned NOT NULL,
		version varchar(20) NOT NULL,
		status varchar(20) NOT NULL,
		type varchar(20) NOT NULL,
		changelog TEXT,
		start_date DATETIME,
		PRIMARY KEY  (ID),
		KEY product__version (product,version)
		) {$wpdb->get_charset_collate()};";
	}

	/**
	 * Retrieve the version number of the current table schema as written.
	 *
	 * The version should be incremented by 1 for each change.
	 *
	 * @since 1.0
	 *
	 * @return int
	 */
	public function get_version() {
		return 1;
	}
}

Simple_Query.php

<?php
/**
 * Make queries against a custom db table.
 *
 * @author Iron Bound Designs
 * @since  1.0
 */

namespace IronBound\DB\Query;

use IronBound\DB\Exception;
use IronBound\DB\Query\Tag\From;
use IronBound\DB\Query\Tag\Select;
use IronBound\DB\Query\Tag\Where;
use IronBound\DB\Table\Table;

/**
 * Class Query
 * @package IronBound\DB
 */
class Simple_Query {

	/**
	 * @var \wpdb
	 */
	protected $wpdb;

	/**
	 * @var Table
	 */
	protected $table;

	/**
	 * Constructor.
	 *
	 * @param \wpdb $wpdb
	 * @param Table  $table
	 */
	public function __construct( \wpdb $wpdb, Table $table ) {
		$this->wpdb  = $wpdb;
		$this->table = $table;
	}

	/**
	 * Retrieve a row by the primary key
	 *
	 * @since 1.0
	 *
	 * @param string       $row_key
	 * @param array|string $columns
	 *
	 * @return object
	 *
	 * @throws Exception
	 */
	public function get( $row_key, $columns = '*' ) {
		return $this->get_by( $this->table->get_primary_key(), $row_key, $columns );
	}

	/**
	 * Retrieve a row by a specific column / value
	 *
	 * @since 1.0
	 *
	 * @param string       $column Column name
	 * @param string       $value  Value for the column.
	 * @param string|array $columns
	 *
	 * @return object
	 *
	 * @throws Exception
	 */
	public function get_by( $column, $value, $columns = '*' ) {

		$builder = new Builder();

		$allowed_columns = $this->table->get_columns();

		if ( is_array( $columns ) ) {

			$select = new Select( null );

			foreach ( $columns as $col ) {

				if ( ! isset( $allowed_columns[ $col ] ) ) {
					throw new Exception( "Invalid column." );
				}

				$select->also( $col );
			}
		} elseif ( $columns == Select::ALL ) {
			$select = new Select( $columns );
		} else {
			if ( ! isset( $allowed_columns[ $columns ] ) ) {
				throw new Exception( "Invalid column" );
			}

			$select = new Select( $columns );
		}

		$builder->append( $select );
		$builder->append( new From( $this->table->get_table_name( $this->wpdb ) ) );
		$builder->append( new Where( $column, true, $this->escape_value( $column, $value ) ) );

		return $this->wpdb->get_row( $builder->build() );
	}

	/**
	 * Retrieve a specific column's value by the primary key
	 *
	 * @since 1.0
	 *
	 * @param string $column
	 * @param string $row_key
	 *
	 * @return string
	 *
	 * @throws Exception
	 */
	public function get_column( $column, $row_key ) {
		return $this->get_column_by( $column, $this->table->get_primary_key(), $row_key );
	}

	/**
	 * Retrieve a specific column's value by the the specified column / value
	 *
	 * @since 1.0
	 *
	 * @param string $column Var to retrieve
	 * @param string $where
	 * @param string $value
	 *
	 * @return string
	 *
	 * @throws Exception
	 */
	public function get_column_by( $column, $where, $value ) {

		$builder = new Builder();

		$allowed_columns = $this->table->get_columns();

		if ( ! isset( $allowed_columns[ $column ] ) ) {
			throw new Exception( "Invalid column." );
		}

		$builder->append( new Select( $column ) );
		$builder->append( new From( $this->table->get_table_name( $this->wpdb ) ) );
		$builder->append( new Where( $where, true, $this->escape_value( $where, $value ) ) );

		return $this->wpdb->get_var( $builder->build() );
	}

	/**
	 * Retrieve the number of rows matching a certain where clause
	 *
	 * @since 1.0
	 *
	 * @param array $wheres
	 *
	 * @return int
	 */
	public function count( $wheres = array() ) {

		$builder = new Builder();

		$select = new Select( null );
		$select->expression( 'COUNT', '*' );
		$builder->append( $select );

		$builder->append( new From( $this->table->get_table_name( $this->wpdb ) ) );

		if ( ! empty( $wheres ) ) {

			foreach ( $wheres as $column => $value ) {
				if ( ! isset( $where ) ) {
					$where = new Where( $column, true, $this->escape_value( $column, $value ) );
				} else {
					$where->qAnd( new Where( $column, true, $this->escape_value( $column, $value ) ) );
				}
			}

			$builder->append( $where );
		}

		return (int) $this->wpdb->get_var( $builder->build() );
	}

	/**
	 * Insert a new row
	 *
	 * @since 1.0
	 *
	 * @param array $data
	 *
	 * @return mixed Insert ID
	 *
	 * @throws Exception
	 */
	public function insert( $data ) {
		// Set default values
		$data = wp_parse_args( $data, $this->table->get_column_defaults() );

		// Initialise column format array
		$column_formats = $this->table->get_columns();

		// White list columns
		$data = array_intersect_key( $data, $column_formats );

		// Reorder $column_formats to match the order of columns given in $data
		$data_keys      = array_keys( $data );
		$column_formats = array_merge( array_flip( $data_keys ), $column_formats );

		$prev = $this->wpdb->show_errors( false );
		$this->wpdb->insert( $this->table->get_table_name( $this->wpdb ), $data, $column_formats );
		$this->wpdb->show_errors( $prev );

		if ( $this->wpdb->last_error ) {
			throw $this->generate_exception_from_db_error();
		}

		return $this->wpdb->insert_id;
	}

	/**
	 * Update a row
	 *
	 * @since 1.0
	 *
	 * @param string $row_key
	 * @param array  $data
	 * @param array  $where
	 *
	 * @return  bool
	 *
	 * @throws Exception
	 */
	public function update( $row_key, $data, $where = array() ) {

		if ( empty( $row_key ) ) {
			return false;
		}

		if ( empty( $where ) ) {
			$where = array( $this->table->get_primary_key() => $row_key );
		}

		// Initialise column format array
		$column_formats = $this->table->get_columns();

		// White list columns
		$data = array_intersect_key( $data, $column_formats );

		// Reorder $column_formats to match the order of columns given in $data
		$data_keys      = array_keys( $data );
		$column_formats = array_merge( array_flip( $data_keys ), $column_formats );

		$prev   = $this->wpdb->show_errors( false );
		$result = $this->wpdb->update( $this->table->get_table_name( $this->wpdb ), $data, $where, $column_formats );
		$this->wpdb->show_errors( $prev );

		if ( $this->wpdb->last_error ) {
			throw $this->generate_exception_from_db_error();
		}

		return (bool) $result;
	}

	/**
	 * Delete a row identified by the primary key
	 *
	 * @since 1.0
	 *
	 * @param string $row_key
	 *
	 * @return  bool
	 *
	 * @throws Exception
	 */
	public function delete( $row_key ) {

		if ( empty( $row_key ) ) {
			return false;
		}

		$row_key = $this->escape_value( $this->table->get_primary_key(), $row_key );

		$prev   = $this->wpdb->show_errors( false );
		$result = $this->wpdb->delete( $this->table->get_table_name( $this->wpdb ), array( $this->table->get_primary_key() => $row_key ) );
		$this->wpdb->show_errors( $prev );

		if ( $this->wpdb->last_error ) {
			throw $this->generate_exception_from_db_error();
		}

		return (bool) $result;
	}

	/**
	 * Delete many rows.
	 *
	 * @since 1.0
	 *
	 * @param $wheres array
	 *
	 * @return bool
	 *
	 * @throws Exception
	 */
	public function delete_many( $wheres ) {

		$prev   = $this->wpdb->show_errors( false );
		$result = $this->wpdb->delete( $this->table->get_table_name( $this->wpdb ), $wheres );
		$this->wpdb->show_errors( $prev );

		if ( $this->wpdb->last_error ) {
			throw $this->generate_exception_from_db_error();
		}

		return (bool) $result;
	}

	/**
	 * Escape a value using sprintf.
	 *
	 * @param string $column
	 * @param mixed  $value
	 *
	 * @return mixed
	 *
	 * @throws Exception
	 */
	public function escape_value( $column, $value ) {

		$columns = $this->table->get_columns();

		if ( ! isset( $columns[ $column ] ) ) {
			throw new Exception( "Invalid database column." );
		}

		$column_format = $columns[ $column ];

		if ( $value[0] == '%' ) {
			$value = '%' . $value;
		}

		if ( $value[ strlen( $value ) - 1 ] == '%' ) {
			$value = $value . '%';
		}

		return esc_sql( sprintf( $column_format, $value ) );
	}

	/**
	 * Generate an Exception object from a DB error.
	 *
	 * @return Exception
	 */
	protected function generate_exception_from_db_error() {

		if ( ! $this->wpdb->last_error ) {
			return null;
		}

		if ( $this->is_mysqli() ) {
			$error_number = mysqli_errno( $this->get_dbh() );
		} else {
			$error_number = mysql_errno( $this->get_dbh() );
		}

		return new Exception( $this->wpdb->last_error, $error_number );
	}

	/**
	 * Check if wpdb is using mysqli
	 *
	 * @return bool
	 */
	protected function is_mysqli() {
		return $this->wpdb->use_mysqli;
	}

	/**
	 * Get the mysql dbh
	 *
	 * @return mixed
	 */
	protected function get_dbh() {
		return $this->wpdb->dbh;
	}
}

Table.php

<?php
/**
 * Used as a base for all custom DB tables.
 *
 * @author      Iron Bound Designs
 * @since       1.0
 * @copyright   2015 (c) Iron Bound Designs.
 * @license     GPLv2
 */

namespace IronBound\DB\Table;

/**
 * Interface Base
 * @package IronBound\DB\Table
 */
interface Table {

	/**
	 * Retrieve the name of the database table.
	 *
	 * @since 1.0
	 *
	 * @param \wpdb $wpdb
	 *
	 * @return string
	 */
	public function get_table_name( \wpdb $wpdb );

	/**
	 * Get the slug of this table.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public function get_slug();

	/**
	 * Columns in the table.
	 *
	 * key => sprintf field type
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public function get_columns();

	/**
	 * Default column values.
	 *
	 * @since 1.0
	 *
	 * @return array
	 */
	public function get_column_defaults();

	/**
	 * Retrieve the name of the primary key.
	 *
	 * @since 1.0
	 *
	 * @return string
	 */
	public function get_primary_key();

	/**
	 * Get creation SQL.
	 *
	 * @since 1.0
	 *
	 * @param \wpdb $wpdb
	 *
	 * @return string
	 */
	public function get_creation_sql( \wpdb $wpdb );

	/**
	 * Retrieve the version number of the current table schema as written.
	 *
	 * The version should be incremented by 1 for each change.
	 *
	 * @since 1.0
	 *
	 * @return int
	 */
	public function get_version();
}