tom-- / yii2-dynamic-ar Goto Github PK

View Code? Open in Web Editor NEW

57.0 57.0 16.0 1.48 MB

An extension to add NoSQL-like documents to Yii 2 Framework's Active Record ORM.

License: ISC License

PHP 100.00%

yii2-dynamic-ar's People

Contributors

Stargazers

Watchers

Forkers

cebe fernandezekiel mata-man juanmacias erickskrauch gvasilopulos enigmatix sankam-nikolya vuongminh-xx khvalov bokinga genhir ivanchejam clairebrione my6uot9

yii2-dynamic-ar's Issues

unit tests broken

@tom-- moved one of @gvasilopulos issue from #17

Hello and sorry in advance for the length of this ,unfortunately I could not manage to run the tests. I did edit tests/data/config.php I left only postgres and adjusted db/username/pass . I have to say I've never worked with tests so it is all kind of hazy in my head. running phpunit tests/unit/DynamicActiveRecordPgsqlTest.php get's skipped all together. Running phpunit -c phpunit.xml returns this
123) tests\unit\DynamicActiveRecordTest::testFindLazyVia
Exception: driverName is not set for a DatabaseTestCase. on all the failed tests (140 of them and skips another 247) .
It probably does not connect to the db but I cannot figure how to do this in the test env.
Notice that I cloned the extension, runned composer update, cloned the tests from yii2 master
(for some reason they are not getting installed with yii2-dev package) and that is the test enviroment .
Let me know if I have to setup an yii basic or advanced app to do this (this is what I suspect).

Make getDirtyAttributes detect updated dynamic fields

Or at least just mark the field we use for dynamic fields as 'dirty'.

It will be useful in many places as for example in TimestampBehavior, that updates timestamps only if model has dirty attributes.

What do you think @tom-- ?

NFR: Support MySQL 5.7 JSON data type.

https://dev.mysql.com/doc/refman/5.7/en/json.html

If a dynamic column name starts with number it does not work.is there a solution?
example :
$product = new Product([ 'sku' => 5463, 'upc' => '234569', 'price' => 4.99, 'title' => 'Clue-by-four', 'description' => 'Used for larting lusers or constructing things', 'dimensions' => [ 'unit' => 'inch', 'width' => 4, 'height' => 2, 'length' => 20, ], 'material' => 'wood', ]); $product->save();

if dimensions start with number '52dimensions' it works only in insert but in where close it does not work and I get SQL syntax error near '!)'.

how to search for unknown key(auto generated array index)

hi tom,
user post form with value like this:

product[name]='name';
product[color][0]='red';
product[color][1]='blue';

i save colors to dynamic field and want to search for all

product where color is blue

product where color is blue and/or black

how to write query for this?
note: i tested find_in_set and locate(if store colors as comma seperated list), but performance and multi value search issue.
thanks and sorry for very poorly en :(

Failed to save dynamic attributes from POST

If add to from dynamic field e.g:

$form->field($product, 'dynamic_attribute_name')
                ->dropDownList(explode(',', $attribute->variants), ['required' => $attribute->required])
                ->label($attribute->title);

those attributes will not saved.

PHP identifier restriction on attribute name is spurious

The restriction on attribute names that they should be (start with) a string that would be a valid PHP identifier, is spurious. It's not the way PHP behaves. Get rid of it.

$n = '123';
$$n = 'xyz';
var_dump($$n); 
>>> string(3) "xyz"
$o = new StdClass;
$o->$n = 'abd';
var_dump($o);
    >>> class stdClass#1 (1) {
      public $123 =>
      string(3) "abd"
    }

Don`t work with Geometry data (like mysql geometry or point)

i use it

for save
base64_encode(serialize($data))
for load
unserialize(base64_decode($data)); for fixed

DynamicActiveQuery::prepare() should only select from current table

Hi,

I came across this issue when integrating DynamicActiveRecord into my project. I use scopes to restrict data access for the current user, and these scopes LEFT JOIN a secondary table to ensure that access is permitted.

Unfortunately, if this second table shares column names with the first (eg. id, name), it is these values from the second table that get assigned to the returned model.

The native Yii2 ActiveQuery class handles this by using SELECT tableAlias.* instead of SELECT *

To illustrate another way:

Table 1 - User has one record:

id	name
1	joel

Table 2 - Account has one record:

id	name	details
1	mad max	null

Example:

$user = User::findOne(1); echo $user->name; // prints 'mad max', should print 'joel'

The fix would ideally be completed at line 110 in DynamicActiveQuery like so:

    $isDefault = false;

    if (empty($this->select)) {
        list(, $alias) = $this->getTableNameAndAlias();
        $this->select = ["$alias.*"];
        $isDefault = true;

    }

    if (is_array($this->select) && $isDefault) {
        $db = $modelClass::getDb();
        $this->select[$this->_dynamicColumn] =
            'COLUMN_JSON(' . $db->quoteColumnName($this->_dynamicColumn) . ')';
    }

And by copying the getTableNameAndAlias from the native implementation in ActiveQuery

private function getTableNameAndAlias()
{
    if (empty($this->from)) {
        /* @var $modelClass ActiveRecord */
        $modelClass = $this->modelClass;
        $tableName = $modelClass::tableName();
    } else {
        $tableName = '';
        foreach ($this->from as $alias => $tableName) {
            if (is_string($alias)) {
                return [$tableName, $alias];
            } else {
                break;
            }
        }
    }

    if (preg_match('/^(.*?)\s+({{\w+}}|\w+)$/', $tableName, $matches)) {
        $alias = $matches[2];
    } else {
        $alias = $tableName;
    }

    return [$tableName, $alias];
}

If you accept the above and it sounds agreeable, I am happy to prepare a pull request for it.

Error when use count query

Item::find()->distinct()->count();

Error:

Column already exists: 1060 Duplicate column name 'dynamic column'

How to install this extension via composer?

Try this command : composer require tom--/yii2-dynamic-ar .
But composer wrote error below: [InvalidArgumentException]
Could not find package tom--/yii2-dynamic-ar at any version for your minimu
m-stability (stable). Check the package spelling or your minimum-stability
Whats wrong? Help me please.

use of parent::

Refactor DynamicActiveRecord so that methods use parent:: only to call the parent of the same method, e.g. DynamicActiveRecord::getAttribute() is allowed to call parent::getAttribute() but not parent::__get().

Does this improve maintainability

Is this feasible?

any thoughts @djagya ?

remove the check whether an attribute exist before setting it in setAttributes()

trying to implement pgsqlencoder

On the "actual" thing , some info on what happens
running this with the current implementation

    $person = new Person([
        'person_fname' => 'lapo',
        'person_lname' => 'kapo',
        'phone1' => '2810222333',
        //  'comments'=>[
        'comments.tags' => ["tag1" => 1, "tag2" => 3, "tag3" => ["trialari" => "lari", "tralalalali" => "lolo"]],
        'comments.title' => "this title",
        'comments.author' => "thisauthor",
        'comments.lalalal.la.la' => "lalala11",
        'comments.binstring' => "\x00\x01\x02\x03\x04\x05\x06\x07\x08\x09\x0a\x0b\x0c\x0d\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a\x1b\x1c\x1d\x1e\x1f\x20\x21\x22\x23\x24\x25\x26\x27\x28\x29\x2a\x2b\x2c\x2d\x2e\x2f\x30\x31\x32\x33\x34\x35\x36\x37\x38\x39\x3a\x3b\x3c\x3d\x3e\x3f\x40\x41\x42\x43\x44\x45\x46\x47\x48\x49\x4a\x4b\x4c\x4d\x4e\x4f\x50\x51\x52\x53\x54\x55\x56\x57\x58\x59\x5a\x5b\x5c\x5d\x5e\x5f\x60\x61\x62\x63\x64\x65\x66\x67\x68\x69\x6a\x6b\x6c\x6d\x6e\x6f\x70\x71\x72\x73\x74\x75\x76\x77\x78\x79\x7a\x7b\x7c\x7d\x7e\x7f\x80\x81\x82\x83\x84\x85\x86\x87\x88\x89\x8a\x8b\x8c\x8d\x8e\x8f\x90\x91\x92\x93\x94\x95\x96\x97\x98\x99\x9a\x9b\x9c\x9d\x9e\x9f\xa0\xa1\xa2\xa3\xa4\xa5\xa6\xa7\xa8\xa9\xaa\xab\xac\xad\xae\xaf\xb0\xb1\xb2\xb3\xb4\xb5\xb6\xb7\xb8\xb9\xba\xbb\xbc\xbd\xbe\xbf\xc0\xc1\xc2\xc3\xc4\xc5\xc6\xc7\xc8\xc9\xca\xcb\xcc\xcd\xce\xcf\xd0\xd1\xd2\xd3\xd4\xd5\xd6\xd7\xd8\xd9\xda\xdb\xdc\xdd\xde\xdf\xe0\xe1\xe2\xe3\xe4\xe5\xe6\xe7\xe8\xe9\xea\xeb\xec\xed\xee\xef\xf0\xf1\xf2\xf3\xf4\xf5\xf6\xf7\xf8\xf9\xfa\xfb\xfc\xfd\xfe\xff"
        //],
        // 'comments.author.thisauthor'=>["tralala","lalala"]

    ]);
    $person->save();

returns this

SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $7
The SQL being executed was:

INSERT INTO "person" (
    "person_fname",
    "person_lname",
    "phone1",
    "created_at",
    "updated_at",
    "created_by",
    "updated_by",
    "person_id",
    "comments"
) VALUES (
    'lapo',
    'kapo',
    '2810222333',
    NOW(),
    NOW(),
    1,
    1,
    '00ce9db2-8dcb-4004-b132-d061a6188c93',
    json_build_object('comments',
        json_build_object('tags',
            json_build_object('tag1', 1, 'tag2', 3, 'tag3',
                json_build_object('trialari','lari','tralalalali','lolo')::jsonb
            )::jsonb,
            'title',
            'this title',
            'author',
            'thisauthor',
            'lalalal',
            json_build_object('la',
                json_build_object('la','lalala11')::jsonb
            )::jsonb,
            'binstring',
            'data:application/octet-stream;base64,AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh8gISIjJCUmJygpKissLS4vMDEyMzQ1Njc4OTo7PD0+P0BBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWltcXV5fYGFiY2RlZmdoaWprbG1ub3BxcnN0dXZ3eHl6e3x9fn+AgYKDhIWGh4iJiouMjY6PkJGSk5SVlpeYmZqbnJ2en6ChoqOkpaanqKmqq6ytrq+wsbKztLW2t7i5uru8vb6/wMHCw8TFxsfIycrLzM3Oz9DR0tPU1dbX2Nna29zd3t/g4eLj5OXm5+jp6uvs7e7v8PHy8/T19vf4+fr7/P3+/w=='
        )::jsonb
    )::jsonb
) RETURNING "person_id"

Error Info: Array
(
    [0] => 42P18
    [1] => 7
    [2] => ERROR:  could not determine data type of parameter $7
)

now if we do not call dynColSqlMaria (it is altered by nineinchnick to create jsonb_objects) and simply json_encode like this

public function encodeDynamicColumn($attributes)
{
    if (!$attributes) {
        return null;
    }

    $params = [];

    // todo For now we only have Maria. Add PgSQL and generic JSON.          static::encodeDynamicAttributeArray($attributes);
    $sql = json_encode($attributes); //simply encode attributes
    //   $sql = static::dynColSqlMaria($attributes, $params);
    $sql = '\'' . $sql . '\''; //simply add ' ' before and after so pg accepts the value
    return new \yii\db\Expression('(select CAST (' . $sql . ' AS JSONB))', $params);
    // return new \yii\db\Expression($sql,$params);     
}

it returns this and gets inserted in the db. I do not know what side effects there will be since I cannot run the tests I would like some lecturing on that part :-)
this is the info I could gather

{
    "comments": {
        "tags": {
            "tag1": 1,
            "tag2": 3,
            "tag3": {
                "trialari": "lari",
                "tralalalali": "lolo"
            }
        },
        "title": "this title",
        "author": "thisauthor",
        "lalalal": {
            "la": {
                "la": "lalala22"
            }
        },
        "binstring": "data:application/octet-stream;base64,AAECAwQFBgcICQoLDA0ODxAREhMUFRYXGBkaGxwdHh8gISIjJCUmJygpKissLS4vMDEyMzQ1Njc4OTo7PD0+P0BBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWltcXV5fYGFiY2RlZmdoaWprbG1ub3BxcnN0dXZ3eHl6e3x9fn+AgYKDhIWGh4iJiouMjY6PkJGSk5SVlpeYmZqbnJ2en6ChoqOkpaanqKmqq6ytrq+wsbKztLW2t7i5uru8vb6/wMHCw8TFxsfIycrLzM3Oz9DR0tPU1dbX2Nna29zd3t/g4eLj5OXm5+jp6uvs7e7v8PHy8/T19vf4+fr7/P3+/w=="
    }
}

Validation of nested attributes does not work

If you try to validate nested attributes, it does not work - so even your example is wrong:

public function rules()
    {
        return [['dimensions.length', 'double', 'min' => 0.0]];
    }

Validation of nested attributes can't work due to fact, that yii\validators\Validator uses $model->$attribute instead of $model->getAttribute($attribute) in validateAttribute($model, $attribute) method:

public function validateAttribute($model, $attribute)
    {
    	$result = $this->validateValue($model->$attribute);
        
        if (!empty($result)) {
            $this->addError($model, $attribute, $result[0], $result[1]);
        }
    }

How to overcome this issue? We could override the validateAttribute() method of yii\validators\Validator class in our own inhered class and create a Standalone Validator for every validator type we want to use for nested dynamic attributes.
Is there a better way?

fisture errors in yii db tests?

PHPUnit 4.8.34 by Sebastian Bergmann and contributors.

...............................................................  63 / 176 ( 35%)
.................................I............................. 126 / 176 ( 71%)
.......................E.E.E......................

Time: 21.23 seconds, Memory: 10.00MB

There were 3 errors:

1) tests\unit\DynamicActiveRecordTest::testPopulateRecordCallWhenQueryingOnParentClass
yii\base\InvalidConfigException: The table does not exist: animal

/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:299
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:330
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/BaseActiveRecord.php:461
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/BaseActiveRecord.php:303
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/tests/data/ar/Animal.php:31
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/base/Object.php:107
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/tests/framework/db/ActiveRecordTest.php:1118
/Volumes/Sites/dynamic-ar/vendor/phpunit/phpunit/phpunit:52

2) tests\unit\DynamicActiveRecordTest::testOptimisticLock
yii\base\InvalidConfigException: The table does not exist: {{%document}}

/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:299
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:320
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:176
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/BaseActiveRecord.php:107
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/tests/framework/db/ActiveRecordTest.php:1139
/Volumes/Sites/dynamic-ar/vendor/phpunit/phpunit/phpunit:52

3) tests\unit\DynamicActiveRecordTest::testBit
yii\base\InvalidConfigException: The table does not exist: {{%bit_values}}

/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:299
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:320
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/ActiveRecord.php:176
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/framework/db/BaseActiveRecord.php:107
/Volumes/Sites/dynamic-ar/vendor/yiisoft/yii2-dev/tests/framework/db/ActiveRecordTest.php:1240
/Volumes/Sites/dynamic-ar/vendor/phpunit/phpunit/phpunit:52

FAILURES!
Tests: 176, Assertions: 1146, Errors: 3, Incomplete: 1.

Unable to create record using associative array

I'm unable to use an associative array for the dynamic AR attributes.
Here is the table:

$this->createTable('{{%spreadsheet_item}}', [
    'id' => Schema::TYPE_PK,
    'parent_id' => Schema::TYPE_INTEGER . ' NOT NULL',
    'data' => 'LONGBLOB NOT NULL',
    'created_by' => Schema::TYPE_INTEGER . ' NOT NULL',
    'updated_by' => Schema::TYPE_INTEGER . ' NOT NULL',
    'alias_by' => Schema::TYPE_INTEGER . ' DEFAULT NULL',
    'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
    'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
    'alias_at' => Schema::TYPE_INTEGER . ' DEFAULT NULL',
], $tableOptions);

Also, my model has the static function dynamicColumn() returning 'data' as the column name and i also have these rules:

        return [
            [['parent_id'], 'required'],
            [['parent_id', 'created_by', 'updated_by', 'alias_by', 'created_at', 'updated_at', 'alias_at'], 'integer']
        ];

When trying to save by the associative array

$item = new SpreadsheetItem([
   'parent_id' => 0,
    'data' => [
        'first_name' => 'Bob'
    ]
]);
$item->save();

I receive this integrity constraint error :
Column 'data' cannot be null The SQL being executed was: INSERT INTOspreadsheet_item(parent_id,data,created_at,updated_at,created_by,updated_by,alias_at,alias_by) VALUES (0, NULL, 1437431869, 1437431869, 487, 487, 1437431869, NULL)

Float converts to string on save

PDO doesn't have a type for float so Yii uses string. So a PHP value that was a float is string by the time the DB receives the command.

It might be possible to correct this by adding
[as type] parts to the COLUMN_CREATE()
expression.

Nested columns in wrong charset in Maria 10.1

tests\unit\DynamicActiveRecordTest::testCustomColumns
tests\unit\DynamicActiveRecordTest::testCustomColumnsExpression

are failing because https://jira.mariadb.org/browse/MDEV-9429

In these tests the insert is

INSERT INTO `product` (id, name, dynamic_columns) VALUES (
    1,
    'product1',
    COLUMN_CREATE(
        'supplier_id', 1,
        'str', 'value1',
        'int', 123,
        'float', 123.456,
        'bool', TRUE,
        'null', null,
        'children', COLUMN_CREATE(
            'str', 'value1',
            'int', 123,
            'float', 123.456,
            'bool', TRUE,
            'null', null
        )
    ))

and the select is

SELECT *, 
    (
        COLUMN_GET(
            COLUMN_GET(`dynamic_columns`, 'children' AS char), 
            'int' AS char
        ) * 2
    ) AS customColumn, 
    COLUMN_JSON(`dynamic_columns`) AS `dynamic_columns` 
FROM `product` WHERE `name`='product1'

If the inner column_get is instead

            COLUMN_GET(`dynamic_columns`, 'children' AS binary)

then all should be well.

Hence it may be sufficient to get all columns deeper than level 1 AS binary.