WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 6 months ago

#28591 new defect (bug)

dbDelta Non-literal DEFAULT not working (CURRENT_TIMESTAMP)

Reported by: sc0ttkclark Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Database Keywords: needs-patch
Focuses: Cc:
PR Number:

Description

Using dbDelta and any internal SQL values like CURRENT_TIMESTAMP won't work because dbDelta matches based on this regex:

"| DEFAULT '(.*?)'|i"

The block current looks like this:

if (preg_match("| DEFAULT '(.*?)'|i", $cfields[strtolower($tablefield->Field)], $matches)) {
	$default_value = $matches[1];
	if ($tablefield->Default != $default_value) {
		// Add a query to change the column's default value
		$cqueries[] = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->Field} SET DEFAULT '{$default_value}'";
		$for_update[$table.'.'.$tablefield->Field] = "Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}";
	}
}

I'm not sure what the best solution is for this, but perhaps it should be:

  1. Check if there is a default to change, if there is -- store default alter query, don't add to $cqueries[] yet
  2. Check if there is a field type change OR a default non-literal found, if there is -- use this CHANGE COLUMN query instead of the 'default' changing query in point 1

Does that sound like a good solution here?

Change History (3)

#1 @sc0ttkclark
5 years ago

Here's an idea for at line 1672 in upgrade.php, but it can be further improved for sure.

$cquery = null;

// Get the default value from the array
	//echo "{$cfields[strtolower($tablefield->Field)]}<br>";
if (preg_match("| DEFAULT '(.*?)'|i", $cfields[strtolower($tablefield->Field)], $matches)) {
	$default_value = $matches[1];
	if ($tablefield->Default != $default_value) {
		// Add a query to change the column's default value
		$cquery = "ALTER TABLE {$table} ALTER COLUMN {$tablefield->Field} SET DEFAULT '{$default_value}'";
		$for_update[$table.'.'.$tablefield->Field] = "Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}";
	}
}

// Check for default non-literals
if ( preg_match("| DEFAULT (\w*)|i", $cfields[strtolower($tablefield->Field)], $non_literal_matches) ) {
	if ( $tablefield->Default == $non_literal_matches[1] ) {
		$non_literal_matches = false;
	}
}

// Is actual field type different from the field type in query?
if ($tablefield->Type != $fieldtype || $non_literal_matches) {
	// Add a query to change the column type
	$cquery = "ALTER TABLE {$table} CHANGE COLUMN {$tablefield->Field} " . $cfields[strtolower($tablefield->Field)];

	if ( $tablefield->Type != $fieldtype ) {
		$for_update[$table.'.'.$tablefield->Field] = "Changed type of {$table}.{$tablefield->Field} from {$tablefield->Type} to " . $cfields[strtolower($tablefield->Field)];
	}
	else {
		$for_update[$table.'.'.$tablefield->Field] = "Changed definition of {$table}.{$tablefield->Field} to " . $cfields[strtolower($tablefield->Field)];
	}
}

if ( $cquery ) {
	$cqueries[] = $cquery;
}
Last edited 5 years ago by sc0ttkclark (previous) (diff)

#2 @SergeyBiryukov
5 years ago

  • Component changed from General to Database

#3 @chriscct7
4 years ago

  • Keywords needs-patch added
Note: See TracTickets for help on using tickets.