fluentform / database / Migrations / Submissions.php

Submissions.php in Fluent Forms – Customizable Contact Forms, Survey, Quiz, & Conversational Form Builder 6.2.6, at database/Migrations/Submissions.php

103 lines 4.7 KB

Raw Download Zip

1	<?php
2
3	namespace FluentForm\Database\Migrations;
4
5	class Submissions
6	{
7	/**
8	* Migrate the table.
9	*
10	* @return void
11	*/
12	public static function migrate($force = false)
13	{
14	global $wpdb;
15
16	$charsetCollate = $wpdb->get_charset_collate();
17
18	$table = $wpdb->prefix . 'fluentform_submissions';
19
20	$sql = "CREATE TABLE $table (
21	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
22	`form_id` INT UNSIGNED NULL,
23	`serial_number` INT UNSIGNED NULL,
24	`response` LONGTEXT NULL,
25	`source_url` VARCHAR(255) NULL,
26	`user_id` INT UNSIGNED NULL,
27	`status` VARCHAR(45) NULL DEFAULT 'unread' COMMENT 'possible values: read, unread, trashed',
28	`is_favourite` TINYINT(1) NOT NULL DEFAULT 0,
29	`browser` VARCHAR(45) NULL,
30	`device` VARCHAR(45) NULL,
31	`ip` VARCHAR(45) NULL,
32	`city` VARCHAR(45) NULL,
33	`country` VARCHAR(45) NULL,
34	`payment_status` VARCHAR(45) NULL,
35	`payment_method` VARCHAR(45) NULL,
36	`payment_type` VARCHAR(45) NULL,
37	`currency` VARCHAR(45) NULL,
38	`payment_total` FLOAT NULL,
39	`total_paid` FLOAT NULL,
40	`created_at` TIMESTAMP NULL,
41	`updated_at` TIMESTAMP NULL,
42	PRIMARY KEY (`id`),
43	KEY `form_id_status` (`form_id`, `status`),
44	KEY `form_id_created_at` (`form_id`, `created_at`),
45	KEY `user_id` (`user_id`),
46	KEY `serial_number` (`serial_number`)) $charsetCollate;";
47
48	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration file, direct query needed
49	if ($force \|\| $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
50	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
51	dbDelta($sql);
52	} else {
53	// Add indexes to existing tables
54	self::maybeAddIndexes();
55	}
56	}
57
58	/**
59	* Add indexes to existing tables for better query performance.
60	*
61	* @return void
62	*/
63	private static function maybeAddIndexes()
64	{
65	global $wpdb;
66
67	$table = $wpdb->prefix . 'fluentform_submissions';
68
69	// Check if indexes already exist
70	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, checking indexes, %1s is for identifier
71	$indexes = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %1s", $table), ARRAY_A);
72
73	$existingIndexes = [];
74	foreach ($indexes as $index) {
75	$existingIndexes[] = $index['Key_name'];
76	}
77
78	// Add composite index form_id + status if it doesn't exist
79	if (!in_array('form_id_status', $existingIndexes)) {
80	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, adding composite index for performance, %1s is for identifier
81	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `form_id_status` (`form_id`, `status`)", $table));
82	}
83
84	// Add composite index form_id + created_at if it doesn't exist
85	if (!in_array('form_id_created_at', $existingIndexes)) {
86	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, adding composite index for performance, %1s is for identifier
87	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `form_id_created_at` (`form_id`, `created_at`)", $table));
88	}
89
90	// Add user_id index if it doesn't exist
91	if (!in_array('user_id', $existingIndexes)) {
92	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, adding index for performance, %1s is for identifier
93	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `user_id` (`user_id`)", $table));
94	}
95
96	// Add serial_number index if it doesn't exist
97	if (!in_array('serial_number', $existingIndexes)) {
98	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, adding index for performance, %1s is for identifier
99	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `serial_number` (`serial_number`)", $table));
100	}
101	}
102	}
103