5 * 提供存取以 SQLite 資料庫構成的資料結構後端的物件
\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
13 var $ENV, $dbname, $tablename; // Local Constant
\r
14 var $con, $prepared, $useTransaction; // Local Global
\r
16 function PIOsqlite($connstr='', $ENV){
\r
18 $this->prepared = 0;
\r
19 if($connstr) $this->dbConnect($connstr);
\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
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
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
44 function pioVersion(){
\r
45 return '0.6 (v20100404)';
\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
59 function dbInit($isAddInitData=true){
\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
90 $result .= 'CREATE INDEX IDX_'.$this->tablename.'_resto_no ON '.$this->tablename.'(resto,no);';
\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
99 function dbPrepare($transaction=true){
\r
100 if($this->prepared) return true;
\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
106 $this->prepared = 1;
\r
110 function dbCommit(){
\r
111 if(!$this->prepared) return false;
\r
112 if($this->useTransaction) @sqlite_exec($this->con, 'COMMIT;'); // 交易性能模式提交
\r
116 function dbMaintanence($action, $doit=false){
\r
120 $this->dbPrepare(false);
\r
121 if($this->_sqlite_call('VACUUM '.$this->tablename)) return true;
\r
123 }else return true; // 支援最佳化資料表
\r
127 $this->dbPrepare(false);
\r
128 $gp = gzopen('piodata.log.gz', 'w9');
\r
129 gzwrite($gp, $this->dbExport());
\r
131 return '<a href="piodata.log.gz">下載 piodata.log.gz 中介檔案</a>';
\r
132 }else return true; // 支援匯出資料
\r
136 default: return false; // 不支援
\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(",", ",", $txt);');
\r
146 for($i = 0; $i < $data_count; $i++){
\r
147 $line = array_map($replaceComma, explode(',', $data[$i])); // 取代 , 為 ,
\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
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
167 $this->dbCommit(); // 送交
\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
177 $replaceComma = create_function('$txt', 'return str_replace(",", ",", $txt);');
\r
178 while($row = sqlite_fetch_array($line, SQLITE_ASSOC)){
\r
179 $row = array_map($replaceComma, $row); // 取代 , 為 ,
\r
180 $data .= implode(',', $row).",\r\n";
\r
186 function postCount($resno=0){
\r
187 if(!$this->prepared) $this->dbPrepare();
\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
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
201 function threadCount(){
\r
202 if(!$this->prepared) $this->dbPrepare();
\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
211 function getLastPostNo($state){
\r
212 if(!$this->prepared) $this->dbPrepare();
\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
218 }else return 0; // 其他狀態沒用
\r
222 function fetchPostList($resno=0, $start=0, $amount=0){
\r
223 if(!$this->prepared) $this->dbPrepare();
\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
234 $tree = $this->_sqlite_call($tmpSQL, array('Fetch post list failed', __LINE__));
\r
235 while($rows = sqlite_fetch_array($tree)) $line[] = $rows[0]; // 迴圈
\r
240 function fetchThreadList($start=0, $amount=0, $isDESC=false) {
\r
241 if(!$this->prepared) $this->dbPrepare();
\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
253 function fetchPosts($postlist){
\r
254 if(!$this->prepared) $this->dbPrepare();
\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
265 /* 刪除舊附件 (輸出附件清單) */
\r
266 function delOldAttachments($total_size, $storage_max, $warnOnly=true){
\r
268 if(!$this->prepared) $this->dbPrepare();
\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
280 return $warnOnly ? $arr_warn : $this->removeAttachments($arr_kill);
\r
284 function removePosts($posts){
\r
285 if(!$this->prepared) $this->dbPrepare();
\r
286 if(count($posts)==0) return array();
\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
295 /* 刪除附件 (輸出附件清單) */
\r
296 function removeAttachments($posts, $recursion=false){
\r
298 if(!$this->prepared) $this->dbPrepare();
\r
299 if(count($posts)==0) return 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
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
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
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
326 $this->_sqlite_call('UPDATE '.$this->tablename.' SET root = "'.$updatetime.'" WHERE no = '.$resto,
\r
327 array('Push the post failed', __LINE__)); // 將被回應的文章往上移動
\r
329 }else $root = $updatetime; // 新增討論串, 討論串最後被更新時間
\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
333 "'$root',". // 最後更新時間
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
350 function isSuccessivePost($lcount, $com, $timestamp, $pass, $passcookie, $host, $isupload){
\r
352 if(!$this->prepared) $this->dbPrepare();
\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
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
369 function isDuplicateAttachment($lcount, $md5hash){
\r
371 if(!$this->prepared) $this->dbPrepare();
\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
382 function isThread($no){
\r
383 if(!$this->prepared) $this->dbPrepare();
\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
390 function searchPost($keyword, $field, $method){
\r
391 if(!$this->prepared) $this->dbPrepare();
\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
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
406 function searchCategory($category){
\r
407 if(!$this->prepared) $this->dbPrepare();
\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
417 function getPostStatus($status){
\r
418 return new FlagHelper($status); // 回傳 FlagHelper 物件
\r
422 function updatePost($no, $newValues){
\r
423 if(!$this->prepared) $this->dbPrepare();
\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
436 function setPostStatus($no, $newStatus){
\r
437 $this->updatePost($no, array('status' => $newStatus));
\r