/** * 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[] = ';'; }
/** * 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; } } }