# * George Moschovitis  <gm@navel.gr>
# (c) 2004-2005 Navel, all rights reserved.
# $Id: psql.rb 270 2005-03-07 17:52:16Z gmosx $

require 'postgres'

require 'og/adapter'
require 'og/connection'
require 'glue/attribute'

module Og

# The PostgreSQL adapter. This adapter communicates with 
# an PostgreSQL rdbms. For extra documentation see 
# lib/og/adapter.rb

class PsqlAdapter < Adapter

	def self.escape(str)
		return nil unless str
		return PGconn.escape(str)
	end

	def self.timestamp(time = Time.now)
		return nil unless time
		return time.strftime("%Y-%m-%d %H:%M:%S")
	end
	
	def self.date(date)
		return nil unless date
		return "#{date.year}-#{date.month}-#{date.mday}" 
	end

	def read_prop(p, idx)
		if p.klass.ancestors.include?(Integer)
			return "res.getvalue(tuple, #{idx}).to_i()"
		elsif p.klass.ancestors.include?(Float)
			return "res.getvalue(tuple, #{idx}).to_f()"
		elsif p.klass.ancestors.include?(String)
			return "res.getvalue(tuple, #{idx})"
		elsif p.klass.ancestors.include?(Time)
			return "#{self.class}.parse_timestamp(res.getvalue(tuple, #{idx}))"
		elsif p.klass.ancestors.include?(Date)
			return "#{self.class}.parse_date(res.getvalue(tuple, #{idx}))"
		elsif p.klass.ancestors.include?(TrueClass)
			return %|('t' == res.getvalue(tuple, #{idx}))|
		else 
			return "YAML::load(res.getvalue(tuple, #{idx}))"
		end		
	end

	def create_db(database, user = nil, password = nil)
		`createdb #{database} -U #{user}`
		super
	end

	def drop_db(database, user = nil, password = nil)
		`dropdb #{database} -U #{user}`
		super
	end

	def insert_code(klass, db, pre_cb, post_cb)
		props = props_for_insert(klass)
		values = props.collect { |p| write_prop(p) }.join(',') 

		sql = "INSERT INTO #{klass::DBTABLE} (#{props.collect {|p| p.name}.join(',')}) VALUES (#{values})"

		%{
			#{pre_cb}
			res = conn.store.exec("SELECT nextval('#{klass::DBSEQ}')")
			@oid = res.getvalue(0, 0).to_i
			res.clear
			conn.exec "#{sql}"
			#{post_cb}
		}
	end

	def new_connection(db)
		return PsqlConnection.new(db)
	end

	def calc_field_index(klass, db)
		res = db.query "SELECT * FROM #{klass::DBTABLE} LIMIT 1"
		meta = db.managed_classes[klass]
	
		for field in res.fields
			meta.field_index[field] = res.fieldnum(field)
		end

	ensure
		res.clear if res
	end

	def create_table(klass, db)
		conn = db.get_connection

		fields = create_fields(klass)

		sql = "CREATE TABLE #{klass::DBTABLE} (#{fields.join(', ')}"
		
		# Create table constrains.
		
		if klass.__meta and constrains = klass.__meta[:sql_constrain]
			sql << ", #{constrains.join(', ')}"
		end
		
		sql << ") WITHOUT OIDS;"
		
		# Create indices.
		
		if klass.__meta and indices = klass.__meta[:sql_index]
			for data in indices
				idx, options = *data
				idx = idx.to_s
				pre_sql, post_sql = options[:pre], options[:post]
				idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
				sql << " CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx});"  
			end
		end

		begin
			conn.store.exec(sql).clear
			Logger.info "Created table '#{klass::DBTABLE}'."
		rescue => ex
			# gmosx: any idea how to better test this?
			if ex.to_s =~ /relation .* already exists/i
				Logger.debug 'Table already exists' if $DBG
				return
			else
				raise
			end
		end

		# Create join tables if needed. Join tables are used in
		# 'many_to_many' relations.
		
		if klass.__meta and joins = klass.__meta[:sql_join] 
			for data in joins
				# the class to join to and some options.
				join_name, join_class, options = *data
				
				# gmosx: dont use DBTABLE here, perhaps the join class
				# is not managed yet.
				join_table = "#{self.class.join_table(klass, join_class, join_name)}"
				join_src = "#{self.class.encode(klass)}_oid"
				join_dst = "#{self.class.encode(join_class)}_oid"
				begin
					conn.store.exec("CREATE TABLE #{join_table} ( key1 integer NOT NULL, key2 integer NOT NULL )").clear
					conn.store.exec("CREATE INDEX #{join_table}_key1_idx ON #{join_table} (key1)").clear
					conn.store.exec("CREATE INDEX #{join_table}_key2_idx ON #{join_table} (key2)").clear
				rescue => ex
					# gmosx: any idea how to better test this?
					if ex.to_s =~ /relation .* already exists/i
						Logger.debug "Join table already exists" if $DBG
					else
						raise
					end
				end
			end
		end

	ensure
		db.put_connection
	end

	def drop_table(klass)
		super
		exec "DROP SEQUENCE #{klass::DBSEQ}"		
	end

	# Generate the property for oid.

	def eval_og_oid(klass)
		klass.class_eval %{
			prop_accessor :oid, Fixnum, :sql => 'serial PRIMARY KEY'
		}
	end

end

# The PostgreSQL connection.

class PsqlConnection < Connection

	def initialize(db)
		super

		config = db.config

		begin
			@store = PGconn.connect(
				config[:address], 
				config[:port], 
				nil, 
				nil, 
				config[:database], 
				config[:user].to_s, 
				config[:password].to_s
			)
		rescue => ex
			# gmosx: any idea how to better test this?
			if ex.to_s =~ /database .* does not exist/i
				Logger.info "Database '#{config[:database]}' not found!"
				@db.adapter.create_db(config[:database], config[:user])
				retry
			end
			raise
		end
	end

	def close
		@store.close
		super
	end

	def query(sql)
		Logger.debug sql if $DBG
		begin
			return @store.exec(sql)
		rescue => ex
			Logger.error "DB error #{ex}, [#{sql}]"
			Logger.error ex.backtrace.join("\n")
			return nil
		end
	end

	def exec(sql)
		Logger.debug sql if $DBG
		begin
			@store.exec(sql).clear
		rescue => ex
			Logger.error "DB error #{ex}, [#{sql}]"
			Logger.error ex.backtrace.join("\n")
		end
	end

	def valid_res?(res)
		return !(res.nil? or 0 == res.num_tuples)
	end

	def read_one(res, klass)
		return nil unless valid_res?(res)

		obj = klass.new
		obj.og_read(res, 0)
	
		res.clear
		return obj
	end

	def read_all(res, klass)
		return [] unless valid_res?(res)
		objects = []

		for tuple in (0...res.num_tuples)
			obj = klass.new
			obj.og_read(res, tuple)
			objects << obj
		end
		
		res.clear
		return objects
	end

	def read_int(res, idx = 0)
		val = res.getvalue(0, idx).to_i
		res.clear
		return val
	end

end

end