diff options
author | Jeremy Kemper <jeremy@bitsweat.net> | 2007-09-16 03:32:47 +0000 |
---|---|---|
committer | Jeremy Kemper <jeremy@bitsweat.net> | 2007-09-16 03:32:47 +0000 |
commit | 30fb7b8c8bfc72ed3097352539544c07cbb38d0d (patch) | |
tree | 10b9d6b1d6ec2c792b8a503bf297a6a4e196ae68 /activerecord | |
parent | 78da29a84f886386c73768fa60609a0090a3a250 (diff) | |
download | rails-30fb7b8c8bfc72ed3097352539544c07cbb38d0d.tar.gz rails-30fb7b8c8bfc72ed3097352539544c07cbb38d0d.tar.bz2 rails-30fb7b8c8bfc72ed3097352539544c07cbb38d0d.zip |
connection.select_rows 'sql' returns an array (rows) of arrays (field values). Closes #2329.
git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@7497 5ecf4fe2-1ee6-0310-87b1-e25e094e27de
Diffstat (limited to 'activerecord')
8 files changed, 91 insertions, 9 deletions
diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index e4585b46b1..f2c0b4642d 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* connection.select_rows 'sql' returns an array (rows) of arrays (field values). #2329 [Michael Schuerig] + * Eager loading respects explicit :joins. #9496 [dasil003] * Extract Firebird, FrontBase, and OpenBase adapters into gems. #9508, #9509, #9510 [Jeremy Kemper] diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb index 75dafe61da..effb36faf1 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb @@ -23,10 +23,14 @@ module ActiveRecord # Returns an array of the values of the first column in a select: # select_values("SELECT id FROM companies LIMIT 3") => [1,2,3] def select_values(sql, name = nil) - result = select_all(sql, name) - result.map{ |v| v.values.first } + result = select_rows(sql, name) + result.map { |v| v[0] } end + # Returns an array of arrays containing the field values. + # Order is the same as that returned by #columns. + def select_rows(sql, name = nil) end + # Executes the SQL statement in the context of this connection. def execute(sql, name = nil) raise NotImplementedError, "execute is an abstract method" diff --git a/activerecord/lib/active_record/connection_adapters/db2_adapter.rb b/activerecord/lib/active_record/connection_adapters/db2_adapter.rb index 7a3cd22789..c496132fc0 100644 --- a/activerecord/lib/active_record/connection_adapters/db2_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/db2_adapter.rb @@ -48,6 +48,21 @@ begin end end + def select_rows(sql, name = nil) + stmt = nil + log(sql, name) do + stmt = DB2::Statement.new(@connection) + stmt.exec_direct("#{sql.gsub(/=\s*null/i, 'IS NULL')} with ur") + end + + rows = [] + while row = stmt.fetch + rows << row + end + stmt.free + rows + end + def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) execute(sql, name = nil) id_value || last_insert_id diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb index 47a1b355f8..7cf8f478d7 100755 --- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb @@ -247,6 +247,15 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== + def select_rows(sql, name = nil) + @connection.query_with_result = true + result = execute(sql, name) + rows = [] + result.each { |row| rows << row } + result.free + rows + end + def execute(sql, name = nil) #:nodoc: log(sql, name) { @connection.query(sql) } rescue ActiveRecord::StatementInvalid => exception diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 4a48bf3a28..b14acc1e68 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -367,6 +367,12 @@ module ActiveRecord # DATABASE STATEMENTS ====================================== + # Executes a SELECT query and returns an array of rows. Each row is an + # array of field values. + def select_rows(sql, name = nil) + select_raw(sql, name).last + end + # Executes an INSERT query and returns the new record's ID def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) execute(sql, name) @@ -755,16 +761,28 @@ module ActiveRecord # Executes a SELECT query and returns the results, performing any data type # conversions that require to be performed here instead of in PostgreSQLColumn. def select(sql, name = nil) + fields, rows = select_raw(sql, name) + result = [] + for row in rows + row_hash = {} + fields.each_with_index do |f, i| + row_hash[f] = row[i] + end + result << row_hash + end + result + end + + def select_raw(sql, name = nil) res = execute(sql, name) results = res.result + fields = [] rows = [] if results.length > 0 fields = res.fields results.each do |row| hashed_row = {} row.each_index do |cell_index| - column = row[cell_index] - # If this is a money type column and there are any currency symbols, # then strip them off. Indeed it would be prettier to do this in # PostgresSQLColumn.string_to_decimal but would break form input @@ -774,21 +792,21 @@ module ActiveRecord # cases to consider (note the decimal seperators): # (1) $12,345,678.12 # (2) $12.345.678,12 - case column + case column = row[cell_index] when /^-?\D+[\d,]+\.\d{2}$/ # (1) - column = column.gsub(/[^-\d\.]/, '') + row[cell_index] = column.gsub(/[^-\d\.]/, '') when /^-?\D+[\d\.]+,\d{2}$/ # (2) - column = column.gsub(/[^-\d,]/, '').sub(/,/, '.') + row[cell_index] = column.gsub(/[^-\d,]/, '').sub(/,/, '.') end end hashed_row[fields[cell_index]] = column end - rows << hashed_row + rows << row end end res.clear - return rows + return fields, rows end # Returns the list of a table's column names, data types, and default values. diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb index f985bd9615..57eefbf1d6 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb @@ -165,6 +165,11 @@ module ActiveRecord result.nil? ? nil : result.first end + def select_rows(sql, name = nil) + execute(sql, name).map do |row| + (0...(row.size / 2)).map { |i| row[i] } + end + end def begin_db_transaction #:nodoc: catch_schema_changes { @connection.transaction } diff --git a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb index 5463bb645f..4799c69b83 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb @@ -256,6 +256,25 @@ module ActiveRecord @connection.disconnect rescue nil end + def select_rows(sql, name = nil) + rows = [] + repair_special_columns(sql) + log(sql, name) do + @connection.select_all(sql) do |row| + record = [] + row.each do |col| + if col.is_a? DBI::Timestamp + record << col.to_time + else + record << col + end + end + rows << record + end + end + rows + end + def columns(table_name, name = nil) return [] if table_name.blank? table_name = table_name.to_s if table_name.is_a?(Symbol) diff --git a/activerecord/test/finder_test.rb b/activerecord/test/finder_test.rb index ef995aeb3f..b5bc5ae7f3 100644 --- a/activerecord/test/finder_test.rb +++ b/activerecord/test/finder_test.rb @@ -559,6 +559,16 @@ class FinderTest < Test::Unit::TestCase assert_equal ["37signals","Summit","Microsoft", "Flamboyant Software", "Ex Nihilo", "RailsCore", "Leetsoft", "Jadedpixel", "Odegy"], Company.connection.select_values("SELECT name FROM companies ORDER BY id") end + def test_select_rows + assert_equal( + [["1", nil, nil, "37signals"], + ["2", "1", "2", "Summit"], + ["3", "1", "1", "Microsoft"]], + Company.connection.select_rows("SELECT id, firm_id, client_of, name FROM companies ORDER BY id LIMIT 3")) + assert_equal [["1", "37signals"], ["2", "Summit"], ["3", "Microsoft"]], + Company.connection.select_rows("SELECT id, name FROM companies ORDER BY id LIMIT 3") + end + protected def bind(statement, *vars) if vars.first.is_a?(Hash) |