337 lines
12 KiB
PHP
337 lines
12 KiB
PHP
<?php
|
|
|
|
/**
|
|
* DB Memory Cache Module for PrestaShop
|
|
* Robust edition: High performance, dynamic sizing, auto-recovery.
|
|
*/
|
|
|
|
declare(strict_types=1);
|
|
|
|
if (!defined('_PS_VERSION_')) {
|
|
exit;
|
|
}
|
|
|
|
class DbMemoryCache extends Module
|
|
{
|
|
private string $tableName;
|
|
|
|
// Statically cache the max size so we don't query the `configuration` table on every set()
|
|
private static ?int $maxCacheSizeChars = null;
|
|
|
|
public function __construct()
|
|
{
|
|
$this->name = 'dbmemorycache';
|
|
$this->tab = 'administration';
|
|
$this->version = '1.3.0';
|
|
$this->author = 'Panariga';
|
|
$this->need_instance = 0;
|
|
$this->ps_versions_compliancy = ['min' => '8.0.0', 'max' => _PS_VERSION_];
|
|
$this->bootstrap = true;
|
|
|
|
parent::__construct();
|
|
|
|
$this->displayName = $this->trans('MySQL Memory Cache Pro', [], 'Modules.Dbmemorycache.Admin');
|
|
$this->description = $this->trans('High-speed RAM cache. Auto-recovers table and handles dynamic sizes.', [], 'Modules.Dbmemorycache.Admin');
|
|
|
|
$this->tableName = _DB_PREFIX_ . 'custom_mem_cache';
|
|
}
|
|
|
|
public function install(): bool
|
|
{
|
|
return parent::install() && $this->calculateAndSaveLimits() && $this->createCacheTable();
|
|
}
|
|
|
|
public function uninstall(): bool
|
|
{
|
|
return $this->dropCacheTable()
|
|
&& Configuration::deleteByName('DB_MEM_CACHE_MAX_SIZE')
|
|
&& parent::uninstall();
|
|
}
|
|
/**
|
|
* Load the configuration page in the PrestaShop Back Office.
|
|
*/
|
|
public function getContent(): string
|
|
{
|
|
$output = '';
|
|
|
|
// Handle form submissions for cache management
|
|
if (Tools::isSubmit('submitFlushCache')) {
|
|
if ($this->flush()) {
|
|
$output .= $this->displayConfirmation($this->trans('Cache flushed successfully.', [], 'Modules.Dbmemorycache.Admin'));
|
|
} else {
|
|
$output .= $this->displayError($this->trans('Failed to flush cache. Table might be missing.', [], 'Modules.Dbmemorycache.Admin'));
|
|
}
|
|
} elseif (Tools::isSubmit('submitPruneCache')) {
|
|
if ($this->prune()) {
|
|
$output .= $this->displayConfirmation($this->trans('Expired keys pruned successfully.', [], 'Modules.Dbmemorycache.Admin'));
|
|
} else {
|
|
$output .= $this->displayError($this->trans('Failed to prune cache.', [], 'Modules.Dbmemorycache.Admin'));
|
|
}
|
|
}
|
|
|
|
// Fetch current table statistics
|
|
$stats = $this->getTableStats();
|
|
|
|
// Build the HTML view using standard PrestaShop Bootstrap UI
|
|
$currentIndex = $this->context->link->getAdminLink('AdminModules', false)
|
|
. '&configure=' . $this->name
|
|
. '&tab_module=' . $this->tab
|
|
. '&module_name=' . $this->name
|
|
. '&token=' . Tools::getAdminTokenLite('AdminModules');
|
|
|
|
$statusLabel = $stats['exists']
|
|
? '<span class="badge badge-success" style="background-color:#8bc34a">Online (MEMORY Engine)</span>'
|
|
: '<span class="badge badge-danger" style="background-color:#f44336">Offline / Missing</span>';
|
|
|
|
$this->context->smarty->assign([
|
|
'statusLabel' => $statusLabel,
|
|
'stats' => $stats,
|
|
'dataMemoryUsed' => $this->formatBytes($stats['data_size']),
|
|
'indexMemoryUsed' => $this->formatBytes($stats['index_size']),
|
|
'currentIndex' => $currentIndex,
|
|
]);
|
|
$output .= $this->context->smarty->fetch($this->local_path . 'views/templates/admin/configure.tpl');
|
|
|
|
return $output;
|
|
}
|
|
|
|
/**
|
|
* Queries MySQL for the specific MEMORY table statistics.
|
|
*/
|
|
private function getTableStats(): array
|
|
{
|
|
$db = Db::getInstance();
|
|
$stats = [
|
|
'exists' => false,
|
|
'rows' => 0,
|
|
'expired_rows' => 0,
|
|
'data_size' => 0,
|
|
'index_size' => 0,
|
|
'max_size' => $this->getMaxCacheSize(),
|
|
];
|
|
|
|
// Escape the prefix+tablename for the LIKE clause
|
|
$safeTableName = pSQL($this->tableName);
|
|
$sql = "SHOW TABLE STATUS LIKE '$safeTableName'";
|
|
$stats['sql'] = $sql;
|
|
|
|
try {
|
|
$tableStatus = $db->executeS($sql, true);
|
|
if ($tableStatus) {
|
|
$stats['exists'] = true;
|
|
$stats['rows'] = (int)($tableStatus['0']['Rows'] ?? 0);
|
|
$stats['data_size'] = (int)($tableStatus['0']['Data_length'] ?? 0);
|
|
$stats['index_size'] = (int)($tableStatus['0']['Index_length'] ?? 0);
|
|
|
|
// Count how many rows are technically expired but haven't been overwritten/pruned yet
|
|
$now = time();
|
|
$expiredSql = "SELECT COUNT(*) FROM `{$this->tableName}` WHERE `expiry` <= $now";
|
|
$stats['expired_rows'] = (int)$db->getValue($expiredSql);
|
|
}
|
|
} catch (\Exception $e) {
|
|
// Table likely doesn't exist (reboot/crash)
|
|
$stats['exists'] = false;
|
|
}
|
|
|
|
return $stats;
|
|
}
|
|
|
|
/**
|
|
* Helper to convert bytes to human-readable format.
|
|
*/
|
|
private function formatBytes(int $bytes, int $precision = 2): string
|
|
{
|
|
if ($bytes <= 0) {
|
|
return '0 B';
|
|
}
|
|
$units = ['B', 'KB', 'MB', 'GB', 'TB'];
|
|
$bytes = max($bytes, 0);
|
|
$pow = floor(($bytes ? log($bytes) : 0) / log(1024));
|
|
$pow = min($pow, count($units) - 1);
|
|
|
|
$bytes /= (1 << (10 * $pow));
|
|
|
|
return round($bytes, $precision) . ' ' . $units[$pow];
|
|
}
|
|
/**
|
|
* Calculates the maximum safe VARCHAR size for the MEMORY engine
|
|
* based on MySQL's 65,535 byte row limit.
|
|
*/
|
|
private function calculateAndSaveLimits(): bool
|
|
{
|
|
// Max row size: 65535 bytes
|
|
// Key: 128 chars * 4 bytes (utf8mb4) = 512 bytes
|
|
// Expiry: INT = 4 bytes
|
|
// Available bytes for value: 65535 - 512 - 4 = 65019 bytes
|
|
// Max characters (utf8mb4): floor(65019 / 4) = 16254
|
|
|
|
// We set 16000 as a safe upper boundary, but you could query DB version here if needed.
|
|
$safeCharLimit = 16000;
|
|
|
|
return Configuration::updateValue('DB_MEM_CACHE_MAX_SIZE', $safeCharLimit);
|
|
}
|
|
|
|
/**
|
|
* Fetch max size from config (cached statically for performance)
|
|
*/
|
|
private function getMaxCacheSize(): int
|
|
{
|
|
if (self::$maxCacheSizeChars === null) {
|
|
self::$maxCacheSizeChars = (int) Configuration::get('DB_MEM_CACHE_MAX_SIZE', null, null, null, 16000);
|
|
}
|
|
return self::$maxCacheSizeChars;
|
|
}
|
|
|
|
private function createCacheTable(): bool
|
|
{
|
|
$maxChars = $this->getMaxCacheSize();
|
|
|
|
// We use utf8mb4_bin for the Key (case-sensitive)
|
|
// We use ascii for the Value (1 byte per char) to save 75% RAM!
|
|
$sql = "CREATE TABLE IF NOT EXISTS `{$this->tableName}` (
|
|
`cache_key` VARCHAR(128) COLLATE utf8mb4_bin NOT NULL,
|
|
`cache_value` VARCHAR(" . $maxChars . ") CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
|
|
`expiry` INT(11) UNSIGNED NOT NULL,
|
|
PRIMARY KEY (`cache_key`),
|
|
INDEX `idx_expiry` (`expiry`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 MAX_ROWS=10000;";
|
|
|
|
try {
|
|
return Db::getInstance()->execute($sql);
|
|
} catch (\Exception $e) {
|
|
PrestaShopLogger::addLog("DbMemoryCache Installation Error: " . $e->getMessage(), 3);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
private function dropCacheTable(): bool
|
|
{
|
|
return Db::getInstance()->execute("DROP TABLE IF EXISTS `{$this->tableName}`");
|
|
}
|
|
|
|
public function setValue(string $key, $value, int $ttlSeconds = 3600): bool
|
|
{
|
|
// We use serialize instead of JSON. JSON converts Objects to Arrays,
|
|
// which breaks cache expectations for other PrestaShop modules.
|
|
$encodedValue = serialize($value);
|
|
|
|
// Size Guard
|
|
if (mb_strlen($encodedValue, 'UTF-8') > $this->getMaxCacheSize()) {
|
|
return false;
|
|
}
|
|
|
|
$expiry = time() + $ttlSeconds;
|
|
$db = Db::getInstance();
|
|
$safeKey = pSQL($key);
|
|
$safeValue = pSQL($encodedValue, true);
|
|
// REPLACE INTO is faster and cleaner than DELETE + INSERT IGNORE
|
|
$sql = "REPLACE INTO `{$this->tableName}` (`cache_key`, `cache_value`, `expiry`)
|
|
VALUES ('$safeKey', '$safeValue', $expiry)";
|
|
try {
|
|
|
|
return $db->execute($sql);
|
|
} catch (\PrestaShopDatabaseException $e) {
|
|
$errorMsg = $e->getMessage();
|
|
|
|
// Error 1146: Table doesn't exist (Manual drop or DB crash)
|
|
if (strpos($errorMsg, '1146') !== false) {
|
|
if ($this->createCacheTable()) {
|
|
return $db->execute($sql); // Retry once
|
|
}
|
|
}
|
|
|
|
// Error 1114: The table is full
|
|
if (strpos($errorMsg, 'is full') !== false || $e->getCode() == 1114) {
|
|
$this->prune(); // Emergency prune
|
|
try {
|
|
return $db->execute($sql); // Retry once
|
|
} catch (\Exception $e2) {
|
|
return false; // Still full, fail gracefully
|
|
}
|
|
}
|
|
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function getValue(string $key)
|
|
{
|
|
$safeKey = pSQL($key);
|
|
$now = time();
|
|
|
|
try {
|
|
$sql = "SELECT `cache_value` FROM `{$this->tableName}`
|
|
WHERE `cache_key` = '$safeKey' AND `expiry` > $now";
|
|
$result = Db::getInstance()->getValue($sql);
|
|
} catch (\PrestaShopDatabaseException $e) {
|
|
// Recreate table quietly if it went missing, return null
|
|
if (strpos($e->getMessage(), '1146') !== false) {
|
|
$this->createCacheTable();
|
|
}
|
|
return null;
|
|
}
|
|
|
|
if ($result === false || $result === null) {
|
|
return null;
|
|
}
|
|
|
|
// Supress unserialize notices if data became corrupted
|
|
return @unserialize($result);
|
|
}
|
|
|
|
public function existsValue(string $key): bool
|
|
{
|
|
$safeKey = pSQL($key);
|
|
$now = time();
|
|
|
|
try {
|
|
$sql = "SELECT 1 FROM `{$this->tableName}`
|
|
WHERE `cache_key` = '$safeKey' AND `expiry` > $now";
|
|
return (bool) Db::getInstance()->getValue($sql);
|
|
} catch (\Exception $e) {
|
|
if (strpos($e->getMessage(), '1146') !== false) {
|
|
$this->createCacheTable();
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function deleteValue(string $key): bool
|
|
{
|
|
$safeKey = pSQL($key);
|
|
try {
|
|
return Db::getInstance()->execute("DELETE FROM `{$this->tableName}` WHERE `cache_key` = '$safeKey'");
|
|
} catch (\Exception $e) {
|
|
if (strpos($e->getMessage(), '1146') !== false) {
|
|
$this->createCacheTable();
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function prune(): bool
|
|
{
|
|
$now = time();
|
|
try {
|
|
return Db::getInstance()->execute("DELETE FROM `{$this->tableName}` WHERE `expiry` <= $now");
|
|
} catch (\Exception $e) {
|
|
if (strpos($e->getMessage(), '1146') !== false) {
|
|
$this->createCacheTable();
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public function flush(): bool
|
|
{
|
|
try {
|
|
return Db::getInstance()->execute("TRUNCATE TABLE `{$this->tableName}`");
|
|
} catch (\Exception $e) {
|
|
if (strpos($e->getMessage(), '1146') !== false) {
|
|
$this->createCacheTable();
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
}
|