simple-urls / models / class-model.php

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

1,163 lines 28.9 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 = $method;
587
588	if ( 0 === strpos( $method, 'get_' ) \|\| 0 === strpos( $method, 'set_' ) ) {
589	$derived = $this->get_property_name( $method );
590	if ( $derived ) {
591	$property = $derived;
592	}
593	}
594
595	if ( ! in_array( $property, $this->columns, true ) ) {
596	return;
597	}
598
599	// ? see if there is an extra getter method: get_name()
600	if ( ! method_exists( $this, $method ) ) {
601	// ? if there is no getter, receive all public/protected vars and return the correct one if found
602	return $this->data[ $property ] ?? null;
603	} else {
604	return $this->$method(); // ? call the getter
605	}
606
607	return null;
608	}
609
610	/**
611	* Get a row in DB by a column
612	*
613	* @param string $column Column name in DB.
614	* @param string $value Value in DB.
615	* @param array $select_columns Custom selected the columns. Default to empty array.
616	*
617	* @return array\|object
618	*/
619	public function get_one_by_col( $column, $value, $select_columns = array() ) {
620	$result = null;
621
622	if ( ! $column \|\| ! $value ) {
623	return $result;
624	}
625
626	$select = $this->build_select_columns( $select_columns );
627	$sql = '
628	SELECT ' . $select . '
629	FROM ' . $this->get_table_name() . '
630	WHERE `' . $column . '` = %s
631	';
632	$prepare = self::prepare( $sql, $value ); // phpcs:ignore
633
634	$result = self::get_row( $prepare );
635	$this->map_properties( $result );
636	$this->is_db_loaded = true;
637
638	return $this;
639	}
640
641	/**
642	* Update a row by a column
643	*
644	* @param string $column Column name in DB.
645	* @param string $value Value in DB.
646	*/
647	public function update_by_col( $column, $value ) {
648	$result = self::get_wpdb()->update(
649	$this->get_table_name(),
650	$this->data,
651	array( $column => $value )
652	);
653
654	return $result;
655	}
656
657	/**
658	* Delete rows by a column
659	*
660	* @param string $column Column name in DB.
661	* @param string $value Value in DB.
662	*/
663	public function delete_by_col( $column, $value ) {
664	$result = self::get_wpdb()->delete(
665	$this->get_table_name(),
666	array( $column => $value )
667	);
668
669	return $result;
670	}
671
672	/**
673	* Prepare data
674	*
675	* @param string $sql SQL query.
676	* @param mixed ...$args Further variables to substitute into the query's placeholders if being called with individual arguments.
677	*/
678	public static function prepare( $sql, ...$args ) {
679	return self::get_wpdb()->prepare( $sql, ...$args );
680	}
681
682	/**
683	* Get row
684	* Get row from cache if existed
685	*
686	* @param string $sql Sql query.
687	* @param string $output Type of results.
688	* @param boolean $enable_cache Enable cache.
689	*
690	* @return array\|object\|null\|void Database query result in format specified by $output or null on failure.
691	*/
692	public static function get_row( $sql, $output = 'OBJECT', $enable_cache = false ) {
693	$result = null;
694	$cache_string = md5( trim( (string) $sql ) . $output . __FUNCTION__ );
695
696	if ( $enable_cache ) {
697	$result = Cache_Per_Process::get_instance()->get_cache( $cache_string );
698	}
699
700	if ( ! $result ) {
701	$wpdb = self::get_wpdb();
702	$result = $wpdb->get_row( $sql, $output ); // phpcs:ignore
703	self::log_error( $wpdb->last_error );
704
705	if ( $enable_cache ) {
706	Cache_Per_Process::get_instance()->set_cache( $cache_string, $result );
707	}
708	}
709
710	return $result;
711	}
712
713	/**
714	* Map data into object
715	*
716	* @param object $row A record from DB.
717	*/
718	protected function map_properties( $row ) {
719	if ( ! $row \|\| $this->is_map_properties ) {
720	return;
721	}
722
723	$columns = $this->columns;
724	foreach ( $columns as $column ) {
725	$method = 'set_' . $column;
726	$this->$method( $row->$column ?? null );
727	}
728	$this->is_map_properties = true;
729	return $this;
730	}
731
732	/**
733	* Set value for column
734	*
735	* @param string $column Column name.
736	* @param string $value Value.
737	*/
738	protected function map_property( $column, $value ) {
739	$method = 'set_' . $column;
740	$this->$method( $value ?? null );
741	return $this;
742	}
743
744	/**
745	* Get results
746	* Get results from cache if existed
747	*
748	* @param string $sql Sql query.
749	* @param string $output Type of results.
750	* @param boolean $enable_cache Enable cache.
751	*
752	* @return array\|object\|null Database query results.
753	*/
754	public static function get_results( $sql, $output = 'OBJECT', $enable_cache = false ) {
755	$results = null;
756	$cache_string = md5( trim( (string) $sql ) . $output . __FUNCTION__ );
757
758	if ( $enable_cache ) {
759	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
760	}
761
762	if ( ! $results ) {
763	$wpdb = self::get_wpdb();
764	$results = $wpdb->get_results( $sql, $output ); // phpcs:ignore
765	self::log_error( $wpdb->last_error );
766
767	if ( $enable_cache ) {
768	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
769	}
770	}
771
772	return $results;
773	}
774
775	/**
776	* The replace method replaces a row in a table if it exists
777	* or inserts a new row in a table if the row did not already exist.
778	*
779	* @param string $table The name of the table to replace data in.
780	* @param array $data Data to replace (in column => value pairs).
781	* Both $data columns and $data values should be “raw” (neither should be SQL escaped).
782	*/
783	public static function replace( $table, $data ) {
784	$wpdb = self::get_wpdb();
785	$results = $wpdb->replace( $table, $data ); // phpcs:ignore
786	self::log_error( $wpdb->last_error );
787
788	return $results;
789	}
790
791	/**
792	* Run query
793	*
794	* @param string $sql Sql query.
795	*
796	* @return int\|bool Boolean true for CREATE, ALTER, TRUNCATE and DROP queries.
797	* Number of rows affected/selected for all other queries. Boolean false on error.
798	*/
799	public static function query( $sql ) {
800	$wpdb = self::get_wpdb();
801	$results = $wpdb->query( $sql ); // phpcs:ignore
802	self::log_error( $wpdb->last_error );
803
804	return $results;
805	}
806
807	/**
808	* Get var
809	* Get var from cache if existed
810	*
811	* @param string $sql Sql query.
812	* @param boolean $enable_cache Enable cache.
813	*
814	* @return mixed Database query result (as string), or null on failure.
815	*/
816	public static function get_var( $sql, $enable_cache = false ) {
817	$results = null;
818	$cache_string = md5( trim( (string) $sql ) . __FUNCTION__ );
819
820	if ( $enable_cache ) {
821	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
822	}
823
824	if ( ! $results ) {
825	$wpdb = self::get_wpdb();
826	$results = $wpdb->get_var( $sql ); // phpcs:ignore
827	self::log_error( $wpdb->last_error );
828
829	if ( $enable_cache ) {
830	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
831	}
832	}
833
834	return $results;
835	}
836
837	/**
838	* Get col of the rows
839	* Get col from cache if existed
840	*
841	* @param string $sql Sql query.
842	* @param boolean $enable_cache Is use cache.
843	*/
844	public static function get_col( $sql, $enable_cache = false ) {
845	$results = null;
846	$cache_string = md5( trim( (string) $sql ) . __FUNCTION__ );
847
848	if ( $enable_cache ) {
849	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
850	}
851
852	if ( ! $results ) {
853	global $wpdb;
854
855	$results = $wpdb->get_col( $sql ); // phpcs:ignore
856	self::log_error( $wpdb->last_error );
857
858	if ( $enable_cache ) {
859	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
860	}
861	}
862
863	return $results;
864	}
865
866	/**
867	* Count items by a sql query
868	*
869	* @param string $sql Sql query.
870	*/
871	public static function get_count( $sql ) {
872	$count_sql = '
873	SELECT COUNT(*) AS `count`
874	FROM (' . $sql . ') AS `tbl_count`
875	';
876
877	$result = self::get_var( $count_sql );
878	$result = intval( $result );
879
880	return $result;
881	}
882
883	/**
884	* Print error log message to log file
885	*
886	* @param string $error Error message.
887	*/
888	private static function log_error( $error ) {
889	$log_name = 'sql_errors';
890	if ( ! empty( $error ) ) {
891	if ( Lasso_Helper::is_lasso_tables_does_not_exist_error( $error ) // ? Only recreate Lasso's tables when error relative to Lasso's table.
892	\|\| strpos( $error, 'Illegal mix of collations' ) !== false
893	\|\| strpos( $error, 'Unknown column' ) !== false
894	) {
895	if ( ! self::should_recreate_tables() ) {
896	return;
897	}
898
899	Update_DB::create_tables();
900	}
901
902	// ? Add force write log for lasso_debug, to see what happen when Lasso call query.
903	trigger_error( '[lasso-lite-sql] ' . $error, E_USER_NOTICE ); // phpcs:ignore
904	}
905	}
906
907	/**
908	* Check if number of time to call Lasso_Activator::create_lasso_table() > limit time. So we decide that should call this method or not
909	*
910	* @return bool
911	*/
912	private static function should_recreate_tables() {
913	$lasso_recreate_table_time = Lasso_Helper::get_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, 0 );
914	$lasso_recreate_table_time = intval( $lasso_recreate_table_time );
915
916	// ? Increase number of times we call Update_DB::create_tables().
917	++$lasso_recreate_table_time;
918	if ( $lasso_recreate_table_time > self::LASSO_RECREATE_TABLES_LIMIT_TIMES ) {
919	$now = time();
920	$next_run = Lasso_Helper::get_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, 0 );
921	$next_run = intval( $next_run );
922	if ( 0 === $next_run ) {
923	Lasso_Helper::update_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, strtotime( '+1 hour' ) );
924	}
925
926	if ( $next_run && $now > $next_run ) {
927	Lasso_Helper::update_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, 1 );
928	Lasso_Helper::update_option( Lasso_Setting_Enum::NEXT_TIME_RECREATE_TABLE, 0 );
929	return true;
930	}
931
932	return false;
933	}
934	Lasso_Helper::update_option( Lasso_Setting_Enum::RECREATE_TABLE_TIME, $lasso_recreate_table_time );
935	return true;
936	}
937
938	/**
939	* Get charset collate
940	*/
941	protected function get_charset_collate() {
942	return self::get_wpdb()->get_charset_collate();
943	}
944
945	/**
946	* Create table
947	*
948	* @param string $sql SQL query.
949	* @param string $table Table name.
950	* @param string $reference_charset_table Reference table to get charset.
951	*/
952	protected function modify_table( $sql, $table, $reference_charset_table = null ) {
953	if ( ! function_exists( 'dbDelta' ) ) {
954	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
955	}
956
957	$result = dbDelta( $sql );
958	self::log_error( self::get_wpdb()->last_error );
959
960	$reference_charset_table = $reference_charset_table ? $reference_charset_table : self::get_wp_table_name( 'posts' );
961	$reference_charset_status = $this->get_table_charset( $reference_charset_table, true );
962	$current_table_status = $this->get_table_charset( $table, false );
963
964	if ( $reference_charset_status[1] !== $current_table_status[1] ) {
965	$result = $this->update_table_collation( $table, $reference_charset_status[0], $reference_charset_status[1] );
966	} else {
967	$result = true;
968	}
969
970	$result = $result[ $table ] ?? 'Table already exists.';
971	if ( 'Table already exists.' === $result ) {
972	$check = self::get_row( "CHECK TABLE $table" );
973	$check_msg_txt = $check->Msg_text ?? ''; // phpcs:ignore
974	if ( 'OK' === $check_msg_txt ) {
975	$result = 'The table is okay, it does not need to be repaired.';
976	} else {
977	self::log_error( $check_msg_txt );
978	}
979	}
980
981	return array( $table, $result );
982	}
983
984	/**
985	* Get table charset
986	*
987	* @param string $table Table name.
988	* @param bool $is_posts Is posts table or not. Default to false.
989	*/
990	private function get_table_charset( $table, $is_posts = false ) {
991	// @codeCoverageIgnoreStart
992	$wpdb = self::get_wpdb();
993
994	$tablekey = strtolower( $table );
995	$charset = apply_filters( 'pre_get_table_charset', null, $table );
996	if ( null !== $charset ) {
997	return $charset;
998	}
999
1000	if ( isset( $this->table_charset[ $tablekey ] ) ) {
1001	return array(
1002	$this->table_charset[ $tablekey ],
1003	$this->table_collation[ $tablekey ],
1004	$this->table_collation_default,
1005	$this->get_charset_collate(),
1006	);
1007	}
1008
1009	$charsets_collections = array();
1010	$columns = array();
1011
1012	$table_parts = explode( '.', $table );
1013	$table = '`' . implode( '`.`', $table_parts ) . '`';
1014	$results = self::get_results( "SHOW FULL COLUMNS FROM $table" );
1015	if ( ! $results ) {
1016	return 'wpdb_get_table_charset_failure';
1017	}
1018
1019	foreach ( $results as $column ) {
1020	$columns[ strtolower( $column->Field ) ] = $column; // phpcs:ignore
1021	}
1022
1023	$this->col_meta[ $tablekey ] = $columns;
1024
1025	foreach ( $columns as $column ) {
1026	if ( ! empty( $column->Collation ) ) { // phpcs:ignore
1027	$this->table_collation[ $tablekey ] = $column->Collation; // phpcs:ignore
1028
1029	if ( $is_posts ) {
1030	$this->table_collation_default = $column->Collation; // phpcs:ignore
1031	}
1032
1033	list( $charset ) = explode( '_', $column->Collation ); // phpcs:ignore
1034
1035	// If the current connection can't support utf8mb4 characters, let's only send 3-byte utf8 characters.
1036	if ( 'utf8mb4' === $charset && ! $wpdb->has_cap( 'utf8mb4' ) ) {
1037	$charset = 'utf8';
1038	}
1039
1040	$charsets_collections[ strtolower( $charset ) ] = $column->Collation; // phpcs:ignore
1041	} else {
1042	$this->table_collation[ $tablekey ] = $this->table_collation_default;
1043	}
1044
1045	list( $type ) = explode( '(', $column->Type ); // phpcs:ignore
1046
1047	// A binary/blob means the whole query gets treated like this.
1048	if ( in_array( strtoupper( $type ), array( 'BINARY', 'VARBINARY', 'TINYBLOB', 'MEDIUMBLOB', 'BLOB', 'LONGBLOB' ), true ) ) {
1049	$this->table_charset[ $tablekey ] = 'binary';
1050	return 'binary';
1051	}
1052	}
1053
1054	// utf8mb3 is an alias for utf8.
1055	if ( isset( $charsets_collections['utf8mb3'] ) ) {
1056	$charsets_collections['utf8'] = str_replace( 'utf8mb3', 'utf8', $charsets_collections['utf8mb3'] );
1057	$this->table_collation[ $tablekey ] = $charsets_collections['utf8'];
1058	unset( $charsets_collections['utf8mb3'] );
1059	}
1060
1061	// Check if we have more than one charset in play.
1062	$count = count( $charsets_collections );
1063	if ( 1 === $count ) {
1064	$charset = key( $charsets_collections );
1065	} elseif ( 0 === $count ) {
1066	// No charsets, assume this table can store whatever.
1067	$charset = false;
1068	} else {
1069	// More than one charset. Remove latin1 if present and recalculate.
1070	unset( $charsets_collections['latin1'] );
1071	$count = count( $charsets_collections );
1072	if ( 1 === $count ) {
1073	// Only one charset (besides latin1).
1074	$charset = key( $charsets_collections );
1075
1076	// ? Update suitable collation for this charset
1077	$this->table_collation[ $tablekey ] = $charsets_collections[ $charset ];
1078	} elseif ( 2 === $count && isset( $charsets_collections['utf8'], $charsets_collections['utf8mb4'] ) ) {
1079	// Two charsets, but they're utf8 and utf8mb4, use utf8.
1080	$charset = 'utf8';
1081
1082	// ? Update suitable collation for this charset
1083	$this->table_collation[ $tablekey ] = $charsets_collections['utf8'];
1084	} else {
1085	// Two mixed character sets. ascii.
1086	$charset = 'ascii';
1087
1088	// ? Update suitable collation for this charset
1089	$this->table_collation[ $tablekey ] = 'ascii_general_ci';
1090	}
1091	}
1092
1093	$this->table_charset[ $tablekey ] = $charset;
1094
1095	return array(
1096	$this->table_charset[ $tablekey ],
1097	$this->table_collation[ $tablekey ],
1098	$this->table_collation_default,
1099	$this->get_charset_collate(),
1100	);
1101	// @codeCoverageIgnoreEnd
1102	}
1103
1104	/**
1105	* Update table collation
1106	*
1107	* @param string $table Table name.
1108	* @param string $character Table character. Default to utf8mb4.
1109	* @param string $collate Table collation. Default to utf8mb4_unicode_520_ci.
1110	*/
1111	private function update_table_collation( $table, $character = 'utf8mb4', $collate = 'utf8mb4_unicode_520_ci' ) {
1112	if ( '' === $character \|\| '' === $collate ) {
1113	return false;
1114	}
1115
1116	$sql = '
1117	ALTER TABLE ' . $table . '
1118	CONVERT TO CHARACTER SET ' . $character . '
1119	COLLATE ' . $collate . ';
1120	';
1121
1122	return self::query( $sql );
1123	}
1124
1125	/**
1126	* Build select columns query.
1127	*
1128	* @param array $select_columns Custom selecting columns.
1129	* @return string
1130	*/
1131	private function build_select_columns( $select_columns ) {
1132	if ( empty( $select_columns ) \|\| ! is_array( $select_columns ) ) {
1133	return '*';
1134	}
1135
1136	$valid_columns = array_intersect( $select_columns, $this->columns );
1137	if ( empty( $valid_columns ) ) {
1138	$select = '*';
1139	} else {
1140	$select = implode( ',', $valid_columns );
1141	}
1142
1143	return $select;
1144	}
1145
1146	/**
1147	* Get total records of table.
1148	*
1149	* @param bool $enable_cache Is Enable query cache per process.
1150	* @return int
1151	*/
1152	public function total_count( $enable_cache = false ) {
1153	$sql = '
1154	SELECT COUNT(*) as total
1155	FROM ' . $this->get_table_name() . '
1156	';
1157
1158	$result = self::get_var( $sql, $enable_cache );
1159
1160	return empty( $result ) ? 0 : intval( $result );
1161	}
1162	}
1163