Inserting a map

[bashilbers]

Doctrine\DBAL\Connection::insert() does this:

$query = 'INSERT INTO ' . $tableName
       . ' (' . implode(', ', $cols) . ')'
       . ' VALUES (' . implode(', ', $placeholders) . ')';

return $this->executeUpdate($query, array_values($data), $types);

I have the same basic table setup as you have in the readme.md:

$sm = $conn->getSchemaManager();

    $table = new \Doctrine\DBAL\Schema\Table('items');
    $objDefinition = array(
        'type' => \Crate\DBAL\Types\MapType::STRICT,
        'data' => array(
            new \Doctrine\DBAL\Schema\Column('id',  \Doctrine\DBAL\Types\Type::getType('integer'), array()),
            new \Doctrine\DBAL\Schema\Column('name',  \Doctrine\DBAL\Types\Type::getType('string'), array()),
        ),
    );
    $table->addColumn(
        'object_column', \Crate\DBAL\Types\MapType::NAME,
        array('platformOptions' => $objDefinition)
    );
    $sm->createTable($table);

Inserting a row with a map column:

$conn->insert('items', [
        $conn->quoteIdentifier('object_column')  => json_encode([
            'id' => 1,
            'name' => 'inner object test'
        ])
    ]);

Will result in the following exception:

DBALException: An exception occurred while executing 'INSERT INTO items ("object_column") VALUES (?)' with params ["{\"id\":1,\"name\":\"inner object test\"}"]:

SQLActionException[Validation failed for object_column: "'{"id":1,"name":"inner object test"}'" has a type that can't be implicitly cast to that of "items.object_column" (object)]

It looks like something in doctrine is stopping me from inserting this data.. any help?

[seut]

objects and array column types are unfortunately currently not supported at crate-pdo.
we'll try to add this feature asap and will notify you.
btw. json_encode the map would be wrong anyway because this would result in a json string.

[bashilbers]

Any thoughts on how it will work when it's done? Do the object and map type work at ORM level?

[seut]

fixed through 7d6e95f.
I've released 0.0.4 just now.

In your example, following should work now (note the list of data types argument):

$conn->insert('items', [
        $conn->quoteIdentifier('object_column')  => [
            'id' => 1,
            'name' => 'inner object test'
        ]
    ], [\Crate\PDO\PDO::PARAM_OBJECT]);

[bashilbers]

unfortunately it doesn't work, I get the following exception:

 DBALException: An exception occurred while executing 'INSERT INTO items ("object_column") VALUES (?)' with params [{"id":1,"name":"inner object test"}]:

SQLActionException[Column 'object_column.id' unknown]

I'm on doctrine/dbal 2.3.5 and crate/crate-dbal 0.0.4

update: the insert does work when I set the object_column to DYNAMIC instead of STRICT

[seut]

hm you're right, the table creation does not work properly, it does not create the map's sub-columns.
will check this.
meanwhile you could work around it by defining the map column type as \Crate\DBAL\Types\MapType::DYNAMIC, so unknown map keys will be created dynamically while inserting.

[seut]

after I reproduced your problem, I've found the issue:

sub-columns of a MapType must be declared under the name fields not data.
so this map column declaration will work:

$objDefinition = array(
        'type' => \Crate\DBAL\Types\MapType::STRICT,
        'fields' => array(
            new \Doctrine\DBAL\Schema\Column('id',  \Doctrine\DBAL\Types\Type::getType('integer'), array()),
            new \Doctrine\DBAL\Schema\Column('name',  \Doctrine\DBAL\Types\Type::getType('string'), array()),
        ),
    );