#! ruby -Ks
# by KUMAGAI Hidetake (ggb03124@nifty.ne.jp)
# modified by YOSHIDA Kazuhiro (moriq@moriq.com)
#
# 2004-11-06 internal_query allows nested call
# 2004-11-06 sql_params move from DotSqlDataSet to DataSet_ex & modified.
#
#-- $(apollo)/lib/rdb/rdb.rb
#-- rdb.pi
#

require 'phi'
require 'rdb/rdb.so'

RDB = Phi

unless $NO_PHI_EXTENSION
module RDB

######## general extension for Enumerable ########

class IndexDefs     ; include Phi::Enumerable ; end
class DBGridColumns ; include Phi::Enumerable ; end
class Params        ; include Phi::Enumerable ; end
class FieldDefs     ; include Phi::Enumerable ; end
class Fields        ; include Phi::Enumerable ; end

######## utilitis ########

def self.add_sql_to_error_message( sql , params=nil, *others )
  if sql
    $!.message << "\n-- SQL is .. --\n#{sql}"
    $!.message << "\n-- params is .. --\n#{params.inspect}" if params
    $!.message << "\n-- end of SQL --\n"
  end
end

# [ap-dev:0988]
def self.close
  ObjectSpace.each_object(DataSet){|o| o.close }
end

######## options ########

#### field name adjuster ####

@@name_case_option = :downcase

  module_function
  def name_case_option
    @@name_case_option
  end
  def name_case_option=(v)
    @@name_case_option=v
  end
  def adjust_name_case name
    case RDB::name_case_option
    when :downcase ; name.downcase
    when :updace   ; name.updcase
    else           ; name
    end
  end

  class Field
    alias _field_name field_name
    def field_name
      RDB.adjust_name_case( _field_name )
    end
  end

######## Database/Connection Level ########

module Connection_ex
  @query = nil
  @table = nil

  def execute_ex( *args )
     execute( *args )
  rescue
    RDB.add_sql_to_error_message( *args )
    raise
  end

  #
  # This returns internal dataset.
  # attention:
  #   You may not use internal dataset as constant,
  #   and must close just after using it.
  #
  @@queries = [] # nestable
  def internal_query(*args)
    @@queries.push(query = new_query)
    begin
      query.open(*args) unless args.empty?
      yield query
    ensure
      query.close
      @@queries.pop
      GC.start unless $NO_GC_FOR_INTERBASE
    end
  end

  def internal_table(table_name=nil)
    @table ||= new_table
    if table_name  ## 2003-04-24(木)
      @table.table_name = table_name
      @table.open
    end
    begin
      yield @table
    ensure
      @table.close
      GC.start unless $NO_GC_FOR_INTERBASE
    end
  end

  #
  #  This returns Array of Hash,
  #  pay attention NOT returns dataset.
  #
  def select( what, from, where, params=[] )
    from  = "'#{from}'" if ( not /^['"]/ =~ from ) and ( /\./ =~ from )
    sql = "SELECT #{what} FROM #{from}"
    sql << " WHERE #{where}" if where
    internal_query(sql,params){|query| return query.open.values }
  rescue
    RDB.add_sql_to_error_message( sql , params )
    raise
  end

  #
  # This returns the value of first field of first record.
  # ex:
  #   sum = DB.lookup "SUM(budget)", "department", "head_dept='#{val}'"
  #   sid = DB.lookup "GEN_ID(#{gen_name},1)", "dummy_for_gen", nil
  #
  def lookup( what, from, where, params=[] )
    from  = "'#{from}'" if ( not /^['"]/ =~ from ) and ( /\./ =~ from )
    sql = "SELECT #{what} FROM #{from}"
    sql << " WHERE #{where}" if where
    internal_query(sql,params){|query|
      return nil if (rec = query.value[0]).nil? # <= differ from select!
##      return rec[0].value # rec[0] が値を返すなんて・・
      return rec[0]
    }
  rescue Exception
    RDB.add_sql_to_error_message( sql , params )
    raise
  end

  #
  # mark:
  #   '*'               all record
  #   'ALL'             all record without NULL
  #   'DISTINCT'        all record distinct without NULL
  #
  def rec_count(from, where=nil, mark='*')
    return lookup( "COUNT(#{mark})" , from, where )
  end

  def rec_exist?(from, where=nil)
    rec_count(from, where) != 0
  end

=begin
2003-07-24(木)
テーブルを他のアプリが使っているとき：
execute_ex("DROP TABLE #{table_name}")は、
（１）実際には削除されていないのに、
（２）エラーにならず、
（３）次のtable_exist?(table_name)はfalseを返す。
これは要するに、トランザクションの継続中に、そのトランザクションにおいては削除されたものと見なす、という仕様になっているものと思われる。
execute_ex("commit")を追加したところ、「unsuccessful metadata update//object AITE_V08 is in use」という例外が出るようになった。具合がよい。
ただし、副作用があるかもしれない。
=end

  def table_drop(table_name, avoid_error=false)
    return if avoid_error && ( not table_exist?( table_name))
    execute_ex "DROP TABLE #{table_name}"
    execute_ex "COMMIT"
    ##raise "fail to drop" if table_exist?( table_name)
  end

    ##
    ##  他で使用中でも削除＋作成できる？？
    ##  テーブル自体の作成は可能だが、そのあとオープンできない。
    ##
  def table_create(table_name, col_def)
    execute_ex "CREATE TABLE #{table_name} ( #{col_def} )"
    execute_ex "COMMIT"
    ##raise "fail to create" if ( not table_exist?( table_name) )
  end

  def table_recreate(table_name, col_def)
    table_drop   table_name if table_exist?( table_name)
    table_create table_name, col_def
  end

  def table_exist?(table_name)
    internal_table{ |table|
      table.table_name = table_name
      return table.exist?
    }
  end

  #
  #  transaction{}
  #
  #  wrapper of transaction pattern
  #
  #	2002-01-21: break/throw to commit
  #
  def transaction
    raise "no iterator" unless iterator?
    start_transaction
    begin
      yield
    rescue
      rollback if in_transaction?
      raise
    ensure
      commit if in_transaction?
    end
  end

end

#### module for InterBase / Firebird ####

module InterBase
  #
  # table
  #
  def table_names( system_table=false )
    if system_table
      sql = "select RDB$RELATION_NAME from RDB$RELATIONS where RDB$SYSTEM_FLAG = 1"
    else
      sql = "select RDB$RELATION_NAME from RDB$RELATIONS where RDB$SYSTEM_FLAG <> 1"
    end
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

  #
  # index
  #
  # [Firebird-jp-general][01303] 
  #
  def index_names( table_name=nil , system_table=false )
    if system_table
      sql = "select RDB$INDEX_NAME from RDB$INDICES where RDB$SYSTEM_FLAG = 1"
    else
      sql = "select RDB$INDEX_NAME from RDB$INDICES where ((RDB$SYSTEM_FLAG IS NULL) OR (RDB$SYSTEM_FLAG <> 1))"
    end
    if table_name
      sql << " and (RDB$RELATION_NAME = '#{table_name.upcase}')"
    end
    internal_query(sql){ |query|
      return query.value.flatten.collect{|i|i.strip} }
  end

  def index_exist?( index_name , table_name=nil , system_table=false )
    index_names(table_name, system_table).include?(index_name)
  end

  #
  # constraint
  #
  def constraint_exist? table_name, constraint_name
    rec_exist? "RDB$RELATION_CONSTRAINTS", <<EOT
                RDB$CONSTRAINT_NAME = '#{constraint_name.upcase}'
            AND RDB$RELATION_NAME = '#{table_name.upcase}'
EOT
  end

  def constraint_drop(table_name, constraint_name, avoid_error=false)
    return if avoid_error and not constraint_exist? table_name, constraint_name  # 必要 2003-07-02(水)
    execute_ex "ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint_name}"
  rescue
    return if avoid_error and not constraint_exist? table_name, constraint_name
    raise
  end

  #
  # "ALTER TABLE ADD FOREIGN KEY" fails
  # if DatabaseExplorer uses the database,
  # but returns no report or exception.
  # So, this routine checks fail after adding foreign key.
  #
  def named_foreign_key_add(table_name, constraint_name, key, ref)
    execute_ex <<EOT
        ALTER TABLE #{table_name}
          ADD CONSTRAINT #{constraint_name}
              FOREIGN KEY ( #{key} ) REFERENCES #{ref}
EOT
  end
  alias foreign_key_add named_foreign_key_add

  #
  # trigger
  #
  def trigger_names
    sql = "select RDB$TRIGGER_NAME from RDB$TRIGGERS where RDB$SYSTEM_FLAG is null"
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

  #
  # view
  #
  def view_names
    sql = "select distinct RDB$VIEW_NAME from RDB$VIEW_RELATIONS "
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

  def view_exist?(view_name)
    rec_exist? "RDB$VIEW_RELATIONS", "RDB$VIEW_NAME='#{view_name.upcase}'"
  end

  def view_drop(view_name, avoid_error=false)
    return if avoid_error and not view_exist? view_name ## 必要 2003-07-02(水)
    execute_ex( "DROP VIEW #{view_name}" )
  rescue
    return if avoid_error and not view_exist? view_name
    raise
  end

  #
  # procedure
  #
  def procedure_names
    sql = "select RDB$PROCEDURE_NAME from RDB$PROCEDURES"
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

  #
  # counter
  #
  def counter_names
    sql = "select RDB$GENERATOR_NAME from RDB$GENERATORS where RDB$SYSTEM_FLAG is null"
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

  def counter_exist?(counter_name)
    rec_exist? "RDB$GENERATORS", "RDB$GENERATOR_NAME='#{counter_name.upcase}'"
  end

  def counter_create(counter_name, start=0, max=nil)
    execute_ex "CREATE GENERATOR #{counter_name}"
    counter_inc(counter_name,start) if start != 0
  end

  def counter_drop(counter_name, avoid_error=false)
    raise "not exist counter '#{counter_name}'" unless avoid_error || counter_exist?(counter_name)
    execute_ex "DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME='#{counter_name.upcase}'"
  end

  def counter_inc(counter_name, inc=1, avoid_error=false)
    lookup "GEN_ID(#{counter_name},#{inc})", "rdb$database", nil
  end

  def counter_set(counter_name, n, avoid_error=false)
    execute_ex "SET GENERATOR #{counter_name} TO #{n}"
    return n
  end

  #
  # user
  #
  def user_names
    sql = "select distinct RDB$USER from RDB$USER_PRIVILEGES"
    internal_query(sql){ |query| return query.value.flatten.collect{|i|i.strip} }
  end

end

#### module for Oracle ####

module Oracle

  def fix_dummy_for_counter
    unless table_exist? "dummy_for_counter"
      table_create 'dummy_for_counter' , "dmy   INTEGER"
      execute_ex "INSERT INTO dummy_for_counter (dmy) VALUES (0) "
      execute_ex "GRANT SELECT ON TABLE dummy_for_counter TO PUBLIC"
    end
  end
  private :fix_dummy_for_counter

  def counter_create counter_name,start=0,max=nil
    maxvalue = ''
    maxvalue = 'MAXVALUE #{max}' if max
    execute_ex <<EOT
      CREATE SEQUENCE #{counter_name}
        INCREMENT BY 1
        START WITH #{start}
        #{maxvalue}
EOT
    fix_dummy_for_counter
  end

  #def counter_drop counter_name
  #  execute_ex "DROP SEQUENCE #{counter_name}"
  #  raise "please teach Mr.take_tk how to realize 'drop_counter'"
  #end

  def counter_inc counter_name,inc=1
    fix_dummy_for_counter
    if inc == 0
      lookup "#{counter_name}.CURVAL", "dummy_for_counter", nil
    else
      lookup "#{counter_name}.NEXTVAL", "dummy_for_counter", nil
    end
  end

  #def counter_exist? counter_name
  #  raise "please teach Mr.take_tk how to realize 'counter_exist?'"
  #end

end

########  DataSet Level ########

module DataSet_ex
  include Enumerable

  def each
    return unless active?
    bookmark{
      self.first
      until eof?
        yield fields
        self.next
      end
    }
  end

    ##
    ## use active_ex !
    ##
  def active_ex
    self.active
  end
  def active_ex=(v)
    return v if self.active == v
    self.active = v
    GC.start unless $NO_GC_FOR_INTERBASE
    raise "cannot open/close!!" unless active == v  ## ????
    return v
  rescue
    if defined? self.sql_text
      RDB.add_sql_to_error_message( self.sql_text , self.sql_params , self )
    elsif defined? self.table_name
      RDB.add_sql_to_error_message( self.table_name , nil , self )
    end
    raise
  end

  def open(sql=nil, params=nil)
    if sql
      if defined? self.sql_text
        self.sql_text = sql
        self.sql_params = params if params
      else
        raise "sql_text not defined"
      end
    end

    if block_given?
      curr_active = self.active_ex
      self.active_ex = true
      begin
        return yield( self )
      ensure
        self.active_ex = curr_active if self.active_ex != curr_active
      end
    else
      self.active_ex = true
    end
    self
  end

  def close
    if block_given?
      curr_active = self.active_ex
      self.active_ex = false
      begin
        return yield( self )
      ensure
        self.active_ex = curr_active if self.active_ex != curr_active
      end
    else
      self.active_ex = false
    end
    self
  end

  def locate_ex( key_field,key_value,options=[] )
    options += [LO_PARTIAL_KEY]
    (1...10).each{
      ret = locate key_field,key_value,options
      return ret if ret
      if key_value.is_a? String
        if key_value.size > 1
          key_value = key_value.chop
        else
          return  ret
        end
      elsif key_value.is_a? Integer
        key_value -= 1
      else
        return ret
      end
    }
    return ret
  end

  def struct(title=true)
    result = []
    self.open{
      result << ["name", "type", "size"].join("\t") if title
      result+= fields.collect{|f|
        [f.field_name, data_type_to_s(f.data_type), f.size].join("\t")
      }
    }
    result
  end

  def display(out=$defout)
    values = []
    ulines = []
    titles = []
    open{
      fields.each{|f|
        max = [f.display_label.length , f.display_width].max
        max = -max unless f.value.is_a? Numeric
        values.push "%#{max}s"
        ulines.push "-" * max.abs
        titles.push "%#{max}s" % f.display_label
      }
      format = values.join(' ') + "\n"
      titles.join(' ').display(out); "\n".display(out)
      ulines.join(' ').display(out); "\n".display(out)
      each{|r|
        (format % r.value).display(out)
        Phi::APPLICATION.process_messages
      }
    }
    self
  end
  alias list display  # dBASE like

  def to_s
    return unless active?
    collect{|a| a.collect{|f|
      if f.data_type == RDB::FT_STRING
        '"'+f.to_s+'"'
      else
        f.to_s
      end
    }.join("\t")}.join("\n")
  end

  def to_a
    return unless active?
    collect{|a| a.value}
  end

  def inspect
    '#<%s:0x%x>' % [self.class.to_s, self.object_id << 1]
  end

  def data_type_to_s(t)
    result = Phi::TYPE_INFO['TFieldType'].enum_name(t)
    return result[2,255] if result
    return "??(%d)" % [t]
  end

  def value
    return self.collect{|fields| fields.value  }
  end
  def values
    return self.collect{|fields| fields.values }
  end
  def data_types
    return fields.data_types
  end

  # must be override if not exist "self.params"
  def sql_params
    self.params
  end

  def sql_params=(v)
    case v
    when Array
      v.each_with_index{|item,index|
        begin
          self.sql_params[index].value = item
        rescue
          $!.message << "\n params=#{self.sql_params.values.inspect} ,\n args=#{v.inspect}\n"
          raise
        end
      }
    when Hash
      v.each{|key,item|
        self.sql_params[key.to_s].value = item
      }
    else
      self.sql_params = v
    end
  end

end # of module DataSet_ex

#### extender module for DotSql-type datasets ####

module DotSqlDataSet
  def sql_text
    self.sql.text
  end
  def sql_text=(v)
    self.sql.text=v
  end
end

#### extender module for command_text-type datasets ####

module CommandTextDataSet
  def sql_text
    self.command_text
  end
  def sql_text=(v)
    self.command_type = CT_QUERY
    self.command_text=v
  end

  def table_name
    self.command_text
  end
  def table_name=(v)
    self.command_type = CT_TABLE
    self.command_text = v
  end
end

########

class ClientDataSet # < DataSet
  include DataSet_ex
  include CommandTextDataSet
end

########  Field Level ########

######## 2001-08-30(木)

=begin
  Field#values
    { 項目名  => 内容 } というハッシュを返す。
  Fields#values
    { 項目名1 => 内容1, 項目名2 => 内容2, ...} というハッシュを返す。
  DataSet#values
    Fields#values の配列を返す。

  Field#values=(v)
    v (Hash) に { 自分の項目名  => 内容 } があれば設定する。
  Fields#values=(v)
    すべての項目につき Field#values=(v) を実行する。
  DataSet#values=(v)
    (未実装)

  return Hash[*(self.collect{|field| [field.field_name, field.data_type]}.flatten)]
  という方法もあるが..。
=end

class Field
  def values
    return Hash[field_name, value]
  end
  def values=(hash)
    self.value = hash[field_name] if hash[field_name]
    return hash
  end
  def data_types  # Read Only
    return Hash( self.field_name , self.data_type )
  end
end

class Fields
  def values
    hash = {}; each{|field| hash[field.field_name] = field.value     }
    return hash
  end

  #
  # 2003-01-28(火) ????
  # 2003-12-05(金) rescue for old version, "if self[]" for new version
  #
  def values= hash
    hash.each{|k,v|
      self[k].value = v if self[k]
    }
    return hash
  end

  def data_types
    hash = {}; each{|field| hash[field.field_name] = field.data_type }
    return hash
  end

  def value
    collect{|f| f.value}
  end

  def names
    collect{|f| f.field_name}
  end

  def to_s
    names.join("\t")
  end

  def inspect
    '#<%s:0x%x>' % [self.class.to_s, self.object_id << 1]
  end
end

class FieldDef

  def data_types
    return Hash( self.field_name , self.data_type )
  end

  def data_types= (hash)
    self.data_type = hash[self.name] if hash.has_key? self.name
    return hash
  end
end

class FieldDefs
  def data_types
    hash = {} ; each{ |field_def| hash[field_def.name] = field_def.data_type }
    return hash
  end
  def data_types= (hash)
    each{ |field_def| field_def.data_types = hash }
    return hash
  end
end

=begin
: values
  Param にデータをハッシュで設定する。

  Hash で受け取る
  {ParamName1 => Value1, ParamName2 => Value2, .. }

  ハッシュにないものは nil を設定する。要注意。
  前のレコードのデータがコピーされてしまうのを防ぐため。
  ＤＢでは NULL も立派なデータ。

  キーはパラメータ名であってフィールド名でないことに注意。

  あらかじめ型を指定しておかないと
  「項目 'AITENAME2' の型がわかりません」
  と出ることがある。その場合には、事前に
  query.sql_params.data_types = dataset.data_types
  で型をコピーしておけばＯＫ。

: data_types
  Param の型を Hash でセットする。

  Hash で受け取る。
  {ParamName1 => DataType1, ParamName2 => DataType2, .. }

  ハッシュにないものは何もしない。
  キーはパラメータ名であってフィールド名でないことに注意。
=end

class Params
  def values=(hash)
    case hash
    when Hash
      each{|param| self[param.name] = hash[param.name] }
    when Array  #++ 2002-11-07(木)
      hash.each_with_index{|v,i| self[i] = v }
    when nil
    else
      raise "cannot accept #{hash}"
    end
    return hash
  end
  def values
    hash = {};
    each{|param| hash[param.name] = self[param.name] }
    return hash
  end

  def data_types=(hash)
    self.each{ |param|
      value = hash[param.name]
      self[param.name].data_type = value if value
    }
    return hash
  end
  def data_types
    hash = {} ; each{ |param| hash[param.name] = param.data_type }
    return hash
  end
end

class DBGrid
  def on_mouse_wheel( x1,x2,x3,x4 )
    if x3 > 0
      data_source.data_set.prior
    else
      data_source.data_set.next
    end
  end
end # of class

end # of module RDB
end # of if $NO_PHI_EXTENSION
