数据库调试 记录当前SQL及分析性能
@access protected
@param  boolean $start 调试开始标记 true 开始 false 结束
@param  string  $sql 执行的SQL语句 留空自动获取
@param  bool    $master 主从标记
@return void
549893
protected function debug($start, $sql = '', $master = false)
    {
        if (!empty($this->config['debug'])) {
            // 开启数据库调试模式
            if ($start) {
                $this->queryStartTime = microtime(true);
            } else {
                $runtime = number_format((microtime(true) - $this->queryStartTime), 6);
                $sql     = $sql ?: $this->getLastsql();
                $result  = [];

                // SQL性能分析
                if ($this->config['sql_explain'] && 0 === stripos(trim($sql), 'select')) {
                    $result = $this->getExplain($sql);
                }

                // SQL监听
                $this->triggerSql($sql, $runtime, $result, $master);
            }
        }
    }
288780
public function exec_script($sql)
    {
        $sql  = $this->fix_table_names($sql);
        $buff = '';
        $body = false;

        foreach (explode("\n", $sql) as $line) {
            $tok = strtolower(trim($line));
            if (preg_match('/^--/', $line) || $tok == '' || $tok == '/') {
                continue;
            }

            $buff .= $line . "\n";

            // detect PL/SQL function bodies, don't break on semicolon
            if ($body && $tok == 'end;') {
                $body = false;
            }
            else if (!$body && $tok == 'begin') {
                $body = true;
            }

            if (!$body && substr($tok, -1) == ';') {
                $this->query($buff);
                $buff = '';
                if ($this->db_error) {
                    break;
                }
            }
        }

        return !$this->db_error;
    }
160441
protected function beginQuery()
    {
        $autoSlave = $this->db->isAutoSlave();
        if ($autoSlave) {
            $this->db->switchConnection('slave');
        }

        $reconnect = $this->db->getOption('reconnect');
        $reconnectRetries = $this->db->getOption('reconnect_retries', 3);
        $reconnectDelayMS = $this->db->getOption('reconnect_delay_ms', 1000);

        while (true) {
            $e = null;
            $errorCode = null;
            $errorInfo = null;
            $result = null;

            $isReconnectError = false;
            try {
                $this->prepare();
                $result = $this->statement->execute($this->params ? $this->params : $this->positionParams);
            } catch (\Exception $e) {
            }

            if ($result === false || !$e) {
                $errorCode = $this->statement->errorCode();
                $errorInfo = $this->statement->errorInfo();
            }

            $isReconnectError = Util::checkReconnectError($errorCode, $errorInfo, $e);

            // reconnect
            if ($reconnect && $isReconnectError && $reconnectRetries > 0) {
                $reconnectRetries--;
                $this->statement = null;
                $this->db->close();
                $reconnectDelayMS && usleep($reconnectDelayMS * 1000);
                continue;
            }

            break;
        }

        if ($autoSlave) {
            $this->db->switchConnection();
        }

        if ($e) {
            $this->db->logError(sprintf('Statement exception error #%s: %s', $e->getCode(), $e->getMessage()));
            throw $e;
        }

        if ($result === false) {
            $errorMsg = sprintf('Statement execute error #%s: %s', $errorInfo[0], $errorInfo[2]);
            $this->db->logError($errorMsg);
            throw new Exception($errorMsg);
        }
    }
1173680
public function patchQuery($sql=array()) {
        if(!is_array($sql)) return false;
        // 自动启动事务支持
        $this->startTrans();
        try{
            foreach ($sql as $_sql){
                $result   =  $this->execute($_sql);
                if(false === $result) {
                    // 发生错误自动回滚事务
                    $this->rollback();
                    return false;
                }
            }
            // 提交事务
            $this->commit();
        } catch (ThinkException $e) {
            $this->rollback();
        }
        return true;
    }
1133880
public function select($offset = null, $limit = null, $useMaster = false)
    {
        list($sql, $cacheKey) = $this->buildSql($offset, $limit, true);

        if ($this->openCache && $this->currentQueryUseCache) {
            $cacheKey = md5($sql . json_encode($this->bindParams)) . implode('', $cacheKey);
            $return = Model::getInstance()->cache()->get($cacheKey);
        } else {
            $return = false;
        }

        if ($return === false) {
            $this->currentQueryIsMaster = $useMaster;
            $stmt = $this->prepare($sql, $useMaster ? $this->wlink : $this->rlink);
            $this->execute($stmt);
            $return = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            $this->openCache && $this->currentQueryUseCache && Model::getInstance()->cache()->set($cacheKey, $return, $this->conf['cache_expire']);
            $this->currentQueryUseCache = true;
        } else {
            if (Cml::$debug) {
                $this->currentSql = $sql;
                $this->debugLogSql(Debug::SQL_TYPE_FROM_CACHE);
                $this->currentSql = '';
            }

            $this->reset();
            $this->clearBindParams();
        }
        return $return;
    }
544127
public function start()
    {
        $metaData = array("columns"=>array());

        if ($this->countTotal) {
            $totalQuery = $this->prepareParams($this->totalQuery, $this->sqlParams);
            $stm = $this->connection->prepare($totalQuery);
            $this->bindParams($stm, $this->sqlParams);
            $stm->execute();
            $error = $stm->errorInfo();
            if ($error[2]!=null) {
                throw new \Exception("Query Error >> [".$error[2]."] >> $totalQuery");
                return;
            }
            $row = $stm->fetch();
            $result = $row[0];
            $metaData['totalRecords'] = $result;
        }

        if ($this->countFilter) {
            $filterQuery = $this->prepareParams($this->filterQuery, $this->sqlParams);
            $stm = $this->connection->prepare($filterQuery);
            $this->bindParams($stm, $this->sqlParams);
            $stm->execute();
            $error = $stm->errorInfo();
            if ($error[2]!=null) {
                throw new \Exception("Query Error >> [".$error[2]."] >> $filterQuery");
                return;
            }
            $row = $stm->fetch();
            $result = $row[0];
            $metaData['filterRecords'] = $result;
        }
        $row = null;

        $query = $this->prepareParams($this->query, $this->sqlParams);
        // echo "pdodatasource start query=$query <br>";
        $stm = $this->connection->prepare($query);
        $this->bindParams($stm, $this->sqlParams);
        $stm->execute();

        $error = $stm->errorInfo();
        // if($error[2]!=null)
        if ($error[0]!='00000') {
            throw new \Exception("Query Error >> [".$error[2]."] >> $query");
            return;
        }

        $driver = strtolower($this->connection->getAttribute(PDO::ATTR_DRIVER_NAME));
        $metaSupportDrivers = array('dblib', 'mysql', 'pgsql', 'sqlite');
        $metaSupport = false;
        foreach ($metaSupportDrivers as $supportDriver) {
            if (strpos($driver, $supportDriver) !== false) {
                $metaSupport = true;
            }
        }
            
        if (!$metaSupport) {
            $row = $stm->fetch(PDO::FETCH_ASSOC);
            $cNames = empty($row) ? array() : array_keys($row);
            $numcols = count($cNames);
        } else {
            $numcols = $stm->columnCount();
        }

        // $metaData = array("columns"=>array());
        for ($i=0;$i<$numcols;$i++) {
            if (! $metaSupport) {
                $cName = $cNames[$i];
                $cType = $this->guessTypeFromValue($row[$cName]);
            } else {
                $info = $stm->getColumnMeta($i);
                $cName = $info["name"];
                $cType = $this->guessType($info["native_type"]);

            }
            $metaData["columns"][$cName] = array(
                "type"=>$cType,
            );
            switch($cType)
            {
            case "datetime":
                $metaData["columns"][$cName]["format"] = "Y-m-d H:i:s";
                break;
            case "date":
                $metaData["columns"][$cName]["format"] = "Y-m-d";
                break;
            case "time":
                $metaData["columns"][$cName]["format"] = "H:i:s";
                break;
            }
        }
                
        $this->sendMeta($metaData, $this);
        $this->startInput(null);
        
        $numberColumnList = array();
        foreach ($metaData["columns"] as $cName=>$cMeta) {
            if ($cMeta["type"]=="number") {
                array_push($numberColumnList, $cName);
            }
        }
                        
        if (! isset($row)) {
            $row=$stm->fetch(PDO::FETCH_ASSOC);
        }
            
        while ($row) {
            // print_r($row); echo '<br>';
            foreach ($numberColumnList as $cName) {
                    $row[$cName]+=0;
            }
            $this->next($row, $this);
            $row=$stm->fetch(PDO::FETCH_ASSOC);
        }
        $this->endInput(null);
    }
383027
public function createTbl($sql)
    {
        if (isset($sql) && !empty(trim($sql))) {
            $this->db->exec($sql);

            return true;
        } else {
            return false;
        }
    }
354820
function buildQuery( $sql = NULL ) {
		if( !isset( $this->query ) OR empty( $sql ) ) {
			return FALSE;
		}

		$this->query .= $sql;

		return $this->query;
	}
437691
public function read($sql)
    {
        $sql = (string)$sql;

        $conn = $this->getConnection();
        $this->Benchmark->start('sql-query');

        try {

//            if(!substr($sql, 0, 4) == 'SHOW') {
//
//                $explain = $conn->query("EXPLAIN ".$sql);
//
//                $explainresults = $explain->fetchAll(PDO::FETCH_ASSOC);
//            }

//            $start =  microtime(TRUE);

            $result = $conn->query($sql);

//            $end =  microtime(TRUE);
//            $totalms = ($end-$start)*1000;

//            if($totalms > 1 && !empty($explainresults)) {
//
//                foreach($explainresults as $qline)
//                {
//
//                    if(strtolower($qline['select_type']) != 'union result' && strtolower($qline['type']) == 'all')
//                    {
//                        // table scan
//                        $pr = ArrayUtils::arrayToStr($explainresults);
//                        error_log("[SQL]  BAD QUERY: TABLE SCAN {$qline['key']}\n"."SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n");
//
//                    }else if($qline['rows'] > 5000) {
//
//                        // poor index
//                        $pr = ArrayUtils::arrayToStr($explainresults);
//                        error_log("[SQL]  BAD QUERY: SCANNED OVER 5000 ROWS {$qline['key']}\n"."SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n");
//
//                    }
//                }
//
//            }
//
//            if($totalms > 1000) {
//
//                if(!empty($explainresults)) {
//                    $pr = ArrayUtils::arrayToStr($explainresults);
//                    $email = "SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n";
//                } else {
//                    $email = "SQL:\n\n{$sql}\n\nTOTAL TIME:{$totalms}ms\n";
//                }
//                error_log("[SQL]  BAD QUERY: TOOK OVER 1 SECOND\n".$email);
//
//            }




        } catch(PDOException $pe) {
            $errorMessage = $pe->getMessage();

            $this->Logger->debug(array(
                'Conn'                => $this->connDebugString,
                'SQL'                 => $sql,
                'Execution Time (ms)' => $this->Benchmark->end('sql-query'),
                'Error'               => $errorMessage,
            ));


            throw new SQLException($sql, $errorMessage, intVal($pe->getCode()));
        }

        $this->Logger->debug(array(
                'Conn'                => $this->connDebugString,
                'SQL'                 => $sql,
                'Execution Time (ms)' => $this->Benchmark->end('sql-query'),
                'Rows Returned'       => $result->rowCount(),
            ));

        unset($sql);
        unset($conn);

        return $result;
    }
414993
private function initSql()
    {
        $this->sql['start'] = null;
        $this->sql['join'] = '';
        $this->sql['where'] = '';
        $this->sql['orderby'] = null;
        $this->sql['limit'] = null;
        $this->sql['offset'] = null;
    }
1366029
public function startQuery($sql, array $params = NULL, array $types = NULL)
	{
		Debugger::timer('doctrine');

		$source = NULL;
		foreach (debug_backtrace() as $row) {
			if (isset($row['file']) && $this->filterTracePaths(realpath($row['file']))) {
				if (isset($row['class']) && stripos($row['class'], '\\' . Proxy::MARKER) !== FALSE) {
					if (!in_array(Proxy::class, class_implements($row['class']))) {
						continue;
					} elseif (isset($row['function']) && $row['function'] === '__load') {
						continue;
					}
				} elseif (stripos($row['file'], DIRECTORY_SEPARATOR . Proxy::MARKER) !== FALSE) {
					continue;
				}
				$source = [$row['file'], (int) $row['line']];
				break;
			}
		}

		$this->queries[] = [$sql, (array) $params, NULL, (array) $types, $source];
	}
1073769
protected function initConnect($master = true)
    {
        if (!empty($this->config['deploy'])) {
            // 采用分布式数据库
            if ($master || $this->transTimes) {
                if (!$this->linkWrite) {
                    $this->linkWrite = $this->multiConnect(true);
                }

                $this->linkID = $this->linkWrite;
            } else {
                if (!$this->linkRead) {
                    $this->linkRead = $this->multiConnect(false);
                }

                $this->linkID = $this->linkRead;
            }
        } elseif (!$this->linkID) {
            // 默认单数据库
            $this->linkID = $this->connect();
        }
    }
642405
protected function debug($start, $sql = '')
    {
        if (!empty($this->config['debug'])) {
            // 开启数据库调试模式
            $debug = Container::get('debug');
            if ($start) {
                $debug->remark('queryStartTime', 'time');
            } else {
                // 记录操作结束时间
                $debug->remark('queryEndTime', 'time');

                $runtime = $debug->getRangeTime('queryStartTime', 'queryEndTime');

                $sql = $sql ?: $this->queryStr;

                // SQL监听
                $this->triggerSql($sql, $runtime, $this->options);
            }
        }
    }
621495
public function backup($table, $start)
    {
        $db = self::connect();
        // 备份表结构
        if (0 == $start) {
            $result = $db->query("SHOW CREATE TABLE `{$table}`");
            $sql = "\n";
            $sql .= "-- -----------------------------\n";
            $sql .= "-- Table structure for `{$table}`\n";
            $sql .= "-- -----------------------------\n";
            $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
            $sql .= trim($result[0]['Create Table']) . ";\n\n";
            if (false === $this->write($sql)) {
                return false;
            }
        }
        //数据总数
        $result = $db->query("SELECT COUNT(*) AS count FROM `{$table}`");
        $count = $result['0']['count'];
        //备份表数据
        if ($count) {
            //写入数据注释
            if (0 == $start) {
                $sql = "-- -----------------------------\n";
                $sql .= "-- Records of `{$table}`\n";
                $sql .= "-- -----------------------------\n";
                $this->write($sql);
            }
            //备份数据记录
            $result = $db->query("SELECT * FROM `{$table}` LIMIT {$start}, 1000");
            foreach ($result as $row) {
                $row = array_map('addslashes', $row);
                $sql = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\\r', '\\n'), implode("', '", $row)) . "');\n";
                if (false === $this->write($sql)) {
                    return false;
                }
            }
            //还有更多数据
            if ($count > $start + 1000) {
                //return array($start + 1000, $count);
                return $this->backup($table, $start + 1000);
            }
        }
        //备份下一表
        return 0;
    }
1340374
public function startQuery($sql, array $params = null, array $types = null)
    {
        if (class_exists('Tideways\Profiler')) {
            $this->span = \Tideways\Profiler::createSpan('sql');
            $this->span->startTimer();
            $this->span->annotate(['title' => $sql]);

            return;
        }

        if (class_exists('QafooLabs\Profiler') and method_exists('QafooLabs\Profiler', 'startCustomTimer')) {
            $this->id = \QafooLabs\Profiler::startCustomTimer('sql', $sql);
        }
    }
1178373
public function fetchAll($sql, $bind = [], $fetchMode = \PDO::FETCH_ASSOC, $useMaster = false)
    {
        $context = $this->_context;

        $context->sql = $sql;
        $context->bind = $bind;
        $context->affected_rows = 0;

        $this->eventsManager->fireEvent('db:beforeQuery', $this);

        if ($context->connection) {
            $type = null;
            $connection = $context->connection;
        } else {
            $type = $this->_has_slave ? 'slave' : 'default';
            $connection = $this->poolManager->pop($this, $this->_timeout, $type);
        }

        try {
            $start_time = microtime(true);
            $result = $connection->query($sql, $bind, $fetchMode, $useMaster);
            $elapsed = round(microtime(true) - $start_time, 3);
        } finally {
            if ($type) {
                $this->poolManager->push($this, $connection, $type);
            }
        }

        $count = $context->affected_rows = count($result);

        $event_data = compact('elapsed', 'count', 'sql', 'bind', 'result');

        $this->logger->debug($event_data, 'db.query');

        $this->eventsManager->fireEvent('db:afterQuery', $this, $event_data);

        return $result;
    }
689555
function exec($sql)
    {
        if ($this->trace_count){
            Trace::groups('DB')->group('count')->set(Trace::groups('DB')->group('count')->get()+1);
        }
        if ($this->trace_sql){
            Benchmark::start('sql');
            $sql = $this->addPrefixes($sql);
            $result = parent::exec($sql);
            $bm = Benchmark::stop('sql', true);
            if ($bm['time'] >= $this->slow_query){
                Trace::groups('DB')->group('query')->group()->set(array(
                    'sql' => $sql,
                    'benchmark' => $bm
                    )
                );
                Trace::groups('DB')->group('sql_time')->set(Trace::groups('DB')->group('sql_time')->get()+$bm['time']);
                Trace::groups('DB')->group('slow_count')->set(Trace::groups('DB')->group('slow_count')->get()+1);
            }
            return $result;
        }
        return parent::exec($this->addPrefixes($sql));
    }