module PgsqlStats
  class Collector
    @@connection = ActiveRecord::Base.connection
    cattr_accessor :connection

    def self.server_activity(database = nil)
      case database
      when Integer, /^\d+$/
        where = "WHERE datid = #{database.to_i}"
      when String, Symbol
        where = "WHERE datname = '#{database.to_s}'"
      else
        where = ""
      end
        
      @@connection.select_all("SELECT * FROM pg_stat_activity #{where}").collect { |row| Process.new(row) }
    end
    
    def self.databases(database = nil)
      @@connection.select_all("SELECT * FROM pg_stat_database").collect { |row| Database.new(row) }
    end
    
    def self.database(database)
      case database
      when Integer, /^\d+$/
        where = "WHERE datid = #{database.to_i}"
      when String, Symbol
        where = "WHERE datname = '#{database.to_s}'"
      else
        raise ArgumentError, "database must be a database name or OID"
      end
      
      Database.new(@@connection.select_one("SELECT * FROM pg_stat_database #{where}"))
    end
    
    def self.current_database
      oid = @@connection.select_one(<<-SQL
      SELECT pg_stat_get_backend_dbid(server.backendid) AS db_oid
      FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS server
      WHERE pg_stat_get_backend_pid(server.backendid) = pg_backend_pid();
      SQL
      )["db_oid"].to_i
      database(oid)
    end
    
    def self.all_tables
      load_tables("all")
    end
    
    def self.system_tables
      load_tables("sys")
    end
    
    def self.user_tables
      load_tables("user")
    end
    
    def self.all_indexes(table = nil)
      load_indexes("all", table)
    end
    
    def self.system_indexes(table = nil)
      load_indexes("sys", table)
    end
    
    def self.user_indexes(table = nil)
      load_indexes("user", table)
    end
    
    def self.all_sequences
      load_sequences("all")
    end
    
    def self.system_sequences
      load_sequences("sys")
    end
    
    def self.user_sequences
      load_sequences("user")
    end
    
    def self.reset_counters
      @@connection.select_one("SELECT pg_stat_reset() AS result")["result"] == "t"
    end
    
    #------------------------------------------------------------------------
    
    def self.load_tables(view)
      tables = []
      @@connection.transaction do
        stats = @@connection.select_all("SELECT * FROM pg_stat_#{view}_tables ORDER BY relid")
        io = @@connection.select_all("SELECT * FROM pg_statio_#{view}_tables ORDER BY relid")
        tables = stats.collect do |row|
          oid = row["relid"].to_i
          Table.new(@@connection, row, io.find { |iorow| iorow["relid"].to_i == oid })
        end
      end
      tables
    end
    
    def self.load_indexes(view, table = nil)
      case table
      when Integer, /^\d+$/
        where = "WHERE relid = #{table.to_i}"
      when String, Symbol
        where = "WHERE relname = '#{table.to_s}'"
      else
        where = ""
      end
        
      indexes = []
      @@connection.transaction do
        stats = @@connection.select_all("SELECT * FROM pg_stat_#{view}_indexes #{where} ORDER BY relid")
        io = @@connection.select_all("SELECT * FROM pg_statio_#{view}_indexes #{where} ORDER BY relid")
        indexes = stats.collect do |row|
          oid = row["indexrelid"].to_i
          Index.new(@@connection, row, io.find { |iorow| iorow["indexrelid"].to_i == oid })
        end
      end
      indexes
    end
    
    def self.load_sequences(view)
      @@connection.select_all("SELECT * FROM pg_statio_#{view}_sequences").collect do |row|
        Sequence.new(@@connection, row)
      end
    end
  end
end