handle setting a dyncol to null by removing the field in dyncols

2 files changed, 22 insertions, 4 deletions

diff --git a/it_dbi.class b/it_dbi.class
index d9688cd..004c22d 100644
--- a/it_dbi.class
+++ b/it_dbi.class
@@ -264,8 +264,12 @@ function _set($tags, $force = false)
 		{
 			if (substr($field, 0, 1) === "-")
 				$newdyns[] = "'\$.$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);
+			else if ($force || isset($value) && isset($dyndata[$f]) ? strval($value) !== strval($dyndata[$f]) : $value !== $dyndata[$f] || !array_key_exists($f, $dyndata)) {
+				if (is_null($value))
+					$deldyns[] = "'\$.$f'";
+				else
+					$newdyns[] = "'\$.$f', " . $this->escape_string($value);
+			}
 
 			$alldyns[] = $this->escape_string($f) . ", " . (substr($field, 0, 1) === "-" ? $value : $this->escape_string($value));
 			$dyndata[$f] = $value;
@@ -280,8 +284,14 @@ function _set($tags, $force = false)
 	{
 		if ($force == "insert") # INSERT/REPLACE
 			$result[] = "dyncols = JSON_OBJECT(" . join(", ", (array)$alldyns) . ")";
-		else if ($newdyns)
-			$result[] = "dyncols = JSON_SET(" . ($this->_dyndata ? 'dyncols' : '"{}"') . ", " . join(", ", $newdyns) .")";
+		else if ($newdyns || $deldyns) {
+			$source = $this->_dyndata ? 'dyncols' : '"{}"';
+			if ($newdyns)
+				$source = "JSON_SET($source, " . join(', ', $newdyns) . ')';
+			if ($deldyns)
+				$source = "JSON_REMOVE($source, " . join(', ', $deldyns) . ')';
+			$result[] = "dyncols = $source";
+		}
 	}
 
 	$this->_writes += $result ? 1 : 0;
diff --git a/test/it_dbi.t b/test/it_dbi.t
index bf5d0f0..6b89db2 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -364,6 +364,14 @@ is($r->_dyndata, [], '_dyndata for record with empty dyncols should be empty');
 $r->update(['key3' => 'c']);
 is($r->key3, 'c', 'dynamic column for record with empty dyncols whould be correctly created');
 
+$r->clear(false);
+is($r->select(['-key1 IS NOT' => 'NULL']), 1, 'only one entry has a value for key1');
+is($r->select(['-key2 IS' => 'NULL']), 2, "two entries don't have a value for key1");
+$r->read(1);
+$r->update(['key1' => null]);
+is($r->key1, null, 'setting dyncol to null should make it be null');
+is($r->select(['-key1 IS NOT' => 'NULL']), 0, 'no entry should have a value for key1 anymore');
+
 # don't do dyncols stuff in where when we select form multiple tables (column names are unknown then)
 is($r->_where(['FROM' => 'it_dbi_test AS a, it_dbi_test AS b', '-a.ID' => 'b.ID']), 'WHERE a.ID = b.ID', "don't do JSON_EXTRACT when we have multiple tables in FROM");
 is($r->_where(['JOIN' => 'it_dbi_test AS b', '-a.ID' => 'b.ID']), 'WHERE a.ID = b.ID', "don't do JSON_EXTRACT when we JOIN");