/**
* Get query with optional table as a filter.
*
* @param string $database
* @param string $table
* @return bool|array
*/
protected function getQuery($database = '', $table = '')
{
$this->Log->write(__METHOD__, Log::LOG_LEVEL_SYSTEM_INFORMATION);
// input validation
if (!Helpers::is_string_ne($database)) {
$this->Log->write('database is invalid', Log::LOG_LEVEL_WARNING, $database);
return false;
}
if ($this->mysql_56) {
// If the server is running MySQL 5.6 or later, use the first query because it executes extremely quickly.
// build SELECT statement for foreign key relationships for MySQL 5.6+
$sql = 'SELECT
SUBSTRING(t.FOR_NAME, LENGTH(@db) + 2) AS "child_table"
, c.FOR_COL_NAME AS "child_field"
, SUBSTRING(t.REF_NAME, LENGTH(@db) + 2) AS "parent_table"
, c.REF_COL_NAME AS "parent_field"
FROM INNODB_SYS_FOREIGN_COLS c
JOIN INNODB_SYS_FOREIGN t ON (c.ID = t.ID)
WHERE SUBSTRING(t.ID, 1, LENGTH(@db)) = @db';
$table_field = 't.FOR_NAME';
} else {
// build SELECT statement for foreign key relationships for < MySQL 5.6
// This query takes about 4.5 seconds to fully execute.
$sql = 'SELECT k.TABLE_NAME AS "child_table"
, k.COLUMN_NAME AS "child_field"
, k.REFERENCED_TABLE_NAME AS "parent_table"
, k.REFERENCED_COLUMN_NAME AS "parent_field"
FROM KEY_COLUMN_USAGE k
JOIN TABLE_CONSTRAINTS c ON (k.`CONSTRAINT_SCHEMA` = c.`CONSTRAINT_SCHEMA` AND k.CONSTRAINT_NAME = c.CONSTRAINT_NAME)
WHERE k.CONSTRAINT_SCHEMA = @db
AND c.CONSTRAINT_TYPE = \'FOREIGN KEY\'
ORDER BY child_table, child_field';
$table_field = 'k.TABLE_NAME';
}
$params = [];
if (Helpers::is_string_ne($table)) {
$sql .= PHP_EOL . ' AND ' . $table_field . ' = ?';
$params[] = $database . '/' . $table;
}
// use quoted database name instead of variable
$sql = str_replace('@db', $this->quote($database, 'string'), $sql);
return array($sql, $params);
}
/**
* Append the PHP string content to a file.
*
* @return bool|int
*/
protected function write()
{
$this->Log->write(__METHOD__, Log::LOG_LEVEL_SYSTEM_INFORMATION);
// input validation
if (!Helpers::is_string_ne($this->php)) {
$this->Log->write('php is not a string', Log::LOG_LEVEL_WARNING);
return false;
}
// write contents to file
$bytes = file_put_contents($this->file_path, $this->php . PHP_EOL, FILE_APPEND);
if ($bytes === false) {
$this->Log->write('error writing contents to file', Log::LOG_LEVEL_WARNING);
} else {
$this->Log->write('file wrote ' . $bytes . ' bytes', Log::LOG_LEVEL_USER);
$this->php = '';
}
return $bytes;
}
/**
* @return array|bool
* @todo Add a check for inside string literal
*/
private function splitDelimiter()
{
$delimiters = substr_count(strtoupper($this->sql), 'DELIMITER');
if ($delimiters === 0) {
return false;
}
$delimiter = ';';
$words = explode(' ', $this->sql);
$this->sql_lines = array();
$current_statement = '';
$num_words = count($words);
// go through string word by word, checking for delimiter for statement, consuming minimal memory
for ($i = 0; $i < $num_words; $i++) {
$word = $words[$i];
if (!Helpers::is_string_ne(trim($word))) {
continue;
}
if ($word === $delimiter) {
$this->sql_lines[] = trim($current_statement);
$current_statement = '';
continue;
} elseif (strtoupper(trim($word)) === 'DELIMITER') {
$delimiter = $words[$i + 1];
$i++;
continue;
} else {
$current_statement .= trim($word) . ' ';
}
if ($i >= $num_words && Helpers::is_string_ne($current_statement)) {
$this->sql_lines[] = trim($current_statement);
break;
}
}
return $this->sql_lines;
}
