bit-form / includes / Core / Database / DB.php

DB.php in Bit Form – Contact Form, Payment Forms, Multi Step Forms, Calculator & Custom Form Builder 3.1.2, at includes/Core/Database/DB.php

299 lines 13.4 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Class For Database Migration
5	*
6	* @category Database
7	* @author BitCode Developer <developer@bitcode.pro>
8	*/
9
10	namespace BitCode\BitForm\Core\Database;
11
12	use BitCode\BitForm\Core\Util\Log;
13
14	/**
15	* Database Migration
16	*/
17	final class DB
18	{
19	/**
20	* Undocumented function
21	*
22	* @return void
23	*/
24	public static function migrate()
25	{
26	global $wpdb;
27	global $bitforms_db_version;
28	$collate = '';
29
30	if ($wpdb->has_cap('collation')) {
31	if (!empty($wpdb->charset)) {
32	$collate .= "DEFAULT CHARACTER SET $wpdb->charset";
33	}
34
35	if (!empty($wpdb->collate)) {
36	$collate .= " COLLATE $wpdb->collate";
37	}
38	}
39
40	$max_index_length = 191;
41
42	$table_schema = [
43	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_reports` (
44	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
45	`category` varchar(50) NOT NULL,
46	`type` varchar(20) NOT NULL,
47	`context` varchar(20) NOT NULL,
48	`details` longtext DEFAULT NULL,
49	`isDefault` tinyint(1) DEFAULT 0,/* 0 not default, 1 default */
50	`user_id` bigint(20) unsigned DEFAULT NULL,
51	`user_ip` int(11) unsigned DEFAULT NULL,
52	`user_device` varchar(50) DEFAULT NULL,
53	`created_at` datetime DEFAULT NULL,
54	`updated_at` datetime DEFAULT NULL,
55	PRIMARY KEY (`id`)
56	) $collate;",
57
58	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form` (
59	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
60	`form_name` varchar(255) DEFAULT NULL,
61	`form_content` longtext DEFAULT NULL,
62	`builder_helper_state` longtext DEFAULT NULL,
63	`atomic_class_map` longtext DEFAULT NULL,
64	`generated_script_page_ids` longtext DEFAULT NULL,
65	`user_id` bigint(20) unsigned DEFAULT NULL,
66	`user_ip` int(11) unsigned DEFAULT NULL,
67	`views` int(11) unsigned DEFAULT 0,
68	`entries` int(11) unsigned DEFAULT 0,
69	`user_device` varchar(50) DEFAULT NULL,
70	`status` tinyint(1) DEFAULT 1,/* 0 not published, 1 published, 2 trashed */
71	`created_at` datetime DEFAULT NULL,
72	`updated_at` datetime DEFAULT NULL,
73	PRIMARY KEY (`id`)
74	) $collate;",
75
76	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_workflows` (
77	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
78	`workflow_name` varchar(255) NOT NULL,
79	`workflow_type` varchar(50) NOT NULL,
80	`workflow_run` varchar(50) NOT NULL,
81	`workflow_behaviour` varchar(25) NOT NULL,
82	`workflow_condition` longtext DEFAULT NULL,
83	`workflow_info` longtext DEFAULT NULL,
84	`workflow_order` int(11) unsigned DEFAULT NULL,
85	`workflow_status` tinyint(1) DEFAULT 1,/* 0 disable, 1 enable, 2 enable in field settings */
86	`form_id` bigint(20) unsigned DEFAULT NULL,
87	`user_id` bigint(20) unsigned DEFAULT NULL,
88	`user_ip` int(11) unsigned DEFAULT NULL,
89	`user_location` text DEFAULT NULL,
90	`user_device` varchar(50) DEFAULT NULL,
91	`created_at` datetime DEFAULT NULL,
92	`updated_at` datetime DEFAULT NULL,
93	PRIMARY KEY (`id`),
94	KEY `form_id` (`form_id`)
95	) $collate;",
96
97	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_success_messages` (
98	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
99	`message_title` varchar(255) DEFAULT NULL,
100	`message_content` longtext DEFAULT NULL,
101	`message_config` longtext DEFAULT NULL,
102	`form_id` bigint(20) unsigned DEFAULT NULL,
103	`user_id` bigint(20) unsigned DEFAULT NULL,
104	`user_ip` int(11) unsigned DEFAULT NULL,
105	`created_at` datetime DEFAULT NULL,
106	`updated_at` datetime DEFAULT NULL,
107	PRIMARY KEY (`id`)
108	) $collate;",
109
110	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_email_template` (
111	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
112	`title` text DEFAULT NULL,
113	`sub` text DEFAULT NULL,
114	`body` longtext DEFAULT NULL,
115	`form_id` bigint(20) unsigned DEFAULT NULL,
116	`user_id` bigint(20) unsigned DEFAULT NULL,
117	`user_ip` int(11) unsigned DEFAULT NULL,
118	`created_at` datetime DEFAULT NULL,
119	`updated_at` datetime DEFAULT NULL,
120	PRIMARY KEY (`id`)
121	) $collate;",
122
123	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_integration` (
124	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
125	`category` varchar(50) NOT NULL,
126	`integration_name` varchar(255) DEFAULT NULL,
127	`integration_type` varchar(50) NOT NULL,
128	`integration_details` longtext DEFAULT NULL,
129	`form_id` bigint(20) unsigned DEFAULT NULL, /* form_id = 0 means all/app */
130	`user_id` bigint(20) unsigned DEFAULT NULL,
131	`user_ip` int(11) unsigned DEFAULT NULL,
132	`status` tinyint(1) DEFAULT 1,/* 0 disabled, 1 published, 2 trashed */
133	`created_at` datetime DEFAULT NULL,
134	`updated_at` datetime DEFAULT NULL,
135	PRIMARY KEY (`id`)
136	) $collate;",
137
138	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entries` (
139	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
140	`form_id` bigint(20) unsigned DEFAULT NULL,
141	`user_id` bigint(20) unsigned DEFAULT NULL,
142	`user_ip` varchar(255) DEFAULT NULL,
143	`user_location` text DEFAULT NULL,
144	`user_device` varchar(50) DEFAULT NULL,
145	`referer` TEXT DEFAULT NULL,
146	`status` tinyint(1) DEFAULT 0,/* 0 not viewed, 1 viewed, 2 trashed */
147	`created_at` datetime DEFAULT NULL,
148	`updated_at` datetime DEFAULT NULL,
149	PRIMARY KEY (`id`),
150	KEY `form_id` (`form_id`)
151	) $collate;",
152
153	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entrymeta` (
154	`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
155	`bitforms_form_entry_id` bigint(20) unsigned DEFAULT NULL,
156	`meta_key` varchar(255) DEFAULT NULL,
157	`meta_value` longtext,
158	PRIMARY KEY (`meta_id`),
159	KEY `meta_key` (`meta_key`({$max_index_length})),
160	KEY `entry_id` (`bitforms_form_entry_id`)
161	) $collate;",
162
163	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_log` (
164	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
165	`user_id` int(11) NOT NULL,
166	`log_type` varchar(50) DEFAULT NULL,
167	`ip` int(11) DEFAULT NULL,
168	`action_type` varchar(50) DEFAULT NULL,
169	`form_entry_id` bigint(20) DEFAULT NULL,
170	`form_id` bigint(20) DEFAULT NULL,
171	`content` longtext,
172	`created_at` DATETIME NOT NULL,
173	PRIMARY KEY (`id`),
174	KEY `form_id` (`form_id`),
175	KEY `form_entry_id` (`form_entry_id`)
176	) $collate;",
177
178	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_log_details` (
179	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
180	`log_id` bigint(20) NOT NULL,
181	`integration_id` bigint(20) DEFAULT NULL,
182	`api_type` varchar(255) DEFAULT NULL,
183	`response_type` varchar(50) DEFAULT NULL,
184	`response_obj` LONGTEXT DEFAULT NULL,
185	`created_at` DATETIME NOT NULL,
186	PRIMARY KEY (`id`),
187	KEY `log_id` (`log_id`),
188	KEY `integration_id` (`integration_id`)
189	) $collate;",
190	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_relatedinfo` (
191	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
192	`info_type` VARCHAR(50) NOT NULL,
193	`info_details` LONGTEXT NULL,
194	`form_id` BIGINT(20) UNSIGNED NOT NULL,
195	`entry_id` BIGINT(20) UNSIGNED NOT NULL,
196	`user_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
197	`user_ip` INT(11) UNSIGNED NULL DEFAULT NULL,
198	`status` INT(1) UNSIGNED NOT NULL DEFAULT '1',
199	`created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
200	`updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
201	PRIMARY KEY (`id`),
202	KEY `form_id` (`form_id`),
203	KEY `entry_id` (`entry_id`)
204	) $collate;"
205	];
206
207	try {
208	include_once ABSPATH . 'wp-admin/includes/upgrade.php';
209	foreach ($table_schema as $table) {
210	dbDelta($table);
211	}
212	$installed_db_version = get_option('bitforms_db_version');
213	if ($installed_db_version && version_compare('1.1', $installed_db_version, '>=')) {
214	// if new db version is equal or higher than 1.1
215	// Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL statements.
216	try {
217	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`
218	MODIFY `log_type` VARCHAR(50) NULL DEFAULT NULL,
219	MODIFY `created_at` DATETIME NOT NULL,
220	CHANGE COLUMN `meta_key` `content` LONGTEXT NULL,
221	ADD COLUMN `action_type` VARCHAR(50) NULL DEFAULT NULL,
222	ADD COLUMN `content` LONGTEXT NULL AFTER `action_type`");
223	} catch (\Exception $e) {
224	Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`...");
225	}
226	try {
227	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`
228	MODIFY `api_type` VARCHAR(255) NULL DEFAULT NULL,
229	MODIFY `response_obj` LONGTEXT NULL,
230	MODIFY `created_at` DATETIME NOT NULL");
231	} catch (\Exception $e) {
232	Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`...");
233	}
234	}
235	if ($installed_db_version && version_compare('1.3', $installed_db_version, '>=')) {
236	// if new db version is equal or higher than 1.3
237	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entries` MODIFY `user_ip` VARCHAR(255) NULL DEFAULT NULL");
238	}
239
240	if ($installed_db_version && version_compare('2.0', $installed_db_version, '>=')) {
241	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_success_messages` LIKE 'message_config'")) {
242	$wpdb->query(
243	"ALTER TABLE `{$wpdb->prefix}bitforms_success_messages`
244	ADD COLUMN `message_config` LONGTEXT NULL AFTER `message_content`"
245	);
246	}
247	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form` LIKE 'builder_helper_state'")) {
248	$wpdb->query(
249	"ALTER TABLE `{$wpdb->prefix}bitforms_form`
250	ADD COLUMN `builder_helper_state` LONGTEXT NULL AFTER `form_content`,
251	ADD COLUMN `atomic_class_map` LONGTEXT NULL AFTER `builder_helper_state`,
252	ADD COLUMN `generated_script_page_ids` LONGTEXT NULL AFTER `atomic_class_map`"
253	);
254	}
255	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_order'")) {
256	$wpdb->query(
257	"ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
258	ADD COLUMN `workflow_order` INT(11) DEFAULT NULL AFTER `workflow_condition`"
259	);
260	}
261	}
262
263	if ($installed_db_version && version_compare('2.2', $installed_db_version, '>=')) {
264	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form_entries` LIKE 'referer'")) {
265	$wpdb->query(
266	"ALTER TABLE `{$wpdb->prefix}bitforms_form_entries`
267	ADD COLUMN `referer` TEXT NULL DEFAULT NULL"
268	);
269	}
270	}
271
272	if ($installed_db_version && version_compare('2.4', $installed_db_version, '>=')) {
273	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_status'")) {
274	$wpdb->query(
275	"ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
276	ADD COLUMN `workflow_status` TINYINT(1) DEFAULT 1 NOT NULL AFTER `workflow_order`"
277	);
278	}
279	}
280
281	// add workflow_info column if not exists
282	if ($installed_db_version && version_compare('3.0', $installed_db_version, '>=')) {
283	$table_name = $wpdb->prefix . 'bitforms_workflows';
284	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$table_name}` LIKE 'workflow_info'")) {
285	$wpdb->query(
286	"ALTER TABLE `{$table_name}`
287	ADD COLUMN `workflow_info` LONGTEXT NULL AFTER `workflow_condition`"
288	);
289	}
290	}
291
292	update_option('bitforms_db_version', $bitforms_db_version, true);
293	} catch (\Exception $e) {
294	// Log the error to a file or the database
295	Log::debug_log('Error during DB migration: ' . $e->getMessage());
296	}
297	}
298	}
299