/**
* Generate body of stored procedure.
*
* @param array[] $columns Columns from table.
* @param array[] $params Params for where block.
* @param string[] $lines Stored procedure code lines.
*/
protected function bodyPart($params, $columns, &$lines)
{
$set = [];
$primaryKeys = DataLayer::getTablePrimaryKeys($this->dataSchema, $this->tableName);
$lines[] = sprintf('update %s', $this->tableName);
foreach ($columns as $column) {
$check = StaticDataLayer::searchInRowSet('Column_name', $column['column_name'], $primaryKeys);
if (!isset($check)) {
$set[] = $column;
}
}
reset($set);
$first = key($set);
$lines[] = 'set';
$lengthLastLine = 0;
foreach ($set as $key => $column) {
if ($key === $first) {
$lengthLastLine = strlen($lines[count($lines) - 1]);
$format = sprintf("%%%ds %%s = p_%%s", $lengthLastLine);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[count($lines) - 1] .= $line;
} else {
$format = sprintf("%%-%ds %%s = p_%%s", $lengthLastLine + 3);
$line = sprintf($format, ',', $column['column_name'], $column['column_name']);
$lines[] = $line;
}
}
$lines[] = 'where';
reset($params);
$first = key($params);
foreach ($params as $key => $column) {
if ($key === $first) {
$format = sprintf("%%%ds %%s = p_%%s", 1);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[count($lines) - 1] .= $line;
} else {
$format = sprintf("and%%%ds %%s = p_%%s", 3);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[] = $line;
}
}
$lines[] = ';';
}
/**
* {@inheritdoc}
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
$this->io = new StratumStyle($input, $output);
$this->input = $input;
$this->output = $output;
$configFileName = $input->getArgument('config file');
$settings = $this->readConfigFile($configFileName);
$this->sourceDirectory = $this->getSetting($settings, true, 'loader', 'source_directory');
$host = $this->getSetting($settings, true, 'database', 'host');
$user = $this->getSetting($settings, true, 'database', 'user');
$password = $this->getSetting($settings, true, 'database', 'password');
$this->dataSchema = $this->getSetting($settings, true, 'database', 'database');
DataLayer::connect($host, $user, $password, $this->dataSchema);
DataLayer::setIo($this->io);
$tableList = DataLayer::getTablesNames($this->dataSchema);
$this->helper = new QuestionHelper();
$this->printAllTables($tableList);
$this->startAsking($tableList);
DataLayer::disconnect();
}
/**
* Generate main part with name and params.
*
* @param array[] $columns Columns from table.
* @param string|null $spType Stored procedure type {insert|update|delete|select}.
*
* @return array[]|null
*/
protected function checkUniqueKeys($columns, $spType = null)
{
$primaryKeys = DataLayer::getTablePrimaryKeys($this->dataSchema, $this->tableName);
$uniqueKeys = DataLayer::getTableUniqueKeys($this->dataSchema, $this->tableName);
$resultColumns = [];
if (!isset($spType)) {
if (count($uniqueKeys) <= 0 && count($primaryKeys) <= 0) {
return null;
} else {
return $columns;
}
}
if (count($primaryKeys) > 0) {
foreach ($columns as $column) {
$check = StaticDataLayer::searchInRowSet('Column_name', $column['column_name'], $primaryKeys);
if (isset($check)) {
$resultColumns[] = $column;
}
}
return $resultColumns;
} else {
if (count($uniqueKeys) > 0) {
reset($uniqueKeys);
$first = key($uniqueKeys);
if (count($uniqueKeys) > 1) {
$this->io->writeln(sprintf('Table <dbo>%s</dbo> has more than one unique key.', $this->tableName));
$array = [];
foreach ($uniqueKeys as $column) {
if (isset($array[$column['Key_name']])) {
$array[$column['Key_name']] .= ',';
$array[$column['Key_name']] .= $column['Column_name'];
} else {
$array[$column['Key_name']] = $column['Column_name'];
}
}
$tableArray = [];
foreach ($array as $key => $column) {
$tableArray[] = [$key, $column];
}
$table = new Table($this->output);
$table->setHeaders(['Name', 'Keys']);
$table->setRows($tableArray);
$table->render();
$question = new Question(sprintf('What unique keys use in statement?(%s): ', $uniqueKeys[$first]['Key_name']), $uniqueKeys[$first]['Key_name']);
$uniqueKeys = $this->helper->ask($this->input, $this->output, $question);
$uniqueKeys = explode(',', $array[$uniqueKeys]);
foreach ($uniqueKeys as $column) {
$resultColumns[] = ['column_name' => $column];
}
return $resultColumns;
} else {
foreach ($uniqueKeys as $column) {
$resultColumns[] = ['column_name' => $column['Column_name']];
}
return $resultColumns;
}
} else {
return null;
}
}
}
