king-addons / includes / extensions / Image_Optimizer / Image_Optimizer_DB.php

Image_Optimizer_DB.php in King Addons for Elementor – 80+ Elementor Widgets, 4 000+ Elementor Templates, WooCommerce, Mega Menu, Popup Builder 51.1.76, at includes/extensions/Image_Optimizer/Image_Optimizer_DB.php

573 lines 20.1 KB

Raw Download Zip

1	<?php
2	/**
3	* Image Optimizer database/metadata operations.
4	*
5	* @package King_Addons
6	*/
7
8	namespace King_Addons\Image_Optimizer;
9
10	if (!defined('ABSPATH')) {
11	exit;
12	}
13
14	class Image_Optimizer_DB
15	{
16	private const META_KEY = '_king_img_optimized';
17
18	/**
19	* Sync the WordPress attachment to point to the optimized files.
20	* This updates `_wp_attached_file` and attachment metadata, while keeping originals via backup meta keys.
21	*/
22	public static function sync_attachment_to_optimized(int $attachment_id, array $opt_meta): void
23	{
24	self::update_attachment_metadata_to_webp($attachment_id, $opt_meta);
25	}
26
27	/**
28	* Get optimization metadata for an attachment.
29	*/
30	public static function get_optimization_meta(int $attachment_id): ?array
31	{
32	$meta = get_post_meta($attachment_id, self::META_KEY, true);
33	return is_array($meta) ? $meta : null;
34	}
35
36	/**
37	* Set optimization metadata for an attachment.
38	*/
39	public static function set_optimization_meta(int $attachment_id, array $meta): bool
40	{
41	return (bool) update_post_meta($attachment_id, self::META_KEY, $meta);
42	}
43
44	/**
45	* Delete optimization metadata for an attachment.
46	*/
47	public static function delete_optimization_meta(int $attachment_id): bool
48	{
49	return delete_post_meta($attachment_id, self::META_KEY);
50	}
51
52	/**
53	* Apply WebP URLs in database - replace original URLs with optimized versions.
54	*/
55	public static function apply_webp_urls(int $attachment_id, array $meta): int
56	{
57	global $wpdb;
58
59	if (empty($meta['sizes'])) {
60	return 0;
61	}
62
63	$replacements = [];
64	$upload_dir = wp_upload_dir();
65
66	foreach ($meta['sizes'] as $size => $data) {
67	$original_path = $data['original_path'] ?? '';
68	$optimized_path = $data['optimized_path'] ?? $data['webp_path'] ?? '';
69
70	if (empty($original_path) \|\| empty($optimized_path)) {
71	continue;
72	}
73
74	// Skip if paths are the same (shouldn't happen but safety check)
75	if ($original_path === $optimized_path) {
76	continue;
77	}
78
79	// Build URLs from paths
80	$original_url = str_replace(
81	$upload_dir['basedir'],
82	$upload_dir['baseurl'],
83	$original_path
84	);
85	$optimized_url = str_replace(
86	$upload_dir['basedir'],
87	$upload_dir['baseurl'],
88	$optimized_path
89	);
90
91	$replacements[$original_url] = $optimized_url;
92	}
93
94	if (empty($replacements)) {
95	return 0;
96	}
97
98	$updated = 0;
99
100	// Update wp_posts.post_content
101	foreach ($replacements as $old_url => $new_url) {
102	$result = $wpdb->query(
103	$wpdb->prepare(
104	"UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s) WHERE post_content LIKE %s",
105	$old_url,
106	$new_url,
107	'%' . $wpdb->esc_like($old_url) . '%'
108	)
109	);
110	$updated += $result !== false ? $result : 0;
111	}
112
113	// Update wp_postmeta
114	foreach ($replacements as $old_url => $new_url) {
115	$result = $wpdb->query(
116	$wpdb->prepare(
117	"UPDATE {$wpdb->postmeta} SET meta_value = REPLACE(meta_value, %s, %s) WHERE meta_value LIKE %s",
118	$old_url,
119	$new_url,
120	'%' . $wpdb->esc_like($old_url) . '%'
121	)
122	);
123	$updated += $result !== false ? $result : 0;
124	}
125
126	// Update wp_options (widgets, theme mods, etc.)
127	foreach ($replacements as $old_url => $new_url) {
128	$result = $wpdb->query(
129	$wpdb->prepare(
130	"UPDATE {$wpdb->options} SET option_value = REPLACE(option_value, %s, %s) WHERE option_value LIKE %s",
131	$old_url,
132	$new_url,
133	'%' . $wpdb->esc_like($old_url) . '%'
134	)
135	);
136	$updated += $result !== false ? $result : 0;
137	}
138
139	// Update wp_termmeta
140	foreach ($replacements as $old_url => $new_url) {
141	$result = $wpdb->query(
142	$wpdb->prepare(
143	"UPDATE {$wpdb->termmeta} SET meta_value = REPLACE(meta_value, %s, %s) WHERE meta_value LIKE %s",
144	$old_url,
145	$new_url,
146	'%' . $wpdb->esc_like($old_url) . '%'
147	)
148	);
149	$updated += $result !== false ? $result : 0;
150	}
151
152	// Handle Elementor data (JSON in postmeta)
153	$updated += self::replace_in_elementor_data($replacements);
154
155	// Update attachment metadata
156	self::update_attachment_metadata_to_webp($attachment_id, $meta);
157
158	// Mark URLs as replaced in meta
159	$meta['urls_replaced'] = true;
160	$meta['urls_replaced_at'] = current_time('mysql');
161	$meta['url_replacements'] = $replacements;
162	self::set_optimization_meta($attachment_id, $meta);
163
164	return $updated;
165	}
166
167	/**
168	* Replace URLs in Elementor data.
169	*/
170	private static function replace_in_elementor_data(array $replacements): int
171	{
172	global $wpdb;
173
174	$updated = 0;
175
176	// Get all Elementor data
177	$results = $wpdb->get_results(
178	"SELECT post_id, meta_value FROM {$wpdb->postmeta} WHERE meta_key = '_elementor_data'"
179	);
180
181	foreach ($results as $row) {
182	$data = $row->meta_value;
183	$modified = false;
184
185	foreach ($replacements as $old_url => $new_url) {
186	if (strpos($data, $old_url) !== false) {
187	$data = str_replace($old_url, $new_url, $data);
188	$modified = true;
189	}
190	}
191
192	if ($modified) {
193	$result = $wpdb->update(
194	$wpdb->postmeta,
195	['meta_value' => $data],
196	[
197	'post_id' => $row->post_id,
198	'meta_key' => '_elementor_data',
199	]
200	);
201	if ($result !== false) {
202	$updated += $result;
203	}
204	}
205	}
206
207	return $updated;
208	}
209
210	/**
211	* Update attachment metadata to use optimized version.
212	*/
213	private static function update_attachment_metadata_to_webp(int $attachment_id, array $opt_meta): void
214	{
215	$metadata = wp_get_attachment_metadata($attachment_id);
216
217	if (!is_array($metadata)) {
218	return;
219	}
220
221	// Store original metadata for potential rollback (if not already stored)
222	if (!get_post_meta($attachment_id, '_king_img_original_metadata', true)) {
223	update_post_meta($attachment_id, '_king_img_original_metadata', $metadata);
224	}
225
226	$format = $opt_meta['format'] ?? 'webp';
227
228	$upload_dir = wp_upload_dir();
229
230	// Backup original attached file (relative) for rollback
231	if (!get_post_meta($attachment_id, '_king_img_original_attached_file', true)) {
232	$original_attached = get_post_meta($attachment_id, '_wp_attached_file', true);
233	if (!empty($original_attached)) {
234	update_post_meta($attachment_id, '_king_img_original_attached_file', $original_attached);
235	}
236	}
237
238	// Update main file: set metadata['file'] and _wp_attached_file to the optimized version
239	$full_optimized_path = $opt_meta['sizes']['full']['optimized_path'] ?? $opt_meta['sizes']['full']['webp_path'] ?? '';
240	if (!empty($full_optimized_path) && is_string($full_optimized_path) && strpos($full_optimized_path, $upload_dir['basedir']) === 0) {
241	$relative = ltrim(str_replace($upload_dir['basedir'], '', $full_optimized_path), '/');
242	if (!empty($relative)) {
243	$metadata['file'] = $relative;
244
245	// Update stored filesize so Media Library shows correct value
246	if (file_exists($full_optimized_path)) {
247	$metadata['filesize'] = filesize($full_optimized_path);
248	}
249
250	// Use WP helper when available
251	if (function_exists('update_attached_file')) {
252	update_attached_file($attachment_id, $full_optimized_path);
253	} else {
254	update_post_meta($attachment_id, '_wp_attached_file', $relative);
255	}
256
257	// Update dimensions so Media Library "Dimensions" stays correct after browser resize
258	if (function_exists('getimagesize') && file_exists($full_optimized_path)) {
259	$dims = @getimagesize($full_optimized_path);
260	if (is_array($dims) && !empty($dims[0]) && !empty($dims[1])) {
261	$metadata['width'] = (int) $dims[0];
262	$metadata['height'] = (int) $dims[1];
263	}
264	}
265	}
266	}
267
268	// Update sizes' filenames + filesizes + dimensions if we have optimized versions
269	if (!empty($metadata['sizes']) && is_array($metadata['sizes']) && !empty($opt_meta['sizes'])) {
270	foreach ($metadata['sizes'] as $size_name => &$size_data) {
271	$size_optimized_path = $opt_meta['sizes'][$size_name]['optimized_path'] ?? $opt_meta['sizes'][$size_name]['webp_path'] ?? '';
272	if (!empty($size_optimized_path) && is_string($size_optimized_path) && strpos($size_optimized_path, $upload_dir['basedir']) === 0) {
273	$size_data['file'] = basename($size_optimized_path);
274	if (file_exists($size_optimized_path)) {
275	$size_data['filesize'] = filesize($size_optimized_path);
276
277	if (function_exists('getimagesize')) {
278	$dims = @getimagesize($size_optimized_path);
279	if (is_array($dims) && !empty($dims[0]) && !empty($dims[1])) {
280	$size_data['width'] = (int) $dims[0];
281	$size_data['height'] = (int) $dims[1];
282	}
283	}
284	}
285	$size_format = $opt_meta['sizes'][$size_name]['format'] ?? $format;
286	$size_data['mime-type'] = ($size_format === 'jpg') ? 'image/jpeg' : ('image/' . $size_format);
287	}
288	}
289	unset($size_data);
290	}
291
292	// Update sizes
293	if (!empty($metadata['sizes'])) {
294	foreach ($metadata['sizes'] as $size_name => &$size_data) {
295	// Determine which path to use
296	$size_optimized_path = $opt_meta['sizes'][$size_name]['optimized_path'] ?? $opt_meta['sizes'][$size_name]['webp_path'] ?? '';
297
298	if (!empty($size_optimized_path)) {
299	$size_data['file'] = basename($size_optimized_path);
300
301	// Update mime type based on actual format
302	$size_format = $opt_meta['sizes'][$size_name]['format'] ?? $format;
303	if ($size_format === 'jpg') {
304	$size_data['mime-type'] = 'image/jpeg';
305	} else {
306	$size_data['mime-type'] = 'image/' . $size_format;
307	}
308	}
309	}
310	}
311
312	wp_update_attachment_metadata($attachment_id, $metadata);
313
314	// Update post mime type
315	$new_mime = ($format === 'jpg') ? 'image/jpeg' : 'image/' . $format;
316	wp_update_post([
317	'ID' => $attachment_id,
318	'post_mime_type' => $new_mime,
319	]);
320	}
321
322	/**
323	* Revert to original URLs.
324	*/
325	public static function revert_to_original_urls(int $attachment_id): int
326	{
327	$meta = self::get_optimization_meta($attachment_id);
328
329	$updated = 0;
330
331	if (!empty($meta['url_replacements']) && is_array($meta['url_replacements'])) {
332	global $wpdb;
333
334	// Reverse the replacements
335	$replacements = array_flip($meta['url_replacements']);
336
337	// Update wp_posts.post_content
338	foreach ($replacements as $old_url => $new_url) {
339	$result = $wpdb->query(
340	$wpdb->prepare(
341	"UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s) WHERE post_content LIKE %s",
342	$old_url,
343	$new_url,
344	'%' . $wpdb->esc_like($old_url) . '%'
345	)
346	);
347	$updated += $result !== false ? $result : 0;
348	}
349
350	// Update wp_postmeta
351	foreach ($replacements as $old_url => $new_url) {
352	$result = $wpdb->query(
353	$wpdb->prepare(
354	"UPDATE {$wpdb->postmeta} SET meta_value = REPLACE(meta_value, %s, %s) WHERE meta_value LIKE %s",
355	$old_url,
356	$new_url,
357	'%' . $wpdb->esc_like($old_url) . '%'
358	)
359	);
360	$updated += $result !== false ? $result : 0;
361	}
362
363	// Update wp_options
364	foreach ($replacements as $old_url => $new_url) {
365	$result = $wpdb->query(
366	$wpdb->prepare(
367	"UPDATE {$wpdb->options} SET option_value = REPLACE(option_value, %s, %s) WHERE option_value LIKE %s",
368	$old_url,
369	$new_url,
370	'%' . $wpdb->esc_like($old_url) . '%'
371	)
372	);
373	$updated += $result !== false ? $result : 0;
374	}
375
376	// Update wp_termmeta
377	foreach ($replacements as $old_url => $new_url) {
378	$result = $wpdb->query(
379	$wpdb->prepare(
380	"UPDATE {$wpdb->termmeta} SET meta_value = REPLACE(meta_value, %s, %s) WHERE meta_value LIKE %s",
381	$old_url,
382	$new_url,
383	'%' . $wpdb->esc_like($old_url) . '%'
384	)
385	);
386	$updated += $result !== false ? $result : 0;
387	}
388
389	// Elementor data
390	$updated += self::replace_in_elementor_data($replacements);
391	}
392
393	// Restore original attachment metadata
394	$original_metadata = get_post_meta($attachment_id, '_king_img_original_metadata', true);
395	if ($original_metadata) {
396	wp_update_attachment_metadata($attachment_id, $original_metadata);
397	delete_post_meta($attachment_id, '_king_img_original_metadata');
398	}
399
400	// Restore original attached file (so Media Library points back to the original)
401	$original_attached = get_post_meta($attachment_id, '_king_img_original_attached_file', true);
402	if (!empty($original_attached)) {
403	update_post_meta($attachment_id, '_wp_attached_file', $original_attached);
404	delete_post_meta($attachment_id, '_king_img_original_attached_file');
405
406	// Also restore mime type based on the restored file
407	$original_file_abs = get_attached_file($attachment_id);
408	if ($original_file_abs && file_exists($original_file_abs)) {
409	$file_type = wp_check_filetype($original_file_abs);
410	if (!empty($file_type['type'])) {
411	wp_update_post([
412	'ID' => $attachment_id,
413	'post_mime_type' => $file_type['type'],
414	]);
415	}
416	}
417	}
418
419	// Update meta (if it exists)
420	if (is_array($meta)) {
421	$meta['urls_replaced'] = false;
422	$meta['urls_replaced_at'] = null;
423	unset($meta['url_replacements']);
424	self::set_optimization_meta($attachment_id, $meta);
425	}
426
427	return $updated;
428	}
429
430	/**
431	* Get images pending optimization.
432	*/
433	public static function get_pending_images(int $limit = 50, int $offset = 0): array
434	{
435	global $wpdb;
436
437	return $wpdb->get_results(
438	$wpdb->prepare(
439	"SELECT p.ID, p.post_title, p.post_mime_type
440	FROM {$wpdb->posts} p
441	LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = %s
442	WHERE p.post_type = 'attachment'
443	AND p.post_mime_type LIKE 'image/%%'
444	AND (pm.meta_value IS NULL OR pm.meta_value LIKE '%%\"status\";s:7:\"pending\"%%')
445	ORDER BY p.ID DESC
446	LIMIT %d OFFSET %d",
447	self::META_KEY,
448	$limit,
449	$offset
450	),
451	ARRAY_A
452	);
453	}
454
455	/**
456	* Get optimized images.
457	*/
458	public static function get_optimized_images(int $limit = 50, int $offset = 0): array
459	{
460	global $wpdb;
461
462	return $wpdb->get_results(
463	$wpdb->prepare(
464	"SELECT p.ID, p.post_title, p.post_mime_type, pm.meta_value as optimization_meta
465	FROM {$wpdb->posts} p
466	INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = %s
467	WHERE p.post_type = 'attachment'
468	AND p.post_mime_type LIKE 'image/%%'
469	AND pm.meta_value LIKE '%%\"status\";s:9:\"optimized\"%%'
470	ORDER BY p.ID DESC
471	LIMIT %d OFFSET %d",
472	self::META_KEY,
473	$limit,
474	$offset
475	),
476	ARRAY_A
477	);
478	}
479
480	/**
481	* Count images by status.
482	*/
483	public static function count_images_by_status(): array
484	{
485	global $wpdb;
486
487	$total = (int) $wpdb->get_var(
488	"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'attachment' AND post_mime_type LIKE 'image/%'"
489	);
490
491	$optimized = (int) $wpdb->get_var(
492	$wpdb->prepare(
493	"SELECT COUNT(*) FROM {$wpdb->posts} p
494	INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
495	WHERE p.post_type = 'attachment'
496	AND p.post_mime_type LIKE 'image/%%'
497	AND pm.meta_key = %s
498	AND pm.meta_value LIKE '%%\"status\";s:9:\"optimized\"%%'",
499	self::META_KEY
500	)
501	);
502
503	return [
504	'total' => $total,
505	'optimized' => $optimized,
506	'pending' => $total - $optimized,
507	];
508	}
509
510	/**
511	* Get images by format.
512	*/
513	public static function count_images_by_format(): array
514	{
515	global $wpdb;
516
517	$results = $wpdb->get_results(
518	"SELECT post_mime_type, COUNT(*) as count
519	FROM {$wpdb->posts}
520	WHERE post_type = 'attachment' AND post_mime_type LIKE 'image/%'
521	GROUP BY post_mime_type",
522	ARRAY_A
523	);
524
525	$formats = [];
526	foreach ($results as $row) {
527	$format = str_replace('image/', '', $row['post_mime_type']);
528	$formats[$format] = (int) $row['count'];
529	}
530
531	return $formats;
532	}
533
534	/**
535	* Get total bytes saved.
536	*/
537	public static function get_total_savings(): array
538	{
539	global $wpdb;
540
541	$results = $wpdb->get_results(
542	$wpdb->prepare(
543	"SELECT pm.meta_value FROM {$wpdb->postmeta} pm
544	INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
545	WHERE pm.meta_key = %s
546	AND p.post_type = 'attachment'
547	AND pm.meta_value LIKE '%%\"status\";s:9:\"optimized\"%%'",
548	self::META_KEY
549	)
550	);
551
552	$total_original = 0;
553	$total_optimized = 0;
554	$total_saved = 0;
555
556	foreach ($results as $row) {
557	$meta = maybe_unserialize($row->meta_value);
558	if (is_array($meta)) {
559	$total_original += $meta['total_original_bytes'] ?? 0;
560	$total_optimized += $meta['total_optimized_bytes'] ?? 0;
561	$total_saved += $meta['total_saved_bytes'] ?? 0;
562	}
563	}
564
565	return [
566	'total_original' => $total_original,
567	'total_optimized' => $total_optimized,
568	'total_saved' => $total_saved,
569	'savings_percent' => $total_original > 0 ? round(($total_saved / $total_original) * 100, 1) : 0,
570	];
571	}
572	}
573