From 3d25ad7de4fdbb3eba2cde2a62422e6be5d79a0a Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Tue, 25 Aug 2026 22:57:22 +0000
Subject: [PATCH] =It's been a minute. I went through the Routes and associated Managers and ensured they played together well. Reworked the Cache.php, and I think I caught all the references. Moving away from JVBase() root class, to individuall classes that are Instanced. Double checked for the ABSPATH check in every file, and added index.php to every directory that didn't already have it. Also still working on the MenuBlock to work with WooCommerce for now for Northeh
---
inc/managers/CustomTable.php | 574 +++++++++++++++++++++++++++++++++++++++++++++++++++------
1 files changed, 512 insertions(+), 62 deletions(-)
diff --git a/inc/managers/CustomTable.php b/inc/managers/CustomTable.php
index 893e5ad..c8a9171 100644
--- a/inc/managers/CustomTable.php
+++ b/inc/managers/CustomTable.php
@@ -21,11 +21,27 @@
{
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 ?CustomTable $karmaTable = null;
+ protected ?CustomTable $favouritesTable = null;
+
+ protected Cache $cache;
/**
* Fluent factory method
@@ -35,15 +51,220 @@
*
* @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 setKarma(CustomTable $karma):void
+ {
+ $this->karmaTable = $karma;
+ }
+ public function setFavourite(CustomTable $favourites):void
+ {
+ $this->favouritesTable = $favourites;
+ }
+
+ 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;
+ }
+
+ public function getColumnKeys():array
+ {
+ return array_keys($this->columns);
+ }
+
+ /**
+ * @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 +277,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}";
+
}
// =========================================================================
@@ -100,17 +334,37 @@
return $this;
}
+ public function orderByKarma(string $metric = 'karmic_score', string $direction = 'DESC'): self
+ {
+ if (!$this->karmaTable || !in_array($metric, ['upvotes', 'downvotes', 'karmic_score'])) {
+ return $this->orderBy('created_at');
+ }
+ $this->builder['orderby'] = $metric;
+ $this->builder['order'] = strtoupper($direction);
+ return $this;
+ }
+
+ public function orderByFavourites(string $direction = 'DESC'): self
+ {
+ if (!$this->favouritesTable) {
+ return $this->orderBy('created_at');
+ }
+ $this->builder['orderby'] = 'favourites_count';
+ $this->builder['order'] = strtoupper($direction);
+ return $this;
+ }
+
/**
- * Set LIMIT
+ * Set pagination
*
- * @param int $limit Number of records
- * @param int $offset Optional offset
+ * @param int $perPage Number of records per page
+ * @param int $page Page number (1-based)
* @return self
*/
- public function limit(int $limit, int $offset = 0): self
+ public function limit(int $perPage, int $page = 1): self
{
- $this->builder['limit'] = $limit;
- $this->builder['offset'] = $offset;
+ $this->builder['per_page'] = $perPage;
+ $this->builder['page'] = $page;
return $this;
}
@@ -236,6 +490,8 @@
return false;
}
+ $this->cache->flush();
+
return $this->wpdb->insert_id;
}
@@ -266,26 +522,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 +612,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(
+ 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);
+ }
+ );
}
/**
@@ -381,39 +637,122 @@
* 'where' => ['user_id' => 1],
* 'orderby' => 'date_added',
* 'order' => 'DESC',
- * 'limit' => 20
+ * 'per_page' => 20
* ]);
*/
- public function getMany(array $args = [], string $output = OBJECT): array
+ public function getMany(array $args = [], bool $itemsOnly = true, string $output = OBJECT): array
{
- $query = "SELECT * FROM {$this->fullTableName}";
- $values = [];
+ $items = $this->cache->remember(
+ 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']);
+
+ $query .= " WHERE {$clause['sql']}";
+ $values = array_merge($values, $clause['values']);
+ }
+
+ // ORDER BY
+ if (!empty($args['orderby'])) {
+ $orderby = $this->resolveOrderby($args['orderby']);
+ $order = (!empty($args['order']) && strtoupper($args['order']) === 'ASC') ? 'ASC' : 'DESC';
+ $query .= " ORDER BY {$orderby} {$order}";
+ }
+
+ // LIMIT
+ if (array_key_exists('limit', $args)) {
+ error_log('[CustomTable]::getMany deprecated key \'limit\' - use \'per_page\' instead. '.print_r($args, true));
+ $args['per_page'] = $args['limit'];
+ }
+ if (array_key_exists('offset', $args)) {
+ error_log('[CustomTable]::getMany deprecated key \'offset\' - use \'page\' instead. '.print_r($args, true));
+ $args['page'] = $args['offset'] / $args['limit'];
+ }
+ if (!empty($args['per_page'])) {
+ $perPage = absint($args['per_page']);
+ $page = !empty($args['page']) ? absint($args['page']) : 1;
+ $offset = ($page - 1) * $perPage;
+ $query .= " LIMIT {$offset}, {$perPage}";
+ }
+
+ if (empty($values)) {
+ return $this->wpdb->get_results($query, $output);
+ }
+
+ return $this->wpdb->get_results($this->wpdb->prepare($query, $values), $output);
+ }
+ );
+
+ if ($itemsOnly) {
+ return $items;
+ }
+ $page = max(1, $args['page'] ?? 1);
+ $perPage = $args['per_page']??false;
+ $total = $this->count($args['where']);
+ return [
+ 'items' => $items,
+ 'total' => $total,
+ 'has_more' => $perPage && ($page * $perPage) < $total,
+ ];
+ }
+
+ protected function resolveOrderby(string $orderby):string
+ {
+ $orderby = strtolower($orderby);
+ if (in_array($orderby, ['upvotes', 'downvotes', 'karmic_score']) && $this->karmaTable) {
+ $tableName = $this->karmaTable->getFullTableName();
+ return match ($orderby) {
+ 'upvotes' => "(SELECT COUNT(*) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id AND k.vote = 'up')",
+ 'downvotes' => "(SELECT COUNT(*) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id AND k.vote = 'down')",
+ 'karmic_score' => "(SELECT COALESCE(SUM(CASE WHEN k.vote = 'up' THEN 1 WHEN k.vote = 'down' THEN -1 ELSE 0 END), 0) FROM {$tableName} k WHERE k.item_id = {$this->fullTableName}.id)",
+ };
}
- // 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 ($orderby === 'favourites_count' && $this->favouritesTable) {
+ $tableName = $this->favouritesTable->getFullTableName();
+ return "(SELECT COUNT(*) FROM {$tableName} f WHERE f.target_id = {$this->fullTableName}.id)";
}
- // LIMIT
- if (!empty($args['limit'])) {
- $limit = absint($args['limit']);
- $offset = !empty($args['offset']) ? absint($args['offset']) : 0;
- $query .= " LIMIT {$offset}, {$limit}";
- }
+ return sanitize_sql_orderby($orderby);
+ }
- 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(
+ $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'] = $this->resolveOrderby($orderby);
+ }
+ if ($order) {
+ $args['order'] = $order;
+ }
+ if ($limit) {
+ $args['per_page'] = $limit;
+ }
+
+ return array_column($this->getMany($args), $column);
+ }
+ );
+
}
/**
@@ -424,19 +763,26 @@
*/
public function count(array $where = []): int
{
- $query = "SELECT COUNT(*) FROM {$this->fullTableName}";
- $values = [];
+ return $this->cache->remember(
+ 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 +833,7 @@
if ($result === false) {
$this->logError('update', ['data' => $data, 'where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -516,6 +863,7 @@
if ($result === false) {
$this->logError('delete', ['where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -638,7 +986,7 @@
} catch (Exception $e) {
$this->rollback();
$this->logError('transaction', ['error' => $e->getMessage()]);
- throw $e;
+ return false;
}
}
@@ -678,25 +1026,121 @@
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);
+
+ // OR grouping: ['OR' => [ [...cond1], [...cond2] ]]
+ if ($column === 'OR' && is_array($value)) {
+ $orConditions = [];
+ foreach ($value as $orWhere) {
+ $orClause = $this->buildWhereClause($orWhere);
+ $orConditions[] = $orClause['sql'];
+ $values = array_merge($values, $orClause['values']);
+ }
+ $conditions[] = '(' . implode(' OR ', $orConditions) . ')';
+ continue;
+ }
+
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 ($operator === 'IS NOT NULL') {
+ $conditions[] = "`{$column_safe}` IS NOT NULL";
+ } elseif (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);
+ } elseif (in_array($operator, ['BETWEEN', 'NOT BETWEEN']) && is_array($operand) && count($operand) === 2) {
+ $conditions[] = "`{$column_safe}` {$operator} " . $this->getPlaceholder($operand[0]) . " AND " . $this->getPlaceholder($operand[1]);
+ $values[] = $operand[0];
+ $values[] = $operand[1];
+ } 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];
}
/**
@@ -744,4 +1188,10 @@
error_log("[CustomTable:{$this->tableName}] {$operation} failed: " . $this->wpdb->last_error);
}
}
+
+ public function sum(string $column, array $where):float
+ {
+ $rows = $this->pluck($column, $where);
+ return array_sum($rows);
+ }
}
--
Gitblit v1.10.0