schema_oracle_spatial.rb - experimenting with oracle spatial and activerecord connections

schema_oracle_spatial.rb

$:.unshift(File.join(File.dirname(__FILE__), 'spatial_adapter', 'lib'))
$:.unshift(File.join(File.dirname(__FILE__), '..', 'utils'))

require 'rubygems'
require 'pp'
require 'active_record'
require 'active_record/connection_adapters/oracle_enhanced_adapter'
require 'geo_ruby'
require 'oci8'
require 'sdo_geometry.rb'

include GeoRuby::SimpleFeatures

DB_CONFIG = YAML.load_file('database.yml')

# ============= Connect to oracle spatial development database =============
begin
  class DevDb < ActiveRecord::Base
    establish_connection(DB_CONFIG['development'])
    execute("ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-FXYYYY'")
  end
rescue Exception => e
  pp "#{e.message}: might I recommend iSQL to fix this" if e.is_a?(OCISuccessWithInfo) 
  pp e.message
end

# ============= Connect to oracle spatial production database =============
begin
  class ProdDb < ActiveRecord::Base
    establish_connection(DB_CONFIG['production'])
    execute("ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-FXYYYY'")
  end
rescue Exception => e
  pp "#{e.message}: might I recommend iSQL to fix this" if e.is_a?(OCISuccessWithInfo) 
  pp e.message
end

#ActiveRecord::Base.connection.disable_dbms_output
# ==============================================================
# add spatial support from georuby and common spatial adapter
# These should be added AFTER the AR connection is established otherwise you will get
# "uninitialized constant ActiveRecord::..." error 
require 'common_spatial_adapter' # WARNING: MAKE SURE THIS IS AFTER AR CONNECTION IS ESTABLISHED
#include SpatialAdapter


ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
  include SpatialAdapter

  alias :original_native_database_types :native_database_types
  def native_database_types
    original_native_database_types.merge!(geometry_data_types)
  end
  
  alias :original_quote :quote
  #Redefines the quote method to add behaviour for when a Geometry is encountered
  def quote(value, column = nil)
    if value.kind_of?(GeoRuby::SimpleFeatures::Geometry)
      value.as_sdo_geometry
    else
      original_quote(value,column)
    end
  end  
end


module Dev
  class MySample < DevDb
    set_table_name :my_samples
    set_sequence_name :autogenerated 
    set_primary_key :sampleno
  end

  class Sample < DevDb
    set_table_name :samples
    set_sequence_name :autogenerated 
    set_primary_key :sampleno
  end
end

module Prod

  class Survey < ProdDb
    set_table_name :surveys
    set_primary_key :eno
    has_many :samples
  end

  class Sample < ProdDb
    set_table_name :samples
    set_sequence_name :autogenerated 
    set_primary_key :sampleno
    set_date_columns :entrydate, :qadate, :acquiredate, :confid_until, :lastupdate
    has_many :sample_data
    belongs_to :survey, :foreign_key => :eno
  end

  class SampleData < ProdDb
    set_table_name :sampledata
    set_sequence_name :autogenerated 
    set_primary_key :datano
    belongs_to :sample, :foreign_key => :sampleno
  end

end