[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3   * BlogDataModule class file
   4   *
   5   * @author Qiang Xue <qiang.xue@gmail.com>
   6   * @link http://www.pradosoft.com/
   7   * @copyright Copyright &copy; 2006 PradoSoft
   8   * @license http://www.pradosoft.com/license/
   9   * @version $Id: BlogDataModule.php 1397 2006-09-07 07:55:53Z wei $
  10   */
  11  
  12  /**
  13   * BlogDataModule class
  14   *
  15   * @author Qiang Xue <qiang.xue@gmail.com>
  16   * @link http://www.pradosoft.com/
  17   * @copyright Copyright &copy; 2006 PradoSoft
  18   * @license http://www.pradosoft.com/license/
  19   */
  20  class BlogDataModule extends TModule
  21  {
  22      const DB_FILE_EXT='.db';
  23      const DEFAULT_DB_FILE='Application.Data.Blog';
  24      private $_db=null;
  25      private $_dbFile=null;
  26  
  27  	public function init($config)
  28      {
  29          $this->connectDatabase();
  30      }
  31  
  32  	public function getDbFile()
  33      {
  34          if($this->_dbFile===null)
  35              $this->_dbFile=Prado::getPathOfNamespace(self::DEFAULT_DB_FILE,self::DB_FILE_EXT);
  36          return $this->_dbFile;
  37      }
  38  
  39  	public function setDbFile($value)
  40      {
  41          if(($this->_dbFile=Prado::getPathOfNamespace($value,self::DB_FILE_EXT))===null)
  42              throw new BlogException(500,'blogdatamodule_dbfile_invalid',$value);
  43      }
  44  
  45  	protected function createDatabase()
  46      {
  47          $schemaFile=dirname(__FILE__).'/schema.sql';
  48          $statements=explode(';',file_get_contents($schemaFile));
  49          foreach($statements as $statement)
  50          {
  51              if(trim($statement)!=='')
  52              {
  53                  if(@sqlite_query($this->_db,$statement)===false)
  54                      throw new BlogException(500,'blogdatamodule_createdatabase_failed',sqlite_error_string(sqlite_last_error($this->_db)),$statement);
  55              }
  56          }
  57      }
  58  
  59  	protected function connectDatabase()
  60      {
  61          $dbFile=$this->getDbFile();
  62          $newDb=!is_file($dbFile);
  63          $error='';
  64          if(($this->_db=sqlite_open($dbFile,0666,$error))===false)
  65              throw new BlogException(500,'blogdatamodule_dbconnect_failed',$error);
  66          if($newDb)
  67              $this->createDatabase();
  68      }
  69  
  70  	protected function generateModifier($filter,$orderBy,$limit)
  71      {
  72          $modifier='';
  73          if($filter!=='')
  74              $modifier=' WHERE '.$filter;
  75          if($orderBy!=='')
  76              $modifier.=' ORDER BY '.$orderBy;
  77          if($limit!=='')
  78              $modifier.=' LIMIT '.$limit;
  79          return $modifier;
  80      }
  81  
  82  	public function query($sql)
  83      {
  84          if(($result=@sqlite_query($this->_db,$sql))!==false)
  85              return $result;
  86          else
  87              throw new BlogException(500,'blogdatamodule_query_failed',sqlite_error_string(sqlite_last_error($this->_db)),$sql);
  88      }
  89  
  90  	protected function populateUserRecord($row)
  91      {
  92          $userRecord=new UserRecord;
  93          $userRecord->ID=(integer)$row['id'];
  94          $userRecord->Name=$row['name'];
  95          $userRecord->FullName=$row['full_name'];
  96          $userRecord->Role=(integer)$row['role'];
  97          $userRecord->Password=$row['passwd'];
  98          $userRecord->VerifyCode=$row['vcode'];
  99          $userRecord->Email=$row['email'];
 100          $userRecord->CreateTime=(integer)$row['reg_time'];
 101          $userRecord->Status=(integer)$row['status'];
 102          $userRecord->Website=$row['website'];
 103          return $userRecord;
 104      }
 105  
 106  	public function queryUsers($filter='',$orderBy='',$limit='')
 107      {
 108          if($filter!=='')
 109              $filter='WHERE '.$filter;
 110          $sql="SELECT * FROM tblUsers $filter $orderBy $limit";
 111          $result=$this->query($sql);
 112          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 113          $users=array();
 114          foreach($rows as $row)
 115              $users[]=$this->populateUserRecord($row);
 116          return $users;
 117      }
 118  
 119  	public function queryUserCount($filter)
 120      {
 121          if($filter!=='')
 122              $filter='WHERE '.$filter;
 123          $sql="SELECT COUNT(id) AS user_count FROM tblUsers $filter";
 124          $result=$this->query($sql);
 125          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 126              return $row['user_count'];
 127          else
 128              return 0;
 129      }
 130  
 131  	public function queryUserByID($id)
 132      {
 133          $sql="SELECT * FROM tblUsers WHERE id=$id";
 134          $result=$this->query($sql);
 135          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 136              return $this->populateUserRecord($row);
 137          else
 138              return null;
 139      }
 140  
 141  	public function queryUserByName($name)
 142      {
 143          $name=sqlite_escape_string($name);
 144          $sql="SELECT * FROM tblUsers WHERE name='$name'";
 145          $result=$this->query($sql);
 146          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 147              return $this->populateUserRecord($row);
 148          else
 149              return null;
 150      }
 151  
 152  	public function insertUser($user)
 153      {
 154          $name=sqlite_escape_string($user->Name);
 155          $fullName=sqlite_escape_string($user->FullName);
 156          $passwd=sqlite_escape_string($user->Password);
 157          $email=sqlite_escape_string($user->Email);
 158          $website=sqlite_escape_string($user->Website);
 159          $createTime=time();
 160          $sql="INSERT INTO tblUsers ".
 161                  "(name,full_name,role,passwd,email,reg_time,status,website) ".
 162                  "VALUES ('$name','$fullName',{$user->Role},'$passwd','$email',$createTime,{$user->Status},'$website')";
 163          $this->query($sql);
 164          $user->ID=sqlite_last_insert_rowid($this->_db);
 165      }
 166  
 167  	public function updateUser($user)
 168      {
 169          $name=sqlite_escape_string($user->Name);
 170          $fullName=sqlite_escape_string($user->FullName);
 171          $passwd=sqlite_escape_string($user->Password);
 172          $email=sqlite_escape_string($user->Email);
 173          $website=sqlite_escape_string($user->Website);
 174          $sql="UPDATE tblUsers SET
 175                  name='$name',
 176                  full_name='$fullName',
 177                  role={$user->Role},
 178                  passwd='$passwd',
 179                  vcode='{$user->VerifyCode}',
 180                  email='$email',
 181                  status={$user->Status},
 182                  website='$website'
 183                  WHERE id={$user->ID}";
 184          $this->query($sql);
 185      }
 186  
 187  	public function deleteUser($id)
 188      {
 189          $this->query("DELETE FROM tblUsers WHERE id=$id");
 190      }
 191  
 192  	protected function populatePostRecord($row)
 193      {
 194          $postRecord=new PostRecord;
 195          $postRecord->ID=(integer)$row['id'];
 196          $postRecord->AuthorID=(integer)$row['author_id'];
 197          if($row['author_full_name']!=='')
 198              $postRecord->AuthorName=$row['author_full_name'];
 199          else
 200              $postRecord->AuthorName=$row['author_name'];
 201          $postRecord->CreateTime=(integer)$row['create_time'];
 202          $postRecord->ModifyTime=(integer)$row['modify_time'];
 203          $postRecord->Title=$row['title'];
 204          $postRecord->Content=$row['content'];
 205          $postRecord->Status=(integer)$row['status'];
 206          $postRecord->CommentCount=(integer)$row['comment_count'];
 207          return $postRecord;
 208      }
 209  
 210  	public function queryPosts($postFilter,$categoryFilter,$orderBy,$limit)
 211      {
 212          $filter='';
 213          if($postFilter!=='')
 214              $filter.=" AND $postFilter";
 215          if($categoryFilter!=='')
 216              $filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";
 217          $sql="SELECT a.id AS id,
 218                      a.author_id AS author_id,
 219                      b.name AS author_name,
 220                      b.full_name AS author_full_name,
 221                      a.create_time AS create_time,
 222                      a.modify_time AS modify_time,
 223                      a.title AS title,
 224                      a.content AS content,
 225                      a.status AS status,
 226                      a.comment_count AS comment_count
 227                  FROM tblPosts a, tblUsers b
 228                  WHERE a.author_id=b.id $filter $orderBy $limit";
 229          $result=$this->query($sql);
 230          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 231          $posts=array();
 232          foreach($rows as $row)
 233              $posts[]=$this->populatePostRecord($row);
 234          return $posts;
 235      }
 236  
 237  	public function queryPostCount($postFilter,$categoryFilter)
 238      {
 239          $filter='';
 240          if($postFilter!=='')
 241              $filter.=" AND $postFilter";
 242          if($categoryFilter!=='')
 243              $filter.=" AND a.id IN (SELECT post_id AS id FROM tblPost2Category WHERE $categoryFilter)";
 244          $sql="SELECT COUNT(a.id) AS post_count
 245                  FROM tblPosts a, tblUsers b
 246                  WHERE a.author_id=b.id $filter";
 247          $result=$this->query($sql);
 248          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 249              return $row['post_count'];
 250          else
 251              return 0;
 252      }
 253  
 254  	public function queryPostByID($id)
 255      {
 256          $sql="SELECT a.id AS id,
 257                       a.author_id AS author_id,
 258                       b.name AS author_name,
 259                       b.full_name AS author_full_name,
 260                       a.create_time AS create_time,
 261                       a.modify_time AS modify_time,
 262                       a.title AS title,
 263                       a.content AS content,
 264                       a.status AS status,
 265                       a.comment_count AS comment_count
 266                FROM tblPosts a, tblUsers b
 267                WHERE a.id=$id AND a.author_id=b.id";
 268          $result=$this->query($sql);
 269          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 270              return $this->populatePostRecord($row);
 271          else
 272              return null;
 273      }
 274  
 275  	public function escapeString($string)
 276      {
 277          return sqlite_escape_string($string);
 278      }
 279  
 280  	public function insertPost($post,$catIDs)
 281      {
 282          $title=sqlite_escape_string($post->Title);
 283          $content=sqlite_escape_string($post->Content);
 284          $sql="INSERT INTO tblPosts
 285                  (author_id,create_time,modify_time,title,content,status)
 286                  VALUES ({$post->AuthorID},{$post->CreateTime},{$post->ModifyTime},'$title','$content',{$post->Status})";
 287          $this->query($sql);
 288          $post->ID=sqlite_last_insert_rowid($this->_db);
 289          foreach($catIDs as $catID)
 290              $this->insertPostCategory($post->ID,$catID);
 291      }
 292  
 293  	public function updatePost($post,$newCatIDs=null)
 294      {
 295          if($newCatIDs!==null)
 296          {
 297              $cats=$this->queryCategoriesByPostID($post->ID);
 298              $catIDs=array();
 299              foreach($cats as $cat)
 300                  $catIDs[]=$cat->ID;
 301              $deleteIDs=array_diff($catIDs,$newCatIDs);
 302              foreach($deleteIDs as $id)
 303                  $this->deletePostCategory($post->ID,$id);
 304              $insertIDs=array_diff($newCatIDs,$catIDs);
 305              foreach($insertIDs as $id)
 306                  $this->insertPostCategory($post->ID,$id);
 307          }
 308  
 309          $title=sqlite_escape_string($post->Title);
 310          $content=sqlite_escape_string($post->Content);
 311          $sql="UPDATE tblPosts SET
 312                  modify_time={$post->ModifyTime},
 313                  title='$title',
 314                  content='$content',
 315                  status={$post->Status}
 316                  WHERE id={$post->ID}";
 317          $this->query($sql);
 318      }
 319  
 320  	public function deletePost($id)
 321      {
 322          $cats=$this->queryCategoriesByPostID($id);
 323          foreach($cats as $cat)
 324              $this->deletePostCategory($id,$cat->ID);
 325          $this->query("DELETE FROM tblComments WHERE post_id=$id");
 326          $this->query("DELETE FROM tblPosts WHERE id=$id");
 327      }
 328  
 329  	protected function populateCommentRecord($row)
 330      {
 331          $commentRecord=new CommentRecord;
 332          $commentRecord->ID=(integer)$row['id'];
 333          $commentRecord->PostID=(integer)$row['post_id'];
 334          $commentRecord->AuthorName=$row['author_name'];
 335          $commentRecord->AuthorEmail=$row['author_email'];
 336          $commentRecord->AuthorWebsite=$row['author_website'];
 337          $commentRecord->AuthorIP=$row['author_ip'];
 338          $commentRecord->CreateTime=(integer)$row['create_time'];
 339          $commentRecord->Content=$row['content'];
 340          $commentRecord->Status=(integer)$row['status'];
 341          return $commentRecord;
 342      }
 343  
 344  	public function queryComments($filter,$orderBy,$limit)
 345      {
 346          if($filter!=='')
 347              $filter='WHERE '.$filter;
 348          $sql="SELECT * FROM tblComments $filter $orderBy $limit";
 349          $result=$this->query($sql);
 350          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 351          $comments=array();
 352          foreach($rows as $row)
 353              $comments[]=$this->populateCommentRecord($row);
 354          return $comments;
 355      }
 356  
 357  	public function queryCommentsByPostID($id)
 358      {
 359          $sql="SELECT * FROM tblComments WHERE post_id=$id ORDER BY create_time DESC";
 360          $result=$this->query($sql);
 361          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 362          $comments=array();
 363          foreach($rows as $row)
 364              $comments[]=$this->populateCommentRecord($row);
 365          return $comments;
 366      }
 367  
 368  	public function insertComment($comment)
 369      {
 370          $authorName=sqlite_escape_string($comment->AuthorName);
 371          $authorEmail=sqlite_escape_string($comment->AuthorEmail);
 372          $authorWebsite=sqlite_escape_string($comment->AuthorWebsite);
 373          $content=sqlite_escape_string($comment->Content);
 374          $sql="INSERT INTO tblComments
 375                  (post_id,author_name,author_email,author_website,author_ip,create_time,status,content)
 376                  VALUES ({$comment->PostID},'$authorName','$authorEmail','$authorWebsite','{$comment->AuthorIP}',{$comment->CreateTime},{$comment->Status},'$content')";
 377          $this->query($sql);
 378          $comment->ID=sqlite_last_insert_rowid($this->_db);
 379          $this->query("UPDATE tblPosts SET comment_count=comment_count+1 WHERE id={$comment->PostID}");
 380      }
 381  
 382  	public function updateComment($comment)
 383      {
 384          $authorName=sqlite_escape_string($comment->AuthorName);
 385          $authorEmail=sqlite_escape_string($comment->AuthorEmail);
 386          $content=sqlite_escape_string($comment->Content);
 387          $sql="UPDATE tblComments SET status={$comment->Status} WHERE id={$comment->ID}";
 388          $this->query($sql);
 389      }
 390  
 391  	public function deleteComment($id)
 392      {
 393          $result=$this->query("SELECT post_id FROM tblComments WHERE id=$id");
 394          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 395          {
 396              $postID=$row['post_id'];
 397              $this->query("DELETE FROM tblComments WHERE id=$id");
 398              $this->query("UPDATE tblPosts SET comment_count=comment_count-1 WHERE id=$postID");
 399          }
 400      }
 401  
 402  	protected function populateCategoryRecord($row)
 403      {
 404          $catRecord=new CategoryRecord;
 405          $catRecord->ID=(integer)$row['id'];
 406          $catRecord->Name=$row['name'];
 407          $catRecord->Description=$row['description'];
 408          $catRecord->PostCount=$row['post_count'];
 409          return $catRecord;
 410      }
 411  
 412  	public function queryCategories()
 413      {
 414          $sql="SELECT * FROM tblCategories ORDER BY name ASC";
 415          $result=$this->query($sql);
 416          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 417          $cats=array();
 418          foreach($rows as $row)
 419              $cats[]=$this->populateCategoryRecord($row);
 420          return $cats;
 421      }
 422  
 423  	public function queryCategoriesByPostID($postID)
 424      {
 425          $sql="SELECT a.id AS id,
 426                  a.name AS name,
 427                  a.description AS description,
 428                  a.post_count AS post_count
 429                  FROM tblCategories a, tblPost2Category b
 430                  WHERE a.id=b.category_id AND b.post_id=$postID ORDER BY a.name";
 431          $result=$this->query($sql);
 432          $rows=sqlite_fetch_all($result,SQLITE_ASSOC);
 433          $cats=array();
 434          foreach($rows as $row)
 435              $cats[]=$this->populateCategoryRecord($row);
 436          return $cats;
 437      }
 438  
 439  	public function queryCategoryByID($id)
 440      {
 441          $sql="SELECT * FROM tblCategories WHERE id=$id";
 442          $result=$this->query($sql);
 443          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 444              return $this->populateCategoryRecord($row);
 445          else
 446              return null;
 447      }
 448  
 449  	public function queryCategoryByName($name)
 450      {
 451          $name=sqlite_escape_string($name);
 452          $sql="SELECT * FROM tblCategories WHERE name='$name'";
 453          $result=$this->query($sql);
 454          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 455              return $this->populateCategoryRecord($row);
 456          else
 457              return null;
 458      }
 459  
 460  	public function insertCategory($category)
 461      {
 462          $name=sqlite_escape_string($category->Name);
 463          $description=sqlite_escape_string($category->Description);
 464          $sql="INSERT INTO tblCategories
 465                  (name,description)
 466                  VALUES ('$name','$description')";
 467          $this->query($sql);
 468          $category->ID=sqlite_last_insert_rowid($this->_db);
 469      }
 470  
 471  	public function updateCategory($category)
 472      {
 473          $name=sqlite_escape_string($category->Name);
 474          $description=sqlite_escape_string($category->Description);
 475          $sql="UPDATE tblCategories SET name='$name', description='$description', post_count={$category->PostCount} WHERE id={$category->ID}";
 476          $this->query($sql);
 477      }
 478  
 479  	public function deleteCategory($id)
 480      {
 481          $sql="DELETE FROM tblPost2Category WHERE category_id=$id";
 482          $this->query($sql);
 483          $sql="DELETE FROM tblCategories WHERE id=$id";
 484          $this->query($sql);
 485      }
 486  
 487  	public function insertPostCategory($postID,$categoryID)
 488      {
 489          $sql="INSERT INTO tblPost2Category (post_id, category_id) VALUES ($postID, $categoryID)";
 490          $this->query($sql);
 491          $sql="UPDATE tblCategories SET post_count=post_count+1 WHERE id=$categoryID";
 492          $this->query($sql);
 493      }
 494  
 495  	public function deletePostCategory($postID,$categoryID)
 496      {
 497          $sql="DELETE FROM tblPost2Category WHERE post_id=$postID AND category_id=$categoryID";
 498          if($this->query($sql)>0)
 499          {
 500              $sql="UPDATE tblCategories SET post_count=post_count-1 WHERE id=$categoryID";
 501              $this->query($sql);
 502          }
 503      }
 504  
 505  	public function queryEarliestPostTime()
 506      {
 507          $sql="SELECT MIN(create_time) AS create_time FROM tblPosts";
 508          $result=$this->query($sql);
 509          if(($row=sqlite_fetch_array($result,SQLITE_ASSOC))!==false)
 510              return $row['create_time'];
 511          else
 512              return time();
 513      }
 514  }
 515  
 516  class UserRecord
 517  {
 518      const ROLE_USER=0;
 519      const ROLE_ADMIN=1;
 520      const STATUS_NORMAL=0;
 521      const STATUS_DISABLED=1;
 522      const STATUS_PENDING=2;
 523      public $ID;
 524      public $Name;
 525      public $FullName;
 526      public $Role;
 527      public $Password;
 528      public $VerifyCode;
 529      public $Email;
 530      public $CreateTime;
 531      public $Status;
 532      public $Website;
 533  }
 534  
 535  class PostRecord
 536  {
 537      const STATUS_PUBLISHED=0;
 538      const STATUS_DRAFT=1;
 539      const STATUS_PENDING=2;
 540      const STATUS_STICKY=3;
 541      public $ID;
 542      public $AuthorID;
 543      public $AuthorName;
 544      public $CreateTime;
 545      public $ModifyTime;
 546      public $Title;
 547      public $Content;
 548      public $Status;
 549      public $CommentCount;
 550  }
 551  
 552  class CommentRecord
 553  {
 554      public $ID;
 555      public $PostID;
 556      public $AuthorName;
 557      public $AuthorEmail;
 558      public $AuthorWebsite;
 559      public $AuthorIP;
 560      public $CreateTime;
 561      public $Status;
 562      public $Content;
 563  }
 564  
 565  class CategoryRecord
 566  {
 567      public $ID;
 568      public $Name;
 569      public $Description;
 570      public $PostCount;
 571  }
 572  
 573  ?>