← All changes | inc/database/base.php +118 -6 0.0.1 → 1.6.1 View file →

		@@ -73,11 +73,21 @@
73	73	*
74	74	* @var bool
75	75	* @since 0.0.1
76	76	*/
77		- private $db_upgradable;
	77	+ protected $db_upgradable;
78	78
79	79	/**
	80	+ * Previously stored version of this table before the current upgrade
	81	+ * (0 when the table had no recorded version yet). Exposed so child classes
	82	+ * can gate one-time data migrations in run_data_migrations().
	83	+ *
	84	+ * @var int
	85	+ * @since 1.3.0
	86	+ */
	87	+ protected $prev_version = 0;
	88	+
	89	+ /**
80	90	* Current table database result caches.
81	91	*
82	92	* @var array<mixed>
83	93	* @since 0.0.1
		@@ -124,8 +134,30 @@
124	134	*/
125	135	abstract public function get_columns_definition();
126	136
127	137	/**
	138	+ * Columns to add if the table already exists. Override in child class.
	139	+ * Each entry is a bare SQL fragment: "column_name TYPE [constraints] [AFTER other_col]"
	140	+ * or "INDEX index_name (column)" for indexes.
	141	+ *
	142	+ * @return array<string>
	143	+ * @since 1.0.0
	144	+ */
	145	+ public function get_new_columns_definition() {
	146	+ return [];
	147	+ }
	148	+
	149	+ /**
	150	+ * Run one-time data migrations for this table after its columns are in
	151	+ * place. Called only while the table is upgradable (see register.php).
	152	+ * No-op by default; override in a child class and gate on $this->prev_version.
	153	+ *
	154	+ * @return void
	155	+ * @since 1.3.0
	156	+ */
	157	+ public function run_data_migrations() {}
	158	+
	159	+ /**
128	160	* Start the database upgrade process.
129	161	*
130	162	* @return void
131	163	* @since 0.0.1
		@@ -134,8 +166,10 @@
134	166	$versions = Helper::get_suredonation_option( self::VERSION_OPTION_KEY, [] );
135	167	$versions = is_array( $versions ) ? $versions : [];
136	168	$prev_version = ! empty( $versions[ $this->table_suffix ] ) ? absint( $versions[ $this->table_suffix ] ) : false;
137	169
	170	+ $this->prev_version = $prev_version ? (int) $prev_version : 0;
	171	+
138	172	if ( ! $prev_version ) {
139	173	$this->db_upgradable = true;
140	174	return;
141	175	}
		@@ -223,17 +257,95 @@
223	257
224	258	$columns_list = implode( ', ', $columns );
225	259	$wpdb = $this->wpdb;
226	260
227		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder -- Column definitions and charset are hardcoded DDL, not user input.
228		- $query = $wpdb->prepare( 'CREATE TABLE IF NOT EXISTS %i ( %1s ) %2s', $this->get_tablename(), $columns_list, $this->get_charset_collate() );
	261	+ // `$wpdb->prepare()` cannot be used here: it escapes single quotes in
	262	+ // the interpolated column-definition string, turning literal `DEFAULT ''`
	263	+ // into `DEFAULT \'\'`, which MySQL rejects with a syntax error. The
	264	+ // table name, column list, and charset are all hardcoded DDL (not user
	265	+ // input), so direct concatenation is safe.
	266	+ //
	267	+ // Column definitions must quote string literals with single quotes.
	268	+ // wpdb strips the composite `ANSI` sql_mode on connect but not a
	269	+ // standalone `ANSI_QUOTES`, under which `DEFAULT ""` parses as an empty
	270	+ // identifier and fails the statement permanently on every retry.
	271	+ $query = sprintf(
	272	+ 'CREATE TABLE IF NOT EXISTS `%s` ( %s ) %s',
	273	+ esc_sql( $this->get_tablename() ),
	274	+ $columns_list,
	275	+ $this->get_charset_collate()
	276	+ );
229	277
230		- if ( ! $query ) {
	278	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
	279	+ $result = $wpdb->query( $query );
	280	+
	281	+ if ( false === $result ) {
	282	+ $this->db_upgradable = false;
	283	+ }
	284	+
	285	+ return $result;
	286	+ }
	287	+
	288	+ /**
	289	+ * Add new columns to an existing table conditionally.
	290	+ *
	291	+ * Checks existing columns/indexes and only adds missing ones via ALTER TABLE.
	292	+ *
	293	+ * @param array<string> $new_columns Column definitions to add.
	294	+ * @return int|bool Result of ALTER query, or false.
	295	+ * @since 1.0.0
	296	+ */
	297	+ public function maybe_add_new_columns( $new_columns = [] ) {
	298	+ if ( ! $new_columns ) {
231	299	return false;
232	300	}
233	301
234		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
235		- $result = $wpdb->query( $query );
	302	+ if ( ! $this->db_upgradable ) {
	303	+ return false;
	304	+ }
	305	+
	306	+ $existing_columns = $this->get_columns();
	307	+
	308	+ if ( ! $existing_columns ) {
	309	+ // Table does not exist or is new.
	310	+ return false;
	311	+ }
	312	+
	313	+ $existing_indexes = $this->get_indexes();
	314	+ $alter_queries = [];
	315	+ $wpdb = $this->wpdb;
	316	+
	317	+ foreach ( $new_columns as $column_definition ) {
	318	+ // Check if this is an INDEX definition.
	319	+ preg_match( '/INDEX\s+(.*?)\s+\(/', $column_definition, $index_matches );
	320	+
	321	+ if ( ! empty( $index_matches[1] ) ) {
	322	+ if ( isset( $existing_indexes[ $index_matches[1] ] ) ) {
	323	+ continue; // Index already exists.
	324	+ }
	325	+ // Index definitions come from get_new_columns() — hardcoded DDL, not user input.
	326	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Hardcoded DDL fragment from get_new_columns().
	327	+ $alter_queries[] = 'ADD ' . $column_definition;
	328	+ continue;
	329	+ }
	330	+
	331	+ // Extract column name from definition.
	332	+ preg_match( '/(\w+)\s/', $column_definition, $column_matches );
	333	+ $column_name = $column_matches[1] ?? '';
	334	+
	335	+ if ( ! isset( $existing_columns[ $column_name ] ) ) {
	336	+ // Column definitions come from get_new_columns() — hardcoded DDL, not user input.
	337	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Hardcoded DDL fragment from get_new_columns().
	338	+ $alter_queries[] = 'ADD COLUMN ' . $column_definition;
	339	+ }
	340	+ }
	341	+
	342	+ if ( ! $alter_queries ) {
	343	+ return false;
	344	+ }
	345	+
	346	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- ALTER TABLE with prepared table name and hardcoded DDL fragments.
	347	+ $result = $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ', $this->get_tablename() ) . implode( ', ', $alter_queries ) . ';' );
236	348
237	349	if ( false === $result ) {
238	350	$this->db_upgradable = false;
239	351	}