diff options
| -rw-r--r-- | it_dbi.class | 53 | ||||
| -rwxr-xr-x | test/it_dbi.t | 36 |
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 |