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

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

363 lines 15.9 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
25	/**
26	* Idempotently add the email-template status/config columns.
27	*
28	* @return void
29	*/
30	public static function ensureEmailTemplateStatusColumn()
31	{
32	global $wpdb;
33	$emailTable = $wpdb->prefix . 'bitforms_email_template';
34	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$emailTable}` LIKE 'status'")) {
35	// Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
36	$wpdb->query(
37	"ALTER TABLE `{$emailTable}`
38	ADD COLUMN `status` TINYINT(1) DEFAULT 1 AFTER `body`,
39	ADD COLUMN `config` LONGTEXT NULL AFTER `status`"
40	);
41	}
42	}
43
44	/**
45	* Idempotently add the workflows `workflow_info` column.
46	*
47	* @return void
48	*/
49	public static function ensureWorkflowInfoColumn()
50	{
51	global $wpdb;
52	$table = $wpdb->prefix . 'bitforms_workflows';
53	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$table}` LIKE 'workflow_info'")) {
54	// Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
55	$wpdb->query(
56	"ALTER TABLE `{$table}`
57	ADD COLUMN `workflow_info` LONGTEXT NULL AFTER `workflow_condition`"
58	);
59	}
60	}
61
62	/**
63	* Idempotently add the workflows `workflow_category` column (conditional-logic classic/basic split).
64	*
65	* @return void
66	*/
67	public static function ensureWorkflowCategoryColumn()
68	{
69	self::ensureWorkflowInfoColumn();
70	global $wpdb;
71	$table = $wpdb->prefix . 'bitforms_workflows';
72	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$table}` LIKE 'workflow_category'")) {
73	// Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL.
74	$wpdb->query(
75	"ALTER TABLE `{$table}`
76	ADD COLUMN `workflow_category` VARCHAR(20) DEFAULT 'classic' AFTER `workflow_info`,
77	ADD KEY `workflow_category` (`workflow_category`)"
78	);
79	}
80	}
81
82	public static function migrate()
83	{
84	global $wpdb;
85	global $bitforms_db_version;
86	$collate = '';
87
88	if ($wpdb->has_cap('collation')) {
89	if (!empty($wpdb->charset)) {
90	$collate .= "DEFAULT CHARACTER SET $wpdb->charset";
91	}
92
93	if (!empty($wpdb->collate)) {
94	$collate .= " COLLATE $wpdb->collate";
95	}
96	}
97
98	$max_index_length = 191;
99
100	$table_schema = [
101	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_reports` (
102	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
103	`category` varchar(50) NOT NULL,
104	`type` varchar(20) NOT NULL,
105	`context` varchar(20) NOT NULL,
106	`details` longtext DEFAULT NULL,
107	`isDefault` tinyint(1) DEFAULT 0,/* 0 not default, 1 default */
108	`user_id` bigint(20) unsigned DEFAULT NULL,
109	`user_ip` int(11) unsigned DEFAULT NULL,
110	`user_device` varchar(50) DEFAULT NULL,
111	`created_at` datetime DEFAULT NULL,
112	`updated_at` datetime DEFAULT NULL,
113	PRIMARY KEY (`id`)
114	) $collate;",
115
116	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form` (
117	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
118	`form_name` varchar(255) DEFAULT NULL,
119	`form_content` longtext DEFAULT NULL,
120	`builder_helper_state` longtext DEFAULT NULL,
121	`atomic_class_map` longtext DEFAULT NULL,
122	`generated_script_page_ids` longtext DEFAULT NULL,
123	`user_id` bigint(20) unsigned DEFAULT NULL,
124	`user_ip` int(11) unsigned DEFAULT NULL,
125	`views` int(11) unsigned DEFAULT 0,
126	`entries` int(11) unsigned DEFAULT 0,
127	`user_device` varchar(50) DEFAULT NULL,
128	`status` tinyint(1) DEFAULT 1,/* 0 not published, 1 published, 2 trashed */
129	`created_at` datetime DEFAULT NULL,
130	`updated_at` datetime DEFAULT NULL,
131	PRIMARY KEY (`id`)
132	) $collate;",
133
134	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_workflows` (
135	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
136	`workflow_name` varchar(255) NOT NULL,
137	`workflow_type` varchar(50) NOT NULL,
138	`workflow_run` varchar(50) NOT NULL,
139	`workflow_behaviour` varchar(25) NOT NULL,
140	`workflow_condition` longtext DEFAULT NULL,
141	`workflow_info` longtext DEFAULT NULL,
142	`workflow_category` varchar(20) DEFAULT 'classic',/* classic = legacy, basic = field show/hide, advanced = multi-condition routing */
143	`workflow_order` int(11) unsigned DEFAULT NULL,
144	`workflow_status` tinyint(1) DEFAULT 1,/* 0 disable, 1 enable, 2 enable in field settings */
145	`form_id` bigint(20) unsigned DEFAULT NULL,
146	`user_id` bigint(20) unsigned DEFAULT NULL,
147	`user_ip` int(11) unsigned DEFAULT NULL,
148	`user_location` text DEFAULT NULL,
149	`user_device` varchar(50) DEFAULT NULL,
150	`created_at` datetime DEFAULT NULL,
151	`updated_at` datetime DEFAULT NULL,
152	PRIMARY KEY (`id`),
153	KEY `form_id` (`form_id`),
154	KEY `workflow_category` (`workflow_category`)
155	) $collate;",
156
157	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_success_messages` (
158	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
159	`message_title` varchar(255) DEFAULT NULL,
160	`message_content` longtext DEFAULT NULL,
161	`message_config` longtext DEFAULT NULL,
162	`form_id` bigint(20) unsigned DEFAULT NULL,
163	`user_id` bigint(20) unsigned DEFAULT NULL,
164	`user_ip` int(11) unsigned DEFAULT NULL,
165	`created_at` datetime DEFAULT NULL,
166	`updated_at` datetime DEFAULT NULL,
167	PRIMARY KEY (`id`)
168	) $collate;",
169
170	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_email_template` (
171	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
172	`title` text DEFAULT NULL,
173	`sub` text DEFAULT NULL,
174	`body` longtext DEFAULT NULL,
175	`status` tinyint(1) DEFAULT 1,/* 0 disabled, 1 enabled */
176	`config` longtext DEFAULT NULL,/* SendTo / From / From Name / CC / BCC / Reply-To / attachments JSON */
177	`form_id` bigint(20) unsigned DEFAULT NULL,
178	`user_id` bigint(20) unsigned DEFAULT NULL,
179	`user_ip` int(11) unsigned DEFAULT NULL,
180	`created_at` datetime DEFAULT NULL,
181	`updated_at` datetime DEFAULT NULL,
182	PRIMARY KEY (`id`)
183	) $collate;",
184
185	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_integration` (
186	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
187	`category` varchar(50) NOT NULL,
188	`integration_name` varchar(255) DEFAULT NULL,
189	`integration_type` varchar(50) NOT NULL,
190	`integration_details` longtext DEFAULT NULL,
191	`form_id` bigint(20) unsigned DEFAULT NULL, /* form_id = 0 means all/app */
192	`user_id` bigint(20) unsigned DEFAULT NULL,
193	`user_ip` int(11) unsigned DEFAULT NULL,
194	`status` tinyint(1) DEFAULT 1,/* 0 disabled, 1 published, 2 trashed */
195	`created_at` datetime DEFAULT NULL,
196	`updated_at` datetime DEFAULT NULL,
197	PRIMARY KEY (`id`)
198	) $collate;",
199
200	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entries` (
201	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
202	`form_id` bigint(20) unsigned DEFAULT NULL,
203	`user_id` bigint(20) unsigned DEFAULT NULL,
204	`user_ip` varchar(255) DEFAULT NULL,
205	`user_location` text DEFAULT NULL,
206	`user_device` varchar(50) DEFAULT NULL,
207	`referer` TEXT DEFAULT NULL,
208	`status` tinyint(1) DEFAULT 0,/* 0 not viewed, 1 viewed, 2 trashed */
209	`created_at` datetime DEFAULT NULL,
210	`updated_at` datetime DEFAULT NULL,
211	PRIMARY KEY (`id`),
212	KEY `form_id` (`form_id`)
213	) $collate;",
214
215	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entrymeta` (
216	`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
217	`bitforms_form_entry_id` bigint(20) unsigned DEFAULT NULL,
218	`meta_key` varchar(255) DEFAULT NULL,
219	`meta_value` longtext,
220	PRIMARY KEY (`meta_id`),
221	KEY `meta_key` (`meta_key`({$max_index_length})),
222	KEY `entry_id` (`bitforms_form_entry_id`)
223	) $collate;",
224
225	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_log` (
226	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
227	`user_id` int(11) NOT NULL,
228	`log_type` varchar(50) DEFAULT NULL,
229	`ip` int(11) DEFAULT NULL,
230	`action_type` varchar(50) DEFAULT NULL,
231	`form_entry_id` bigint(20) DEFAULT NULL,
232	`form_id` bigint(20) DEFAULT NULL,
233	`content` longtext,
234	`created_at` DATETIME NOT NULL,
235	PRIMARY KEY (`id`),
236	KEY `form_id` (`form_id`),
237	KEY `form_entry_id` (`form_entry_id`)
238	) $collate;",
239
240	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_log_details` (
241	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
242	`log_id` bigint(20) NOT NULL,
243	`integration_id` bigint(20) DEFAULT NULL,
244	`api_type` varchar(255) DEFAULT NULL,
245	`response_type` varchar(50) DEFAULT NULL,
246	`response_obj` LONGTEXT DEFAULT NULL,
247	`created_at` DATETIME NOT NULL,
248	PRIMARY KEY (`id`),
249	KEY `log_id` (`log_id`),
250	KEY `integration_id` (`integration_id`)
251	) $collate;",
252	"CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}bitforms_form_entry_relatedinfo` (
253	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
254	`info_type` VARCHAR(50) NOT NULL,
255	`info_details` LONGTEXT NULL,
256	`form_id` BIGINT(20) UNSIGNED NOT NULL,
257	`entry_id` BIGINT(20) UNSIGNED NOT NULL,
258	`user_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
259	`user_ip` INT(11) UNSIGNED NULL DEFAULT NULL,
260	`status` INT(1) UNSIGNED NOT NULL DEFAULT '1',
261	`created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
262	`updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
263	PRIMARY KEY (`id`),
264	KEY `form_id` (`form_id`),
265	KEY `entry_id` (`entry_id`)
266	) $collate;"
267	];
268
269	try {
270	include_once ABSPATH . 'wp-admin/includes/upgrade.php';
271	foreach ($table_schema as $table) {
272	dbDelta($table);
273	}
274	$installed_db_version = get_option('bitforms_db_version');
275	if ($installed_db_version && version_compare('1.1', $installed_db_version, '>=')) {
276	// if new db version is equal or higher than 1.1
277	// Schema migration: table name interpolation only (no user input). $wpdb->prepare() cannot parameterize DDL statements.
278	try {
279	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`
280	MODIFY `log_type` VARCHAR(50) NULL DEFAULT NULL,
281	MODIFY `created_at` DATETIME NOT NULL,
282	CHANGE COLUMN `meta_key` `content` LONGTEXT NULL,
283	ADD COLUMN `action_type` VARCHAR(50) NULL DEFAULT NULL,
284	ADD COLUMN `content` LONGTEXT NULL AFTER `action_type`");
285	} catch (\Exception $e) {
286	Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_entry_log`...");
287	}
288	try {
289	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`
290	MODIFY `api_type` VARCHAR(255) NULL DEFAULT NULL,
291	MODIFY `response_obj` LONGTEXT NULL,
292	MODIFY `created_at` DATETIME NOT NULL");
293	} catch (\Exception $e) {
294	Log::debug_log("Update failed: {$e->getMessage()} in query: ALTER TABLE `{$wpdb->prefix}bitforms_form_log_details`...");
295	}
296	}
297	if ($installed_db_version && version_compare('1.3', $installed_db_version, '>=')) {
298	// if new db version is equal or higher than 1.3
299	$wpdb->query("ALTER TABLE `{$wpdb->prefix}bitforms_form_entries` MODIFY `user_ip` VARCHAR(255) NULL DEFAULT NULL");
300	}
301
302	if ($installed_db_version && version_compare('2.0', $installed_db_version, '>=')) {
303	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_success_messages` LIKE 'message_config'")) {
304	$wpdb->query(
305	"ALTER TABLE `{$wpdb->prefix}bitforms_success_messages`
306	ADD COLUMN `message_config` LONGTEXT NULL AFTER `message_content`"
307	);
308	}
309	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form` LIKE 'builder_helper_state'")) {
310	$wpdb->query(
311	"ALTER TABLE `{$wpdb->prefix}bitforms_form`
312	ADD COLUMN `builder_helper_state` LONGTEXT NULL AFTER `form_content`,
313	ADD COLUMN `atomic_class_map` LONGTEXT NULL AFTER `builder_helper_state`,
314	ADD COLUMN `generated_script_page_ids` LONGTEXT NULL AFTER `atomic_class_map`"
315	);
316	}
317	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_order'")) {
318	$wpdb->query(
319	"ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
320	ADD COLUMN `workflow_order` INT(11) DEFAULT NULL AFTER `workflow_condition`"
321	);
322	}
323	}
324
325	if ($installed_db_version && version_compare('2.2', $installed_db_version, '>=')) {
326	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_form_entries` LIKE 'referer'")) {
327	$wpdb->query(
328	"ALTER TABLE `{$wpdb->prefix}bitforms_form_entries`
329	ADD COLUMN `referer` TEXT NULL DEFAULT NULL"
330	);
331	}
332	}
333
334	if ($installed_db_version && version_compare('2.4', $installed_db_version, '>=')) {
335	if (null === $wpdb->get_var("SHOW COLUMNS FROM `{$wpdb->prefix}bitforms_workflows` LIKE 'workflow_status'")) {
336	$wpdb->query(
337	"ALTER TABLE `{$wpdb->prefix}bitforms_workflows`
338	ADD COLUMN `workflow_status` TINYINT(1) DEFAULT 1 NOT NULL AFTER `workflow_order`"
339	);
340	}
341	}
342
343	// add workflow_info column if not exists
344	if ($installed_db_version && version_compare('3.0', $installed_db_version, '>=')) {
345	self::ensureWorkflowInfoColumn();
346	}
347
348	// add workflow_category column if not exists (conditional logic separation)
349	// existing rows default to 'classic' so Free keeps executing them unchanged; data normalization in Fallback WorkFlow@normalizeCategory
350	if ($installed_db_version && version_compare('3.2', $installed_db_version, '>=')) {
351	self::ensureWorkflowCategoryColumn();
352	// email template enable/disable + per-template config (SendTo/From/CC/BCC/Reply-To/attachments)
353	self::ensureEmailTemplateStatusColumn();
354	}
355
356	update_option('bitforms_db_version', $bitforms_db_version, true);
357	} catch (\Exception $e) {
358	// Log the error to a file or the database
359	Log::debug_log('Error during DB migration: ' . $e->getMessage());
360	}
361	}
362	}
363