ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Changeset 6246


Ignore:
Timestamp:
Apr 2, 2008, 3:50:00 PM (14 years ago)
Author:
toraneko
Message:

日記コメント記入履歴を別テーブルにするチューニング

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  
    77
    88CREATE INDEX c_commu_id_c_member_id ON c_commu_member(c_commu_id,c_member_id);
     9
     10/* 日記コメント記入履歴Table */
     11CREATE 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  
    514514            ' AND public_flag <> \'private\'' .
    515515            ' ORDER BY c_diary.r_datetime DESC';
    516 
    517516    $c_diary_friend_list = db_get_all_limit($sql, 0, $limit);
    518517
    … …  
    544543    $is_recurred = false;
    545544
    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 = ?' .
    570548           ' 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)));
    572550
    573551    $result = array();
    … …  
    587565function p_h_diary_comment_list_c_diary_my_comment_list4c_member_id($c_member_id, $page, $page_size)
    588566{
    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';
    645571    $params = array(intval($c_member_id));
    646572    $list = db_get_all_page($sql, $page, $page_size, $params);
    … …  
    648574    foreach ($list as $key => $value) {
    649575        $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));
    666585    $total_num = db_get_one($sql, $params);
    667586
    … …  
    11251044    }
    11261045
     1046    $sql = 'DELETE FROM c_diary_comment_summary WHERE c_diary_id = ?';
     1047    db_query($sql, $params);
     1048
    11271049    $sql = 'DELETE FROM c_diary_comment WHERE c_diary_id = ?';
    11281050    db_query($sql, $params);
    … …  
    12591181}
    12601182
     1183/**
     1184 * 日記コメント記入履歴の追加
     1185 *
     1186 * @param  int    $c_member_id
     1187 * @param  int    $c_diary_id
     1188 */
     1189function 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 */
     1210function 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 */
     1229function 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
    12611255?>

  • OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/lib/mail/sns.php

    r6067 r6246  
    425425        //日記コメント書き込み
    426426        $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);
    427434
    428435        // 写真登録

  • OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/ktai/do/fh_diary_delete_c_diary_comment.php

    r4933 r6246  
    3434        db_diary_delete_c_diary_comment($target_c_diary_comment_id, $u);
    3535
     36        //コメント記入履歴削除実行
     37        db_diary_delete_c_diary_comment_summary($target_c_member_id,$c_diary_comment['c_diary_id']);
     38
    3639        $p = array('target_c_diary_id' => $c_diary['c_diary_id']);
    3740        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  
    4444
    4545        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
    4654        //日記コメントが書き込まれたので日記自体を未読扱いにする
    4755        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  
    3535            //コメント削除実行
    3636            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);
    3740        }
    3841

  • OpenPNE/branches/work/toraneko/prj_dbtuning/webapp/modules/pc/do/fh_diary_insert_c_diary_comment.php

    r4933 r6246  
    5656        db_diary_insert_c_diary_comment_images($c_diary_comment_id, $filename_1, $filename_2, $filename_3);
    5757
     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
    5864        //日記コメントが書き込まれたので日記自体を未読扱いにする
    5965        db_diary_update_c_diary_is_checked($target_c_diary_id, 0);
Note: See TracChangeset for help on using the changeset viewer.