lib/pio/pio.sqlite3.php

   1 <?php\r
   2 /**\r
   3  * PIO SQLite3 (PDO) API\r
   4  *\r
   5  * 提供存取以 SQLite3 資料庫構成的資料結構後端的物件 (需要 PHP 5.1.0 以上並開啟 PDO 功能)\r
   6  *\r
   7  * @package PMCLibrary\r
   8  * @version $Id: pio.sqlite3.php 658 2008-08-06 15:59:17Z scribe $\r
   9  * @date $Date: 2008-08-06 23:59:17 +0800 (星期三, 06 八月 2008) $\r
  10  */\r
  11 \r
  12 class PIOsqlite3{\r
  13         private $ENV, $DSN, $tablename; // Local Constant\r
  14         private $con, $prepared, $useTransaction; // Local Global\r
  15 \r
  16         public function __construct($connstr='', $ENV){\r
  17                 $this->ENV = $ENV;\r
  18                 $this->prepared = false;\r
  19                 if($connstr) $this->dbConnect($connstr);\r
  20         }\r
  21 \r
  22         /* private 攔截SQL錯誤 */\r
  23         private function _error_handler($errtext, $errline){\r
  24                 $err = "Pixmicat! SQL Error: $errtext on line $errline";\r
  25                 //error_log($err."\n".print_r($this->con->errorInfo(),true)."\n", 3, 'error.log');\r
  26                 trigger_error($err, E_USER_ERROR);\r
  27                 exit();\r
  28         }\r
  29 \r
  30         /* PIO模組版本 */\r
  31         function pioVersion(){\r
  32                 return '0.6 (v20100404)';\r
  33         }\r
  34 \r
  35         /* 處理連線字串/連接 */\r
  36         public function dbConnect($connStr){\r
  37                 // 格式： sqlite3://資料庫檔案之位置/資料表/\r
  38                 // 示例： sqlite://yotsubanome.db/img_loog/\r
  39                 // 　　　 sqlite3://:memory:/img_loog/\r
  40                 if(preg_match('/^sqlite3:\/\/(.*)\/(.*)\/$/i', $connStr, $linkinfos)){\r
  41                         $this->DSN = 'sqlite:'.$linkinfos[1];\r
  42                         $this->tablename = $linkinfos[2];\r
  43                 }\r
  44         }\r
  45 \r
  46         /* 初始化 */\r
  47         public function dbInit($isAddInitData=true){\r
  48                 $this->dbPrepare();\r
  49                 $nline = $this->con->query('SELECT COUNT(name) FROM sqlite_master WHERE name LIKE "'.$this->tablename.'"')->fetch();\r
  50                 if($nline[0]==='0'){ // 資料表不存在\r
  51                         $result = 'CREATE TABLE '.$this->tablename.' (\r
  52         "no" INTEGER  NOT NULL PRIMARY KEY,\r
  53         "resto" INTEGER  NOT NULL,\r
  54         "root" TIMESTAMP DEFAULT \'0\' NOT NULL,\r
  55         "time" INTEGER  NOT NULL,\r
  56         "md5chksum" VARCHAR(32)  NOT NULL,\r
  57         "category" VARCHAR(255)  NOT NULL,\r
  58         "tim" INTEGER  NOT NULL,\r
  59         "ext" VARCHAR(4)  NOT NULL,\r
  60         "imgw" INTEGER  NOT NULL,\r
  61         "imgh" INTEGER  NOT NULL,\r
  62         "imgsize" VARCHAR(10)  NOT NULL,
  63         "filename" VARCHAR(255)  NOT NULL,\r
  64         "tw" INTEGER  NOT NULL,\r
  65         "th" INTEGER  NOT NULL,\r
  66         "pwd" VARCHAR(8)  NOT NULL,\r
  67         "now" VARCHAR(255)  NOT NULL,\r
  68         "name" VARCHAR(255)  NOT NULL,\r
  69         "email" VARCHAR(255)  NOT NULL,\r
  70         "sub" VARCHAR(255)  NOT NULL,\r
  71         "com" TEXT  NOT NULL,\r
  72         "host" VARCHAR(255)  NOT NULL,\r
  73         "status" VARCHAR(255)  NOT NULL\r
  74         );'; // PIO Structure V4\r
  75                         $idx = array('resto', 'root', 'time');\r
  76                         foreach($idx as $x) $result .= 'CREATE INDEX IDX_'.$this->tablename.'_'.$x.' ON '.$this->tablename.'('.$x.');';\r
  77                         $result .= 'CREATE INDEX IDX_'.$this->tablename.'_resto_no ON '.$this->tablename.'(resto,no);';\r
  78                         if($isAddInitData) $result .= 'INSERT INTO '.$this->tablename.' (no,resto,root,time,md5chksum,category,tim,ext,imgw,imgh,imgsize,filename,tw,th,pwd,now,name,email,sub,com,host,status) VALUES (0, 0, datetime("now"), 1111111111, "", "", 1111111111111, "", 0, 0, "", "", 0, 0, "", "08/11/08(土)10:24:04", "【スパーキー（④ ^ヮ^）】", "", "'.$this->ENV['NOTITLE'].'", "'.$this->ENV['NOCOMMENT'].'", "", "");';\r
  79                         $this->con->exec($result);\r
  80                         $this->dbCommit();\r
  81                 }\r
  82         }\r
  83 \r
  84         /* 準備/讀入 */\r
  85         public function dbPrepare($transaction=false){\r
  86                 if($this->prepared) return true;\r
  87 \r
  88                 ($this->con = new PDO($this->DSN, '', '', array(PDO::ATTR_PERSISTENT => true))) or $this->_error_handler('Open database failed', __LINE__);\r
  89                 $this->useTransaction = $transaction;\r
  90                 if($transaction) @$this->con->beginTransaction(); // 啟動交易性能模式\r
  91 \r
  92                 $this->prepared = true;\r
  93         }\r
  94 \r
  95         /* 提交/儲存 */\r
  96         public function dbCommit(){\r
  97                 if(!$this->prepared) return false;\r
  98                 if($this->useTransaction) @$this->con->commit(); // 交易性能模式提交\r
  99         }\r
 100 \r
 101         /* 資料表維護 */\r
 102         public function dbMaintanence($action, $doit=false){\r
 103                 switch($action) {\r
 104                         case 'optimize':\r
 105                                 if($doit){\r
 106                                         $this->dbPrepare(false);\r
 107                                         if($this->con->exec('VACUUM '.$this->tablename)!==false) return true;\r
 108                                         else return false;\r
 109                                 }else return true; // 支援最佳化資料表\r
 110                                 break;\r
 111                         case 'export':\r
 112                                 if($doit){\r
 113                                         $this->dbPrepare(false);\r
 114                                         $gp = gzopen('piodata.log.gz', 'w9');\r
 115                                         gzwrite($gp, $this->dbExport());\r
 116                                         gzclose($gp);\r
 117                                         return '<a href="piodata.log.gz">下載 piodata.log.gz 中介檔案</a>';\r
 118                                 }else return true; // 支援匯出資料\r
 119                                 break;\r
 120                         case 'check':\r
 121                         case 'repair':\r
 122                         default: return false; // 不支援\r
 123                 }\r
 124         }\r
 125 \r
 126         /* 匯入資料來源 */\r
 127         public function dbImport($data){\r
 128                 $this->dbInit(false); // 僅新增結構不新增資料\r
 129                 $data = explode("\r\n", $data);\r
 130                 $data_count = count($data) - 1;\r
 131                 $replaceComma = create_function('$txt', 'return str_replace("&#44;", ",", $txt);');\r
 132                 $SQL = 'INSERT INTO '.$this->tablename.' (no,resto,root,time,md5chksum,category,tim,ext,imgw,imgh,imgsize,filename,tw,th,pwd,now,name,email,sub,com,host,status) VALUES '\r
 133                                 .'(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';\r
 134                 $PDOStmt = $this->con->prepare($SQL);\r
 135                 for($i = 0; $i < $data_count; $i++){\r
 136                         $line = array_map($replaceComma, explode(',', $data[$i])); // 取代 &#44; 為 ,
 137                         $tim = substr($line[5], 0, 10);\r
 138                         $PDOStmt->bindValue(1, $line[0], PDO::PARAM_INT);\r
 139                         $PDOStmt->bindValue(2, $line[1], PDO::PARAM_INT);\r
 140                         $PDOStmt->bindValue(3, $line[2], PDO::PARAM_STR);\r
 141                         $PDOStmt->bindValue(4, $tim, PDO::PARAM_INT);\r
 142                         $PDOStmt->bindValue(5, $line[3], PDO::PARAM_STR);\r
 143                         $PDOStmt->bindValue(6, $line[4], PDO::PARAM_STR);\r
 144                         $PDOStmt->bindValue(7, $line[5], PDO::PARAM_INT);\r
 145                         $PDOStmt->bindValue(8, $line[6], PDO::PARAM_STR);\r
 146                         $PDOStmt->bindValue(9, $line[7], PDO::PARAM_INT);\r
 147                         $PDOStmt->bindValue(10, $line[8], PDO::PARAM_INT);\r
 148                         $PDOStmt->bindValue(11, $line[9], PDO::PARAM_STR);
 149                         $PDOStmt->bindValue(12, $line[10], PDO::PARAM_STR);\r
 150                         $PDOStmt->bindValue(13, $line[11], PDO::PARAM_INT);\r
 151                         $PDOStmt->bindValue(14, $line[12], PDO::PARAM_INT);\r
 152                         $PDOStmt->bindValue(15, $line[13], PDO::PARAM_STR);\r
 153                         $PDOStmt->bindValue(16, $line[14], PDO::PARAM_STR);\r
 154                         $PDOStmt->bindValue(17, $line[15], PDO::PARAM_STR);\r
 155                         $PDOStmt->bindValue(18, $line[16], PDO::PARAM_STR);\r
 156                         $PDOStmt->bindValue(19, $line[17], PDO::PARAM_STR);\r
 157                         $PDOStmt->bindValue(20, $line[18], PDO::PARAM_STR);\r
 158                         $PDOStmt->bindValue(21, $line[19], PDO::PARAM_STR);\r
 159                         $PDOStmt->bindValue(22, $line[20], PDO::PARAM_STR);\r
 160                         $PDOStmt->execute() or $this->_error_handler('Insert a new post failed', __LINE__);\r
 161                 }\r
 162                 $this->dbCommit(); // 送交\r
 163                 return true;\r
 164         }\r
 165 \r
 166         /* 匯出資料來源 */\r
 167         public function dbExport(){\r
 168                 if(!$this->prepared) $this->dbPrepare();\r
 169                 $line = $this->con->query('SELECT no,resto,root,md5chksum,category,tim,ext,imgw,imgh,imgsize,filename,tw,th,pwd,now,name,email,sub,com,host,status FROM '.$this->tablename.' ORDER BY no DESC');\r
 170                 $data = '';\r
 171                 $replaceComma = create_function('$txt', 'return str_replace(",", "&#44;", $txt);');\r
 172                 while($row = $line->fetch(PDO::FETCH_ASSOC)){\r
 173                         $row = array_map($replaceComma, $row); // 取代 , 為 &#44;\r
 174                         $data .= implode(',', $row).",\r\n";\r
 175                 }\r
 176                 return $data;\r
 177         }\r
 178 \r
 179         /* 文章數目 */\r
 180         public function postCount($resno=0){\r
 181                 if(!$this->prepared) $this->dbPrepare();\r
 182 \r
 183                 if($resno){ // 一討論串文章總數目\r
 184                         $line = $this->con->query('SELECT COUNT(no) FROM '.$this->tablename.' WHERE resto = '.intval($resno))->fetch();\r
 185                         $countline = $line[0] + 1;\r
 186                 }else{ // 文章總數目\r
 187                         $line = $this->con->query('SELECT COUNT(no) FROM '.$this->tablename)->fetch();\r
 188                         $countline = $line[0];\r
 189                 }\r
 190                 return $countline;\r
 191         }\r
 192 \r
 193         /* 討論串數目 */\r
 194         public function threadCount(){\r
 195                 if(!$this->prepared) $this->dbPrepare();\r
 196 \r
 197                 $tree = $this->con->query('SELECT COUNT(no) FROM '.$this->tablename.' WHERE resto = 0')->fetch();\r
 198                 return $tree[0]; // 討論串目前數目\r
 199         }\r
 200 \r
 201         /* 取得最後文章編號 */\r
 202         public function getLastPostNo($state){\r
 203                 if(!$this->prepared) $this->dbPrepare();\r
 204 \r
 205                 if($state=='afterCommit'){ // 送出後的最後文章編號\r
 206                         $lastno = $this->con->query('SELECT MAX(no) FROM '.$this->tablename)->fetch();\r
 207                         return $lastno[0];\r
 208                 }else return 0; // 其他狀態沒用\r
 209         }\r
 210 \r
 211         /* 輸出文章清單 */\r
 212         public function fetchPostList($resno=0, $start=0, $amount=0){\r
 213                 if(!$this->prepared) $this->dbPrepare();\r
 214 \r
 215                 $resno = intval($resno);\r
 216                 if($resno){ // 輸出討論串的結構 (含自己, EX : 1,2,3,4,5,6)\r
 217                         $tmpSQL = 'SELECT no FROM '.$this->tablename.' WHERE no = '.$resno.' OR resto = '.$resno.' ORDER BY no';\r
 218                 }else{ // 輸出所有文章編號，新的在前\r
 219                         $tmpSQL = 'SELECT no FROM '.$this->tablename.' ORDER BY no DESC';\r
 220                         $start = intval($start); $amount = intval($amount);\r
 221                         if($amount) $tmpSQL .= " LIMIT {$start}, {$amount}"; // 指定數量\r
 222                 }\r
 223                 return $this->con->query($tmpSQL)->fetchAll(PDO::FETCH_COLUMN, 0);\r
 224         }\r
 225 \r
 226         /* 輸出討論串清單 */\r
 227         public function fetchThreadList($start=0, $amount=0, $isDESC=false) {\r
 228                 if(!$this->prepared) $this->dbPrepare();\r
 229 \r
 230                 $tmpSQL = 'SELECT no FROM '.$this->tablename.' WHERE resto = 0 ORDER BY '.($isDESC ? 'no' : 'root').' DESC';\r
 231                 $start = intval($start); $amount = intval($amount);\r
 232                 if($amount) $tmpSQL .= " LIMIT {$start}, {$amount}"; // 指定數量\r
 233                 return $this->con->query($tmpSQL)->fetchAll(PDO::FETCH_COLUMN, 0);\r
 234         }\r
 235 \r
 236         /* 輸出文章 */\r
 237         public function fetchPosts($postlist){\r
 238                 if(!$this->prepared) $this->dbPrepare();\r
 239 \r
 240                 if(is_array($postlist)){ // 取多串\r
 241                         $pno = implode(', ', $postlist); // ID字串\r
 242                         $tmpSQL = 'SELECT * FROM '.$this->tablename.' WHERE no IN ('.$pno.') ORDER BY no';\r
 243                         if(count($postlist) > 1){ if($postlist[0] > $postlist[1]) $tmpSQL .= ' DESC'; } // 由大排到小\r
 244                 }else $tmpSQL = 'SELECT * FROM '.$this->tablename.' WHERE no = '.intval($postlist); // 取單串\r
 245                 $line = $this->con->query($tmpSQL)->fetchAll();\r
 246                 return $line;\r
 247         }\r
 248 \r
 249         /* 刪除舊附件 (輸出附件清單) */\r
 250         public function delOldAttachments($total_size, $storage_max, $warnOnly=true){\r
 251                 global $FileIO;\r
 252                 if(!$this->prepared) $this->dbPrepare();\r
 253 \r
 254                 $arr_warn = $arr_kill = array(); // 警告 / 即將被刪除標記\r
 255                 ($result = $this->con->query('SELECT no,ext,tim FROM '.$this->tablename.' WHERE ext <> "" ORDER BY no')) or $this->_error_handler('Get the old post failed', __LINE__);\r
 256                 while(list($dno, $dext, $dtim) = $result->fetch(PDO::FETCH_NUM)){\r
 257                         $dfile = $dtim.$dext; $dthumb = $dtim.'s.jpg';\r
 258                         if($FileIO->imageExists($dfile)){ $total_size -= $FileIO->getImageFilesize($dfile) / 1024; $arr_kill[] = $dno; $arr_warn[$dno] = 1; } // 標記刪除\r
 259                         if($FileIO->imageExists($dthumb)) $total_size -= $FileIO->getImageFilesize($dthumb) / 1024;\r
 260                         if($total_size < $storage_max) break;\r
 261                 }\r
 262                 return $warnOnly ? $arr_warn : $this->removeAttachments($arr_kill);\r
 263         }\r
 264 \r
 265         /* 刪除文章 */\r
 266         public function removePosts($posts){\r
 267                 if(!$this->prepared) $this->dbPrepare();\r
 268 \r
 269                 $files = $this->removeAttachments($posts, true); // 先遞迴取得刪除文章及其回應附件清單\r
 270                 $pno = implode(', ', $posts); // ID字串\r
 271                 if(!$this->con->exec('DELETE FROM '.$this->tablename.' WHERE no IN ('.$pno.') OR resto IN('.$pno.')')) $this->_error_handler('Delete old posts and replies failed', __LINE__);\r
 272                 return $files;\r
 273         }\r
 274 \r
 275         /* 刪除附件 (輸出附件清單) */\r
 276         public function removeAttachments($posts, $recursion=false){\r
 277                 global $FileIO;\r
 278                 if(!$this->prepared) $this->dbPrepare();\r
 279 \r
 280                 $files = array();\r
 281                 $pno = implode(', ', $posts); // ID字串\r
 282                 if($recursion) $tmpSQL = 'SELECT ext,tim FROM '.$this->tablename.' WHERE (no IN ('.$pno.') OR resto IN('.$pno.")) AND ext <> ''"; // 遞迴取出 (含回應附件)\r
 283                 else $tmpSQL = 'SELECT ext,tim FROM '.$this->tablename.' WHERE no IN ('.$pno.") AND ext <> ''"; // 只有指定的編號\r
 284 \r
 285                 ($result = $this->con->query($tmpSQL)) or $this->_error_handler('Get attachments of the post failed', __LINE__);\r
 286                 while(list($dext, $dtim) = $result->fetch(PDO::FETCH_NUM)){\r
 287                         $dfile = $dtim.$dext; $dthumb = $dtim.'s.jpg';\r
 288                         if($FileIO->imageExists($dfile)) $files[] = $dfile;\r
 289                         if($FileIO->imageExists($dthumb)) $files[] = $dthumb;\r
 290                 }\r
 291                 return $files;\r
 292         }\r
 293 \r
 294         /* 新增文章/討論串 */\r
 295         public function addPost($no, $resto, $md5chksum, $category, $tim, $ext, $imgw, $imgh, $imgsize, $filename, $tw, $th, $pwd, $now, $name, $email, $sub, $com, $host, $age=false, $status='') {\r
 296                 if(!$this->prepared) $this->dbPrepare();\r
 297 \r
 298                 $time = (int)substr($tim, 0, -3); // 13位數的數字串是檔名，10位數的才是時間數值\r
 299                 $updatetime = gmdate('Y-m-d H:i:s'); // 更動時間 (UTC)\r
 300                 if($resto){ // 新增回應\r
 301                         $root = '0';\r
 302                         if($age){ // 推文\r
 303                                 $result = $this->con->prepare('UPDATE '.$this->tablename.' SET root = :now WHERE no = :resto');\r
 304                                 $result->execute(array(':now' => $updatetime, ':resto' => $resto)) or $this->_error_handler('Push the post failed', __LINE__);\r
 305                         }\r
 306                 }else $root = $updatetime; // 新增討論串, 討論串最後被更新時間\r
 307 \r
 308                 $SQL = 'INSERT INTO '.$this->tablename.' (resto,root,time,md5chksum,category,tim,ext,imgw,imgh,imgsize,filename,tw,th,pwd,now,name,email,sub,com,host,status) VALUES '\r
 309                                 .'(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';\r
 310                 $PDOStmt = $this->con->prepare($SQL);\r
 311                 $PDOStmt->bindValue(1, $resto, PDO::PARAM_INT);\r
 312                 $PDOStmt->bindValue(2, $root, PDO::PARAM_STR);\r
 313                 $PDOStmt->bindValue(3, $time, PDO::PARAM_INT);\r
 314                 $PDOStmt->bindValue(4, $md5chksum, PDO::PARAM_STR);\r
 315                 $PDOStmt->bindValue(5, $category, PDO::PARAM_STR);\r
 316                 $PDOStmt->bindValue(6, $tim, PDO::PARAM_INT);\r
 317                 $PDOStmt->bindValue(7, $ext, PDO::PARAM_STR);\r
 318                 $PDOStmt->bindValue(8, $imgw, PDO::PARAM_INT);\r
 319                 $PDOStmt->bindValue(9, $imgh, PDO::PARAM_INT);\r
 320                 $PDOStmt->bindValue(10, $imgsize, PDO::PARAM_STR);
 321                 $PDOStmt->bindValue(11, $filename, PDO::PARAM_STR);\r
 322                 $PDOStmt->bindValue(12, $tw, PDO::PARAM_INT);\r
 323                 $PDOStmt->bindValue(13, $th, PDO::PARAM_INT);\r
 324                 $PDOStmt->bindValue(14, $pwd, PDO::PARAM_STR);\r
 325                 $PDOStmt->bindValue(15, $now, PDO::PARAM_STR);\r
 326                 $PDOStmt->bindValue(16, $name, PDO::PARAM_STR);\r
 327                 $PDOStmt->bindValue(17, $email, PDO::PARAM_STR);\r
 328                 $PDOStmt->bindValue(18, $sub, PDO::PARAM_STR);\r
 329                 $PDOStmt->bindValue(19, $com, PDO::PARAM_STR);\r
 330                 $PDOStmt->bindValue(20, $host, PDO::PARAM_STR);\r
 331                 $PDOStmt->bindValue(21, $status, PDO::PARAM_STR);\r
 332                 $PDOStmt->execute() or $this->_error_handler('Insert a new post failed', __LINE__);\r
 333         }\r
 334 \r
 335         /* 檢查是否連續投稿 */\r
 336         public function isSuccessivePost($lcount, $com, $timestamp, $pass, $passcookie, $host, $isupload){\r
 337                 global $FileIO;\r
 338                 if(!$this->prepared) $this->dbPrepare();\r
 339 \r
 340                 if(!$this->ENV['PERIOD.POST']) return false; // 關閉連續投稿檢查\r
 341                 $timestamp = intval($timestamp);\r
 342                 $tmpSQL = 'SELECT pwd,host FROM '.$this->tablename.' WHERE time > '.($timestamp - (int)$this->ENV['PERIOD.POST']); // 一般投稿時間檢查\r
 343                 if($isupload) $tmpSQL .= ' OR time > '.($timestamp - (int)$this->ENV['PERIOD.IMAGEPOST']); // 附加圖檔的投稿時間檢查 (與下者兩者擇一)\r
 344                 else $tmpSQL .= " OR md5(com) = '".md5($com)."'"; // 內文一樣的檢查 (與上者兩者擇一)\r
 345                 $this->con->sqliteCreateFunction('md5', 'md5', 1); // Register MD5 function\r
 346                 ($result = $this->con->query($tmpSQL)) or $this->_error_handler('Get the post to check the succession failed', __LINE__);\r
 347                 while(list($lpwd, $lhost) = $result->fetch(PDO::FETCH_NUM)){\r
 348                         // 判斷為同一人發文且符合連續投稿條件\r
 349                         if($host==$lhost || $pass==$lpwd || $passcookie==$lpwd) return true;\r
 350                 }\r
 351                 return false;\r
 352         }\r
 353 \r
 354         /* 檢查是否重複貼圖 */\r
 355         public function isDuplicateAttachment($lcount, $md5hash){\r
 356                 global $FileIO;\r
 357                 if(!$this->prepared) $this->dbPrepare();\r
 358 \r
 359                 ($result = $this->con->query('SELECT tim,ext FROM '.$this->tablename.' WHERE ext <> "" AND md5chksum = "'.$md5hash.'" ORDER BY no DESC'))\r
 360                         or $this->_error_handler('Get the post to check the duplicate attachment failed', __LINE__);\r
 361                 while(list($ltim, $lext) = $result->fetch(PDO::FETCH_NUM)){\r
 362                         if($FileIO->imageExists($ltim.$lext)) return true; // 有相同檔案\r
 363                 }\r
 364                 return false;\r
 365         }\r
 366 \r
 367         /* 有此討論串? */\r
 368         public function isThread($no){\r
 369                 if(!$this->prepared) $this->dbPrepare();\r
 370 \r
 371                 $result = $this->con->query('SELECT no FROM '.$this->tablename.' WHERE no = '.intval($no).' AND resto = 0');\r
 372                 return $result->fetch() ? true : false;\r
 373         }\r
 374 \r
 375         /* 搜尋文章 */\r
 376         public function searchPost($keyword, $field, $method){\r
 377                 if(!$this->prepared) $this->dbPrepare();\r
 378 \r
 379                 $keyword_cnt = count($keyword);\r
 380                 $SearchQuery = 'SELECT * FROM '.$this->tablename." WHERE {$field} LIKE ".$this->con->quote('%'.$keyword[0].'%')."";\r
 381                 if($keyword_cnt > 1) for($i = 1; $i < $keyword_cnt; $i++) $SearchQuery .= " {$method} {$field} LIKE ".$this->con->quote('%'.$keyword[$i].'%'); // 多重字串交集 / 聯集搜尋\r
 382                 $SearchQuery .= ' ORDER BY no DESC'; // 按照號碼大小排序\r
 383                 ($line = $this->con->query($SearchQuery)) or $this->_error_handler('Search the post failed', __LINE__);\r
 384                 return $line->fetchAll();\r
 385         }\r
 386 \r
 387         /* 搜尋類別標籤 */\r
 388         public function searchCategory($category){\r
 389                 if(!$this->prepared) $this->dbPrepare();\r
 390 \r
 391                 $result = $this->con->prepare('SELECT no FROM '.$this->tablename.' WHERE lower(category) LIKE :category ORDER BY no DESC');\r
 392                 $result->execute(array(':category' => '%,'.strtolower($category).',%'));\r
 393                 return $result->fetchAll(PDO::FETCH_COLUMN, 0);\r
 394         }\r
 395 \r
 396         /* 取得文章屬性 */\r
 397         public function getPostStatus($status){\r
 398                 return new FlagHelper($status); // 回傳 FlagHelper 物件\r
 399         }\r
 400 \r
 401         /* 更新文章 */\r
 402         public function updatePost($no, $newValues){\r
 403                 if(!$this->prepared) $this->dbPrepare();\r
 404 \r
 405                 $no = intval($no);\r
 406                 $chk = array('resto', 'md5chksum', 'category', 'tim', 'ext', 'imgw', 'imgh', 'imgsize', 'filename', 'tw', 'th', 'pwd', 'now', 'name', 'email', 'sub', 'com', 'host', 'status');\r
 407                 foreach($chk as $c){\r
 408                         if(isset($newValues[$c])){\r
 409                                 if(!$this->con->exec('UPDATE '.$this->tablename." SET $c = ".$this->con->quote($newValues[$c]).' WHERE no = '.$no))\r
 410                                         $this->_error_handler('Update the field of the post failed', __LINE__); // 更新討論串屬性\r
 411                         }\r
 412                 }\r
 413         }\r
 414 \r
 415         /* 設定文章屬性 */\r
 416         public function setPostStatus($no, $newStatus){\r
 417                 $this->updatePost($no, array('status' => $newStatus));\r
 418         }\r
 419 }\r
 420 ?>