require 'rubygems'
require 'thread'
require_gem 'postgres'
require 'metaid'

module ServerSide
  class Database
    attr_reader :conn
  
    def initialize(opts = {})
      @opts = opts
      @mutex = Mutex.new
      @conn = make_connection
    end
    
    def from(*args); query.from(*args); end
    def select(*args); query.select(*args); end
  end
  
  class Dataset
    include Enumerable
  
    def initialize(db, opts = {})
      @db = db
      @opts = opts || {}
    end
    
    def dup_merge(opts)
      self.class.new(@db, @opts.merge(opts))
    end
    
    # sql helpers
    def field_list(fields)
      case fields
      when Array:
        if fields.empty?
          "*"
        else
          fields.map {|i| field_name(i)}.join(", ")
        end
      else
        fields
      end
    end
    
    def field_name(field)
      if field === Symbol
        s = field.to_s
        if s =~ /(.*)___(.*)/
          s = "%s AS %s" % [$1, $2]
        end
        s.split('__').join('.')
      else
        field.to_s
      end
    end
    
    def source_list(source)
      case source
      when Array: source.join(', ')
      when Hash: source.map {|kv| "#{kv[1]} AS #{kv[0]}"}
      else source
      end 
    end
    
    # DSL constructors
    def from(source)
      dup_merge(:source => source)
    end
    
    def select(*fields)
      fields = fields.first if fields.size == 1
      dup_merge(:fields => fields)
    end
    
    def from!(source)
      @sql = nil
      @opts[:source] = source
      self
    end
    
    def select!(*fields)
      @sql = nil
      fields = fields.first if fields.size == 1
      @opts[:fields] = fields
      self
    end
  end
end

module Postgres
  PG_TYPES = {
    16 => :to_bool,
    20 => :to_i,
    21 => :to_i,
    22 => :to_i,
    23 => :to_i,
    700 => :to_f,
    701 => :to_f
  }

  class Database < ServerSide::Database
    def make_connection
      PGconn.connect(
        @opts[:host] || '127.0.0.1',
        @opts[:port] || 5432,
        '', '',
        @opts[:database] || 'test',
        @opts[:user] || 'postgres',
        @opts[:password] || '240374'
      )
    end
    
    def query(opts = nil)
      Postgres::Dataset.new(self, opts)
    end
  end
  
  class Dataset < ServerSide::Dataset
    SELECT = "SELECT %s FROM %s".freeze
    LIMIT = "LIMIT %s".freeze
  
    def each(field = nil, &block)
      execute
      @result.each do |r|
        row = fetch_row(r)
        block.call(field ? row[field] : row)
      end
      self
    end
    
    def all(field = nil)
      execute
      result = []
      @result.each do |r|
        row = fetch_row(r)
        result << (field ? row[field] : row)
      end
      result
    end
    
    def first
      execute(@opts.merge(:limit => 1))
      @result.each do |r|
        break fetch_row(r)
      end
    end
    
    def execute(opts = nil)
      @result = @db.conn.exec(compile_sql(opts))
      @fields = @result.fields.map {|s| s.to_sym}
      @types = (0..(@result.num_fields - 1)).map {|idx| @result.type(idx)}
      compile_row_fetcher
    end

    def compile_sql(opts = nil)
      custom_opts = !opts.nil?
      return @sql if @sql && custom_opts

      opts = @opts if opts.nil?
      fields = opts[:fields]
      select_fields = fields ? field_list(fields) : "*"
      select_source = source_list(opts[:source]) 
      select_clause = SELECT % [select_fields, select_source]
      
      limit = opts[:limit]
      limit_clause = limit ? LIMIT % [limit] : ''
      
      sql = [select_clause, limit_clause].join(' ')
      @sql = sql unless custom_opts
    end
    
    def compile_row_fetcher
      parts = (0..(@result.num_fields - 1)).inject([]) do |m, f|
        translate_fn = PG_TYPES[@types[f]]
        translator = translate_fn ? ".#{translate_fn}" : ""
        m << ":#{@fields[f]} => r[#{f}]#{translator}"
      end
      l = eval("lambda {|r|{#{parts.join(',')}}}")
      extend(Module.new {define_method(:fetch_row, &l)})
    end
  end
end

DB = Postgres::Database.new

$d = DB.query.from(:foo)

$d.each {|n| puts n[:name]}

__END__

class Database
  attr_reader :conn

  def initialize
    @conn = PGconn.connect('localhost', 5432, '', '', 
      'reality_development', 'postgres', '240374')
  end

  def query(sql)
    Query.new(@conn, sql)
  end
  
  def select(opts)
    return Query.new(@conn, opts[:sql]) if opts[:sql]

    if opts[:select]
      fields = opts[:select].is_a?(Array) ?
        opts[:select].join(',') : opts[:select]
    else
      fields = '*'
    end
    sql = "SELECT #{fields} FROM #{opts[:table]}"
    sql << " WHERE #{opts[:where]}" if opts[:where]
    Query.new(@conn, sql)
  end
  
  def select_count(opts)
    select(opts.merge(:select => 'COUNT(*)')).each {|r| return r[:count]}
  end
  
  def literal_repr(o)
    if String === o
      "'#{o}'"
    else
      o.to_s
    end
  end  

  def [](name)
    Filter.new(self, {:table => name})
  end
end

PG_TYPES = {
  16 => :to_bool,
  20 => :to_i,
  21 => :to_i,
  22 => :to_i,
  23 => :to_i,
  700 => :to_f,
  701 => :to_f
}


class Query
  attr_reader :result, :fields
  
  def initialize(conn, sql)
    @conn = conn
    @sql = sql
  end
  
  def execute(*args)
    @result = @conn.exec(@sql)
    @fields = @result.fields.map {|s| s.to_sym}
    @types = (0..(@result.num_fields - 1)).map {|idx| @result.type(idx)}
    compile_row_fetcher
  end
  
  def compile_row_fetcher
    parts = (0..(@result.num_fields - 1)).inject([]) do |m, f|
      translate_fn = PG_TYPES[@types[f]]
      translator = translate_fn ? ".#{translate_fn}" : ""
      m << ":#{@fields[f]} => r[#{f}]#{translator}"
    end
    l = eval("lambda {|r|{#{parts.join(',')}}}")
    extend(Module.new {define_method(:fetch_row, &l)})
  end
  
  def each(&block)
    execute
    @result.each do |r|
      block.call(fetch_row(r))
    end
    self
  end
  
  def row_count
    @result.num_tuples
  end
end

class Filter
  def initialize(db, opts)
    @db = db
    @opts = opts 
  end
  
  def compile_conditions(conditions)
    return nil unless conditions
    
    result = conditions.shift
    while result && (result =~ /\?/)
      a = @db.literal_repr(conditions.shift)
      result.sub!(/\?/, a)
    end
    result
  end
  
  def filter(conditions)
    Filter.new(@db, @opts.merge(:where => compile_conditions(conditions)))
  end
  
  alias_method :select, :filter
  
  def first(opts = nil)
    opts ? (opts[:limit] = 1) : (opts = {:limit => 1})
    each {|r| return r}
  end
  
  def[](attributes)
    params = []
    rule = attributes.inject('') {|m, kv| params << kv[1]; m << "(#{kv[0]} = ?)"}
    filter(params.unshift(rule)).first
  end
  
  def all(opts = nil)
    a = []
    each(opts) {|r| a << r}
    a
  end
  
  def each(opts = nil, &block)
    @db.select(opts ? @opts.merge(opts) : @opts).each(&block)
  end
  
  def count(opts = nil)
    @db.select_count(opts ? @opts.merge(opts) : @opts)
  end
  
  def summary(*fields)
    @db.select(@opts.merge(:select => fields)).each {|r| return r}
  end
  
  def update(values)
    @db.update(@opts, values)
  end
end

class Symbol
  def max
    "max(#{to_s}) AS max_#{to_s}"
  end

  def min
    "min(#{to_s}) AS min_#{to_s}"
  end
end

################################################################################
################################################################################

$db = Database.new
$t = $db[:states]

#puts $db[:nodes].all.inspect

puts $t.summary(:quality.max, :quality.min, :stamp.max, :stamp.min).inspect

################################################################################
# Table creation
################################################################################
#$db.create_table(:states, 
#  :fields => {
#    id => :int, 
#    :node_id => :int, 
#    :quality => :int, 
#    :stamp => :double,
#    :value => :text, 
#    :datatype => :int},
#  :index => :id
#);

################################################################################
# Joins
################################################################################

#
#
#
#
#
#
#
#
#
#

require 'benchmark'

def t1
  $t.each {|n| n[:node_id]}
end

def t2
  $t.filter(nil).each() {|n| n.inspect}
end

n = 100
Benchmark.bm do |x|
  x.report {n.times {t1}}  
  x.report {n.times {t2}}  
end