<?php
namespace Croogo\Core\Database;
use Cake\Database\Driver\Postgres;
use Cake\Datasource\ConnectionManager;
use Cake\Log\LogTrait;
use Psr\Log\LogLevel;
class SequenceFixer
{
use LogTrait;
public function fix($connectionName)
{
$db = ConnectionManager::get($connectionName);
$driver = $db->getDriver();
if ($driver instanceof Postgres) {
$this->fixPostgres($db);
}
}
protected function fixPostgres($db)
{
$config = $db->config();
$database = $config['database'];
$schema = isset($config['schema']) ? $config['schema'] : 'public';
// gets a list of columns that uses a sequence as the default value
$sql = sprintf(
"
select table_name, column_name, column_default
from information_schema.columns
where table_catalog = '%s'
and table_schema = '%s'
and column_default like 'nextval%%'",
$database,
$schema
);
$columns = $db->query($sql)->fetchAll('assoc');
// iterates columns and gets its current max value, increments it, and
// alter it's starting value
foreach ($columns as $column) {
$nextValue = $db->query(
sprintf(
'select MAX(%s.%s) as max from %s',
$column['table_name'],
$column['column_name'],
$column['table_name']
)
)->fetch('assoc');
$nextValue = empty($nextValue['max']) ? 1 : $nextValue['max'] + 1;
preg_match_all("/'(.*)'/", $column['column_default'], $matches);
$sequenceName = $matches[1][0];
$sql = sprintf(
'alter sequence %s restart with %d',
$sequenceName,
$nextValue
);
$result = $db->execute($sql);
$this->log(
sprintf(
'Sequence %s reset to %d',
$sequenceName,
$nextValue
),
LogLevel::WARNING
);
}
}
}