Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 221 lines (188 sloc) 7.609 kb
232d222 Konstantin Shabanov Modularize postgresql adapter
etehtsea authored
1 module ActiveRecord
2 module ConnectionAdapters
3 class PostgreSQLAdapter < AbstractAdapter
4 module DatabaseStatements
5 def explain(arel, binds = [])
6 sql = "EXPLAIN #{to_sql(arel, binds)}"
7 ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds))
8 end
9
10 class ExplainPrettyPrinter # :nodoc:
11 # Pretty prints the result of a EXPLAIN in a way that resembles the output of the
12 # PostgreSQL shell:
13 #
14 # QUERY PLAN
15 # ------------------------------------------------------------------------------
16 # Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0)
17 # Join Filter: (posts.user_id = users.id)
18 # -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4)
19 # Index Cond: (id = 1)
20 # -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4)
21 # Filter: (posts.user_id = 1)
22 # (6 rows)
23 #
24 def pp(result)
25 header = result.columns.first
26 lines = result.rows.map(&:first)
27
28 # We add 2 because there's one char of padding at both sides, note
29 # the extra hyphens in the example above.
30 width = [header, *lines].map(&:length).max + 2
31
32 pp = []
33
34 pp << header.center(width).rstrip
35 pp << '-' * width
36
37 pp += lines.map {|line| " #{line}"}
38
39 nrows = result.rows.length
40 rows_label = nrows == 1 ? 'row' : 'rows'
41 pp << "(#{nrows} #{rows_label})"
42
43 pp.join("\n") + "\n"
44 end
45 end
46
47 # Executes a SELECT query and returns an array of rows. Each row is an
48 # array of field values.
49 def select_rows(sql, name = nil)
50 select_raw(sql, name).last
51 end
52
53 # Executes an INSERT query and returns the new record's ID
54 def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)
55 unless pk
56 # Extract the table from the insert sql. Yuck.
57 table_ref = extract_table_ref_from_insert_sql(sql)
58 pk = primary_key(table_ref) if table_ref
59 end
60
61 if pk && use_insert_returning?
62 select_value("#{sql} RETURNING #{quote_column_name(pk)}")
63 elsif pk
64 super
65 last_insert_id_value(sequence_name || default_sequence_name(table_ref, pk))
66 else
67 super
68 end
69 end
70
71 def create
72 super.insert
73 end
74
75 # create a 2D array representing the result set
76 def result_as_array(res) #:nodoc:
77 # check if we have any binary column and if they need escaping
78 ftypes = Array.new(res.nfields) do |i|
79 [i, res.ftype(i)]
80 end
81
82 rows = res.values
83 return rows unless ftypes.any? { |_, x|
84 x == BYTEA_COLUMN_TYPE_OID || x == MONEY_COLUMN_TYPE_OID
85 }
86
87 typehash = ftypes.group_by { |_, type| type }
88 binaries = typehash[BYTEA_COLUMN_TYPE_OID] || []
89 monies = typehash[MONEY_COLUMN_TYPE_OID] || []
90
91 rows.each do |row|
92 # unescape string passed BYTEA field (OID == 17)
93 binaries.each do |index, _|
94 row[index] = unescape_bytea(row[index])
95 end
96
97 # If this is a money type column and there are any currency symbols,
98 # then strip them off. Indeed it would be prettier to do this in
99 # PostgreSQLColumn.string_to_decimal but would break form input
100 # fields that call value_before_type_cast.
101 monies.each do |index, _|
102 data = row[index]
103 # Because money output is formatted according to the locale, there are two
104 # cases to consider (note the decimal separators):
105 # (1) $12,345,678.12
106 # (2) $12.345.678,12
107 case data
108 when /^-?\D+[\d,]+\.\d{2}$/ # (1)
109 data.gsub!(/[^-\d.]/, '')
110 when /^-?\D+[\d.]+,\d{2}$/ # (2)
111 data.gsub!(/[^-\d,]/, '').sub!(/,/, '.')
112 end
113 end
114 end
115 end
116
117 # Queries the database and returns the results in an Array-like object
118 def query(sql, name = nil) #:nodoc:
119 log(sql, name) do
120 result_as_array @connection.async_exec(sql)
121 end
122 end
123
124 # Executes an SQL statement, returning a PGresult object on success
125 # or raising a PGError exception otherwise.
126 def execute(sql, name = nil)
127 log(sql, name) do
128 @connection.async_exec(sql)
129 end
130 end
131
132 def substitute_at(column, index)
133 Arel::Nodes::BindParam.new "$#{index + 1}"
134 end
135
136 def exec_query(sql, name = 'SQL', binds = [])
ffbefc7 Aaron Patterson wrap logging around the actual query call itself.
tenderlove authored
137 result = without_prepared_statement?(binds) ? exec_no_cache(sql, name, binds) :
138 exec_cache(sql, name, binds)
139
140 types = {}
141 fields = result.fields
142 fields.each_with_index do |fname, i|
143 ftype = result.ftype i
144 fmod = result.fmod i
145 types[fname] = OID::TYPE_MAP.fetch(ftype, fmod) { |oid, mod|
146 warn "unknown OID: #{fname}(#{oid}) (#{sql})"
147 OID::Identity.new
148 }
232d222 Konstantin Shabanov Modularize postgresql adapter
etehtsea authored
149 end
ffbefc7 Aaron Patterson wrap logging around the actual query call itself.
tenderlove authored
150
151 ret = ActiveRecord::Result.new(fields, result.values, types)
152 result.clear
153 return ret
232d222 Konstantin Shabanov Modularize postgresql adapter
etehtsea authored
154 end
155
156 def exec_delete(sql, name = 'SQL', binds = [])
ffbefc7 Aaron Patterson wrap logging around the actual query call itself.
tenderlove authored
157 result = without_prepared_statement?(binds) ? exec_no_cache(sql, name, binds) :
158 exec_cache(sql, name, binds)
159 affected = result.cmd_tuples
160 result.clear
161 affected
232d222 Konstantin Shabanov Modularize postgresql adapter
etehtsea authored
162 end
163 alias :exec_update :exec_delete
164
165 def sql_for_insert(sql, pk, id_value, sequence_name, binds)
166 unless pk
167 # Extract the table from the insert sql. Yuck.
168 table_ref = extract_table_ref_from_insert_sql(sql)
169 pk = primary_key(table_ref) if table_ref
170 end
171
172 if pk && use_insert_returning?
173 sql = "#{sql} RETURNING #{quote_column_name(pk)}"
174 end
175
176 [sql, binds]
177 end
178
179 def exec_insert(sql, name, binds, pk = nil, sequence_name = nil)
180 val = exec_query(sql, name, binds)
181 if !use_insert_returning? && pk
182 unless sequence_name
183 table_ref = extract_table_ref_from_insert_sql(sql)
184 sequence_name = default_sequence_name(table_ref, pk)
185 return val unless sequence_name
186 end
187 last_insert_id_result(sequence_name)
188 else
189 val
190 end
191 end
192
193 # Executes an UPDATE query and returns the number of affected tuples.
194 def update_sql(sql, name = nil)
195 super.cmd_tuples
196 end
197
198 # Begins a transaction.
199 def begin_db_transaction
200 execute "BEGIN"
201 end
202
392eeec Jon Leighton Support for specifying transaction isolation level
jonleighton authored
203 def begin_isolated_db_transaction(isolation)
204 begin_db_transaction
205 execute "SET TRANSACTION ISOLATION LEVEL #{transaction_isolation_levels.fetch(isolation)}"
206 end
207
232d222 Konstantin Shabanov Modularize postgresql adapter
etehtsea authored
208 # Commits a transaction.
209 def commit_db_transaction
210 execute "COMMIT"
211 end
212
213 # Aborts a transaction.
214 def rollback_db_transaction
215 execute "ROLLBACK"
216 end
217 end
218 end
219 end
220 end
Something went wrong with that request. Please try again.