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

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

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