3 * PIO SQLite3 (PDO) API
\r
5 * 提供存取以 SQLite3 資料庫構成的資料結構後端的物件 (需要 PHP 5.1.0 以上並開啟 PDO 功能)
\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
13 private $ENV, $DSN, $tablename; // Local Constant
\r
14 private $con, $prepared, $useTransaction; // Local Global
\r
16 public function __construct($connstr='', $ENV){
\r
18 $this->prepared = false;
\r
19 if($connstr) $this->dbConnect($connstr);
\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
31 function pioVersion(){
\r
32 return '0.6 (v20100404)';
\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
47 public function dbInit($isAddInitData=true){
\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
85 public function dbPrepare($transaction=false){
\r
86 if($this->prepared) return true;
\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
92 $this->prepared = true;
\r
96 public function dbCommit(){
\r
97 if(!$this->prepared) return false;
\r
98 if($this->useTransaction) @$this->con->commit(); // 交易性能模式提交
\r
102 public function dbMaintanence($action, $doit=false){
\r
106 $this->dbPrepare(false);
\r
107 if($this->con->exec('VACUUM '.$this->tablename)!==false) return true;
\r
109 }else return true; // 支援最佳化資料表
\r
113 $this->dbPrepare(false);
\r
114 $gp = gzopen('piodata.log.gz', 'w9');
\r
115 gzwrite($gp, $this->dbExport());
\r
117 return '<a href="piodata.log.gz">下載 piodata.log.gz 中介檔案</a>';
\r
118 }else return true; // 支援匯出資料
\r
122 default: return false; // 不支援
\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(",", ",", $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])); // 取代 , 為 ,
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
162 $this->dbCommit(); // 送交
\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
171 $replaceComma = create_function('$txt', 'return str_replace(",", ",", $txt);');
\r
172 while($row = $line->fetch(PDO::FETCH_ASSOC)){
\r
173 $row = array_map($replaceComma, $row); // 取代 , 為 ,
\r
174 $data .= implode(',', $row).",\r\n";
\r
180 public function postCount($resno=0){
\r
181 if(!$this->prepared) $this->dbPrepare();
\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
187 $line = $this->con->query('SELECT COUNT(no) FROM '.$this->tablename)->fetch();
\r
188 $countline = $line[0];
\r
194 public function threadCount(){
\r
195 if(!$this->prepared) $this->dbPrepare();
\r
197 $tree = $this->con->query('SELECT COUNT(no) FROM '.$this->tablename.' WHERE resto = 0')->fetch();
\r
198 return $tree[0]; // 討論串目前數目
\r
202 public function getLastPostNo($state){
\r
203 if(!$this->prepared) $this->dbPrepare();
\r
205 if($state=='afterCommit'){ // 送出後的最後文章編號
\r
206 $lastno = $this->con->query('SELECT MAX(no) FROM '.$this->tablename)->fetch();
\r
208 }else return 0; // 其他狀態沒用
\r
212 public function fetchPostList($resno=0, $start=0, $amount=0){
\r
213 if(!$this->prepared) $this->dbPrepare();
\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
223 return $this->con->query($tmpSQL)->fetchAll(PDO::FETCH_COLUMN, 0);
\r
227 public function fetchThreadList($start=0, $amount=0, $isDESC=false) {
\r
228 if(!$this->prepared) $this->dbPrepare();
\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
237 public function fetchPosts($postlist){
\r
238 if(!$this->prepared) $this->dbPrepare();
\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
249 /* 刪除舊附件 (輸出附件清單) */
\r
250 public function delOldAttachments($total_size, $storage_max, $warnOnly=true){
\r
252 if(!$this->prepared) $this->dbPrepare();
\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
262 return $warnOnly ? $arr_warn : $this->removeAttachments($arr_kill);
\r
266 public function removePosts($posts){
\r
267 if(!$this->prepared) $this->dbPrepare();
\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
275 /* 刪除附件 (輸出附件清單) */
\r
276 public function removeAttachments($posts, $recursion=false){
\r
278 if(!$this->prepared) $this->dbPrepare();
\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
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
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
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
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
306 }else $root = $updatetime; // 新增討論串, 討論串最後被更新時間
\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
336 public function isSuccessivePost($lcount, $com, $timestamp, $pass, $passcookie, $host, $isupload){
\r
338 if(!$this->prepared) $this->dbPrepare();
\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
355 public function isDuplicateAttachment($lcount, $md5hash){
\r
357 if(!$this->prepared) $this->dbPrepare();
\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
368 public function isThread($no){
\r
369 if(!$this->prepared) $this->dbPrepare();
\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
376 public function searchPost($keyword, $field, $method){
\r
377 if(!$this->prepared) $this->dbPrepare();
\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
388 public function searchCategory($category){
\r
389 if(!$this->prepared) $this->dbPrepare();
\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
397 public function getPostStatus($status){
\r
398 return new FlagHelper($status); // 回傳 FlagHelper 物件
\r
402 public function updatePost($no, $newValues){
\r
403 if(!$this->prepared) $this->dbPrepare();
\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
416 public function setPostStatus($no, $newStatus){
\r
417 $this->updatePost($no, array('status' => $newStatus));
\r