diff options
author | David M <davidmj@users.sourceforge.net> | 2006-05-05 22:06:17 +0000 |
---|---|---|
committer | David M <davidmj@users.sourceforge.net> | 2006-05-05 22:06:17 +0000 |
commit | 3d2a45ab049606701172c11552aa0c1006d2fbf1 (patch) | |
tree | ea65a291ac3901d723f33446c54e449e3218ea13 | |
parent | b6ffae82b938eb3c40395234f14d79b53ba003c4 (diff) | |
download | forums-3d2a45ab049606701172c11552aa0c1006d2fbf1.tar forums-3d2a45ab049606701172c11552aa0c1006d2fbf1.tar.gz forums-3d2a45ab049606701172c11552aa0c1006d2fbf1.tar.bz2 forums-3d2a45ab049606701172c11552aa0c1006d2fbf1.tar.xz forums-3d2a45ab049606701172c11552aa0c1006d2fbf1.zip |
I hope nothing broke!
- Added a query builder, it is currently only used for complex queries that involve a FROM clause with two tables and a left join
- Changed some function calls in the DBAL
- Made the viewtopic queries nicer
git-svn-id: file:///svn/phpbb/trunk@5885 89ea8834-ac86-4346-8a33-228a782c2dd0
-rw-r--r-- | phpBB/includes/acp/acp_permissions.php | 62 | ||||
-rw-r--r-- | phpBB/includes/auth.php | 124 | ||||
-rw-r--r-- | phpBB/includes/db/dbal.php | 61 | ||||
-rw-r--r-- | phpBB/includes/db/firebird.php | 13 | ||||
-rw-r--r-- | phpBB/includes/db/mssql.php | 10 | ||||
-rw-r--r-- | phpBB/includes/db/mssql_odbc.php | 10 | ||||
-rw-r--r-- | phpBB/includes/db/mysql.php | 15 | ||||
-rw-r--r-- | phpBB/includes/db/mysql4.php | 15 | ||||
-rw-r--r-- | phpBB/includes/db/mysqli.php | 15 | ||||
-rw-r--r-- | phpBB/includes/db/oracle.php | 6 | ||||
-rw-r--r-- | phpBB/includes/db/postgres.php | 15 | ||||
-rw-r--r-- | phpBB/includes/db/sqlite.php | 10 | ||||
-rw-r--r-- | phpBB/includes/functions_admin.php | 32 | ||||
-rw-r--r-- | phpBB/includes/mcp/mcp_front.php | 38 | ||||
-rw-r--r-- | phpBB/includes/ucp/ucp_main.php | 72 | ||||
-rw-r--r-- | phpBB/mcp.php | 26 | ||||
-rw-r--r-- | phpBB/memberlist.php | 27 | ||||
-rwxr-xr-x | phpBB/ucp.php | 29 | ||||
-rw-r--r-- | phpBB/viewtopic.php | 122 |
19 files changed, 529 insertions, 173 deletions
diff --git a/phpBB/includes/acp/acp_permissions.php b/phpBB/includes/acp/acp_permissions.php index f42a03ba15..1059629863 100644 --- a/phpBB/includes/acp/acp_permissions.php +++ b/phpBB/includes/acp/acp_permissions.php @@ -344,14 +344,29 @@ class acp_permissions $sql_forum_id = ($permission_scope == 'global') ? 'AND a.forum_id = 0' : ((sizeof($forum_id)) ? 'AND a.forum_id IN (' . implode(', ', $forum_id) . ')' : 'AND a.forum_id <> 0'); $sql_permission_option = "AND o.auth_option LIKE '" . $db->sql_escape($permission_type) . "%'"; - $sql = 'SELECT DISTINCT u.username, u.user_regdate, u.user_id - FROM (' . USERS_TABLE . ' u, ' . ACL_OPTIONS_TABLE . ' o, ' . ACL_USERS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . " r ON (a.auth_role_id = r.role_id) - WHERE (a.auth_option_id = o.auth_option_id OR r.auth_option_id = o.auth_option_id) - $sql_permission_option - $sql_forum_id - AND u.user_id = a.user_id - ORDER BY u.username, u.user_regdate ASC"; + $sql = $db->sql_build_query('SELECT_DISTINCT', array( + 'SELECT' => 'u.username, u.user_regdate, u.user_id', + + 'FROM' => array( + USERS_TABLE => 'u', + ACL_OPTIONS_TABLE => 'o', + ACL_USERS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ) + ), + + 'WHERE' => "(a.auth_option_id = o.auth_option_id OR r.auth_option_id = o.auth_option_id) + $sql_permission_option + $sql_forum_id + AND u.user_id = a.user_id", + + 'ORDER_BY' => 'u.username, u.user_regdate ASC' + )); $result = $db->sql_query($sql); $s_defined_user_options = ''; @@ -363,14 +378,29 @@ class acp_permissions } $db->sql_freeresult($result); - $sql = 'SELECT DISTINCT g.group_type, g.group_name, g.group_id - FROM (' . GROUPS_TABLE . ' g, ' . ACL_OPTIONS_TABLE . ' o, ' . ACL_GROUPS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . " r ON (a.auth_role_id = r.role_id) - WHERE (a.auth_option_id = o.auth_option_id OR r.auth_option_id = o.auth_option_id) - $sql_permission_option - $sql_forum_id - AND g.group_id = a.group_id - ORDER BY g.group_type DESC, g.group_name ASC"; + $sql = $db->sql_build_query('SELECT_DISTINCT', array( + 'SELECT' => 'g.group_type, g.group_name, g.group_id', + + 'FROM' => array( + GROUPS_TABLE => 'g', + ACL_OPTIONS_TABLE => 'o', + ACL_USERS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ) + ), + + 'WHERE' => "(a.auth_option_id = o.auth_option_id OR r.auth_option_id = o.auth_option_id) + $sql_permission_option + $sql_forum_id + AND g.group_id = a.group_id", + + 'ORDER_BY' => 'g.group_type DESC, g.group_name ASC' + )); $result = $db->sql_query($sql); $s_defined_group_options = ''; diff --git a/phpBB/includes/auth.php b/phpBB/includes/auth.php index d32cb0a945..c31a45bc7f 100644 --- a/phpBB/includes/auth.php +++ b/phpBB/includes/auth.php @@ -486,14 +486,29 @@ class auth // First grab user settings ... each user has only one setting for each // option ... so we shouldn't need any ACL_NO checks ... he says ... - $sql = 'SELECT ao.auth_option, a.auth_role_id, r.auth_setting as role_auth_setting, a.user_id, a.forum_id, a.auth_setting - FROM (' . ACL_OPTIONS_TABLE . ' ao, ' . ACL_USERS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . ' r ON (a.auth_role_id = r.role_id) - WHERE (ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) - ' . (($sql_user) ? 'AND a.' . $sql_user : '') . " - $sql_forum - $sql_opts - ORDER BY a.forum_id, ao.auth_option"; + // Grab assigned roles... + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'ao.auth_option, a.auth_role_id, r.auth_setting as role_auth_setting, a.user_id, a.forum_id, a.auth_setting', + + 'FROM' => array( + ACL_OPTIONS_TABLE => 'ao', + ACL_USERS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ) + ), + + 'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) + ' . (($sql_user) ? 'AND a.' . $sql_user : '') . " + $sql_forum + $sql_opts", + + 'ORDER_BY' => 'a.forum_id, ao.auth_option' + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -504,15 +519,30 @@ class auth $db->sql_freeresult($result); // Now grab group settings ... ACL_NO overrides ACL_YES so act appropriatley - $sql = 'SELECT ug.user_id, ao.auth_option, a.forum_id, a.auth_setting, a.auth_role_id, r.auth_setting as role_auth_setting - FROM (' . USER_GROUP_TABLE . ' ug, ' . ACL_OPTIONS_TABLE . ' ao, ' . ACL_GROUPS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . ' r ON (a.auth_role_id = r.role_id) - WHERE (ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) - AND a.group_id = ug.group_id - ' . (($sql_user) ? 'AND ug.' . $sql_user : '') . " - $sql_forum - $sql_opts - ORDER BY a.forum_id, ao.auth_option"; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'ug.user_id, ao.auth_option, a.forum_id, a.auth_setting, a.auth_role_id, r.auth_setting as role_auth_setting', + + 'FROM' => array( + USER_GROUP_TABLE => 'ug', + ACL_OPTIONS_TABLE => 'ao', + ACL_GROUPS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ) + ), + + 'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) + AND a.group_id = ug.group_id + ' . (($sql_user) ? 'AND ug.' . $sql_user : '') . " + $sql_forum + $sql_opts", + + 'ORDER_BY' => 'a.forum_id, ao.auth_option' + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -571,14 +601,28 @@ class auth $hold_ary = array(); // Grab user settings... - $sql = 'SELECT ao.auth_option, a.auth_role_id, r.auth_setting as role_auth_setting, a.user_id, a.forum_id, a.auth_setting - FROM (' . ACL_OPTIONS_TABLE . ' ao, ' . ACL_USERS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . ' r ON (a.auth_role_id = r.role_id) - WHERE (ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) - ' . (($sql_user) ? 'AND a.' . $sql_user : '') . " - $sql_forum - $sql_opts - ORDER BY a.forum_id, ao.auth_option"; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'ao.auth_option, a.auth_role_id, r.auth_setting as role_auth_setting, a.user_id, a.forum_id, a.auth_setting', + + 'FROM' => array( + ACL_OPTIONS_TABLE => 'ao', + ACL_USERS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ), + ), + + 'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) + ' . (($sql_user) ? 'AND a.' . $sql_user : '') . " + $sql_forum + $sql_opts", + + 'ORDER_BY' => 'a.forum_id, ao.auth_option' + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -616,14 +660,28 @@ class auth $hold_ary = array(); // Grab group settings... - $sql = 'SELECT a.group_id, ao.auth_option, a.forum_id, a.auth_setting, a.auth_role_id, r.auth_setting as role_auth_setting - FROM (' . ACL_OPTIONS_TABLE . ' ao, ' . ACL_GROUPS_TABLE . ' a) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . ' r ON (a.auth_role_id = r.role_id) - WHERE (ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) - ' . (($sql_group) ? 'AND a.' . $sql_group : '') . " - $sql_forum - $sql_opts - ORDER BY a.forum_id, ao.auth_option"; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'a.group_id, ao.auth_option, a.forum_id, a.auth_setting, a.auth_role_id, r.auth_setting as role_auth_setting', + + 'FROM' => array( + ACL_OPTIONS_TABLE => 'ao', + ACL_USERS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ), + ), + + 'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id) + ' . (($sql_group) ? 'AND a.' . $sql_group : '') . " + $sql_forum + $sql_opts", + + 'ORDER_BY' => 'a.forum_id, ao.auth_option' + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php index acc64da799..1a8b8a4ddf 100644 --- a/phpBB/includes/db/dbal.php +++ b/phpBB/includes/db/dbal.php @@ -192,6 +192,67 @@ class dbal } /** + * Build sql statement from array for select and select distinct statements + * + * Possible query values: SELECT, SELECT_DISTINCT + */ + function sql_build_query($query, $array) + { + $sql = ''; + switch ($query) + { + case 'SELECT': + case 'SELECT_DISTINCT'; + + if ($query == 'SELECT_DISTINCT') + { + $sql .= 'SELECT DISTINCT'; + } + else + { + $sql .= 'SELECT'; + } + + $sql .= ' ' . $array['SELECT']; + $sql .= ' FROM '; + + $table_array = array(); + foreach ($array['FROM'] as $table_name => $alias) + { + $table_array[] = $table_name . ' ' . $alias; + } + + $sql .= $this->_sql_custom_build('FROM', implode(', ', $table_array)); + + if (!empty($array['LEFT_JOIN'])) + { + foreach ($array['LEFT_JOIN'] as $join) + { + $sql .= ' LEFT JOIN ' . key($join['FROM']) . ' ' . current($join['FROM']) . ' ON (' . $join['ON'] . ')'; + } + } + + if (!empty($array['WHERE'])) + { + $sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']); + } + + if (!empty($array['GROUP_BY'])) + { + $sql .= ' GROUP BY ' . $array['GROUP_BY']; + } + + if (!empty($array['ORDER_BY'])) + { + $sql .= ' ORDER BY ' . $array['ORDER_BY']; + } + + break; + } + return $sql; + } + + /** * display sql error page */ function sql_error($sql = '') diff --git a/phpBB/includes/db/firebird.php b/phpBB/includes/db/firebird.php index c94fd00882..e4eca60772 100644 --- a/phpBB/includes/db/firebird.php +++ b/phpBB/includes/db/firebird.php @@ -56,6 +56,7 @@ class dbal_firebird extends dbal switch ($status) { case 'begin': + $result = true; $this->transaction = true; break; @@ -90,7 +91,6 @@ class dbal_firebird extends dbal { global $cache; - $query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query); $this->last_query_text = $query; $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; @@ -320,6 +320,15 @@ class dbal_firebird extends dbal } /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** * return sql error array * @private */ @@ -360,7 +369,7 @@ class dbal_firebird extends dbal { // Take the time spent on parsing rows into account } - @ibase_freeresult($result); + @ibase_free_result($result); $splittime = explode(' ', microtime()); $splittime = $splittime[0] + $splittime[1]; diff --git a/phpBB/includes/db/mssql.php b/phpBB/includes/db/mssql.php index 0e7ee89196..e1082d282c 100644 --- a/phpBB/includes/db/mssql.php +++ b/phpBB/includes/db/mssql.php @@ -98,7 +98,6 @@ class dbal_mssql extends dbal { global $cache; - $query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query); // EXPLAIN only in extra debug mode if (defined('DEBUG_EXTRA')) @@ -334,6 +333,15 @@ class dbal_mssql extends dbal } /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** * Close sql connection * @private */ diff --git a/phpBB/includes/db/mssql_odbc.php b/phpBB/includes/db/mssql_odbc.php index 1e219f985e..a31c6074bc 100644 --- a/phpBB/includes/db/mssql_odbc.php +++ b/phpBB/includes/db/mssql_odbc.php @@ -101,7 +101,6 @@ class dbal_mssql_odbc extends dbal $this->sql_report('start', $query); } - $query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query); $this->last_query_text = $query; $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false; @@ -318,6 +317,15 @@ class dbal_mssql_odbc extends dbal } /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** * return sql error array * @private */ diff --git a/phpBB/includes/db/mysql.php b/phpBB/includes/db/mysql.php index 625c6876ee..d9369f3a75 100644 --- a/phpBB/includes/db/mysql.php +++ b/phpBB/includes/db/mysql.php @@ -290,6 +290,21 @@ class dbal_mysql extends dbal return @mysql_real_escape_string($msg, $this->db_connect_id); } + + /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + switch ($stage) + { + case 'FROM': + $data = '(' . $data . ')'; + break; + } + return $data; + } /** * return sql error array diff --git a/phpBB/includes/db/mysql4.php b/phpBB/includes/db/mysql4.php index c388987e40..47c1ebc41f 100644 --- a/phpBB/includes/db/mysql4.php +++ b/phpBB/includes/db/mysql4.php @@ -293,6 +293,21 @@ class dbal_mysql4 extends dbal return @mysql_real_escape_string($msg, $this->db_connect_id); } + + /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + switch ($stage) + { + case 'FROM': + $data = '(' . $data . ')'; + break; + } + return $data; + } /** * return sql error array diff --git a/phpBB/includes/db/mysqli.php b/phpBB/includes/db/mysqli.php index 137ca39591..0233c7092f 100644 --- a/phpBB/includes/db/mysqli.php +++ b/phpBB/includes/db/mysqli.php @@ -284,6 +284,21 @@ class dbal_mysqli extends dbal { return @mysqli_real_escape_string($this->db_connect_id, $msg); } + + /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + switch ($stage) + { + case 'FROM': + $data = '(' . $data . ')'; + break; + } + return $data; + } /** * return sql error array diff --git a/phpBB/includes/db/oracle.php b/phpBB/includes/db/oracle.php index 2a4898ca0d..72732d2a50 100644 --- a/phpBB/includes/db/oracle.php +++ b/phpBB/includes/db/oracle.php @@ -90,7 +90,6 @@ class dbal_oracle extends dbal { global $cache; - $query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query); // EXPLAIN only in extra debug mode if (defined('DEBUG_EXTRA')) @@ -361,6 +360,11 @@ class dbal_oracle extends dbal return str_replace("'", "''", str_replace('\\', '\\\\', $msg)); } + function _sql_custom_build($stage, $data) + { + return $data; + } + /** * return sql error array * @private diff --git a/phpBB/includes/db/postgres.php b/phpBB/includes/db/postgres.php index d9e8bc2bfa..eb5f2d17e8 100644 --- a/phpBB/includes/db/postgres.php +++ b/phpBB/includes/db/postgres.php @@ -127,10 +127,6 @@ class dbal_postgres extends dbal { global $cache; - if (strpos($query, 'SELECT') === 0 && strpos($query, 'FROM (') !== false) - { - $query = preg_replace('#FROM \(([^)]+)\)\s#', 'FROM \1 ', $query); - } // EXPLAIN only in extra debug mode if (defined('DEBUG_EXTRA')) @@ -145,7 +141,7 @@ class dbal_postgres extends dbal { $this->num_queries++; - if (($this->query_result = @pg_exec($this->db_connect_id, $query)) === false) + if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false) { $this->sql_error($query); } @@ -179,6 +175,15 @@ class dbal_postgres extends dbal } /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** * Build LIMIT query */ function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0) diff --git a/phpBB/includes/db/sqlite.php b/phpBB/includes/db/sqlite.php index 16319aec83..f2f3ceb1f1 100644 --- a/phpBB/includes/db/sqlite.php +++ b/phpBB/includes/db/sqlite.php @@ -94,7 +94,6 @@ class dbal_sqlite extends dbal { global $cache; - $query = preg_replace('#FROM \(([^)]*)\)(,|[\n\r\t ]+(?:WHERE|LEFT JOIN)) #', 'FROM \1\2 ', $query); // EXPLAIN only in extra debug mode if (defined('DEBUG_EXTRA')) @@ -286,6 +285,15 @@ class dbal_sqlite extends dbal } /** + * Build db-specific query data + * @private + */ + function _sql_custom_build($stage, $data) + { + return $data; + } + + /** * Close sql connection * @private */ diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php index 7b3c7e68f1..2df3c37f9b 100644 --- a/phpBB/includes/functions_admin.php +++ b/phpBB/includes/functions_admin.php @@ -1733,15 +1733,29 @@ function cache_moderators() $ug_id_ary = array_keys($hold_ary); // Remove users who have group memberships with DENY moderator permissions - $sql = 'SELECT a.forum_id, ug.user_id - FROM (' . ACL_OPTIONS_TABLE . ' o, ' . ACL_GROUPS_TABLE . ' a, ' . USER_GROUP_TABLE . ' ug) - LEFT JOIN ' . ACL_ROLES_DATA_TABLE . ' r ON (a.auth_role_id = r.role_id) - WHERE (o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id) - AND ((a.auth_setting = ' . ACL_NO . ' AND r.auth_setting IS NULL) - OR r.auth_setting = ' . ACL_NO . ') - AND a.group_id = ug.group_id - AND ug.user_id IN (' . implode(', ', $ug_id_ary) . ") - AND o.auth_option LIKE 'm\_%'"; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'a.forum_id, ug.user_id', + + 'FROM' => array( + ACL_OPTIONS_TABLE => 'o', + USER_GROUP_TABLE => 'ug', + ACL_GROUPS_TABLE => 'a' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), + 'ON' => 'a.auth_role_id = r.role_id' + ) + ), + + 'WHERE' => '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id) + AND ((a.auth_setting = ' . ACL_NO . ' AND r.auth_setting IS NULL) + OR r.auth_setting = ' . ACL_NO . ') + AND a.group_id = ug.group_id + AND ug.user_id IN (' . implode(', ', $ug_id_ary) . ") + AND o.auth_option LIKE 'm\_%'", + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/includes/mcp/mcp_front.php b/phpBB/includes/mcp/mcp_front.php index 653af87a45..f227dec9a6 100644 --- a/phpBB/includes/mcp/mcp_front.php +++ b/phpBB/includes/mcp/mcp_front.php @@ -123,16 +123,34 @@ function mcp_front_view($id, $mode, $action) if ($total) { - $sql = 'SELECT r.*, p.post_id, p.post_subject, u.username, t.topic_id, t.topic_title, f.forum_id, f.forum_name - FROM (' . REPORTS_TABLE . ' r, ' . REASONS_TABLE . ' rr,' . POSTS_TABLE . ' p, ' . TOPICS_TABLE . ' t, ' . USERS_TABLE . ' u) - LEFT JOIN ' . FORUMS_TABLE . ' f ON (f.forum_id = p.forum_id) - WHERE r.post_id = p.post_id - AND r.report_closed = 0 - AND r.reason_id = rr.reason_id - AND p.topic_id = t.topic_id - AND r.user_id = u.user_id - AND p.forum_id IN (0, ' . implode(', ', $forum_list) . ') - ORDER BY p.post_id DESC'; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'r.*, p.post_id, p.post_subject, u.username, t.topic_id, t.topic_title, f.forum_id, f.forum_name', + + 'FROM' => array( + REPORTS_TABLE => 'r', + REASONS_TABLE => 'rr', + TOPICS_TABLE => 't', + USERS_TABLE => 'u', + POSTS_TABLE => 'p' + + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(FORUMS_TABLE => 'f'), + 'ON' => 'f.forum_id = p.forum_id' + ) + ), + + 'WHERE' => 'r.post_id = p.post_id + AND r.report_closed = 0 + AND r.reason_id = rr.reason_id + AND p.topic_id = t.topic_id + AND r.user_id = u.user_id + AND p.forum_id IN (0, ' . implode(', ', $forum_list) . ')', + + 'ORDER_BY' => 'p.post_id DESC' + )); $result = $db->sql_query_limit($sql, 5); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/includes/ucp/ucp_main.php b/phpBB/includes/ucp/ucp_main.php index 7959e656de..2577e1e781 100644 --- a/phpBB/includes/ucp/ucp_main.php +++ b/phpBB/includes/ucp/ucp_main.php @@ -216,25 +216,36 @@ class ucp_main } } + $sql_array = array( + 'SELECT' => 'f.*', + + 'FROM' => array( + FORUMS_WATCH_TABLE => 'fw', + FORUMS_TABLE => 'f' + ), + + 'WHERE' => "fw.user_id = " . $user->data['user_id'] . ' + AND f.forum_id = fw.forum_id', + + 'ORDER_BY' => 'left_id' + ); + if ($config['load_db_lastread']) { - $sql_join = ' LEFT JOIN ' . FORUMS_TRACK_TABLE . ' ft ON (ft.user_id = ' . $user->data['user_id'] . ' AND ft.forum_id = f.forum_id)'; - $lastread_select = ', ft.mark_time '; + $sql_array['LEFT_JOIN'] = array( + array( + 'FROM' => array(FORUMS_TRACK_TABLE => 'ft'), + 'ON' => 'ft.user_id = ' . $user->data['user_id'] . ' AND ft.forum_id = f.forum_id' + ) + ); + $sql_array['SELECT'] .= ', ft.mark_time '; } else { - $sql_join = ''; - $lastread_select = ''; - $tracking_topics = (isset($_COOKIE[$config['cookie_name'] . '_track'])) ? unserialize(stripslashes($_COOKIE[$config['cookie_name'] . '_track'])) : array(); } - $sql = "SELECT f.*$lastread_select - FROM (" . FORUMS_TABLE . ' f, ' . FORUMS_WATCH_TABLE . " fw) - $sql_join - WHERE fw.user_id = " . $user->data['user_id'] . ' - AND f.forum_id = fw.forum_id - ORDER BY left_id'; + $sql = $db->sql_build_query('SELECT', $sql_array); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) @@ -315,31 +326,34 @@ class ucp_main ); } - $sql_join = ($config['load_db_lastread']) ? ' LEFT JOIN ' . FORUMS_TRACK_TABLE . ' ft ON (ft.forum_id = t.forum_id AND ft.user_id = ' . $user->data['user_id'] . ')' : ''; - $sql_f_select = ($config['load_db_lastread']) ? ', ft.mark_time AS forum_mark_time' : ''; - $sql_t_select = ''; + $sql_array = array( + 'SELECT' => 't.*', - if ($config['load_db_track']) - { - $sql_join .= ' LEFT JOIN ' . TOPICS_POSTED_TABLE . ' tp ON (tp.topic_id = t.topic_id - AND tp.user_id = ' . $user->data['user_id'] . ')'; - $sql_t_select .= ', tp.topic_posted'; - } + 'FROM' => array( + TOPICS_WATCH_TABLE => 'tw', + TOPICS_TABLE => 't' + ), + + 'WHERE' => "tw.user_id = " . $user->data['user_id'] . ' + AND t.topic_id = tw.topic_id', + + 'ORDER_BY' => 't.topic_last_post_time DESC' + ); if ($config['load_db_lastread']) { - $sql_join .= ' LEFT JOIN ' . TOPICS_TRACK_TABLE . ' tt ON (tt.topic_id = t.topic_id - AND tt.user_id = ' . $user->data['user_id'] . ')'; - $sql_t_select .= ', tt.mark_time'; + $sql_array['LEFT_JOIN'][] = array('FROM' => array(FORUMS_TRACK_TABLE => 'ft'), 'ON' => 'ft.forum_id = t.forum_id AND ft.user_id = ' . $user->data['user_id']); + $sql_array['LEFT_JOIN'][] = array('FROM' => array(TOPICS_TRACK_TABLE => 'tt'), 'ON' => 'tt.topic_id = t.topic_id AND tt.user_id = ' . $user->data['user_id']); + $sql_array['SELECT'] .= ', tt.mark_time, ft.mark_time AS forum_mark_time'; } + if ($config['load_db_track']) + { + $sql_array['LEFT_JOIN'][] = array('FROM' => array(TOPICS_POSTED_TABLE => 'tp'), 'ON' => 'tp.topic_id = t.topic_id AND tp.user_id = ' . $user->data['user_id']); + $sql_array['SELECT'] .= ', tp.topic_posted'; + } - $sql = "SELECT t.* $sql_f_select $sql_t_select - FROM (" . TOPICS_WATCH_TABLE . ' tw, ' . TOPICS_TABLE . " t) - $sql_join - WHERE tw.user_id = " . $user->data['user_id'] . ' - AND t.topic_id = tw.topic_id - ORDER BY t.topic_last_post_time DESC'; + $sql = $db->sql_build_query('SELECT', $sql_array); $result = $db->sql_query_limit($sql, $config['topics_per_page'], $start); $topic_list = $topic_forum_list = $global_announce_list = $rowset = array(); diff --git a/phpBB/mcp.php b/phpBB/mcp.php index 8c6c94fa3e..eb2dd03085 100644 --- a/phpBB/mcp.php +++ b/phpBB/mcp.php @@ -258,12 +258,26 @@ function get_post_data($post_ids, $acl_list = false) global $db, $auth; $rowset = array(); - $sql = 'SELECT p.*, u.*, t.*, f.* - FROM (' . USERS_TABLE . ' u, ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . ' p) - LEFT JOIN ' . FORUMS_TABLE . ' f ON (f.forum_id = p.forum_id) - WHERE p.post_id IN (' . implode(', ', $post_ids) . ') - AND u.user_id = p.poster_id - AND t.topic_id = p.topic_id'; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'p.*, u.*, t.*, f.*', + + 'FROM' => array( + USERS_TABLE => 'u', + TOPICS_TABLE => 't', + POSTS_TABLE => 'p' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(FORUMS_TABLE => 'f'), + 'ON' => 'f.forum_id = p.forum_id' + ) + ), + + 'WHERE' => 'p.post_id IN (' . implode(', ', $post_ids) . ') + AND u.user_id = p.poster_id + AND t.topic_id = p.topic_id', + )); $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/memberlist.php b/phpBB/memberlist.php index f4f4284e36..2f276a98bb 100644 --- a/phpBB/memberlist.php +++ b/phpBB/memberlist.php @@ -113,12 +113,27 @@ switch ($mode) } $db->sql_freeresult($result); - $sql = 'SELECT u.user_id, u.username, u.user_colour, u.user_rank, u.user_posts, g.group_id, g.group_name, g.group_colour, g.group_type, ug.user_id as ug_user_id - FROM (' . USERS_TABLE . ' u, ' . GROUPS_TABLE . ' g) - LEFT JOIN ' . USER_GROUP_TABLE . ' ug ON (ug.group_id = g.group_id AND ug.user_id = ' . $user->data['user_id'] . ') - WHERE u.user_id IN (' . implode(', ', array_unique(array_merge($admin_id_ary, $mod_id_ary))) . ') - AND u.group_id = g.group_id - ORDER BY g.group_name ASC, u.username ASC'; + $sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'u.user_id, u.username, u.user_colour, u.user_rank, u.user_posts, g.group_id, g.group_name, g.group_colour, g.group_type, ug.user_id as ug_user_id', + + 'FROM' => array( + USERS_TABLE => 'u', + GROUPS_TABLE => 'g' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(USER_GROUP_TABLE => 'ug'), + 'ON' => 'ug.group_id = g.group_id AND ug.user_id = ' . $user->data['user_id'] + ) + ), + + 'WHERE' => 'u.user_id IN (' . implode(', ', array_unique(array_merge($admin_id_ary, $mod_id_ary))) . ') + AND u.group_id = g.group_id', + + 'GROUP_BY' => 'g.group_name ASC, u.username ASC' + )); + $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/ucp.php b/phpBB/ucp.php index 95e2d55b78..36b7afadfc 100755 --- a/phpBB/ucp.php +++ b/phpBB/ucp.php @@ -244,13 +244,28 @@ if (!$user->data['is_registered']) // Output listing of friends online $update_time = $config['load_online_time'] * 60; -$sql = 'SELECT DISTINCT u.user_id, u.username, u.user_allow_viewonline, MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline - FROM (' . USERS_TABLE . ' u, ' . ZEBRA_TABLE . ' z) - LEFT JOIN ' . SESSIONS_TABLE . ' s ON (s.session_user_id = z.zebra_id) - WHERE z.user_id = ' . $user->data['user_id'] . ' - AND z.friend = 1 - AND u.user_id = z.zebra_id - GROUP BY z.zebra_id, u.user_id, u.username, u.user_allow_viewonline'; +$sql = $db->sql_build_query('SELECT_DISTINCT', array( + 'SELECT' => 'u.user_id, u.username, u.user_allow_viewonline, MAX(s.session_time) as online_time, MIN(s.session_viewonline) AS viewonline', + + 'FROM' => array( + USERS_TABLE => 'u', + ZEBRA_TABLE => 'z' + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(SESSIONS_TABLE => 's'), + 'ON' => 's.session_user_id = z.zebra_id' + ) + ), + + 'WHERE' => 'z.user_id = ' . $user->data['user_id'] . ' + AND z.friend = 1 + AND u.user_id = z.zebra_id', + + 'GROUP_BY' => 'z.zebra_id, u.user_id, u.username, u.user_allow_viewonline' +)); + $result = $db->sql_query($sql); while ($row = $db->sql_fetchrow($result)) diff --git a/phpBB/viewtopic.php b/phpBB/viewtopic.php index 9ff1b4bd48..bf9d7e15f4 100644 --- a/phpBB/viewtopic.php +++ b/phpBB/viewtopic.php @@ -103,11 +103,10 @@ if ($view && !$post_id) $sql_ordering = ($view == 'next') ? 'ASC' : 'DESC'; $sql = 'SELECT t.topic_id, t.forum_id - FROM ' . TOPICS_TABLE . ' t, ' . TOPICS_TABLE . " t2 - WHERE t2.topic_id = $topic_id - AND t.forum_id = t2.forum_id + FROM ' . TOPICS_TABLE . ' t + LEFT JOIN ' . TOPICS_TABLE . " t2 ON (t2.topic_id = $topic_id AND t.forum_id = t2.forum_id) + WHERE t.topic_last_post_time $sql_condition t2.topic_last_post_time " . (($auth->acl_get('m_approve', $forum_id)) ? '' : 'AND t.topic_approved = 1') . " - AND t.topic_last_post_time $sql_condition t2.topic_last_post_time ORDER BY t.topic_last_post_time $sql_ordering"; $result = $db->sql_query_limit($sql, 1); $row = $db->sql_fetchrow($result); @@ -157,64 +156,81 @@ if ($view && !$post_id) // This rather complex gaggle of code handles querying for topics but // also allows for direct linking to a post (and the calculation of which // page the post is on and the correct display of viewtopic) -$join_sql_table = ''; +$group = ''; $select_sql = 't.topic_id, t.forum_id, t.topic_title, t.topic_attachment, t.topic_status, t.topic_approved, t.topic_replies_real, t.topic_replies, t.topic_first_post_id, t.topic_last_post_id, t.topic_last_poster_id, t.topic_last_post_time, t.topic_poster, t.topic_time, t.topic_time_limit, t.topic_type, t.topic_bumped, t.topic_bumper, t.poll_max_options, t.poll_start, t.poll_length, t.poll_title, t.poll_vote_change, f.forum_name, f.forum_desc, f.forum_desc_uid, f.forum_desc_bitfield, f.forum_parents, f.parent_id, f.left_id, f.right_id, f.forum_status, f.forum_type, f.forum_id, f.forum_style, f.forum_password, f.forum_rules, f.forum_rules_link, f.forum_rules_uid, f.forum_rules_bitfield'; -if (!$post_id) -{ - $join_sql = "t.topic_id = $topic_id"; -} -else -{ - $join_sql = "p.post_id = $post_id" . ((!$auth->acl_get('m_approve', $forum_id)) ? ' AND p.post_approved = 1' : '') . ' AND t.topic_id = p.topic_id AND p2.topic_id = p.topic_id' . ((!$auth->acl_get('m_approve', $forum_id)) ? ' AND p2.post_approved = 1' : ''); +$sql_array = array( + 'SELECT' => $select_sql, + + 'FROM' => array( + FORUMS_TABLE => 'f', + ) +); + +$left_join = array(); - // This is for determining where we are (page) - $join_sql .= ($sort_dir == 'd') ? " AND p2.post_id >= $post_id" : " AND p2.post_id <= $post_id"; -} -$extra_fields = (!$post_id) ? '' : ', COUNT(p2.post_id) AS prev_posts'; -$extra_sql = ''; if ($user->data['is_registered']) { - $extra_fields .= ', tw.notify_status'; - $extra_sql .= ', tw.notify_status'; - $join_sql_table .= ' LEFT JOIN ' . TOPICS_WATCH_TABLE . ' tw ON (tw.user_id = ' . $user->data['user_id'] . ' - AND t.topic_id = tw.topic_id)'; + $sql_array['SELECT'] .= ', tw.notify_status'; + $group .= ', tw.notify_status'; + $left_join[] = array( + 'FROM' => array(TOPICS_WATCH_TABLE => 'tw'), + 'ON' => 'tw.user_id = ' . $user->data['user_id'] . ' AND t.topic_id = tw.topic_id' + ); if ($config['allow_bookmarks']) { - $extra_fields .= ', bm.order_id as bookmarked'; - $extra_sql .= ', bm.order_id'; - $join_sql_table .= ' LEFT JOIN ' . BOOKMARKS_TABLE . ' bm ON (bm.user_id = ' . $user->data['user_id'] . ' - AND t.topic_id = bm.topic_id)'; + $sql_array['SELECT'] .= ', bm.order_id as bookmarked'; + $group .= ', bm.order_id'; + $left_join[] = array( + 'FROM' => array(BOOKMARKS_TABLE => 'bm'), + 'ON' => 'bm.user_id = ' . $user->data['user_id'] . ' AND t.topic_id = bm.topic_id' + ); } if ($config['load_db_lastread']) { - $extra_fields .= ', tt.mark_time, ft.mark_time as forum_mark_time'; - $extra_sql .= ', tt.mark_time, ft.mark_time'; - $join_sql_table .= ' LEFT JOIN ' . TOPICS_TRACK_TABLE . ' tt ON (tt.user_id = ' . $user->data['user_id'] . ' - AND t.topic_id = tt.topic_id)'; - - $join_sql_table .= ' LEFT JOIN ' . FORUMS_TRACK_TABLE . ' ft ON (ft.user_id = ' . $user->data['user_id'] . ' - AND t.forum_id = ft.forum_id)'; + $sql_array['SELECT'] .= ', tt.mark_time, ft.mark_time as forum_mark_time'; + $group .= ', tt.mark_time, ft.mark_time'; + $left_join[] = array( + 'FROM' => array(TOPICS_TRACK_TABLE => 'tt'), + 'ON' => 'tt.user_id = ' . $user->data['user_id'] . ' AND t.topic_id = tt.topic_id' + ); + $left_join[] = array( + 'FROM' => array(FORUMS_TRACK_TABLE => 'ft'), + 'ON' => 'ft.user_id = ' . $user->data['user_id'] . ' AND t.forum_id = ft.forum_id' + ); } } -$order_sql = (!$post_id) ? '' : 'GROUP BY p.post_id, ' . $select_sql . $extra_sql . ' ORDER BY p.post_id ' . (($sort_dir == 'd') ? 'DESC' : 'ASC'); +if (!$post_id) +{ + $sql_array['WHERE'] = "t.topic_id = $topic_id"; +} +else +{ + $sql_array['WHERE'] = "p.post_id = $post_id AND t.topic_id = p.topic_id" . ((!$auth->acl_get('m_approve', $forum_id)) ? ' AND p.post_approved = 1' : ''); + $sql_array['SELECT'] .= ', COUNT(p2.post_id) AS prev_posts'; + $sql_array['GROUP_BY'] = 'p.post_id, ' . $select_sql . $group; + $sql_array['ORDER_BY'] = 'p.post_id ' . (($sort_dir == 'd') ? 'DESC' : 'ASC'); + $sql_array['FROM'][POSTS_TABLE] = 'p'; + + $left_join[] = array( + 'FROM' => array(POSTS_TABLE => 'p2'), + 'ON' => 'p2.topic_id = t.topic_id AND p2.post_approved = 1 ' . (($sort_dir == 'd') ? "AND p2.post_id >= $post_id" : "AND p2.post_id <= $post_id") + ); +} +$sql_array['LEFT_JOIN'] = $left_join; +$sql_array['WHERE'] .= ' AND (f.forum_id = t.forum_id ' . ((!$forum_id) ? 'OR t.topic_type = ' . POST_GLOBAL : 'OR (t.topic_type = ' . POST_GLOBAL . " AND f.forum_id = $forum_id)") . ')'; +$sql_array['FROM'][TOPICS_TABLE] = 't'; // Join to forum table on topic forum_id unless topic forum_id is zero // whereupon we join on the forum_id passed as a parameter ... this // is done so navigation, forum name, etc. remain consistent with where // user clicked to view a global topic -$sql = "SELECT $select_sql $extra_fields - FROM (" . FORUMS_TABLE . ' f' . ((!$post_id) ? '' : ', ' . POSTS_TABLE . ' p, ' . POSTS_TABLE . ' p2') . ', ' . TOPICS_TABLE . ' t) ' . - $join_sql_table . " - WHERE $join_sql - AND (f.forum_id = t.forum_id - " . ((!$forum_id) ? ' OR t.topic_type = ' . POST_GLOBAL : 'OR (t.topic_type = ' . POST_GLOBAL . " AND f.forum_id = $forum_id)") . " - ) - $order_sql"; +$sql = $db->sql_build_query('SELECT', $sql_array); + $result = $db->sql_query($sql); if (!($topic_data = $db->sql_fetchrow($result))) @@ -775,11 +791,25 @@ if (!sizeof($post_list)) // We need to grab it because we do reverse ordering sometimes $max_post_time = 0; -$sql = 'SELECT u.username, u.user_id, u.user_colour, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_jabber, u.user_regdate, u.user_msnm, u.user_allow_viewemail, u.user_allow_viewonline, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_sig_bbcode_bitfield, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, u.user_warnings, z.friend, z.foe, p.* - FROM (' . USERS_TABLE . ' u, ' . POSTS_TABLE . ' p) - LEFT JOIN ' . ZEBRA_TABLE . ' z ON (z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id) - WHERE p.post_id IN (' . implode(', ', $post_list) . ') - AND u.user_id = p.poster_id'; +$sql = $db->sql_build_query('SELECT', array( + 'SELECT' => 'u.username, u.user_id, u.user_colour, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_jabber, u.user_regdate, u.user_msnm, u.user_allow_viewemail, u.user_allow_viewonline, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_sig_bbcode_bitfield, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, u.user_warnings, z.friend, z.foe, p.*', + + 'FROM' => array( + USERS_TABLE => 'u', + POSTS_TABLE => 'p', + ), + + 'LEFT_JOIN' => array( + array( + 'FROM' => array(ZEBRA_TABLE => 'z'), + 'ON' => 'z.user_id = ' . $user->data['user_id'] . ' AND z.zebra_id = p.poster_id' + ) + ), + + 'WHERE' => 'p.post_id IN (' . implode(', ', $post_list) . ') + AND u.user_id = p.poster_id' +)); + $result = $db->sql_query($sql); // Posts are stored in the $rowset array while $attach_list, $user_cache |