fluentform / database / Migrations / Submissions.php

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

112 lines 5.5 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`),
47	KEY `created_at` (`created_at`)) $charsetCollate;";
48
49	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration file, direct query needed
50	if ($force \|\| $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) != $table) {
51	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
52	dbDelta($sql);
53	} else {
54	// Add indexes to existing tables
55	self::maybeAddIndexes();
56	}
57	}
58
59	/**
60	* Add indexes to existing tables for better query performance.
61	*
62	* @return void
63	*/
64	private static function maybeAddIndexes()
65	{
66	global $wpdb;
67
68	$table = $wpdb->prefix . 'fluentform_submissions';
69
70	// Check if indexes already exist
71	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Migration file, checking indexes, %1s is for identifier
72	$indexes = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %1s", $table), ARRAY_A);
73
74	$existingIndexes = [];
75	foreach ($indexes as $index) {
76	$existingIndexes[] = $index['Key_name'];
77	}
78
79	// Add composite index form_id + status if it doesn't exist
80	if (!in_array('form_id_status', $existingIndexes)) {
81	// 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
82	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `form_id_status` (`form_id`, `status`)", $table));
83	}
84
85	// Add composite index form_id + created_at if it doesn't exist
86	if (!in_array('form_id_created_at', $existingIndexes)) {
87	// 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
88	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `form_id_created_at` (`form_id`, `created_at`)", $table));
89	}
90
91	// Add a standalone created_at index for cross-form, date-only reports —
92	// form_id_created_at leads with form_id, so it can't serve a query that
93	// filters on created_at with no form_id (e.g. the unrestricted analytics).
94	if (!in_array('created_at', $existingIndexes)) {
95	// 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
96	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `created_at` (`created_at`)", $table));
97	}
98
99	// Add user_id index if it doesn't exist
100	if (!in_array('user_id', $existingIndexes)) {
101	// 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
102	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `user_id` (`user_id`)", $table));
103	}
104
105	// Add serial_number index if it doesn't exist
106	if (!in_array('serial_number', $existingIndexes)) {
107	// 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
108	$wpdb->query($wpdb->prepare("ALTER TABLE %1s ADD KEY `serial_number` (`serial_number`)", $table));
109	}
110	}
111	}
112