7 * LICENSE: This source file is licensed under the terms of the GNU General Public License.
9 * @package Magic3 Framework
10 * @author 平田直毅(Naoki Hirata) <naoki@aplo.co.jp>
11 * @copyright Copyright 2006-2011 Magic3 Project.
12 * @license http://www.gnu.org/copyleft/gpl.html GPL License
13 * @version SVN: $Id: bbs_2ch_mainDb.php 4026 2011-03-10 07:40:49Z fishbone $
14 * @link http://www.magic3.org
16 require_once($gEnvManager->getDbPath() . '/baseDb.php');
18 class bbs_2ch_mainDb extends BaseDb
23 * @param array $rows レコード
24 * @param string $boardId 掲示板ID
25 * @return bool 1行以上取得 = true, 取得なし= false
27 function getAllConfig(&$rows, $boardId = '')
29 $queryStr = 'SELECT * FROM bbs_2ch_config ';
30 $queryStr .= 'WHERE tg_board_id = ? ';
31 $queryStr .= 'ORDER BY tg_index';
32 $retValue = $this->selectRecords($queryStr, array($boardId), $rows);
38 * @param string $key キーとなる項目値
39 * @param string $value 値
40 * @param string $boardId 掲示板ID
41 * @return true = 正常、false=異常
43 function updateConfig($key, $value, $boardId = '')
46 $this->startTransaction();
48 $queryStr = 'SELECT tg_value FROM bbs_2ch_config ';
49 $queryStr .= 'WHERE tg_board_id = ? ';
50 $queryStr .= 'AND tg_id = ? ';
51 $ret = $this->selectRecord($queryStr, array($boardId, $key), $row);
53 $queryStr = 'UPDATE bbs_2ch_config ';
54 $queryStr .= 'SET tg_value = ? ';
55 $queryStr .= 'WHERE tg_board_id = ? ';
56 $queryStr .= 'AND tg_id = ? ';
57 $ret = $this->execStatement($queryStr, array($value, $boardId, $key));
59 $queryStr = 'INSERT INTO bbs_2ch_config (';
60 $queryStr .= 'tg_board_id, ';
61 $queryStr .= 'tg_id, ';
62 $queryStr .= 'tg_value ';
63 $queryStr .= ') VALUES (';
64 $queryStr .= '?, ?, ?';
66 $ret = $this->execStatement($queryStr, array($boardId, $key, $value));
69 $ret = $this->endTransaction();
75 * @param string $boardId 掲示板ID
76 * @param string $threadId スレッドID
77 * @param string $subject スレッド件名
78 * @param string $userName 投稿者名
79 * @param string $email Eメールアドレス
80 * @param string $message 投稿メッセージ
81 * @return bool true = 成功、false = 失敗
83 function addNewThread($boardId, $threadId, $subject, $userName, $email, $message)
85 $now = date("Y/m/d H:i:s"); // 現在日時
86 $user = $this->gEnv->getCurrentUserId(); // 現在のユーザ
87 $accessLog = $this->gEnv->getCurrentAccessLogSerial();
90 $this->startTransaction();
93 $queryStr = 'INSERT INTO bbs_2ch_thread ';
95 $queryStr .= 'th_board_id, ';
96 $queryStr .= 'th_id, ';
97 $queryStr .= 'th_subject, ';
98 $queryStr .= 'th_message_count, ';
99 $queryStr .= 'th_dt, ';
100 $queryStr .= 'th_log_serial, ';
101 $queryStr .= 'th_create_user_id, ';
102 $queryStr .= 'th_create_dt) ';
103 $queryStr .= 'VALUES ';
104 $queryStr .= '(?, ?, ?, ?, ?, ?, ?, ?)';
105 $this->execStatement($queryStr, array($boardId, $threadId, $subject, 1, $now, $accessLog, $user, $now));
107 $queryStr = 'INSERT INTO bbs_2ch_thread_message ';
109 $queryStr .= 'te_board_id, ';
110 $queryStr .= 'te_thread_id, ';
111 $queryStr .= 'te_index, ';
112 $queryStr .= 'te_user_name, ';
113 $queryStr .= 'te_email, ';
114 $queryStr .= 'te_message, ';
115 $queryStr .= 'te_regist_dt, ';
116 $queryStr .= 'te_log_serial, ';
117 $queryStr .= 'te_update_user_id, ';
118 $queryStr .= 'te_update_dt) ';
119 $queryStr .= 'VALUES ';
120 $queryStr .= '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
121 $this->execStatement($queryStr, array($boardId, $threadId, 1, $userName, $email, $message, $now, $accessLog, $user, $now));
124 $ret = $this->endTransaction();
130 * @param function $callback コールバック関数
131 * @param string $boardId 掲示板ID
132 * @param int $limit 取得数(-1=すべて取得)
135 function getThread($callback, $boardId, $limit)
137 $queryStr = 'SELECT * FROM bbs_2ch_thread ';
138 $queryStr .= 'WHERE th_board_id = ? ';
139 $queryStr .= 'AND th_deleted = false '; // 削除されていない
141 $queryStr .= 'ORDER BY th_dt DESC';
143 $queryStr .= 'ORDER BY th_dt DESC limit ' . intval($limit);
145 $this->selectLoop($queryStr, array($boardId), $callback, null);
150 * @param function $callback コールバック関数
151 * @param string $boardId 掲示板ID
152 * @param int $limit 取得数(-1=すべて取得)
153 * @param string $keyword 検索キーワード
156 function getThreadByKeyword($callback, $boardId, $limit, $keyword)
158 $queryStr = 'SELECT th_id,th_subject,th_message_count FROM bbs_2ch_thread_message LEFT JOIN bbs_2ch_thread ON te_board_id = th_board_id AND te_thread_id = th_id AND th_deleted = false ';
159 $queryStr .= 'WHERE te_board_id = ? ';
160 $queryStr .= 'AND te_deleted = false '; // 削除されていない
163 $keyword = addslashes($keyword);
164 $queryStr .= 'AND (te_user_name LIKE \'%' . $keyword . '%\' ';
165 $queryStr .= 'OR te_email LIKE \'%' . $keyword . '%\' ';
166 $queryStr .= 'OR te_message LIKE \'%' . $keyword . '%\') ';
168 $queryStr .= 'GROUP BY th_id ';
170 $queryStr .= 'ORDER BY th_dt DESC';
172 $queryStr .= 'ORDER BY th_dt DESC limit ' . intval($limit);
174 $this->selectLoop($queryStr, array($boardId), $callback, null);
179 * @param function $callback コールバック関数
180 * @param string $boardId 掲示板ID
181 * @param array $threadId スレッドID
182 * @param array $minIndexArray メッセージインデックス番号の最小値
185 function getThreadMessage($callback, $boardId, $threadId, $minIndexArray)
188 if (empty($threadId)) return;
191 $threadCount = count($threadId);
192 $caseStr = 'CASE te_thread_id ';
194 for ($i = 0; $i < $threadCount; $i++){
195 $threadIdStr = addslashes($threadId[$i]);
196 $caseStr .= 'WHEN \'' . $threadIdStr . '\' THEN ' . $i . ' ';
197 $thread .= '\'' . $threadIdStr . '\',';
199 $caseStr .= 'END AS no';
200 $thread = rtrim($thread, ',');
204 for ($i = 0; $i < $threadCount; $i++){
205 if ($i > 0) $condStr .= 'OR ';
206 $threadIdStr = addslashes($threadId[$i]);
207 $condStr .= '(te_thread_id = \'' . $threadIdStr . '\' ';
208 $condStr .= 'AND (te_index = 1 OR te_index >= ' . intval($minIndexArray[$i]) . ')) ';
212 $queryStr = 'SELECT *, ' . $caseStr . ' FROM bbs_2ch_thread_message ';
213 $queryStr .= 'WHERE te_board_id = ? ';
214 $queryStr .= $condStr;
215 $queryStr .= 'ORDER BY no, te_index';
216 $this->selectLoop($queryStr, array($boardId), $callback, null);
221 * @param function $callback コールバック関数
222 * @param string $boardId 掲示板ID
223 * @param array $threadId スレッドID
224 * @param int $limit 取得する項目数(0のときすべて)
225 * @param int $offset 取得開始位置(0~)
228 function getThreadMessageByRange($callback, $boardId, $threadId, $limit, $offset)
230 $queryStr = 'SELECT * FROM bbs_2ch_thread_message ';
231 $queryStr .= 'WHERE te_board_id = ? ';
232 $queryStr .= 'AND te_thread_id = ? ';
234 $queryStr .= 'ORDER BY te_index';
236 $queryStr .= 'ORDER BY te_index limit ' . intval($limit) . ' offset ' . intval($offset);
238 $this->selectLoop($queryStr, array($boardId, $threadId), $callback);
243 * @param string $boardId 掲示板ID
244 * @param string $threadId スレッドID
245 * @param array $row 取得データ
246 * @return true=正常、false=異常
248 function getThreadInfo($boardId, $threadId, &$row)
250 $queryStr = 'SELECT * FROM bbs_2ch_thread ';
251 $queryStr .= 'WHERE th_board_id = ? ';
252 $queryStr .= 'AND th_id = ? ';
253 $queryStr .= 'AND th_deleted = false '; // 削除されていない
254 $ret = $this->selectRecord($queryStr, array($boardId, $threadId), $row);
260 * @param string $boardId 掲示板ID
261 * @param string $threadId スレッドID
262 * @param string $userName 投稿者名
263 * @param string $email Eメールアドレス
264 * @param string $message 投稿メッセージ
265 * @param bool $updateDt 日付を更新するかどうか
266 * @param int $newSerial 新規シリアル番号
267 * @return bool true = 成功、false = 失敗
269 function addMessage($boardId, $threadId, $userName, $email, $message, $updateDt, &$newSerial)
271 $now = date("Y/m/d H:i:s"); // 現在日時
272 $user = $this->gEnv->getCurrentUserId(); // 現在のユーザ
273 $accessLog = $this->gEnv->getCurrentAccessLogSerial();
276 $this->startTransaction();
279 $queryStr = 'SELECT * FROM bbs_2ch_thread ';
280 $queryStr .= 'WHERE th_board_id = ? ';
281 $queryStr .= 'AND th_id = ? ';
282 $queryStr .= 'AND th_deleted = false '; // 削除されていない
283 $ret = $this->selectRecord($queryStr, array($boardId, $threadId), $row);
286 $messageCount = $row['th_message_count'] + 1; // メッセージ数
287 $queryStr = 'UPDATE bbs_2ch_thread ';
288 $queryStr .= 'SET th_message_count = ?, '; $params[] = $messageCount;
290 $queryStr .= 'th_dt = ?, '; $params[] = $now; // 日付を更新
292 $queryStr .= 'th_update_user_id = ?, '; $params[] = $user;
293 $queryStr .= 'th_update_dt = ? '; $params[] = $now;
294 $queryStr .= 'WHERE th_serial = ?'; $params[] = $row['th_serial'];
295 $ret = $this->execStatement($queryStr, $params);
296 } else { // 存在しない場合は終了
297 $this->endTransaction();
301 $queryStr = 'INSERT INTO bbs_2ch_thread_message ';
303 $queryStr .= 'te_board_id, ';
304 $queryStr .= 'te_thread_id, ';
305 $queryStr .= 'te_index, ';
306 $queryStr .= 'te_user_name, ';
307 $queryStr .= 'te_email, ';
308 $queryStr .= 'te_message, ';
309 $queryStr .= 'te_regist_dt, ';
310 $queryStr .= 'te_log_serial, ';
311 $queryStr .= 'te_update_user_id, ';
312 $queryStr .= 'te_update_dt) ';
313 $queryStr .= 'VALUES ';
314 $queryStr .= '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
315 $this->execStatement($queryStr, array($boardId, $threadId, $messageCount, $userName, $email, $message, $now, $accessLog, $user, $now));
318 $queryStr = 'SELECT MAX(te_serial) AS mx FROM bbs_2ch_thread_message ';
319 $ret = $this->selectRecord($queryStr, array(), $row);
320 if ($ret) $newSerial = $row['mx'];
323 $ret = $this->endTransaction();
329 * @param string $boardId 掲示板ID
330 * @param int $limit 取得数
331 * @param int $page 取得するページ(1~)
332 * @param function $callback コールバック関数
335 function getMessage($boardId, $limit, $page, $callback)
337 if ($limit < 0) $limit = 0;
339 $offset = $limit * ($page -1);
340 if ($offset < 0) $offset = 0;
342 $queryStr = 'SELECT * FROM bbs_2ch_thread_message LEFT JOIN bbs_2ch_thread ON te_board_id = th_board_id AND te_thread_id = th_id AND th_deleted = false ';
343 $queryStr .= 'WHERE te_board_id = ? ';
344 $queryStr .= 'AND te_deleted = false '; // 削除されていない
345 $queryStr .= 'ORDER BY th_dt DESC, te_index ';
346 $queryStr .= 'LIMIT ' . $limit . ' OFFSET ' . $offset;
347 $this->selectLoop($queryStr, array($boardId), $callback);
352 * @param string $boardId 掲示板ID
355 function getMessageCount($boardId)
357 $queryStr = 'SELECT * FROM bbs_2ch_thread_message LEFT JOIN bbs_2ch_thread ON te_board_id = th_board_id AND te_thread_id = th_id AND th_deleted = false ';
358 $queryStr .= 'WHERE te_board_id = ? ';
359 $queryStr .= 'AND te_deleted = false '; // 削除されていない
360 return $this->selectRecordCount($queryStr, array($boardId));
363 * メッセージをシリアル番号で取得(管理用)
365 * @param string $serial シリアル番号
366 * @param array $row レコード
367 * @return bool 取得 = true, 取得なし= false
369 function getMessageBySerial($serial, &$row)
371 $queryStr = 'SELECT * FROM bbs_2ch_thread_message LEFT JOIN bbs_2ch_thread ON te_board_id = th_board_id AND te_thread_id = th_id AND th_deleted = false ';
372 $queryStr .= 'WHERE te_serial = ? ';
373 $ret = $this->selectRecord($queryStr, array($serial), $row);
379 * @param array $serial シリアルNo
380 * @return true=成功、false=失敗
382 function delMessage($serial)
384 $now = date("Y/m/d H:i:s"); // 現在日時
385 $userId = $this->gEnv->getCurrentUserId(); // 現在のユーザ
387 if (!is_array($serial) || count($serial) <= 0) return true;
390 $this->startTransaction();
392 // 指定のシリアルNoのレコードが削除状態でないかチェック
393 $delThread = array(); // 削除するスレッドのID
394 for ($i = 0; $i < count($serial); $i++){
395 $queryStr = 'SELECT * FROM bbs_2ch_thread_message ';
396 $queryStr .= 'WHERE te_deleted = false '; // 未削除
397 $queryStr .= 'AND te_serial = ? ';
398 $ret = $this->selectRecord($queryStr, array($serial[$i]), $row);
400 // 存在しない場合は、既に削除されたとして終了
402 $boardId = $row['te_board_id'];
403 $index = $row['te_index'];
404 if ($index == 1) $delThread[] = $row['te_thread_id'];
406 $this->endTransaction();
412 $queryStr = 'UPDATE bbs_2ch_thread_message ';
413 $queryStr .= 'SET te_deleted = true, '; // 削除
414 $queryStr .= 'te_update_user_id = ?, ';
415 $queryStr .= 'te_update_dt = ? ';
416 $queryStr .= 'WHERE te_serial in (' . implode($serial, ',') . ') ';
417 $this->execStatement($queryStr, array($userId, $now));
420 if (count($delThread) > 0){
422 for ($i = 0; $i < count($delThread); $i++){
423 $delId .= '\'' . addslashes($delThread[$i]) . '\',';
425 $delId = rtrim($delId, ',');
426 $queryStr = 'UPDATE bbs_2ch_thread ';
427 $queryStr .= 'SET th_deleted = true, '; // 削除
428 $queryStr .= 'th_update_user_id = ?, ';
429 $queryStr .= 'th_update_dt = ? ';
430 $queryStr .= 'WHERE th_board_id = ? ';
431 $queryStr .= 'AND th_id in (' . $delId . ') ';
432 $queryStr .= 'AND th_deleted = false ';
433 $this->execStatement($queryStr, array($userId, $now, $boardId));
435 // 削除するスレッドに属するメッセージはすべて削除
436 $queryStr = 'UPDATE bbs_2ch_thread_message ';
437 $queryStr .= 'SET te_deleted = true, '; // 削除
438 $queryStr .= 'te_update_user_id = ?, ';
439 $queryStr .= 'te_update_dt = ? ';
440 $queryStr .= 'WHERE te_board_id = ? ';
441 $queryStr .= 'AND te_thread_id in (' . $delId . ') ';
442 $queryStr .= 'AND te_deleted = false ';
443 $this->execStatement($queryStr, array($userId, $now, $boardId));
447 $ret = $this->endTransaction();
453 * @param int $serial シリアルNo
454 * @param string $userName 投稿者名
455 * @param string $email Eメールアドレス
456 * @param string $message 投稿メッセージ
457 * @return bool true = 成功、false = 失敗
459 function updateMessage($serial, $userName, $email, $message)
461 $now = date("Y/m/d H:i:s"); // 現在日時
462 $userId = $this->gEnv->getCurrentUserId(); // 現在のユーザ
465 $this->startTransaction();
468 $queryStr = 'UPDATE bbs_2ch_thread_message ';
469 $queryStr .= 'SET te_user_name = ?, ';
470 $queryStr .= 'te_email = ?, ';
471 $queryStr .= 'te_message = ?, ';
472 $queryStr .= 'te_update_user_id = ?, ';
473 $queryStr .= 'te_update_dt = ? ';
474 $queryStr .= 'WHERE te_serial = ?';
475 $this->execStatement($queryStr, array($userName, $email, $message, $userId, $now, $serial));
478 $ret = $this->endTransaction();