# = PostgreSQL backend 
#
# Implement the Db backend using the PostgreSQL RDBMS. 
# Include the Psql backend to the standard DbConnection
# object to synthesize a PsqlConnection at runtime. 
#
# code:
# George Moschovitis  <gm@navel.gr>
#
# (c) 2004 Navel, all rights reserved.
# $Id$

require "postgres"
require "time.rb"
require "date.rb"

module N;

# Backend specific utils
#
module DbUtils
	
	# Escape an sql string
	#
	def self.escape(str)
		return nil unless str
		return PGconn.escape(str)
	end

	# Escape bytes
	#
	def self.escape_bytes(bytes)
		return nil unless bytes
		return PGconn.escape_bytea(bytes)
	end
	
	# Unescape bytes
	# FIXME: optimize this! Even better integrate this in the 
	# libpq library, or find out why the default method doesnt 
	# work.
	#
	def self.unescape_bytes(bytes)
		return bytes.gsub(/(\\\d+)/) { |m| m.gsub(/\\/, "").oct.chr }
	end

	# Convert a ruby time to an sql timestamp.
	# 
	def self.sql_timestamp(time = Time.now)
		return nil unless time
		return time.strftime("%Y-%m-%d %H:%M:%S")
	end
	
	# Output YYY-mm-dd
	#
	def self.sql_date(date)
		return nil unless date
		return "#{date.year}-#{date.month}-#{date.mday}" 
	end
	
	# Parse sql datetime
	#
	# TODO: Optimize this
	#
	def self.parse_sql_timestamp(str)
		return Time.parse(str)		
	end
	
	# Input YYYY-mm-dd
	#
	def self.parse_sql_date(str)
		return nil unless str
		return Date.strptime(str)
	end

	# Return an evaluator for reading the property
	# No need to optimize this, used only to precalculate code.
	#
	def self.read_prop(p, idx)
		case p.klass.to_s
			when Fixnum.name
				return "rows.getvalue(tuple, #{idx}).to_i()"
			when Float.name
				return "rows.getvalue(tuple, #{idx}).to_f()"
			when Time.name
				return "N::DbUtils.parse_sql_timestamp(rows.getvalue(tuple, #{idx}))"
			when Date.name
				return "N::DbUtils.parse_sql_date(rows.getvalue(tuple, #{idx}))"
			when TrueClass.name
				return "('true' == rows.getvalue(tuple, #{idx}))"
			when Object.name
				return "Marshal.load(N::DbUtils.unescape_bytes(rows.getvalue(tuple, #{idx})))"
			when Array.name
				return "Marshal.load(N::DbUtils.unescape_bytes(rows.getvalue(tuple, #{idx})))"
			when Hash.name
				return "Marshal.load(N::DbUtils.unescape_bytes(rows.getvalue(tuple, #{idx})))"
			else # String 
				return "rows.getvalue(tuple, #{idx})"
		end		
	end
end

# = PsqlBackend
#
# Implement the Db backend using the PostgreSQL RDBMS. 
#
module PsqlBackend

	# map between Ruby and SQL types
	#
	TYPEMAP = {
		Integer => "integer",
		Fixnum => "integer",
		Float => "float",
		String => "text",
		Time => "timestamp",
		Date => "date",
		TrueClass => "boolean",
		Object => "bytea",
		Array => "bytea",
		Hash => "bytea"
	}

	# Initialize a connection to the database
	#
	def initialize(config)
		@rdb = PGconn.connect(nil, nil, nil, nil, config[:database], 
				config[:user], config[:password])
	end

	# Close the connection to the database
	#
	def close()
		@rdb.close
	end

	# Create the sequence that generates the unified space id
	# oids. You *MUST* call this method on newly created
	# databases.
	#
	def create_schema()
		safe_query("CREATE SEQUENCE oids_seq")
	end
	
	# Drop the oid sequence
	#
	def drop_schema()
		safe_query("DROP SEQUENCE oids_seq")
	end

	# NOT IMPLEMENTED
	#
	def prepare_statement()
		@rdb.query("PREPARE")
	end
	alias_method :pstatement, :prepare_statement

	# NOT IMPLEMENTED
	#
	def execute_statement()
		self.select("EXECUTE")
	end
	alias_method :xstatement, :execute_statement
	
	# Create a table for an entity.
	#
	def create_table(klass)
		fields = []
		klass.__props.each { |p|
			field = "#{p.symbol}"
			if p.sql
				field << " #{p.sql}"
			else
				field << " #{TYPEMAP[p.klass]}"
			end
			
			fields << field 
		}
		
		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
			for data in klass.__meta[:sql_index]
				idx, pre_sql, post_sql = *data
				idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
				sql << " CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx});"  
			end
		end

		safe_query(sql)
		$log.info "Created table #{klass::DBTABLE}!"

		# create the sequence for this table. Even if the table
		# uses the oids_seq, attempt to create it. This makes
		# the system more fault tolerant.
		safe_query("CREATE SEQUENCE #{klass::DBSEQ}")
		$log.info "Created sequence #{klass::DBSEQ}!"
	end
	
	# Drop the entity table
	#
	def drop_table(klass)
		safe_query("DROP TABLE #{klass::DBTABLE}")
		if klass.include?(N::Sequenced)
			safe_query("DROP SEQUENCE #{klass::DBSEQ};")
		end
	end
	
	# Calculate the fields map for the given class
	#	
	def calc_fields(rows, klass)
		# gmosx SOS, FIXME, INVESTIGATE: no need for second safe hash ????
		fields = $db.fields[klass] = {}
		for field in rows.fields
			fields[field] = rows.fieldnum(field)
		end
		N::Managed.eval_db_read_row(klass)
	end
	
	# Grab the join fields returned by an sql join query, and attach them
	# to the entity.
	#
	def get_join_fields(rows, tuple, entity, join_fields)
		entity.join_fields = {}
		for f in join_fields
			entity.join_fields[f] = rows.getvalue(tuple, $db.fields[entity.class][f.to_s])
		end
	end
	
	# If the connection is in deserialize mode, deserialize one row.
	#
	def deserialize_one(rows, klass, join_fields = nil)	
		return nil unless rows
		
		calc_fields(rows, klass) unless $db.fields[klass]
		
		if @deserialize
			# gmosx: Enities should have no params constructor SOS.
			#
			entity = klass.new()
			entity.__db_read_row(rows, 0)
			
			get_join_fields(rows, 0, entity, join_fields) if join_fields
			
			rows.clear()
			return entity
		end

		return rows[0]
	end

	# If the connection is in deserialize mode, deserialize all rows.
	#
	def deserialize_all(rows, klass, join_fields = nil)	
		return nil unless rows

		calc_fields(rows, klass) unless $db.fields[klass]
		
		if @deserialize		
			entities = []
		
			for tuple in (0...rows.num_tuples)
				entity = klass.new()
				entity.__db_read_row(rows, tuple)
				
				get_join_fields(rows, tuple, entity, join_fields) if join_fields
				
				entities << entity
			end
			
			rows.clear()
			return entities
		end
		
		return rows
	end

	#
	# Execute an sql query. If the entity table is missing, create
	# it and retry.
	#
	# exec() is used instead of query because it is faster and we also 
	# need fields()
	#
	# FIXME: is the result cleared?
	#
	def retry_query(sql, klass = nil)
		$log.debug sql if $DBG
		retries = 0
		begin
			rows = @rdb.exec(sql)
			if rows && (rows.num_tuples > 0) 
				return rows
			else
				return nil
			end
		rescue => ex
			# Any idea how to better test this?
			if ex.to_s =~ /relation .* not exist/
				$log.info "RETRY_QUERY"
				# table does not exist, create it!
				create_table(klass)
				# gmosx: only allow ONE retry to avoid loops here!
				retries += 1 
				retry if retries <= 1
			else
				$log.error "RETRY_QUERY: surpressing db error: #{ex}, [#{sql}]"
#				$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
				return nil
			end
		end
	end

	# Execute an sql query and catch the errors.
	#
	# exec() is used instead of query because it is faster and we also 
	# need fields()
	#
	def safe_query(sql)	
		$log.debug sql if $DBG
		begin
			rows = @rdb.exec(sql)
			if rows && (rows.num_tuples > 0) 
				return rows
			else
				return nil
			end
		rescue => ex
				$log.error "SAFE_QUERY: surpressing db error #{ex}, [#{sql}]"
#				$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
			return nil
		end
	end

	# Get the next oid in the sequence for this klass
	#
	def next_oid(klass)
		retries = 0
		begin
			res = @rdb.exec("SELECT nextval('#{klass::DBSEQ}')")
			oid = res.getvalue(0, 0).to_i()
			res.clear()
			return oid
		rescue => ex
			# Any idea how to better test this?
			if ex.to_s =~ /relation .* not exist/
				$log.info "next_oid: #{ex}"
				# table does not exist, create it!
				create_table(klass)
				# gmosx: only allow ONE retry to avoid loops here!
				retries += 1 
				retry if retries <= 1
			else
				$log.error "DB Error: #{ex}, #next_oid"
				$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
				return nil
			end
		end
	end
	
end

# Mix into the DbConnection class
#
N::DbConnection.module_eval %{
	include N::PsqlBackend
}

end # namespace