public function load(CM_OutputStream_Interface $output)
{
$client = $this->getServiceManager()->getDatabases()->getMaster();
$query = new CM_Db_Query_Insert($client, 'cm_requestClientCounter', ['counter' => 0]);
$query->execute();
$this->_setLoaded(true);
}
/**
* @param string $key
* @param mixed $value
*/
public function set($key, $value)
{
$fields = array('key' => $key, 'value' => serialize($value));
$query = new CM_Db_Query_Insert($this->_getDatabaseClient(), 'cm_option', $fields, null, null, 'REPLACE');
$query->execute();
$this->_clearCache();
}
public function load(CM_OutputStream_Interface $output)
{
$dbClient = $this->getServiceManager()->getDatabases()->getMaster();
$values = Functional\map($this->_getAllTypes(), function ($className, $type) {
return [$type, $className];
});
$query = new CM_Db_Query_Truncate($dbClient, 'cm_tmp_classType');
$query->execute();
$query = new CM_Db_Query_Insert($dbClient, 'cm_tmp_classType', ['type', 'className'], $values, null, 'REPLACE');
$query->execute();
}
public function testAll()
{
// Null value insertion
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('bar' => null));
$this->assertSame('INSERT INTO `t``est` (`bar`) VALUES (NULL)', $query->getSqlTemplate());
$this->assertEquals(array(), $query->getParameters());
// Associative array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo1'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo1'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo2', 'bar2'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo2', 'bar2', 'fooX'), $query->getParameters());
// Scalar and array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', 'foo3');
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo3'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo4', 'foo5'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo4', 'foo5', 'fooX'), $query->getParameters());
// Array and array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo6'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo6'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo7', 'foo8'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo7', 'foo8'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo9', 'bar9'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'), array('foo' => 'fooX', 'bar' => 'barX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?,`bar` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo9', 'bar9', 'fooX', 'barX'), $query->getParameters());
// Scalar and array of array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo10')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo10'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo11', 'foo12'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo11', 'foo12', 'fooX'), $query->getParameters());
// Array and array of array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo13')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo13'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo14'), array('foo15')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo14', 'foo15'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo16', 'bar16')));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo16', 'bar16'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18', 'fooX'), $query->getParameters());
// Statement
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, null, 'INSERT IGNORE');
$this->assertSame('INSERT IGNORE INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
}
public function testAll()
{
// Null value insertion
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('bar' => null));
$this->assertSame('INSERT INTO `t``est` (`bar`) VALUES (NULL)', $query->getSqlTemplate());
$this->assertEquals(array(), $query->getParameters());
// Associative array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo1'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo1'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo2', 'bar2'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo2', 'bar2', 'fooX'), $query->getParameters());
// Scalar and array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', 'foo3');
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo3'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo4', 'foo5'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array('foo4', 'foo5'), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo4', 'foo5', 'fooX'), $query->getParameters());
// Array and array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo6'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo6'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array('foo7', 'foo8'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo7', 'foo8'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo9', 'bar9'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array('foo9', 'bar9'), array('foo' => 'fooX', 'bar' => 'barX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `foo` = ?,`bar` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo9', 'bar9', 'fooX', 'barX'), $query->getParameters());
// Scalar and array of array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo10')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo10'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo11', 'foo12'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', 'foo', array(array('foo11'), array('foo12')), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo11', 'foo12', 'fooX'), $query->getParameters());
// Array and array of array
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo13')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?)', $query->getSqlTemplate());
$this->assertEquals(array('foo13'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo'), array(array('foo14'), array('foo15')));
$this->assertSame('INSERT INTO `t``est` (`foo`) VALUES (?),(?)', $query->getSqlTemplate());
$this->assertEquals(array('foo14', 'foo15'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo16', 'bar16')));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo16', 'bar16'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?)', $query->getSqlTemplate());
$this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18'), $query->getParameters());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo', 'bar'), array(array('foo17', 'bar17'), array('foo18', 'bar18')), array('foo' => 'fooX'));
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE `foo` = ?', $query->getSqlTemplate());
$this->assertEquals(array('foo17', 'bar17', 'foo18', 'bar18', 'fooX'), $query->getParameters());
// Statement
$query = new CM_Db_Query_Insert(self::$_client, 't`est', array('foo' => 'foo2', 'bar' => 'bar2'), null, null, 'INSERT IGNORE');
$this->assertSame('INSERT IGNORE INTO `t``est` (`foo`,`bar`) VALUES (?,?)', $query->getSqlTemplate());
$query = new CM_Db_Query_Insert(self::$_client, 't`est', ['foo' => 'foo2', 'bar' => 'bar2'], null, ['id' => ['literal' => 'LAST_INSERT_ID(id)']]);
$this->assertSame('INSERT INTO `t``est` (`foo`,`bar`) VALUES (?,?) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(id)', $query->getSqlTemplate());
$exception = $this->catchException(function () use($query) {
new CM_Db_Query_Insert(self::$_client, 't`est', ['foo' => 'foo2', 'bar' => 'bar2'], null, ['id' => ['literal' => 'COUNT(*)']]);
});
$this->assertInstanceOf('CM_Exception', $exception);
$this->assertSame('Unescaped Value is not whitelisted', $exception->getMessage());
}
/**
* @param string $table
* @param string|array $fields Column-name OR Column-names array OR associative field=>value pair
* @param string|array|null $values Column-value OR Column-values array OR Multiple Column-values array(array)
* @param array|null $onDuplicateKeyValues
* @param string|null $statement
* @return string|null
*/
public static function insert($table, $fields, $values = null, array $onDuplicateKeyValues = null, $statement = null)
{
if (null === $statement) {
$statement = 'INSERT';
}
$client = self::getClient();
$query = new CM_Db_Query_Insert($client, $table, $fields, $values, $onDuplicateKeyValues, $statement);
$query->execute();
return $client->getLastInsertId();
}
