/**
* Return where part of query.
*
* @param int $id
* @return string
*/
private function getWherePartById($id)
{
if (empty($id)) {
throw new InvalidArgumentException("Value '{$id}' is not a valid ID");
}
return $this->connection->prepare('(`id` = ?)', $id);
}
/**
* {@inheritdoc}
*/
public function countFailedJobs()
{
if ($queue_id = $this->getQueueId()) {
return (int) $this->connection->executeFirstCell('SELECT COUNT(`id`) AS "row_count" FROM `' . MySqlQueue::FAILED_JOBS_TABLE_NAME . '` WHERE `batch_id` = ?', $queue_id);
} else {
throw new RuntimeException("Can't get number of jobs from an unsaved batch");
}
}
/**
* {@inheritdoc}
*/
public function setAllAsExecuted()
{
$this->connection->transact(function () {
$timestamp = new DateTimeValue();
$batch = $this->connection->batchInsert($this->getTableName(), ['migration', 'executed_at'], 50, ConnectionInterface::REPLACE);
foreach ($this->getMigrations() as $migration) {
$batch->insert(get_class($migration), $timestamp);
}
$batch->done();
});
}
/**
* Insert rows that are already loaded.
*/
public function flush()
{
if ($this->is_done) {
throw new RuntimeException('This batch insert is already done');
}
$count_rows = count($this->rows);
if ($count_rows > 0) {
$this->connection->execute($this->sql_foundation . implode(', ', $this->rows));
$this->rows = [];
$this->total += $count_rows;
}
}
/**
* Return escaped list of fields that we can order by.
*
* @param string $type
* @return string
*/
public function getEscapedTypeOrderBy($type)
{
return $this->getTypeProperty($type, 'escaped_order_by', function () use($type) {
$table_name = $this->getTypeTable($type, true);
return implode(',', array_map(function ($field_name) use($table_name) {
if (substr($field_name, 0, 1) == '!') {
return $table_name . '.' . $this->connection->escapeFieldName(substr($field_name, 1)) . ' DESC';
} else {
return $table_name . '.' . $this->connection->escapeFieldName($field_name);
}
}, $this->getTypeOrderBy($type)));
});
}
/**
* {@inheritdoc}
*/
public function uniqueWhere($field_name, $where, ...$context)
{
if (empty($field_name)) {
throw new InvalidArgumentException("Value '{$field_name}' is not a valid field name");
}
if (empty($context) && (!array_key_exists($field_name, $this->field_values) || $this->field_values[$field_name] === null)) {
return true;
// NULL is always good for single column keys because MySQL does not check NULL for uniqueness
}
$field_names = [$field_name];
if (count($context)) {
$field_names = array_merge($field_names, $context);
}
// Check if we have existsing columns
foreach ($field_names as $v) {
if (!array_key_exists($v, $this->field_values)) {
throw new InvalidArgumentException("Field '{$v}' is not known");
}
}
$table_name = $this->connection->escapeTableName($this->table_name);
$conditions = [];
if ($where) {
$conditions[] = $this->connection->prepareConditions($where);
}
foreach ($field_names as $v) {
$escaped_field_name = $this->connection->escapeFieldName($v);
if ($this->field_values[$v] === null) {
$conditions[] = "{$escaped_field_name} IS NULL";
} else {
$conditions[] = $this->connection->prepare("{$escaped_field_name} = ?", $this->field_values[$v]);
}
}
$conditions = implode(' AND ', $conditions);
if (empty($this->object_id)) {
$sql = sprintf("SELECT COUNT(`id`) AS 'row_count' FROM {$table_name} WHERE {$conditions}");
} else {
$sql = $this->connection->prepare("SELECT COUNT(`id`) AS 'row_count' FROM {$table_name} WHERE ({$conditions}) AND (`id` != ?)", $this->old_object_id ? $this->old_object_id : $this->object_id);
}
if ($this->connection->executeFirstCell($sql) > 0) {
if (empty($context)) {
$this->addFieldError($field_name, "Value of '{$field_name}' needs to be unique");
} else {
$this->addFieldError($field_name, "Value of '{$field_name}' needs to be unique in context of " . implode(', ', array_map(function ($field_name) {
return "'{$field_name}'";
}, $context)));
}
return false;
}
return true;
}
/**
* @param string $table_name
* @param string $prefixed_table_name
*/
private function createConversionRateTriggers(string $table_name, string $prefixed_table_name)
{
if ($table_name == 'daily_conversions') {
$insert_trigger = 'daily_trial_conversion_rate_on_insert';
$update_trigger = 'daily_trial_conversion_rate_on_update';
} else {
$insert_trigger = 'monthly_trial_conversion_rate_on_insert';
$update_trigger = 'monthly_trial_conversion_rate_on_update';
}
$this->connection->execute("DROP TRIGGER IF EXISTS `{$insert_trigger}`");
$this->connection->execute("CREATE TRIGGER `{$insert_trigger}` BEFORE INSERT ON `{$prefixed_table_name}` FOR EACH ROW\n BEGIN\n IF NEW.visits > '0' AND NEW.trials > '0' THEN\n SET NEW.to_trial_rate = NEW.trials / NEW.visits * 100;\n ELSE\n SET NEW.to_trial_rate = '0';\n END IF;\n\n IF NEW.visits > '0' AND NEW.conversions > '0' THEN\n SET NEW.to_paid_rate = NEW.conversions / NEW.visits * 100;\n ELSE\n SET NEW.to_paid_rate = '0';\n END IF;\n END");
$this->connection->execute("DROP TRIGGER IF EXISTS `{$update_trigger}`");
$this->connection->execute("CREATE TRIGGER `{$update_trigger}` BEFORE UPDATE ON `{$prefixed_table_name}` FOR EACH ROW\n BEGIN\n IF NEW.visits != OLD.visits OR NEW.trials != OLD.trials OR NEW.conversions != OLD.conversions THEN\n IF NEW.visits > '0' AND NEW.trials > '0' THEN\n SET NEW.to_trial_rate = NEW.trials / NEW.visits * 100;\n ELSE\n SET NEW.to_trial_rate = '0';\n END IF;\n\n IF NEW.visits > '0' AND NEW.conversions > '0' THEN\n SET NEW.to_paid_rate = NEW.conversions / NEW.visits * 100;\n ELSE\n SET NEW.to_paid_rate = '0';\n END IF;\n END IF;\n END");
}
/**
* {@inheritdoc}
*/
public function tearDown()
{
$this->connection->execute('SET FOREIGN_KEY_CHECKS = 0');
foreach ($this->connection->getTableNames() as $table_name) {
$this->connection->dropTable($table_name);
}
$this->connection->execute('SET FOREIGN_KEY_CHECKS = 1');
$this->connection->disconnect();
parent::tearDown();
}
/**
* Prepare SQL and load one or more records.
*
* @return mixed
*/
public function execute()
{
$select_sql = $this->getSelectSql();
if ($this->loadByTypeField()) {
$return_by = ConnectionInterface::RETURN_OBJECT_BY_FIELD;
$return_by_value = 'type';
} else {
$return_by = ConnectionInterface::RETURN_OBJECT_BY_CLASS;
$return_by_value = $this->type;
}
if ($this->hasContainer()) {
return $this->connection->advancedExecute($select_sql, null, ConnectionInterface::LOAD_ALL_ROWS, $return_by, $return_by_value, [&$this->connection, &$this->pool, &$this->log], $this->getContainer());
} else {
return $this->connection->advancedExecute($select_sql, null, ConnectionInterface::LOAD_ALL_ROWS, $return_by, $return_by_value, [&$this->connection, &$this->pool, &$this->log]);
}
}
/**
* Check number of records in failed jobs queue table.
*
* @param int $expected
*/
protected function assertFailedRecordsCount($expected)
{
$this->assertSame($expected, $this->connection->executeFirstCell('SELECT COUNT(`id`) AS "row_count" FROM `' . MySqlQueue::FAILED_JOBS_TABLE_NAME . '`'));
}
/**
* @param PromiseInterface $promise
* @return bool
*/
public function isSettled(PromiseInterface $promise)
{
return (bool) $this->connection->executeFirstCell('SELECT COUNT(`id`) AS "row_count" FROM ' . self::PROMISES_TABLE_NAME . ' WHERE `signature` = ? AND `settlement` IS NOT NULL AND `settled_at` IS NOT NULL', $promise->getSignature());
}
/**
* {@inheritdoc}
*/
public function countBatches()
{
return $this->connection->count(self::BATCHES_TABLE_NAME);
}
