/**
* @dataProvider dataProviderTestWriteRead
*
* @param string $name
* @param mixed $value
*/
public function testMariaEncoding($name, $value)
{
self::$resetFixture = false;
$bar = DynamicActiveRecord::encodeForMaria($value);
$bar = json_encode($bar);
$bar = json_decode($bar);
$bar = DynamicActiveRecord::decodeForMaria($bar);
$this->assertSame($value, DynamicActiveRecord::decodeForMaria(DynamicActiveRecord::encodeForMaria($value)));
}
/**
* Generate DB command from ActiveQuery with Maria-specific SQL for dynamic columns.
*
* User of DynamicActiveQuery should not normally need to use this method.
*
* #### History
*
* This implementation is the best I could manage. A dynamic attribute name
* can appear anywhere that a schema attribute name could appear (select, join, where, ...).
* It needs to be converted to the Maria SQL using COLUMN_CREATE('name', value, …)
* for accessing dynamic columns.
* Because SQL is statically-typed and there is no schema to refer to for dynamic
* attributes, the accessor SQL must specify the the dyn-col's type, e.g.
*
* ```sql
* WHERE COLUMN_GET(details, 'color' AS CHAR) = 'black'
* ```
*
* In which details is the blob column containing all the dynamic columns, 'color' is the
* name of a dynamic column that may or may not appear in any given table record, and
* CHAR means the value should be cast to CHAR before it is compared with 'black'.
* `COLUMN_GET(details, 'color' AS CHAR)` is the "accessor SQL".
*
* So I faced two problems:
*
* 1. How to identify a dynamic attribute name in an ActiveQuery?
* 2. How to choose the type to which it should be cast in the SQL?
*
* The design prociple of DynamicAR is "an attribute that isn't an instance variable,
* a column and doesn't have a magic get-/setter is assumed to be a dynamic attribute".
* So, in order to infer from the properties of an AQ instance the attribute names
* that need to be converted to dynamic column accessor SQL, I need to go through
* the AQ to identify
* all the column names and remove those in the schema. But I don't know how to
* identify column names in an AQ instance. Even if I did, there's problem 2.
*
* The only way I can imagine to infer datatype from an AQ instance is to look
* at the context. If the attribute is compared with a bound parameter, that's a clue.
* If it is being used in an SQL function, e.g. CONCAT(), or being compared with a
* schema column, that suggests something. But if it is on its own in a SELECT then I am
* stuck. Also stuck if it is compared with another dynamic attribute. This seems
* fundamentally intractible.
*
* So I decided that the user needs to help DynamicActiveQuery by distinguishing the names
* of dynamic attributes and by explicitly specifying the type. The format for this:
*
* (!name|type!)
*
* Omitting type implies the default type: CHAR. Children of dynamic attributes, i.e.
* array elements, are separated from parents with `.` (period), e.g.
* `(!address.country|CHAR!)`. (Spaces are not alowed around the `|`.) So a user can do:
*
* $blueShirts = Product::find()
* ->where(['category' => Product::SHIRT, '(!color!)' => 'blue'])
* ->all();
*
* $cheapShirts = Product::find()
* ->select(
* ['sale' => 'MAX((!cost|decimal(6,2)!), 0.75 * (!price.wholesale.12|decimal(6,2)!))']
* )
* ->where(['category' => Product::SHIRT])
* ->andWhere('(!price.retail.unit|decimal(6,2)!) < 20.00')
* ->all();
*
* The implementation is like db\Connection's quoting of [[string]] and {{string}}. Once
* the full SQL string is ready, `preg_repalce()` it. The regex pattern is a bit complex
* and the replacement callback isn't pretty either. Is there a better way to add to
* `$params` in the callback than this? And for the parameter placeholder counter `$i`?
*
* @param null|\yii\db\Connection $db The database connection
*
* @return \yii\db\Command the modified SQL statement
*/
public function createCommand($db = null)
{
/** @var DynamicActiveRecord $modelClass */
$modelClass = $this->modelClass;
if ($db === null) {
$db = $modelClass::getDb();
}
if ($this->sql === null) {
list($sql, $params) = $db->getQueryBuilder()->build($this);
} else {
$sql = $this->sql;
$params = $this->params;
}
$dynamicColumn = $modelClass::dynamicColumn();
$callback = function ($matches) use(&$params, $dynamicColumn) {
$type = !empty($matches[3]) ? $matches[3] : 'CHAR';
$sql = $dynamicColumn;
foreach (explode('.', $matches[2]) as $column) {
$placeholder = DynamicActiveRecord::placeholder();
$params[$placeholder] = $column;
$sql = "COLUMN_GET({$sql}, {$placeholder} AS {$type})";
}
return $sql;
};
$pattern = <<<'REGEXP'
% (`?) \(! \s*?
( [a-z_\x7f-\xff][a-z0-9_\x7f-\xff]* (?: \. [^.|\s]+)* )
(?: \| (binary (?:\(\d+\))? | char (?:\(\d+\))? | time (?:\(\d+\))? | datetime (?:\(\d+\))? | date
| decimal (?:\(\d\d?(?:,\d\d?)?\))? | double (?:\(\d\d?,\d\d?\))?
| int(eger)? | (?:un)? signed (?:\s+int(eger)?)?) )?
\s*? !\) \1 %ix
REGEXP;
$sql = preg_replace_callback($pattern, $callback, $sql);
return $db->createCommand($sql, $params);
}
