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']
? 'Online (MEMORY Engine)'
: 'Offline / Missing';
$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;
}
}
}