#ENCODING: cp1250
=begin rdoc
Testcode active record.

See 
* http://snippets.dzone.com/posts/show/3097
* http://ar.rubyonrails.org/
* http://codeigniter.com/user_guide/database/active_record.html
* Multiple data bases:
  http://weare.buildingsky.net/2006/12/06/multiple-concurrent-database-connections-with-activerecord
  
=end

require 'rake'
require 'active_record' #2.3.8

class ActiveRecord::Migration
=begin
*ActiveRecord::Migration schreibt Messages auf STDOUT. 
  Umdefinieren von ActiveRecord::Migration.write verhindert dies.
=end
  def self.write(text="")
  end
end

#======connect===========
desc 'Connect to database'
task :connect => :connect_sqlite 

#======connect_sqlite===========
task :connect_sqlite do
  ActiveRecord::Base.establish_connection( 
      :adapter  => "sqlite3",
      :database => ":memory:"
    )
=begin
*Multiple DB can be defined with {{{class DB < ActiveRecord::Base}}}
=end
end #task connect_sqlite

#======connect_odbc===========
task :connect_odbc do
#dsn, username and password must be known.
ActiveRecord::Base.establish_connection( 
    :adapter=>"sqlserver", 
    :mode=>"odbc", 
    :dsn    => $dsn,
    :username => $username, 
    :password=> $password
  )
end #task :connect_odbc

#======disconnect===========
task :disconnect do
  ActiveRecord::Base.connection.disconnect!
end #task disconnect

#======create_model===========
task :create_model => :connect do
  class Film < ActiveRecord::Base
    #set_table_name :films
  end
=begin
* Es wird implizit eine Tabelle //films// angenommen
* Abweichende Tabellennamen können angegeben werden.
=end
end #task create_model


#======create_tables===========
task :create_tables => :connect do
  ActiveRecord::Schema.define do 
      create_table :films do |t|
      #~ primary_key :fid
      t.column :title, :string
      t.column :year, :string, :size => 4
    end
  end
=begin
*Es wird implizit ein Attribut 'id' angelegt.
*Spalten werden im Block angelegt.
*ActiveRecord::Migration schreibt Messages auf STDOUT. 
  Umdefinieren von ActiveRecord::Migration.write verhindert dies.
=end
end #task create_tables

task :create_tables_2 =>  :connect do 
  ActiveRecord::Schema.define do 
    create_table( :films )
    add_column(:films, :title, :string)
    add_column(:films, :year, :string, :size => 4)
  end
=begin
*Tabelle wird ohne Spalten angelegt
*Spalten werden anschliessend angelegt.
=end
end


#======fill_db===========
desc 'DB mit Testwerten füllen'
task :fill_db => [:create_tables, :create_model] do
  YAML.load(File.read('filmdaten.yaml')).each{|film|
    Film.create( :title => film['title'], :year => film['year'] )
  }
=begin
=end
end #task fill_db

#======insert_values===========
task :insert_values => [:create_tables, :create_model] do
  #Model Film wurde bereits definiert
  Film.create(:title => 'Three Ages', :year => 1923)
=begin
=end
end #task insert_values

#======select2array===========
task :select2array => :fill_db do
  #Model Film wurde bereits definiert
  res = Film.find(:all)
  puts res.inspect[0..42] + '...'
=begin
Die Definition eines Models ist notwendig (?)
=end
end #task select2array

#======select2block===========
task :select2block => :fill_db do
  #Model Film wurde bereits definiert
  Film.find(:all).each{|film|
    p film
  }
=begin
=end
end #task select2block

#======select_where===========
task :select_where => :fill_db do
  Film.find(  :all, 
                :conditions => "year = '1923'"
            ).each{|f| p f}
=begin
=end
end #task select_where

#======select_where_gt===========
task :select_where_gt => [:create_model, :fill_db] do
  Film.find(  :all, 
                :conditions => "year > '1927'"
              ).each{|f| p f}
=begin
In :conditions steht die SQL Where-Klausel.
=end
end #task select_where_gt

#~ #======select_where_in===========
task :select_where_in => [:create_model, :fill_db] do
  Film.find(:all, 
              :conditions => "year in ( '1920', '1923')"
            ).each{|f| p f}
=begin
In :conditions steht die SQL Where-Klausel.
=end
end #task select_where_in

#~ #======select_where_range===========
task :select_where_range => [:create_model, :fill_db] do
  Film.find(:all, 
              :conditions => "year between '1920' and '1923'"
            ).each{|f| p f}
=begin
In :conditions steht die SQL Where-Klausel.
=end
end #task select_where_range

#======select_col===========
task :select_col => [:create_model, :fill_db] do
  #Model Film wurde bereits definiert
  res = Film.find(:all, :select => "title")
=begin
=end
  res.each{|f| p f}
end #task select_col

#======update===========
task :update => [:create_model, :fill_db] do
  #1 = interne id
  Film.update(1, :year => 'egal' )
  Film.find(:all, :conditions => "year = 'egal'")
=begin
=end
end #task update
#======create_view===========
task :create_view => [:create_tables, :fill_db] do
  nil
=begin
*Ansatzpunkte: 
**Migration
**Raw sql mit execute
=end
end #task create_view

#======check_schema===========
desc 'Tabellenbeschreibung erhalten'
task :check_schema => [:create_tables, :create_model] do
ActiveRecord::SchemaDumper.dump
=begin
* Mit ActiveRecord::SchemaDumper.dump wird ruby-code zum erzeugen der DB-Schemas ausgegeben.
=end
end #task check_schema

#======check_schema===========
desc 'Tabellenbeschreibung erhalten'
task :check_schema_columns => [:create_tables, :create_model] do
    Film.columns.each{|column|
      p column
    }
=begin
=end
end #task check_schema

#~ #======use_sql===========
task :use_sql => :fill_db do
  ActiveRecord::Base.connection.execute(
      "create table actors (
      id integer primary key, 
      name varchar(255)
    )"
    )
=begin
=end
end #task use_sql

#======use_sql_select===========
task :use_sql_select => :fill_db do 
  ActiveRecord::Base.connection.select_values(
      "select title from films where year = '1923'"
    ).each{|f| p f }
=begin
*Array mit hash.
*Statt ActiveRecord::Base kann jedes Model verwendet werden.
=end
end #task use_sql_select

#~ #======use_sql_inverse===========
#~ task :use_sql_inverse => :fill_db do
#~ =begin
#~ =end
#~ end #task use_sql_inverse

# =================

#~ task :default => :connect
#~ task :default => :connect_sqlite
#~ task :default => :connect_odbc
#~ task :default => :disconnect
#~ task :default => :create_model
#~ task :default => :create_tables
#~ task :default => :fill_db
#~ task :default => :insert_values
#~ task :default => :select2array
#~ task :default => :select2block
#~ task :default => :select_where
#~ task :default => :select_where_gt
#~ task :default => :select_where_in
#~ task :default => :select_where_range
#~ task :default => :select_col_no_model
#~ task :default => :select_col
#~ task :default => :update
#~ task :default => :insert_values_no_model
#~ task :default => :no_model_select2array
#~ task :default => :no_model_select2block
#~ task :default => :no_model_select_where
#~ task :default => :no_model_update
#~ task :default => :create_view
#~ task :default => :check_schema
task :default => :use_sql
task :default => :use_sql_select
#~ task :default => :use_sql_inverse
#~ task :default => :disconnect


if $0 == __FILE__
  Rake.application[:default].invoke
end
__END__