QueryAnalyzer.php 6.0 KB

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