name = 'dbmemorycache';
$this->tab = 'administration';
$this->version = '1.4.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
{
$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) {
return $this->handleWriteError($e, $sql);
}
}
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;
}
}
/**
* Unified handler for write errors (1114 table full, 1146 table missing).
* Attempts recovery and retries the SQL once.
*/
private function handleWriteError(\PrestaShopDatabaseException $e, string $retrySql): bool
{
$errorMsg = $e->getMessage();
$db = Db::getInstance();
// Error 1146: Table doesn't exist (manual drop or DB crash/reboot)
if (strpos($errorMsg, '1146') !== false) {
if ($this->createCacheTable()) {
try {
return $db->execute($retrySql);
} catch (\Exception $e2) {
return false;
}
}
return false;
}
// Error 1114: The table is full
if (strpos($errorMsg, '1114') !== false || strpos($errorMsg, 'is full') !== false) {
if ($this->emergencyCleanup()) {
try {
return $db->execute($retrySql);
} catch (\PrestaShopDatabaseException $e2) {
// Still full after cleanup — escalate one more time
$errorMsg2 = $e2->getMessage();
if (strpos($errorMsg2, '1114') !== false || strpos($errorMsg2, 'is full') !== false) {
$this->emergencyCleanup(true); // aggressive mode
try {
return $db->execute($retrySql);
} catch (\Exception $e3) {
PrestaShopLogger::addLog(
'DbMemoryCache: Table still full after aggressive cleanup. Key dropped.',
3, null, 'DbMemoryCache'
);
return false;
}
}
return false;
}
}
return false;
}
// Unknown write error — log and fail
PrestaShopLogger::addLog(
'DbMemoryCache: Unhandled write error: ' . $errorMsg,
3, null, 'DbMemoryCache'
);
return false;
}
/**
* Multi-level emergency cleanup for "table is full" errors.
*
* Strategy (escalating):
* 1. Prune all expired rows
* 2. Evict oldest 25% of rows by expiry (soonest to expire = least valuable)
* 3. (aggressive) Evict oldest 50% + flush if nothing else works
*
* @param bool $aggressive If true, skip to the most aggressive cleanup level.
* @return bool True if some rows were freed.
*/
private function emergencyCleanup(bool $aggressive = false): bool
{
// Prevent recursive cleanup if setValue is called during cleanup
if (self::$cleanupInProgress) {
return false;
}
self::$cleanupInProgress = true;
try {
$db = Db::getInstance();
// Level 1: Prune expired rows
if (!$aggressive) {
$now = time();
try {
$db->execute("DELETE FROM `{$this->tableName}` WHERE `expiry` <= $now");
} catch (\Exception $e) {
// table might be missing, nothing to clean
}
$freedRows = (int) $db->getNumberOfRows();
if ($freedRows > 0) {
PrestaShopLogger::addLog(
"DbMemoryCache: Emergency prune freed $freedRows expired rows.",
2, null, 'DbMemoryCache'
);
return true;
}
// Level 2: Evict the oldest 25% by nearest expiry (least time remaining)
if ($this->evictOldest(25)) {
return true;
}
}
// Level 3 (aggressive): Evict 50%
if ($this->evictOldest(50)) {
PrestaShopLogger::addLog(
'DbMemoryCache: Aggressive cleanup — evicted 50% of rows.',
2, null, 'DbMemoryCache'
);
return true;
}
// Level 4: Full flush as last resort
PrestaShopLogger::addLog(
'DbMemoryCache: Full flush triggered — all other cleanup levels failed.',
3, null, 'DbMemoryCache'
);
try {
return $db->execute("TRUNCATE TABLE `{$this->tableName}`");
} catch (\Exception $e) {
// TRUNCATE can't fail on MEMORY tables unless table is missing
$this->createCacheTable();
return true;
}
} finally {
self::$cleanupInProgress = false;
}
}
/**
* Evict the oldest N% of rows (by soonest expiry = least remaining TTL).
* For a MEMORY table, this is the most efficient LRU-like eviction.
*
* @param int $percent Percentage of rows to evict (1-100).
* @return bool True if any rows were deleted.
*/
private function evictOldest(int $percent): bool
{
$db = Db::getInstance();
try {
$totalRows = (int) $db->getValue("SELECT COUNT(*) FROM `{$this->tableName}`");
if ($totalRows === 0) {
return false;
}
$rowsToEvict = max(1, (int) ceil($totalRows * $percent / 100));
// Find the expiry threshold: the Nth-lowest expiry value
$threshold = $db->getValue(
"SELECT `expiry` FROM `{$this->tableName}` ORDER BY `expiry` ASC LIMIT $rowsToEvict, 1"
);
if ($threshold !== false && $threshold !== null) {
// Delete all rows with expiry below the threshold
$db->execute(
"DELETE FROM `{$this->tableName}` WHERE `expiry` < " . (int) $threshold
);
} else {
// Fewer rows than the limit — delete the bottom chunk directly
// For MEMORY engine, sub-selects in DELETE are not supported,
// so we use a two-step approach
$keysToDelete = $db->executeS(
"SELECT `cache_key` FROM `{$this->tableName}` ORDER BY `expiry` ASC LIMIT $rowsToEvict"
);
if ($keysToDelete && count($keysToDelete) > 0) {
$keyList = implode("','", array_map(function ($row) {
return pSQL($row['cache_key']);
}, $keysToDelete));
$db->execute(
"DELETE FROM `{$this->tableName}` WHERE `cache_key` IN ('$keyList')"
);
}
}
$deletedCount = max(0, $totalRows - (int) $db->getValue("SELECT COUNT(*) FROM `{$this->tableName}`"));
if ($deletedCount > 0) {
PrestaShopLogger::addLog(
"DbMemoryCache: Evicted $deletedCount rows ({$percent}% target of $totalRows).",
2, null, 'DbMemoryCache'
);
return true;
}
} catch (\Exception $e) {
PrestaShopLogger::addLog(
'DbMemoryCache: evictOldest failed: ' . $e->getMessage(),
3, null, 'DbMemoryCache'
);
}
return false;
}
}