# * Matt Bowen  <matt.bowen@farweststeel.com>
# * George Moschovitis  <gm@navel.gr>
# (c) 2004-2005 Navel, all rights reserved.
# $Id: oracle.rb 337 2005-03-31 16:20:40Z gmosx $

begin
	require 'oracle'
rescue
	Logger.error 'Ruby-Oracle bindings are not installed!'
	Logger.error ex
end

require 'og/adapter'
require 'og/connection'
require 'glue/attribute'

module Og

# The Oracle adapter. This adapter communicates with 
# an Oracle rdbms. For extra documentation see 
# lib/og/adapter.rb

class OracleAdapter < Adapter

  def initialize
    super
    @typemap.update(
      Integer => 'number',
      Fixnum => 'number',
      String => 'varchar2(512)',
      TrueClass => 'number',
      Object => 'varchar2(1024)',
      Array => 'varchar2(1024)',
      Hash => 'varchar2(1024)'
    )
		@typecast.update(TrueClass => "#\{:s: ? \"1\" : 'NULL' \}")
  end

  def self.timestamp(time = Time.now)
    return nil unless time
    return time.strftime("%Y-%m-%d %H:%M:%S")
  end
  
  def self.date(date)
    return nil unless date
    return "#{date.year}-#{date.month}-#{date.mday}" 
  end

  def write_prop(p)
    if p.klass.ancestors.include?(Integer)
      return "#\{@#{p.symbol} || 'NULL'\}"
    elsif p.klass.ancestors.include?(Float)
      return "#\{@#{p.symbol} || 'NULL'\}"    
    elsif p.klass.ancestors.include?(String)
      return "'#\{#{self.class}.escape(@#{p.symbol})\}'"
    elsif p.klass.ancestors.include?(Time)
      return %|#\{@#{p.symbol} ? "'#\{#{self.class}.timestamp(@#{p.symbol})\}'" : 'NULL'\}|
    elsif p.klass.ancestors.include?(Date)
      return %|#\{@#{p.symbol} ? "'#\{#{self.class}.date(@#{p.symbol})\}'" : 'NULL'\}|
    elsif p.klass.ancestors.include?(TrueClass)
      return "#\{@#{p.symbol} ? \"1\" : 'NULL' \}"
    else 
      return %|#\{@#{p.symbol} ? "'#\{#{self.class}.escape(@#{p.symbol}.to_yaml)\}'" : "''"\}|
    end
  end   
  
	#--
	# mcb:
	# Unlike MySQL or Postgres, Oracle database/schema creation is a big deal. 
	# I don't know how to do it from the command line. I use Oracle's Database 
	# Configuration Assistant utility (dbca). I takes 30min - 1hr to create 
	# a full blown schema. So, your FIXME comments are fine. I'm thinking you
	# won't be able to do this via Og, but once created, Og will be able to create
	# tables, indexes, and other objects.
	#++

  def create_db(database, user = nil, password = nil)
    # FIXME: what is appropriate for oracle?
    # `createdb #{database} -U #{user}`
    super
  end

  def drop_db(database, user = nil, password = nil)
    # FIXME: what is appropriate for oracle?
    # `dropdb #{database} -U #{user}`
    super
  end

  def insert_code(klass, db, pre_cb, post_cb)
    props = props_for_insert(klass)
    values = props.collect { |p| write_prop(p) }.join(',') 

    sql = "INSERT INTO #{klass::DBTABLE} (#{props.collect {|p| p.name}.join(',')}) VALUES (#{values})"

    %{
      #{pre_cb}
      res = conn.store.exec("SELECT #{klass::DBSEQ}.nextval FROM DUAL")
      @oid = res.fetch[0].to_i
      res.close
      conn.exec "#{sql}"
      #{post_cb}
    }
  end

  def new_connection(db)
    return OracleConnection.new(db)
  end

  def calc_field_index(klass, db)
    # gmosx: This is incredible!!! argh!
    # res = db.query "SELECT * FROM #{klass::DBTABLE} # LIMIT 1"
    res = db.query "SELECT * FROM (SELECT * FROM #{klass::DBTABLE}) WHERE ROWNUM <= 1"
    meta = db.managed_classes[klass]

    columns = res.getColNames

    for idx in (0...columns.size)
      # mcb: Oracle will return column names in uppercase.
      meta.field_index[columns[idx].downcase] = idx
    end

  ensure
    res.close if res
  end

  def create_table(klass, db)
    conn = db.get_connection

    fields = create_fields(klass)

    sql = "CREATE TABLE #{klass::DBTABLE} (#{fields.join(', ')}"
    
    # Create table constrains.
    
    if klass.__meta and constrains = klass.__meta[:sql_constrain]
      sql << ", #{constrains.join(', ')}"
    end

		# mcb: Oracle driver chokes on semicolon.
    sql << ")"

  	# mcb:
  	# Oracle driver appears to have problems executing multiple SQL 
		# statements in single exec() call. Chokes with or without semicolon 
		# delimiter. Solution: make separate calls for each statement.

    begin
      conn.store.exec(sql).close
      Logger.info "Created table '#{klass::DBTABLE}'."
      
      # Create indices.

      if klass.__meta and indices = klass.__meta[:sql_index]
        for data in indices
          idx, options = *data
          idx = idx.to_s
          pre_sql, post_sql = options[:pre], options[:post]
          idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
          sql = " CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx})"  
          conn.store.exec(sql).close
          Logger.info "Created index '#{klass::DBTABLE}_#{idxname}_idx'."          
        end
      end
    rescue Exception => ex
      # gmosx: any idea how to better test this?
      if ex.to_s =~ /ORA-00955/i
        Logger.debug 'Table or index already exists' if $DBG
        return
      else
        raise
      end
    end

    # Create the sequence for this table. 
    begin
      conn.store.exec("CREATE SEQUENCE #{klass::DBSEQ}").close
      Logger.info "Created sequence '#{klass::DBSEQ}'."       
    rescue Exception => ex
      # gmosx: any idea how to better test this?
      if ex.to_s =~ /ORA-00955/i
        Logger.debug "Sequence already exists" if $DBG
      else
        raise
      end
    end

    # Create join tables if needed. Join tables are used in
    # 'many_to_many' relations.
    
    if klass.__meta and joins = klass.__meta[:sql_join] 
      for data in joins
        # the class to join to and some options.
        join_name, join_class, options = *data
        
        # gmosx: dont use DBTABLE here, perhaps the join class
        # is not managed yet.
        join_table = "#{self.class.join_table(klass, join_class, join_name)}"
        join_src = "#{self.class.encode(klass)}_oid"
        join_dst = "#{self.class.encode(join_class)}_oid"
        begin
          conn.store.exec("CREATE TABLE #{join_table} ( key1 integer NOT NULL, key2 integer NOT NULL )").close
          conn.store.exec("CREATE INDEX #{join_table}_key1_idx ON #{join_table} (key1)").close
          conn.store.exec("CREATE INDEX #{join_table}_key2_idx ON #{join_table} (key2)").close
        rescue Exception => ex
          # gmosx: any idea how to better test this?
          if ex.to_s =~ /ORA-00955/i
            Logger.debug "Join table already exists" if $DBG
          else
            raise
          end
        end
      end
    end

  ensure
    db.put_connection
  end

  def drop_table(klass)
    super
    exec "DROP SEQUENCE #{klass::DBSEQ}"    
  end

  # Generate the property for oid.

	#--
	# mcb:
	# Oracle doesn't have a "serial" datatype. Replace with 
	# integer (which is probably just a synonym for NUMBER(38,0))
	# A sequence is created automatically by Og.
	#++

  def eval_og_oid(klass)
    klass.class_eval %{
      prop_accessor :oid, Fixnum, :sql => 'integer PRIMARY KEY'
    }
  end
end

# The Oracle connection.

class OracleConnection < Connection

	# mcb:
	# The database connection details are tucked away in a 
	# TNS entry (Transparent Network Substrate) which specifies host, 
	# port, protocol, and database instance. Here is a sample TNS 
	# entry:
	#
	# File: tns_names.ora
	#
	# KBSID =
	#  (DESCRIPTION =
	#    (ADDRESS_LIST =
	#      (ADDRESS = (PROTOCOL = TCP)(HOST = keebler.farweststeel.com)(PORT = 1521))
	#    )
	#    (CONNECT_DATA =
	#      (SID = KBSID)
	#    )
	#  )

  def initialize(db)
    super
    config = db.config

    begin
      # FIXME: how to pass address etc?
      @store = Oracle.new(config[:user], config[:password], config[:database])
			# gmosx: better use this???
			# @store = Oracle.new(config[:tns])

			# gmosx: does this work?
			@store.autocommit = true
    rescue Exception => ex
  		# mcb:
	 	 	# Oracle will raise a ORA-01017 if username, password, or 
			# SID aren't valid. I verified this for all three.
  		# irb(main):002:0> conn = Oracle.new('keebler', 'dfdfd', 'kbsid')
  		# /usr/local/lib/ruby/site_ruby/1.8/oracle.rb:27:in `logon': ORA-01017: invalid username/password; logon denied (OCIError)
      # gmosx:
			# any idea how to better test this? an integer error id?
      if ex.to_s =~ /ORA-01017/i     
        Logger.info "Database '#{config[:database]}' not found!"
        @db.adapter.create_db(config[:database], config[:user])
        retry
      end
      raise
    end
  end

  def close
    @store.logoff
    super
  end

  def query(sql)
    Logger.debug sql if $DBG
    begin
      return @store.exec(sql)
    rescue Exception => ex
			handle_db_exception(ex, sql)
    end
  end

  def exec(sql)
    Logger.debug sql if $DBG
    begin
      @store.exec(sql)
    rescue Exception => ex
			handle_db_exception(ex, sql)
    end
  end

	def start
		@store.autocommit = false
	end
	
	def commit
		@store.commit
	ensure
		@store.autocommit = true
	end
	
	def rollback
		@store.rollback
	ensure
		@store.autocommit = true
	end

  def valid_res?(res)
    return !(res.nil?)
  end

  def read_one(res, klass)
    return nil unless valid_res?(res)

    row = res.fetch
    return nil unless row

    obj = klass.new
    obj.og_read(row)

    res.close
    return obj
  end

  def read_all(res, klass)
    return [] unless valid_res?(res)
    objects = []

    while row = res.fetch
      obj = klass.new
      obj.og_read(row)
      objects << obj
    end

    res.close
    return objects
  end

  def read_int(res, idx = 0)
    val = res.fetch[idx].to_i
    res.close
    return val
  end

	def get_row(res)
		res.fetch
	end
end

end