From ed57c386db34d8693ca75311972d0929ebe5f488 Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Mon, 01 Jun 2026 22:23:19 +0000
Subject: [PATCH] =Added some more Schema classes, allowed for override of array in outputSchema for complex schema, as for timeline post types
---
inc/managers/CustomTable.php | 228 +++++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 173 insertions(+), 55 deletions(-)
diff --git a/inc/managers/CustomTable.php b/inc/managers/CustomTable.php
index 4299b71..cb458ce 100644
--- a/inc/managers/CustomTable.php
+++ b/inc/managers/CustomTable.php
@@ -34,8 +34,12 @@
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
@@ -54,6 +58,19 @@
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();
@@ -94,12 +111,25 @@
}
}
+ 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;
+ }
+ }
/**
*
@@ -154,7 +184,7 @@
if (is_array($key)) {
$value = $key['value'];
// Ensure value is wrapped in parentheses
- if (!str_starts_with(trim($value), '(')) {
+ if (!str_contains(trim($value), '(`')) {
$value = '(`' . $value . '`)';
}
$parts[] = $key['key'] . ' KEY ' . $value;
@@ -165,11 +195,12 @@
}
// Constraints
- foreach ($this->constraints as $constraint => $references) {
- $parts[] = "CONSTRAINT {$constraint} REFERENCES {$references}";
+ foreach ($this->constraints as $constraint) {
+ $parts[] = $constraint;
}
$this->definition = "(\n " . implode(",\n ", $parts) . "\n)";
+ $this->createTable();
return $this;
}
@@ -190,6 +221,11 @@
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();
@@ -232,6 +268,8 @@
$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}";
@@ -409,6 +447,8 @@
return false;
}
+ $this->cache->flush();
+
return $this->wpdb->insert_id;
}
@@ -439,26 +479,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);
}
/**
@@ -536,10 +569,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);
+ }
+ );
}
/**
@@ -559,34 +599,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);
+ }
+ );
+
}
/**
@@ -597,19 +679,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));
+ }
+ );
+
}
/**
@@ -660,6 +749,7 @@
if ($result === false) {
$this->logError('update', ['data' => $data, 'where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -689,6 +779,7 @@
if ($result === false) {
$this->logError('delete', ['where' => $where]);
}
+ $this->cache->flush();
return $result;
}
@@ -811,7 +902,7 @@
} catch (Exception $e) {
$this->rollback();
$this->logError('transaction', ['error' => $e->getMessage()]);
- throw $e;
+ return false;
}
}
@@ -866,11 +957,21 @@
$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
@@ -909,18 +1010,35 @@
/**
* 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