lib/pio/pio.pgsql.php

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