/**
* Build SQL query
*
* @param array $args Arguments for query.
* @return array
*
* @throws SystemException
*/
private function buildSQL(array $args)
{
//If first argument contains space - assume this is SQL string
if (strpos($args[0], ' ')) {
return $args;
}
//Want to do it this way - but it throws Notice with our level of error reporting
//list($tableName, $fields, $condition, $order, $limit ) = $args;
$fields = true;
$condition = $order = $limit = NULL;
$tableName = $args[0];
if (isset($args[1])) {
$fields = $args[1];
}
if (isset($args[2])) {
$condition = $args[2];
}
if (isset($args[3])) {
$order = $args[3];
}
if (isset($args[4])) {
$limit = $args[4];
}
if (is_array($fields) && !empty($fields)) {
$fields = array_map('strtolower', $fields);
$fields = implode(', ', $fields);
} elseif (is_string($fields)) {
$fields = strtolower($fields);
} elseif ($fields === true) {
$fields = '*';
} else {
throw new SystemException(self::ERR_BAD_QUERY_FORMAT, SystemException::ERR_DB, [$tableName, $fields, $condition, $order, $limit]);
}
$sqlQuery = "SELECT {$fields} FROM " . QAL::getFQTableName($tableName);
if (isset($condition)) {
$sqlQuery .= $this->buildWhereCondition($condition);
}
if (isset($order)) {
$sqlQuery .= $this->buildOrderCondition($order);
}
if (isset($limit)) {
if (is_array($limit)) {
$sqlQuery .= ' LIMIT ' . implode(', ', $limit);
} else {
$sqlQuery .= " LIMIT {$limit}";
}
}
return [$sqlQuery];
}
/**
* Analyze table structure.
*
* @param string $tableName Table name.
* @return array|PDOStatement|string
*
* @throws SystemException
*/
private function analyzeTable($tableName)
{
$dTableName = QAL::getFQTableName($tableName, true);
$query = '/*ms=slave*/SHOW CREATE TABLE ' . implode('.', $dTableName);
$dbName = '';
if (sizeof($dTableName) == 2) {
$dbName = $dTableName[0];
}
$res = $this->pdo->query($query);
if (!$res) {
throw new SystemException('BAD_TABLE_NAME ' . $tableName, SystemException::ERR_DB, $query);
}
$sql = $res->fetchColumn(1);
$res = array();
$s = strpos($sql, '(');
$l = strrpos($sql, ')') - $s;
// работаем только с полями и индексами
$fields = substr($sql, $s + 1, $l);
$trimQ = function ($s) {
return trim($s, '`');
};
$row = '(?:^\\s*`(?<name>\\w+)`' . '\\s+(?<type>\\w+)' . '(?:\\((?<len>.+)\\))?' . '(?:\\s+(?<unsigned>unsigned))?' . '(?:\\s*(?<is_null>(?:NOT )?NULL))?' . '(?:\\s+DEFAULT (?<default>(?:NULL|\'[^\']+\')))?' . '.*$)';
$constraint = '(?:^\\s*CONSTRAINT `(?<constraint>\\w+)` FOREIGN KEY \\(`(?<cname>\\w+)`\\) REFERENCES `(?<tableName>\\w+)` \\(`(?<fieldName>\\w+)`\\)' . '.*$)';
$mul = '(?:^\\s*(?:UNIQUE\\s*)?KEY\\s+`\\w+`\\s+\\((?<muls>.*)\\),?$)';
$pri = '(?:PRIMARY KEY\\s+\\((?<pri>[^\\)]*)\\))';
$pattern = "/(?:{$row}|{$constraint}|{$mul}|{$pri})/im";
if (preg_match_all($pattern, trim($fields), $matches)) {
if ($matches['name']) {
// список полей в первичном ключе
$pri = array();
if (isset($matches['pri'])) {
foreach ($matches['pri'] as $priField) {
if ($priField) {
$pri = array_map($trimQ, explode(',', $priField));
break;
}
}
}
// список полей входящих в индексы
$muls = array();
if (isset($matches['muls'])) {
$mulStr = '';
foreach ($matches['muls'] as $s) {
if ($s) {
$mulStr .= ($mulStr ? ',' : '') . $s;
}
}
$muls = array_map($trimQ, explode(',', $mulStr));
}
foreach ($matches['name'] as $index => $fieldName) {
if (!$fieldName) {
continue;
}
$options = null;
$type = self::convertType($matches['type'][$index]);
//for enum or set
if (in_array($type, [QAL::COLTYPE_SET, QAL::COLTYPE_ENUM])) {
$options = explode(',', $matches['len'][$index]);
$options = array_map(function ($value) {
return trim($value, '\'""');
}, $options);
$length = true;
} elseif ($type == QAL::COLTYPE_DECIMAL) {
//len = 10,2
//length = 10 + 2 + 1 = 13
$length = array_sum(array_map('intval', array_filter(explode(',', $matches['len'][$index])))) + 1;
} else {
$length = (int) $matches['len'][$index];
}
$res[$fieldName] = ['nullable' => $matches['is_null'][$index] != 'NOT NULL', 'length' => $length, 'default' => strcasecmp(trim($matches['default'][$index], "'"), 'null') == 0 ? null : trim($matches['default'][$index], "'"), 'key' => false, 'type' => $type, 'index' => false, 'options' => $options];
// входит ли поле в индекс
if (in_array($fieldName, $pri)) {
$res[$fieldName]['index'] = 'PRI';
$res[$fieldName]['key'] = true;
} elseif (in_array($fieldName, $muls)) {
$res[$fieldName]['index'] = 'MUL';
}
// внешний ключ
$cIndex = array_search($fieldName, $matches['cname']);
if ($cIndex !== false) {
$res[$fieldName]['key'] = ['tableName' => ($dbName ? $dbName . '.' : '') . $matches['tableName'][$cIndex], 'fieldName' => $matches['fieldName'][$cIndex], 'constraint' => $matches['constraint'][$cIndex]];
}
}
}
}
return $res;
}
