From a9b3b28d001941921aa70d37fdc87c758a163a44 Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Fri, 05 Jun 2026 16:47:03 +0000
Subject: [PATCH] =Some hefty changes to FeedBlock. Transitioning to loading first page in php to save on extra requests. Got a bit to do yet, but I have to work on Northeh for a bit here.
---
inc/managers/CustomTable.php | 461 ++++++++++++++++++++++++++++++++++++++++++++++++++-------
1 files changed, 405 insertions(+), 56 deletions(-)
diff --git a/inc/managers/CustomTable.php b/inc/managers/CustomTable.php
index 893e5ad..572d8a2 100644
--- a/inc/managers/CustomTable.php
+++ b/inc/managers/CustomTable.php
@@ -21,11 +21,25 @@
{
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
@@ -35,15 +49,206 @@
*
* @example CustomTable::for('favourites')->insert($data);
*/
- public static function for(string $tableName): self
+ public static function for(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false): self
{
if (!isset(self::$instances[$tableName])) {
- self::$instances[$tableName] = new self($tableName);
+ self::$instances[$tableName] = new self($tableName, $user, $auth, $useTransactions);
}
+
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)
*/
@@ -56,13 +261,26 @@
* @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)
+ public function __construct(string $tableName, bool $user = false, bool $auth = false, bool $useTransactions = false)
{
global $wpdb;
$this->wpdb = $wpdb;
$this->tableName = $tableName;
- $this->fullTableName = $wpdb->prefix . BASE . $tableName;
+ $this->fullTableName = $wpdb->prefix . apply_filters('jvb_base', BASE) . $tableName;
$this->useTransactions = $useTransactions;
+
+ $this->cache = Cache::for($tableName);
+ if ($user) {
+ $this->cache->user();
+ }
+ if ($auth) {
+ $this->cache->auth();
+ }
+
+ $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}";
+
}
// =========================================================================
@@ -236,6 +454,8 @@
return false;
}
+ $this->cache->flush();
+
return $this->wpdb->insert_id;
}
@@ -266,26 +486,19 @@
* );
* // Returns: ['id' => 456, 'created' => false, 'record' => object]
*/
- public function findOrCreate(array $searchData, array $createData = []): array
+ public function findOrCreate(array $searchData, array $createData = []):int|false
{
$record = $this->get($searchData);
if ($record) {
- return [
- 'id' => $record->id ?? 0,
- 'created' => false,
- 'record' => $record
- ];
+ if (!empty($createData)) {
+ return $this->update($createData, $searchData);
+ }
+ return $record->id;
}
$data = array_merge($searchData, $createData);
- $id = $this->insert($data);
-
- return [
- 'id' => $id,
- 'created' => true,
- 'record' => $this->get(['id' => $id])
- ];
+ return $this->insert($data);
}
/**
@@ -363,10 +576,17 @@
*/
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->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);
+ return $this->wpdb->get_row($this->wpdb->prepare($query, $values), $output);
+ }
+ );
}
/**
@@ -386,34 +606,76 @@
*/
public function getMany(array $args = [], string $output = OBJECT): array
{
- $query = "SELECT * FROM {$this->fullTableName}";
- $values = [];
+ 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'])) {
- $query .= " WHERE " . $this->buildWhereClause($args['where']);
- $values = array_merge($values, array_values($args['where']));
- }
+ // WHERE clause
+ if (!empty($args['where'])) {
+ $clause = $this->buildWhereClause($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}";
- }
+ $query .= " WHERE {$clause['sql']}";
+ $values = array_merge($values, $clause['values']);
+ }
- // LIMIT
- if (!empty($args['limit'])) {
- $limit = absint($args['limit']);
- $offset = !empty($args['offset']) ? absint($args['offset']) : 0;
- $query .= " LIMIT {$offset}, {$limit}";
- }
+ // 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}";
+ }
- if (empty($values)) {
- return $this->wpdb->get_results($query, $output);
- }
+ // LIMIT
+ if (!empty($args['limit'])) {
+ $limit = absint($args['limit']);
+ $offset = !empty($args['offset']) ? absint($args['offset']) : 0;
+ $query .= " LIMIT {$offset}, {$limit}";
+ }
- return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
+ 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);
+ }
+ );
+
}
/**
@@ -424,19 +686,26 @@
*/
public function count(array $where = []): int
{
- $query = "SELECT COUNT(*) FROM {$this->fullTableName}";
- $values = [];
+ 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)) {
- $query .= " WHERE " . $this->buildWhereClause($where);
- $values = array_values($where);
- }
+ if (!empty($where)) {
+ $clause = $this->buildWhereClause($where);
+ $query .= " WHERE {$clause['sql']}";
+ $values = $clause['values'];
+ }
- if (empty($values)) {
- return (int) $this->wpdb->get_var($query);
- }
+ if (empty($values)) {
+ return (int) $this->wpdb->get_var($query);
+ }
- return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $values));
+ return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $values));
+ }
+ );
+
}
/**
@@ -487,6 +756,7 @@
if ($result === false) {
$this->logError('update', ['data' => $data, 'where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -516,6 +786,7 @@
if ($result === false) {
$this->logError('delete', ['where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -638,7 +909,7 @@
} catch (Exception $e) {
$this->rollback();
$this->logError('transaction', ['error' => $e->getMessage()]);
- throw $e;
+ return false;
}
}
@@ -678,25 +949,103 @@
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): string
+ 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 implode(' AND ', $conditions);
+
+ return ['sql' => implode(' AND ', $conditions), 'values' => $values];
}
/**
--
Gitblit v1.10.0