# = Database Connection
#
# Encapsulates a database connection.
#
# === Design
#
# Try to make the methods to work with oids.
# Do NOT implement descendants, use a root id (rid).
#
# === Todo
#
# - support caching
# - support prepared statements
# - foreign keys (delete cascade)
# - keep attribudes as marshaled!!
#
# === Investigate
#
# - should we use retry_query ?
#
# code: 
# George Moschovitis  <gm@navel.gr>
#
# (c) 2004 Navel, all rights reserved.
# $Id: connection.rb 79 2004-10-18 16:38:19Z gmosx $

module N; 

require "n/properties"
require "n/utils/array"
require "n/utils/time"

# = DbConnection
#
# A Connection to the Database.
#
class DbConnection
	include N::DbUtils
	
	# the actual connection to the database.
	attr_reader :rdb
	
	# If set to true, the select methods deserialize the rows to 
	# create entities.
	attr_accessor :deserialize 
	
	# Initializate a connection to the database.
	#
	def initialize(config)
		super
		@deserialize = true
		$log.debug "Created connection."
	end
	
	# Close the connection to the database.
	#
	def close()
		super
		$log.debug "Closed connection."
	end
	
	# --------------------------------------------------------------------
	# Basic methods
	
	# Put an entity to the database. Insert if this is a new entity or
	# update if this is an existing entity.
	#	
	def put(entity)
		if entity.oid
			# entity allready inserted, update!
			entity.__db_update(self)
		else
			# this is a new entity, insert!
			entity.__db_insert(self)
		end
	end
	alias_method :<<, :put
		
	# Force insertion of managed object.
	# Typically used for NON-entities.
	#
	def insert(obj)
		obj.__db_insert(self)
	end

	# Force update of managed object.
	# Typically used for relations.
	#
	def update(obj)
		obj.__db_update(self)
	end
	
	# Update only specific fields of the entity
	# 
	# Input:
	# sql = the sql code to updated the properties.
	#
	def update_properties(update_sql, ent_or_oid, klass = nil)
		oid = ent_or_oid.to_i()
		klass = ent_or_oid.class unless klass
		
		sql = "UPDATE #{klass::DBTABLE} SET #{update_sql} WHERE oid=#{oid}"
		retry_query(sql, klass)
	end
	alias_method :pupdate, :update_properties
	
	# Get an entity from the database.
	#
	# COOL: klass can be the class to fetch or the DBTABLE of the class.
	# thanks to duck typing it works correctly!
	#
	# Input:
	# oid = the entity oid, OR the entity name.
	#
	def get(oid, klass)
		if oid.to_i > 0 # a valid Fixnum ?
			get_by_oid(oid, klass)
		else
			get_by_name(oid, klass)
		end
	end
	
	# Get an entity by oid.
	#
	# COOL: klass can be the class to fetch or the DBTABLE of the class.
	# thanks to duck typing it works correctly!
	#
	def get_by_oid(oid, klass)
		rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} WHERE oid=#{oid}")
		return deserialize_one(rows, klass)
	end
	
	# Get an entity by name.
	#
	# COOL: klass can be the class to fetch or the DBTABLE of the class.
	# thanks to duck typing it works correctly!
	#
	def get_by_name(name, klass)
		rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} WHERE name='#{name}'")
		return deserialize_one(rows, klass)
	end

	# Get all entities of the given klass.
	#
	# COOL: klass can be the class to fetch or the DBTABLE of the class.
	# thanks to duck typing it works correctly!
	#
	# Used to be called 'collect' in an earlier version.
	#
	def get_all(klass, extrasql = nil)
		rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} #{extrasql}")
		return deserialize_all(rows, klass)
	end
	
	# Delete an entity from the database. Allways perform a deep delete.
	#
	# DONT use ::DBTABLE (allow classes as table names).
	#
	# Input:
	#
	# entity = Entity or oid to delete.
	# klass = klass of entity (can be nil if an entity is passed)
	#
	def delete(entity, klass = nil, cascade = true)
		if entity.is_a?(Fixnum)
			oid = entity
			entity = klass.new
			entity.oid = oid
		end
		
		transaction {
			entity.__db_pre_delete(self)
			exec_clear("DELETE FROM #{DbUtils.sql_table(entity.class)} WHERE oid=#{entity.oid}")
		}
	end
	alias_method :delete!, :delete
	
	# Recursively delete all descendants of this entity.
	# Operates in a transaction.
	#
	def delete_descendants(pid, pclass)
		return unless pclass.respond_to?(:__descendants_classes)
		
		for dclass in pclass.__descendants_classes
			if dclass.include?(N::ParentClass)
				rs = exec("SELECT oid FROM #{dclass::DBTABLE} WHERE pid=#{pid} AND pclass='#{pclass}'")
				exec_clear("DELETE FROM #{dclass::DBTABLE} where pid=#{pid} AND pclass='#{pclass}'")
			else
				rs = exec("SELECT oid FROM #{dclass::DBTABLE} WHERE pid=#{pid}")
				exec_clear("DELETE FROM #{dclass::DBTABLE} where pid=#{pid}")			
			end
			
			for tuple in (0...rs.num_tuples)
				delete_descendants(rs.getvalue(tuple, 0), dclass)
			end
			
			rs.clear()
		end	
	end
	alias_method :delete_descendants!, :delete_descendants
	
	# --------------------------------------------------------------------
	# Graph methods
	
	# = child
	#
	# Return one children of the parent
	#
	def child(entity, klass, extrasql = nil)		
		pid = entity.to_i()
		
		sql = "SELECT * FROM #{klass::DBTABLE}"
		sql << " WHERE pid=#{pid}" if pid > 0
		sql << " #{extrasql}" if extrasql

		rows = safe_query(sql)		
		
		return deserialize_one(rows, klass)
	end
	
	# = children
	#
	# entity = entity or oid.
	# klass = the class of the children to return.
	# extrasql = extra sql for limit/order etc.
	#
	# === Design:
	#
	# If the calculated pid is 0 returns all entities
	# of the given class. It works like the older collect method.
	# This make sense, the root of all objects has an oid = 0.
	# INVESTIGATE: is this a security problem?
	#
	# only_oids is not needed use the limit modifiers instead.
	# if you need oids, use specialized sql. You can use the
	# extrasql parameter to change the where clause too.
	#
	def children(entity, klass, extrasql = nil)		
		pid = entity.to_i()
		
		sql = "SELECT * FROM #{klass::DBTABLE}"
		sql << " WHERE pid=#{pid}" if pid > 0
		sql << " #{extrasql}" if extrasql

		rows = safe_query(sql)		
		
		return deserialize_all(rows, klass)
	end

	# = count_children
	#
	# Use extrasql to change the where clause.
	#
	def count_children(entity, klass, extasql = nil)
		pid = entity.to_i()
		
		sql = "SELECT COUNT(pid) FROM #{klass::DBTABLE}"
		sql << " WHERE pid=#{pid}" if pid > 0

		if rows = safe_query(sql)
			return rows[0][0].to_i()
		end 
		
		return 0
	end
	
	# --------------------------------------------------------------------
	# Transaction methods.
	
	def start
		exec_clear("START TRANSACTION")
	end
	
	def commit
		exec_clear("COMMIT")
	end
	
	def rollback
		exec_clear("ROLLBACK")
	end
	
	# Transaction helper
	#
	def transaction(&block)
		begin
			exec_clear("START TRANSACTION")
			yield 
			exec_clear("COMMIT")
		rescue => ex
			$log.error "DB Error: ERROR IN TRANSACTION"
			$log.error "DB Error: #{ex}, #{caller[0]} : #{caller[1]} : #{caller[2]}"
			exec_clear("ROLLBACK")
		end
	end
	
	# Useful in transactions
	#
	def exec(sql)
		@rdb.exec(sql)
	end
	
	# Useful in transactions
	# Exec an sql command and clear the resultset.
	#
	def exec_and_clear(sql)
		rows = @rdb.exec(sql)
		rows.clear()
	end
	alias_method :exec_clear, :exec_and_clear
		
	# --------------------------------------------------------------------
	# Standard SQL methods.
	
	# Perform a standard SQL query to the database. Deserializes the
	# results.
	#
	def select(sql, klass, join_fields = nil)
		rows = safe_query(sql)
		return deserialize_all(rows, klass, join_fields)
	end
	
	# Optimized for one result.
	#
	def select_one(sql, klass, join_fields = nil)
		rows = safe_query(sql)
		return deserialize_one(rows, klass, join_fields)
	end
	
	# Perform a standard SQL query to the database. Returns the result 
	# rows.
	#
	def query(sql)
		return safe_query(sql)
	end
	alias_method :sql, :query
	
	# Perform a standard SQL query to the database. Used for queries that
	# return one row (typically with values) ie COUNT, etc.
	#
	def query_one(sql)
		if rows = safe_query(sql)
			return rows[0]
		else
			return nil
		end
	end
	
	# Count the entities returned by the sql statement.
	#
	def count(sql)
		if rows = safe_query(sql)
			return rows[0][0].to_i()
		end 
		
		return 0
	end

	# Match a query, return true if a resultset was found
	#
	def match(sql)
		res = safe_query(sql)
		return true if res and (res.num_tuples > 0)
	end
	alias_method :match?, :match

end

end # module