<commit_before><?php
use Darya\Storage\Query;
use Darya\Storage\InMemory;
use Darya\Database\Storage;
use Darya\Database\Query\Translator;

class MySqlTest extends PHPUnit_Framework_TestCase {
	
	protected function translator() {
		return new Translator\MySql;
	}
	
	protected function mockConnection() {
		return $this->getMockBuilder('Darya\Database\Connection')->getMock();
	}
	
	public function testSelect() {
		$translator = $this->translator();
		
		$query = new Query('users');
		$query->where('age >=', 23)
			->where('name like', '%test%')
			->order('id')
			->limit(5);
		
		$result = $translator->translate($query);
		$this->assertEquals("SELECT * FROM `users` WHERE `age` >= ? AND `name` LIKE ? ORDER BY `id` ASC LIMIT 5", $result->string);
		$this->assertEquals(array(23, '%test%'), $result->parameters);
		
		$query->fields(array('id', 'firstname', 'lastname'));
		$query->where('role_id', array(1, 2, '3', '4', 5));
		$query->limit(0);
		
		$result = $translator->translate($query);
		$this->assertEquals("SELECT `id`, `firstname`, `lastname` FROM `users` WHERE `age` >= ? AND `name` LIKE ? AND `role_id` IN (?, ?, ?, ?, ?) ORDER BY `id` ASC", $result->string);
		$this->assertEquals(array(23, '%test%', 1, 2, '3', '4', 5), $result->parameters);
	}
	
	public function testSelectWithJoins() {
		$translator = $this->translator();
		
		$query = new Query('users', array('users.*'));
		$query->join('posts', 'posts.user_id = users.id');
		$query->where('posts.title like', '%swag%');
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `users`.* FROM `users` "
			. "JOIN `posts` ON `posts`.`user_id` = `users`.`id` "
			. "WHERE `posts`.`title` LIKE ?",
			$result->string
		);
		
		$query->join('comments as c', 'c.post_id = posts.id');
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `users`.* FROM `users` "
			. "JOIN `posts` ON `posts`.`user_id` = `users`.`id` "
			. "JOIN `comments` `c` ON `c`.`post_id` = `posts`.`id` "
			. "WHERE `posts`.`title` LIKE ?",
			$result->string
		);
	}
	
	public function testSelectWithComplexJoins() {
		$translator = $this->translator();
		
		$query = new Query('pages', array('pages.*'));
		
		$query->join('sections as s', function($join) {
			$join->on('s.page_id = pages.id')
				->on('s.page_id = pages.id')
				->where('pages.id > ', 1)
				->where('or', array('s.page_id >' => 2, 's.id >' => 3));
		});
		
		$query->where('page.awesome', true);
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `pages`.* FROM `pages` "
			. "JOIN `sections` `s` ON `s`.`page_id` = `pages`.`id` "
			. "AND `s`.`page_id` = `pages`.`id` AND `pages`.`id` > ? "
			. "AND (`s`.`page_id` > ? OR `s`.`id` > ?) "
			. "WHERE `page`.`awesome` IS TRUE",
			$result->string
		);
		
		$this->assertEquals(array(1, 2, 3), $result->parameters);
	}
	
	public function testSelectWithSubqueries() {
		$translator = $this->translator();
		
		$query = new Query('pages');
		
		$subquery = new Query('old_pages', array('id'));
		$subquery->where('id >', 1);
		
		$query->where('id not in', $subquery);
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT * FROM `pages` "
			. "WHERE `id` NOT IN (SELECT `id` FROM `old_pages` WHERE `id` > ?)",
			$result->string
		);
		
		$this->assertEquals(array(1), $result->parameters);
	}
	
	public function testSelectWithJoinSubqueries() {
		
	}
	
	public function testSelectWithQueryBuilderFilterValues() {
		$connection = $this->getMockBuilder('Darya\Database\Connection')->getMock();
		$storage = new Storage($connection);
		
		$builder = $storage->query('table');
		
		$tables = array('table_2', 'table_3', 'table_4', 'table_5');
		
		$current = $builder;
		
		foreach ($tables as $table) {
			$new = $storage->query($table, 'id');
			
			$current->where('id not in', $new);
			
			$current = $new;
		}
		
		$translator = $this->translator();
		
		$result = $translator->translate($builder->query);
		
		// Fuck yeah
		$this->assertEquals("SELECT * FROM `table` "
			. "WHERE `id` NOT IN ("
				. "SELECT `id` FROM `table_2` "
				. "WHERE `id` NOT IN ("
					. "SELECT `id` FROM `table_3` "
					. "WHERE `id` NOT IN ("
						. "SELECT `id` FROM `table_4` "
						. "WHERE `id` NOT IN ("
							. "SELECT `id` FROM `table_5`"
						. ")"
					. ")"
				. ")"
			. ")",
			$result->string
		);
	}
	
	public function testInsert() {
		$translator = $this->translator();
		
		$query = new Query('users');
		$query->create(array(
			'firstname' => 'Chris',
			'lastname'  => 'Andrew',
			'age'       => 23,
			'role_id'   => 1
		));<commit_msg>Tested parameters in nested query builder filter values.<commit_after><?php
use Darya\Storage\Query;
use Darya\Storage\InMemory;
use Darya\Database\Storage;
use Darya\Database\Query\Translator;

class MySqlTest extends PHPUnit_Framework_TestCase {
	
	protected function translator() {
		return new Translator\MySql;
	}
	
	protected function mockConnection() {
		return $this->getMockBuilder('Darya\Database\Connection')->getMock();
	}
	
	public function testSelect() {
		$translator = $this->translator();
		
		$query = new Query('users');
		$query->where('age >=', 23)
			->where('name like', '%test%')
			->order('id')
			->limit(5);
		
		$result = $translator->translate($query);
		$this->assertEquals("SELECT * FROM `users` WHERE `age` >= ? AND `name` LIKE ? ORDER BY `id` ASC LIMIT 5", $result->string);
		$this->assertEquals(array(23, '%test%'), $result->parameters);
		
		$query->fields(array('id', 'firstname', 'lastname'));
		$query->where('role_id', array(1, 2, '3', '4', 5));
		$query->limit(0);
		
		$result = $translator->translate($query);
		$this->assertEquals("SELECT `id`, `firstname`, `lastname` FROM `users` WHERE `age` >= ? AND `name` LIKE ? AND `role_id` IN (?, ?, ?, ?, ?) ORDER BY `id` ASC", $result->string);
		$this->assertEquals(array(23, '%test%', 1, 2, '3', '4', 5), $result->parameters);
	}
	
	public function testSelectWithJoins() {
		$translator = $this->translator();
		
		$query = new Query('users', array('users.*'));
		$query->join('posts', 'posts.user_id = users.id');
		$query->where('posts.title like', '%swag%');
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `users`.* FROM `users` "
			. "JOIN `posts` ON `posts`.`user_id` = `users`.`id` "
			. "WHERE `posts`.`title` LIKE ?",
			$result->string
		);
		
		$query->join('comments as c', 'c.post_id = posts.id');
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `users`.* FROM `users` "
			. "JOIN `posts` ON `posts`.`user_id` = `users`.`id` "
			. "JOIN `comments` `c` ON `c`.`post_id` = `posts`.`id` "
			. "WHERE `posts`.`title` LIKE ?",
			$result->string
		);
	}
	
	public function testSelectWithComplexJoins() {
		$translator = $this->translator();
		
		$query = new Query('pages', array('pages.*'));
		
		$query->join('sections as s', function($join) {
			$join->on('s.page_id = pages.id')
				->on('s.page_id = pages.id')
				->where('pages.id > ', 1)
				->where('or', array('s.page_id >' => 2, 's.id >' => 3));
		});
		
		$query->where('page.awesome', true);
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT `pages`.* FROM `pages` "
			. "JOIN `sections` `s` ON `s`.`page_id` = `pages`.`id` "
			. "AND `s`.`page_id` = `pages`.`id` AND `pages`.`id` > ? "
			. "AND (`s`.`page_id` > ? OR `s`.`id` > ?) "
			. "WHERE `page`.`awesome` IS TRUE",
			$result->string
		);
		
		$this->assertEquals(array(1, 2, 3), $result->parameters);
	}
	
	public function testSelectWithSubqueries() {
		$translator = $this->translator();
		
		$query = new Query('pages');
		
		$subquery = new Query('old_pages', array('id'));
		$subquery->where('id >', 1);
		
		$query->where('id not in', $subquery);
		
		$result = $translator->translate($query);
		
		$this->assertEquals(
			"SELECT * FROM `pages` "
			. "WHERE `id` NOT IN (SELECT `id` FROM `old_pages` WHERE `id` > ?)",
			$result->string
		);
		
		$this->assertEquals(array(1), $result->parameters);
	}
	
	public function testSelectWithJoinSubqueries() {
		
	}
	
	public function testSelectWithQueryBuilderFilterValues() {
		$connection = $this->getMockBuilder('Darya\Database\Connection')->getMock();
		$storage = new Storage($connection);
		
		$builder = $storage->query('table');
		
		$tables = array('table_2', 'table_3', 'table_4', 'table_5');
		
		$current = $builder;
		
		foreach ($tables as $table) {
			$new = $storage->query($table, 'id');
			
			$current->where('id not in', $new);
			
			$current = $new;
		}
		
		$current->where('id', 'swag');
		
		$translator = $this->translator();
		
		$result = $translator->translate($builder->query);
		
		// Fuck yeah
		$this->assertEquals("SELECT * FROM `table` "
			. "WHERE `id` NOT IN ("
				. "SELECT `id` FROM `table_2` "
				. "WHERE `id` NOT IN ("
					. "SELECT `id` FROM `table_3` "
					. "WHERE `id` NOT IN ("
						. "SELECT `id` FROM `table_4` "
						. "WHERE `id` NOT IN ("
							. "SELECT `id` FROM `table_5` "
							. "WHERE `id` = ?"
						. ")"
					. ")"
				. ")"
			. ")",
			$result->string
		);
		
		$this->assertEquals(array('swag'), $result->parameters);
	}
	
	public function testInsert() {
		$translator = $this->translator();
		
		$query = new Query('users');
		$query->create(array(
			'firstname' => 'Chris',
			'lastname'  => 'Andrew',
			'age'       => 23,
			'role_id'   => 1
		));
{'fraction_non_alphanumeric': 0.2173223350253807, 'fraction_numerical': 0.008037225042301184, 'mean_word_length': 3.4482596425211667, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 164, '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': '5211757', 'n_tokens_mistral': 3959, 'n_tokens_neox': 3286, 'n_words': 697}