← All changes | inc/database/tables/entries.php +245 -31 0.0.13 → 2.12.8 View file →

		@@ -36,9 +36,9 @@
36	36	* {@inheritDoc}
37	37	*
38	38	* @var int
39	39	*/
40		- protected $table_version = 1;
	40	+ protected $table_version = 2;
41	41
42	42	/**
43	43	* Current logs.
44	44	*
		@@ -119,8 +119,13 @@
119	119	public function get_columns_definition() {
120	120	return [
121	121	'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
122	122	'form_id BIGINT(20) UNSIGNED',
	123	+ // NOT NULL DEFAULT 0 is load-bearing, not tidiness. An anonymous
	124	+ // submission stores 0, and Forms_Data::calculate_form_metrics() excludes
	125	+ // site editors with `user_id NOT IN (…)`. NOT IN never matches NULL, so
	126	+ // making this column nullable would drop every anonymous entry from the
	127	+ // numerator and report a conversion rate near 0% on every form.
123	128	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
124	129	'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
125	130	'logs LONGTEXT',
126	131	'notes LONGTEXT',
		@@ -132,8 +137,14 @@
132	137	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
133	138	'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
134	139	'INDEX idx_user_id (user_id)',
135	140	'INDEX idx_form_id_created_at_status (form_id, created_at, status)', // Composite index for performance improvements.
	141	+ // Leads on the two columns Forms_Data::get_editing_submitter_ids()
	142	+ // filters. idx_user_id alone cannot serve it -- created_at is not in it,
	143	+ // and no other index leads on created_at -- so that lookup was a range
	144	+ // scan with a row read per row plus a temp table for DISTINCT, on every
	145	+ // Forms-list render.
	146	+ 'INDEX idx_user_id_created_at (user_id, created_at)',
136	147	];
137	148	}
138	149
139	150	/**
		@@ -145,8 +156,10 @@
145	156	'type VARCHAR(20) AFTER status',
146	157	'extras LONGTEXT AFTER status',
147	158	'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
148	159	'INDEX idx_user_id (user_id)',
	160	+ // Note: @since 2.12.7 -- Covers the conversion rate's submitter lookup.
	161	+ 'INDEX idx_user_id_created_at (user_id, created_at)',
149	162	];
150	163	}
151	164
152	165	/**
		@@ -175,20 +188,22 @@
175	188
176	189	/**
177	190	* Add a new log entry.
178	191	*
179		- * @param string $title The title of the log entry.
180		- * @param string[] $messages Optional. An array of messages to include in the log entry. Default is an empty array.
	192	+ * @param string $title The title of the log entry.
	193	+ * @param array<string> $messages Optional. An array of messages to include in the log entry. Default is an empty array.
181	194	* @since 0.0.10
182	195	* @return int|null The key of the newly added log entry, or null if the log could not be added.
183	196	*/
184	197	public function add_log( $title, $messages = [] ) {
185		- $this->logs[] = [
	198	+ $log = [
186	199	'title' => Helper::get_string_value( trim( $title ) ),
187	200	'messages' => Helper::get_array_value( $messages ),
188		- 'timestamp' => time(),
	201	+ 'timestamp' => current_time( 'mysql' ), //phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp -- Using current_time() to match the WordPress timezone.
189	202	];
190	203
	204	+ $this->logs = array_merge( [ $log ], $this->logs );
	205	+
191	206	return $this->get_last_log_key();
192	207	}
193	208
194	209	/**
		@@ -193,11 +208,11 @@
193	208
194	209	/**
195	210	* Update an existing log entry.
196	211	*
197		- * @param int $log_key The key of the log entry to update.
198		- * @param string|null $title Optional. The new title for the log entry. If null, the title will not be changed.
199		- * @param string[] $messages Optional. An array of new messages to add to the log entry.
	212	+ * @param int|null $log_key The key of the log entry to update.
	213	+ * @param string|null $title Optional. The new title for the log entry. If null, the title will not be changed.
	214	+ * @param array<string> $messages Optional. An array of new messages to add to the log entry.
200	215	* @since 0.0.10
201	216	* @return int|null The key of the updated log entry, or null if the log entry does not exist.
202	217	*/
203	218	public function update_log( $log_key, $title = null, $messages = [] ) {
		@@ -214,8 +229,18 @@
214	229	return $log_key;
215	230	}
216	231
217	232	/**
	233	+ * Resets logs to zero.
	234	+ *
	235	+ * @since 1.3.0
	236	+ * @return void
	237	+ */
	238	+ public function reset_logs() {
	239	+ $this->logs = [];
	240	+ }
	241	+
	242	+ /**
218	243	* Retrieve all log entries.
219	244	*
220	245	* @since 0.0.10
221	246	* @return array<array<string,mixed>>
		@@ -248,9 +273,18 @@
248	273	// Add default logs if no logs provided.
249	274	$data['logs'] = $instance->get_logs();
250	275	}
251	276
252		- return $instance->use_insert( $data );
	277	+ $result = $instance->use_insert( $data );
	278	+
	279	+ if ( ! $result ) {
	280	+ // A failed entries write is the live-drop signal issue #3084 describes: the
	281	+ // table can have been dropped after being cached as present. Drop that cache
	282	+ // so the next admin load re-checks instead of trusting a stale answer.
	283	+ \SRFM\Inc\Database\Register::flush_entries_table_cache();
	284	+ }
	285	+
	286	+ return $result;
253	287	}
254	288
255	289	/**
256	290	* Update an entry by entry id.
		@@ -263,8 +297,14 @@
263	297	public static function update( $entry_id, $data = [] ) {
264	298	if ( empty( $entry_id ) ) {
265	299	return false;
266	300	}
	301	+
	302	+ if ( isset( $data['logs'] ) ) {
	303	+ // Add logs from the current cache at the very last moment so that we don't tax the performance.
	304	+ $data['logs'] = array_merge( Helper::get_array_value( $data['logs'] ), Helper::get_array_value( self::get( $entry_id )['logs'] ) );
	305	+ }
	306	+
267	307	return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
268	308	}
269	309
270	310	/**
		@@ -274,8 +314,12 @@
274	314	* @since 0.0.13
275	315	* @return int|false The number of rows deleted, or false on error.
276	316	*/
277	317	public static function delete( $entry_id ) {
	318	+ // Add action before deleting the entry.
	319	+ do_action( 'srfm_before_delete_entry', $entry_id );
	320	+
	321	+ // Delete the entry.
278	322	return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
279	323	}
280	324
281	325	/**
		@@ -309,39 +353,29 @@
309	353	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
310	354	* @type string $orderby The column by which to order the results. Default is 'created_at'.
311	355	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
312	356	* }
	357	+ * @param bool $set_limit Whether to set the limit on the query. Default is true.
313	358	*
314	359	* @since 0.0.13
315	360	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
316	361	*/
317		- public static function get_all( $args = [] ) {
318		- $_args = wp_parse_args(
319		- $args,
320		- [
321		- 'where' => [],
322		- 'limit' => 10,
323		- 'offset' => 0,
324		- 'orderby' => 'created_at',
325		- 'order' => 'DESC',
326		- ]
327		- );
328		- return self::get_instance()->get_results(
329		- $_args['where'],
330		- '*',
331		- [
332		- sprintf( 'ORDER BY `%1$s` %2$s', Helper::get_string_value( esc_sql( $_args['orderby'] ) ), Helper::get_string_value( esc_sql( $_args['order'] ) ) ),
333		- sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) ),
334		- ]
335		- );
	362	+ public static function get_all( $args = [], $set_limit = true ) {
	363	+ /**
	364	+ * Refactored the get_all method to use the get_records_by_args method from the Base class.
	365	+ * Moved the common logic inside the Base class so it can be reused by other tables as well.
	366	+ *
	367	+ * @since 1.13.0
	368	+ */
	369	+ return self::get_instance()->get_records_by_args( $args, $set_limit );
336	370	}
337	371
338	372	/**
339	373	* Get the total count of entries by status.
340	374	*
341		- * @param string $status The status of the entries to count.
342		- * @param int|null $form_id The ID of the form to count entries for.
343		- * @param array<string,mixed> $where_clause Additional where clause to add to the query.
	375	+ * @param string $status The status of the entries to count.
	376	+ * @param int|null $form_id The ID of the form to count entries for.
	377	+ * @param array<mixed> $where_clause Additional where clause to add to the query.
344	378	* @since 0.0.13
345	379	* @return int The total number of entries with the specified status.
346	380	*/
347	381	public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
		@@ -380,8 +414,50 @@
380	414	}
381	415	}
382	416
383	417	/**
	418	+ * Get the total number of entries created after the given timestamp.
	419	+ *
	420	+ * @param int $timestamp Timestamp in seconds.
	421	+ * @param int $form_id Optional. The ID of the form to count entries for. Default 0 for all forms.
	422	+ * @since 1.7.3
	423	+ * @return int Total number of entries created after the timestamp.
	424	+ */
	425	+ public static function get_entries_count_after( $timestamp, $form_id = 0 ) {
	426	+ $timestamp = absint( $timestamp );
	427	+
	428	+ if ( ! $timestamp ) {
	429	+ return self::get_total_entries_by_status( 'all', $form_id );
	430	+ }
	431	+
	432	+ global $wpdb;
	433	+
	434	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct DB access is required here to get the most accurate server time for menu badge logic. Caching is not suitable as this is used for real-time admin notifications.
	435	+ $mysql_time = $wpdb->get_var( 'SELECT NOW()' );
	436	+
	437	+ // Convert to timestamps.
	438	+ $mysql_timestamp = strtotime( $mysql_time );
	439	+ $php_timestamp = time();
	440	+
	441	+ // Offset between MySQL and PHP.
	442	+ $offset_seconds = $mysql_timestamp - $php_timestamp;
	443	+
	444	+ $adjusted_timestamp = $timestamp + $offset_seconds;
	445	+
	446	+ $where_clause = [
	447	+ [
	448	+ [
	449	+ 'key' => 'created_at',
	450	+ 'compare' => '>',
	451	+ 'value' => gmdate( 'Y-m-d H:i:s', $adjusted_timestamp ),
	452	+ ],
	453	+ ],
	454	+ ];
	455	+
	456	+ return self::get_total_entries_by_status( 'all', $form_id, $where_clause );
	457	+ }
	458	+
	459	+ /**
384	460	* Get the available months for entries.
385	461	*
386	462	* @param array<string,mixed> $where_clause Additional where clause to add to the query.
387	463	* @since 0.0.13
		@@ -403,6 +479,144 @@
403	479	$months[ $result['month_value'] ] = $result['month_label'];
404	480	}
405	481	}
406	482	return $months;
	483	+ }
	484	+
	485	+ /**
	486	+ * Get all the entry ID's for a form.
	487	+ * The data is used for checking unique field validation.
	488	+ *
	489	+ * @param int $form_id The ID of the form to fetch entry IDs for.
	490	+ * @since 1.0.0
	491	+ * @return array<mixed> An array of entry IDs.
	492	+ */
	493	+ public static function get_all_entry_ids_for_form( $form_id ) {
	494	+ return self::get_instance()->get_results(
	495	+ [ 'form_id' => $form_id ],
	496	+ 'ID',
	497	+ [
	498	+ 'ORDER BY ID DESC',
	499	+ ]
	500	+ );
	501	+ }
	502	+
	503	+ /**
	504	+ * Check if any non-trashed entry for a given form contains a specific field value.
	505	+ *
	506	+ * Uses a single SQL query with JSON_EXTRACT on the form_data column
	507	+ * instead of loading all entries into PHP. Stops at the first match.
	508	+ *
	509	+ * SECURITY INVARIANT — do not relax the key matching. The only caller is the
	510	+ * unauthenticated uniqueness check (Form_Submit::field_unique_validation()), which
	511	+ * allows a probe only for fields the form marks unique. That restriction holds
	512	+ * because the lookup is anchored to the EXACT submitted key: a stored form_data key
	513	+ * always embeds its own block ID, so a key that resolves to field X can only carry
	514	+ * X's block ID. Matching on block ID instead, or switching to LIKE / JSON_SEARCH,
	515	+ * would break that anchoring and widen the probe beyond the allowlisted field.
	516	+ *
	517	+ * @param int $form_id The form ID to search within.
	518	+ * @param string $field_key The form_data JSON key to match against.
	519	+ * @param string $field_value The value to check for uniqueness.
	520	+ * @since 2.7.0
	521	+ * @return bool True if a duplicate exists, false otherwise.
	522	+ */
	523	+ public static function has_duplicate_field_value( $form_id, $field_key, $field_value ) {
	524	+ if ( empty( $form_id ) || empty( $field_key ) || '' === $field_value ) {
	525	+ return false;
	526	+ }
	527	+
	528	+ global $wpdb;
	529	+ $table_name = self::get_instance()->get_tablename();
	530	+
	531	+ // $wpdb->prepare() escapes this for SQL, but the key is interpolated into a JSON
	532	+ // path string, where a quote or backslash would change the path's meaning rather
	533	+ // than break the query. Neutralise both so the path can only ever be a single
	534	+ // quoted member access.
	535	+ $json_path = '$."' . str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $field_key ) . '"';
	536	+
	537	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-off existence check; table name from get_tablename() (not user input); caching not beneficial for uniqueness validation.
	538	+ $exists = $wpdb->get_var(
	539	+ $wpdb->prepare(
	540	+ "SELECT 1 FROM {$table_name} WHERE form_id = %d AND status != 'trash' AND JSON_UNQUOTE(JSON_EXTRACT(form_data, %s)) = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internally generated.
	541	+ $form_id,
	542	+ $json_path,
	543	+ $field_value
	544	+ )
	545	+ );
	546	+
	547	+ return null !== $exists;
	548	+ }
	549	+
	550	+ /**
	551	+ * Get form IDs associated with a list of entry IDs.
	552	+ * This method retrieves the distinct form IDs that are linked to the provided entry IDs.
	553	+ *
	554	+ * @param array<int> $entry_ids An array of entry IDs to fetch associated form IDs for.
	555	+ * @since 1.1.1
	556	+ * @return array<int> An array of form IDs.
	557	+ */
	558	+ public static function get_form_ids_by_entries( $entry_ids ) {
	559	+ if ( empty( $entry_ids ) || ! is_array( $entry_ids ) ) {
	560	+ return [];
	561	+ }
	562	+
	563	+ $results = self::get_instance()->get_results(
	564	+ [
	565	+ [
	566	+ [
	567	+ 'key' => 'ID',
	568	+ 'compare' => 'IN',
	569	+ 'value' => $entry_ids,
	570	+ ],
	571	+ ],
	572	+ ],
	573	+ 'DISTINCT form_id'
	574	+ );
	575	+
	576	+ return array_map( 'absint', array_column( $results, 'form_id' ) ); // Flatten the array.
	577	+ }
	578	+
	579	+ /**
	580	+ * Get the form data for a specific entry.
	581	+ *
	582	+ * @param int $entry_id The ID of the entry to get the form data for.
	583	+ * @since 1.0.0
	584	+ * @return array<string,mixed> An associative array representing the entry's form data.
	585	+ */
	586	+ public static function get_form_data( $entry_id ) {
	587	+ $result = self::get_instance()->get_results(
	588	+ [ 'ID' => $entry_id ],
	589	+ 'form_data'
	590	+ );
	591	+ return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['form_data'] ) : [];
	592	+ }
	593	+
	594	+ /**
	595	+ * Get the entry data for a specific entry.
	596	+ *
	597	+ * @param int $entry_id The ID of the entry to get the entry data for.
	598	+ * @since 1.8.0
	599	+ * @return array<string,mixed> An associative array representing the entry's data.
	600	+ */
	601	+ public static function get_entry_data( $entry_id ) {
	602	+ $result = self::get_instance()->get_results(
	603	+ [ 'ID' => $entry_id ],
	604	+ 'form_data, extras'
	605	+ );
	606	+ return isset( $result[0] ) && is_array( $result[0] ) ? $result[0] : [];
	607	+ }
	608	+
	609	+ /**
	610	+ * {@inheritDoc}
	611	+ *
	612	+ * Restricts orderable columns to indexed, semantically meaningful fields.
	613	+ * Excludes LONGTEXT blob columns (form_data, submission_info, notes, logs, extras)
	614	+ * to prevent full-table sorts on un-indexed columns.
	615	+ *
	616	+ * @since 2.6.0
	617	+ * @return array<string>
	618	+ */
	619	+ protected function get_allowed_orderby_columns() {
	620	+ return [ 'ID', 'id', 'form_id', 'user_id', 'status', 'type', 'created_at', 'updated_at' ];
407	621	}
408	622	}