aboutsummaryrefslogtreecommitdiffstats
path: root/activerecord
diff options
context:
space:
mode:
authorJeremy Kemper <jeremy@bitsweat.net>2007-09-16 03:32:47 +0000
committerJeremy Kemper <jeremy@bitsweat.net>2007-09-16 03:32:47 +0000
commit30fb7b8c8bfc72ed3097352539544c07cbb38d0d (patch)
tree10b9d6b1d6ec2c792b8a503bf297a6a4e196ae68 /activerecord
parent78da29a84f886386c73768fa60609a0090a3a250 (diff)
downloadrails-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')
-rw-r--r--activerecord/CHANGELOG2
-rw-r--r--activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb8
-rw-r--r--activerecord/lib/active_record/connection_adapters/db2_adapter.rb15
-rwxr-xr-xactiverecord/lib/active_record/connection_adapters/mysql_adapter.rb9
-rw-r--r--activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb32
-rw-r--r--activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb5
-rw-r--r--activerecord/lib/active_record/connection_adapters/sqlserver_adapter.rb19
-rw-r--r--activerecord/test/finder_test.rb10
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)