<?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 $definition;
|
protected string $fullTableName;
|
protected bool $useTransactions;
|
protected array $columns;
|
protected array $keys = [];
|
protected array $constraints =[];
|
|
/** @var array<string, self> Instance cache for fluent interface */
|
protected static array $instances = [];
|
protected static string $charsetCollate;
|
|
protected static string $userTable;
|
protected static string $userIDType;
|
protected static string $termTable;
|
protected static string $termIDType;
|
protected static string $postIDType;
|
protected static string $postTable;
|
|
protected Cache $cache;
|
|
/**
|
* 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];
|
}
|
public static function destroyInstance(string $tableName):void
|
{
|
if (isset(self::$instances[$tableName])) {
|
unset(self::$instances[$tableName]);
|
}
|
}
|
public static function getInstance(string $tableName):self|false
|
{
|
if (!isset(self::$instances[$tableName])) {
|
return false;
|
}
|
return self::$instances[$tableName];
|
}
|
|
public function ensureDefined() {
|
$this->ensureUserTable();
|
$this->ensureUserIDType();
|
$this->ensureTermIDType();
|
$this->ensurePostIDType();
|
}
|
protected function ensureUserTable():void
|
{
|
if (!isset(static::$userTable)) {
|
static::$userTable = is_multisite() ? $this->getMultisiteUsersTable() : $this->wpdb->users;
|
}
|
}
|
protected function getMultisiteUsersTable():string
|
{
|
$siteUsersTable = $this->wpdb->prefix . 'users';
|
$siteExists = $this->wpdb->get_var(
|
$this->wpdb->prepare("SHOW TABLES LIKE %s", $siteUsersTable)
|
);
|
if ($siteExists) {
|
return $siteUsersTable;
|
}
|
//fallback to main one
|
return $this->wpdb->users;
|
}
|
protected function ensureUserIDType():void
|
{
|
if (!isset(static::$userIDType)) {
|
$this->ensureUserTable();
|
static::$userIDType = $this->getColumnType(static::$userTable, 'ID');
|
}
|
}
|
|
protected function ensureTermIDType():void
|
{
|
if (!isset(static::$termIDType)) {
|
static::$termIDType = $this->getColumnType($this->wpdb->terms, 'term_id');
|
}
|
}
|
|
protected function ensureTermTable():void
|
{
|
if (!isset(static::$termTable)) {
|
static::$termTable = $this->wpdb->terms;
|
}
|
}
|
|
protected function ensurePostIDType():void
|
{
|
if (!isset(static::$postIDType)) {
|
static::$postIDType = $this->getColumnType($this->wpdb->posts, 'ID');
|
}
|
}
|
protected function ensurePostTable():void
|
{
|
if (!isset(static::$postTable)) {
|
static::$postTable = $this->wpdb->posts;
|
}
|
}
|
|
/**
|
*
|
* @param array $columns An array of $columnName => $columnDefinition
|
* @return $this
|
*/
|
public function setColumns(array $columns):self
|
{
|
$this->columns = $columns;
|
return $this;
|
}
|
|
/**
|
* @param array $keys An array of {string} $keys. If a $key is an array, you can define a custom $key => $value, example: 'UNIQUE' => $value, or 'PRIMARY' => $value
|
* @return $this
|
*/
|
public function setKeys(array $keys):self
|
{
|
$this->keys = $keys;
|
return $this;
|
}
|
|
/**
|
* @param array $constraints an array of arrays, each value a $constraint => $references
|
* @return $this
|
*/
|
public function setConstraints(array $constraints):self
|
{
|
$this->constraints = $constraints;
|
return $this;
|
}
|
|
public function defineTable(): self
|
{
|
if (empty($this->columns)) {
|
error_log('[CustomTable] No columns defined for ' . $this->tableName);
|
return $this;
|
}
|
|
$parts = [];
|
|
// Columns
|
foreach ($this->columns as $name => $type) {
|
$parts[] = "`{$name}` {$type}";
|
}
|
|
// Keys
|
if (empty($this->keys)) {
|
$parts[] = 'PRIMARY KEY (`' . array_key_first($this->columns) . '`)';
|
} else {
|
foreach ($this->keys as $key) {
|
if (is_array($key)) {
|
$value = $key['value'];
|
// Ensure value is wrapped in parentheses
|
if (!str_contains(trim($value), '(`')) {
|
$value = '(`' . $value . '`)';
|
}
|
$parts[] = $key['key'] . ' KEY ' . $value;
|
} else {
|
$parts[] = 'KEY ' . $key;
|
}
|
}
|
}
|
|
// Constraints
|
foreach ($this->constraints as $constraint) {
|
$parts[] = $constraint;
|
}
|
|
$this->definition = "(\n " . implode(",\n ", $parts) . "\n)";
|
$this->createTable();
|
return $this;
|
}
|
|
public static function ensureTables():void
|
{
|
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
|
|
foreach (self::$instances as $instance) {
|
if (!$instance->wpdb->get_var("SHOW TABLES LIKE '{$instance->fullTableName}'")) {
|
$instance->createTable();
|
}
|
}
|
}
|
|
protected function createTable():void
|
{
|
if (!$this->definition && empty($this->definition)) {
|
error_log('[CustomTable]No definition set for '.$this->tableName);
|
return;
|
}
|
if ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
|
return;
|
}
|
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
|
error_log('Creating Database Table: '. $this->tableName);
|
$charset = self::$charsetCollate;
|
$schema = "CREATE TABLE IF NOT EXISTS {$this->fullTableName} {$this->definition} {$charset}";
|
$this->wpdb->flush();
|
|
$hasForeignKey = stripos($schema, 'FOREIGN KEY') !== false;
|
if ($hasForeignKey) {
|
$result = $this->wpdb->query($schema);
|
$success = ($result !== false && !$this->wpdb->last_error);
|
} else {
|
dbDelta($schema.';');
|
$success = !$this->wpdb->last_error;
|
}
|
|
if (!$success) {
|
$error_msg = "SQL Error creating table {$this->tableName}: " . $this->wpdb->last_error;
|
error_log($error_msg);
|
error_log("Failed SQL Query: " . $schema);
|
} elseif ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->fullTableName}'")) {
|
error_log("Successfully created table: {$this->fullTableName}");
|
}
|
}
|
|
/**
|
* 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 . apply_filters('jvb_base', BASE) . $tableName;
|
$this->useTransactions = $useTransactions;
|
|
$this->cache = Cache::for($tableName);
|
|
$usersStatus = $this->wpdb->get_row("SHOW TABLE STATUS LIKE '{$this->wpdb->users}'");
|
$parentCollation = $usersStatus->Collation ?? 'utf8mb4_general_ci';
|
self::$charsetCollate = "DEFAULT CHARACTER SET utf8mb4 COLLATE {$parentCollation}";
|
|
}
|
|
// =========================================================================
|
// 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;
|
}
|
|
$this->cache->flush();
|
|
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 = []):int|false
|
{
|
$record = $this->get($searchData);
|
|
if ($record) {
|
if (!empty($createData)) {
|
return $this->update($createData, $searchData);
|
}
|
return $record->id;
|
}
|
|
$data = array_merge($searchData, $createData);
|
return $this->insert($data);
|
}
|
|
/**
|
* 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
|
{
|
return $this->cache->remember(
|
$this->cache->generateKey(array_merge(['output' => $output], $where)),
|
function() use ($where, $output) {
|
$query = "SELECT * FROM {$this->fullTableName}";
|
$where = $this->buildWhereClause($where);
|
$query .= " WHERE {$where['sql']}";
|
$values = $where['values'];
|
|
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
|
{
|
return $this->cache->remember(
|
$this->cache->generateKey(array_merge($args, ['output' => $output])),
|
function () use ($args, $output) {
|
$query = "SELECT * FROM {$this->fullTableName}";
|
$values = [];
|
|
// WHERE clause
|
if (!empty($args['where'])) {
|
$clause = $this->buildWhereClause($args['where']);
|
|
$query .= " WHERE {$clause['sql']}";
|
$values = array_merge($values, $clause['values']);
|
}
|
|
// 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);
|
}
|
);
|
|
}
|
|
/**
|
* Get a specific column value from all matches
|
* @param string $column
|
* @param array $where
|
* @return array
|
*/
|
public function pluck(string $column, array $where = [], ?string $orderby = null, ?string $order = null, ?int $limit = null): array
|
{
|
$key = array_merge($where, ['column' => $column, 'orderby' => $orderby, 'order' => $order, 'limit' => $limit]);
|
|
return $this->cache->remember(
|
$this->cache->generateKey($key),
|
function() use ($column, $where, $orderby, $order, $limit) {
|
if (!empty($this->columns) && !array_key_exists($column, $this->columns)) {
|
$this->logError('pluck', ['reason' => "Column '{$column}' not in definition"]);
|
return [];
|
}
|
$args = ['where' => $where];
|
if ($orderby) {
|
$args['orderby'] = $orderby;
|
}
|
if ($order) {
|
$args['order'] = $order;
|
}
|
if ($limit) {
|
$args['limit'] = $limit;
|
}
|
|
return array_column($this->getMany($args), $column);
|
}
|
);
|
|
}
|
|
/**
|
* Count records
|
*
|
* @param array $where Associative array of column => value conditions
|
* @return int
|
*/
|
public function count(array $where = []): int
|
{
|
return $this->cache->remember(
|
$this->cache->generateKey(array_merge(['source' => 'count'], $where)),
|
function() use ($where) {
|
$query = "SELECT COUNT(*) FROM {$this->fullTableName}";
|
$values = [];
|
|
if (!empty($where)) {
|
$clause = $this->buildWhereClause($where);
|
$query .= " WHERE {$clause['sql']}";
|
$values = $clause['values'];
|
}
|
|
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]);
|
}
|
$this->cache->flush();
|
|
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]);
|
}
|
$this->cache->flush();
|
|
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()]);
|
return false;
|
}
|
}
|
|
// =========================================================================
|
// 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;
|
}
|
|
public function getUserIDType():string
|
{
|
$this->ensureUserIdType();
|
return static::$userIDType;
|
}
|
public function getUserTable():string
|
{
|
$this->ensureUserTable();
|
return static::$userTable;
|
}
|
public function getTermIDType():string
|
{
|
$this->ensureTermIDType();
|
return static::$termIDType;
|
}
|
public function getTermTable():string
|
{
|
$this->ensureTermTable();
|
return static::$termTable;
|
}
|
public function getPostIDType():string
|
{
|
$this->ensurePostIDType();
|
return static::$postIDType;
|
}
|
public function getPostTable():string
|
{
|
$this->ensurePostTable();
|
return static::$postTable;
|
}
|
|
// =========================================================================
|
// PRIVATE HELPERS
|
// =========================================================================
|
private function getColumnType(string $table, string $column):string|false {
|
$tableExists = $this->wpdb->get_var(
|
$this->wpdb->prepare("SHOW TABLES LIKE %s", $table)
|
);
|
if (!$tableExists) {
|
error_log("[CustomTable] Table {$table} does not exist for getColumnType");
|
return 'bigint(20) unsigned'; // safe fallback
|
}
|
|
$result = $this->wpdb->get_row(
|
$this->wpdb->prepare(
|
"SELECT COLUMN_TYPE
|
FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = %s
|
AND COLUMN_NAME = %s",
|
$table,
|
$column
|
)
|
);
|
if ($result && isset($result->COLUMN_TYPE)) {
|
return $result->COLUMN_TYPE;
|
}
|
error_log("[CustomTable] Could not determine column type for {$table}.{$column}");
|
return 'bigint(20) unsigned';
|
}
|
|
private function tableExists():bool
|
{
|
return !is_null($this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $this->fullTableName)));
|
}
|
/**
|
* Build WHERE clause from associative array
|
*/
|
private function buildWhereClause(array $where):array
|
{
|
$conditions = [];
|
$values = [];
|
foreach ($where as $column => $value) {
|
$column_safe = esc_sql($column);
|
|
if ($value === null) {
|
$conditions[] = "`{$column_safe}` IS NULL";
|
} elseif (is_array($value) && count($value) === 2 && is_string($value[0])) {
|
[$operator, $operand] = $value;
|
$operator = strtoupper(trim($operator));
|
|
if (in_array($operator, ['IN', 'NOT IN']) && is_array($operand)) {
|
$placeholders = implode(',', array_map([$this, 'getPlaceholder'], $operand));
|
$conditions[] = "`{$column_safe}` {$operator} ({$placeholders})";
|
$values = array_merge($values, $operand);
|
} else {
|
// <, >, <=, >=, !=, LIKE, etc.
|
$conditions[] = "`{$column_safe}` {$operator} " . $this->getPlaceholder($operand);
|
$values[] = $operand;
|
}
|
} else {
|
$conditions[] = "`{$column_safe}` = " . $this->getPlaceholder($value);
|
$values[] = $value;
|
}
|
}
|
|
return ['sql' => implode(' AND ', $conditions), 'values' => $values];
|
}
|
|
/**
|
* 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);
|
}
|
}
|
}
|
