<commit_before><?php
namespace Rap2hpoutre\MySQLExplainExplain;

use \Jasny\MySQL\DB as DB;
use \Jasny\MySQL\DB_Exception as DB_Exception;

/**
 * Class Row
 * @package Rap2hpoutre\MySQLExplainExplain
 */
class Row {
	/**
	 * @var array
	 */
	public $cells = array();

	/**
	 * @var array
	 */
	private $_keys = array();

	/**
	 * On peut accéder à la ligne précédente (utile pour analyser les jointures)
	 * @var Row
	 */
	private $_previous_row = null;

	/**
	 * @param $row
	 * @param Row $prev
	 */
	public function __construct($row, Row $prev = null) {
		foreach($row as $k => $v) {
			$this->cells[$k] = new Cell($v);
		}
		if ($prev !== null) {
			$this->_previous_row = $prev;
		}

		$this->buildTableSchema();
		$this->initKeys($this->cells['table']->v);

		$this->performSelectTypeAnalysis();
		$this->performExtraAnalysis();
		$this->performKeyAnalysis();
		$this->performTypeAnalysis();
		$this->performRefAnalysis();

		$this->cells['id']->info = 'SELECT identifier #' . $this->cells['id']->v;
		$this->cells['rows']->info = "MySQL believes it must examine {$this->cells['rows']->v} rows to execute the query";
	}

	/**
	 * Analyse de la colonne type
	 */
	public function performSelectTypeAnalysis() {
		$infos = array(
			'SIMPLE' => 'Simple SELECT (not using UNION or subqueries)',
			'PRIMARY' => 'Outermost SELECT',
			'UNION' => 'Second or later SELECT statement in a UNION',
			'DEPENDENT' => 'UNION	Second or later SELECT statement in a UNION, dependent on outer query',
			'UNION RESULT' => 'Result of a UNION.',
			'SUBQUERY' => 'First SELECT in subquery',
			'DEPENDENT SUBQUERY' => 'First SELECT in subquery, dependent on outer query',
			'DERIVED' => 'Derived table SELECT (subquery in FROM clause)',
			'MATERIALIZED' => 'Materialized subquery',
			'UNCACHEABLE SUBQUERY' => 'A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query',
			'UNCACHEABLE UNION' => 'The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)'
		);
		$this->cells['select_type']->info = $infos[$this->cells['select_type']->v];
	}

	/**
	 * Analyse de la colonne extra
	 */
	public function performExtraAnalysis() {
		// La colonne extra contient des infos multiples alors on utilise un tableau d'information
		// Par contre l'état (danger ou success) reste global à la cellule et est à gérer au cas par cas
		$infos = array();
		// Contient Using temporary; Using filesort
		if (preg_match('/Using temporary;\\s*Using filesort/', $this->cells['Extra']->v)) {
			$this->cells['Extra']->setDanger();
			$infos[] = 	'<p>You should avoid <code>Using temporary</code> and <code>Using filesort</code> on big queries.
						It means a temporary table is created, and a sort is performed on that temporary table</p>
						<ul>
							<li>Using temporary: To resolve the query, MySQL needs to create a temporary table to hold the result</li>
							<li>Using filesort: MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
							The sort is done by going through all rows according to the join type and storing the sort key and 
							pointer to the row for all rows that match the WHERE clause.</li>
						</ul>';
		} elseif (preg_match('/Using temporary(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = 'To resolve the query, MySQL needs to create a temporary table to hold the result';
		} elseif (preg_match('/Using filesort(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = 'MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
						The sort is done by going through all rows according to the join type and storing the sort key and
						pointer to the row for all rows that match the WHERE clause';
		}
		// Contient Impossible WHERE noticed after reading const tables
		if (preg_match('/Impossible WHERE noticed after reading const tables/', $this->cells['Extra']->v)) {
			$infos[] = 	'MySQL has read all <code>const</code> (and <code>system</code>) tables and 
						notice that the WHERE clause is always false';
		}
		// Contient Using where
		if(preg_match('/Using where(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "A WHERE clause is used to restrict which rows to match against the next table or send to the client.
						Unless you specifically intend to fetch or examine all rows from the table, you may have something
						wrong in your query if the <code>Extra</code> value is not <code>Using where</code> and the table join
						type is <code>ALL</code> or <code>index</code>.";
		}
		// Contient Using join buffer
		if(preg_match('/Using join buffer \\((.*?)\\)/', $this->cells['Extra']->v, $matches)) {
			$infos[] = "Tables from earlier joins are read in portions into the join buffer, and then their rows
						are used from the buffer to perform the join with the current table
						<code>{$this->cells['table']->v}</code> using <code>{$matches[1]}</code> algorithm";
		}
		// Contient Using index
		if(preg_match('/Using index(;|$)/', $this->cells['Extra']->v)) {
			$tmp = "The column information is retrieved from the table using only information in the index tree
					without having to do an additional seek to read the actual row.
					This strategy can be used when the query uses only columns that are part of a single index.";
			if(preg_match('/Using where/', $this->cells['Extra']->v)) {
				$tmp .= "The index is being used to perform lookups of key values";
			} else {
				$tmp .= "The optimizer may be reading the index to avoid reading data rows but not using it for lookups.
						For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.";
			}
			$infos[] = $tmp;
		}
		// Contient const row not found
		if(preg_match('/const row not found/', $this->cells['Extra']->v)) {
			$infos[] = "The table was empty";
		}

		// Traités dans l'ordre de l'apparition dans la doc

		// Distinct
		if(preg_match('/Distinct(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "MySQL is looking for distinct values, so it stops searching for more rows
						for the current row combination after it has found the first matching row";
		}
		// Full scan on NULL key
		if(preg_match('/Full scan on NULL key(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "This occurs for subquery optimization as a fallback strategy
						when the optimizer cannot use an index-lookup access method.";
		}
		// Impossible HAVING
		if(preg_match('/Impossible HAVING(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The HAVING clause is always false and cannot select any rows.";
		}
		// Impossible WHERE
		if(preg_match('/Impossible WHERE(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The WHERE clause is always false and cannot select any rows.";
		}
		if (!count($infos)) {
			$infos[] = 'Not Implemented Now :(';
		}
		
		$this->cells['Extra']->info = implode('<br /><br />', $infos);<commit_msg>more infos in extra<commit_after><?php
namespace Rap2hpoutre\MySQLExplainExplain;

use \Jasny\MySQL\DB as DB;
use \Jasny\MySQL\DB_Exception as DB_Exception;

/**
 * Class Row
 * @package Rap2hpoutre\MySQLExplainExplain
 */
class Row {
	/**
	 * @var array
	 */
	public $cells = array();

	/**
	 * @var array
	 */
	private $_keys = array();

	/**
	 * On peut accéder à la ligne précédente (utile pour analyser les jointures)
	 * @var Row
	 */
	private $_previous_row = null;

	/**
	 * @param $row
	 * @param Row $prev
	 */
	public function __construct($row, Row $prev = null) {
		foreach($row as $k => $v) {
			$this->cells[$k] = new Cell($v);
		}
		if ($prev !== null) {
			$this->_previous_row = $prev;
		}

		$this->buildTableSchema();
		$this->initKeys($this->cells['table']->v);

		$this->performSelectTypeAnalysis();
		$this->performExtraAnalysis();
		$this->performKeyAnalysis();
		$this->performTypeAnalysis();
		$this->performRefAnalysis();

		$this->cells['id']->info = 'SELECT identifier #' . $this->cells['id']->v;
		$this->cells['rows']->info = "MySQL believes it must examine {$this->cells['rows']->v} rows to execute the query";
	}

	/**
	 * Analyse de la colonne type
	 */
	public function performSelectTypeAnalysis() {
		$infos = array(
			'SIMPLE' => 'Simple SELECT (not using UNION or subqueries)',
			'PRIMARY' => 'Outermost SELECT',
			'UNION' => 'Second or later SELECT statement in a UNION',
			'DEPENDENT' => 'UNION	Second or later SELECT statement in a UNION, dependent on outer query',
			'UNION RESULT' => 'Result of a UNION.',
			'SUBQUERY' => 'First SELECT in subquery',
			'DEPENDENT SUBQUERY' => 'First SELECT in subquery, dependent on outer query',
			'DERIVED' => 'Derived table SELECT (subquery in FROM clause)',
			'MATERIALIZED' => 'Materialized subquery',
			'UNCACHEABLE SUBQUERY' => 'A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query',
			'UNCACHEABLE UNION' => 'The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)'
		);
		$this->cells['select_type']->info = $infos[$this->cells['select_type']->v];
	}

	/**
	 * Analyse de la colonne extra
	 */
	public function performExtraAnalysis() {
		// La colonne extra contient des infos multiples alors on utilise un tableau d'information
		// Par contre l'état (danger ou success) reste global à la cellule et est à gérer au cas par cas
		$infos = array();
		// Contient Using temporary; Using filesort
		if (preg_match('/Using temporary;\\s*Using filesort/', $this->cells['Extra']->v)) {
			$this->cells['Extra']->setDanger();
			$infos[] = 	'<p>You should avoid <code>Using temporary</code> and <code>Using filesort</code> on big queries.
						It means a temporary table is created, and a sort is performed on that temporary table</p>
						<ul>
							<li>Using temporary: To resolve the query, MySQL needs to create a temporary table to hold the result</li>
							<li>Using filesort: MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
							The sort is done by going through all rows according to the join type and storing the sort key and 
							pointer to the row for all rows that match the WHERE clause.</li>
						</ul>';
		} elseif (preg_match('/Using temporary(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = 'To resolve the query, MySQL needs to create a temporary table to hold the result';
		} elseif (preg_match('/Using filesort(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = 'MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
						The sort is done by going through all rows according to the join type and storing the sort key and
						pointer to the row for all rows that match the WHERE clause';
		}
		// Contient Impossible WHERE noticed after reading const tables
		if (preg_match('/Impossible WHERE noticed after reading const tables/', $this->cells['Extra']->v)) {
			$infos[] = 	'MySQL has read all <code>const</code> (and <code>system</code>) tables and 
						notice that the WHERE clause is always false';
		}
		// Contient Using where
		if(preg_match('/Using where(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "A WHERE clause is used to restrict which rows to match against the next table or send to the client.
						Unless you specifically intend to fetch or examine all rows from the table, you may have something
						wrong in your query if the <code>Extra</code> value is not <code>Using where</code> and the table join
						type is <code>ALL</code> or <code>index</code>.";
		}
		// Contient Using join buffer
		if(preg_match('/Using join buffer \\((.*?)\\)/', $this->cells['Extra']->v, $matches)) {
			$infos[] = "Tables from earlier joins are read in portions into the join buffer, and then their rows
						are used from the buffer to perform the join with the current table
						<code>{$this->cells['table']->v}</code> using <code>{$matches[1]}</code> algorithm";
		}
		// Contient Using index
		if(preg_match('/Using index(;|$)/', $this->cells['Extra']->v)) {
			$tmp = "The column information is retrieved from the table using only information in the index tree
					without having to do an additional seek to read the actual row.
					This strategy can be used when the query uses only columns that are part of a single index.";
			if(preg_match('/Using where/', $this->cells['Extra']->v)) {
				$tmp .= "The index is being used to perform lookups of key values";
			} else {
				$tmp .= "The optimizer may be reading the index to avoid reading data rows but not using it for lookups.
						For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.";
			}
			$infos[] = $tmp;
		}
		// Contient const row not found
		if(preg_match('/const row not found/', $this->cells['Extra']->v)) {
			$infos[] = "The table was empty";
		}

		// Traités dans l'ordre de l'apparition dans la doc

		// Distinct
		if(preg_match('/Distinct(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "MySQL is looking for distinct values, so it stops searching for more rows
						for the current row combination after it has found the first matching row";
		}
		// Full scan on NULL key
		if(preg_match('/Full scan on NULL key(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "This occurs for subquery optimization as a fallback strategy
						when the optimizer cannot use an index-lookup access method.";
		}
		// Impossible HAVING
		if(preg_match('/Impossible HAVING(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The HAVING clause is always false and cannot select any rows.";
		}
		// Impossible WHERE
		if(preg_match('/Impossible WHERE(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The WHERE clause is always false and cannot select any rows.";
		}
		// LooseScan
		if(preg_match('/LooseScan(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The semi-join LooseScan strategy is used.";
		}
		// No matching min/max row
		if(preg_match('/No matching min\\/max row(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "No row satisfies the condition for a query such as <code>SELECT MIN(col) FROM table WHERE condition</code>";
		}
		// no matching row in const table
		if(preg_match('/no matching row in const table(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition";
		}
		// no matching row in const table
		if(preg_match('/No matching rows after partition pruning(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "For <code>DELETE</code> or <code>UPDATE</code>, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to <code>Impossible WHERE</code> for <code>SELECT</code> statements.";
		}
		// No tables used
		if(preg_match('/No tables used(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "The query has no <code>FROM</code> clause, or has a <code>FROM DUAL</code> clause.";
		}
		// Not exists
		if(preg_match('/Not exists(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "MySQL was able to do a <code>LEFT JOIN</code> optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the <code>LEFT JOIN</code> criteria";
		}
		// Plan isn't ready yet
		if(preg_match('/Plan isn\'t ready yet(;|$)/', $this->cells['Extra']->v)) {
			$infos[] = "This value occurs with EXPLAIN FOR CONNECTION when the optimizer has not finished creating the execution plan for the statement executing in the named connection. If execution plan output comprises multiple lines, any or all of them could have this Extra value, depending on the progress of the optimizer in determining the full execution plan.";
		}




		/*

		 */

		if (!count($infos)) {
			$infos[] = 'Not Implemented Now :(';
		}
		
		$this->cells['Extra']->info = implode('<br /><br />', $infos);
{'fraction_non_alphanumeric': 0.1411587091904133, 'fraction_numerical': 0.0003774297037176826, 'mean_word_length': 3.6417518248175185, 'pattern_counts': {'":': 0, '<': 83, '<?xml version=': 0, '>': 229, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1728655', 'n_tokens_mistral': 5483, 'n_tokens_neox': 4811, 'n_words': 1880}