/** * 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; }