rewrite dyncols to work around mariadb bug in JSON_SET; add write optimization

2 files changed, 63 insertions, 26 deletions

diff --git a/it_dbi.class b/it_dbi.class
index a6b7eee..426e653 100644
--- a/it_dbi.class
+++ b/it_dbi.class
@@ -50,6 +50,7 @@ class it_dbi
 	var $_link;        # DB link identifier (private)
 	var $_dbid;        # string identifying db connection. used for _state_purgeshared from outside
 	var $_affectedrows;# Affected rows (mysql_affected_rows() gets clobbered)
+	var $_writes;      # Number of (non-omittable) writes to sql using this instance
 
 
 /**
@@ -243,33 +244,49 @@ function _connect($p = array())
 
 /**
  * INTERNAL: construct SQL SET clause of changed values from tags array.
+ * $force = must write all fields, dont try to optimize
  */
-function _set($tags, $allfields = false)
+function _set($tags, $force = false)
 {
 	$result = array();
+	$dyndata = $this->_dyndata;
+
 	foreach((array)$tags as $field => $value)
 	{
-		if (!$this->_fields[trim($field, "-")] && it::match("," . trim($field, "-") . ",", $this->_fieldnames))
-			it::error("case mismatch on field " . trim($field, "-") . ", valid fields $this->_fieldnames");
+		$f = trim($field, "-");
+		if (!$this->_fields[$f] && it::match(",$f,", $this->_fieldnames))
+			it::error("case mismatch on field $f, valid fields $this->_fieldnames");
 
-		if (substr($field, 0, 1) === "-" && !$this->_fields[trim($field, "-")] && $this->_fields['dyncols'])
-			$dyns[] = $this->escape_string(trim($field, "-")) . ", $value";
-		else if (substr($field, 0, 1) === '-')		# Unquoted value (always added)
-			$result[] = substr($field, 1) . "=$value";
-		else if ($allfields || (isset($value) && isset($this->_data[$field]) ? strval($value) !== strval($this->_data[$field]) : $value !== $this->_data[$field] || !array_key_exists($field, $this->_data)))
+		if ($this->_p['charset'] == "utf8") # NOTE: Mysql charset is simply utf8, not utf-8
+			$value = it::any2utf8($value, "error in db-field $field");
+
+		if (!$this->_fields[$f] && $this->_fields['dyncols'])
 		{
-			if ($this->_p['charset'] == "utf8") # NOTE: Mysql charset is simply utf8, not utf-8
-				$value = it::any2utf8($value, "error in db-field $field");
+			if (substr($field, 0, 1) === "-")
+				$newdyns[] = $unquoteddyn = "'\$.$f', " . $value;
+			else if ($force || isset($value) && isset($dyndata[$f]) ? strval($value) !== strval($dyndata[$f]) : $value !== $dyndata[$f] || !array_key_exists($f, $dyndata))
+				$newdyns[] = "'\$.$f', " . $this->escape_string($value);
 
-			if (!$this->_fields[$field] && $this->_fields['dyncols'])
-				$dyns[] = $this->escape_string(trim($field, "-")) . ", " . (!isset($value) ? 'NULL' : (it::match('^\d+$', $value) ? $value : ($this->escape_string($value))));
-			else
-				$result[] = "`$field`=" . (isset($value) ? $this->escape_string($value) : 'NULL');
+			$alldyns[] = $this->escape_string($f) . ", " . (substr($field, 0, 1) === "-" ? $value : $this->escape_string($value));
+			$dyndata[$f] = $value;
 		}
+		else if (substr($field, 0, 1) === '-')		# Unquoted value (always added)
+			$result[] = substr($field, 1) . "=$value";
+		else if ($force || (isset($value) && isset($this->_data[$field]) ? strval($value) !== strval($this->_data[$field]) : $value !== $this->_data[$field] || !array_key_exists($field, $this->_data)))
+			$result[] = "`$field`=" . (isset($value) ? $this->escape_string($value) : 'NULL');
+	}
+
+	if ($alldyns)
+	{
+		if ($force == "insert") # INSERT/REPLACE
+			$result[] = "dyncols = JSON_OBJECT(" . join(", ", (array)$alldyns) . ")";
+		else if ($force || $unquoteddyn) # UPDATE with WHERE
+			$result[] = "dyncols = JSON_SET(dyncols, " . join(", ", $newdyns) .")";
+		else if ($newdyns) # UPDATE. Write all fields even if just one changed. FIXME 2018-10 UM this works around a JSON_SET bug
+			$result[] = "dyncols = " . $this->escape_string(json_encode($dyndata, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE));
 	}
 
-	if ($dyns)
-		$result[] = "dyncols = IF(dyncols IS NULL, JSON_OBJECT(" . join(", ", (array)$dyns) . "), JSON_SET(dyncols, ". join(", ", it::replace(["^'" => "'$."], $dyns)) ."))";
+	$this->_writes += $result ? 1 : 0;
 
 	return $result ? 'SET ' . implode(', ', $result) : '';
 }
@@ -663,7 +680,7 @@ function iterate()
 				foreach ($localizedfields as $field => $dummy)
 					unset($this->$field);
 
-			foreach (($t = $this->_data['dyncols']) ? (array)json_decode($t) : [] as $field => $value)
+			foreach (($t = $this->_data['dyncols']) ? ($this->_dyndata = (array)json_decode($t, true)) : [] as $field => $value)
 				$this->_data[$field] = $value;
 			unset($this->_data['dyncols']);
 
@@ -724,7 +741,7 @@ function insert($tags = array(), $command = "INSERT")
 	if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']]))
 		$tags[$this->_p['keyfield']] = md5(uniqid(mt_rand()));
 
-	$set = $this->_set($tags, true);
+	$set = $this->_set($tags, "insert");
 
 	if ($result = $this->query("$command INTO {$this->_p['table']} " . $set))
 	{
diff --git a/test/it_dbi.t b/test/it_dbi.t
index ab70fae..70d7f6f 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -320,14 +320,34 @@ is(
 #
 # Test saving/retrieving/matching values in dynamically created columns
 #
-$record = new it_dbi_test;
-$record->replace(['ID' => 5, 'key1' => "val0'"]);          is($record->key1, "val0'");
-$record->update(['key1' => "val1'"], ['key1' => "val0'"]); is($record->key1, "val1'");
-$record->update(['key2' => "val2'"]);                      is($record->key1, "val1'"); is($record->key2, "val2'");
-$record->update(['-key3' => "2*2"]);                       is($record->key1, "val1'"); is($record->key2, "val2'"); is($record->key3, 4);
-$record->update(['key1' => "val0'"]);                      is($record->key1, "val0'"); is($record->key2, "val2'"); is($record->key3, 4);
-$record->replace(['ID' => 6, 'key4' => "val4'"]);          is($record->key4, "val4'");
-$record->select(['key2' => "val2'"]);                      is($record->key2, "val2'"); is($record->key4, null, "clear previous fields");
+
+# REPLACE (without and with quoting)
+$r = new it_dbi_test;
+$r->replace(['ID' => 5, '-key1' => "2*2"]);           is($r->key1, 4);
+$r->replace(['ID' => 5, 'key1' => "val0'"]);          is($r->key1, "val0'");
+
+# UPDATE with WHERE (without and with quoting)
+$r->update(['-key1' => "2*2"], ['key1' => "val0'"]);  is($r->key1, 4);
+$r->update(['key1' => "val1'"], ['key1' => "4"]);     is($r->key1, "val1'");
+
+# UPDATE (without and with quoting)
+$r->update(['key1' => "val1'", 'key2' => "ö"]);       is($r->key1, "val1'"); is($r->key2, "ö"); # check mariadb bug
+$r->update(['-key2' => "1*2"]);                       is($r->key1, "val1'"); is($r->key2, 2);
+$r->update(['key2' => "val2'"]);                      is($r->key1, "val1'"); is($r->key2, "val2'");
+$r->update(['-key3' => "1*3"]);                       is($r->key1, "val1'"); is($r->key2, "val2'"); is($r->key3, 3);
+$r->update(['key1' => "val0'"]);                      is($r->key1, "val0'"); is($r->key2, "val2'"); is($r->key3, 3);
+$r->update(['key1' => "val/'"]);                      is($r->key1, "val/'"); # check json encoding
+
+# Check for mistakenly inherited fields
+$r->replace(['ID' => 6, 'key4' => "val4'"]);          is($r->key4, "val4'");
+$r->select(['key2' => "val2'"]);                      is($r->key2, "val2'"); is($r->key4, null, "cleared previous fields?");
+
+# Check removal of unnecessary writes
+$r->_writes = 0;
+$r->select(['ID' => 6]);
+$r->update(['key4' => "val4'"]);                      is($r->_writes, 0); is($r->key4, "val4'");
+$r->update(['key4' => "val4'", 'key5' => "val5'"]);   is($r->_writes, 1); is($r->key4, "val4'"); is($r->key5, "val5'");
+$r->update(['key4' => "val4'", 'key5' => "val5'"]);   is($r->_writes, 1); is($r->key4, "val4'"); is($r->key5, "val5'");
 
 #
 # Test tracked update