← All changes | inc/database/tables/entries.php +435 -10 0.0.11 → 2.12.8 View file →

		@@ -32,8 +32,15 @@
32	32	*/
33	33	protected $table_suffix = 'entries';
34	34
35	35	/**
	36	+ * {@inheritDoc}
	37	+ *
	38	+ * @var int
	39	+ */
	40	+ protected $table_version = 2;
	41	+
	42	+ /**
36	43	* Current logs.
37	44	*
38	45	* @var array<array<string,mixed>> $logs
39	46	* The structure of each log entry is:
		@@ -59,15 +66,24 @@
59	66	// Submitted form ID.
60	67	'form_id' => [
61	68	'type' => 'number',
62	69	],
63		- // Current entry status: ['read', 'unread'].
	70	+ // User ID.
	71	+ 'user_id' => [
	72	+ 'type' => 'number',
	73	+ 'default' => 0,
	74	+ ],
	75	+ // Current entry status: 'read', 'unread' and 'trash'.
64	76	'status' => [
65	77	'type' => 'string',
66	78	'default' => 'unread',
67	79	],
	80	+ // Entry's form type eg quiz, standard etc. Default empty or null means standard.
	81	+ 'type' => [
	82	+ 'type' => 'string',
	83	+ ],
68	84	// Submitted form data by user.
69		- 'user_data' => [
	85	+ 'form_data' => [
70	86	'type' => 'array',
71	87	'default' => [],
72	88	],
73	89	// Additional information about the current submitted data: i.e: Browser type, device etc.
		@@ -84,12 +100,83 @@
84	100	'logs' => [
85	101	'type' => 'array',
86	102	'default' => [],
87	103	],
	104	+ // Entry submitted date and time.
	105	+ 'created_at' => [
	106	+ 'type' => 'datetime',
	107	+ ],
	108	+ // Any misc extra data that needs to be saved.
	109	+ 'extras' => [
	110	+ 'type' => 'array',
	111	+ 'default' => [],
	112	+ ],
88	113	];
89	114	}
90	115
91	116	/**
	117	+ * {@inheritDoc}
	118	+ */
	119	+ public function get_columns_definition() {
	120	+ return [
	121	+ 'ID BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
	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.
	128	+ 'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
	129	+ 'form_data LONGTEXT', // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
	130	+ 'logs LONGTEXT',
	131	+ 'notes LONGTEXT',
	132	+ 'submission_info LONGTEXT',
	133	+ 'status VARCHAR(10)',
	134	+ 'type VARCHAR(20)', // Note: @since 0.0.13 -- We have added type column, it will have entry's form type eg quiz, standard etc.
	135	+ 'extras LONGTEXT',
	136	+ 'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
	137	+ 'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
	138	+ 'INDEX idx_form_id (form_id)', // Indexing for the performance improvements.
	139	+ 'INDEX idx_user_id (user_id)',
	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)',
	147	+ ];
	148	+ }
	149	+
	150	+ /**
	151	+ * {@inheritDoc}
	152	+ */
	153	+ public function get_new_columns_definition() {
	154	+ return [
	155	+ // Note: @since 0.0.13 -- We have added new columns `type`, `extras` and `user_id`.
	156	+ 'type VARCHAR(20) AFTER status',
	157	+ 'extras LONGTEXT AFTER status',
	158	+ 'user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 AFTER form_id',
	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)',
	162	+ ];
	163	+ }
	164	+
	165	+ /**
	166	+ * {@inheritDoc}
	167	+ */
	168	+ public function get_columns_to_rename() {
	169	+ return [
	170	+ // Note: @since 0.0.13 -- We have renamed `user_data` column to `form_data`.
	171	+ [
	172	+ 'from' => 'user_data',
	173	+ 'to' => 'form_data',
	174	+ ],
	175	+ ];
	176	+ }
	177	+
	178	+ /**
92	179	* Retrieve the key of the last log entry.
93	180	*
94	181	* @since 0.0.10
95	182	* @return int|null The key of the last log entry if logs exist, or null if no logs are present.
		@@ -101,20 +188,22 @@
101	188
102	189	/**
103	190	* Add a new log entry.
104	191	*
105		- * @param string $title The title of the log entry.
106		- * @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.
107	194	* @since 0.0.10
108	195	* @return int|null The key of the newly added log entry, or null if the log could not be added.
109	196	*/
110	197	public function add_log( $title, $messages = [] ) {
111		- $this->logs[] = [
	198	+ $log = [
112	199	'title' => Helper::get_string_value( trim( $title ) ),
113	200	'messages' => Helper::get_array_value( $messages ),
114		- 'timestamp' => time(),
	201	+ 'timestamp' => current_time( 'mysql' ), //phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp -- Using current_time() to match the WordPress timezone.
115	202	];
116	203
	204	+ $this->logs = array_merge( [ $log ], $this->logs );
	205	+
117	206	return $this->get_last_log_key();
118	207	}
119	208
120	209	/**
		@@ -119,11 +208,11 @@
119	208
120	209	/**
121	210	* Update an existing log entry.
122	211	*
123		- * @param int $log_key The key of the log entry to update.
124		- * @param string|null $title Optional. The new title for the log entry. If null, the title will not be changed.
125		- * @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.
126	215	* @since 0.0.10
127	216	* @return int|null The key of the updated log entry, or null if the log entry does not exist.
128	217	*/
129	218	public function update_log( $log_key, $title = null, $messages = [] ) {
		@@ -140,8 +229,18 @@
140	229	return $log_key;
141	230	}
142	231
143	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	+ /**
144	243	* Retrieve all log entries.
145	244	*
146	245	* @since 0.0.10
147	246	* @return array<array<string,mixed>>
		@@ -174,12 +273,57 @@
174	273	// Add default logs if no logs provided.
175	274	$data['logs'] = $instance->get_logs();
176	275	}
177	276
178		- return $instance->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;
179	287	}
180	288
181	289	/**
	290	+ * Update an entry by entry id.
	291	+ *
	292	+ * @param int $entry_id Entry ID.
	293	+ * @param array<string,mixed> $data Data to update.
	294	+ * @since 0.0.13
	295	+ * @return int|false The number of rows updated, or false on error.
	296	+ */
	297	+ public static function update( $entry_id, $data = [] ) {
	298	+ if ( empty( $entry_id ) ) {
	299	+ return false;
	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	+
	307	+ return self::get_instance()->use_update( $data, [ 'ID' => absint( $entry_id ) ] );
	308	+ }
	309	+
	310	+ /**
	311	+ * Delete an entry by entry id.
	312	+ *
	313	+ * @param int $entry_id Entry ID to delete.
	314	+ * @since 0.0.13
	315	+ * @return int|false The number of rows deleted, or false on error.
	316	+ */
	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.
	322	+ return self::get_instance()->use_delete( [ 'ID' => absint( $entry_id ) ], [ '%d' ] );
	323	+ }
	324	+
	325	+ /**
182	326	* Retrieve a specific entry from the database.
183	327	*
184	328	* @param int $entry_id The ID of the entry to retrieve.
185	329	* @since 0.0.10
		@@ -192,6 +336,287 @@
192	336	]
193	337	);
194	338
195	339	return isset( $results[0] ) ? Helper::get_array_value( $results[0] ) : [];
	340	+ }
	341	+
	342	+ /**
	343	+ * Retrieves a list of records based on the provided arguments.
	344	+ *
	345	+ * This method fetches results from the database, allowing for various
	346	+ * customization options such as filtering, pagination, and sorting.
	347	+ *
	348	+ * @param array<string,mixed> $args {
	349	+ * Optional. An array of arguments to customize the query.
	350	+ *
	351	+ * @type array $where An associative array of conditions to filter the results.
	352	+ * @type int $limit The maximum number of results to return. Default is 10.
	353	+ * @type int $offset The number of records to skip before starting to collect results. Default is 0.
	354	+ * @type string $orderby The column by which to order the results. Default is 'created_at'.
	355	+ * @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
	356	+ * }
	357	+ * @param bool $set_limit Whether to set the limit on the query. Default is true.
	358	+ *
	359	+ * @since 0.0.13
	360	+ * @return array<mixed> The results of the query, typically an array of objects or associative arrays.
	361	+ */
	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 );
	370	+ }
	371	+
	372	+ /**
	373	+ * Get the total count of entries by status.
	374	+ *
	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.
	378	+ * @since 0.0.13
	379	+ * @return int The total number of entries with the specified status.
	380	+ */
	381	+ public static function get_total_entries_by_status( $status = 'all', $form_id = 0, $where_clause = [] ) {
	382	+ switch ( $status ) {
	383	+ case 'all':
	384	+ $where_clause[] =
	385	+ [
	386	+ [
	387	+ 'key' => 'status',
	388	+ 'compare' => '!=',
	389	+ 'value' => 'trash',
	390	+ ],
	391	+ ];
	392	+ if ( 0 < $form_id ) {
	393	+ $where_clause[] = [
	394	+ [
	395	+ 'key' => 'form_id',
	396	+ 'compare' => '=',
	397	+ 'value' => $form_id,
	398	+ ],
	399	+ ];
	400	+ }
	401	+ return self::get_instance()->get_total_count( $where_clause );
	402	+ case 'unread':
	403	+ case 'trash':
	404	+ $where_clause[] = [
	405	+ [
	406	+ 'key' => 'status',
	407	+ 'compare' => '=',
	408	+ 'value' => $status,
	409	+ ],
	410	+ ];
	411	+ return self::get_instance()->get_total_count( $where_clause );
	412	+ default:
	413	+ return self::get_instance()->get_total_count();
	414	+ }
	415	+ }
	416	+
	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	+ /**
	460	+ * Get the available months for entries.
	461	+ *
	462	+ * @param array<string,mixed> $where_clause Additional where clause to add to the query.
	463	+ * @since 0.0.13
	464	+ * @return array<int|string, mixed>
	465	+ */
	466	+ public static function get_available_months( $where_clause = [] ) {
	467	+ $results = self::get_instance()->get_results(
	468	+ $where_clause,
	469	+ 'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
	470	+ [
	471	+ 'ORDER BY month_value ASC',
	472	+ ],
	473	+ false
	474	+ );
	475	+
	476	+ $months = [];
	477	+ foreach ( $results as $result ) {
	478	+ if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
	479	+ $months[ $result['month_value'] ] = $result['month_label'];
	480	+ }
	481	+ }
	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' ];
196	621	}
197	622	}