/tests/test_PDO_SQLite.php

<?php



// version: 3.0.0



require_once(dirname(__FILE__) . '/../surosql/Surosql.class.php');

require_once(dirname(__FILE__) . '/../surosql/SurosqlOO.class.php');

require_once(dirname(__FILE__) . '/../surosql/SurosqlPDO.class.php');



require_once(dirname(__FILE__) . '/../surosql/SurosqlPDOCache.class.php');

require_once(dirname(__FILE__) . '/../surosql/suracache/SuraCache.class.php');

require_once(dirname(__FILE__) . '/../surosql/suracache/SuraCacheRequest.class.php');



use ns\SurosqlOO, ns\SurosqlBindable, ns\SurosqlPDOCache, ns\SurosqlOOManager, ns\SurosqlPDO;

use ns\Surosql as osql;

use \Exception;





class User extends SurosqlOO {

	static $surosql = array(

			'columns' => array('id', 'name', 'registrationdate', 'meta', 'meta2'),

			'primary' => 'id',

			'alias' => 'user',

			'serialization' => array('meta' => array('json_encode', 'json_decode'), 'meta2'),

			'table' => 'wp_users');

	public $unused_var;

}



class wp_postmeta {

	static $surosql = array(

			'primary' => 'id',

			);

	static $unused_var;

	public $id;

	public $meta;

}



class Comment {

	static $surosql = array(

		'table' => 'wp_comments',

		'alias' => 'comment',

		'connections' => array(

			'author(user)' => '`%comment%`.`author_id` = `%author%`.`id`'

			)

		);

	public $content;

}



spl_autoload_register(create_function('$a', 'throw new Exception("Loading unnecessary class: " . addslashes($a));'));



class SurosqlTest {

	static $assertValues = array();



	function __construct() {

		$this->osql = $osql = SurosqlPDO::register('dsn=sqlite::memory:');



		$osql->aliases['post'] = array('table' => 'wp_posts', 'where' => '`%post%`.`approved` = 1', 'columns' => array('id', 'title'));

		$osql->aliases['user2'] = array('class' => 'UnexistingCLass', 'table' => 'wp_users', 'columns' => array('id', 'name'));

		$osql->aliases['alias_with_columns'] = array('table' => 'table', 'columns' => array());



		$osql->addclass('Comment');

		$osql->addclass('User');



		$osql->connect('post - comment', '`%post%`.`id` = `%comment%`.`post_id`')

			->connect('post - author(user)', '`%post%`.`author_id` = `%author%`.`id`')

			->connect('post - meta(wp_postmeta)', '`%post%`.`id` = `%meta%`.`post_id`')

			->connect('user - friendships', '`%user%`.`id` = `%friendships%`.`first`')

			->connect('friendships - friend(user)', '`%friendships%`.`second` = `%friend%`.`id`')

			->connect('user - friend(user)', 'friendships - friend', array('jointype' => 'm2m'));



		$osql->exec(file_get_contents(dirname(__FILE__) . '/test_db.sqlite.sql'));

	}



	function testBindables() {

		assert(osql::q('5 < ', 6, ' AND a IN', array(1, 2, 3))->parts === array('5 < ', 6, ' AND a IN (', 1, ', ', 2, ', ', 3, ') '));

		assert(osql::q('a < ', 1)->andq('b > ', 2)->parts === array('(a < ', 1 ,') AND (b > ', 2, ')'));

		assert(osql::q('a < ', 1, '+1')->orq('b > ', 2, '+1')->parts === array('(a < ', 1, '+1) OR (b > ', 2, '+1)'));

		assert(osql::q('a <', 1)->andq('b >', 2)->orq('c > ', 2, ' +1')->orq('d ==', 4)->parts === array ('(((a <', 1, ') AND (b >', 2, ')) OR (c > ', 2, ' +1)) OR (d ==', 4, ')'));

	}



	function testQuoting() {

		$osql = $this->osql;



		assert($osql->quote("'") === "''''");

		assert($osql->quote_rec(array("'" => "'")) === array("'" => "''''"));

		assert($osql->quote_rec(array(array("'")), 1) === array("'Array'"));

		assert($osql->quote_rec((object) array('a' => "'"))->a === "''''");

	}



/*

	function testOperators() {

		$osql = $this->osql;



		assert($osql->is('a <', '5')->str === '(a <  :bound_var_1th)');

		assert($osql->from('table')->ifeq('id', 2)->ifis('age <', 10)->sql() === 'SELECT * FROM `table` WHERE ((((id = :bound_var_2th))) AND ((age <  :bound_var_3th)))');



		assert($osql->eq('123', '3,4,5')->str === '(123 = :bound_var_4th)');

		assert($osql->neq(array('123', '456'), '3,4,5')->str === '(123 != :bound_var_5th) AND (456 != :bound_var_6th)');

		assert($osql->in('123', array('3', '4\'', '5'))->str === '(123 IN (:bound_var_7th, :bound_var_8th, :bound_var_9th))');

		assert($osql->from('a')->ifnin('b', array('c', 'd'))->sql() === 'SELECT * FROM `a` WHERE (((b NOT IN (:bound_var_10th, :bound_var_11th))))');



		assert($osql->like(array('f1', 'f2'), "%'v'%")->str === '(f1 LIKE :bound_var_12th) AND (f2 LIKE :bound_var_13th)');

		assert($osql->like('f1', "%'v'%")->str === '(f1 LIKE :bound_var_14th)');

		assert($osql->from('tbl')->iflike('f', 'v')->sql() === 'SELECT * FROM `tbl` WHERE (((f LIKE :bound_var_15th)))');

	}



	function testLogicalConnectives() {

		$osql = $this->osql;



		assert($osql->logand('1', '2', array('2', '3'))->str === '(1) AND (2) AND (2) AND (3)');

		assert($osql->logor(array())->str === '0');

		assert($osql->logand()->str === '1');

	}

*/



	function testPropertieRetrieval() {

		$osql = $this->osql;



		assert($osql->count()->limit(0, 30)->limit() === '0,30');

		assert($osql->count()->select() === 'COUNT(*)');

	}



	function testSQLConstruction() {

		$osql = $this->osql;



		assert($osql->wp_postmeta->sql() === 'SELECT `wp_postmeta`.`id` AS `wp_postmeta_id`, `wp_postmeta`.`meta` AS `wp_postmeta_meta` FROM `wp_postmeta`');

		assert($osql->count()->from('abc AS `def`, `ghi` jkl')->join('INNER JOIN `123`')->sql() === 'SELECT COUNT(*) FROM `abc` AS `def` ,`ghi` AS `jkl` INNER JOIN `123`');

		assert($osql->count()->from('abc AS `def`, `ghi` jkl')->sql() === 'SELECT COUNT(*) FROM `abc` AS `def` ,`ghi` AS `jkl`');

	}



	function testSelectParsing() {

		assert(osql::select('"a %p, a" a, %p, \'"", %p, \' b, %u')->from('comment p, comment u')->sql() === 'SELECT "a %p, a" a, `p`.`content` AS `p_content`, \'"", %p, \' b, `u`.`content` AS `u_content` FROM `wp_comments` AS `p` ,`wp_comments` AS `u`');

		assert(osql::select('"" a')->from('p')->sql() === 'SELECT "" a FROM `p`');

		assert(osql::select('"\"" a')->from('p')->sql() === 'SELECT "\"" a FROM `p`');

		assert(osql::select('"\\\'" a')->from('p')->sql() === 'SELECT "\\\'" a FROM `p`');

		assert(osql::select('"\\\\" a')->from('p')->sql() === 'SELECT "\\\\" a FROM `p`');

	}



	function testPHP52Style() {

		if(version_compare(PHP_VERSION, '5.3', '>=')) {

			return;

		}



		assert(osql('user')->sql() === 'SELECT `user`.`id` AS `user_id`, `user`.`name` AS `user_name`, `user`.`registrationdate` AS `user_registrationdate`, `user`.`meta` AS `user_meta`, `user`.`meta2` AS `user_meta2` FROM `wp_users` AS `user`');

	}



	function testPHP53Style() {

		if(version_compare(PHP_VERSION, '5.3', '<')) {

			return;

		}



		assert(osql::count()->from('table')->sql() === 'SELECT COUNT(*) FROM `table`');

		$osql = $this->osql;

		assert($osql('user')->sql() === 'SELECT `user`.`id` AS `user_id`, `user`.`name` AS `user_name`, `user`.`registrationdate` AS `user_registrationdate`, `user`.`meta` AS `user_meta`, `user`.`meta2` AS `user_meta2` FROM `wp_users` AS `user`');

	}



	function testNoClassExistsCall() {

		$osql = $this->osql;



		assert($osql->from('UnexistingTable')->sql() === 'SELECT * FROM `UnexistingTable`');



		osql::fromoo('alias_with_columns')->sql(); // no exception == good

	}



	function testDataRetrieval() {

		$osql = $this->osql;



		assert($osql->select('id')->from('wp_postmeta')->col() === array('1'));

		assert($osql->select()->from('wp_postmeta')->ifeq('id', 1)->first()->id == 1);



		assert($osql->count()->from('wp_posts')->var() == 3);

		assert($osql->count()->from('wp_posts s')->var() == 3);

		assert($osql->count()->from('`post`')->var() == 2);

		assert($osql->count()->from('post AS `s`')->var() == 2);



		assert($osql->select('"a %p, a" a, %p, \'"", %p, \' b, %u')->from('post p, post u')->first()->a === 'a %p, a');

		assert($osql->select('%*, "c" c')->from('post a, post b')->first()->b->id == 1);



		assert($osql->select()->from('user u, comment')->first() instanceof stdClass);

		assert($osql->select('%*')->from('post p')->first()->title === 'My first post!');



		assert($osql->select()->from('user2')->first()->name !== null);

	}



	function testObjectOrientatedDataRetrieval() {

		$osql = $this->osql;



		assert($osql->selectoo()->from('comment')->select('%*')->sql() === 'SELECT `comment`.`content` AS `comment_content` FROM `wp_comments` AS `comment`');

		assert($osql->selectoo()->from('post p')->where('p.id=-1')->fetch() === false);

		assert($osql->selectoo()->from('post p')->where('p.id=-1')->first(0) === 0);

		assert($osql->selectoo('%m, m.meta')->from('wp_postmeta m')->first()->meta === 'meta post 1');

		assert($osql->selectoo()->from('User u, Comment')->first()->u instanceof User);

		assert($osql->selectoo('%m')->from('wp_postmeta m')->first() instanceof wp_postmeta);

		assert(count($osql->selectoo()->from('post - comment')->fetchAll()) == 1);

	}



	function testDataRetrievalFromManager() {

		assert(User::get(1)->first()->id == 1);

		assert(User::get()->ifeq('id', 1)->first()->id == 1);

		assert(User::get(array('id' => 1))->first()->id == 1);

	}



	function testDataRetrievalWithRelations() {

		$osql = $this->osql;



		assert($osql->count()->from('comment - author')->fetchVar() == 1);

		assert($osql->count('s.title, comment.content AS comment')->from('post s - comment')->var() == 1);

		assert($osql->count()->from('post - (comment c, author, meta AS `m`)')->var() == 1);

		assert($osql->count()->from('post s - (`comment`, author)')->var() == 1);

	}



	function testDataRetrievalWithManyToManyRelation() {

		$osql = $this->osql;



		assert($osql->count()->from('user u - friend f')->var() == 1);

	}



	function testDataModification() {

		$osql = $this->osql;



		assert($osql->exec('UPDATE wp_users SET name="Willem" WHERE id=1') == 1);

		assert($osql->update('user')->set('name = ?, registrationdate = date("NOW")')->where('id=?')->bind('Willem', 1)->exec() == 1);

		assert($osql->update('user')->set(array('name' => 'Johan'))->where('id=:idvar')->bind(array('idvar' => '1'))->exec() == 1);

		assert($osql->update('user')->setq('name=', 'Hans')->whereq('id=', 1)->exec() == 1);

		assert(User::get(1)->first()->name === 'Hans');



		assert($osql->insert('wp_postmeta', array('id' => 5, 'post_id' => 1, 'meta' => 'meta')));



		assert(count($osql->select()->from('User')->all()) === 4);

		assert(count($osql->selectoo('u.id')->from('User u')->all()) === 4);

		assert(count($osql->selectoo()->from('user')->all()) === 4);



		try {

			assert($osql->selectoo()->from('post p')->where('p.id=-1')->first() || false);

			assert(false);

		} catch(Exception $e) {

			assert($e->getMessage() == 'Trying to fetch from empty resultset.');

		}



		SuraCache::register('backend=SuraCacheRequest');

		SurosqlPDOCache::register();



		assert($osql->count()->from('wp_postmeta')->cached_var() == 2);



		$meta = new wp_postmeta();

		$meta->meta = 'test123';

		$meta->post_id = 1;

		assert((int) SurosqlOOManager::insertorupdate($meta) > 0); // insert



		$meta->post_id = 78;

		assert((int) SurosqlOOManager::insertorupdate($meta) > 0); // update



		assert(osql::count()->from('wp_postmeta')->ifeq('post_id', 78)->var() == 1); // check



		assert($osql->count()->from('wp_postmeta')->cached_var() == 2);

		assert($osql->count()->from('wp_postmeta')->var() == 3);



		$user = new User(array('name' => 'Lowie', 'blabla' => 'bloblo'));

		assert(!isset($user->blabla));

		$user->meta = array('key' => 'value');

		$user->meta2 = array('key' => 'value');

		$user->insert(null, array('registrationdate' => 'date("NOW")'));



		assert($user->id > 0);

		assert(strlen($osql->selectoo()->from('user')->where('id=?')->bind($user->id)->first()->registrationdate) > 7);

		assert($osql->selectoo()->from('user')->where('id=?')->bind($user->id)->first()->meta->key == 'value');

		assert($osql->selectoo()->from('user')->where('id=?')->bind($user->id)->first()->meta2['key'] == 'value');



		$user->name = 'Hedwig';

		assert($user->update(array('name')) == 1);

		assert($osql->updateobj($user, array(), array('registrationdate' => 'date("now")')) == 1);

		$user->id = -1;

		try {

			$user->update();

			assert(false);

		} catch(Exception $e) {

			assert(strpos($e->getMessage(), 'No rows found to update.') !== false);

		}



		try {

			$user = new User();

			$user->id = 1;

			$user->name = 'Jos';

			$osql->insertobj($user);

			assert(false);

		} catch(PDOException $e) {

			assert(strpos($e->getMessage(), 'Integrity constraint violation') !== false);

		}



		assert($osql->count()->from('user')->where('id=?')->bind($user->id)->var() == 1);

		$user->delete();

		assert($osql->count()->from('user')->where('id=?')->bind($user->id)->var() == 0);



		$iterator_count = 0;

		foreach($osql->select()->from('user') as $u) {

			$iterator_count++;

		}

		assert($iterator_count > 2);

	}



	static function setupAssertHandler() {

		self::$assertValues = array(

			'ASSERT_ACTIVE' => assert_options(ASSERT_ACTIVE, 1),

			'ASSERT_WARNING' => assert_options(ASSERT_WARNING, 0),

			'ASSERT_CALLBACK' => assert_options(ASSERT_CALLBACK, create_function('$file,$line,$code', 'echo "------------\nAssertion Failed: File \'$file\'\nLine \'$line\'\nCode \'$code\'\n------------\n";')),

			'error_reporting' => error_reporting(E_ERROR),

			'error_handler' => set_error_handler(create_function('$errno, $errstr, $errfile, $errline', 'if(error_reporting() != 0) {throw new Exception($errstr, $errno);}'))

			);

	}



	static function tearDownAssertHandler() {

		assert_options(ASSERT_ACTIVE, self::$assertValues['ASSERT_ACTIVE']);

		assert_options(ASSERT_WARNING, self::$assertValues['ASSERT_WARNING']);

		assert_options(ASSERT_CALLBACK, self::$assertValues['ASSERT_CALLBACK']);

		error_reporting(self::$assertValues['error_reporting']);

		set_error_handler(is_null(self::$assertValues['error_handler']) ? create_function('', 'return false;') : self::$assertValues['error_handler']);

	}



	static function runTests() {

		self::setupAssertHandler();

		echo "<pre>\n";

		$inst = new self();

		foreach(get_class_methods($inst) as $test) {

			if(substr($test, 0, 4) == 'test') {

				echo '-<b>', substr($test, 4), "</b>\n";

				call_user_func(array($inst, $test));

			}

		}

		echo "------------\nAll tests executed\n</pre>";

		self::tearDownAssertHandler();

	}

}



SurosqlTest::runTests();



?>