code:
<?php

Yii::import('system.db.CDbConnection');
Yii::import('system.db.ar.CActiveRecord');

require_once(dirname(__FILE__).'/../data/models.php');

class CActiveRecordTest extends CTestCase
{
	protected $backupStaticAttributes = true;

	private $_connection;

	protected function setUp()
	{
		if(!extension_loaded('pdo') || !extension_loaded('pdo_sqlite'))
			$this->markTestSkipped('PDO and SQLite extensions are required.');

		$this->_connection=new CDbConnection('sqlite::memory:');
		$this->_connection->active=true;
		$this->_connection->pdoInstance->exec(file_get_contents(dirname(__FILE__).'/../data/sqlite.sql'));
		CActiveRecord::$db=$this->_connection;
	}

	protected function tearDown()
	{
		$this->_connection->active=false;
	}

	public function testModel()
	{
		$model=Post::model();
		$this->assertTrue($model instanceof Post);
		$this->assertTrue($model->dbConnection===$this->_connection);
		$this->assertTrue($model->dbConnection->active);
		$this->assertEquals('posts',$model->tableName());
		$this->assertEquals('id',$model->tableSchema->primaryKey);
		$this->assertTrue($model->tableSchema->sequenceName==='');
		$this->assertEquals(array(),$model->attributeLabels());
		$this->assertEquals('Id',$model->getAttributeLabel('id'));
		$this->assertEquals('Author Id',$model->getAttributeLabel('author_id'));
		$this->assertTrue($model->getActiveRelation('author') instanceof CBelongsToRelation);
		$this->assertTrue($model->tableSchema instanceof CDbTableSchema);
		$this->assertTrue($model->commandBuilder instanceof CDbCommandBuilder);
		$this->assertTrue($model->hasAttribute('id'));
		$this->assertFalse($model->hasAttribute('comments'));
		$this->assertFalse($model->hasAttribute('foo'));
		$this->assertEquals(array('id'=>null,'title'=>null,'create_time'=>null,'author_id'=>null,'content'=>null),$model->attributes);

		$post=new Post;
		$this->assertNull($post->id);
		$this->assertNull($post->title);
		$post->setAttributes(array('id'=>3,'title'=>'test title'));
		$this->assertNull($post->id);
		$this->assertEquals('test title',$post->title);
	}

	public function testFind()
	{
		// test find() with various parameters
		$post=Post::model()->find();
		$this->assertTrue($post instanceof Post);
		$this->assertEquals(1,$post->id);

		$post=Post::model()->find('id=5');
		$this->assertTrue($post instanceof Post);
		$this->assertEquals(5,$post->id);

		$post=Post::model()->find('id=:id',array(':id'=>2));
		$this->assertTrue($post instanceof Post);
		$this->assertEquals(2,$post->id);

		$post=Post::model()->find(array('condition'=>'id=:id','params'=>array(':id'=>3)));
		$this->assertTrue($post instanceof Post);
		$this->assertEquals(3,$post->id);

		// test find() without result
		$post=Post::model()->find('id=6');
		$this->assertNull($post);

		// test findAll() with various parameters
		$posts=Post::model()->findAll();
		$this->assertEquals(5,count($posts));
		$this->assertTrue($posts[3] instanceof Post);
		$this->assertEquals(4,$posts[3]->id);

		$posts=Post::model()->findAll(new CDbCriteria(array('limit'=>3,'offset'=>1)));
		$this->assertEquals(3,count($posts));
		$this->assertTrue($posts[2] instanceof Post);
		$this->assertEquals(4,$posts[2]->id);

		// test findAll() without result
		$posts=Post::model()->findAll('id=6');
		$this->assertTrue($posts===array());

		// test findByPk
		$post=Post::model()->findByPk(2);
		$this->assertEquals(2,$post->id);

		$post=Post::model()->findByPk(array(3,2));
		$this->assertEquals(2,$post->id);

		$post=Post::model()->findByPk(array());
		$this->assertNull($post);

		$post=Post::model()->findByPk(6);
		$this->assertNull($post);

		// test findAllByPk
		$posts=Post::model()->findAllByPk(2);
		$this->assertEquals(1,count($posts));
		$this->assertEquals(2,$posts[0]->id);

		$posts=Post::model()->findAllByPk(array(4,3,2),'id<4');
		$this->assertEquals(2,count($posts));
		$this->assertEquals(2,$posts[0]->id);
		$this->assertEquals(3,$posts[1]->id);

		$posts=Post::model()->findAllByPk(array());
		$this->assertTrue($posts===array());

		// test findByAttributes
		$post=Post::model()->findByAttributes(array('author_id'=>2),array('order'=>'id DESC'));
		$this->assertEquals(4,$post->id);

		// test findAllByAttributes
		$posts=Post::model()->findAllByAttributes(array('author_id'=>2));
		$this->assertEquals(3,count($posts));

		// test findBySql
		$post=Post::model()->findBySql('select * from posts where id=:id',array(':id'=>2));
		$this->assertEquals(2,$post->id);

		// test findAllBySql
		$posts=Post::model()->findAllBySql('select * from posts where id>:id',array(':id'=>2));
		$this->assertEquals(3,count($posts));

		// test count
		$this->assertEquals(5,Post::model()->count());
		$this->assertEquals(3,Post::model()->count(array('condition'=>'id>2')));

		// test countBySql
		$this->assertEquals(1,Post::model()->countBySql('select id from posts limit 1'));

		// test exists
		$this->assertTrue(Post::model()->exists('id=:id',array(':id'=>1)));
		$this->assertFalse(Post::model()->exists('id=:id',array(':id'=>6)));
	}

	public function testInsert()
	{
		$post=new Post;
		$this->assertEquals(array('id'=>null,'title'=>null,'create_time'=>null,'author_id'=>null,'content'=>null),$post->attributes);
		$post->title='test post 1';
		$post->create_time=time();
		$post->author_id=1;
		$post->content='test post content 1';
		$this->assertTrue($post->isNewRecord);
		$this->assertNull($post->id);
		$this->assertTrue($post->save());
		$this->assertEquals(array(
			'id'=>6,
			'title'=>'test post 1',
			'create_time'=>$post->create_time,
			'author_id'=>1,
			'content'=>'test post content 1'),$post->attributes);
		$this->assertFalse($post->isNewRecord);
		$this->assertEquals($post->attributes,Post::model()->findByPk($post->id)->attributes);
	}

	public function testUpdate()
	{
		// test save
		$post=Post::model()->findByPk(1);
		$this->assertFalse($post->isNewRecord);
		$this->assertEquals('post 1',$post->title);
		$post->title='test post 1';
		$this->assertTrue($post->save());
		$this->assertFalse($post->isNewRecord);
		$this->assertEquals('test post 1',$post->title);
		$this->assertEquals('test post 1',Post::model()->findByPk(1)->title);

		// test updateByPk
		$this->assertEquals(2,Post::model()->updateByPk(array(4,5),array('title'=>'test post')));
		$this->assertEquals('post 2',Post::model()->findByPk(2)->title);
		$this->assertEquals('test post',Post::model()->findByPk(4)->title);
		$this->assertEquals('test post',Post::model()->findByPk(5)->title);

		// test updateAll
		$this->assertEquals(1,Post::model()->updateAll(array('title'=>'test post'),'id=1'));
		$this->assertEquals('test post',Post::model()->findByPk(1)->title);

		// test updateCounters
		$this->assertEquals(2,Post::model()->findByPk(2)->author_id);
		$this->assertEquals(2,Post::model()->findByPk(3)->author_id);
		$this->assertEquals(2,Post::model()->findByPk(4)->author_id);
		$this->assertEquals(3,Post::model()->updateCounters(array('author_id'=>-1),'id>2'));
		$this->assertEquals(2,Post::model()->findByPk(2)->author_id);
		$this->assertEquals(1,Post::model()->findByPk(3)->author_id);
	}

	public function testSaveCounters()
	{
		$post=Post::model()->findByPk(2);
		$this->assertEquals(2, $post->author_id);
		$result=$post->saveCounters(array('author_id'=>-1));
		$this->assertTrue($result);
		$this->assertEquals(1, $post->author_id);
		$this->assertEquals(1, Post::model()->findByPk(2)->author_id);
		$this->assertEquals(2, Post::model()->findByPk(3)->author_id);
	}

	public function testDelete()
	{
		$post=Post::model()->findByPk(1);
		$this->assertTrue($post->delete());
		$this->assertNull(Post::model()->findByPk(1));

		$this->assertTrue(Post::model()->findByPk(2) instanceof Post);
		$this->assertTrue(Post::model()->findByPk(3) instanceof Post);
		$this->assertEquals(2,Post::model()->deleteByPk(array(2,3)));
		$this->assertNull(Post::model()->findByPk(2));
		$this->assertNull(Post::model()->findByPk(3));

		$this->assertTrue(Post::model()->findByPk(5) instanceof Post);
		$this->assertEquals(1,Post::model()->deleteAll('id=5'));
		$this->assertNull(Post::model()->findByPk(5));
	}

	public function testRefresh()
	{
		$post=Post::model()->findByPk(1);
		$post2=Post::model()->findByPk(1);
		$post2->title='new post';
		$post2->save();
		$this->assertEquals('post 1',$post->title);
		$this->assertTrue($post->refresh());
		$this->assertEquals('new post',$post->title);
	}

	public function testEquals()
	{
		$post=Post::model()->findByPk(1);
		$post2=Post::model()->findByPk(1);
		$post3=Post::model()->findByPk(3);
		$this->assertEquals(1,$post->primaryKey);
		$this->assertTrue($post->equals($post2));
		$this->assertTrue($post2->equals($post));
		$this->assertFalse($post->equals($post3));
		$this->assertFalse($post3->equals($post));
	}

	public function testValidation()
	{
		$user=new User;
		$user->password='passtest';
		$this->assertFalse($user->hasErrors());
		$this->assertEquals(array(),$user->errors);
		$this->assertEquals(array(),$user->getErrors('username'));
		$this->assertFalse($user->save());
		$this->assertNull($user->id);
		$this->assertTrue($user->isNewRecord);
		$this->assertTrue($user->hasErrors());
		$this->assertTrue($user->hasErrors('username'));
		$this->assertTrue($user->hasErrors('email'));
		$this->assertFalse($user->hasErrors('password'));
		$this->assertEquals(1,count($user->getErrors('username')));
		$this->assertEquals(1,count($user->getErrors('email')));
		$this->assertEquals(2,count($user->errors));

		$user->clearErrors();
		$this->assertFalse($user->hasErrors());
		$this->assertEquals(array(),$user->errors);
	}

	public function testCompositeKey()
	{
		$order=new Order;
		$this->assertEquals(array('key1','key2'),$order->tableSchema->primaryKey);
		$order=Order::model()->findByPk(array('key1'=>2,'key2'=>1));
		$this->assertEquals('order 21',$order->name);
		$orders=Order::model()->findAllByPk(array(array('key1'=>2,'key2'=>1),array('key1'=>1,'key2'=>3)));
		$this->assertEquals('order 13',$orders[0]->name);
		$this->assertEquals('order 21',$orders[1]->name);
	}

	public function testDefault()
	{
		$type=new ComplexType;
		$this->assertEquals(1,$type->int_col2);
		$this->assertEquals('something',$type->char_col2);
		$this->assertEquals(1.23,$type->float_col2);
		$this->assertEquals(33.22,$type->numeric_col);
		$this->assertEquals(123,$type->time);
		$this->assertEquals(null,$type->bool_col);
		$this->assertEquals(true,$type->bool_col2);
	}

	public function testPublicAttribute()
	{
		$post=new PostExt;
		$this->assertEquals(array('id'=>null,'title'=>'default title','create_time'=>null,'author_id'=>null,'content'=>null),$post->attributes);
		$post=Post::model()->findByPk(1);
		$this->assertEquals(array(
			'id'=>1,
			'title'=>'post 1',
			'create_time'=>100000,
			'author_id'=>1,
			'content'=>'content 1'),$post->attributes);

		$post=new PostExt;
		$post->title='test post';
		$post->create_time=1000000;
		$post->author_id=1;
		$post->content='test';
		$post->save();
		$this->assertEquals(array(
			'id'=>6,
			'title'=>'test post',
			'create_time'=>1000000,
			'author_id'=>1,
			'content'=>'test'),$post->attributes);
	}

	public function testLazyRelation()
	{
		// test belongsTo
		$post=Post::model()->findByPk(2);
		$this->assertTrue($post->author instanceof User);
		$this->assertEquals(array(
			'id'=>2,
			'username'=>'user2',
			'password'=>'pass2',
			'email'=>'email2'),$post->author->attributes);

		// test hasOne
		$post=Post::model()->findByPk(2);
		$this->assertTrue($post->firstComment instanceof Comment);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->firstComment->attributes);
		$post=Post::model()->findByPk(4);
		$this->assertNull($post->firstComment);

		// test hasMany
		$post=Post::model()->findByPk(2);
		$this->assertEquals(2,count($post->comments));
		$this->assertEquals(array(
			'id'=>5,
			'content'=>'comment 5',
			'post_id'=>2,
			'author_id'=>2),$post->comments[0]->attributes);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->comments[1]->attributes);
		$post=Post::model()->findByPk(4);
		$this->assertEquals(array(),$post->comments);

		// test manyMany
		$post=Post::model()->findByPk(2);
		$this->assertEquals(2,count($post->categories));
		$this->assertEquals(array(
			'id'=>4,
			'name'=>'cat 4',
			'parent_id'=>1),$post->categories[0]->attributes);
		$this->assertEquals(array(
			'id'=>1,
			'name'=>'cat 1',
			'parent_id'=>null),$post->categories[1]->attributes);
		$post=Post::model()->findByPk(4);
		$this->assertEquals(array(),$post->categories);

		// test self join
		$category=Category::model()->findByPk(5);
		$this->assertEquals(array(),$category->posts);
		$this->assertEquals(2,count($category->children));
		$this->assertEquals(array(
			'id'=>6,
			'name'=>'cat 6',
			'parent_id'=>5),$category->children[0]->attributes);
		$this->assertEquals(array(
			'id'=>7,
			'name'=>'cat 7',
			'parent_id'=>5),$category->children[1]->attributes);
		$this->assertTrue($category->parent instanceof Category);
		$this->assertEquals(array(
			'id'=>1,
			'name'=>'cat 1',
			'parent_id'=>null),$category->parent->attributes);

		$category=Category::model()->findByPk(2);
		$this->assertEquals(1,count($category->posts));
		$this->assertEquals(array(),$category->children);
		$this->assertNull($category->parent);

		// test composite key
		$order=Order::model()->findByPk(array('key1'=>1,'key2'=>2));
		$this->assertEquals(2,count($order->items));
		$order=Order::model()->findByPk(array('key1'=>2,'key2'=>1));
		$this->assertEquals(0,count($order->items));
		$item=Item::model()->findByPk(4);
		$this->assertTrue($item->order instanceof Order);
		$this->assertEquals(array(
			'key1'=>2,
			'key2'=>2,
			'name'=>'order 22'),$item->order->attributes);
	}

	public function testEagerRelation2()
	{
		$post=Post::model()->with('author','firstComment','comments','categories')->findByPk(2);
	}

	private function checkEagerLoadedModel($post)
	{
		$this->assertEquals(array(
			'id'=>2,
			'username'=>'user2',
			'password'=>'pass2',
			'email'=>'email2'),$post->author->attributes);
		$this->assertTrue($post->firstComment instanceof Comment);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->firstComment->attributes);
		$this->assertEquals(2,count($post->comments));
		$this->assertEquals(array(
			'id'=>5,
			'content'=>'comment 5',
			'post_id'=>2,
			'author_id'=>2),$post->comments[0]->attributes);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->comments[1]->attributes);
		$this->assertEquals(2,count($post->categories));
		$this->assertEquals(array(
			'id'=>4,
			'name'=>'cat 4',
			'parent_id'=>1),$post->categories[0]->attributes);
		$this->assertEquals(array(
			'id'=>1,
			'name'=>'cat 1',
			'parent_id'=>null),$post->categories[1]->attributes);
	}

	public function testEagerRelation()
	{
		$post=Post::model()->with('author','firstComment','comments','categories')->findByPk(2);
		$this->checkEagerLoadedModel($post);
		$post=Post::model()->findByPk(2,array(
			'with'=>array('author','firstComment','comments','categories'),
		));
		$this->checkEagerLoadedModel($post);

		$post=Post::model()->with('author','firstComment','comments','categories')->findByPk(4);
		$this->assertEquals(array(
			'id'=>2,
			'username'=>'user2',
			'password'=>'pass2',
			'email'=>'email2'),$post->author->attributes);
		$this->assertNull($post->firstComment);
		$this->assertEquals(array(),$post->comments);
		$this->assertEquals(array(),$post->categories);
	}

	public function testLazyRecursiveRelation()
	{
		$post=PostExt::model()->findByPk(2);
		$this->assertEquals(2,count($post->comments));
		$this->assertTrue($post->comments[0]->post instanceof Post);
		$this->assertTrue($post->comments[1]->post instanceof Post);
		$this->assertTrue($post->comments[0]->author instanceof User);
		$this->assertTrue($post->comments[1]->author instanceof User);
		$this->assertEquals(3,count($post->comments[0]->author->posts));
		$this->assertEquals(3,count($post->comments[1]->author->posts));
		$this->assertTrue($post->comments[0]->author->posts[1]->author instanceof User);

		// test self join
		$category=Category::model()->findByPk(1);
		$this->assertEquals(2,count($category->nodes));
		$this->assertTrue($category->nodes[0]->parent instanceof Category);
		$this->assertTrue($category->nodes[1]->parent instanceof Category);
		$this->assertEquals(0,count($category->nodes[0]->children));
		$this->assertEquals(2,count($category->nodes[1]->children));
	}

	public function testEagerRecursiveRelation()
	{
		//$post=Post::model()->with(array('comments'=>'author','categories'))->findByPk(2);
		$post=Post::model()->with('comments.author','categories')->findByPk(2);
		$this->assertEquals(2,count($post->comments));
		$this->assertEquals(2,count($post->categories));

		$posts=PostExt::model()->with('comments')->findAll();
		$this->assertEquals(5,count($posts));
	}

	public function testRelationWithCondition()
	{
		$posts=Post::model()->with('comments')->findAllByPk(array(2,3,4),array('order'=>'t.id'));
		$this->assertEquals(3,count($posts));
		$this->assertEquals(2,count($posts[0]->comments));
		$this->assertEquals(4,count($posts[1]->comments));
		$this->assertEquals(0,count($posts[2]->comments));

		$post=Post::model()->with('comments')->findByAttributes(array('id'=>2));
		$this->assertTrue($post instanceof Post);
		$this->assertEquals(2,count($post->comments));
		$posts=Post::model()->with('comments')->findAllByAttributes(array('id'=>2));
		$this->assertEquals(1,count($posts));

		$post=Post::model()->with('comments')->findBySql('select * from posts where id=:id',array(':id'=>2));
		$this->assertTrue($post instanceof Post);
		$posts=Post::model()->with('comments')->findAllBySql('select * from posts where id=:id1 OR id=:id2',array(':id1'=>2,':id2'=>3));
		$this->assertEquals(2,count($posts));

		$post=Post::model()->with('comments','author')->find('t.id=:id',array(':id'=>2));
		$this->assertTrue($post instanceof Post);

		$posts=Post::model()->with('comments','author')->findAll(array(
			'select'=>'title',
			'condition'=>'t.id=:id',
			'limit'=>1,
			'offset'=>0,
			'order'=>'t.title',
			'group'=>'t.id',
			'params'=>array(':id'=>2)));
		$this->assertTrue($posts[0] instanceof Post);

		$posts=Post::model()->with('comments','author')->findAll(array(
			'select'=>'title',
			'condition'=>'t.id=:id',
			'limit'=>1,
			'offset'=>2,
			'order'=>'t.title',
			'params'=>array(':id'=>2)));
		$this->assertTrue($posts===array());
	}

	public function testRelationWithColumnAlias()
	{
		$users=User::model()->with('posts')->findAll(array(
			'select'=>'id, username AS username2',
			'order'=>'username2',
		));

		$this->assertEquals(4,count($users));
		$this->assertEquals($users[1]->username,null);
		$this->assertEquals($users[1]->username2,'user2');
	}

	public function testRelationalWithoutFK()
	{
		$users=UserNoFk::model()->with('posts')->findAll();
		$this->assertEquals(4,count($users));
		$this->assertEquals(3,count($users[1]->posts));

		$posts=PostNoFk::model()->with('author')->findAll();
		$this->assertEquals(5,count($posts));
		$this->assertTrue($posts[2]->author instanceof UserNoFk);
	}

	public function testRelationWithNewRecord()
	{
		$user=new User;
		$posts=$user->posts;
		$this->assertTrue(is_array($posts) && empty($posts));

		$post=new Post;
		$author=$post->author;
		$this->assertNull($author);
	}

	public function testRelationWithDynamicCondition()
	{
		$user=User::model()->with('posts')->findByPk(2);
		$this->assertEquals($user->posts[0]->id,2);
		$this->assertEquals($user->posts[1]->id,3);
		$this->assertEquals($user->posts[2]->id,4);
		$user=User::model()->with(array('posts'=>array('order'=>'posts.id DESC')))->findByPk(2);
		$this->assertEquals($user->posts[0]->id,4);
		$this->assertEquals($user->posts[1]->id,3);
		$this->assertEquals($user->posts[2]->id,2);
	}

	public function testEagerTogetherRelation()
	{
		$post=Post::model()->with('author','firstComment','comments','categories')->findByPk(2);
		$comments=$post->comments;
		$this->assertEquals(array(
			'id'=>2,
			'username'=>'user2',
			'password'=>'pass2',
			'email'=>'email2'),$post->author->attributes);
		$this->assertTrue($post->firstComment instanceof Comment);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->firstComment->attributes);
		$this->assertEquals(2,count($post->comments));
		$this->assertEquals(array(
			'id'=>5,
			'content'=>'comment 5',
			'post_id'=>2,
			'author_id'=>2),$post->comments[0]->attributes);
		$this->assertEquals(array(
			'id'=>4,
			'content'=>'comment 4',
			'post_id'=>2,
			'author_id'=>2),$post->comments[1]->attributes);
		$this->assertEquals(2,count($post->categories));
		$this->assertEquals(array(
			'id'=>4,
			'name'=>'cat 4',
			'parent_id'=>1),$post->categories[0]->attributes);
		$this->assertEquals(array(
			'id'=>1,
			'name'=>'cat 1',
			'parent_id'=>null),$post->categories[1]->attributes);

		$post=Post::model()->with('author','firstComment','comments','categories')->findByPk(4);
		$this->assertEquals(array(
			'id'=>2,
			'username'=>'user2',
			'password'=>'pass2',
			'email'=>'email2'),$post->author->attributes);
		$this->assertNull($post->firstComment);
		$this->assertEquals(array(),$post->comments);
		$this->assertEquals(array(),$post->categories);
	}

	public function testRelationalCount()
	{
		$count=Post::model()->with('author','firstComment','comments','categories')->count();
		$this->assertEquals(5,$count);

		$count=Post::model()->count(array('with'=>array('author','firstComment','comments','categories')));
		$this->assertEquals(5,$count);

		$count=Post::model()->with('author','firstComment','comments','categories')->count('t.id=4');
		$this->assertEquals(1,$count);

		$count=Post::model()->with('author','firstComment','comments','categories')->count('t.id=14');
		$this->assertEquals(0,$count);
	}

	public function testRelationalStat()
	{
		$users=User::model()->with('postCount')->findAll();
		$this->assertEquals(4,count($users));
		$this->assertEquals(1,$users[0]->postCount);
		$this->assertEquals(3,$users[1]->postCount);
		$this->assertEquals(1,$users[2]->postCount);

		$users=User::model()->findAll();
		$this->assertEquals(4,count($users));
		$this->assertEquals(1,$users[0]->postCount);
		$this->assertEquals(3,$users[1]->postCount);
		$this->assertEquals(1,$users[2]->postCount);

		$orders=Order::model()->with('itemCount')->findAll();
		$this->assertEquals(4,count($orders));
		$this->assertEquals(2,$orders[0]->itemCount);
		$this->assertEquals(1,$orders[1]->itemCount);
		$this->assertEquals(0,$orders[2]->itemCount);
		$this->assertEquals(2,$orders[3]->itemCount);

		$orders=Order::model()->findAll();
		$this->assertEquals(4,count($orders));
		$this->assertEquals(2,$orders[0]->itemCount);
		$this->assertEquals(1,$orders[1]->itemCount);
		$this->assertEquals(0,$orders[2]->itemCount);
		$this->assertEquals(2,$orders[3]->itemCount);

		$categories=Category::model()->with('postCount')->findAll();
		$this->assertEquals(7,count($categories));
		$this->assertEquals(3,$categories[0]->postCount);
		$this->assertEquals(1,$categories[1]->postCount);
		$this->assertEquals(1,$categories[2]->postCount);
		$this->assertEquals(1,$categories[3]->postCount);
		$this->assertEquals(0,$categories[4]->postCount);
		$this->assertEquals(0,$categories[5]->postCount);
		$this->assertEquals(0,$categories[6]->postCount);

		$categories=Category::model()->findAll();
		$this->assertEquals(7,count($categories));
		$this->assertEquals(3,$categories[0]->postCount);
		$this->assertEquals(1,$categories[1]->postCount);
		$this->assertEquals(1,$categories[2]->postCount);
		$this->assertEquals(1,$categories[3]->postCount);
		$this->assertEquals(0,$categories[4]->postCount);
		$this->assertEquals(0,$categories[5]->postCount);
		$this->assertEquals(0,$categories[6]->postCount);

		$users=User::model()->with('postCount','posts.commentCount')->findAll();
		$this->assertEquals(4,count($users));
	}

	public function testLazyLoadingWithConditions()
	{
		$user=User::model()->findByPk(2);
		$posts=$user->posts;
		$this->assertEquals(3,count($posts));
		$posts=$user->posts(array('condition'=>'posts.id>=3', 'alias'=>'posts'));
		$this->assertEquals(2,count($posts));
	}

	public function testDuplicateLazyLoadingBug()
	{
		$user=User::model()->with(array(
			'posts'=>array('on'=>'posts.id=-1')
		))->findByPk(1);
		// with the bug, an eager loading for 'posts' would be trigger in the following
		// and result with non-empty posts
		$this->assertTrue($user->posts===array());
	}

	public function testTogether()
	{
		// test without together
		$users=UserNoTogether::model()->with('posts.comments')->findAll();
		$postCount=0;
		$commentCount=0;
		foreach($users as $user)
		{
			$postCount+=count($user->posts);
			foreach($posts=$user->posts as $post)
				$commentCount+=count($post->comments);
		}
		$this->assertEquals(4,count($users));
		$this->assertEquals(5,$postCount);
		$this->assertEquals(10,$commentCount);

		// test with together
		$users=UserNoTogether::model()->with('posts.comments')->together()->findAll();
		$postCount=0;
		$commentCount=0;
		foreach($users as $user)
		{
			$postCount+=count($user->posts);
			foreach($posts=$user->posts as $post)
				$commentCount+=count($post->comments);
		}
		$this->assertEquals(3,count($users));
		$this->assertEquals(4,$postCount);
		$this->assertEquals(10,$commentCount);
	}

	public function testTogetherWithOption()
	{
		// test with together off option
		$users=User::model()->with(array(
			'posts'=>array(
				'with'=>array(
					'comments'=>array(
						'joinType'=>'INNER JOIN',
						'together'=>false,
					),
				),
				'joinType'=>'INNER JOIN',
				'together'=>false,
			),
		))->findAll();

		$postCount=0;
		$commentCount=0;
		foreach($users as $user)
		{
			$postCount+=count($user->posts);
			foreach($posts=$user->posts as $post)
				$commentCount+=count($post->comments);
		}
		$this->assertEquals(4,count($users));
		$this->assertEquals(5,$postCount);
		$this->assertEquals(10,$commentCount);

		// test with together on option
		$users=User::model()->with(array(
			'posts'=>array(
				'with'=>array(
					'comments'=>array(
						'joinType'=>'INNER JOIN',
						'together'=>true,
					),
				),
				'joinType'=>'INNER JOIN',
				'together'=>true,
			),
		))->findAll();

		$postCount=0;
		$commentCount=0;
		foreach($users as $user)
		{
			$postCount+=count($user->posts);
			foreach($posts=$user->posts as $post)
				$commentCount+=count($post->comments);
		}
		$this->assertEquals(3,count($users));
		$this->assertEquals(4,$postCount);
		$this->assertEquals(10,$commentCount);
	}

	public function testCountByAttributes()
	{
		$n=Post::model()->countByAttributes(array('author_id'=>2));
		$this->assertEquals(3,$n);

	}

	public function testScopes()
	{
		$models1=Post::model()->post23()->findAll();
		$models2=Post::model()->findAll(array('scopes'=>'post23'));

		foreach(array($models1,$models2) as $models)
		{
			$this->assertEquals(2,count($models));
			$this->assertEquals(2,$models[0]->id);
			$this->assertEquals(3,$models[1]->id);
		}

		$model1=Post::model()->post23()->find();
		$model2=Post::model()->find(array('scopes'=>'post23'));

		foreach(array($model1,$model2) as $model)
			$this->assertEquals(2,$model->id);

		$models1=Post::model()->post23()->post3()->findAll();
		$models2=Post::model()->findAll(array('scopes'=>array('post23','post3')));

		foreach(array($models1,$models2) as $models)
		{
			$this->assertEquals(1,count($models));
			$this->assertEquals(3,$models[0]->id);
		}

		$models1=Post::model()->post23()->findAll('id=3');
		$models2=Post::model()->post23()->findAll(array('condition'=>'id=3','scopes'=>'post23'));

		foreach(array($models1,$models2) as $models)
		{
			$this->assertEquals(1,count($models));
			$this->assertEquals(3,$models[0]->id);
		}

		$models1=Post::model()->recent()->with('author')->findAll();
		$models2=Post::model()->with('author')->findAll(array('scopes'=>'recent'));
		$models3=Post::model()->with('author')->findAll(array('scopes'=>array('recent')));
		$models4=Post::model()->with('author')->findAll(array('scopes'=>array(array('recent'=>array()))));

		foreach(array($models1,$models2,$models3,$models4) as $models)
		{
			$this->assertEquals(5,count($models));
			$this->assertEquals(5,$models[0]->id);
			$this->assertEquals(4,$models[1]->id);
		}

		$models1=Post::model()->recent(3)->findAll();
		$models2=Post::model()->findAll(array('scopes'=>array('recent'=>3)));
		$models3=Post::model()->findAll(array('scopes'=>array(array('recent'=>3))));

		foreach(array($models1,$models2,$models3) as $models)
		{
			$this->assertEquals(3,count($models));
			$this->assertEquals(5,$models[0]->id);
			$this->assertEquals(4,$models[1]->id);
		}

		//default scope
		$models=PostSpecial::model()->findAll();
		$this->assertEquals(2,count($models));
		$this->assertEquals(2,$models[0]->id);
		$this->assertEquals(3,$models[1]->id);

		//default scope + scope
		$models1=PostSpecial::model()->desc()->findAll();
		$models2=PostSpecial::model()->findAll(array('scopes'=>'desc'));

		foreach(array($models1,$models2) as $models)
		{
			$this->assertEquals(2,count($models));
			$this->assertEquals(3,$models[0]->id);
			$this->assertEquals(2,$models[1]->id);
		}

		//behavior scope
		$models=Post::model()->findAll(array('scopes'=>'behaviorPost23'));
		$this->assertEquals(2,count($models));
		$this->assertEquals(2,$models[0]->id);
		$this->assertEquals(3,$models[1]->id);

		//behavior parametrized scope
		$models=Post::model()->findAll(array('scopes'=>array('behaviorRecent'=>3)));
		$this->assertEquals(3,count($models));
		$this->assertEquals(5,$models[0]->id);
		$this->assertEquals(4,$models[1]->id);
	}

	public function testScopeWithRelations()
	{
		$user1=User::model()->with('posts:post23')->findByPk(2);
		$user2=User::model()->with(array('posts'=>array('scopes'=>'post23')))->findByPk(2);
		$user3=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>'post23'))));
		//ensure alais overloading work correctly
		$user4=User::model()->with(array('posts:post23A'=>array('alias'=>'alias')))->findByPk(2);
		$user5=User::model()->with(array('posts'=>array('scopes'=>'post23A','alias'=>'alias')))->findByPk(2);
		$user6=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>'post23A','alias'=>'alias'))));

		foreach(array($user1,$user2,$user3,$user4,$user5,$user6) as $user)
		{
			$this->assertEquals(2,count($user->posts));
			$this->assertEquals(2,$user->posts[0]->id);
			$this->assertEquals(3,$user->posts[1]->id);
		}

		$user1=User::model()->with(array('posts'=>array('scopes'=>array('p'=>4))))->findByPk(2);
		$user2=User::model()->with(array('posts'=>array('scopes'=>array('p'=>array(4)))))->findByPk(2);
		$user3=User::model()->with(array('posts'=>array('scopes'=>array(array('p'=>4)))))->findByPk(2);
		$user4=User::model()->with(array('posts'=>array('scopes'=>array(array('p'=>array(4))))))->findByPk(2);
		$user5=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array('p'=>4)))));
		$user6=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array('p'=>array(4))))));
		$user7=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array(array('p'=>4))))));
		$user8=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array(array('p'=>array(4)))))));

		foreach(array($user1,$user2,$user3,$user4,$user5,$user6,$user7,$user8) as $user)
		{
			$this->assertEquals(1,count($user->posts));
			$this->assertEquals(4,$user->posts[0]->id);
		}

		$user=UserSpecial::model()->findByPk(2);
		$posts=$user->posts;
		$this->assertEquals(2,count($posts));
		$this->assertEquals(2,$posts[0]->id);
		$this->assertEquals(3,$posts[1]->id);

		$user=UserSpecial::model()->findByPk(2);
		$posts=$user->posts(array('params'=>array(':id1'=>4),'order'=>'posts.id DESC'));
		$this->assertEquals(2,count($posts));
		$this->assertEquals(4,$posts[0]->id);
		$this->assertEquals(3,$posts[1]->id);

		$user=User::model()->with('posts:post23')->findByPk(2);
		$posts=$user->posts(array('scopes'=>'post23'));
		$this->assertEquals(2,count($posts));
		$this->assertEquals(2,$posts[0]->id);
		$this->assertEquals(3,$posts[1]->id);

		//related model behavior scope
		$user1=User::model()->with('posts:behaviorPost23')->findByPk(2);
		$user2=User::model()->with(array('posts'=>array('scopes'=>'behaviorPost23')))->findByPk(2);
		$user3=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>'behaviorPost23'))));

		foreach(array($user1,$user2,$user3) as $user)
		{
			$this->assertEquals(2,count($user->posts));
			$this->assertEquals(2,$user->posts[0]->id);
			$this->assertEquals(3,$user->posts[1]->id);
		}

		//related model with behavior parametrized scope
		$user1=User::model()->with(array('posts'=>array('scopes'=>array('behaviorP'=>4))))->findByPk(2);
		$user2=User::model()->with(array('posts'=>array('scopes'=>array('behaviorP'=>array(4)))))->findByPk(2);
		$user3=User::model()->with(array('posts'=>array('scopes'=>array(array('behaviorP'=>4)))))->findByPk(2);
		$user4=User::model()->with(array('posts'=>array('scopes'=>array(array('behaviorP'=>array(4))))))->findByPk(2);
		$user5=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array('behaviorP'=>4)))));
		$user6=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array('behaviorP'=>array(4))))));
		$user7=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array(array('behaviorP'=>4))))));
		$user8=User::model()->findByPk(2,array('with'=>array('posts'=>array('scopes'=>array(array('behaviorP'=>array(4)))))));

		foreach(array($user1,$user2,$user3,$user4,$user5,$user6,$user7,$user8) as $user)
		{
			$this->assertEquals(1,count($user->posts));
			$this->assertEquals(4,$user->posts[0]->id);
		}

		//related model with 'scopes' as relation option
		$user=User::model()->with('postsOrderDescFormat1')->findByPk(2);
		$this->assertEquals(3,count($user->postsOrderDescFormat1));
		$this->assertEquals(array(4,3,2),array(
			$user->postsOrderDescFormat1[0]->id,
			$user->postsOrderDescFormat1[1]->id,
			$user->postsOrderDescFormat1[2]->id,
		));
		$user=User::model()->with('postsOrderDescFormat2')->findByPk(2);
		$this->assertEquals(3,count($user->postsOrderDescFormat2));
		$this->assertEquals(array(4,3,2),array(
			$user->postsOrderDescFormat2[0]->id,
			$user->postsOrderDescFormat2[1]->id,
			$user->postsOrderDescFormat2[2]->id,
		));
	}

	public function testResetScope()
	{
		// resetting named scope
		$posts=Post::model()->post23()->resetScope()->findAll();
		$this->assertEquals(5,count($posts));

		// resetting default scope
		$posts=PostSpecial::model()->resetScope()->findAll();
		$this->assertEquals(5,count($posts));
	}

	public function testJoinWithoutSelect()
	{
        // 1:1 test
		$groups=Group::model()->findAll(array(
			'with'=>array(
				'description'=>array(
					'select'=>false,
					'joinType'=>'INNER JOIN',
				),
			),
		));

		$result=array();
		foreach($groups as $group)
		{
			// there should be nothing in relation
			$this->assertFalse($group->hasRelated('description'));
			$result[]=array($group->id,$group->name);
		}

		$this->assertEquals(array(
			array(1,'group1'),
			array(2,'group2'),
			array(3,'group3'),
			array(4,'group4'),
		),$result);

		// 1:M test
		$users=User::model()->findAll(array(
			'with'=>array(
				'roles'=>array(
					'select'=>false,
					'joinType'=>'INNER JOIN',
				),
			),
		));

		$result=array();
		foreach($users as $user)
		{
			// there should be nothing in relation
			$this->assertFalse($user->hasRelated('roles'));
			$result[]=array($user->id,$user->username,$user->email);
		}

		$this->assertEquals(array(
			array(1,'user1','email1'),
			array(2,'user2','email2'),
		),$result);
	}

	public function testHasManyThroughEager()
	{
		// just bridge
		$user=User::model()->with('groups')->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name);

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		// just bridge, base limited
		$users=User::model()->with('groups')->findAll(array('limit'=>1));

		$result=array();
		foreach($users as $user)
		{
			foreach($user->groups as $group)
				$result[]=array($user->username,$group->name);
		}

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		// 'through' should not clear existing relations defined via short syntax
		$user=User::model()->with('groups.description')->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name,$group->description->name);

		$this->assertEquals(array(
			array('user1','group1','room1'),
			array('user1','group2','room2'),
		),$result);

		// 'through' should not clear existing with
		$user=User::model()->with(array('groups'=>array('with'=>'description')))->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name,$group->description->name);

		$this->assertEquals(array(
			array('user1','group1','room1'),
			array('user1','group2','room2'),
		),$result);

		// bridge fields handling
		$user=User::model()->with('roles','groups')->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name);

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		$result=array();
		foreach($user->roles as $role)
			$result[]=array($user->username,$role->name);

		$this->assertEquals(array(
			array('user1','dev'),
			array('user1','user'),
		),$result);

		// bridge fields handling, another relations order
		$user=User::model()->with('groups','roles')->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name);

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		$result=array();
		foreach($user->roles as $role)
			$result[]=array($user->username,$role->name);

		$this->assertEquals(array(
			array('user1','dev'),
			array('user1','user'),
		),$result);

		// bridge fields handling, base limited
		$users=User::model()->with('roles','groups')->findAll(array('limit'=>1));

		$result=array();
		foreach($users as $user)
		{
			foreach($user->groups as $group)
				$result[]=array($user->username,$group->name);
		}

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		$result=array();
		foreach($users as $user)
		{
			foreach($user->roles as $role)
				$result[]=array($user->username,$role->name);
		}

		$this->assertEquals(array(
			array('user1','dev'),
			array('user1','user'),
		),$result);

		// nested through
		$group=Group::model()->with('comments')->findByPk(1);

		$result=array();
		foreach($group->comments as $comment)
			$result[]=array($group->name,$comment->content);

		$this->assertEquals(array(
			array('group1','comment 1'),
			array('group1','comment 2'),
			array('group1','comment 3'),
			array('group1','comment 4'),
			array('group1','comment 5'),
			array('group1','comment 6'),
			array('group1','comment 7'),
			array('group1','comment 8'),
			array('group1','comment 9'),
		),$result);

		// nested through, base limited
		$groups=Group::model()->with('comments')->findAll(array('limit'=>1));

		$result=array();
		foreach($groups as $group)
		{
			foreach($group->comments as $comment)
				$result[]=array($group->name,$comment->content);
		}

		$this->assertEquals(array(
			array('group1','comment 1'),
			array('group1','comment 2'),
			array('group1','comment 3'),
			array('group1','comment 4'),
			array('group1','comment 5'),
			array('group1','comment 6'),
			array('group1','comment 7'),
			array('group1','comment 8'),
			array('group1','comment 9'),
		),$result);

		// self through
		$teachers=User::model()->with('students')->findAll();

		$result=array();
		foreach($teachers as $teacher)
		{
			foreach($teacher->students as $student)
				$result[]=array($teacher->username,$student->username);
		}

		$this->assertEquals(array(
			array('user1','user3'),
			array('user2','user4'),
		),$result);

		// self through, bridge fields handling for right part
		$teachers=User::model()->with('mentorships','students')->findAll();

		$result=array();
		foreach($teachers as $teacher)
		{
			foreach($teacher->students as $student)
				$result[$student->primaryKey]=array('teacher'=>$teacher->username,'student'=>$student->username);

			foreach($teacher->mentorships as $mentorship)
				$result[$mentorship->student_id]['progress']=$mentorship->progress;
		}

		$this->assertEquals(array(
			3=>array('teacher'=>'user1','student'=>'user3','progress'=>'good'),
			4=>array('teacher'=>'user2','student'=>'user4','progress'=>'average'),
		),$result);

		// self through, base limited
		$teachers=User::model()->with('students')->findAll(array('limit'=>1));

		$result=array();
		foreach($teachers as $teacher)
		{
			foreach($teacher->students as $student)
				$result[]=array($teacher->username,$student->username);
		}

		$this->assertEquals(array(
			array('user1','user3'),
		),$result);
	}

	public function testHasManyThroughLazy()
	{
		$user=User::model()->findByPk(1);

		$result=array();
		foreach($user->groups as $group)
			$result[]=array($user->username,$group->name);

		$this->assertEquals(array(
			array('user1','group1'),
			array('user1','group2'),
		),$result);

		$user=User::model()->findByPk(1);

		$result=array();
		foreach($user->groups(array('with'=>'description')) as $group)
			$result[]=array($user->username,$group->name,$group->description->name);

		$this->assertEquals(array(
			array('user1','group1','room1'),
			array('user1','group2','room2'),
		),$result);

		// nested through
		$group=Group::model()->findByPk(1);

		$result=array();
		foreach($group->comments as $comment)
			$result[]=array($group->name,$comment->content);

		$this->assertEquals(array(
			array('group1','comment 1'),
			array('group1','comment 2'),
			array('group1','comment 3'),
			array('group1','comment 4'),
			array('group1','comment 5'),
			array('group1','comment 6'),
			array('group1','comment 7'),
			array('group1','comment 8'),
			array('group1','comment 9'),
		),$result);

		// self through
		$teacher=User::model()->findByPk(1);

		$result=array();
		foreach($teacher->students as $student)
			$result[]=array($teacher->username,$student->username);

		$this->assertEquals(array(
			array('user1','user3'),
		),$result);
	}

	/**
	 * @see issue2274
	 */
	function testMergingWith()
	{
		User::model()->nonEmptyPosts()->findAll(array(
			'with'=>array(
        		'posts'=>array(
            		'joinType'=>'INNER JOIN',
        		),
    		)
		));
	}
}


code:
<?php

class User extends CActiveRecord
{
	public $username2;

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function rules()
	{
		return array(
			array('username, password, email', 'required'),
			array('username, password', 'match', 'pattern'=>'/^[\d\w_]+$/'),
			array('email', 'email'),
			array('username', 'length', 'min'=>3, 'max'=>32),
			array('password', 'length', 'min'=>6, 'max'=>32),
		);
	}

	public function relations()
	{
		return array(
			'roles'=>array(self::HAS_MANY,'Role','user_id'),
			'groups'=>array(self::HAS_MANY,'Group',array('group_id'=>'id'),'through'=>'roles'),
			'mentorships'=>array(self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN'),
			'students'=>array(self::HAS_MANY,'User',array('student_id'=>'id'),'through'=>'mentorships','joinType'=>'INNER JOIN'),
			'posts'=>array(self::HAS_MANY,'Post','author_id'),
			'postsOrderDescFormat1'=>array(self::HAS_MANY,'Post','author_id','scopes'=>'orderDesc'),
			'postsOrderDescFormat2'=>array(self::HAS_MANY,'Post','author_id','scopes'=>array('orderDesc')),
			'postCount'=>array(self::STAT,'Post','author_id'),
		);
	}

	public function tableName()
	{
		return 'users';
	}

	public function scopes()
    {
        return array(
            'nonEmptyPosts'=>array(
                'with'=>array(
                    'posts'=>array(
                        'condition'=>'posts.id is not NULL',
                    ),
                ),
            ),
        );
    }
}

class Mentorship extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'mentorships';
	}
}

class Group extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'roles'=>array(self::HAS_MANY,'Role','group_id'),
			'users'=>array(self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles'),
			'comments'=>array(self::HAS_MANY,'Comment',array('id'=>'author_id'),'through'=>'users'),
			'description'=>array(self::HAS_ONE,'GroupDescription','group_id'),
		);
	}

	public function tableName()
	{
		return 'groups';
	}
}

class GroupDescription extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'groups_descriptions';
	}
}

class Role extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'roles';
	}
}

class Post extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'User','author_id'),
			'firstComment'=>array(self::HAS_ONE,'Comment','post_id','order'=>'firstComment.content'),
			'comments'=>array(self::HAS_MANY,'Comment','post_id','order'=>'comments.content DESC'),
			'commentCount'=>array(self::STAT,'Comment','post_id'),
			'categories'=>array(self::MANY_MANY,'Category','post_category(post_id,category_id)','order'=>'categories.id DESC'),
		);
	}

	public function tableName()
	{
		return 'posts';
	}

	public function behaviors()
	{
		return array(
			'PostScopesBehavior',
		);
	}

	public function scopes()
	{
		return array(
			'post23'=>array('condition'=>'posts.id=2 OR posts.id=3', 'alias'=>'posts', 'order'=>'posts.id'),
			'post23A'=>array('condition'=>"$this->tableAlias.id=2 OR $this->tableAlias.id=3",'order'=>"$this->tableAlias.id"),
			'post3'=>array('condition'=>'id=3'),
			'postX'=>array('condition'=>'id=:id1 OR id=:id2', 'params'=>array(':id1'=>2, ':id2'=>3)),
			'orderDesc'=>array('order'=>'posts.id DESC','alias'=>'posts'),
		);
	}

	public function rules()
	{
		return array(
			array('title', 'required'),
		);
	}

	public function recent($limit=5)
	{
		$this->getDbCriteria()->mergeWith(array(
			'order'=>'create_time DESC',
			'limit'=>$limit,
		));
		return $this;
	}

	//used for relation model scopes test
	public function p($id)
	{
		$this->getDbCriteria()->mergeWith(array(
			'condition'=>'posts.id=?',
			'params'=>array($id),
		));
		return $this;
	}
}

class PostScopesBehavior extends CActiveRecordBehavior
{
	public function behaviorPost23()
	{
		$this->getOwner()->getDbCriteria()->mergeWith(array(
			'condition'=>'posts.id=2 OR posts.id=3',
			'alias'=>'posts',
			'order'=>'posts.id',
		));

		return $this;
	}

	public function behaviorRecent($limit)
	{
		$this->getOwner()->getDbCriteria()->mergeWith(array(
			'order'=>'create_time DESC',
			'limit'=>$limit,
		));

		return $this;
	}

	//used for relation model scopes test
	public function behaviorP($id)
	{
		$this->getOwner()->getDbCriteria()->mergeWith(array(
			'condition'=>'posts.id=?',
			'params'=>array($id),
		));

		return $this;
	}
}

class PostSpecial extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'posts';
	}

	public function defaultScope()
	{
		return array(
			'condition'=>'posts.id=:id1 OR posts.id=:id2',
			'params'=>array(':id1'=>2, ':id2'=>3),
			'alias'=>'posts',
		);
	}

	public function scopes()
	{
		return array(
			'desc'=>array('order'=>'id DESC'),
		);
	}
}

class UserSpecial extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::HAS_MANY,'PostSpecial','author_id'),
		);
	}

	public function tableName()
	{
		return 'users';
	}
}

class PostExt extends CActiveRecord
{
	public $title='default title';
	public $id;

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'posts';
	}

	public function relations()
	{
		return array(
			'comments'=>array(self::HAS_MANY,'Comment','post_id','order'=>'comments.content DESC','with'=>array('post'=>array('alias'=>'post'), 'author')),
		);
	}
}

class Comment extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'post'=>array(self::BELONGS_TO,'Post','post_id'),
			'author'=>array(self::BELONGS_TO,'User','author_id'),
		);
	}

	public function tableName()
	{
		return 'comments';
	}
}

class Category extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'categories';
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::MANY_MANY, 'Post', 'post_category(post_id,category_id)'),
			'parent'=>array(self::BELONGS_TO,'Category','parent_id'),
			'children'=>array(self::HAS_MANY,'Category','parent_id'),
			'nodes'=>array(self::HAS_MANY,'Category','parent_id','with'=>array('parent','children')),
			'postCount'=>array(self::STAT, 'Post', 'post_category(post_id,category_id)'),
		);
	}
}

class Order extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'items'=>array(self::HAS_MANY,'Item','col1, col2'),
			'itemCount'=>array(self::STAT,'Item','col1, col2'),
		);
	}

	public function tableName()
	{
		return 'orders';
	}
}

class Item extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'order'=>array(self::BELONGS_TO,'Order','col1, col2','alias'=>'_order'),
		);
	}

	public function tableName()
	{
		return 'items';
	}
}

class ComplexType extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'types';
	}
}

class Content extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'Content';
	}

	public function relations()
	{
		return array(
			'parent'=>array(self::BELONGS_TO,'Content','parentID'),
			'children'=>array(self::HAS_MANY,'Content','parentID'),
			'owner'=>array(self::BELONGS_TO,'User','ownerID'),
		);
	}
}

class Article extends Content
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'Article';
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'User','authorID'),
			'comments'=>array(self::HAS_MANY,'ArticleComment','parentID'),
		);
	}
}

class ArticleComment extends Content
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'Comment';
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'User','authorID'),
			'article'=>array(self::BELONGS_TO,'Article','parentID'),
		);
	}
}

class UserNoFk extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::HAS_MANY,'PostNoFk','author_id'),
		);
	}

	public function tableName()
	{
		return 'users';
	}
}

class PostNoFk extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'UserNoFk','author_id'),
		);
	}

	public function tableName()
	{
		return 'posts_nofk';
	}
}

class UserNoTogether extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::HAS_MANY,'PostNoTogether','author_id','together'=>false,'joinType'=>'INNER JOIN'),
		);
	}

	public function tableName()
	{
		return 'users';
	}
}

class PostNoTogether extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'comments'=>array(self::HAS_MANY,'Comment','post_id','together'=>false,'joinType'=>'INNER JOIN'),
		);
	}

	public function tableName()
	{
		return 'posts';
	}
}

class UserWithWrappers extends CActiveRecord
{
	private static $_counters=array();

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::HAS_MANY,'PostWithWrappers','author_id'),
			'postCount'=>array(self::STAT,'PostWithWrappers','author_id'),
		);
	}

	public function tableName()
	{
		return 'users';
	}

	protected function beforeFind()
	{
		parent::beforeFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function afterFind()
	{
		parent::afterFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function incrementCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
			self::$_counters[$wrapper]++;
		else
			self::$_counters[$wrapper]=1;
	}

	public static function getCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
		{
			$result=self::$_counters[$wrapper];
		}
		else
			$result=0;

		self::clearCounters();

		return $result;
	}

	public static function clearCounters()
	{
		self::$_counters=array();
	}
}

class PostWithWrappers extends CActiveRecord
{
	private static $_counters=array();

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'UserWithWrappers','author_id'),
			'comments'=>array(self::HAS_MANY,'CommentWithWrappers','post_id','order'=>'comments.content DESC'),
			'commentCount'=>array(self::STAT,'CommentWithWrappers','post_id'),
		);
	}

	public function tableName()
	{
		return 'posts';
	}

	public function rules()
	{
		return array(
			array('title', 'required'),
		);
	}

	protected function beforeFind()
	{
		parent::beforeFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function afterFind()
	{
		parent::afterFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function incrementCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
			self::$_counters[$wrapper]++;
		else
			self::$_counters[$wrapper]=1;
	}

	public static function getCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
		{
			$result=self::$_counters[$wrapper];
		}
		else
			$result=0;

		self::clearCounters();

		return $result;
	}

	public static function clearCounters()
	{
		self::$_counters=array();
	}
}

class CommentWithWrappers extends CActiveRecord
{
	private static $_counters=array();

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'post'=>array(self::BELONGS_TO,'PostWithWrappers','post_id'),
			'author'=>array(self::BELONGS_TO,'UserWithWrappers','author_id'),
		);
	}

	public function tableName()
	{
		return 'comments';
	}

	protected function beforeFind()
	{
		parent::beforeFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function afterFind()
	{
		parent::afterFind();
		$this->incrementCounter(__FUNCTION__);
	}

	protected function incrementCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
			self::$_counters[$wrapper]++;
		else
			self::$_counters[$wrapper]=1;
	}

	public static function getCounter($wrapper)
	{
		if(isset(self::$_counters[$wrapper]))
		{
			$result=self::$_counters[$wrapper];
		}
		else
			$result=0;

		self::clearCounters();

		return $result;
	}

	public static function clearCounters()
	{
		self::$_counters=array();
	}
}

repo_name: DaSourcerer/yii

license: mit


code:
<?php

class User2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function rules()
	{
		return array(
			array('username, password, email', 'required'),
			array('username, password', 'match', 'pattern'=>'/^[\d\w_]+$/'),
			array('email', 'email'),
			array('username', 'length', 'min'=>3, 'max'=>32),
			array('password', 'length', 'min'=>6, 'max'=>32),
		);
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::HAS_MANY,'Post2','author_id'),
			'friends'=>array(self::MANY_MANY,'User2','test.user_friends(id,friend)'),
		);
	}

	public function tableName()
	{
		return 'test.users';
	}
}

class Post2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'author'=>array(self::BELONGS_TO,'User2','author_id'),
			'firstComment'=>array(self::HAS_ONE,'Comment2','post_id','order'=>'"firstComment".content'),
			'comments'=>array(self::HAS_MANY,'Comment2','post_id','order'=>'comments.content DESC'),
			'categories'=>array(self::MANY_MANY,'Category2','test.post_category(post_id,category_id)','order'=>'categories.id DESC'),
		);
	}

	public function rules()
	{
		return array(
			array('title', 'required'),
		);
	}

	public function tableName()
	{
		return 'test.posts';
	}
}

class PostExt2 extends CActiveRecord
{
	public $title='default title';
	public $id;

	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'test.posts';
	}

	public function relations()
	{
		return array(
			'comments'=>array(self::HAS_MANY,'Comment2','post_id','order'=>'comments.content DESC','with'=>array('post'=>array('alias'=>'post'), 'author')),
		);
	}
}

class Comment2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'post'=>array(self::BELONGS_TO,'Post2','post_id'),
			'author'=>array(self::BELONGS_TO,'User2','author_id'),
		);
	}

	public function tableName()
	{
		return 'test.comments';
	}
}

class Category2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'test.categories';
	}

	public function relations()
	{
		return array(
			'posts'=>array(self::MANY_MANY, 'Post2', 'test.post_category(post_id,category_id)'),
			'parent'=>array(self::BELONGS_TO,'Category2','parent_id'),
			'children'=>array(self::HAS_MANY,'Category2','parent_id'),
			'nodes'=>array(self::HAS_MANY,'Category2','parent_id','with'=>array('parent','children')),
		);
	}
}

class Order2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'items'=>array(self::HAS_MANY,'Item2','col1, col2'),
		);
	}

	public function tableName()
	{
		return 'test.orders';
	}
}

class Item2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function relations()
	{
		return array(
			'order'=>array(self::BELONGS_TO,'Order2','col1, col2','alias'=>'_order'),
		);
	}

	public function tableName()
	{
		return 'test.items';
	}
}

class ComplexType2 extends CActiveRecord
{
	public static function model($class=__CLASS__)
	{
		return parent::model($class);
	}

	public function tableName()
	{
		return 'yii_types';
	}
}


code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[categories]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[categories](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](128) NOT NULL,
	[parent_id] [int] NULL,
 CONSTRAINT [PK_categories] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[orders]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[orders](
	[key1] [int] NOT NULL,
	[key2] [int] NOT NULL,
	[name] [varchar](128) NOT NULL,
 CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
	[key1] ASC,
	[key2] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[types]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[types](
	[int_col] [int] NOT NULL,
	[int_col2] [int] NULL CONSTRAINT [DF_types_int_col2]  DEFAULT (1),
	[char_col] [char](100) NOT NULL,
	[char_col2] [varchar](100) NULL CONSTRAINT [DF_types_char_col2]  DEFAULT ('something'),
	[char_col3] [text] NULL,
	[float_col] [real] NOT NULL,
	[float_col2] [float] NULL CONSTRAINT [DF_types_float_col2]  DEFAULT (1.23),
	[blob_col] [image] NULL,
	[numeric_col] [numeric](5, 2) NULL CONSTRAINT [DF_types_numeric_col]  DEFAULT (33.22),
	[time] [datetime] NULL CONSTRAINT [DF_types_time]  DEFAULT ('2002-01-01 00:00:00'),
	[bool_col] [bit] NOT NULL,
	[bool_col2] [bit] NOT NULL CONSTRAINT [DF_types_bool_col2]  DEFAULT (1)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[users]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[username] [varchar](128) NOT NULL,
	[password] [varchar](128) NOT NULL,
	[email] [varchar](128) NOT NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[post_category]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[post_category](
	[category_id] [int] NOT NULL,
	[post_id] [int] NOT NULL,
 CONSTRAINT [PK_post_category] PRIMARY KEY CLUSTERED
(
	[category_id] ASC,
	[post_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[items]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[items](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](128) NULL,
	[col1] [int] NOT NULL,
	[col2] [int] NOT NULL,
 CONSTRAINT [PK_items] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[comments]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[comments](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[content] [text] NOT NULL,
	[post_id] [int] NOT NULL,
	[author_id] [int] NOT NULL,
 CONSTRAINT [PK_comments] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[posts]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[posts](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[title] [varchar](128) NOT NULL,
	[create_time] [datetime] NOT NULL,
	[author_id] [int] NOT NULL,
	[content] [text] NULL,
 CONSTRAINT [PK_posts] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[profiles]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[profiles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[first_name] [varchar](128) NOT NULL,
	[last_name] [varchar](128) NOT NULL,
	[user_id] [int] NOT NULL,
 CONSTRAINT [PK_profiles] PRIMARY KEY CLUSTERED
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_categories_categories]') AND type = 'F')
ALTER TABLE [dbo].[categories]  WITH CHECK ADD  CONSTRAINT [FK_categories_categories] FOREIGN KEY([parent_id])
REFERENCES [dbo].[categories] ([id])
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_post_category_categories]') AND type = 'F')
ALTER TABLE [dbo].[post_category]  WITH CHECK ADD  CONSTRAINT [FK_post_category_categories] FOREIGN KEY([category_id])
REFERENCES [dbo].[categories] ([id])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_post_category_posts]') AND type = 'F')
ALTER TABLE [dbo].[post_category]  WITH NOCHECK ADD  CONSTRAINT [FK_post_category_posts] FOREIGN KEY([post_id])
REFERENCES [dbo].[posts] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[post_category] CHECK CONSTRAINT [FK_post_category_posts]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_items_orders]') AND type = 'F')
ALTER TABLE [dbo].[items]  WITH CHECK ADD  CONSTRAINT [FK_items_orders] FOREIGN KEY([col1], [col2])
REFERENCES [dbo].[orders] ([key1], [key2])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_comments_users]') AND type = 'F')
ALTER TABLE [dbo].[comments]  WITH NOCHECK ADD  CONSTRAINT [FK_comments_users] FOREIGN KEY([author_id])
REFERENCES [dbo].[users] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[comments] CHECK CONSTRAINT [FK_comments_users]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_post_comment]') AND type = 'F')
ALTER TABLE [dbo].[comments]  WITH NOCHECK ADD  CONSTRAINT [FK_post_comment] FOREIGN KEY([post_id])
REFERENCES [dbo].[posts] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[comments] CHECK CONSTRAINT [FK_post_comment]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_posts_users]') AND type = 'F')
ALTER TABLE [dbo].[posts]  WITH NOCHECK ADD  CONSTRAINT [FK_posts_users] FOREIGN KEY([author_id])
REFERENCES [dbo].[users] ([id])
GO
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_users]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_profile_user]') AND type = 'F')
ALTER TABLE [dbo].[profiles]  WITH NOCHECK ADD  CONSTRAINT [FK_profile_user] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[profiles] CHECK CONSTRAINT [FK_profile_user]

INSERT INTO users (username, password, email) VALUES ('user1','pass1','email1')
GO
INSERT INTO users (username, password, email) VALUES ('user2','pass2','email2')
GO
INSERT INTO users (username, password, email) VALUES ('user3','pass3','email3')
GO

INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1)
GO
INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2)
GO

INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 1','2000-01-01',1,'content 1')
GO
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 2','2000-01-02',2,'content 2')
GO
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 3','2000-01-03',2,'content 3')
GO
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 4','2000-01-04',2,'content 4')
GO
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 5','2000-01-05',3,'content 5')
GO

INSERT INTO comments (content, post_id, author_id) VALUES ('comment 1',1, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 2',1, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 3',1, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 4',2, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 5',2, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 6',3, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 7',3, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 8',3, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 9',3, 2)
GO
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 10',5, 3)
GO

INSERT INTO categories (name, parent_id) VALUES ('cat 1',NULL)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 2',NULL)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 3',NULL)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 4',1)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 5',1)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 6',5)
GO
INSERT INTO categories (name, parent_id) VALUES ('cat 7',5)
GO

INSERT INTO post_category (category_id, post_id) VALUES (1,1)
GO
INSERT INTO post_category (category_id, post_id) VALUES (2,1)
GO
INSERT INTO post_category (category_id, post_id) VALUES (3,1)
GO
INSERT INTO post_category (category_id, post_id) VALUES (4,2)
GO
INSERT INTO post_category (category_id, post_id) VALUES (1,2)
GO
INSERT INTO post_category (category_id, post_id) VALUES (1,3)
GO

INSERT INTO orders (key1,key2,name) VALUES (1,2,'order 12')
GO
INSERT INTO orders (key1,key2,name) VALUES (1,3,'order 13')
GO
INSERT INTO orders (key1,key2,name) VALUES (2,1,'order 21')
GO
INSERT INTO orders (key1,key2,name) VALUES (2,2,'order 22')
GO

INSERT INTO items (name,col1,col2) VALUES ('item 1',1,2)
GO
INSERT INTO items (name,col1,col2) VALUES ('item 2',1,2)
GO
INSERT INTO items (name,col1,col2) VALUES ('item 3',1,3)
GO
INSERT INTO items (name,col1,col2) VALUES ('item 4',2,2)
GO
INSERT INTO items (name,col1,col2) VALUES ('item 5',2,2)
GO


code:
/**
 * This is the database schema for testing MySQL support of yii Active Record.
 * To test this feature, you need to create a database named 'yii' on 'localhost'
 * and create an account 'test/test' which owns this test database.
 */

CREATE TABLE users
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(128) NOT NULL,
	password VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL
) TYPE=INNODB;

INSERT INTO users (username, password, email) VALUES ('user1','pass1','email1');
INSERT INTO users (username, password, email) VALUES ('user2','pass2','email2');
INSERT INTO users (username, password, email) VALUES ('user3','pass3','email3');

CREATE TABLE profiles
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	first_name VARCHAR(128) NOT NULL,
	last_name VARCHAR(128) NOT NULL,
	user_id INTEGER NOT NULL,
	CONSTRAINT FK_profile_user FOREIGN KEY (user_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1);
INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2);

CREATE TABLE posts
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(128) NOT NULL,
	create_time TIMESTAMP NOT NULL,
	author_id INTEGER NOT NULL,
	content TEXT,
	CONSTRAINT FK_post_author FOREIGN KEY (author_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 1','2000-01-01',1,'content 1');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 2','2000-01-02',2,'content 2');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 3','2000-01-03',2,'content 3');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 4','2000-01-04',2,'content 4');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 5','2000-01-05',3,'content 5');

CREATE TABLE comments
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	content TEXT NOT NULL,
	post_id INTEGER NOT NULL,
	author_id INTEGER NOT NULL,
	CONSTRAINT FK_post_comment FOREIGN KEY (post_id)
		REFERENCES posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_user_comment FOREIGN KEY (author_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO comments (content, post_id, author_id) VALUES ('comment 1',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 2',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 3',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 4',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 5',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 6',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 7',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 8',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 9',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 10',5, 3);

CREATE TABLE categories
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
	parent_id INTEGER,
	CONSTRAINT FK_category_category FOREIGN KEY (parent_id)
		REFERENCES categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO categories (name, parent_id) VALUES ('cat 1',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 2',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 3',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 4',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 5',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 6',5);
INSERT INTO categories (name, parent_id) VALUES ('cat 7',5);

CREATE TABLE post_category
(
	category_id INTEGER NOT NULL,
	post_id INTEGER NOT NULL,
	PRIMARY KEY (category_id, post_id),
	CONSTRAINT FK_post_category_post FOREIGN KEY (post_id)
		REFERENCES posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_post_category_category FOREIGN KEY (category_id)
		REFERENCES categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO post_category (category_id, post_id) VALUES (1,1);
INSERT INTO post_category (category_id, post_id) VALUES (2,1);
INSERT INTO post_category (category_id, post_id) VALUES (3,1);
INSERT INTO post_category (category_id, post_id) VALUES (4,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,3);

CREATE TABLE orders
(
	key1 INTEGER NOT NULL,
	key2 INTEGER NOT NULL,
	name VARCHAR(128),
	PRIMARY KEY (key1, key2)
) TYPE=INNODB;

INSERT INTO orders (key1,key2,name) VALUES (1,2,'order 12');
INSERT INTO orders (key1,key2,name) VALUES (1,3,'order 13');
INSERT INTO orders (key1,key2,name) VALUES (2,1,'order 21');
INSERT INTO orders (key1,key2,name) VALUES (2,2,'order 22');

CREATE TABLE items
(
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(128),
	col1 INTEGER NOT NULL,
	col2 INTEGER NOT NULL,
	CONSTRAINT FK_order_item FOREIGN KEY (col1,col2)
		REFERENCES orders (key1,key2) ON DELETE CASCADE ON UPDATE RESTRICT
) TYPE=INNODB;

INSERT INTO items (name,col1,col2) VALUES ('item 1',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 2',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 3',1,3);
INSERT INTO items (name,col1,col2) VALUES ('item 4',2,2);
INSERT INTO items (name,col1,col2) VALUES ('item 5',2,2);

CREATE TABLE types
(
	int_col INT NOT NULL,
	int_col2 INTEGER DEFAULT 1,
	char_col CHAR(100) NOT NULL,
	char_col2 VARCHAR(100) DEFAULT 'something',
	char_col3 TEXT,
	float_col REAL(4,3) NOT NULL,
	float_col2 DOUBLE DEFAULT 1.23,
	blob_col BLOB,
	numeric_col NUMERIC(5,2) DEFAULT 33.22,
	time TIMESTAMP DEFAULT '2002-01-01',
	bool_col BOOL NOT NULL,
	bool_col2 BOOLEAN DEFAULT 1
) TYPE=INNODB;


code:
/**
 * This is the database schema for testing PostgreSQL support of yii Active Record.
 * To test this feature, you need to create a database named 'yii' on 'localhost'
 * and create an account 'test/test' which owns this test database.
 */
CREATE SCHEMA test;

CREATE TABLE test.users
(
	id SERIAL NOT NULL PRIMARY KEY,
	username VARCHAR(128) NOT NULL,
	password VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL
);

INSERT INTO test.users (username, password, email) VALUES ('user1','pass1','email1');
INSERT INTO test.users (username, password, email) VALUES ('user2','pass2','email2');
INSERT INTO test.users (username, password, email) VALUES ('user3','pass3','email3');

CREATE TABLE test.user_friends
(
	id INTEGER NOT NULL,
	friend INTEGER NOT NULL,
	PRIMARY KEY (id, friend),
	CONSTRAINT FK_user_id FOREIGN KEY (id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_friend_id FOREIGN KEY (friend)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.user_friends VALUES (1,2);
INSERT INTO test.user_friends VALUES (1,3);
INSERT INTO test.user_friends VALUES (2,3);

CREATE TABLE test.profiles
(
	id SERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(128) NOT NULL,
	last_name VARCHAR(128) NOT NULL,
	user_id INTEGER NOT NULL,
	CONSTRAINT FK_profile_user FOREIGN KEY (user_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1);
INSERT INTO test.profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2);

CREATE TABLE test.posts
(
	id SERIAL NOT NULL PRIMARY KEY,
	title VARCHAR(128) NOT NULL,
	create_time TIMESTAMP NOT NULL,
	author_id INTEGER NOT NULL,
	content TEXT,
	CONSTRAINT FK_post_author FOREIGN KEY (author_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 1',TIMESTAMP '2004-10-19 10:23:54',1,'content 1');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 2',TIMESTAMP '2004-10-19 10:23:54',2,'content 2');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 3',TIMESTAMP '2004-10-19 10:23:54',2,'content 3');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 4',TIMESTAMP '2004-10-19 10:23:54',2,'content 4');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 5',TIMESTAMP '2004-10-19 10:23:54',3,'content 5');

CREATE TABLE test.comments
(
	id SERIAL NOT NULL PRIMARY KEY,
	content TEXT NOT NULL,
	post_id INTEGER NOT NULL,
	author_id INTEGER NOT NULL,
	CONSTRAINT FK_post_comment FOREIGN KEY (post_id)
		REFERENCES test.posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_user_comment FOREIGN KEY (author_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 1',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 2',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 3',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 4',2, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 5',2, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 6',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 7',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 8',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 9',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 10',5, 3);

CREATE TABLE test.categories
(
	id SERIAL NOT NULL PRIMARY KEY,
	name VARCHAR(128) NOT NULL,
	parent_id INTEGER,
	CONSTRAINT FK_category_category FOREIGN KEY (parent_id)
		REFERENCES test.categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.categories (name, parent_id) VALUES ('cat 1',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 2',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 3',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 4',1);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 5',1);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 6',5);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 7',5);

CREATE TABLE test.post_category
(
	category_id INTEGER NOT NULL,
	post_id INTEGER NOT NULL,
	PRIMARY KEY (category_id, post_id),
	CONSTRAINT FK_post_category_post FOREIGN KEY (post_id)
		REFERENCES test.posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_post_category_category FOREIGN KEY (category_id)
		REFERENCES test.categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.post_category (category_id, post_id) VALUES (1,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (2,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (3,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (4,2);
INSERT INTO test.post_category (category_id, post_id) VALUES (1,2);
INSERT INTO test.post_category (category_id, post_id) VALUES (1,3);

CREATE TABLE test.orders
(
	key1 INTEGER NOT NULL,
	key2 INTEGER NOT NULL,
	name VARCHAR(128),
	PRIMARY KEY (key1, key2)
);

INSERT INTO test.orders (key1,key2,name) VALUES (1,2,'order 12');
INSERT INTO test.orders (key1,key2,name) VALUES (1,3,'order 13');
INSERT INTO test.orders (key1,key2,name) VALUES (2,1,'order 21');
INSERT INTO test.orders (key1,key2,name) VALUES (2,2,'order 22');

CREATE TABLE test.items
(
	id SERIAL NOT NULL PRIMARY KEY,
	name VARCHAR(128),
	col1 INTEGER NOT NULL,
	col2 INTEGER NOT NULL,
	CONSTRAINT FK_order_item FOREIGN KEY (col1,col2)
		REFERENCES test.orders (key1,key2) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.items (name,col1,col2) VALUES ('item 1',1,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 2',1,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 3',1,3);
INSERT INTO test.items (name,col1,col2) VALUES ('item 4',2,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 5',2,2);

CREATE TABLE public.yii_types
(
	int_col INT NOT NULL,
	int_col2 INTEGER DEFAULT 1,
	char_col CHAR(100) NOT NULL,
	char_col2 VARCHAR(100) DEFAULT 'something',
	char_col3 TEXT,
	numeric_col NUMERIC(4,3) NOT NULL,
	real_col REAL DEFAULT 1.23,
	blob_col BYTEA,
	time TIMESTAMP,
	bool_col BOOL NOT NULL,
	bool_col2 BOOLEAN DEFAULT TRUE
);


code:
CREATE TABLE users
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	username VARCHAR(128) NOT NULL,
	password VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL
);

INSERT INTO users(id,username,password,email) VALUES (1,'user1','pass1','email1');
INSERT INTO users(id,username,password,email) VALUES (2,'user2','pass2','email2');
INSERT INTO users(id,username,password,email) VALUES (3,'user3','pass3','email3');
INSERT INTO users(id,username,password,email) VALUES (4,'user4','pass4','email4');

CREATE TABLE groups
(
	id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR(128) NOT NULL
);

INSERT INTO groups(id,name) VALUES (1,'group1');
INSERT INTO groups(id,name) VALUES (2,'group2');
INSERT INTO groups(id,name) VALUES (3,'group3');
INSERT INTO groups(id,name) VALUES (4,'group4');
INSERT INTO groups(id,name) VALUES (5,'group5');
INSERT INTO groups(id,name) VALUES (6,'group6');

CREATE TABLE groups_descriptions
(
	group_id INTEGER NOT NULL PRIMARY KEY,
	name VARCHAR(128) NOT NULL
);

INSERT INTO groups_descriptions(group_id,name) VALUES (1,'room1');
INSERT INTO groups_descriptions(group_id,name) VALUES (2,'room2');
INSERT INTO groups_descriptions(group_id,name) VALUES (3,'room3');
INSERT INTO groups_descriptions(group_id,name) VALUES (4,'room4');

CREATE TABLE roles
(
	user_id INTEGER NOT NULL,
	group_id INTEGER NOT NULL,
	name VARCHAR(128) NOT NULL,
	PRIMARY KEY(user_id,group_id)
);

INSERT INTO roles(user_id,group_id,name) VALUES (1,1,'dev');
INSERT INTO roles(user_id,group_id,name) VALUES (1,2,'user');
INSERT INTO roles(user_id,group_id,name) VALUES (2,1,'dev');
INSERT INTO roles(user_id,group_id,name) VALUES (2,3,'user');

CREATE TABLE mentorships
(
	teacher_id INTEGER NOT NULL,
	student_id INTEGER NOT NULL,
	progress VARCHAR(128) NOT NULL,
	PRIMARY KEY(teacher_id,student_id)
);

INSERT INTO mentorships(teacher_id,student_id,progress) VALUES (1,3,'good');
INSERT INTO mentorships(teacher_id,student_id,progress) VALUES (2,4,'average');

CREATE TABLE profiles
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	first_name VARCHAR(128) NOT NULL,
	last_name VARCHAR(128) NOT NULL,
	user_id INTEGER NOT NULL,
	CONSTRAINT FK_profile_user FOREIGN KEY (user_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1);
INSERT INTO profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2);

CREATE TABLE posts
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	title VARCHAR(128) NOT NULL,
	create_time TIMESTAMP NOT NULL,
	author_id INTEGER NOT NULL,
	content TEXT,
	CONSTRAINT FK_post_author FOREIGN KEY (author_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 1',100000,1,'content 1');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 2',100001,2,'content 2');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 3',100002,2,'content 3');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 4',100003,2,'content 4');
INSERT INTO posts (title, create_time, author_id, content) VALUES ('post 5',100004,3,'content 5');

CREATE TABLE posts_nofk
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	title VARCHAR(128) NOT NULL,
	create_time TIMESTAMP NOT NULL,
	author_id INTEGER NOT NULL,
	content TEXT
);

INSERT INTO posts_nofk (title, create_time, author_id, content) VALUES ('post 1',100000,1,'content 1');
INSERT INTO posts_nofk (title, create_time, author_id, content) VALUES ('post 2',100001,2,'content 2');
INSERT INTO posts_nofk (title, create_time, author_id, content) VALUES ('post 3',100002,2,'content 3');
INSERT INTO posts_nofk (title, create_time, author_id, content) VALUES ('post 4',100003,2,'content 4');
INSERT INTO posts_nofk (title, create_time, author_id, content) VALUES ('post 5',100004,3,'content 5');

CREATE TABLE comments
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	content TEXT NOT NULL,
	post_id INTEGER NOT NULL,
	author_id INTEGER NOT NULL,
	CONSTRAINT FK_post_comment FOREIGN KEY (post_id)
		REFERENCES posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_user_comment FOREIGN KEY (author_id)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO comments (content, post_id, author_id) VALUES ('comment 1',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 2',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 3',1, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 4',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 5',2, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 6',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 7',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 8',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 9',3, 2);
INSERT INTO comments (content, post_id, author_id) VALUES ('comment 10',5, 3);

CREATE TABLE categories
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(128) NOT NULL,
	parent_id INTEGER,
	CONSTRAINT FK_category_category FOREIGN KEY (parent_id)
		REFERENCES categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO categories (name, parent_id) VALUES ('cat 1',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 2',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 3',NULL);
INSERT INTO categories (name, parent_id) VALUES ('cat 4',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 5',1);
INSERT INTO categories (name, parent_id) VALUES ('cat 6',5);
INSERT INTO categories (name, parent_id) VALUES ('cat 7',5);

CREATE TABLE post_category
(
	category_id INTEGER NOT NULL,
	post_id INTEGER NOT NULL,
	PRIMARY KEY (category_id, post_id),
	CONSTRAINT FK_post_category_post FOREIGN KEY (post_id)
		REFERENCES posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_post_category_category FOREIGN KEY (category_id)
		REFERENCES categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO post_category (category_id, post_id) VALUES (1,1);
INSERT INTO post_category (category_id, post_id) VALUES (2,1);
INSERT INTO post_category (category_id, post_id) VALUES (3,1);
INSERT INTO post_category (category_id, post_id) VALUES (4,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,2);
INSERT INTO post_category (category_id, post_id) VALUES (1,3);

CREATE TABLE orders
(
	key1 INTEGER NOT NULL,
	key2 INTEGER NOT NULL,
	name VARCHAR(128),
	PRIMARY KEY (key1, key2)
);

INSERT INTO orders (key1,key2,name) VALUES (1,2,'order 12');
INSERT INTO orders (key1,key2,name) VALUES (1,3,'order 13');
INSERT INTO orders (key1,key2,name) VALUES (2,1,'order 21');
INSERT INTO orders (key1,key2,name) VALUES (2,2,'order 22');

CREATE TABLE items
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(128),
	col1 INTEGER NOT NULL,
	col2 INTEGER NOT NULL,
	CONSTRAINT FK_order_item FOREIGN KEY (col1,col2)
		REFERENCES orders (key1,key2) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO items (name,col1,col2) VALUES ('item 1',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 2',1,2);
INSERT INTO items (name,col1,col2) VALUES ('item 3',1,3);
INSERT INTO items (name,col1,col2) VALUES ('item 4',2,2);
INSERT INTO items (name,col1,col2) VALUES ('item 5',2,2);

CREATE TABLE types
(
	int_col INT NOT NULL,
	int_col2 INTEGER DEFAULT 1,
	char_col CHAR(100) NOT NULL,
	char_col2 VARCHAR(100) DEFAULT 'something',
	char_col3 TEXT,
	float_col REAL(4,3) NOT NULL,
	float_col2 DOUBLE DEFAULT 1.23,
	blob_col BLOB,
	numeric_col NUMERIC(5,2) DEFAULT 33.22,
	time TIMESTAMP DEFAULT 123,
	bool_col BOOL NOT NULL,
	bool_col2 BOOLEAN DEFAULT 1,
	null_col INTEGER DEFAULT NULL
);

CREATE TABLE Content
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	class VARCHAR(128),
	parentID INTEGER NOT NULL,
	ownerID INTEGER NOT NULL,
	title VARCHAR(100),
	CONSTRAINT FK_content_user FOREIGN KEY (ownerID)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
	CONSTRAINT FK_content_parent FOREIGN KEY (parentID)
		REFERENCES Content (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Article',-1,1,'article 1');
INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Article',-1,2,'article 2');
INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Comment',1,1,'comment 1');
INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Article',-1,2,'article 3');
INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Comment',4,2,'comment 2');
INSERT INTO Content (class,parentID,ownerID,title) VALUES ('Comment',4,1,'comment 3');

CREATE TABLE Article
(
	id INTEGER NOT NULL PRIMARY KEY,
	authorID INTEGER NOT NULL,
	body TEXT,
	CONSTRAINT FK_article_content FOREIGN KEY (id)
		REFERENCES Content (id) ON DELETE CASCADE ON UPDATE RESTRICT
	CONSTRAINT FK_article_author FOREIGN KEY (authorID)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO Article (id,authorID,body) VALUES (1,1,'content for article 1');
INSERT INTO Article (id,authorID,body) VALUES (2,2,'content for article 2');
INSERT INTO Article (id,authorID,body) VALUES (4,1,'content for article 3');

CREATE TABLE Comment
(
	id INTEGER NOT NULL PRIMARY KEY,
	authorID INTEGER NOT NULL,
	body TEXT,
	CONSTRAINT FK_comment_content FOREIGN KEY (id)
		REFERENCES Content (id) ON DELETE CASCADE ON UPDATE RESTRICT
	CONSTRAINT FK_article_author FOREIGN KEY (authorID)
		REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO Comment (id,authorID,body) VALUES (3,1,'content for comment 1');
INSERT INTO Comment (id,authorID,body) VALUES (5,1,'content for comment 2');
INSERT INTO Comment (id,authorID,body) VALUES (6,1,'content for comment 3');