Кеширование статистики пользователя (альтернатива)

Ragnar · 23 Фев 2013

С таким кодом работает

PHP:

function get_bt_userdata ($user_id)
{
    if (!$get_bt_sql = CACHE('bb_cache')->get('get_bt_sql _'. $user_id ))
    {
        $get_bt_sql = DB()->fetch_row("
            SELECT bt.*, SUM(tr.speed_up) as speed_up, SUM(tr.speed_down) as speed_down
            FROM      ". BB_BT_USERS  ." bt
            LEFT JOIN ". BB_BT_TRACKER ." tr ON (bt.user_id = tr.user_id)
            WHERE bt.user_id = ". (int) $user_id ."
            GROUP BY bt.user_id
        ");
        CACHE('bb_cache')->set('get_bt_sql_'. $user_id, $get_bt_sql, 300);
    }
 
    return $get_bt_sql;
}

PHP:

function show_bt_userdata ($user_id)
{
    global $lang;
 
    $btu = get_bt_userdata($user_id);
 
    if (!$row = CACHE('bb_cache')->get('row _'. $user_id ))
    {
        $row = DB()->fetch_row("
            SELECT COUNT(tor.user_id) AS all_count,
                    SUM(tor.seeder) AS seeding, tor.topic_id,
                    SUM(tor.releaser) AS releasing, tor.user_id, u.user_id
            FROM ". BB_USERS ." u, ". BB_BT_TRACKER . " tor
            WHERE tor.user_id = u.user_id
            AND tor.user_id = ". $user_id ."
        ");
        CACHE('bb_cache')->set('row_'. $user_id, $row, 300);
    }
 
    $seeding = $row['seeding'] - $row['releasing'];
    if ($seeding < 0)
    {
        $seeding = 0;
    }
    $leeching = $row['all_count'] - $row['seeding'];
 
    $GLOBALS['template']->assign_vars(array(
        'SHOW_BT_USERDATA' => true,
        'UP_TOTAL'        => humn_size($btu['u_up_total']),
        'UP_BONUS'        => humn_size($btu['u_up_bonus']),
        'RELEASED'        => humn_size($btu['u_up_release']),
        'DOWN_TOTAL'      => humn_size($btu['u_down_total']),
        'DOWN_TOTAL_BYTES' => $btu['u_down_total'],
        'USER_RATIO'      => get_bt_ratio($btu),
        'MIN_DL_FOR_RATIO' => humn_size(MIN_DL_FOR_RATIO),
        'MIN_DL_BYTES'    => MIN_DL_FOR_RATIO,
        'AUTH_KEY'        => ($btu['auth_key']) ? $btu['auth_key'] : $lang['NONE'],
 
        'TD_DL'            => humn_size($btu['down_today']),
        'TD_UL'            => humn_size($btu['up_today']),
        'TD_REL'          => humn_size($btu['up_release_today']),
        'TD_BONUS'        => humn_size($btu['up_bonus_today']),
        'TD_POINTS'        => ($btu['auth_key']) ? $btu['points_today'] : '0.00',
 
        'YS_DL'            => humn_size($btu['down_yesterday']),
        'YS_UL'            => humn_size($btu['up_yesterday']),
        'YS_REL'          => humn_size($btu['up_release_yesterday']),
        'YS_BONUS'        => humn_size($btu['up_bonus_yesterday']),
        'YS_POINTS'        => ($btu['auth_key']) ? $btu['points_yesterday'] : '0.00',
 
        'SPEED_UP'        => humn_size($btu['speed_up'], 0, 'KB') .'/s',
        'SPEED_DOWN'      => humn_size($btu['speed_down'], 0, 'KB') .'/s',
 
        'RELEASING'        => $row['releasing'] ? $row['releasing'] : 0,
        'SEEDING'          => $seeding ? $seeding : 0,
        'LEECHING'        => $leeching ? $leeching : 0,
    ));
}

Gemini_13 · 23 Фев 2013

точно?

PHP:

 if (!$row = CACHE('bb_cache')->get('row_'))

Ragnar · 23 Фев 2013

Работает, проверял с нескольких аккаунтов.

Gemini_13 · 24 Фев 2013

Ragnar, ну ты все же подумай на этими строками еще раз

PHP:

if (!$get_bt_sql = CACHE('bb_cache')->get('get_bt_sql_'))

PHP:

if (!$row = CACHE('bb_cache')->get('row_'))

Ragnar · 24 Фев 2013

Gemini_13, исправил

dredd · 24 Фев 2013

Так как не последовало нормального решения проблемы, я напишу

Открываем functions.php и находим:

PHP:

function get_bt_userdata ($user_id)
{
    if (!$get_bt_sql = CACHE('bb_cache')->get('get_bt_sql'))
    {
        $get_bt_sql = DB()->fetch_row("
            SELECT bt.*, SUM(tr.speed_up) as speed_up, SUM(tr.speed_down) as speed_down
            FROM      ". BB_BT_USERS  ." bt
            LEFT JOIN ". BB_BT_TRACKER ." tr ON (bt.user_id = tr.user_id)
            WHERE bt.user_id = ". (int) $user_id ."
            GROUP BY bt.user_id
        ");
        CACHE('bb_cache')->set('get_bt_sql', $get_bt_sql, 300);
    }
 
    return $get_bt_sql;
}

Заменяем на:

PHP:

function get_bt_userdata ($user_id)
{
    if (!$get_bt_sql = CACHE('bb_cache')->get('get_bt_sql_'.$user_id))
    {
        $get_bt_sql = DB()->fetch_row("
            SELECT bt.*, SUM(tr.speed_up) as speed_up, SUM(tr.speed_down) as speed_down
            FROM      ". BB_BT_USERS  ." bt
            LEFT JOIN ". BB_BT_TRACKER ." tr ON (bt.user_id = tr.user_id)
            WHERE bt.user_id = ". (int) $user_id ."
            GROUP BY bt.user_id
        ");
        CACHE('bb_cache')->set('get_bt_sql_'.$user_id, $get_bt_sql, 300);
    }
 
    return $get_bt_sql;
}

Находим:

PHP:

function show_bt_userdata ($user_id)
{
    global $lang;
 
    $btu = get_bt_userdata($user_id);
 
    if (!$row = CACHE('bb_cache')->get('row'))
    {
        $row = DB()->fetch_row("
            SELECT COUNT(tor.user_id) AS all_count,
                    SUM(tor.seeder) AS seeding, tor.topic_id,
                    SUM(tor.releaser) AS releasing, tor.user_id, u.user_id
            FROM ". BB_USERS ." u, ". BB_BT_TRACKER . " tor
            WHERE tor.user_id = u.user_id
            AND tor.user_id = ". $user_id ."
        ");
        CACHE('bb_cache')->set('row', $row, 300);
    }
 
    $seeding = $row['seeding'] - $row['releasing'];
    if ($seeding < 0)
    {
        $seeding = 0;
    }
    $leeching = $row['all_count'] - $row['seeding'];
 
    $GLOBALS['template']->assign_vars(array(
        'SHOW_BT_USERDATA' => true,
        'UP_TOTAL'        => humn_size($btu['u_up_total']),
        'UP_BONUS'        => humn_size($btu['u_up_bonus']),
        'RELEASED'        => humn_size($btu['u_up_release']),
        'DOWN_TOTAL'      => humn_size($btu['u_down_total']),
        'DOWN_TOTAL_BYTES' => $btu['u_down_total'],
        'USER_RATIO'      => get_bt_ratio($btu),
        'MIN_DL_FOR_RATIO' => humn_size(MIN_DL_FOR_RATIO),
        'MIN_DL_BYTES'    => MIN_DL_FOR_RATIO,
        'AUTH_KEY'        => ($btu['auth_key']) ? $btu['auth_key'] : $lang['NONE'],
 
        'TD_DL'            => humn_size($btu['down_today']),
        'TD_UL'            => humn_size($btu['up_today']),
        'TD_REL'          => humn_size($btu['up_release_today']),
        'TD_BONUS'        => humn_size($btu['up_bonus_today']),
        'TD_POINTS'        => ($btu['auth_key']) ? $btu['points_today'] : '0.00',
 
        'YS_DL'            => humn_size($btu['down_yesterday']),
        'YS_UL'            => humn_size($btu['up_yesterday']),
        'YS_REL'          => humn_size($btu['up_release_yesterday']),
        'YS_BONUS'        => humn_size($btu['up_bonus_yesterday']),
        'YS_POINTS'        => ($btu['auth_key']) ? $btu['points_yesterday'] : '0.00',
 
        'SPEED_UP'        => humn_size($btu['speed_up'], 0, 'KB') .'/s',
        'SPEED_DOWN'      => humn_size($btu['speed_down'], 0, 'KB') .'/s',
 
        'RELEASING'        => $row['releasing'] ? $row['releasing'] : 0,
        'SEEDING'          => $seeding ? $seeding : 0,
        'LEECHING'        => $leeching ? $leeching : 0,
    ));
}

Заменяем на:

PHP:

function show_bt_userdata ($user_id)
{
    global $lang;
 
    $btu = get_bt_userdata($user_id);
 
    if (!$row = CACHE('bb_cache')->get('row_'.$user_id))
    {
        $row = DB()->fetch_row("
            SELECT COUNT(tor.user_id) AS all_count,
                    SUM(tor.seeder) AS seeding, tor.topic_id,
                    SUM(tor.releaser) AS releasing, tor.user_id, u.user_id
            FROM ". BB_USERS ." u, ". BB_BT_TRACKER . " tor
            WHERE tor.user_id = u.user_id
            AND tor.user_id = ". $user_id ."
        ");
        CACHE('bb_cache')->set('row_'.$user_id, $row, 300);
    }
 
    $seeding = $row['seeding'] - $row['releasing'];
    if ($seeding < 0)
    {
        $seeding = 0;
    }
    $leeching = $row['all_count'] - $row['seeding'];
 
    $GLOBALS['template']->assign_vars(array(
        'SHOW_BT_USERDATA' => true,
        'UP_TOTAL'        => humn_size($btu['u_up_total']),
        'UP_BONUS'        => humn_size($btu['u_up_bonus']),
        'RELEASED'        => humn_size($btu['u_up_release']),
        'DOWN_TOTAL'      => humn_size($btu['u_down_total']),
        'DOWN_TOTAL_BYTES' => $btu['u_down_total'],
        'USER_RATIO'      => get_bt_ratio($btu),
        'MIN_DL_FOR_RATIO' => humn_size(MIN_DL_FOR_RATIO),
        'MIN_DL_BYTES'    => MIN_DL_FOR_RATIO,
        'AUTH_KEY'        => ($btu['auth_key']) ? $btu['auth_key'] : $lang['NONE'],
 
        'TD_DL'            => humn_size($btu['down_today']),
        'TD_UL'            => humn_size($btu['up_today']),
        'TD_REL'          => humn_size($btu['up_release_today']),
        'TD_BONUS'        => humn_size($btu['up_bonus_today']),
        'TD_POINTS'        => ($btu['auth_key']) ? $btu['points_today'] : '0.00',
 
        'YS_DL'            => humn_size($btu['down_yesterday']),
        'YS_UL'            => humn_size($btu['up_yesterday']),
        'YS_REL'          => humn_size($btu['up_release_yesterday']),
        'YS_BONUS'        => humn_size($btu['up_bonus_yesterday']),
        'YS_POINTS'        => ($btu['auth_key']) ? $btu['points_yesterday'] : '0.00',
 
        'SPEED_UP'        => humn_size($btu['speed_up'], 0, 'KB') .'/s',
        'SPEED_DOWN'      => humn_size($btu['speed_down'], 0, 'KB') .'/s',
 
        'RELEASING'        => $row['releasing'] ? $row['releasing'] : 0,
        'SEEDING'          => $seeding ? $seeding : 0,
        'LEECHING'        => $leeching ? $leeching : 0,
    ));
}

И теперь радуемся что у всех в профиле, своя статистика)

Zenden · 24 Фев 2013

Зачем вычисления в базе делать, она и так является слабым звеном. Правильнее будет арифметику на пых перенести.

pilot · 24 Фев 2013

а кто нибуть может еще сделать ручное обновление статистики для этого чуда?

dredd · 24 Фев 2013

pilot, та там одну кнопку прилепить

pilot · 24 Фев 2013

dredd, подскажи плиз