/**
* Inserts multiple records into a single table, preparing the statement only once,
* and executes all the queries.
* @method insertManyAndExecute
* @param {string} $table_into The name of the table to insert into
* @param {array} [$records=array()] The array of records to insert.
* Each record should be an array of ($field => $value) pairs, with the exact
* same set of keys (field names) in each array.
* @param {array} [$options=array()] An associative array of options, including:
* @param {string} [$options.className]
* If you provide the class name, the system will be able to use any sharding
* indexes under that class name in the config.
* @param {integer} [$options.chunkSize]
* The number of rows to insert at a time. Defaults to 20.
* You can also put 0 here, which means unlimited chunks, but it's not recommended.
* @param {array} [$options.onDuplicateKeyUpdate]
* You can put an array of fieldname => value pairs here,
* which will add an ON DUPLICATE KEY UPDATE clause to the query.
*/
function insertManyAndExecute($table_into, array $records = array(), $options = array())
{
// Validate and get options
if (empty($table_into)) {
throw new Exception("table not specified in call to 'insertManyAndExecute'.");
}
if (empty($records)) {
return false;
}
$chunkSize = isset($options['chunkSize']) ? $options['chunkSize'] : 20;
if ($chunkSize < 0) {
return false;
}
$onDuplicateKeyUpdate = isset($options['onDuplicateKeyUpdate']) ? $options['onDuplicateKeyUpdate'] : null;
$className = isset($options['className']) ? $options['className'] : null;
// Get the columns list
$record = reset($records);
foreach ($record as $column => $value) {
$columns_list[] = Db_Query_Mysql::column($column);
}
$columns_string = implode(', ', $columns_list);
$into = "{$table_into} ({$columns_string})";
// On duplicate key update clause (optional)
$update_fields = array();
$odku_clause = '';
if (isset($onDuplicateKeyUpdate)) {
$odku_clause = "\n\t ON DUPLICATE KEY UPDATE ";
$parts = array();
foreach ($onDuplicateKeyUpdate as $k => $v) {
if ($v instanceof Db_Expression) {
$parts[] .= "`{$k}` = {$v}";
} else {
$parts[] .= "`{$k}` = :__update_{$k}";
$update_fields["__update_{$k}"] = $v;
}
}
$odku_clause .= implode(",\n\t", $parts);
}
// Start filling
$queries = array();
$queryCounts = array();
$bindings = array();
$last_q = array();
$last_queries = array();
foreach ($records as $record) {
// get shard, if any
$query = new Db_Query_Mysql($this, Db_Query::TYPE_INSERT);
$shard = '';
if (isset($className)) {
$query->className = $className;
$sharded = $query->shard(null, $record);
$shard = key($sharded);
if (count($sharded) > 1 or $shard === '*') {
// should be only one shard
throw new Exception("Db_Mysql::insertManyAndExecute query should not hit more than one shard: " . Q::json_encode($record));
}
}
// start filling out the query data
$qc = empty($queryCounts[$shard]) ? 1 : $queryCounts[$shard] + 1;
if (!isset($bindings[$shard])) {
$bindings[$shard] = array();
}
$values_list = array();
foreach ($record as $column => $value) {
if ($value instanceof Db_Expression) {
$values_list[] = "{$value}";
} else {
$values_list[] = ':_' . $qc . '_' . $column;
$bindings[$shard]['_' . $qc . '_' . $column] = $value;
}
}
$values_string = implode(', ', $values_list);
if (empty($queryCounts[$shard])) {
$q = $queries[$shard] = "INSERT INTO {$into}\nVALUES ({$values_string}) ";
$queryCounts[$shard] = 1;
} else {
$q = $queries[$shard] .= ",\n ({$values_string}) ";
++$queryCounts[$shard];
}
// if chunk filled up for this shard, execute it
if ($qc === $chunkSize) {
if ($onDuplicateKeyUpdate) {
$q .= $odku_clause;
}
$query = $this->rawQuery($q)->bind($bindings[$shard]);
if ($onDuplicateKeyUpdate) {
$query = $query->bind($update_fields);
}
if (isset($last_q[$shard]) and $last_q[$shard] === $q) {
// re-use the prepared statement, save round-trips to the db
$query->reuseStatement($last_queries[$shard]);
}
$query->execute(true);
$last_q[$shard] = $q;
$last_queries[$shard] = $query;
// save for re-use
$bindings[$shard] = $queries[$shard] = array();
$queryCounts[$shard] = 0;
}
}
// Now execute the remaining queries, if any
foreach ($queries as $shard => $q) {
if (!$q) {
continue;
}
if ($onDuplicateKeyUpdate) {
$q .= $odku_clause;
}
$query = $this->rawQuery($q)->bind($bindings[$shard]);
if ($onDuplicateKeyUpdate) {
$query = $query->bind($update_fields);
}
if (isset($last_q[$shard]) and $last_q[$shard] === $q) {
// re-use the prepared statement, save round-trips to the db
$query->reuseStatement($last_queries[$shard]);
}
$query->execute(true);
}
}
