lib/pio/pio.sqlite.php

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