<?php
|
namespace JVBase\managers;
|
|
use Exception;
|
|
if (!defined('ABSPATH')) {
|
exit;
|
}
|
|
/**
|
* Custom Table Helper
|
*
|
* Provides consistent interface for CRUD operations on custom tables
|
* Used by routes that interact with custom tables defined in CheckCustomTables.php
|
*
|
* @example
|
* $table = new CustomTable('favourites');
|
* $result = $table->insert(['user_id' => 1, 'type' => 'tattoo', 'target_id' => 123]);
|
*/
|
class CustomTable
|
{
|
protected \wpdb $wpdb;
|
protected string $tableName;
|
protected string $fullTableName;
|
protected bool $useTransactions;
|
|
/** @var array<string, self> Instance cache for fluent interface */
|
protected static array $instances = [];
|
|
/**
|
* Fluent factory method
|
*
|
* @param string $tableName Table name without prefix/BASE
|
* @return self
|
*
|
* @example CustomTable::for('favourites')->insert($data);
|
*/
|
public static function for(string $tableName): self
|
{
|
if (!isset(self::$instances[$tableName])) {
|
self::$instances[$tableName] = new self($tableName);
|
}
|
|
return self::$instances[$tableName];
|
}
|
|
/**
|
* Clear instance cache (useful for testing)
|
*/
|
public static function clearCache(): void
|
{
|
self::$instances = [];
|
}
|
|
/**
|
* @param string $tableName Table name without prefix/BASE (e.g., 'favourites', 'notifications')
|
* @param bool $useTransactions Whether to auto-wrap operations in transactions
|
*/
|
public function __construct(string $tableName, bool $useTransactions = false)
|
{
|
global $wpdb;
|
$this->wpdb = $wpdb;
|
$this->tableName = $tableName;
|
$this->fullTableName = $wpdb->prefix . BASE . $tableName;
|
$this->useTransactions = $useTransactions;
|
}
|
|
// =========================================================================
|
// FLUENT QUERY BUILDER
|
// =========================================================================
|
|
/** @var array Query builder state */
|
protected array $builder = [];
|
|
/**
|
* Start a fluent query - set WHERE conditions
|
*
|
* @param array $conditions Associative array of column => value
|
* @return self
|
*
|
* @example CustomTable::for('favourites')->where(['user_id' => 1])->get();
|
*/
|
public function where(array $conditions): self
|
{
|
$this->builder['where'] = $conditions;
|
return $this;
|
}
|
|
/**
|
* Set ORDER BY
|
*
|
* @param string $column Column to order by
|
* @param string $direction ASC or DESC
|
* @return self
|
*/
|
public function orderBy(string $column, string $direction = 'DESC'): self
|
{
|
$this->builder['orderby'] = $column;
|
$this->builder['order'] = strtoupper($direction);
|
return $this;
|
}
|
|
/**
|
* Set LIMIT
|
*
|
* @param int $limit Number of records
|
* @param int $offset Optional offset
|
* @return self
|
*/
|
public function limit(int $limit, int $offset = 0): self
|
{
|
$this->builder['limit'] = $limit;
|
$this->builder['offset'] = $offset;
|
return $this;
|
}
|
|
/**
|
* Execute the built query and get results
|
*
|
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
|
* @return array
|
*/
|
public function getResults(string $output = OBJECT): array
|
{
|
$results = $this->getMany($this->builder, $output);
|
$this->resetBuilder();
|
return $results;
|
}
|
|
/**
|
* Execute the built query and get first result
|
*
|
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
|
* @return object|array|null
|
*/
|
public function first(string $output = OBJECT): object|array|null
|
{
|
$this->builder['limit'] = 1;
|
$results = $this->getMany($this->builder, $output);
|
$this->resetBuilder();
|
return $results[0] ?? null;
|
}
|
|
/**
|
* Count records with current builder state
|
*
|
* @return int
|
*/
|
public function countResults(): int
|
{
|
$where = $this->builder['where'] ?? [];
|
$count = $this->count($where);
|
$this->resetBuilder();
|
return $count;
|
}
|
|
/**
|
* Check if any records exist with current builder state
|
*
|
* @return bool
|
*/
|
public function existsInQuery(): bool
|
{
|
return $this->countResults() > 0;
|
}
|
|
/**
|
* Delete records matching current builder state
|
*
|
* @return int|false Number of deleted rows
|
*/
|
public function deleteResults(): int|false
|
{
|
$where = $this->builder['where'] ?? [];
|
$result = $this->delete($where);
|
$this->resetBuilder();
|
return $result;
|
}
|
|
/**
|
* Update records matching current builder state
|
*
|
* @param array $data Data to update
|
* @return int|false Number of updated rows
|
*/
|
public function updateResults(array $data): int|false
|
{
|
$where = $this->builder['where'] ?? [];
|
$result = $this->update($data, $where);
|
$this->resetBuilder();
|
return $result;
|
}
|
|
/**
|
* Reset query builder state
|
*/
|
protected function resetBuilder(): void
|
{
|
$this->builder = [];
|
}
|
|
// =========================================================================
|
// CREATE OPERATIONS
|
// =========================================================================
|
|
/**
|
* Insert a single record
|
*
|
* @param array $data Associative array of column => value
|
* @param array|null $format Optional array of format strings (%d, %s, %f)
|
* @return int|false Insert ID on success, false on failure
|
*
|
* @example
|
* $id = $table->insert([
|
* 'user_id' => 1,
|
* 'type' => 'tattoo',
|
* 'target_id' => 123,
|
* 'date_added' => current_time('mysql')
|
* ]);
|
*/
|
public function insert(array $data, ?array $format = null): int|false
|
{
|
// Auto-add created_at if column exists and not provided
|
if (!isset($data['created_at']) && $this->hasColumn('created_at')) {
|
$data['created_at'] = current_time('mysql');
|
}
|
|
$result = $this->wpdb->insert(
|
$this->fullTableName,
|
$data,
|
$format
|
);
|
|
if ($result === false) {
|
$this->logError('insert', $data);
|
return false;
|
}
|
|
return $this->wpdb->insert_id;
|
}
|
|
/**
|
* Alias for insert() - more semantic for fluent interface
|
*
|
* @param array $data Data to insert
|
* @return int|false Insert ID on success
|
*
|
* @example CustomTable::for('favourites')->create(['user_id' => 1]);
|
*/
|
public function create(array $data): int|false
|
{
|
return $this->insert($data);
|
}
|
|
/**
|
* Find or create a record
|
*
|
* @param array $searchData Data to search for
|
* @param array $createData Optional additional data for creation
|
* @return array ['id' => int, 'created' => bool, 'record' => object]
|
*
|
* @example
|
* $result = CustomTable::for('favourites')->findOrCreate(
|
* ['user_id' => 1, 'target_id' => 123],
|
* ['type' => 'tattoo']
|
* );
|
* // Returns: ['id' => 456, 'created' => false, 'record' => object]
|
*/
|
public function findOrCreate(array $searchData, array $createData = []): array
|
{
|
$record = $this->get($searchData);
|
|
if ($record) {
|
return [
|
'id' => $record->id ?? 0,
|
'created' => false,
|
'record' => $record
|
];
|
}
|
|
$data = array_merge($searchData, $createData);
|
$id = $this->insert($data);
|
|
return [
|
'id' => $id,
|
'created' => true,
|
'record' => $this->get(['id' => $id])
|
];
|
}
|
|
/**
|
* Bulk insert multiple records efficiently
|
*
|
* @param array $rows Array of associative arrays
|
* @param array $columns Column names (must be same for all rows)
|
* @return int|false Number of rows inserted, false on failure
|
*
|
* @example
|
* $count = $table->bulkInsert([
|
* ['user_id' => 1, 'type' => 'tattoo', 'target_id' => 123],
|
* ['user_id' => 1, 'type' => 'tattoo', 'target_id' => 456],
|
* ], ['user_id', 'type', 'target_id']);
|
*/
|
public function bulkInsert(array $rows, array $columns): int|false
|
{
|
if (empty($rows)) {
|
return 0;
|
}
|
|
// Auto-add created_at if column exists
|
if ($this->hasColumn('created_at') && !in_array('created_at', $columns)) {
|
$columns[] = 'created_at';
|
$now = current_time('mysql');
|
foreach ($rows as &$row) {
|
$row['created_at'] = $now;
|
}
|
}
|
|
$placeholders = [];
|
$values = [];
|
|
foreach ($rows as $row) {
|
$row_placeholders = [];
|
foreach ($columns as $column) {
|
$value = $row[$column] ?? null;
|
$values[] = $value;
|
$row_placeholders[] = $this->getPlaceholder($value);
|
}
|
$placeholders[] = "(" . implode(',', $row_placeholders) . ")";
|
}
|
|
$columns_escaped = array_map(function($col) {
|
return "`{$col}`";
|
}, $columns);
|
|
$query = "INSERT INTO {$this->fullTableName}
|
(" . implode(',', $columns_escaped) . ")
|
VALUES " . implode(',', $placeholders);
|
|
$result = $this->wpdb->query($this->wpdb->prepare($query, $values));
|
|
if ($result === false) {
|
$this->logError('bulkInsert', ['rows' => count($rows)]);
|
return false;
|
}
|
|
return $result;
|
}
|
|
// =========================================================================
|
// READ OPERATIONS
|
// =========================================================================
|
|
/**
|
* Get a single record
|
*
|
* @param array $where Associative array of column => value conditions
|
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
|
* @return object|array|null
|
*
|
* @example
|
* $fav = $table->get(['user_id' => 1, 'target_id' => 123]);
|
*/
|
public function get(array $where, string $output = OBJECT): object|array|null
|
{
|
$query = "SELECT * FROM {$this->fullTableName} WHERE " . $this->buildWhereClause($where);
|
$values = array_values($where);
|
|
return $this->wpdb->get_row($this->wpdb->prepare($query, $values), $output);
|
}
|
|
/**
|
* Get multiple records
|
*
|
* @param array $args Query arguments: where, orderby, order, limit, offset
|
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
|
* @return array
|
*
|
* @example
|
* $favs = $table->getMany([
|
* 'where' => ['user_id' => 1],
|
* 'orderby' => 'date_added',
|
* 'order' => 'DESC',
|
* 'limit' => 20
|
* ]);
|
*/
|
public function getMany(array $args = [], string $output = OBJECT): array
|
{
|
$query = "SELECT * FROM {$this->fullTableName}";
|
$values = [];
|
|
// WHERE clause
|
if (!empty($args['where'])) {
|
$query .= " WHERE " . $this->buildWhereClause($args['where']);
|
$values = array_merge($values, array_values($args['where']));
|
}
|
|
// ORDER BY
|
if (!empty($args['orderby'])) {
|
$orderby = sanitize_sql_orderby($args['orderby']);
|
$order = (!empty($args['order']) && strtoupper($args['order']) === 'ASC') ? 'ASC' : 'DESC';
|
$query .= " ORDER BY {$orderby} {$order}";
|
}
|
|
// LIMIT
|
if (!empty($args['limit'])) {
|
$limit = absint($args['limit']);
|
$offset = !empty($args['offset']) ? absint($args['offset']) : 0;
|
$query .= " LIMIT {$offset}, {$limit}";
|
}
|
|
if (empty($values)) {
|
return $this->wpdb->get_results($query, $output);
|
}
|
|
return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
|
}
|
|
/**
|
* Count records
|
*
|
* @param array $where Associative array of column => value conditions
|
* @return int
|
*/
|
public function count(array $where = []): int
|
{
|
$query = "SELECT COUNT(*) FROM {$this->fullTableName}";
|
$values = [];
|
|
if (!empty($where)) {
|
$query .= " WHERE " . $this->buildWhereClause($where);
|
$values = array_values($where);
|
}
|
|
if (empty($values)) {
|
return (int) $this->wpdb->get_var($query);
|
}
|
|
return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $values));
|
}
|
|
/**
|
* Check if record exists
|
*
|
* @param array $where Associative array of column => value conditions
|
* @return bool
|
*/
|
public function exists(array $where): bool
|
{
|
return $this->count($where) > 0;
|
}
|
|
// =========================================================================
|
// UPDATE OPERATIONS
|
// =========================================================================
|
|
/**
|
* Update records
|
*
|
* @param array $data Data to update (column => value)
|
* @param array $where Where conditions (column => value)
|
* @param array|null $format Optional format for data
|
* @param array|null $where_format Optional format for where
|
* @return int|false Number of rows updated, false on failure
|
*
|
* @example
|
* $updated = $table->update(
|
* ['status' => 'read'],
|
* ['id' => 123, 'user_id' => 1]
|
* );
|
*/
|
public function update(array $data, array $where, ?array $format = null, ?array $where_format = null): int|false
|
{
|
// Auto-update updated_at if column exists and not provided
|
if (!isset($data['updated_at']) && $this->hasColumn('updated_at')) {
|
$data['updated_at'] = current_time('mysql');
|
}
|
|
$result = $this->wpdb->update(
|
$this->fullTableName,
|
$data,
|
$where,
|
$format,
|
$where_format
|
);
|
|
if ($result === false) {
|
$this->logError('update', ['data' => $data, 'where' => $where]);
|
}
|
|
return $result;
|
}
|
|
// =========================================================================
|
// DELETE OPERATIONS
|
// =========================================================================
|
|
/**
|
* Delete records
|
*
|
* @param array $where Where conditions (column => value)
|
* @param array|null $where_format Optional format for where
|
* @return int|false Number of rows deleted, false on failure
|
*
|
* @example
|
* $deleted = $table->delete(['id' => 123]);
|
*/
|
public function delete(array $where, ?array $where_format = null): int|false
|
{
|
$result = $this->wpdb->delete(
|
$this->fullTableName,
|
$where,
|
$where_format
|
);
|
|
if ($result === false) {
|
$this->logError('delete', ['where' => $where]);
|
}
|
|
return $result;
|
}
|
|
// =========================================================================
|
// RAW QUERY OPERATIONS
|
// =========================================================================
|
|
/**
|
* Execute a raw query with automatic table name substitution
|
*
|
* @param string $query SQL query (use {table} as placeholder)
|
* @param array $values Values for prepare()
|
* @return mixed Query result
|
*
|
* @example
|
* $results = $table->query(
|
* "SELECT * FROM {table} WHERE user_id = %d AND status IN (%s, %s)",
|
* [1, 'pending', 'active']
|
* );
|
*/
|
public function query(string $query, array $values = []): mixed
|
{
|
$query = str_replace('{table}', $this->fullTableName, $query);
|
|
if (empty($values)) {
|
return $this->wpdb->query($query);
|
}
|
|
return $this->wpdb->query($this->wpdb->prepare($query, $values));
|
}
|
|
/**
|
* Get results from raw query
|
*
|
* @param string $query SQL query (use {table} as placeholder)
|
* @param array $values Values for prepare()
|
* @param string $output OBJECT, ARRAY_A, or ARRAY_N
|
* @return array
|
*/
|
public function queryResults(string $query, array $values = [], string $output = OBJECT): array
|
{
|
$query = str_replace('{table}', $this->fullTableName, $query);
|
|
if (empty($values)) {
|
return $this->wpdb->get_results($query, $output);
|
}
|
|
return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
|
}
|
|
/**
|
* Get single value from query
|
*
|
* @param string $query SQL query (use {table} as placeholder)
|
* @param array $values Values for prepare()
|
* @return mixed
|
*/
|
public function queryVar(string $query, array $values = []): mixed
|
{
|
$query = str_replace('{table}', $this->fullTableName, $query);
|
|
if (empty($values)) {
|
return $this->wpdb->get_var($query);
|
}
|
|
return $this->wpdb->get_var($this->wpdb->prepare($query, $values));
|
}
|
|
// =========================================================================
|
// TRANSACTION HELPERS
|
// =========================================================================
|
|
/**
|
* Start a transaction
|
*/
|
public function startTransaction(): void
|
{
|
$this->wpdb->query('START TRANSACTION');
|
}
|
|
/**
|
* Commit a transaction
|
*/
|
public function commit(): void
|
{
|
$this->wpdb->query('COMMIT');
|
}
|
|
/**
|
* Rollback a transaction
|
*/
|
public function rollback(): void
|
{
|
$this->wpdb->query('ROLLBACK');
|
}
|
|
/**
|
* Execute callback within a transaction
|
*
|
* @param callable $callback Function to execute
|
* @return mixed Returns callback result
|
* @throws Exception Rolls back on exception
|
*
|
* @example
|
* $result = $table->transaction(function() use ($table) {
|
* $table->insert(['user_id' => 1, ...]);
|
* $table->update(['status' => 'active'], ['id' => 123]);
|
* return true;
|
* });
|
*/
|
public function transaction(callable $callback): mixed
|
{
|
$this->startTransaction();
|
|
try {
|
$result = $callback($this);
|
$this->commit();
|
return $result;
|
} catch (Exception $e) {
|
$this->rollback();
|
$this->logError('transaction', ['error' => $e->getMessage()]);
|
throw $e;
|
}
|
}
|
|
// =========================================================================
|
// UTILITY METHODS
|
// =========================================================================
|
|
/**
|
* Get the full table name (with prefix)
|
*/
|
public function getFullTableName(): string
|
{
|
return $this->fullTableName;
|
}
|
|
/**
|
* Get last insert ID
|
*/
|
public function getInsertId(): int
|
{
|
return $this->wpdb->insert_id;
|
}
|
|
/**
|
* Get last error
|
*/
|
public function getLastError(): string
|
{
|
return $this->wpdb->last_error;
|
}
|
|
/**
|
* Get number of affected rows from last query
|
*/
|
public function getAffectedRows(): int
|
{
|
return $this->wpdb->rows_affected;
|
}
|
|
// =========================================================================
|
// PRIVATE HELPERS
|
// =========================================================================
|
|
/**
|
* Build WHERE clause from associative array
|
*/
|
private function buildWhereClause(array $where): string
|
{
|
$conditions = [];
|
foreach ($where as $column => $value) {
|
$column_safe = esc_sql($column);
|
if ($value === null) {
|
$conditions[] = "`{$column_safe}` IS NULL";
|
} else {
|
$conditions[] = "`{$column_safe}` = " . $this->getPlaceholder($value);
|
}
|
}
|
return implode(' AND ', $conditions);
|
}
|
|
/**
|
* Get appropriate placeholder for value type
|
*/
|
private function getPlaceholder(mixed $value): string
|
{
|
if (is_int($value)) {
|
return '%d';
|
} elseif (is_float($value)) {
|
return '%f';
|
} else {
|
return '%s';
|
}
|
}
|
|
/**
|
* Check if table has a specific column
|
*/
|
private function hasColumn(string $column): bool
|
{
|
static $cache = [];
|
|
if (!isset($cache[$this->tableName])) {
|
$columns = $this->wpdb->get_col("DESCRIBE {$this->fullTableName}");
|
$cache[$this->tableName] = array_flip($columns);
|
}
|
|
return isset($cache[$this->tableName][$column]);
|
}
|
|
/**
|
* Log database errors
|
*/
|
private function logError(string $operation, array $context = []): void
|
{
|
if (function_exists('JVB')) {
|
JVB()->error()->log(
|
$this->tableName,
|
"CustomTable {$operation} failed: " . $this->wpdb->last_error,
|
$context,
|
'error'
|
);
|
} else {
|
error_log("[CustomTable:{$this->tableName}] {$operation} failed: " . $this->wpdb->last_error);
|
}
|
}
|
}
|
