# code: 
# * George Moschovitis  <gm@navel.gr>
# * Elias Athanasopoulos  <elathan@navel.gr>
#
# (c) 2004 Navel, all rights reserved.
# $Id: mysql.rb 159 2004-11-18 10:18:30Z gmosx $

require "mysql"

require "og/backend"

module Og

# = Utils
#
# A collection of useful utilities.
#
module Utils

	# Escape an SQL string
	#
	def self.escape(str)
		return nil unless str
		return Mysql.quote(str)
	end

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

	# Return an sql string evaluator for the property.
	# No need to optimize this, used only to precalculate code.
	# YAML is used to store general Ruby objects to be more 
	# portable.
	#
	# FIXME: add extra handling for float.
	#
	def self.write_prop(p)
		if p.klass.ancestors.include?(Integer)
			return "#\{@#{p.symbol} || 'NULL'\}"
		elsif p.klass.ancestors.include?(Float)
			return "#\{@#{p.symbol} || 'NULL'\}"		
		elsif p.klass.ancestors.include?(String)
			return "'#\{Og::Utils.escape(@#{p.symbol})\}'"
		elsif p.klass.ancestors.include?(Time)
			return %|#\{@#{p.symbol} ? "'#\{Og::Utils.timestamp(@#{p.symbol})\}'" : 'NULL'\}|
		elsif p.klass.ancestors.include?(Date)
			return %|#\{@#{p.symbol} ? "'#\{Og::Utils.date(@#{p.symbol})\}'" : 'NULL'\}|
		elsif p.klass.ancestors.include?(TrueClass)
			return "#\{@#{p.symbol} || 'NULL'\}"
		else 
			return %|#\{@#{p.symbol} ? "'#\{Og::Utils.escape(@#{p.symbol}.to_yaml)\}'" : "''"\}|
		end
	end		

	# Return an evaluator for reading the property.
	# No need to optimize this, used only to precalculate code.
	#
	def self.read_prop(p, idx)
		if p.klass.ancestors.include?(Integer)
			return "res[#{idx}].to_i()"
		elsif p.klass.ancestors.include?(Float)
			return "res[#{idx}].to_f()"
		elsif p.klass.ancestors.include?(String)
			return "res[#{idx}]"
		elsif p.klass.ancestors.include?(Time)
			return "Og::Utils.parse_timestamp(res[#{idx}])"
		elsif p.klass.ancestors.include?(Date)
			return "Og::Utils.parse_date(res[#{idx}])"
		elsif p.klass.ancestors.include?(TrueClass)
			return "('true' == res[#{idx}])"
		else 
			return "YAML::load(res[#{idx}])"
		end				
	end

	# Returns the props that will be included in the insert query.
	# The oid property is rejected because it is mapped to an 
	# AUTO_INCREMENT column.
	#
	def self.props_for_insert(klass)
		klass.__props.reject { |p| :oid == p.symbol }
	end

		# Returns the code that actually inserts the object into the
	# database. Returns the code as String.
	#
	def self.insert_code(klass, sql, pre_cb, post_cb)
		%{
			#{pre_cb}
			conn.exec "#{sql}"
			@oid = conn.db.conn.insert_id()
			#{post_cb}
		}
	end

	# generate the mapping of the database fields to the
	# object properties.
	#
	def self.calc_field_index(klass, og)
		res = og.query "SELECT * FROM #{klass::DBTABLE} LIMIT 1"
		meta = og.managed_classes[klass]
	
		for idx in (0...res.num_fields)
			meta.field_index[res.fetch_field.name] = idx
		end
	end
	
	# Generate the property for oid
	#
	def self.eval_og_oid(klass)
		klass.class_eval %{
			prop_accessor :oid, Fixnum, :sql => "integer AUTO_INCREMENT PRIMARY KEY"
		}
	end
end

# = MysqlBackend
#
# Implements a MySQL powered backend.
#
class MysqlBackend < Og::Backend

	# A mapping between Ruby and SQL types.
	#
	TYPEMAP = {
		Integer => "integer",
		Fixnum => "integer",
		Float => "float",
		String => "text",
		Time => "timestamp",
		Date => "date",
		TrueClass => "boolean",
		Object => "text",
		Array => "text",
		Hash => "text"		
	}
	
	# Intitialize the connection to the RDBMS.
	# 		
	def initialize(config)
		begin
			@conn = Mysql.connect(config[:address], config[:user], 
					config[:password], config[:database])
		rescue => ex
			if ex.errno == 1049 # database does not exist.
				$log.info "Database '#{config[:database]}' not found!"
				MysqlBackend.create_db(config[:database], config[:user], config[:password])
				retry
			end
			raise
		end
	end

	# Create the database.
	#
	def self.create_db(database, user = nil, password = nil)
		$log.info "Creating database '#{database}'."
		`mysqladmin -f --user=#{user} --password=#{password} create #{database}`
	end
	
	# Drop the database.
	#
	def self.drop_db(database, user = nil, password = nil)
		$log.info "Dropping database '#{database}'."
		`mysqladmin -f --user=#{user} --password=#{password} drop #{database}`
	end

	# Execute an SQL query and return the result 
	#
	def query(sql)
		$log.debug sql if $DBG
		return @conn.query(sql)
	end
	
	# Execute an SQL query, no result returned.
	#
	def exec(sql)
		$log.debug sql if $DBG
		@conn.query(sql)
	end

	# Execute an SQL query and return the result. Wrapped in a rescue
	# block.
	#
	def safe_query(sql)
		$log.debug sql if $DBG
		begin
			return @conn.query(sql)
		rescue => ex
			$log.error "DB error #{ex}, [#{sql}]"
			$log.error ex.backtrace
			return nil
		end
	end
	
	# Execute an SQL query, no result returned. Wrapped in a rescue 
	# block.
	#
	def safe_exec(sql)
		$log.debug sql if $DBG
		begin
			@conn.query(sql)
		rescue => ex
			$log.error "DB error #{ex}, [#{sql}]"
			$log.error ex.backtrace
		end
	end
	
	# Check if it is a valid resultset.
	#
	def valid?(res)
		return !(res.nil? or 0 == res.num_rows)
	end
	
	# Start a new transaction.
	#
	def start
		# no transaction support
	end
	
	# Commit a transaction.
	#
	def commit
		# no transaction support
	end
	
	# Rollback transaction.
	#
	def rollback
		# no transaction support
	end	

	# Create the managed object table. The properties of the
	# object are mapped to the table columns. Additional sql relations
	# and constrains are created (indicices, sequences, etc).
	#
	def create_table(klass)
		fields = []
		
		klass.__props.each do |p|
			klass.sql_index(p.symbol) if p.meta[:sql_index]
			
			field = "#{p.symbol}"
			
			if p.meta and p.meta[:sql]
				field << " #{p.meta[:sql]}"
			else
				field << " #{TYPEMAP[p.klass]}"
			end
			
			fields << field 
		end

		sql = "CREATE TABLE #{klass::DBTABLE} (#{fields.join(', ')}"
		
		# Create table constrains
		
		if klass.__meta and constrains = klass.__meta[:sql_constrain]
			sql << ", #{constrains.join(', ')}"
		end
		
		sql << ');'
		
		begin
			exec(sql)
			$log.info "Created table '#{klass::DBTABLE}'."
		rescue => ex
			if ex.errno == 1050 # table already exists.
				$log.debug "Table already exists" if $DBG
			else
				raise
			end
		end
		
		# Create indices
		
		if klass.__meta
			for data in klass.__meta[:sql_index]
				idx, options = *data
				idx = idx.to_s
				pre_sql, post_sql = options[:pre], options[:post]
				idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
				exec "CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx})"  
			end
		end		
	end

	# Deserialize one row of the resultset.
	#
	def deserialize_one(res, klass)	
		return nil unless valid?(res)
		
		# gmosx: Managed objects should have no params constructor.
		row = res.fetch_row()
		entity = klass.new()
		entity.og_deserialize(row)
		
#		get_join_fields(res, 0, entity, join_fields) if join_fields

		return entity
	end
	
	# Deserialize all rows of the resultset.
	#
	def deserialize_all(res, klass)	
		return nil unless valid?(res)

		entities = []

		for tuple in (0...res.num_rows)
			row = res.fetch_row()

			entity = klass.new()
			entity.og_deserialize(row)
			
#			get_join_fields(res, tuple, entity, join_fields) if join_fields
			
			entities << entity
		end
		
		return entities
	end

	# Return a single integer value from the resultset.
	#
	def get_int(res, idx = 0)	
		return res.fetch_row[idx].to_i
	end
	
end

end # module