diff options
Diffstat (limited to 'activerecord/lib/active_record/connection_adapters')
3 files changed, 137 insertions, 1 deletions
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index 306b185c5e..1146323147 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -228,6 +228,80 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== + def explain(arel) + sql = "EXPLAIN #{to_sql(arel)}" + start = Time.now + result = exec_query(sql, 'EXPLAIN') + elapsed = Time.now - start + + ExplainPrettyPrinter.new.pp(result, elapsed) + end + + class ExplainPrettyPrinter # :nodoc: + # Pretty prints the result of a EXPLAIN in a way that resembles the output of the + # MySQL shell: + # + # +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ + # | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + # +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ + # | 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | | + # | 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 1 | Using where | + # +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ + # 2 rows in set (0.00 sec) + # + # This is an exercise in Ruby hyperrealism :). + def pp(result, elapsed) + widths = compute_column_widths(result) + separator = build_separator(widths) + + pp = [] + + pp << separator + pp << build_cells(result.columns, widths) + pp << separator + + result.rows.each do |row| + pp << build_cells(row, widths) + end + + pp << separator + pp << build_footer(result.rows.length, elapsed) + + pp.join("\n") + "\n" + end + + private + + def compute_column_widths(result) + [].tap do |widths| + result.columns.each_with_index do |column, i| + cells_in_column = [column] + result.rows.map {|r| r[i].nil? ? 'NULL' : r[i].to_s} + widths << cells_in_column.map(&:length).max + end + end + end + + def build_separator(widths) + # Each cell has one char of padding at both sides, that's why we add 2. + '+' + widths.map {|w| '-' * (w + 2)}.join('+') + end + + def build_cells(items, widths) + cells = [] + items.each_with_index do |item, i| + item = 'NULL' if item.nil? + justifier = item.is_a?(Numeric) ? 'rjust' : 'ljust' + cells << item.to_s.send(justifier, widths[i]) + end + '| ' + cells.join(' | ') + ' |' + end + + def build_footer(nrows, elapsed) + rows_label = nrows == 1 ? 'row' : 'rows' + "#{nrows} #{rows_label} in set (%.2f sec)" % elapsed + end + end + # Executes the SQL statement in the context of this connection. def execute(sql, name = nil) if name == :skip_logging @@ -290,6 +364,7 @@ module ActiveRecord # these, we must use a subquery. However, MySQL is too stupid to create a # temporary table for this automatically, so we have to give it some prompting # in the form of a subsubquery. Ugh! + def join_to_update(update, select) #:nodoc: if select.limit || select.offset || select.orders.any? subsubselect = select.clone diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 15e329a1c8..44c2fa439c 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -517,6 +517,48 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== + def explain(arel) + sql = "EXPLAIN #{to_sql(arel)}" + ExplainPrettyPrinter.new.pp(exec_query(sql)) + end + + class ExplainPrettyPrinter # :nodoc: + # Pretty prints the result of a EXPLAIN in a way that resembles the output of the + # PostgreSQL shell: + # + # QUERY PLAN + # ------------------------------------------------------------------------------ + # Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0) + # Join Filter: (posts.user_id = users.id) + # -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4) + # Index Cond: (id = 1) + # -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4) + # Filter: (posts.user_id = 1) + # (6 rows) + # + def pp(result) + header = result.columns.first + lines = result.rows.map(&:first) + + # We add 2 because there's one char of padding at both sides, note + # the extra hyphens in the example above. + width = [header, *lines].map(&:length).max + 2 + + pp = [] + + pp << header.center(width).rstrip + pp << '-' * width + + pp += lines.map {|line| " #{line}"} + + nrows = result.rows.length + rows_label = nrows == 1 ? 'row' : 'rows' + pp << "(#{nrows} #{rows_label})" + + pp.join("\n") + "\n" + end + end + # Executes a SELECT query and returns an array of rows. Each row is an # array of field values. def select_rows(sql, name = nil) @@ -836,7 +878,7 @@ module ActiveRecord # Returns the active schema search path. def schema_search_path - @schema_search_path ||= query('SHOW search_path')[0][0] + @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0] end # Returns the current client message level. diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb index caecbc9b3a..35df0a1542 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb @@ -222,6 +222,25 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== + def explain(arel) + sql = "EXPLAIN QUERY PLAN #{to_sql(arel)}" + ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN')) + end + + class ExplainPrettyPrinter + # Pretty prints the result of a EXPLAIN QUERY PLAN in a way that resembles + # the output of the SQLite shell: + # + # 0|0|0|SEARCH TABLE users USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) + # 0|1|1|SCAN TABLE posts (~100000 rows) + # + def pp(result) # :nodoc: + result.rows.map do |row| + row.join('|') + end.join("\n") + "\n" + end + end + def exec_query(sql, name = nil, binds = []) log(sql, name, binds) do |