change storage format of dynamic columns to JSON

2 files changed, 13 insertions, 14 deletions

diff --git a/it_dbi.class b/it_dbi.class
index 79a56b2..2b0e782 100644
--- a/it_dbi.class
+++ b/it_dbi.class
@@ -268,8 +268,8 @@ function _set($tags, $allfields = false)
 		}
 	}
 
-	if (($dyn = join(", ", (array)$dyns)))
-		$result[] = "dyncols = IF(dyncols IS NULL, COLUMN_CREATE($dyn), COLUMN_ADD(dyncols, $dyn))";
+	if ($dyns)
+		$result[] = "dyncols = IF(dyncols IS NULL, JSON_OBJECT(" . join(", ", (array)$dyns) . "), JSON_SET(dyncols, ". join(", ", it::map('"\'$." . substr($v, 1)', $dyns)) ."))";
 
 	return $result ? 'SET ' . implode(', ', $result) : '';
 }
@@ -356,7 +356,7 @@ function _where($params)
 				}
 
 				if ($this->_fields['dyncols'] && !$this->_fields[$field])
-					$field = "COLUMN_GET(dyncols, " . $this->escape_string($field) . " AS CHAR)";
+					$field = "JSON_EXTRACT(dyncols, " . $this->escape_string('$.' . $field) . ")";
 
 				switch ($op)
 				{
@@ -612,8 +612,6 @@ function select(/* $query = array|string, ... */)
 		$what = $query['SELECT'];
 		unset($query['SELECT']);
 	}
-	if ($what == "*" && $this->_fields['dyncols'])
-		$what .= ", COLUMN_JSON(dyncols) AS _dyncols";
 
 	unset($this->_found_rows);
 	if (isset($query['CALC_FOUND_ROWS']) && $query['CALC_FOUND_ROWS'])
@@ -665,9 +663,9 @@ 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']) ? (array)json_decode($t) : [] as $field => $value)
 				$this->_data[$field] = $value;
-			unset($this->_data['_dyncols'], $this->_data['dyncols']);
+			unset($this->_data['dyncols']);
 
 			foreach ($this->_data as $field => $value)
 				$this->$field = (isset($value) && $this->_convertfunc[$field]) ? ($this->_data[$field] = $this->_convertfunc[$field]($value)) : $value;
diff --git a/test/it_dbi.t b/test/it_dbi.t
index 6a37637..ab70fae 100755
--- a/test/it_dbi.t
+++ b/test/it_dbi.t
@@ -10,7 +10,7 @@ $dbi->query('create temporary table it_dbi_test (
 	ID int not null auto_increment,
 	x int,
 	foo varchar(42),
-	dyncols LONGBLOB,
+	dyncols JSON,
 	primary key(ID)
 );');
 
@@ -321,12 +321,13 @@ is(
 # Test saving/retrieving/matching values in dynamically created columns
 #
 $record = new it_dbi_test;
-$record->replace(['ID' => 5, 'key1' => "val1"]); 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");
+$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");
 
 #
 # Test tracked update