require 'sqlite3'

module Dev
# 
# Branches table
#   Name text		e.g. google/devgem/trunk
#   Uri  text       e.g. https://devgem.googlecode.com/svn/trunk
#
# create table Branches(Name text,Uri text,UNIQUE(Name));
#
class Database
  attr_accessor :db
  
  def self.filename
    return Dev::Environment.dev_root + "/dev.db"
  end

  def self.get_table_names(filename)
    names=Array.new
    db = SQLite3::Database.new filename
	db.execute("select name from sqlite_master where type='table' ORDER BY name") do |row|
	  names << row[0]
	end
	return names
  end

  def initialize
    filename=Dev::Database.filename
    table_names=Dev::Database.get_table_names(filename)
	@db = SQLite3::Database.new filename
	@db.execute("create table Branches(Name text,Uri text,UNIQUE(Name));") if !table_names.include? "Branches"

	columns=""
	[:uri,:revision,:dir,:user,:machine,:ruby_version,:ruby_platform,:cmd,:status,:start_time,:end_time,:elapsed,:timeout,:timed_out,:output,:error].each { |s|
	  columns="#{columns}," if columns.length > 0
	  columns="#{columns}#{s.to_s} text"
	}
	@db.execute("create table Results(#{columns});") if ! table_names.include? "Results"
	@db.close
	@db=nil
  end

  def set_branch_uri(name,uri)
    @db = SQLite3::Database.new Dev::Database.filename
    @db.execute("insert or replace into Branches (Name,Uri) VALUES ('#{name}','#{uri}');")
	@db.close
	@db=nil
  end

  def has_result(h)
    results=get_results({:machine=>h[:machine],:user=>h[:user],:start_time=>h[:start_time]})
	return true if results.length > 0
	return false
  end

  def add_result(h)
    columns=""
	values=""
	[:uri,:revision,:dir,:user,:machine,:ruby_version,:ruby_platform,:cmd,:status,:start_time,:end_time,:elapsed,:timeout,:timed_out,:output,:error].each { |s|
	  sval=""
	  sval=h[s].to_s if h.has_key?(s)
	  sval.gsub("'","''")	# need to escape single quotes for sqlite
	  columns="#{columns}," if columns.length > 0
	  values="#{values}," if values.length > 0
	  columns="#{columns}#{s.to_s}"
	  values="#{values}'#{sval}'"
	}
	#puts "insert or replace into Results (#{columns}) VALUES (#{values});"
	@db = SQLite3::Database.new Dev::Database.filename
	@db.execute("insert or replace into Results (#{columns}) VALUES (#{values});")
	@db.close
	@db=nil
  end

  def get_results(where_hash)
    array=Array.new
    where=""
	where_hash.each{ |k,v|
	  sval=v.to_s
	  sval.gsub("'","''")	# need to escape single quotes for sqlite
	  where="#{where} AND " if where.length > 0
	  where="#{where}#{k.to_s}='#{sval}'"
	}
    
	columns=""
	[:uri,:revision,:dir,:user,:machine,:ruby_version,:ruby_platform,:cmd,:status,:start_time,:end_time,:elapsed,:timeout,:timed_out,:output,:error].each { |s|
	  columns="#{columns}," if columns.length > 0
	  columns="#{columns}#{s.to_s}"
	}

	@db = SQLite3::Database.new Dev::Database.filename
	@db.execute("select #{columns} from Results where #{where};") do |row|
	  h=Hash.new
	  index = 0
	  [:uri,:revision,:dir,:user,:machine,:ruby_version,:ruby_platform,:cmd,:status,:start_time,:end_time,:elapsed,:timeout,:timed_out,:output,:error].each { |s|
	    #puts "row[#{index}].to_s=#{row[index].to_s}"
	    h[s]=row[index].to_s
	    index=index+1
	  }
	  array << h if !h.empty?
	end
	@db.close
	@db=nil
	  
	return array
  end

  def get_branch_uri(name)
    uri=""
	@db = SQLite3::Database.new Dev::Database.filename
    @db.execute("select Uri from Branches where Name='#{name}';") do |row|
	  uri=eval(row[0].to_s)[0]
	end
	@db.close
	@db=nil
	return uri
  end

  def find_branches(pattern)
    names=Array.new
	sql="select Name from Branches where Name LIKE '#{pattern}';"
	sql="select Name from Branches;" if pattern.nil? || pattern.length==0
	@db = SQLite3::Database.new Dev::Database.filename
	@db.execute(sql) do |row|
	  names << row[0] if(row[0].length > 0)
	end
	@db.close
	@db=nil
	return names
  end
end # class Database
end # module Dev