diff options
| -rw-r--r-- | README | 3 | ||||
| -rw-r--r-- | it_dbi.class | 29 | ||||
| -rwxr-xr-x | tests/it_dbi.t | 12 |
3 files changed, 38 insertions, 6 deletions
@@ -67,7 +67,8 @@ it_dbi - Database access ------------------------ The dbi object is a simple mysql interface. For each table in your database, a class is created automatically. Queries are encoded as arrays which ensures -correct quoting, see select(). Errors are by default reported within dbi. +correct quoting, see select(). Errors are by default reported within dbi. If +"dyncols" exists in schema, unknown columns are mapped to a dynamic column. Example: it_dbi::createclasses(); # Not necessary if using it_auto_prepend.php diff --git a/it_dbi.class b/it_dbi.class index 0e20c87..621b59e 100644 --- a/it_dbi.class +++ b/it_dbi.class @@ -245,16 +245,25 @@ function _set($tags, $allfields = false) $r = array(); foreach((array)$tags as $field => $value) { - if (substr($field, 0, 1) == '-') # Unquoted value (always added) + 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) $r[] = substr($field, 1)."=$value"; else if ($allfields || (isset($value) && isset($this->_data[$field]) ? strval($value) !== strval($this->_data[$field]) : $value !== $this->_data[$field])) { - if ($this->_p['charset'] == "utf8") # NOTE: Mysql charset is simply utf8, not utf-8 + if ($this->_p['charset'] == "utf8") # NOTE: Mysql charset is simply utf8, not utf-8 $value = it::any2utf8($value, "error in db-field $field"); - $r[] = "`$field`=".(isset($value) ? $this->escape_string($value) : 'NULL'); + + 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 + $r[] = "`$field`=".(isset($value) ? $this->escape_string($value) : 'NULL'); } } + if (($dyn = join(", ", (array)$dyns))) + $r[] = "dyncols = IF(dyncols IS NULL, COLUMN_CREATE($dyn), COLUMN_ADD(dyncols, $dyn))"; + return $r ? 'SET '.implode(', ', $r) : ''; } @@ -323,6 +332,9 @@ function _where($params = null, $dummy_link = null, $omit_where = false) $qval = $this->escape_string((string)$value); } + if ($this->_fields['dyncols'] && !$this->_fields[$field]) + $field = "COLUMN_GET(dyncols, " . $this->escape_string($field) . " AS CHAR)"; + switch ($op) { case 'NI': @@ -439,7 +451,7 @@ function tables($p = array()) */ function clear($pp = true) { - foreach ((array)$this->_fields + (array)$this->_localizedfields as $field => $dummy) + foreach ((array)$this->_fields + (array)$this->_localizedfields + (array)$this->_data as $field => $dummy) unset($this->$field); unset($this->_key, $this->_data); @@ -563,6 +575,8 @@ function select(/* $query = array|string, ... */) $what = $query['SELECT']; unset($query['SELECT']); } + if ($what == "*" && $this->_fields['dyncols']) + $what .= ", COLUMN_JSON(dyncols) AS _dyncols"; $join = $this->_p['table']; if (isset($query['JOIN']) || isset($query['FROM'])) # WARNING: this field gets abused for "tablename USE INDEX (fast2) @@ -621,6 +635,9 @@ function iterate() foreach ($localizedfields as $field => $dummy) unset($this->$field); + foreach (($t = $this->_data['_dyncols']) ? (array)json_decode($t) : [] as $field => $value) + $this->_data[$field] = $value; + foreach ($this->_data as $field => $value) $this->$field = (isset($value) && $this->_convertfunc[$field]) ? ($this->_data[$field] = $this->_convertfunc[$field]($value)) : $value; @@ -660,7 +677,9 @@ function iterate() /** - * Insert a record into table. Values are taken $tags. + * Insert a record into table. Values are taken assoc array $tags. Keys in $tags + * should contain row names unless "dyncols" exists in schema (they are stored as + * dynamic columns then). Keys with - prefix suppress quoting of values. * After inserting, all values are valid (record is read back). * Does not destroy internal state of last select() call * @param $tags key => value pairs to set diff --git a/tests/it_dbi.t b/tests/it_dbi.t index 006c4d8..961a747 100755 --- a/tests/it_dbi.t +++ b/tests/it_dbi.t @@ -10,6 +10,7 @@ $dbi->query('create temporary table it_dbi_test ( ID int not null auto_increment, x int, foo varchar(42), + dyncols LONGBLOB, primary key(ID) );'); @@ -315,3 +316,14 @@ is( array(1, "franz"), "localized field foobar_fr" ); + +# +# 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"); |