yatra / app / Utils / QueryCache.php

QueryCache.php in Yatra – Travel Booking & Tour Operator Software trunk, at app/Utils/QueryCache.php

187 lines 5.5 KB

Raw Download Zip

1	<?php
2
3	declare(strict_types=1);
4
5	namespace Yatra\Utils;
6
7	/**
8	* Query Result Cache Manager
9	*
10	* Specialized caching for database query results with intelligent invalidation
11	*/
12	class QueryCache
13	{
14	/**
15	* Cache expensive query results with automatic invalidation
16	*
17	* @return mixed
18	*/
19	public static function getQueryResult(string $sql, array $params = [], int $duration = 600)
20	{
21	// Create cache key from SQL and parameters
22	$cacheKey = self::generateQueryCacheKey($sql, $params);
23
24	return Cache::remember($cacheKey, function() use ($sql, $params) {
25	global $wpdb;
26
27	$startTime = microtime(true);
28
29	// Execute query based on type
30	if (empty($params)) {
31	$result = $wpdb->get_results($sql);
32	} else {
33	$result = $wpdb->get_results($wpdb->prepare($sql, ...$params));
34	}
35
36	$executionTime = microtime(true) - $startTime;
37
38	// Log slow queries
39	if ($executionTime > 1.0) {
40	Logger::warning("Slow query cached", [
41	'execution_time' => $executionTime,
42	'sql' => $sql,
43	'params' => $params
44	]);
45	} else {
46	Logger::debug("Query result cached", [
47	'execution_time' => $executionTime,
48	'cache_key' => substr($cacheKey, 0, 50) . '...'
49	]);
50	}
51
52	return $result;
53	}, $duration);
54	}
55
56	/**
57	* Cache single row query results
58	*/
59	public static function getRow(string $sql, array $params = [], int $duration = 600): ?\stdClass
60	{
61	$cacheKey = self::generateQueryCacheKey($sql . '_ROW', $params);
62
63	return Cache::remember($cacheKey, function() use ($sql, $params) {
64	global $wpdb;
65
66	if (empty($params)) {
67	return $wpdb->get_row($sql);
68	} else {
69	return $wpdb->get_row($wpdb->prepare($sql, ...$params));
70	}
71	}, $duration);
72	}
73
74	/**
75	* Cache single value query results
76	*
77	* @return mixed
78	*/
79	public static function getVar(string $sql, array $params = [], int $duration = 600)
80	{
81	$cacheKey = self::generateQueryCacheKey($sql . '_VAR', $params);
82
83	return Cache::remember($cacheKey, function() use ($sql, $params) {
84	global $wpdb;
85
86	if (empty($params)) {
87	return $wpdb->get_var($sql);
88	} else {
89	return $wpdb->get_var($wpdb->prepare($sql, ...$params));
90	}
91	}, $duration);
92	}
93
94	/**
95	* Cache count queries
96	*/
97	public static function getCount(string $table, array $conditions = [], int $duration = 300): int
98	{
99	$cacheKey = 'count_' . $table . '_' . md5(serialize($conditions));
100
101	return (int) Cache::remember($cacheKey, function() use ($table, $conditions) {
102	global $wpdb;
103
104	$sql = "SELECT COUNT(*) FROM {$wpdb->prefix}{$table}";
105	$params = [];
106
107	if (!empty($conditions)) {
108	$whereClauses = [];
109	foreach ($conditions as $field => $value) {
110	$whereClauses[] = "{$field} = %s";
111	$params[] = $value;
112	}
113	$sql .= " WHERE " . implode(' AND ', $whereClauses);
114	}
115
116	if (empty($params)) {
117	return (int) $wpdb->get_var($sql);
118	} else {
119	return (int) $wpdb->get_var($wpdb->prepare($sql, ...$params));
120	}
121	}, $duration);
122	}
123
124	/**
125	* Invalidate query cache by table
126	*/
127	public static function invalidateByTable(string $table): void
128	{
129	// Clear all query caches that might involve this table
130	$patterns = [
131	Cache::PREFIX_QUERY_RESULT,
132	'count_' . $table . '_',
133	];
134
135	foreach ($patterns as $pattern) {
136	Cache::clearByPrefix($pattern);
137	}
138
139	Logger::info("Query cache invalidated for table", ['table' => $table]);
140	}
141
142	/**
143	* Invalidate all query caches
144	*/
145	public static function invalidateAll(): void
146	{
147	Cache::clearByPrefix(Cache::PREFIX_QUERY_RESULT);
148	Cache::clearByPrefix('count_');
149
150	Logger::info("All query caches invalidated");
151	}
152
153	/**
154	* Generate cache key for query
155	*/
156	private static function generateQueryCacheKey(string $sql, array $params = []): string
157	{
158	// Normalize SQL (remove extra whitespace, convert to lowercase)
159	$normalizedSql = preg_replace('/\s+/', ' ', trim(strtolower($sql)));
160
161	// Create hash from SQL and parameters
162	$hash = md5($normalizedSql . serialize($params));
163
164	return Cache::PREFIX_QUERY_RESULT . $hash;
165	}
166
167	/**
168	* Get query cache statistics
169	*/
170	public static function getStats(): array
171	{
172	return [
173	'cache_backend' => Cache::getAvailableBackends()[0] ?? 'none',
174	'total_cached_queries' => self::getCachedQueryCount(),
175	];
176	}
177
178	/**
179	* Get count of cached queries (approximate)
180	*/
181	private static function getCachedQueryCount(): int
182	{
183	// This is an approximation - actual implementation would depend on cache backend
184	return 0; // Placeholder
185	}
186	}
187