Changeset 6246
- Timestamp:
- Apr 2, 2008, 3:50:00 PM (14 years ago)
- Location:
- OpenPNE/branches/work/toraneko/prj_dbtuning
- Files:
-
- 7 edited
Legend:
- Unmodified
- Added
- Removed
-
OpenPNE/branches/work/toraneko/prj_dbtuning/setup/sql/mysql41/update/update-for2.12-db-tuning.sql
r6209 r6246 7 7 8 8 CREATE INDEX c_commu_id_c_member_id ON c_commu_member(c_commu_id,c_member_id); 9 10 /* 日記コメント記入履歴Table */ 11 CREATE TABLE `c_diary_comment_summary` ( 12 `c_diary_comment_summary_id` int(11) NOT NULL auto_increment, 13 `c_member_id` int(11) NOT NULL default '0', 14 `c_diary_id` int(11) NOT NULL default '0', 15 `r_datetime` datetime NOT NULL default '0000-00-00 00:00:00', 16 PRIMARY KEY (`c_diary_comment_summary_id`) 17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 18 -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/lib/db/diary.php
r5998 r6246 514 514 ' AND public_flag <> \'private\'' . 515 515 ' ORDER BY c_diary.r_datetime DESC'; 516 517 516 $c_diary_friend_list = db_get_all_limit($sql, 0, $limit); 518 517 … … 544 543 $is_recurred = false; 545 544 546 $date = date('Y-m-d 00:00:00', strtotime('-15 days')); 547 548 $blocked = db_member_access_block_list4c_member_id_to($c_member_id); 549 $blocked[] = $c_member_id; 550 $except_ids = implode(',', $blocked); 551 552 $sql = 'SELECT c_diary_comment.c_diary_id' . 553 ' FROM c_diary_comment INNER JOIN c_diary USING (c_diary_id)' . 554 ' WHERE c_diary_comment.c_member_id = ?' . 555 ' AND c_diary_comment.r_datetime > ?' . 556 ' AND c_diary.c_member_id NOT IN (' . $except_ids . ')' . 557 ' AND c_diary.public_flag <> \'private\''; 558 $params = array(intval($c_member_id), $date); 559 $c_diary_id_list = db_get_col($sql, $params); 560 $c_diary_id_list = array_unique($c_diary_id_list); 561 if (!$c_diary_id_list) { 562 return array(); 563 } 564 565 $ids = implode(',', $c_diary_id_list); 566 $sql = 'SELECT c_diary_id, MAX(r_datetime) as maxdate' . 567 ' FROM c_diary_comment' . 568 ' WHERE c_diary_id IN (' . $ids . ')' . 569 ' GROUP BY c_diary_id' . 545 $sql = 'SELECT c_diary_id, r_datetime as maxdate' . 546 ' FROM c_diary_comment_summary' . 547 ' WHERE c_member_id = ?' . 570 548 ' ORDER BY maxdate DESC'; 571 $list = db_get_assoc_limit($sql, 0, $limit );549 $list = db_get_assoc_limit($sql, 0, $limit, array(intval($c_member_id))); 572 550 573 551 $result = array(); … … 587 565 function p_h_diary_comment_list_c_diary_my_comment_list4c_member_id($c_member_id, $page, $page_size) 588 566 { 589 $blocked = db_member_access_block_list4c_member_id_to($c_member_id); 590 $blocked[] = $c_member_id; 591 $except_ids = implode(',', $blocked); 592 593 $friends = db_friend_c_member_id_list($c_member_id); 594 $friend_ids = implode(',', $friends); 595 596 if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') { 597 $sql = 'SELECT d.c_diary_id' . 598 ', d.subject' . 599 ', d.c_member_id' . 600 ', sub_diary_tbl.r_datetime' . 601 ', sub_diary_tbl.num_comment' . 602 ' FROM c_diary AS d' . 603 ', ( SELECT dc.c_diary_id' . 604 ', MAX(dc.r_datetime) AS r_datetime' . 605 ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' . 606 ' FROM c_diary AS d' . 607 ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' . 608 ', c_diary_comment AS mydc' . 609 ' WHERE mydc.c_member_id = ?' . 610 ' AND mydc.c_diary_id = d.c_diary_id' . 611 ' AND mydc.c_member_id <> d.c_member_id' . 612 ' AND d.c_member_id NOT IN (' . $except_ids . ')' . 613 ' AND (d.public_flag = \'public\''; 614 if ($friend_ids) { 615 $sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))'; 616 } 617 $sql .= ')' . 618 ' GROUP BY dc.c_diary_id' . 619 ') as sub_diary_tbl' . 620 ' WHERE' . 621 ' sub_diary_tbl.c_diary_id = d.c_diary_id' . 622 ' ORDER BY r_datetime DESC'; 623 } else { 624 $sql = 'SELECT d.c_diary_id' . 625 ', d.subject' . 626 ', d.c_member_id' . 627 ', MAX(dc.r_datetime) AS r_datetime' . 628 ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' . 629 ' FROM c_diary AS d' . 630 ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' . 631 ', c_diary_comment AS mydc' . 632 ' WHERE mydc.c_member_id = ?' . 633 ' AND mydc.c_diary_id = d.c_diary_id' . 634 ' AND mydc.c_member_id <> d.c_member_id' . 635 ' AND d.c_member_id NOT IN (' . $except_ids . ')' . 636 ' AND (d.public_flag = \'public\''; 637 if ($friend_ids) { 638 $sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))'; 639 } 640 $sql .= ')' . 641 ' GROUP BY dc.c_diary_id' . 642 ' ORDER BY r_datetime DESC'; 643 } 644 567 $sql = 'SELECT c_diary_id, c_member_id, r_datetime' . 568 ' FROM c_diary_comment_summary' . 569 ' WHERE c_member_id = ?' . 570 ' ORDER BY r_datetime DESC'; 645 571 $params = array(intval($c_member_id)); 646 572 $list = db_get_all_page($sql, $page, $page_size, $params); … … 648 574 foreach ($list as $key => $value) { 649 575 $list[$key] += db_common_c_member4c_member_id_LIGHT($value['c_member_id']); 650 } 651 652 $sql = 'SELECT COUNT(DISTINCT d.c_diary_id)' . 653 ' FROM c_diary AS d' . 654 ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' . 655 ', c_diary_comment AS mydc' . 656 ' WHERE mydc.c_member_id = ?' . 657 ' AND mydc.c_diary_id = d.c_diary_id' . 658 ' AND mydc.c_member_id <> d.c_member_id' . 659 ' AND d.c_member_id NOT IN (' . $except_ids . ')' . 660 ' AND (d.public_flag = \'public\''; 661 if ($friend_ids) { 662 $sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))'; 663 } 664 $sql .= ')'; 665 576 $diary = db_diary_get_c_diary4id($value['c_diary_id']); 577 $list[$key]['subject'] = $diary['subject']; 578 $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']); 579 } 580 581 $sql = 'SELECT c_diary_id' . 582 ' FROM c_diary_comment_summary' . 583 ' WHERE c_member_id = ?'; 584 $params = array(intval($c_member_id)); 666 585 $total_num = db_get_one($sql, $params); 667 586 … … 1125 1044 } 1126 1045 1046 $sql = 'DELETE FROM c_diary_comment_summary WHERE c_diary_id = ?'; 1047 db_query($sql, $params); 1048 1127 1049 $sql = 'DELETE FROM c_diary_comment WHERE c_diary_id = ?'; 1128 1050 db_query($sql, $params); … … 1259 1181 } 1260 1182 1183 /** 1184 * 日記コメント記入履歴の追加 1185 * 1186 * @param int $c_member_id 1187 * @param int $c_diary_id 1188 */ 1189 function db_diary_insert_c_diary_comment_summary($c_member_id, $c_diary_id) 1190 { 1191 $sql = 'SELECT c_diary_id FROM c_diary_comment_summary' 1192 . ' WHERE c_member_id = ? AND c_diary_id = ?'; 1193 $params = array(intval($c_member_id),intval($c_diary_id)); 1194 $summary_id = db_get_one($sql,$params,'main'); 1195 if(!$summary_id){ 1196 $data = array( 1197 'c_member_id' => intval($c_member_id), 1198 'c_diary_id' => intval($c_diary_id), 1199 'r_datetime' => db_now(), 1200 ); 1201 $ins_id = db_insert('c_diary_comment_summary', $data); 1202 } 1203 } 1204 1205 /** 1206 * 日記コメント記入履歴の更新 1207 * 1208 * @param int $c_diary_id 1209 */ 1210 function db_diary_update_c_diary_comment_summary($c_diary_id) 1211 { 1212 $data = array( 1213 'r_datetime' => db_now(), 1214 ); 1215 $where = array('c_diary_id' => intval($c_diary_id)); 1216 $rtn = db_update('c_diary_comment_summary',$data,$where); 1217 return array($ins_id,$rtn); 1218 } 1219 1220 1221 /** 1222 * 日記コメント記入履歴の削除 1223 * 1224 * @param int $c_member_id 1225 * @param int $c_diary_id 1226 * 該当c_diary_idへのコメント数が0なら、日記コメント記入履歴も削除する。 1227 * 1228 */ 1229 function db_diary_delete_c_diary_comment_summary($c_member_id, $c_diary_id) 1230 { 1231 // 投稿したコメントの有無 1232 $sql = 'SELECT COUNT(c_diary_comment_id) FROM c_diary_comment' 1233 . ' WHERE c_member_id = ? AND c_diary_id = ?'; 1234 $params = array(intval($c_member_id),intval($c_diary_id)); 1235 $count = db_get_one($sql,$params,'main'); 1236 // コメントが無ければ履歴削除 1237 if(!$count){ 1238 $sql = 'DELETE FROM c_diary_comment_summary' 1239 . ' WHERE c_member_id = ? AND c_diary_id = ?'; 1240 $params = array(intval($c_member_id),intval($c_diary_id)); 1241 db_query($sql, $params); 1242 } 1243 $sql = 'SELECT * FROM c_diary_comment WHERE c_diary_id = ?' 1244 . ' ORDER BY c_diary_comment_id DESC'; 1245 $params = array(intval($c_diary_id)); 1246 $comment = db_get_row($sql,$params,'main'); 1247 // 最新コメントの日付で履歴更新 1248 $data = array( 1249 'r_datetime' => $comment['r_datetime'], 1250 ); 1251 $where = array('c_diary_id' => intval($c_diary_id)); 1252 $rtn = db_update('c_diary_comment_summary',$data,$where); 1253 } 1254 1261 1255 ?> -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/lib/mail/sns.php
r6067 r6246 425 425 //日記コメント書き込み 426 426 $ins_id = db_diary_insert_c_diary_comment($this->c_member_id, $c_diary_id, $body); 427 428 //日記コメント記入履歴追加 429 if ($u != $target_c_member_id) { 430 db_diary_insert_c_diary_comment_summary($u,$target_c_diary_id); 431 } 432 //日記コメント記入履歴更新 433 db_diary_update_c_diary_comment_summary($target_c_diary_id); 427 434 428 435 // 写真登録 -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/ktai/do/fh_diary_delete_c_diary_comment.php
r4933 r6246 34 34 db_diary_delete_c_diary_comment($target_c_diary_comment_id, $u); 35 35 36 //コメント記入履歴削除実行 37 db_diary_delete_c_diary_comment_summary($target_c_member_id,$c_diary_comment['c_diary_id']); 38 36 39 $p = array('target_c_diary_id' => $c_diary['c_diary_id']); 37 40 openpne_redirect('ktai', 'page_fh_diary', $p); -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/ktai/do/fh_diary_insert_c_diary_comment.php
r4933 r6246 44 44 45 45 db_diary_insert_c_diary_comment($u, $target_c_diary_id, $body); 46 47 //日記コメント記入履歴追加 48 if ($u != $target_c_member_id) { 49 db_diary_insert_c_diary_comment_summary($u,$target_c_diary_id); 50 } 51 //日記コメント記入履歴更新 52 db_diary_update_c_diary_comment_summary($target_c_diary_id); 53 46 54 //日記コメントが書き込まれたので日記自体を未読扱いにする 47 55 db_diary_update_c_diary_is_checked($target_c_diary_id, 0); -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/pc/do/fh_diary_delete_c_diary_comment.php
r4933 r6246 35 35 //コメント削除実行 36 36 db_diary_delete_c_diary_comment($val, $u); 37 38 //コメント記入履歴削除実行 39 db_diary_delete_c_diary_comment_summary($target_c_diary_comment['c_member_id'],$target_c_diary_id); 37 40 } 38 41 -
OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/pc/do/fh_diary_insert_c_diary_comment.php
r4933 r6246 56 56 db_diary_insert_c_diary_comment_images($c_diary_comment_id, $filename_1, $filename_2, $filename_3); 57 57 58 //日記コメント記入履歴更新 59 if ($u != $target_c_member_id) { 60 db_diary_insert_c_diary_comment_summary($u,$target_c_diary_id); 61 } 62 db_diary_update_c_diary_comment_summary($target_c_diary_id); 63 58 64 //日記コメントが書き込まれたので日記自体を未読扱いにする 59 65 db_diary_update_c_diary_is_checked($target_c_diary_id, 0);
Note: See TracChangeset
for help on using the changeset viewer.