diff options
| author | rxu <rxu@mail.ru> | 2019-11-04 21:48:20 +0700 |
|---|---|---|
| committer | rxu <rxu@mail.ru> | 2019-11-04 21:48:20 +0700 |
| commit | ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b (patch) | |
| tree | 83d2da07d1f9aa45f8d1370c087b0291e4f72c79 | |
| parent | a340c362bcac64e115ddbb138bb90a8afa7faeb9 (diff) | |
| download | forums-ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b.tar forums-ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b.tar.gz forums-ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b.tar.bz2 forums-ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b.tar.xz forums-ba17f16dc87f7f584fa6a56e61ddd44a8aed7f2b.zip | |
[ticket/15294] Get rid of SQL query in a loop as far as possible
PHPBB3-15294
| -rw-r--r-- | phpBB/phpbb/session.php | 51 |
1 files changed, 43 insertions, 8 deletions
diff --git a/phpBB/phpbb/session.php b/phpBB/phpbb/session.php index d8a0d4d708..42ee509df4 100644 --- a/phpBB/phpbb/session.php +++ b/phpBB/phpbb/session.php @@ -964,7 +964,7 @@ class session * Inner SELECT gets most recent expired sessions for unique session_user_id * Outer SELECT gets data for them */ - $sql = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time + $sql_select = 'SELECT s1.session_page, s1.session_user_id, s1.session_time AS recent_time FROM ' . SESSIONS_TABLE . ' AS s1 INNER JOIN ( SELECT session_user_id, MAX(session_time) AS recent_time @@ -975,16 +975,51 @@ class session ) AS s2 ON s1.session_user_id = s2.session_user_id AND s1.session_time = s2.recent_time'; - $result = $db->sql_query($sql); - while ($row = $db->sql_fetchrow($result)) + switch ($db->get_sql_layer()) { - $sql = 'UPDATE ' . USERS_TABLE . ' - SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "' - WHERE user_id = " . (int) $row['session_user_id']; - $db->sql_query($sql); + case 'sqlite3': + case 'oracle': + if ($db->get_sql_layer() === 'sqlite3' && phpbb_version_compare($db->sql_server_info(true), '3.8.3', '>=')) + { + // For SQLite versions 3.8.3+ which support Common Table Expressions (CTE) + $sql = "WITH s3 (session_page, session_user_id, session_time) AS ($sql_select) + UPDATE " . USERS_TABLE . ' + SET (user_lastpage, user_lastvisit) = (SELECT session_page, session_time FROM s3 WHERE session_user_id = user_id) + WHERE EXISTS (SELECT session_user_id FROM s3 WHERE session_user_id = user_id)'; + $db->sql_query($sql); + } + else + { + // For SQLite versions prior to 3.8.3 and Oracle + $result = $db->sql_query($sql_select); + while ($row = $db->sql_fetchrow($result)) + { + $sql = 'UPDATE ' . USERS_TABLE . ' + SET user_lastvisit = ' . (int) $row['recent_time'] . ", user_lastpage = '" . $db->sql_escape($row['session_page']) . "' + WHERE user_id = " . (int) $row['session_user_id']; + $db->sql_query($sql); + } + $db->sql_freeresult($result); + } + break; + + case 'mysqli': + $sql = 'UPDATE ' . USERS_TABLE . " u, + ($sql_select) s3 + SET u.user_lastvisit = s3.recent_time, u.user_lastpage = s3.session_page + WHERE u.user_id = s3.session_user_id"; + $db->sql_query($sql); + break; + + default: + $sql = 'UPDATE ' . USERS_TABLE . " + SET user_lastvisit = s3.recent_time, user_lastpage = s3.session_page + FROM ($sql_select) s3 + WHERE user_id = s3.session_user_id"; + $db->sql_query($sql); + break; } - $db->sql_freeresult($result); // Delete all expired sessions $sql = 'DELETE FROM ' . SESSIONS_TABLE . ' |