Merge from 3.0 branch till 1099.

1 files changed, 535 insertions, 0 deletions

diff --git a/demos/blog/protected/Common/BlogDataModule.php b/demos/blog/protected/Common/BlogDataModule.php
new file mode 100644
index 00000000..714743e7
--- /dev/null
+++ b/demos/blog/protected/Common/BlogDataModule.php
@@ -0,0 +1,535 @@
+<?php

+

+// post status: 0 - draft, 1 - published

+// comment status: 0 - awaiting approval, 1 - published

+class BlogDataModule extends TModule

+{

+	const DB_FILE_EXT='.db';

+	const DEFAULT_DB_FILE='Application.Data.Blog';

+	private $_db=null;

+	private $_dbFile=null;

+

+	public function init($config)

+	{

+		$this->connectDatabase();

+	}

+

+	public function getDbFile()

+	{

+		if($this->_dbFile===null)

+			$this->_dbFile=Prado::getPathOfNamespace(self::DEFAULT_DB_FILE,self::DB_FILE_EXT);

+		return $this->_dbFile;

+	}

+

+	public function setDbFile($value)

+	{

+		if(($this->_dbFile=Prado::getPathOfNamespace($value,self::DB_FILE_EXT))===null)

+			throw new BlogException('blogdatamodule_dbfile_invalid',$value);

+	}

+

+	protected function createDatabase()

+	{

+		$schemaFile=dirname(__FILE__).'/schema.sql';

+		$statements=explode(';',file_get_contents($schemaFile));

+		foreach($statements as $statement)

+		{

+			if(trim($statement)!=='')

+			{

+				if(@sqlite_query($this->_db,$statement)===false)

+					throw new BlogException('blogdatamodule_createdatabase_failed',sqlite_error_string(sqlite_last_error($this->_db)),$statement);

+			}

+		}

+	}

+

+	protected function connectDatabase()

+	{

+		$dbFile=$this->getDbFile();

+		$newDb=!is_file($dbFile);

+		$error='';

+		if(($this->_db=sqlite_open($dbFile,0666,$error))===false)

+			throw new BlogException('blogdatamodule_dbconnect_failed',$error);

+		if($newDb)

+			$this->createDatabase();

+	}

+

+	protected function generateModifier($filter,$orderBy,$limit)

+	{

+		$modifier='';

+		if($filter!=='')

+			$modifier=' WHERE '.$filter;

+		if($orderBy!=='')

+			$modifier.=' ORDER BY '.$orderBy;

+		if($limit!=='')

+			$modifier.=' LIMIT '.$limit;

+		return $modifier;

+	}

+

+	public function query($sql)

+	{

+		if(($result=@sqlite_query($this->_db,$sql))!==false)

+			return $result;

+		else

+			throw new BlogException('blogdatamodule_query_failed',sqlite_error_string(sqlite_last_error($this->_db)),$sql);

+	}

+

+	protected function populateUserRecord($row)

+	{

+		$userRecord=new UserRecord;

+		$userRecord->ID=(integer)$row['id'];

+		$userRecord->Name=$row['name'];

+		$userRecord->FullName=$row['full_name'];

+		$userRecord->Role=(integer)$row['role'];

+		$userRecord->Password=$row['passwd'];

+		$userRecord->VerifyCode=$row['vcode'];

+		$userRecord->Email=$row['email'];

+		$userRecord->CreateTime=(integer)$row['reg_time'];

+		$userRecord->Status=(integer)$row['status'];

+		$userRecord->Website=$row['website'];

+		return $userRecord;

+	}

+

+	public function queryUsers($filter='',$orderBy='',$limit='')

+	{

+		if($filter!=='')

+			$filter='WHERE '.$filter;

+		$sql="SELECT * FROM tblUsers $filter $orderBy $limit";

+		$result=$this->query($sql);

+		$rows=sqlite_fetch_all($result,SQLITE_ASSOC);

+		$users=array();

+		foreach($rows as $row)

+			$users[]=$this->populateUserRecord($row);

+		return $users;

+	}

+

+	public function queryUserCount($filter)

+	{

+		if($filter!=='')

+			$filter='WHERE '.$filter;

+		$sql="SELECT COUNT(id) AS user_count FROM tblUsers $filter";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $row['user_count'];

+		else

+			return 0;

+	}

+

+	public function queryUserByID($id)

+	{

+		$sql="SELECT * FROM tblUsers WHERE id=$id";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $this->populateUserRecord($row);

+		else

+			return null;

+	}

+

+	public function queryUserByName($name)

+	{

+		$name=sqlite_escape_string($name);

+		$sql="SELECT * FROM tblUsers WHERE name='$name'";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $this->populateUserRecord($row);

+		else

+			return null;

+	}

+

+	public function insertUser($user)

+	{

+		$name=sqlite_escape_string($user->Name);

+		$fullName=sqlite_escape_string($user->FullName);

+		$passwd=sqlite_escape_string($user->Password);

+		$email=sqlite_escape_string($user->Email);

+		$website=sqlite_escape_string($user->Website);

+		$createTime=time();

+		$sql="INSERT INTO tblUsers ".

+				"(name,full_name,role,passwd,email,reg_time,website) ".

+				"VALUES ('$name','$fullName',{$user->Role},'$passwd','$email',$createTime,'$website')";

+		$this->query($sql);

+		$user->ID=sqlite_last_insert_rowid($this->_db);

+	}

+

+	public function updateUser($user)

+	{

+		$name=sqlite_escape_string($user->Name);

+		$fullName=sqlite_escape_string($user->FullName);

+		$passwd=sqlite_escape_string($user->Password);

+		$email=sqlite_escape_string($user->Email);

+		$website=sqlite_escape_string($user->Website);

+		$sql="UPDATE tblUsers SET

+				name='$name',

+				full_name='$fullName',

+				role={$user->Role},

+				passwd='$passwd',

+				vcode='{$user->VerifyCode}',

+				email='$email',

+				status={$user->Status},

+				website='$website'

+				WHERE id={$user->ID}";

+		$this->query($sql);

+	}

+

+	public function deleteUser($id)

+	{

+		$this->query("DELETE FROM tblUsers WHERE id=$id");

+	}

+

+	protected function populatePostRecord($row)

+	{

+		$postRecord=new PostRecord;

+		$postRecord->ID=(integer)$row['id'];

+		$postRecord->AuthorID=(integer)$row['author_id'];

+		if($row['author_full_name']!=='')

+			$postRecord->AuthorName=$row['author_full_name'];

+		else

+			$postRecord->AuthorName=$row['author_name'];

+		$postRecord->CreateTime=(integer)$row['create_time'];

+		$postRecord->ModifyTime=(integer)$row['modify_time'];

+		$postRecord->Title=$row['title'];

+		$postRecord->Content=$row['content'];

+		$postRecord->Status=(integer)$row['status'];

+		$postRecord->CommentCount=(integer)$row['comment_count'];

+		return $postRecord;

+	}

+

+	public function queryPosts($authorFilter,$timeFilter,$categoryFilter,$orderBy,$limit)

+	{

+		$filter='';

+		if($authorFilter!=='')

+			$filter.=" AND $authorFilter";

+		if($timeFilter!=='')

+			$filter.=" AND $timeFilter";

+		if($categoryFilter!=='')

+			$filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";

+		$sql="SELECT a.id AS id,

+					a.author_id AS author_id,

+					b.name AS author_name,

+					b.full_name AS author_full_name,

+					a.create_time AS create_time,

+					a.modify_time AS modify_time,

+					a.title AS title,

+					a.content AS content,

+					a.status AS status,

+					a.comment_count AS comment_count

+				FROM tblPosts a, tblUsers b

+				WHERE a.author_id=b.id $filter $orderBy $limit";

+		$result=$this->query($sql);

+		$rows=sqlite_fetch_all($result,SQLITE_ASSOC);

+		$posts=array();

+		foreach($rows as $row)

+			$posts[]=$this->populatePostRecord($row);

+		return $posts;

+	}

+

+	public function queryPostCount($authorFilter,$timeFilter,$categoryFilter)

+	{

+		$filter='';

+		if($authorFilter!=='')

+			$filter.=" AND $authorFilter";

+		if($timeFilter!=='')

+			$filter.=" AND $timeFilter";

+		if($categoryFilter!=='')

+			$filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";

+		$sql="SELECT COUNT(a.id) AS post_count

+				FROM tblPosts a, tblUsers b

+				WHERE a.author_id=b.id $filter";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $row['post_count'];

+		else

+			return 0;

+	}

+

+	public function queryPostByID($id)

+	{

+		$sql="SELECT a.id AS id,

+		             a.author_id AS author_id,

+		             b.name AS author_name,

+		             b.full_name AS author_full_name,

+		             a.create_time AS create_time,

+		             a.modify_time AS modify_time,

+		             a.title AS title,

+		             a.content AS content,

+		             a.status AS status,

+		             a.comment_count AS comment_count

+		      FROM tblPosts a, tblUsers b

+		      WHERE a.id=$id AND a.author_id=b.id";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $this->populatePostRecord($row);

+		else

+			return null;

+	}

+

+	public function insertPost($post,$catIDs)

+	{

+		$title=sqlite_escape_string($post->Title);

+		$content=sqlite_escape_string($post->Content);

+		$sql="INSERT INTO tblPosts

+				(author_id,create_time,title,content,status)

+				VALUES ({$post->AuthorID},{$post->CreateTime},'$title','$content',{$post->Status})";

+		$this->query($sql);

+		$post->ID=sqlite_last_insert_rowid($this->_db);

+		foreach($catIDs as $catID)

+			$this->insertPostCategory($post->ID,$catID);

+	}

+

+	public function updatePost($post,$newCatIDs=null)

+	{

+		if($newCatIDs!==null)

+		{

+			$cats=$this->queryCategoriesByPostID($post->ID);

+			$catIDs=array();

+			foreach($cats as $cat)

+				$catIDs[]=$cat->ID;

+			$deleteIDs=array_diff($catIDs,$newCatIDs);

+			foreach($deleteIDs as $id)

+				$this->deletePostCategory($post->ID,$id);

+			$insertIDs=array_diff($newCatIDs,$catIDs);

+			foreach($insertIDs as $id)

+				$this->insertPostCategory($post->ID,$id);

+		}

+

+		$title=sqlite_escape_string($post->Title);

+		$content=sqlite_escape_string($post->Content);

+		$sql="UPDATE tblPosts SET

+				modify_time={$post->ModifyTime},

+				title='$title',

+				content='$content',

+				status={$post->Status}

+				WHERE id={$post->ID}";

+		$this->query($sql);

+	}

+

+	public function deletePost($id)

+	{

+		$cats=$this->queryCategoriesByPostID($id);

+		foreach($cats as $cat)

+			$this->deletePostCategory($id,$cat->ID);

+		$this->query("DELETE FROM tblComments WHERE post_id=$id");

+		$this->query("DELETE FROM tblPosts WHERE id=$id");

+	}

+

+	protected function populateCommentRecord($row)

+	{

+		$commentRecord=new CommentRecord;

+		$commentRecord->ID=(integer)$row['id'];

+		$commentRecord->PostID=(integer)$row['post_id'];

+		$commentRecord->AuthorName=$row['author_name'];

+		$commentRecord->AuthorEmail=$row['author_email'];

+		$commentRecord->AuthorWebsite=$row['author_website'];

+		$commentRecord->AuthorIP=$row['author_ip'];

+		$commentRecord->CreateTime=(integer)$row['create_time'];

+		$commentRecord->Content=$row['content'];

+		$commentRecord->Status=(integer)$row['status'];

+		return $commentRecord;

+	}

+

+	public function queryCommentsByPostID($id)

+	{

+		$sql="SELECT * FROM tblComments WHERE post_id=$id";

+		$result=$this->query($sql);

+		$rows=sqlite_fetch_all($result,SQLITE_ASSOC);

+		$comments=array();

+		foreach($rows as $row)

+			$comments[]=$this->populateCommentRecord($row);

+		return $comments;

+	}

+

+	public function insertComment($comment)

+	{

+		$authorName=sqlite_escape_string($comment->AuthorName);

+		$authorEmail=sqlite_escape_string($comment->AuthorEmail);

+		$authorWebsite=sqlite_escape_string($comment->AuthorWebsite);

+		$content=sqlite_escape_string($comment->Content);

+		$sql="INSERT INTO tblComments

+				(post_id,author_name,author_email,author_website,author_ip,create_time,status,content)

+				VALUES ({$comment->PostID},'$authorName','$authorEmail','$authorWebsite','{$comment->AuthorIP}',{$comment->CreateTime},{$comment->Status},'$content')";

+		$this->query($sql);

+		$comment->ID=sqlite_last_insert_rowid($this->_db);

+		$this->query("UPDATE tblPosts SET comment_count=comment_count+1 WHERE id={$comment->PostID}");

+	}

+

+	public function updateComment($comment)

+	{

+		$authorName=sqlite_escape_string($comment->AuthorName);

+		$authorEmail=sqlite_escape_string($comment->AuthorEmail);

+		$content=sqlite_escape_string($comment->Content);

+		$sql="UPDATE tblComments SET status={$comment->Status} WHERE id={$comment->ID}";

+		$this->query($sql);

+	}

+

+	public function deleteComment($id)

+	{

+		$result=$this->query("SELECT post_id FROM tblComments WHERE id=$id");

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+		{

+			$postID=$row['post_id'];

+			$this->query("DELETE FROM tblComments WHERE id=$id");

+			$this->query("UPDATE tblPosts SET comment_count=comment_count-1 WHERE id=$postID");

+		}

+	}

+

+	protected function populateCategoryRecord($row)

+	{

+		$catRecord=new CategoryRecord;

+		$catRecord->ID=(integer)$row['id'];

+		$catRecord->Name=$row['name'];

+		$catRecord->Description=$row['description'];

+		$catRecord->PostCount=$row['post_count'];

+		return $catRecord;

+	}

+

+	public function queryCategories()

+	{

+		$sql="SELECT * FROM tblCategories";

+		$result=$this->query($sql);

+		$rows=sqlite_fetch_all($result,SQLITE_ASSOC);

+		$cats=array();

+		foreach($rows as $row)

+			$cats[]=$this->populateCategoryRecord($row);

+		return $cats;

+	}

+

+	public function queryCategoriesByPostID($postID)

+	{

+		$sql="SELECT a.id AS id,

+				a.name AS name,

+				a.description AS description,

+				a.post_count AS post_count

+				FROM tblCategories a, tblPost2Category b

+				WHERE a.id=b.category_id AND b.post_id=$postID";

+		$result=$this->query($sql);

+		$rows=sqlite_fetch_all($result,SQLITE_ASSOC);

+		$cats=array();

+		foreach($rows as $row)

+			$cats[]=$this->populateCategoryRecord($row);

+		return $cats;

+	}

+

+	public function queryCategoryByID($id)

+	{

+		$sql="SELECT * FROM tblCategories WHERE id=$id";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $this->populateCategoryRecord($row);

+		else

+			return null;

+	}

+

+	public function queryCategoryByName($name)

+	{

+		$name=sqlite_escape_string($name);

+		$sql="SELECT * FROM tblCategories WHERE name='$name'";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $this->populateCategoryRecord($row);

+		else

+			return null;

+	}

+

+	public function insertCategory($category)

+	{

+		$name=sqlite_escape_string($category->Name);

+		$description=sqlite_escape_string($category->Description);

+		$sql="INSERT INTO tblCategories

+				(name,description)

+				VALUES ('$name','$description')";

+		$this->query($sql);

+		$category->ID=sqlite_last_insert_rowid($this->_db);

+	}

+

+	public function updateCategory($category)

+	{

+		$name=sqlite_escape_string($category->Name);

+		$description=sqlite_escape_string($category->Description);

+		$sql="UPDATE tblCategories SET name='$name', description='$description', post_count={$category->PostCount} WHERE id={$category->ID}";

+		$this->query($sql);

+	}

+

+	public function deleteCategory($id)

+	{

+		$sql="DELETE FROM tblPost2Category WHERE category_id=$id";

+		$this->query($sql);

+		$sql="DELETE FROM tblCategories WHERE id=$id";

+		$this->query($sql);

+	}

+

+	public function insertPostCategory($postID,$categoryID)

+	{

+		$sql="INSERT INTO tblPost2Category (post_id, category_id) VALUES ($postID, $categoryID)";

+		$this->query($sql);

+		$sql="UPDATE tblCategories SET post_count=post_count+1 WHERE id=$categoryID";

+		$this->query($sql);

+	}

+

+	public function deletePostCategory($postID,$categoryID)

+	{

+		$sql="DELETE FROM tblPost2Category WHERE post_id=$postID AND category_id=$categoryID";

+		if($this->query($sql)>0)

+		{

+			$sql="UPDATE tblCategories SET post_count=post_count-1 WHERE id=$categoryID";

+			$this->query($sql);

+		}

+	}

+

+	public function queryEarliestPostTime()

+	{

+		$sql="SELECT MIN(create_time) AS create_time FROM tblPosts";

+		$result=$this->query($sql);

+		if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)

+			return $row['create_time'];

+		else

+			return time();

+	}

+}

+

+class UserRecord

+{

+	public $ID;

+	public $Name;

+	public $FullName;

+	public $Role;

+	public $Password;

+	public $VerifyCode;

+	public $Email;

+	public $CreateTime;

+	public $Status;

+	public $Website;

+}

+

+class PostRecord

+{

+	public $ID;

+	public $AuthorID;

+	public $AuthorName;

+	public $CreateTime;

+	public $ModifyTime;

+	public $Title;

+	public $Content;

+	public $Status;

+	public $CommentCount;

+}

+

+class CommentRecord

+{

+	public $ID;

+	public $PostID;

+	public $AuthorName;

+	public $AuthorEmail;

+	public $AuthorWebsite;

+	public $AuthorIP;

+	public $CreateTime;

+	public $Status;

+	public $Content;

+}

+

+class CategoryRecord

+{

+	public $ID;

+	public $Name;

+	public $Description;

+	public $PostCount;

+}

+

+?>
\ No newline at end of file