From 2ea02214b2fb6bedb58dbbd318ef171a9e146524 Mon Sep 17 00:00:00 2001 From: xue <> Date: Mon, 29 May 2006 03:08:07 +0000 Subject: Merge from 3.0 branch till 1099. --- demos/blog/protected/Common/BlogDataModule.php | 535 ++++++++++++++++++++++++ demos/blog/protected/Common/BlogErrors.php | 23 + demos/blog/protected/Common/BlogException.php | 14 + demos/blog/protected/Common/BlogPage.php | 26 ++ demos/blog/protected/Common/BlogUser.php | 38 ++ demos/blog/protected/Common/BlogUserManager.php | 56 +++ demos/blog/protected/Common/XListMenu.php | 127 ++++++ demos/blog/protected/Common/messages.txt | 4 + demos/blog/protected/Common/schema.sql | 70 ++++ 9 files changed, 893 insertions(+) create mode 100644 demos/blog/protected/Common/BlogDataModule.php create mode 100644 demos/blog/protected/Common/BlogErrors.php create mode 100644 demos/blog/protected/Common/BlogException.php create mode 100644 demos/blog/protected/Common/BlogPage.php create mode 100644 demos/blog/protected/Common/BlogUser.php create mode 100644 demos/blog/protected/Common/BlogUserManager.php create mode 100644 demos/blog/protected/Common/XListMenu.php create mode 100644 demos/blog/protected/Common/messages.txt create mode 100644 demos/blog/protected/Common/schema.sql (limited to 'demos/blog/protected/Common') 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 @@ +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 diff --git a/demos/blog/protected/Common/BlogErrors.php b/demos/blog/protected/Common/BlogErrors.php new file mode 100644 index 00000000..501ec1c9 --- /dev/null +++ b/demos/blog/protected/Common/BlogErrors.php @@ -0,0 +1,23 @@ +'Unknown error.', + self::ERROR_POST_NOT_FOUND=>'The specified post cannot be found.', + self::ERROR_USER_NOT_FOUND=>'The specified user account cannot be found.', + self::ERROR_PERMISSION_DENIED=>'Sorry, you do not have permission to perform this action.', + ); + + public static function getMessage($errorCode) + { + return isset(self::$_errorMessages[$errorCode])?self::$_errorMessages[$errorCode]:self::$_errorMessages[0]; + } +} + +?> \ No newline at end of file diff --git a/demos/blog/protected/Common/BlogException.php b/demos/blog/protected/Common/BlogException.php new file mode 100644 index 00000000..ab8020d1 --- /dev/null +++ b/demos/blog/protected/Common/BlogException.php @@ -0,0 +1,14 @@ + \ No newline at end of file diff --git a/demos/blog/protected/Common/BlogPage.php b/demos/blog/protected/Common/BlogPage.php new file mode 100644 index 00000000..f1634a80 --- /dev/null +++ b/demos/blog/protected/Common/BlogPage.php @@ -0,0 +1,26 @@ +getApplication()->getModule('data'); + } + + public function gotoDefaultPage() + { + $this->Response->redirect($this->Service->constructUrl($this->Service->DefaultPage)); + } + + public function gotoPage($pagePath,$getParameters=null) + { + $this->Response->redirect($this->Service->constructUrl($pagePath,$getParameters)); + } + + public function reportError($errorCode) + { + $this->gotoPage('ErrorReport',array('id'=>$errorCode)); + } +} + +?> \ No newline at end of file diff --git a/demos/blog/protected/Common/BlogUser.php b/demos/blog/protected/Common/BlogUser.php new file mode 100644 index 00000000..af49c8d7 --- /dev/null +++ b/demos/blog/protected/Common/BlogUser.php @@ -0,0 +1,38 @@ +_id; + } + + public function setID($value) + { + $this->_id=$value; + } + + public function saveToString() + { + $a=array($this->_id,parent::saveToString()); + return serialize($a); + } + + public function loadFromString($data) + { + if(!empty($data)) + { + list($id,$str)=unserialize($data); + $this->_id=$id; + return parent::loadFromString($str); + } + else + return $this; + } +} + +?> \ No newline at end of file diff --git a/demos/blog/protected/Common/BlogUserManager.php b/demos/blog/protected/Common/BlogUserManager.php new file mode 100644 index 00000000..c3ddb80b --- /dev/null +++ b/demos/blog/protected/Common/BlogUserManager.php @@ -0,0 +1,56 @@ +Application->getModule('data'); + if(($userRecord=$db->queryUserByName($username))!==null) + { + $user=new BlogUser($this); + $user->setID($userRecord->ID); + $user->setName($username); + $user->setIsGuest(false); + $user->setRoles($userRecord->Role===0?'user':'admin'); + return $user; + } + else + return null; + } + } + + /** + * Validates if the username and password are correct. + * @param string user name + * @param string password + * @return boolean true if validation is successful, false otherwise. + */ + public function validateUser($username,$password) + { + $db=$this->Application->getModule('data'); + if(($userRecord=$db->queryUserByName($username))!==null) + return $userRecord->Password===md5($password); + else + return false; + } +} + +?> \ No newline at end of file diff --git a/demos/blog/protected/Common/XListMenu.php b/demos/blog/protected/Common/XListMenu.php new file mode 100644 index 00000000..f8223585 --- /dev/null +++ b/demos/blog/protected/Common/XListMenu.php @@ -0,0 +1,127 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2006 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Revision: $ $Date: $ + */ + +Prado::using('System.Web.UI.WebControls.TListControl'); + +/** + * XListMenu class + * + * XListMenu displays a list of hyperlinks that can be used for page menus. + * Menu items adjust their css class automatically according to the current + * page displayed. In particular, a menu item is considered as active if + * the URL it represents is for the page currently displayed. + * + * Usage of XListMenu is similar to PRADO list controls. Each list item has + * two extra properties: {@link XListMenuItem::setPagePath PagePath} and + * {@link XListMenuItem::setNavigateUrl NavigateUrl}. The former is used to + * determine if the item is active or not, while the latter specifies the + * URL for the item. If the latter is not specified, a URL to the page is + * generated automatically. + * + * In template, you may use the following tags to specify a menu: + * + * + * + * + * + * + * + * @author Qiang Xue + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2006 PradoSoft + * @license http://www.pradosoft.com/license/ + */ +class XListMenu extends TListControl +{ + public function addParsedObject($object) + { + if($object instanceof XListMenuItem) + parent::addParsedObject($object); + } + + public function getActiveCssClass() + { + return $this->getViewState('ActiveCssClass',''); + } + + public function setActiveCssClass($value) + { + $this->setViewState('ActiveCssClass',$value,''); + } + + public function getInactiveCssClass() + { + return $this->getViewState('InactiveCssClass',''); + } + + public function setInactiveCssClass($value) + { + $this->setViewState('InactiveCssClass',$value,''); + } + + public function render($writer) + { + if(($activeClass=$this->getActiveCssClass())!=='') + $activeClass=' class="'.$activeClass.'"'; + if(($inactiveClass=$this->getInactiveCssClass())!=='') + $inactiveClass=' class="'.$inactiveClass.'"'; + $currentPagePath=$this->getPage()->getPagePath(); + $writer->write(""); + } +} + +class XListMenuItem extends TListItem +{ + public function getPagePath() + { + return $this->getValue(); + } + + public function setPagePath($value) + { + $this->setValue($value); + } + + public function getNavigateUrl() + { + return $this->hasAttribute('NavigateUrl')?$this->getAttribute('NavigateUrl'):''; + } + + public function setNavigateUrl($value) + { + $this->setAttribute('NavigateUrl',$value); + } +} + +?> \ No newline at end of file diff --git a/demos/blog/protected/Common/messages.txt b/demos/blog/protected/Common/messages.txt new file mode 100644 index 00000000..deb15ee3 --- /dev/null +++ b/demos/blog/protected/Common/messages.txt @@ -0,0 +1,4 @@ +blogdatamodule_dbconnect_failed = Unable to connect to database: {0} +blogdatamodule_dbfile_invalid = BlogDataModule.DbFile='{0}' is invalid. +blogdatamodule_createdatabase_failed = BlogDataModule failed to create database when executing SQL: {1}. Last SQL error is: {0}. +blogdatamodule_query_failed = Failed to execute SQL: {1}. Last SQL error is: {0}. \ No newline at end of file diff --git a/demos/blog/protected/Common/schema.sql b/demos/blog/protected/Common/schema.sql new file mode 100644 index 00000000..49f6f429 --- /dev/null +++ b/demos/blog/protected/Common/schema.sql @@ -0,0 +1,70 @@ +CREATE TABLE tblUsers ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(128) NOT NULL UNIQUE, + full_name VARCHAR(128) DEFAULT '', + role INTEGER NOT NULL DEFAULT 0, /* 0: user, 1: admin */ + passwd VARCHAR(128) NOT NULL, + vcode VARCHAR(128) DEFAULT '', + email VARCHAR(128) NOT NULL, + reg_time INTEGER NOT NULL, + status INTEGER NOT NULL DEFAULT 0, /* 0: normal, 1: disabled, 2: pending approval */ + website VARCHAR(128) DEFAULT '' +); + +CREATE TABLE tblPosts ( + id INTEGER NOT NULL PRIMARY KEY, + author_id INTEGER NOT NULL, + create_time INTEGER NOT NULL, + modify_time INTEGER DEFAULT 0, + title VARCHAR(256) NOT NULL, + content TEXT NOT NULL, + status INTEGER NOT NULL DEFAULT 0, /* 0: published, 1: draft, 2: pending approval */ + comment_count INTEGER NOT NULL DEFAULT 0 +); + +CREATE TABLE tblComments ( + id INTEGER NOT NULL PRIMARY KEY, + post_id INTEGER NOT NULL, + author_name VARCHAR(64) NOT NULL, + author_email VARCHAR(128) NOT NULL, + author_website VARCHAR(128) DEFAULT '', + author_ip CHAR(16) NOT NULL, + create_time INTEGER NOT NULL, + status INTEGER NOT NULL DEFAULT 0, /* 0: published, 1: pending approval */ + content TEXT NOT NULL +); + +CREATE TABLE tblCategories ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(128) NOT NULL UNIQUE, + description TEXT DEFAULT '', + post_count INTEGER NOT NULL DEFAULT 0 +); + +CREATE TABLE tblAttachments ( + id VARCHAR(128) NOT NULL PRIMARY KEY, + post_id INTEGER NOT NULL, + create_time INTEGER NOT NULL, + file_name VARCHAR(128) NOT NULL, + file_size INTEGER NOT NULL, + mime_type VARCHAR(32) NOT NULL DEFAULT 'text/html', + download_count INTEGER NOT NULL DEFAULT 0 +); + +CREATE TABLE tblPost2Category ( + post_id INTEGER NOT NULL, + category_id INTEGER NOT NULL, + PRIMARY KEY (post_id, category_id) +); + +INSERT INTO tblUsers (id,name,full_name,role,status,passwd,email,reg_time,website) + VALUES (1,'admin','Prado User',1,0,'4d688da592969d0a56b5accec3ce8554','admin@example.com',1148819681,'http://www.pradosoft.com'); + +INSERT INTO tblPosts (id,author_id,create_time,title,content,status) + VALUES (1,1,1148819691,'Welcome to Prado Weblog','Congratulations! You have successfully installed Prado Weblog. An administrator account has been created. Please login with admin/prado and update your password as soon as possible.',0); + +INSERT INTO tblCategories (name,description,post_count) + VALUES ('Miscellaneous','This category holds posts on any topic.',1); + +INSERT INTO tblPost2Category (post_id,category_id) + VALUES (1,1); -- cgit v1.2.3