lib/spiderfw/model/storage/db/adapters/oci8.rb in spiderfw-0.5.7 vs lib/spiderfw/model/storage/db/adapters/oci8.rb in spiderfw-0.5.9
- old
+ new
@@ -5,13 +5,14 @@
class OCI8 < DbStorage
@capabilities = {
:autoincrement => false,
:sequences => true,
- :transactions => true
+ :transactions => true,
+ :foreign_keys => true
}
- @reserved_keywords = superclass.reserved_keywords + ['oci8_row_num', 'file', 'uid']
+ @reserved_keywords = superclass.reserved_keywords + ['oci8_row_num', 'file', 'uid', 'name', 'comment']
@safe_conversions = {
'CHAR' => ['VARCHAR', 'CLOB'],
'VARCHAR' => ['CLOB'],
'NUMBER' => ['VARCHAR']
}
@@ -22,10 +23,11 @@
end
def self.new_connection(user, pass, dbname, role)
conn ||= ::OCI8.new(user, pass, dbname, role)
conn.autocommit = true
+ conn.non_blocking = true
return conn
end
def self.disconnect(conn)
conn.logoff
@@ -67,23 +69,27 @@
@connection_params = [@user, @pass, @dbname, @role]
end
def do_start_transaction
+ return unless transactions_enabled?
connection.autocommit = false
end
def in_transaction?
+ return false unless transactions_enabled?
return curr[:conn] && !curr[:conn].autocommit?
end
def do_commit
+ return release unless transactions_enabled?
curr[:conn].commit if curr[:conn]
release
end
def do_rollback
+ return release unless transactions_enabled?
curr[:conn].rollback
release
end
def prepare_value(type, value)
@@ -106,11 +112,11 @@
def value_to_mapper(type, value)
case type.name
when 'Date', 'DateTime'
return nil unless value
- return value if value.is_a?(Type)
+ return value if value.is_a?(type)
return value.to_datetime if type == DateTime
return value.to_date # FIXME: check what is returned, here we espect an OCI8::Date
when 'Spider::DataTypes::Text'
value = value.read if value.respond_to?(:read)
when 'Spider::DataTypes::Decimal', 'BigDecimal'
@@ -124,19 +130,19 @@
if (bind_vars && bind_vars.length > 0)
debug_vars = bind_vars.map{|var| var = var.to_s; var && var.length > 50 ? var[0..50]+"...(#{var.length-50} chars more)" : var}
end
curr[:last_executed] = [sql, bind_vars]
if (Spider.conf.get('storage.db.replace_debug_vars'))
- cnt = -1
- debug("oci8 executing: "+sql.gsub(/:\d+/){
- v = bind_vars[cnt]
- dv = debug_vars[cnt+=1]
+ debug("oci8 #{connection} executing: "+sql.gsub(/:(\d+)/){
+ i = $1.to_i
+ v = bind_vars[i-1]
+ dv = debug_vars[i-1]
v.is_a?(String) ? "'#{dv}'" : dv
})
else
debug_vars_str = debug_vars ? debug_vars.join(', ') : ''
- debug("oci8 executing:\n#{sql}\n[#{debug_vars_str}]")
+ debug("oci8 #{connection} executing:\n#{sql}\n[#{debug_vars_str}]")
end
cursor = connection.parse(sql)
return cursor if (!cursor || cursor.is_a?(Fixnum))
bind_vars.each_index do |i|
var = bind_vars[i]
@@ -150,15 +156,20 @@
have_result = (cursor.type == ::OCI8::STMT_SELECT)
# @cursor = connection.exec(sql, *bind_vars)
if (have_result)
result = []
while (h = cursor.fetch_hash)
+ h.each do |key, val|
+ if val.respond_to?(:read)
+ h[key] = val.read
+ end
+ end
if block_given?
- yield h
- else
- result << h
- end
+ yield h
+ else
+ result << h
+ end
end
end
if (have_result)
unless block_given?
result.extend(StorageResult)
@@ -166,13 +177,18 @@
return result
end
else
return res
end
+ cursor.close
+
rescue => exc
+ curr[:conn].break if curr[:conn]
+ rollback! if in_transaction?
+ #curr[:conn].logoff
release
- raise exc
+ raise
ensure
cursor.close if cursor
release if curr[:conn] && !in_transaction?
end
end
@@ -232,10 +248,11 @@
def sql_select(query)
curr[:bind_cnt] = 0
# Spider::Logger.debug("SQL SELECT:")
# Spider::Logger.debug(query)
bind_vars = query[:bind_vars] || []
+ order_on_different_table = false
if query[:limit] # Oracle is so braindead
replaced_fields = {}
replace_cnt = 0
# add first field to order if none is found; order is needed for limit
query[:order] << [query[:keys][0], 'desc'] if query[:order].length < 1
@@ -246,10 +263,11 @@
# query[:keys].push(field)
# i = query[:keys].length < 1
# end
transformed = "O#{replace_cnt += 1}"
replaced_fields[field.to_s] = transformed
+ order_on_different_table = true if field.is_a?(Spider::Model::Storage::Db::Field) && !query[:tables].include?(field.table)
if (field.is_a?(Spider::Model::Storage::Db::Field) && field.type == 'CLOB')
field = "CAST(#{field} as varchar2(100))"
end
query[:keys] << "#{field} AS #{transformed}"
end
@@ -260,28 +278,26 @@
sql = "SELECT #{keys} FROM #{tables_sql} "
bind_vars += tables_values
where, vals = sql_condition(query)
bind_vars += vals
sql += "WHERE #{where} " if where && !where.empty?
- order = sql_order(query)
+ order = sql_order(query, replaced_fields)
if (query[:limit])
if (query[:offset])
limit = "oci8_row_num between :#{curr[:bind_cnt]+=1} and :#{curr[:bind_cnt]+=1}"
bind_vars << query[:offset] + 1
bind_vars << query[:offset] + query[:limit]
else
limit = "oci8_row_num < :#{curr[:bind_cnt]+=1}"
bind_vars << query[:limit] + 1
end
- replaced_fields.each do |f, repl|
- order = order.gsub(f, repl)
- end
if (!query[:joins].empty?)
+ data_tables_sql = order_on_different_table ? tables_sql : query[:tables].join(', ')
pk_sql = query[:primary_keys].join(', ')
distinct_sql = "SELECT DISTINCT #{pk_sql} FROM #{tables_sql}"
distinct_sql += " WHERE #{where}" if where && !where.empty?
- data_sql = "SELECT #{keys} FROM #{tables_sql} WHERE #{pk_sql} IN (#{distinct_sql}) order by #{order}"
+ data_sql = "SELECT #{keys} FROM #{data_tables_sql} WHERE (#{pk_sql}) IN (#{distinct_sql}) order by #{order}"
else
data_sql = "#{sql} order by #{order}"
end
count_sql = "SELECT /*+ FIRST_ROWS(n) */ a.*, ROWNUM oci8_row_num FROM (#{data_sql}) a"
sql = "SELECT * FROM (#{count_sql}) WHERE #{limit}"
@@ -294,9 +310,10 @@
def sql_limit(query)
# already done in sql_condition
end
def sql_condition_value(key, comp, value, bound_vars=true)
+ curr[:bind_cnt] ||= 0
if (comp.to_s.downcase == 'ilike')
comp = 'like'
key = "UPPER(#{key})"
end
if (value.nil?)