learnpress / inc / databases / class-lp-db.php

class-lp-db.php in LearnPress – WordPress LMS Plugin for Create and Sell Online Courses 4.1.7, at inc/databases/class-lp-db.php

779 lines 17.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Class LP_Database
4	*
5	* @author tungnx
6	* @since 3.2.7.5
7	* @version 2.0.1
8	*/
9	defined( 'ABSPATH' ) \|\| exit();
10
11	//require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
12
13	class LP_Database {
14	private static $_instance;
15	public $wpdb, $tb_users;
16	public $tb_lp_user_items, $tb_lp_user_itemmeta;
17	public $tb_posts, $tb_postmeta, $tb_options;
18	public $tb_terms, $tb_term_relationships;
19	public $tb_lp_order_items, $tb_lp_order_itemmeta;
20	public $tb_lp_sections, $tb_lp_section_items;
21	public $tb_lp_quiz_questions;
22	public $tb_lp_user_item_results;
23	public $tb_lp_question_answers;
24	public $tb_lp_question_answermeta;
25	public $tb_lp_upgrade_db;
26	public $tb_lp_sessions;
27	private $collate = '';
28	public $max_index_length = '191';
29
30	protected function __construct() {
31	/**
32	* @var wpdb $wpdb
33	*/
34	global $wpdb;
35	$prefix = $wpdb->prefix;
36
37	$this->wpdb = $wpdb;
38	$this->tb_users = $wpdb->users;
39	$this->tb_posts = $wpdb->posts;
40	$this->tb_postmeta = $wpdb->postmeta;
41	$this->tb_options = $wpdb->options;
42	$this->tb_terms = $wpdb->terms;
43	$this->tb_term_relationships = $wpdb->term_relationships;
44	$this->tb_lp_user_items = $prefix . 'learnpress_user_items';
45	$this->tb_lp_user_itemmeta = $prefix . 'learnpress_user_itemmeta';
46	$this->tb_lp_order_items = $prefix . 'learnpress_order_items';
47	$this->tb_lp_order_itemmeta = $prefix . 'learnpress_order_itemmeta';
48	$this->tb_lp_section_items = $prefix . 'learnpress_section_items';
49	$this->tb_lp_sections = $prefix . 'learnpress_sections';
50	$this->tb_lp_quiz_questions = $prefix . 'learnpress_quiz_questions';
51	$this->tb_lp_user_item_results = $prefix . 'learnpress_user_item_results';
52	$this->tb_lp_question_answers = $prefix . 'learnpress_question_answers';
53	$this->tb_lp_question_answermeta = $prefix . 'learnpress_question_answermeta';
54	$this->tb_lp_upgrade_db = $prefix . 'learnpress_upgrade_db';
55	$this->tb_lp_sessions = $prefix . 'learnpress_sessions';
56	$this->wpdb->hide_errors();
57	$this->set_collate();
58	}
59
60	/**
61	* Get Instance
62	*
63	* @return LP_Database
64	*/
65	public static function getInstance() {
66	if ( is_null( self::$_instance ) ) {
67	self::$_instance = new self();
68	}
69
70	return self::$_instance;
71	}
72
73	public function set_collate() {
74	$collate = '';
75
76	if ( $this->wpdb->has_cap( 'collation' ) ) {
77	if ( ! empty( $this->wpdb->charset ) ) {
78	$collate .= 'DEFAULT CHARACTER SET ' . $this->wpdb->charset;
79	}
80
81	if ( ! empty( $this->wpdb->collate ) ) {
82	$collate .= ' COLLATE ' . $this->wpdb->collate;
83	}
84	}
85
86	$this->collate = $collate;
87	}
88
89	public function get_collate(): string {
90	return $this->collate;
91	}
92
93	/**
94	* Get total Item by post type and user id
95	*
96	* @param LP_Post_Type_Filter $filter
97	*
98	* @return int
99	* @since 3.2.8
100	*/
101	public function get_count_post_of_user( LP_Post_Type_Filter $filter ): int {
102	$query_append = '';
103
104	$cache_key = _count_posts_cache_key( $filter->post_type );
105
106	// Get cache
107	$counts = wp_cache_get( $cache_key );
108	if ( false !== $counts ) {
109	return $counts;
110	}
111
112	if ( ! empty( $filter->post_status ) ) {
113	$query_append .= $this->wpdb->prepare( ' AND post_status = %s', $filter->post_status );
114	}
115
116	$query = $this->wpdb->prepare(
117	"SELECT Count(ID) FROM $this->tb_posts
118	WHERE post_type = %s
119	AND post_author = %d
120	$query_append",
121	$filter->post_type,
122	$filter->post_author
123	);
124
125	$query = apply_filters( 'learnpress/query/get_total_post_of_user', $query );
126
127	$counts = (int) $this->wpdb->get_var( $query );
128
129	// Set cache
130	wp_cache_set( $cache_key, $counts );
131
132	return $counts;
133	}
134
135	/**
136	* Get post by post_type and slug
137	*
138	* @param string $post_type .
139	* @param string $slug .
140	*
141	* @return int
142	*/
143	public function getPostAuthorByTypeAndSlug( string $post_type = '', string $slug = '' ): int {
144	$query = $this->wpdb->prepare(
145	"
146	SELECT post_author FROM $this->tb_posts
147	WHERE post_type = %s
148	AND post_name = %s",
149	$post_type,
150	$slug
151	);
152
153	return (int) $this->wpdb->get_var( $query );
154	}
155
156	/**
157	* Check table exists.
158	*
159	* @param string $name_table
160	*
161	* @return bool\|int
162	*/
163	public function check_table_exists( string $name_table ) {
164	return $this->wpdb->query( $this->wpdb->prepare( "SHOW TABLES LIKE '%s'", $name_table ) );
165	}
166
167	/**
168	* Clone table
169	*
170	* @param string $name_table .
171	*
172	* @throws Exception
173	*/
174	public function clone_table( string $name_table ):bool {
175	if ( ! current_user_can( ADMIN_ROLE ) ) {
176	throw new Exception( 'You don\'t have permission' );
177	}
178
179	$table_bk = $name_table . '_bk';
180
181	// Drop table bk if exists.
182	$this->drop_table( $table_bk );
183
184	// Clone table
185	$this->wpdb->query( "CREATE TABLE $table_bk LIKE $name_table" );
186	$this->wpdb->query( "INSERT INTO $table_bk SELECT * FROM $name_table" );
187
188	/*dbDelta(
189	"CREATE TABLE $table_bk LIKE $name_table;
190	INSERT INTO $table_bk SELECT * FROM $name_table;"
191	);*/
192
193	$this->check_execute_has_error();
194
195	return true;
196	}
197
198	/**
199	* Check column table
200	*
201	* @param string $name_table .
202	* @param string $name_col .
203	*
204	* @return bool\|int
205	*/
206	public function check_col_table( string $name_table = '', string $name_col = '' ) {
207	$query = $this->wpdb->prepare( "SHOW COLUMNS FROM $name_table LIKE '%s'", $name_col );
208
209	return $this->wpdb->query( $query );
210	}
211
212	/**
213	* Drop Column of Table
214	*
215	* @param string $name_table .
216	* @param string $name_col .
217	*
218	* @return bool\|int
219	* @throws Exception
220	*/
221	public function drop_col_table( string $name_table = '', string $name_col = '' ) {
222	if ( ! current_user_can( 'administrator' ) ) {
223	return false;
224	}
225
226	$check_table = $this->check_col_table( $this->tb_lp_user_items, $name_col );
227
228	if ( $check_table ) {
229	$execute = $this->wpdb->query( "ALTER TABLE $name_table DROP COLUMN $name_col" );
230
231	$this->check_execute_has_error();
232
233	return $execute;
234	}
235
236	return true;
237	}
238
239	/**
240	* Add Column of Table
241	*
242	* @param string $name_table .
243	* @param string $name_col .
244	* @param string $type .
245	* @param string $after_col .
246	*
247	* @return bool\|int
248	* @throws Exception
249	*/
250	public function add_col_table( string $name_table, string $name_col, string $type, string $after_col = '' ) {
251	if ( ! current_user_can( ADMIN_ROLE ) ) {
252	throw new Exception( 'You don\'t have permission' );
253	}
254
255	$query_add = '';
256
257	$col_exists = $this->check_col_table( $name_table, $name_col );
258
259	if ( ! empty( $after_col ) ) {
260	$query_add .= "AFTER $after_col";
261	}
262
263	if ( ! $col_exists ) {
264	$execute = $this->wpdb->query( "ALTER TABLE $name_table ADD COLUMN $name_col $type $query_add" );
265
266	$this->check_execute_has_error();
267
268	return $execute;
269	}
270
271	return true;
272	}
273
274	/**
275	* Drop Index of Table
276	*
277	* @param string $name_table .
278	*
279	* @return void
280	* @throws Exception
281	*/
282	public function drop_indexs_table( string $name_table ) {
283	$show_index = "SHOW INDEX FROM $name_table";
284	$indexs = $this->wpdb->get_results( $show_index );
285
286	foreach ( $indexs as $index ) {
287	if ( 'PRIMARY' === $index->Key_name \|\| '1' !== $index->Seq_in_index ) {
288	continue;
289	}
290
291	$query = "ALTER TABLE $name_table DROP INDEX $index->Key_name";
292
293	$this->wpdb->query( $query );
294	$this->check_execute_has_error();
295	}
296	}
297
298	/**
299	* Add Index of Table
300	*
301	* @param string $name_table .
302	* @param array $indexs.
303	*
304	* @return bool\|int
305	* @throws Exception
306	*/
307	public function add_indexs_table( string $name_table, array $indexs ) {
308	$add_index = '';
309	$count_indexs = count( $indexs ) - 1;
310
311	// Drop indexs .
312	$this->drop_indexs_table( $name_table );
313
314	foreach ( $indexs as $index ) {
315	if ( $count_indexs === array_search( $index, $indexs ) ) {
316	$add_index .= ' ADD INDEX ' . $index . ' (' . $index . ')';
317	} else {
318	$add_index .= ' ADD INDEX ' . $index . ' (' . $index . '),';
319	}
320	}
321
322	$execute = $this->wpdb->query(
323	"ALTER TABLE $name_table
324	$add_index"
325	);
326
327	$this->check_execute_has_error();
328
329	return $execute;
330	}
331
332	/**
333	* Drop table
334	*
335	* @param string $name_table .
336	*
337	* @return bool\|int
338	* @throws Exception
339	*/
340	public function drop_table( string $name_table = '' ) {
341	if ( ! current_user_can( ADMIN_ROLE ) ) {
342	throw new Exception( 'You don\'t have permission' );
343	}
344
345	// Check table exists.
346	$tb_exists = $this->check_table_exists( $name_table );
347	if ( $tb_exists ) {
348	$execute = $this->wpdb->query( "DROP TABLE $name_table" );
349
350	$this->check_execute_has_error();
351
352	return $execute;
353	}
354
355	return true;
356	}
357
358	/**
359	* Get list columns name of table
360	*
361	* @param string $name_table
362	*
363	* @return array
364	* @throws Exception
365	* @version 1.0.0
366	* @since 4.1.6
367	* @author tungnx
368	*/
369	public function get_cols_of_table( string $name_table ): array {
370	$query = "SHOW COLUMNS FROM $name_table";
371
372	$result = $this->wpdb->get_col( $query );
373
374	$this->check_execute_has_error();
375
376	return $result;
377	}
378
379	/**
380	* Create table learnpress_user_item_results
381	*
382	* @return bool\|int
383	* @throws Exception
384	*/
385	public function create_tb_lp_user_item_results() {
386	$collate = $this->get_collate();
387
388	$execute = $this->wpdb->query(
389	"
390	CREATE TABLE IF NOT EXISTS $this->tb_lp_user_item_results(
391	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
392	user_item_id bigint(20) unsigned NOT NULL,
393	result longtext,
394	PRIMARY KEY (id),
395	KEY user_item_id (user_item_id)
396	) $collate
397	"
398	);
399
400	$this->check_execute_has_error();
401
402	return $execute;
403	}
404
405	/**
406	* Create table learnpress_upgrade_db
407	*
408	* @return bool\|int
409	* @throws Exception
410	*/
411	public function create_tb_lp_upgrade_db() {
412	$collate = $this->get_collate();
413
414	$execute = $this->wpdb->query(
415	"
416	CREATE TABLE IF NOT EXISTS {$this->tb_lp_upgrade_db}(
417	step varchar(50) PRIMARY KEY UNIQUE,
418	status varchar(10),
419	KEY status (status)
420	) $collate
421	"
422	);
423
424	$this->check_execute_has_error();
425
426	return $execute;
427	}
428
429	/**
430	* Set step completed.
431	*
432	* @param string $step .
433	* @param string $status .
434	*
435	* @return int\|bool
436	*/
437	public function set_step_complete( string $step, string $status ) {
438	if ( ! current_user_can( 'administrator' ) ) {
439	return false;
440	}
441
442	return $this->wpdb->insert(
443	$this->tb_lp_upgrade_db,
444	array(
445	'step' => $step,
446	'status' => $status,
447	),
448	array( '%s', '%s' )
449	);
450	}
451
452	/**
453	* Get steps completed.
454	*
455	* @return array\|object\|null
456	*/
457	public function get_steps_completed() {
458	return $this->wpdb->get_results( "SELECT step, status FROM {$this->tb_lp_upgrade_db}", OBJECT_K );
459	}
460
461	/**
462	* Check execute current has any errors.
463	*
464	* @throws Exception
465	*/
466	public function check_execute_has_error() {
467	if ( $this->wpdb->last_error ) {
468	throw new Exception( $this->wpdb->last_error );
469	}
470	}
471
472	/**
473	* Important: Reason need set again indexes for table options of WP
474	* because if want change value of "option_name" will error "database error Duplicate entry"
475	* So before set must drop and add when done all
476	*
477	* @author tungnx
478	* @version 1.0.0
479	* @since 4.0.3
480	* @throws Exception
481	*/
482	public function create_indexes_tb_options() {
483	$this->drop_indexs_table( $this->tb_options );
484	$result = $this->wpdb->query(
485	"
486	ALTER TABLE $this->tb_options
487	ADD UNIQUE option_name (option_name),
488	ADD INDEX autoload (autoload)
489	"
490	);
491
492	$this->check_execute_has_error();
493
494	return $result;
495	}
496
497	/**
498	* Rename table
499	*
500	* @author tungnx
501	* @version 1.0.0
502	* @since 4.0.3
503	* @throws Exception
504	*/
505	public function rename_table( string $name_table = '', string $new_name = '' ) {
506	if ( ! current_user_can( ADMIN_ROLE ) ) {
507	throw new Exception( 'You don\'t have permission' );
508	}
509
510	$tb_exists = $this->check_table_exists( $name_table );
511
512	if ( ! $tb_exists ) {
513	throw new Exception( 'Table not exists' );
514	}
515
516	$result = $this->wpdb->query(
517	"
518	ALTER TABLE $name_table
519	RENAME $new_name
520	"
521	);
522	$this->check_execute_has_error();
523
524	return $result;
525	}
526
527	/**
528	* Check key postmeta exist on Database
529	*
530	* @param int $post_id
531	* @param string $key
532	*
533	* @return bool\|int
534	*/
535	public function check_key_postmeta_exists( int $post_id = 0, string $key = '' ) {
536	return $this->wpdb->query(
537	$this->wpdb->prepare(
538	"
539	SELECT meta_id FROM $this->tb_postmeta
540	WHERE meta_key = %s
541	AND post_id = %d
542	",
543	$key,
544	$post_id
545	)
546	);
547	}
548
549	/**
550	* Get total pages
551	*
552	* @param int $limit
553	* @param int $total_rows
554	*
555	* @return false\|float
556	*/
557	public static function get_total_pages( int $limit = 0, int $total_rows = 0 ) {
558	if ( $limit == 0 ) {
559	return 0;
560	}
561
562	$total_pages = floor( $total_rows / $limit );
563	if ( $total_rows % $limit !== 0 ) {
564	$total_pages++;
565	}
566
567	return $total_pages;
568	}
569
570	/**
571	* Get result query
572	*
573	* @return array\|null\|int\|string
574	* @throws Exception
575	* @author tungnx
576	* @version 1.0.0
577	* @since 4.1.6
578	*/
579	public function execute( LP_Filter $filter, int &$total_rows = 0 ) {
580	$result = null;
581
582	// Where
583	$WHERE = array( 'WHERE 1=1' );
584
585	// Fields select
586	$FIELDS = '*';
587	if ( ! empty( $filter->only_fields ) ) {
588	$FIELDS = implode( ',', array_unique( $filter->only_fields ) );
589	} elseif ( ! empty( $filter->fields ) ) {
590	// exclude more fields
591	if ( ! empty( $filter->exclude_fields ) ) {
592	foreach ( $filter->exclude_fields as $field ) {
593	$index_field = array_search( $field, $filter->fields );
594	if ( $index_field ) {
595	unset( $filter->fields[ $index_field ] );
596	}
597	}
598	}
599	$FIELDS = implode( ',', array_unique( $filter->fields ) );
600	}
601	$FIELDS = apply_filters( 'lp/query/fields', $FIELDS, $filter );
602
603	$INNER_JOIN = array();
604	$INNER_JOIN = array_merge( $INNER_JOIN, $filter->join );
605	$INNER_JOIN = apply_filters( 'lp/query/inner_join', $INNER_JOIN, $filter );
606	$INNER_JOIN = implode( ' ', array_unique( $INNER_JOIN ) );
607
608	$WHERE = array_merge( $WHERE, $filter->where );
609	$WHERE = apply_filters( 'lp/query/where', $WHERE, $filter );
610	$WHERE = implode( ' ', array_unique( $WHERE ) );
611
612	// Group by
613	$GROUP_BY = '';
614	if ( $filter->group_by ) {
615	$GROUP_BY .= 'GROUP BY ' . $filter->group_by;
616	$GROUP_BY = apply_filters( 'lp/query/group_by', $GROUP_BY, $filter );
617	}
618
619	// Order by
620	$ORDER_BY = '';
621	if ( ! $filter->return_string_query && $filter->order_by ) {
622	$ORDER_BY .= 'ORDER BY ' . $filter->order_by . ' ' . $filter->order . ' ';
623	$ORDER_BY = apply_filters( 'lp/query/order_by', $ORDER_BY, $filter );
624	}
625
626	// Limit
627	$LIMIT = '';
628	if ( $filter->limit != -1 ) {
629	$filter->limit = absint( $filter->limit );
630	/*if ( $filter->limit > $filter->max_limit ) {
631	$filter->limit = $filter->max_limit;
632	}*/
633	$offset = $filter->limit * ( $filter->page - 1 );
634	$LIMIT = $this->wpdb->prepare( 'LIMIT %d, %d', $offset, $filter->limit );
635	}
636
637	// For nest query
638	if ( $filter->return_string_query ) {
639	$LIMIT = '';
640	}
641
642	// From table or group select
643	$COLLECTION = '';
644	if ( ! empty( $filter->collection ) ) {
645	$COLLECTION = $filter->collection;
646	}
647
648	// Alias table
649	$ALIAS_COLLECTION = 'X';
650	if ( ! empty( $filter->collection_alias ) ) {
651	$ALIAS_COLLECTION = $filter->collection_alias;
652	}
653
654	// Query
655	$query = "SELECT $FIELDS FROM $COLLECTION AS $ALIAS_COLLECTION
656	$INNER_JOIN
657	$WHERE
658	$GROUP_BY
659	$ORDER_BY
660	$LIMIT
661	";
662
663	if ( $filter->return_string_query ) {
664	return $query;
665	} elseif ( ! empty( $filter->union ) ) {
666	$query = implode( ' UNION ', array_unique( $filter->union ) );
667	$query .= $GROUP_BY;
668	$query .= $ORDER_BY;
669	$query .= $LIMIT;
670	}
671
672	if ( ! $filter->query_count ) {
673	// Debug string query
674	if ( $filter->debug_string_query ) {
675	return $query;
676	}
677
678	$result = $this->wpdb->get_results( $query );
679	}
680
681	// Query total rows
682	$query = str_replace( array( $LIMIT, $ORDER_BY ), '', $query );
683	$query_total = "SELECT COUNT($filter->field_count) FROM ($query) AS $ALIAS_COLLECTION";
684	$total_rows = (int) $this->wpdb->get_var( $query_total );
685
686	$this->check_execute_has_error();
687
688	if ( $filter->query_count ) {
689	// Debug string query
690	if ( $filter->debug_string_query ) {
691	return $query_total;
692	}
693
694	return $total_rows;
695	}
696
697	return $result;
698	}
699
700	/**
701	* Query update
702	*
703	* @throws Exception
704	* @since 4.1.7
705	* @version 1.0.0
706	*/
707	public function update_execute( LP_Filter $filter ) {
708
709	$COLLECTION = $filter->collection;
710
711	// SET value
712	$SET = apply_filters( 'lp/query/update/set', $filter->set, $filter );
713	$SET = implode( ' ', array_unique( $SET ) );
714
715	// Where
716	$WHERE = array( 'WHERE 1=1' );
717	$WHERE = array_merge( $WHERE, $filter->where );
718	$WHERE = apply_filters( 'lp/query/update/where', $WHERE, $filter );
719	$WHERE = implode( ' ', array_unique( $WHERE ) );
720
721	$query = "
722	UPDATE $COLLECTION
723	SET $SET
724	$WHERE
725	";
726
727	$result = $this->wpdb->query( $query );
728
729	$this->check_execute_has_error();
730
731	return $result;
732	}
733
734	/**
735	* Query delete
736	*
737	* @throws Exception
738	* @since 4.1.7
739	* @version 1.0.0
740	*/
741	public function delete_execute( LP_Filter $filter ) {
742	$COLLECTION = $filter->collection;
743
744	// Where
745	$WHERE = array( 'WHERE 1=1' );
746	$WHERE = array_merge( $WHERE, $filter->where );
747	$WHERE = apply_filters( 'lp/query/delete/where', $WHERE, $filter );
748	$WHERE = implode( ' ', array_unique( $WHERE ) );
749
750	$query = "
751	DELETE FROM $COLLECTION
752	$WHERE
753	";
754
755	$result = $this->wpdb->query( $query );
756
757	$this->check_execute_has_error();
758
759	return $result;
760	}
761
762	/**
763	* Get values of list object by key
764	*
765	* @param array $arr_object
766	* @param string $key
767	*
768	* @return array
769	*/
770	public static function get_values_by_key( array $arr_object, string $key = 'ID' ): array {
771	$arr_object_ids = array();
772	foreach ( $arr_object as $object ) {
773	$arr_object_ids[] = $object->{$key};
774	}
775
776	return $arr_object_ids;
777	}
778	}
779