QueryAnalyzer.php 5.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242
  1. <?php
  2. namespace Test\Tool;
  3. use Doctrine\DBAL\Logging\SQLLogger;
  4. use Doctrine\DBAL\Types\Type;
  5. use Doctrine\DBAL\Platforms\AbstractPlatform;
  6. /**
  7. * @author Gediminas Morkevicius <gediminas.morkevicius@gmail.com>
  8. * @package Gedmo.Tool.Logging.DBAL
  9. * @subpackage QueryAnalyzer
  10. * @link http://www.gediminasm.org
  11. * @license MIT License (http://www.opensource.org/licenses/mit-license.php)
  12. */
  13. class QueryAnalyzer implements SQLLogger
  14. {
  15. /**
  16. * Used database platform
  17. *
  18. * @var AbstractPlatform
  19. */
  20. protected $platform;
  21. /**
  22. * Start time of currently executed query
  23. *
  24. * @var integer
  25. */
  26. private $queryStartTime = null;
  27. /**
  28. * Total execution time of all queries
  29. *
  30. * @var integer
  31. */
  32. private $totalExecutionTime = 0;
  33. /**
  34. * List of queries executed
  35. *
  36. * @var array
  37. */
  38. private $queries = array();
  39. /**
  40. * Query execution times indexed
  41. * in same order as queries
  42. *
  43. * @var array
  44. */
  45. private $queryExecutionTimes = array();
  46. /**
  47. * Initialize log listener with database
  48. * platform, which is needed for parameter
  49. * conversion
  50. *
  51. * @param AbstractPlatform $platform
  52. */
  53. public function __construct(AbstractPlatform $platform)
  54. {
  55. $this->platform = $platform;
  56. }
  57. /**
  58. * {@inheritdoc}
  59. */
  60. public function startQuery($sql, array $params = null, array $types = null)
  61. {
  62. $this->queryStartTime = microtime(true);
  63. $this->queries[] = $this->generateSql($sql, $params, $types);
  64. }
  65. /**
  66. * {@inheritdoc}
  67. */
  68. public function stopQuery()
  69. {
  70. $ms = round(microtime(true) - $this->queryStartTime, 4) * 1000;
  71. $this->queryExecutionTimes[] = $ms;
  72. $this->totalExecutionTime += $ms;
  73. }
  74. /**
  75. * Clean all collected data
  76. *
  77. * @return QueryAnalyzer
  78. */
  79. public function cleanUp()
  80. {
  81. $this->queries = array();
  82. $this->queryExecutionTimes = array();
  83. $this->totalExecutionTime = 0;
  84. return $this;
  85. }
  86. /**
  87. * Dump the statistics of executed queries
  88. *
  89. * @param boolean $dumpOnlySql
  90. * @return void
  91. */
  92. public function getOutput($dumpOnlySql = false)
  93. {
  94. $output = '';
  95. if (!$dumpOnlySql) {
  96. $output .= 'Platform: ' . $this->platform->getName() . PHP_EOL;
  97. $output .= 'Executed queries: ' . count($this->queries) . ', total time: ' . $this->totalExecutionTime . ' ms' . PHP_EOL;
  98. }
  99. foreach ($this->queries as $index => $sql) {
  100. if (!$dumpOnlySql) {
  101. $output .= 'Query(' . ($index+1) . ') - ' . $this->queryExecutionTimes[$index] . ' ms' . PHP_EOL;
  102. }
  103. $output .= $sql . ';' . PHP_EOL;
  104. }
  105. $output .= PHP_EOL;
  106. return $output;
  107. }
  108. /**
  109. * Index of the slowest query executed
  110. *
  111. * @return integer
  112. */
  113. public function getSlowestQueryIndex()
  114. {
  115. $index = 0;
  116. $slowest = 0;
  117. foreach ($this->queryExecutionTimes as $i => $time) {
  118. if ($time > $slowest) {
  119. $slowest = $time;
  120. $index = $i;
  121. }
  122. }
  123. return $index;
  124. }
  125. /**
  126. * Get total execution time of queries
  127. *
  128. * @return integer
  129. */
  130. public function getTotalExecutionTime()
  131. {
  132. return $this->totalExecutionTime;
  133. }
  134. /**
  135. * Get all queries
  136. *
  137. * @return array
  138. */
  139. public function getExecutedQueries()
  140. {
  141. return $this->queries;
  142. }
  143. /**
  144. * Get number of executed queries
  145. *
  146. * @return integer
  147. */
  148. public function getNumExecutedQueries()
  149. {
  150. return count($this->queries);
  151. }
  152. /**
  153. * Get all query execution times
  154. *
  155. * @return array
  156. */
  157. public function getExecutionTimes()
  158. {
  159. return $this->queryExecutionTimes;
  160. }
  161. /**
  162. * Create the SQL with mapped parameters
  163. *
  164. * @param string $sql
  165. * @param array $params
  166. * @param array $types
  167. * @return sql
  168. */
  169. private function generateSql($sql, $params, $types)
  170. {
  171. if (!count($params)) {
  172. return $sql;
  173. }
  174. $converted = $this->getConvertedParams($params, $types);
  175. if (is_int(key($params))) {
  176. $index = key($converted);
  177. $sql = preg_replace_callback('@\?@sm', function($match) use (&$index, $converted) {
  178. return implode(' ', $converted[$index++]);
  179. }, $sql);
  180. } else {
  181. foreach ($converted as $key => $value) {
  182. $sql = str_replace(':' . $key, $value, $sql);
  183. }
  184. }
  185. return $sql;
  186. }
  187. /**
  188. * Get the converted parameter list
  189. *
  190. * @param array $params
  191. * @param array $types
  192. * @return array
  193. */
  194. private function getConvertedParams($params, $types)
  195. {
  196. $result = array();
  197. foreach ($params as $position => $value) {
  198. if (isset($types[$position])) {
  199. $type = $types[$position];
  200. if (is_string($type)) {
  201. $type = Type::getType($type);
  202. }
  203. if ($type instanceof Type) {
  204. $value = $type->convertToDatabaseValue($value, $this->platform);
  205. }
  206. } else {
  207. if (is_object($value) && $value instanceof \DateTime) {
  208. $value = $value->format($this->platform->getDateTimeFormatString());
  209. } elseif (!is_null($value)) {
  210. $type = Type::getType(gettype($value));
  211. $value = $type->convertToDatabaseValue($value, $this->platform);
  212. }
  213. }
  214. if (is_string($value)) {
  215. $value = "'{$value}'";
  216. } elseif (is_null($value)) {
  217. $value = 'NULL';
  218. }
  219. $result[$position] = $value;
  220. }
  221. return $result;
  222. }
  223. }