diff options
Diffstat (limited to 'demos/blog/protected/Common/BlogDataModule.php')
| -rw-r--r-- | demos/blog/protected/Common/BlogDataModule.php | 535 |
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 |