chatbot / includes / chat-sessions / inc / chatsession-db-structure.php

chatsession-db-structure.php in WPBot – AI ChatBot for Live Support, Lead Generation, WordPress Automation, AI Services 8.8.0, at includes/chat-sessions/inc/chatsession-db-structure.php

208 lines 11.8 KB

Raw Download Zip

1	<?php
2	if ( ! defined( 'ABSPATH' ) ) {
3	exit;
4	}
5	// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
6
7	/******************************************
8	* DB Install
9	******************************************/
10
11	global $wpdb;
12
13	$collate = '';
14
15	if ( $wpdb->has_cap( 'collation' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
16
17	if ( ! empty( $wpdb->charset ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
18
19	$collate .= "DEFAULT CHARACTER SET $wpdb->charset"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
20	}
21	if ( ! empty( $wpdb->collate ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
22
23	$collate .= " COLLATE $wpdb->collate"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
24
25	}
26	}
27
28	$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
29
30	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table1 ) ) ) != $table1 ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
31	$sql_sliders_Table1 = "
32	CREATE TABLE IF NOT EXISTS `$table1` (
33	`id` int(11) NOT NULL AUTO_INCREMENT,
34	`session_id` varchar(256) NOT NULL,
35	`name` varchar(256) NOT NULL,
36	`email` varchar(256) NOT NULL,
37	`phone` varchar(256) NOT NULL,
38	`date` datetime NOT NULL,
39	`user_id` int(11) NOT NULL,
40	`interaction` int(11) NOT NULL,
41	PRIMARY KEY (`id`)
42	) $collate AUTO_INCREMENT=1 ";
43
44	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
45	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
46	}
47
48	$table1 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
49
50	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table1 ) ) ) != strtolower( $table1 ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
51	$sql_sliders_Table1 = "
52	CREATE TABLE IF NOT EXISTS `$table1` (
53	`id` int(11) NOT NULL AUTO_INCREMENT,
54	`user_id` int(11) NOT NULL,
55	`conversation` LONGTEXT NOT NULL,
56	`interaction` int(11) NOT NULL,
57	`environment_info` LONGTEXT NOT NULL,
58	PRIMARY KEY (`id`)
59	) $collate AUTO_INCREMENT=1 ";
60
61	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
62	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
63	}
64
65	$table_failed_response = $wpdb->prefix . 'wpbot_failed_response'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
66
67	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_failed_response ) ) ) != $table_failed_response ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
68	$sql_failed_response = "
69	CREATE TABLE IF NOT EXISTS `$table_failed_response` (
70	`id` int(11) NOT NULL AUTO_INCREMENT,
71	`query` varchar(256) NOT NULL,
72	`count` int(11) NOT NULL,
73	`status` int(11) NOT NULL,
74	PRIMARY KEY (`id`)
75	) $collate AUTO_INCREMENT=1 ";
76
77	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
78	dbDelta( $sql_failed_response ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
79	}
80
81	/******************************************
82	* Helper Function: Check if a Database Column Exists
83	*/
84	if ( ! function_exists( 'qcwp_isset_table_column' ) ) {
85
86	function qcwp_isset_table_column( $table_name, $column_name ) {
87	global $wpdb;
88
89	$columns = $wpdb->get_results( 'SHOW COLUMNS FROM `' . esc_sql( $table_name ) . '`', ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
90
91	foreach ( $columns as $column ) {
92	if ( $column['Field'] == $column_name ) {
93	return true;
94	}
95	}
96	}
97	}
98
99	/******************************************
100	* When the main plugin is activated,
101	* Build the necessary DB Structure.
102	*/
103	register_activation_hook( __FILE__, 'qcld_wb_chatboot_sessions_defualt_options' );
104
105	function qcld_wb_chatboot_sessions_defualt_options() {
106	global $wpdb;
107
108	$collate = '';
109
110	if ( $wpdb->has_cap( 'collation' ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
111
112	if ( ! empty( $wpdb->charset ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
113
114	$collate .= "DEFAULT CHARACTER SET $wpdb->charset"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
115	}
116	if ( ! empty( $wpdb->collate ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
117
118	$collate .= " COLLATE $wpdb->collate"; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
119
120	}
121	}
122
123	// Create Table: wpbot_user
124
125	$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
126
127	$sql_sliders_Table1 = "
128	CREATE TABLE IF NOT EXISTS `$table1` (
129	`id` int(11) NOT NULL AUTO_INCREMENT,
130	`session_id` varchar(256) NOT NULL,
131	`name` varchar(256) NOT NULL,
132	`email` varchar(256) NOT NULL,
133	`phone` varchar(256) NOT NULL,
134	`date` datetime NOT NULL,
135	`user_id` int(11) NOT NULL,
136	`interaction` int(11) NOT NULL,
137	PRIMARY KEY (`id`)
138	) $collate AUTO_INCREMENT=1 ";
139
140	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
141
142	dbDelta( $sql_sliders_Table1 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
143
144	if ( ! qcwp_isset_table_column( $table1, 'phone' ) ) {
145	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `phone` varchar(256) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
146	}
147
148	// Create Table: wpbot_conversation
149
150	$table2 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
151
152	$sql_sliders_Table2 = "
153	CREATE TABLE IF NOT EXISTS `$table2` (
154	`id` int(11) NOT NULL AUTO_INCREMENT,
155	`user_id` int(11) NOT NULL,
156	`conversation` LONGTEXT NOT NULL,
157	`interaction` int(11) NOT NULL,
158	`environment_info` LONGTEXT NOT NULL,
159	PRIMARY KEY (`id`)
160	) $collate AUTO_INCREMENT=1 ";
161
162	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
163
164	dbDelta( $sql_sliders_Table2 ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
165
166	if ( ! qcwp_isset_table_column( $table2, 'interaction' ) ) {
167	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
168	}
169
170	if ( ! qcwp_isset_table_column( $table1, 'interaction' ) ) {
171	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
172	}
173	}
174
175	/******************************************
176	* Update DB Structure, If Necessary
177	******************************************/
178	function qcldwpbot_chatsession_db_update_check() {
179
180	global $wpdb;
181
182	$version = get_option( 'wpbot_chatsession_db_version', '1.0' );
183
184	$table1 = $wpdb->prefix . 'wpbot_user'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
185
186	$table2 = $wpdb->prefix . 'wpbot_conversation'; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
187
188	if ( version_compare( $version, '2.0' ) < 0 ) {
189
190	if ( ! qcwp_isset_table_column( $table1, 'interaction' ) ) {
191	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table1 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
192	}
193
194	if ( ! qcwp_isset_table_column( $table2, 'interaction' ) ) {
195	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `interaction` int(11) NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
196	}
197
198	if ( ! qcwp_isset_table_column( $table2, 'environment_info' ) ) {
199	$wpdb->query( 'ALTER TABLE `' . esc_sql( $table2 ) . '` ADD `environment_info` LONGTEXT NOT NULL' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
200	}
201
202	update_option( 'wpbot_chatsession_db_version', '2.0' );
203
204	}
205	}
206
207	add_action( 'plugins_loaded', 'qcldwpbot_chatsession_db_update_check' );
208