simple-urls / models / class-model.php

class-model.php in Lasso Lite – Affiliate Link Manager & Product Displays 149, at models/class-model.php

1,160 lines 28.8 KB

Raw Download Zip

1	<?php
2	/**
3	* Models
4	*
5	* @package Models
6	*/
7
8	namespace LassoLite\Models;
9
10	use LassoLite\Classes\Cache_Per_Process;
11	use LassoLite\Classes\Helper as Lasso_Helper;
12	use LassoLite\Classes\Setting_Enum as Lasso_Setting_Enum;
13	use LassoLite\Classes\Update_DB;
14
15	/**
16	* Model
17	*
18	* HOW TO USE?
19	* 1. Create a new class object (child class): $model = new Model_Child_Class();
20	* 2. Insert:
21	* a. Set data for columns: $model->set_[column_name]($value);
22	* b. Insert data: $model->insert();
23	* 3. Update:
24	* a. Update a model object after using get_one($id):
25	* i. Just update what columns you want: $model->set_[column_name]($value);
26	* ii. Update data: $model->update();
27	* b. Update a non-model object:
28	* i. Just update what columns you want: $model->set_[column_name]($value);
29	* ii. Update data: $model->update($id);
30	* 4. Delete:
31	* a. Delete a model object after using get_one($id): $model->delete();
32	* b. Delete a non-model object: $model->delete($id);
33	*
34	* COMMON FUNCTIONS
35	* 1. Get table name: $model->get_table_name();
36	* 2. Get a record: $model->get_one($id);
37	* 3. Get all records: $model->get_all($limit, $page);
38	* 4. Get prefix of the table in WP: Model::get_prefix();
39	* 5. Get table name of WP (with prefix): Model::get_wp_table_name('posts');
40	* 6. Create the table: $model->create_table();
41	* 7. Add default data: $model->add_default_data();
42	*
43	* WPDB CLASS: https://developer.wordpress.org/reference/classes/wpdb/
44	* 1. Get a record: Model::get_row($sql, $output, $enable_cache);
45	* 2. Get multiple records: Model::get_results($sql, $output, $enable_cache);
46	* 3. Get a var: Model::get_var($sql, $enable_cache);
47	* 4. Get a column: Model::get_col($sql, $enable_cache);
48	* 5. Run a general query: Model::query($sql);
49	* 5. Replace a row (update or create if it doesn't exist): Model::replace($table, $data, $format);
50	*/
51	abstract class Model {
52	const LASSO_RECREATE_TABLES_LIMIT_TIMES = 3;
53
54	/**
55	* Table name
56	*
57	* @var string
58	*/
59	protected $table;
60
61	/**
62	* Columns of the table
63	*
64	* @var array
65	*/
66	protected $columns;
67
68	/**
69	* Primary key of the table
70	*
71	* @var string
72	*/
73	protected $primary_key;
74
75	/**
76	* Data from DB
77	*
78	* @var object $data
79	*/
80	protected $data;
81
82	/**
83	* Default
84	*
85	* @var mixed $default_data
86	*/
87	protected $default_data;
88
89	/**
90	* Data from DB
91	*
92	* @var bool $is_db_loaded
93	*/
94	protected $is_db_loaded = false;
95
96	/**
97	* Table charset
98	*
99	* @var array $table_charset
100	*/
101	protected $table_charset = array();
102
103	/**
104	* Table collation
105	*
106	* @var array $table_collation
107	*/
108	protected $table_collation = array();
109
110	/**
111	* Table collation default
112	*
113	* @var array $table_collation_default
114	*/
115	protected $table_collation_default = array();
116
117	/**
118	* Column meta
119	*
120	* @var array $col_meta
121	*/
122	protected $col_meta = array();
123
124	/**
125	* Create table
126	*/
127	abstract public function create_table();
128
129	/**
130	* Use to check object is mapped properties
131	*
132	* @var bool $is_map_properties
133	*/
134	private $is_map_properties = false;
135
136	/**
137	* Model constructor.
138	*
139	* @param object $object An object.
140	*/
141	public function __construct( $object = null ) {
142	if ( ! is_null( $object ) && ! $this->is_map_properties ) {
143	$this->map_properties( $object );
144	}
145	}
146
147	/**
148	* Get wpdb
149	*
150	* @return wpdb WP wpdb class.
151	*/
152	public static function get_wpdb() {
153	global $wpdb;
154
155	return $wpdb;
156	}
157
158	/**
159	* Get WP prefix
160	*
161	* @return string WP prefix of table.
162	*/
163	public static function get_db_name() {
164	return self::get_wpdb()->dbname;
165	}
166
167	/**
168	* Get WP prefix
169	*
170	* @return string WP prefix of table.
171	*/
172	public static function get_prefix() {
173	return self::get_wpdb()->prefix;
174	}
175
176	/**
177	* Get Lasso table name
178	*
179	* @return string Get current table name in WP.
180	*/
181	public function get_table_name() {
182	return self::get_prefix() . $this->table;
183	}
184
185	/**
186	* Get WP table name
187	*
188	* @param string $table Table name in DB.
189	*
190	* @return string Table name in WP.
191	*/
192	public static function get_wp_table_name( $table ) {
193	return self::get_prefix() . $table;
194	}
195
196	/**
197	* Get all columns of the table
198	*/
199	public function get_all_columns() {
200	return $this->columns;
201	}
202
203	/**
204	* Get default data
205	*/
206	public function get_default_data() {
207	return $this->default_data;
208	}
209
210	/**
211	* Get a row in DB
212	*
213	* @param int $id Id in DB. Default to null.
214	* @param array $select_columns Custom selecting the columns. Default to empty array.
215	*
216	* @return array\|object
217	*/
218	public function get_one( $id = null, $select_columns = array() ) {
219	$method = 'get_' . $this->primary_key;
220	$id = $id ? $id : $this->$method();
221
222	return $this->get_one_by_col( $this->primary_key, $id, $select_columns );
223	}
224
225	/**
226	* Get all rows in DB
227	*
228	* @param int $limit Number of rows are returned, 0 is no limit. Default to 10.
229	* @param int $page Page number. Default to 1.
230	* @param array $select_columns Custom selecting the columns. Default to empty array.
231	*
232	* @return array\|object
233	*/
234	public function get_all( $limit = 10, $page = 1, $select_columns = array() ) {
235	$select = $this->build_select_columns( $select_columns );
236
237	$sql = '
238	SELECT ' . $select . '
239	FROM ' . $this->get_table_name() . '
240	';
241
242	if ( $limit > 0 ) {
243	$index = ( $page - 1 ) * $limit;
244
245	$sql .= ' LIMIT %d OFFSET %d';
246	$sql = self::prepare( $sql, $limit, $index );
247	}
248
249	return $this->get_results( $sql );
250	}
251
252	/**
253	* Insert a row in DB
254	*/
255	public function insert() {
256	$result = self::get_wpdb()->insert(
257	$this->get_table_name(),
258	$this->data
259	);
260	if ( $result ) {
261	if ( in_array( 'id', $this->columns, true ) ) {
262	$this->map_property( 'id', self::get_wpdb()->insert_id );
263	}
264	}
265	return $result;
266	}
267
268	/**
269	* Update a row in DB
270	*
271	* @param int\|string $id Id in the table. Default to null.
272	*/
273	public function update( $id = null ) {
274	$method = 'get_' . $this->primary_key;
275	$id = $id ? $id : $this->$method();
276
277	if ( ! $id ) {
278	return false;
279	}
280
281	return $this->update_by_col( $this->primary_key, $id );
282	}
283
284	/**
285	* Delete a row in DB
286	*
287	* @param int\|string $id Id in the table. Default to null.
288	*/
289	public function delete( $id = null ) {
290	$method = 'get_' . $this->primary_key;
291	$id = $id ? $id : $this->$method();
292
293	if ( ! $id ) {
294	return false;
295	}
296
297	return $this->delete_by_col( $this->primary_key, $id );
298	}
299
300	/**
301	* Bulk upsert (insert/update) records into a table using WPDB. All rows must contain the same keys.
302	* Returns number of affected (inserted) rows.
303	*
304	* @param array $rows Table data.
305	*/
306	public function bulk_upsert( $rows ) {
307	$table = $this->get_table_name();
308
309	$ids = array();
310	if ( 0 === count( $rows ) ) {
311	return array( false, $ids );
312	}
313
314	// ? Extract column list from first row of data
315	$columns = array_keys( Lasso_Helper::convert_stdclass_to_array( $rows[0] ) );
316	asort( $columns );
317	$column_list = '`' . implode( '`, `', $columns ) . '`';
318
319	// ? Start building SQL, initialise data and placeholder arrays
320	// ? $sql = "INSERT INTO `$table` ($column_list) VALUES\n";
321	$sql = "REPLACE INTO `$table` ($column_list) VALUES\n"; // ? upsert in mysql
322	$placeholders = array();
323	$data = array();
324
325	// ? Build placeholders for each row, and add values to data array
326	foreach ( $rows as $row ) {
327	$row = Lasso_Helper::convert_stdclass_to_array( $row );
328	ksort( $row );
329	$row_placeholders = array();
330	$ids[] = intval( $row['id'] );
331
332	foreach ( $row as $value ) {
333	$data[] = $value;
334	$row_placeholders[] = is_numeric( $value ) ? '%d' : '%s';
335	}
336
337	$placeholders[] = '(' . implode( ', ', $row_placeholders ) . ')';
338	}
339
340	// ? Stitch all rows together
341	$sql .= implode( ",\n", $placeholders );
342	$prepare = self::prepare( $sql, $data ); // phpcs:ignore
343
344	// ? Run the query. Returns number of affected rows.
345	return array( self::query( $prepare ), $ids ); // phpcs:ignore
346	}
347
348	/**
349	* Check whether a column exists or not
350	*
351	* @param string $table Table name.
352	* @param string $column Column name.
353	*/
354	public static function column_exists( $table, $column ) {
355	$sql = "
356	SELECT *
357	FROM information_schema.columns
358	WHERE
359	table_schema = '" . self::get_db_name() . "'
360	AND table_name = '" . $table . "'
361	AND column_name = '" . $column . "'
362	LIMIT 1
363	";
364
365	$result = self::get_results( $sql );
366	$result = isset( $result[0] ) ? true : false;
367
368	return $result;
369	}
370
371	/**
372	* Check whether columns exist or not
373	*
374	* @param array $columns Column name.
375	*/
376	public function are_columns_created( $columns ) {
377	if ( ! is_array( $columns ) \|\| empty( $columns ) ) {
378	return false;
379	}
380
381	$expected_count = count( $columns );
382	$columns = "'" . implode( "', '", $columns ) . "'";
383
384	// @codingStandardsIgnoreStart
385	$sql = "
386	SELECT count(COLUMN_NAME) as total
387	FROM information_schema.COLUMNS
388	WHERE TABLE_SCHEMA = %s
389	AND TABLE_NAME = %s
390	AND COLUMN_NAME IN ($columns)
391	";
392	$prepare = self::prepare( $sql, self::get_db_name(), $this->get_table_name() );
393	// @codingStandardsIgnoreEnd
394	$result = self::get_var( $prepare );
395	$result = intval( $result );
396
397	return $result === $expected_count;
398	}
399
400	/**
401	* Check whether a tahble exists or not
402	*
403	* @param array $table Table name.
404	*/
405	public static function table_exists( $table ) {
406	// @codingStandardsIgnoreStart
407	$prepare = self::prepare(
408	"
409	SELECT count(*)
410	FROM information_schema.TABLES
411	WHERE TABLE_SCHEMA = DATABASE()
412	AND TABLE_NAME = %s
413	",
414	$table
415	);
416	// @codingStandardsIgnoreEnd
417	$result = self::get_var( $prepare );
418
419	return intval( $result ) === 1;
420	}
421
422	/**
423	* Check whether a tahble exists or not
424	*/
425	public function is_table_created() {
426	// @codingStandardsIgnoreStart
427	$prepare = self::prepare(
428	"
429	SELECT count(*)
430	FROM information_schema.TABLES
431	WHERE TABLE_SCHEMA = DATABASE()
432	AND TABLE_NAME = %s
433	",
434	$this->get_table_name()
435	);
436	// @codingStandardsIgnoreEnd
437	$result = self::get_var( $prepare );
438
439	return intval( $result ) === 1;
440	}
441
442	/**
443	* Drop index in the table
444	*
445	* @param string $index_name Index name.
446	*
447	* @return bool
448	*/
449	public function is_index_created( $index_name ) {
450	$sql = '
451	SHOW INDEX
452	FROM ' . $this->get_table_name() . '
453	WHERE KEY_NAME = %s
454	';
455	$prepare = self::prepare( $sql, $index_name );
456	$index = self::query( $prepare );
457
458	return $index > 0;
459	}
460
461	/**
462	* Drop columns in the table
463	*
464	* @param array $columns Columns list.
465	*/
466	public function drop_columns( $columns ) {
467	if ( ! is_array( $columns ) \|\| empty( $columns ) ) {
468	return false;
469	}
470
471	foreach ( $columns as $column ) {
472	if ( $this->are_columns_created( array( $column ) ) ) {
473	$query = '
474	ALTER TABLE ' . $this->get_table_name() . '
475	DROP COLUMN `' . $column . '`
476	';
477	self::query( $query );
478	}
479	}
480	}
481
482	/**
483	* Drop Index
484	*
485	* @param string $index_name Index name.
486	*/
487	public function drop_index( $index_name ) {
488	$index_exists = $this->is_index_created( $index_name );
489
490	if ( ! $index_exists ) {
491	return false;
492	}
493
494	$sql = '
495	ALTER TABLE ' . $this->get_table_name() . '
496	DROP INDEX `' . $index_name . '`
497	';
498
499	return self::query( $sql );
500	}
501
502	/**
503	* Drop table
504	*/
505	public function drop_table() {
506	$sql = 'DROP TABLE IF EXISTS ' . $this->get_table_name();
507
508	return self::query( $sql );
509	}
510
511	/**
512	* Format keyword for searching
513	*
514	* @param string $keyword Keyword.
515	*/
516	public static function esc_like( $keyword ) {
517	return self::get_wpdb()->esc_like( $keyword );
518	}
519
520	/**
521	* Get property name by method
522	*
523	* @param string $method Method.
524	*/
525	private function get_property_name( $method ) {
526	return substr_replace( $method, '', 0, 4 );
527	}
528
529	/**
530	* Call a method in this class
531	*
532	* @param string $method Method name.
533	* @param array $args Arguments.
534	*/
535	public function __call( $method, $args ) {
536	$prefix = substr_replace( $method, '', 4 );
537
538	switch ( $prefix ) {
539	case 'get_':
540	return $this->$method;
541
542	case 'set_':
543	$this->$method = $args[0] ?? null;
544	break;
545	}
546
547	return null;
548	}
549
550	/**
551	* Set value for a property
552	*
553	* @param string $name Function name (set_property_name).
554	* @param mix $value Property value.
555	*/
556	public function __set( $name, $value ) {
557	$method = strtolower( $name );
558	$property = $this->get_property_name( $method );
559
560	if ( ! $property ) {
561	$property = $method;
562	}
563
564	if ( ! in_array( $property, $this->columns, true ) ) {
565	return;
566	}
567
568	// ? see if there exists a extra setter method: setName()
569	if ( ! method_exists( $this, $method ) ) {
570	// ? if there is no setter, receive all public/protected vars and set the correct one if found
571	$this->data[ $property ] = $value;
572	} else {
573	$this->$method( $value ); // ? call the setter with the value
574	}
575	}
576
577	/**
578	* Get value for a property
579	*
580	* @param string $name Function name (get_property_name).
581	*
582	* @return mixed Property value.
583	*/
584	public function __get( $name ) {
585	$method = strtolower( $name );
586	$property = $this->get_property_name( $method );
587
588	if ( ! $property ) {
589	$property = $method;
590	}
591
592	if ( ! in_array( $property, $this->columns, true ) ) {
593	return;
594	}
595
596	// ? see if there is an extra getter method: get_name()
597	if ( ! method_exists( $this, $method ) ) {
598	// ? if there is no getter, receive all public/protected vars and return the correct one if found
599	return $this->data[ $property ] ?? null;
600	} else {
601	return $this->$method(); // ? call the getter
602	}
603
604	return null;
605	}
606
607	/**
608	* Get a row in DB by a column
609	*
610	* @param string $column Column name in DB.
611	* @param string $value Value in DB.
612	* @param array $select_columns Custom selected the columns. Default to empty array.
613	*
614	* @return array\|object
615	*/
616	public function get_one_by_col( $column, $value, $select_columns = array() ) {
617	$result = null;
618
619	if ( ! $column \|\| ! $value ) {
620	return $result;
621	}
622
623	$select = $this->build_select_columns( $select_columns );
624	$sql = '
625	SELECT ' . $select . '
626	FROM ' . $this->get_table_name() . '
627	WHERE `' . $column . '` = %s
628	';
629	$prepare = self::prepare( $sql, $value ); // phpcs:ignore
630
631	$result = self::get_row( $prepare );
632	$this->map_properties( $result );
633	$this->is_db_loaded = true;
634
635	return $this;
636	}
637
638	/**
639	* Update a row by a column
640	*
641	* @param string $column Column name in DB.
642	* @param string $value Value in DB.
643	*/
644	public function update_by_col( $column, $value ) {
645	$result = self::get_wpdb()->update(
646	$this->get_table_name(),
647	$this->data,
648	array( $column => $value )
649	);
650
651	return $result;
652	}
653
654	/**
655	* Delete rows by a column
656	*
657	* @param string $column Column name in DB.
658	* @param string $value Value in DB.
659	*/
660	public function delete_by_col( $column, $value ) {
661	$result = self::get_wpdb()->delete(
662	$this->get_table_name(),
663	array( $column => $value )
664	);
665
666	return $result;
667	}
668
669	/**
670	* Prepare data
671	*
672	* @param string $sql SQL query.
673	* @param mixed ...$args Further variables to substitute into the query's placeholders if being called with individual arguments.
674	*/
675	public static function prepare( $sql, ...$args ) {
676	return self::get_wpdb()->prepare( $sql, ...$args );
677	}
678
679	/**
680	* Get row
681	* Get row from cache if existed
682	*
683	* @param string $sql Sql query.
684	* @param string $output Type of results.
685	* @param boolean $enable_cache Enable cache.
686	*
687	* @return array\|object\|null\|void Database query result in format specified by $output or null on failure.
688	*/
689	public static function get_row( $sql, $output = 'OBJECT', $enable_cache = false ) {
690	$result = null;
691	$cache_string = md5( trim( (string) $sql ) . $output . __FUNCTION__ );
692
693	if ( $enable_cache ) {
694	$result = Cache_Per_Process::get_instance()->get_cache( $cache_string );
695	}
696
697	if ( ! $result ) {
698	$wpdb = self::get_wpdb();
699	$result = $wpdb->get_row( $sql, $output ); // phpcs:ignore
700	self::log_error( $wpdb->last_error );
701
702	if ( $enable_cache ) {
703	Cache_Per_Process::get_instance()->set_cache( $cache_string, $result );
704	}
705	}
706
707	return $result;
708	}
709
710	/**
711	* Map data into object
712	*
713	* @param object $row A record from DB.
714	*/
715	protected function map_properties( $row ) {
716	if ( ! $row \|\| $this->is_map_properties ) {
717	return;
718	}
719
720	$columns = $this->columns;
721	foreach ( $columns as $column ) {
722	$method = 'set_' . $column;
723	$this->$method( $row->$column ?? null );
724	}
725	$this->is_map_properties = true;
726	return $this;
727	}
728
729	/**
730	* Set value for column
731	*
732	* @param string $column Column name.
733	* @param string $value Value.
734	*/
735	protected function map_property( $column, $value ) {
736	$method = 'set_' . $column;
737	$this->$method( $value ?? null );
738	return $this;
739	}
740
741	/**
742	* Get results
743	* Get results from cache if existed
744	*
745	* @param string $sql Sql query.
746	* @param string $output Type of results.
747	* @param boolean $enable_cache Enable cache.
748	*
749	* @return array\|object\|null Database query results.
750	*/
751	public static function get_results( $sql, $output = 'OBJECT', $enable_cache = false ) {
752	$results = null;
753	$cache_string = md5( trim( (string) $sql ) . $output . __FUNCTION__ );
754
755	if ( $enable_cache ) {
756	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
757	}
758
759	if ( ! $results ) {
760	$wpdb = self::get_wpdb();
761	$results = $wpdb->get_results( $sql, $output ); // phpcs:ignore
762	self::log_error( $wpdb->last_error );
763
764	if ( $enable_cache ) {
765	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
766	}
767	}
768
769	return $results;
770	}
771
772	/**
773	* The replace method replaces a row in a table if it exists
774	* or inserts a new row in a table if the row did not already exist.
775	*
776	* @param string $table The name of the table to replace data in.
777	* @param array $data Data to replace (in column => value pairs).
778	* Both $data columns and $data values should be “raw” (neither should be SQL escaped).
779	*/
780	public static function replace( $table, $data ) {
781	$wpdb = self::get_wpdb();
782	$results = $wpdb->replace( $table, $data ); // phpcs:ignore
783	self::log_error( $wpdb->last_error );
784
785	return $results;
786	}
787
788	/**
789	* Run query
790	*
791	* @param string $sql Sql query.
792	*
793	* @return int\|bool Boolean true for CREATE, ALTER, TRUNCATE and DROP queries.
794	* Number of rows affected/selected for all other queries. Boolean false on error.
795	*/
796	public static function query( $sql ) {
797	$wpdb = self::get_wpdb();
798	$results = $wpdb->query( $sql ); // phpcs:ignore
799	self::log_error( $wpdb->last_error );
800
801	return $results;
802	}
803
804	/**
805	* Get var
806	* Get var from cache if existed
807	*
808	* @param string $sql Sql query.
809	* @param boolean $enable_cache Enable cache.
810	*
811	* @return mixed Database query result (as string), or null on failure.
812	*/
813	public static function get_var( $sql, $enable_cache = false ) {
814	$results = null;
815	$cache_string = md5( trim( (string) $sql ) . __FUNCTION__ );
816
817	if ( $enable_cache ) {
818	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
819	}
820
821	if ( ! $results ) {
822	$wpdb = self::get_wpdb();
823	$results = $wpdb->get_var( $sql ); // phpcs:ignore
824	self::log_error( $wpdb->last_error );
825
826	if ( $enable_cache ) {
827	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
828	}
829	}
830
831	return $results;
832	}
833
834	/**
835	* Get col of the rows
836	* Get col from cache if existed
837	*
838	* @param string $sql Sql query.
839	* @param boolean $enable_cache Is use cache.
840	*/
841	public static function get_col( $sql, $enable_cache = false ) {
842	$results = null;
843	$cache_string = md5( trim( (string) $sql ) . __FUNCTION__ );
844
845	if ( $enable_cache ) {
846	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
847	}
848
849	if ( ! $results ) {
850	global $wpdb;
851
852	$results = $wpdb->get_col( $sql ); // phpcs:ignore
853	self::log_error( $wpdb->last_error );
854
855	if ( $enable_cache ) {
856	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
857	}
858	}
859
860	return $results;
861	}
862
863	/**
864	* Count items by a sql query
865	*
866	* @param string $sql Sql query.
867	*/
868	public static function get_count( $sql ) {
869	$count_sql = '
870	SELECT COUNT(*) AS `count`
871	FROM (' . $sql . ') AS `tbl_count`
872	';
873
874	$result = self::get_var( $count_sql );
875	$result = intval( $result );
876
877	return $result;
878	}
879
880	/**
881	* Print error log message to log file
882	*
883	* @param string $error Error message.
884	*/
885	private static function log_error( $error ) {
886	$log_name = 'sql_errors';
887	if ( ! empty( $error ) ) {
888	if ( Lasso_Helper::is_lasso_tables_does_not_exist_error( $error ) // ? Only recreate Lasso's tables when error relative to Lasso's table.
889	\|\| strpos( $error, 'Illegal mix of collations' ) !== false
890	\|\| strpos( $error, 'Unknown column' ) !== false
891	) {
892	if ( ! self::should_recreate_tables() ) {
893	return;
894	}
895
896	Update_DB::create_tables();
897	}
898
899	// ? Add force write log for lasso_debug, to see what happen when Lasso call query.
900	trigger_error( $error, E_USER_NOTICE ); // phpcs:ignore
901	}
902	}
903
904	/**
905	* Check if number of time to call Lasso_Activator::create_lasso_table() > limit time. So we decide that should call this method or not
906	*
907	* @return bool
908	*/
909	private static function should_recreate_tables() {
910	$lasso_recreate_table_time = Lasso_Helper::get_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, 0 );
911	$lasso_recreate_table_time = intval( $lasso_recreate_table_time );
912
913	// ? Increase number of times we call Update_DB::create_tables().
914	++$lasso_recreate_table_time;
915	if ( $lasso_recreate_table_time > self::LASSO_RECREATE_TABLES_LIMIT_TIMES ) {
916	$now = time();
917	$next_run = Lasso_Helper::get_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, 0 );
918	$next_run = intval( $next_run );
919	if ( 0 === $next_run ) {
920	Lasso_Helper::update_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, strtotime( '+1 hour' ) );
921	}
922
923	if ( $next_run && $now > $next_run ) {
924	Lasso_Helper::update_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, 1 );
925	Lasso_Helper::update_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, 0 );
926	return true;
927	}
928
929	return false;
930	}
931	Lasso_Helper::update_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, $lasso_recreate_table_time );
932	return true;
933	}
934
935	/**
936	* Get charset collate
937	*/
938	protected function get_charset_collate() {
939	return self::get_wpdb()->get_charset_collate();
940	}
941
942	/**
943	* Create table
944	*
945	* @param string $sql SQL query.
946	* @param string $table Table name.
947	* @param string $reference_charset_table Reference table to get charset.
948	*/
949	protected function modify_table( $sql, $table, $reference_charset_table = null ) {
950	if ( ! function_exists( 'dbDelta' ) ) {
951	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
952	}
953
954	$result = dbDelta( $sql );
955	self::log_error( self::get_wpdb()->last_error );
956
957	$reference_charset_table = $reference_charset_table ? $reference_charset_table : self::get_wp_table_name( 'posts' );
958	$reference_charset_status = $this->get_table_charset( $reference_charset_table, true );
959	$current_table_status = $this->get_table_charset( $table, false );
960
961	if ( ! $reference_charset_status[1] !== $current_table_status[1] ) {
962	$result = $this->update_table_collation( $table, $reference_charset_status[0], $reference_charset_status[1] );
963	} else {
964	$result = true;
965	}
966
967	$result = $result[ $table ] ?? 'Table already exists.';
968	if ( 'Table already exists.' === $result ) {
969	$check = self::get_row( "CHECK TABLE $table" );
970	$check_msg_txt = $check->Msg_text ?? ''; // phpcs:ignore
971	if ( 'OK' === $check_msg_txt ) {
972	$result = 'The table is okay, it does not need to be repaired.';
973	} else {
974	self::log_error( $check_msg_txt );
975	}
976	}
977
978	return array( $table, $result );
979	}
980
981	/**
982	* Get table charset
983	*
984	* @param string $table Table name.
985	* @param bool $is_posts Is posts table or not. Default to false.
986	*/
987	private function get_table_charset( $table, $is_posts = false ) {
988	// @codeCoverageIgnoreStart
989	$wpdb = self::get_wpdb();
990
991	$tablekey = strtolower( $table );
992	$charset = apply_filters( 'pre_get_table_charset', null, $table );
993	if ( null !== $charset ) {
994	return $charset;
995	}
996
997	if ( isset( $this->table_charset[ $tablekey ] ) ) {
998	return array(
999	$this->table_charset[ $tablekey ],
1000	$this->table_collation[ $tablekey ],
1001	$this->table_collation_default,
1002	$this->get_charset_collate(),
1003	);
1004	}
1005
1006	$charsets_collections = array();
1007	$columns = array();
1008
1009	$table_parts = explode( '.', $table );
1010	$table = '`' . implode( '`.`', $table_parts ) . '`';
1011	$results = self::get_results( "SHOW FULL COLUMNS FROM $table" );
1012	if ( ! $results ) {
1013	return 'wpdb_get_table_charset_failure';
1014	}
1015
1016	foreach ( $results as $column ) {
1017	$columns[ strtolower( $column->Field ) ] = $column; // phpcs:ignore
1018	}
1019
1020	$this->col_meta[ $tablekey ] = $columns;
1021
1022	foreach ( $columns as $column ) {
1023	if ( ! empty( $column->Collation ) ) { // phpcs:ignore
1024	$this->table_collation[ $tablekey ] = $column->Collation; // phpcs:ignore
1025
1026	if ( $is_posts ) {
1027	$this->table_collation_default = $column->Collation; // phpcs:ignore
1028	}
1029
1030	list( $charset ) = explode( '_', $column->Collation ); // phpcs:ignore
1031
1032	// If the current connection can't support utf8mb4 characters, let's only send 3-byte utf8 characters.
1033	if ( 'utf8mb4' === $charset && ! $wpdb->has_cap( 'utf8mb4' ) ) {
1034	$charset = 'utf8';
1035	}
1036
1037	$charsets_collections[ strtolower( $charset ) ] = $column->Collation; // phpcs:ignore
1038	} else {
1039	$this->table_collation[ $tablekey ] = $this->table_collation_default;
1040	}
1041
1042	list( $type ) = explode( '(', $column->Type ); // phpcs:ignore
1043
1044	// A binary/blob means the whole query gets treated like this.
1045	if ( in_array( strtoupper( $type ), array( 'BINARY', 'VARBINARY', 'TINYBLOB', 'MEDIUMBLOB', 'BLOB', 'LONGBLOB' ), true ) ) {
1046	$this->table_charset[ $tablekey ] = 'binary';
1047	return 'binary';
1048	}
1049	}
1050
1051	// utf8mb3 is an alias for utf8.
1052	if ( isset( $charsets_collections['utf8mb3'] ) ) {
1053	$charsets_collections['utf8'] = str_replace( 'utf8mb3', 'utf8', $charsets_collections['utf8mb3'] );
1054	$this->table_collation[ $tablekey ] = $charsets_collections['utf8'];
1055	unset( $charsets_collections['utf8mb3'] );
1056	}
1057
1058	// Check if we have more than one charset in play.
1059	$count = count( $charsets_collections );
1060	if ( 1 === $count ) {
1061	$charset = key( $charsets_collections );
1062	} elseif ( 0 === $count ) {
1063	// No charsets, assume this table can store whatever.
1064	$charset = false;
1065	} else {
1066	// More than one charset. Remove latin1 if present and recalculate.
1067	unset( $charsets_collections['latin1'] );
1068	$count = count( $charsets_collections );
1069	if ( 1 === $count ) {
1070	// Only one charset (besides latin1).
1071	$charset = key( $charsets_collections );
1072
1073	// ? Update suitable collation for this charset
1074	$this->table_collation[ $tablekey ] = $charsets_collections[ $charset ];
1075	} elseif ( 2 === $count && isset( $charsets_collections['utf8'], $charsets_collections['utf8mb4'] ) ) {
1076	// Two charsets, but they're utf8 and utf8mb4, use utf8.
1077	$charset = 'utf8';
1078
1079	// ? Update suitable collation for this charset
1080	$this->table_collation[ $tablekey ] = $charsets_collections['utf8'];
1081	} else {
1082	// Two mixed character sets. ascii.
1083	$charset = 'ascii';
1084
1085	// ? Update suitable collation for this charset
1086	$this->table_collation[ $tablekey ] = 'ascii_general_ci';
1087	}
1088	}
1089
1090	$this->table_charset[ $tablekey ] = $charset;
1091
1092	return array(
1093	$this->table_charset[ $tablekey ],
1094	$this->table_collation[ $tablekey ],
1095	$this->table_collation_default,
1096	$this->get_charset_collate(),
1097	);
1098	// @codeCoverageIgnoreEnd
1099	}
1100
1101	/**
1102	* Update table collation
1103	*
1104	* @param string $table Table name.
1105	* @param string $character Table character. Default to utf8mb4.
1106	* @param string $collate Table collation. Default to utf8mb4_unicode_520_ci.
1107	*/
1108	private function update_table_collation( $table, $character = 'utf8mb4', $collate = 'utf8mb4_unicode_520_ci' ) {
1109	if ( '' === $character \|\| '' === $collate ) {
1110	return false;
1111	}
1112
1113	$sql = '
1114	ALTER TABLE ' . $table . '
1115	CONVERT TO CHARACTER SET ' . $character . '
1116	COLLATE ' . $collate . ';
1117	';
1118
1119	return self::query( $sql );
1120	}
1121
1122	/**
1123	* Build select columns query.
1124	*
1125	* @param array $select_columns Custom selecting columns.
1126	* @return string
1127	*/
1128	private function build_select_columns( $select_columns ) {
1129	if ( empty( $select_columns ) \|\| ! is_array( $select_columns ) ) {
1130	return '*';
1131	}
1132
1133	$valid_columns = array_intersect( $select_columns, $this->columns );
1134	if ( empty( $valid_columns ) ) {
1135	$select = '*';
1136	} else {
1137	$select = implode( ',', $valid_columns );
1138	}
1139
1140	return $select;
1141	}
1142
1143	/**
1144	* Get total records of table.
1145	*
1146	* @param bool $enable_cache Is Enable query cache per process.
1147	* @return int
1148	*/
1149	public function total_count( $enable_cache = false ) {
1150	$sql = '
1151	SELECT COUNT(*) as total
1152	FROM ' . $this->get_table_name() . '
1153	';
1154
1155	$result = self::get_var( $sql, $enable_cache );
1156
1157	return empty( $result ) ? 0 : intval( $result );
1158	}
1159	}
1160