# * George Moschovitis  <gm@navel.gr>
# * Elias Athanasopoulos  <elathan@navel.gr>
# (c) 2004-2005 Navel, all rights reserved.
# $Id: mysql.rb 248 2005-01-31 13:38:34Z gmosx $

require 'mysql'

require 'og/backend'

class Og

# 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 => 'tinyint',
		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.
				Logger.info "Database '#{config[:database]}' not found!"
				MysqlBackend.create_db(config[:database], config[:user], config[:password])
				retry
			end
			raise
		end
	end

	# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	# Utilities
	# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

	# 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::MysqlBackend.escape(@#{p.symbol})\}'"
		elsif p.klass.ancestors.include?(Time)
			return %|#\{@#{p.symbol} ? "'#\{Og::MysqlBackend.timestamp(@#{p.symbol})\}'" : 'NULL'\}|
		elsif p.klass.ancestors.include?(Date)
			return %|#\{@#{p.symbol} ? "'#\{Og::MysqlBackend.date(@#{p.symbol})\}'" : 'NULL'\}|
		elsif p.klass.ancestors.include?(TrueClass)
			return "#\{@#{p.symbol} ? 1 : 0 \}"
		else 
			return %|#\{@#{p.symbol} ? "'#\{Og::MysqlBackend.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::MysqlBackend.parse_timestamp(res[#{idx}])"
		elsif p.klass.ancestors.include?(Date)
			return "Og::MysqlBackend.parse_date(res[#{idx}])"
		elsif p.klass.ancestors.include?(TrueClass)
			return "('0' != 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

	# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	# Connection methods. 
	# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

	# Create the database.
	
	def self.create_db(database, user = nil, password = nil)
		Logger.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)
		Logger.info "Dropping database '#{database}'."
		`mysqladmin -f --user=#{user} --password=#{password} drop #{database}`
	end

	# Execute an SQL query and return the result 
	
	def query(sql)
		Logger.debug sql if $DBG
		return @conn.query(sql)
	end
	
	# Execute an SQL query, no result returned.
	
	def exec(sql)
		Logger.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)
		Logger.debug sql if $DBG
		begin
			return @conn.query(sql)
		rescue => ex
			Logger.error "DB error #{ex}, [#{sql}]"
			Logger.error ex.backtrace
			return nil
		end
	end
	
	# Execute an SQL query, no result returned. Wrapped in a rescue 
	# block.
	
	def safe_exec(sql)
		Logger.debug sql if $DBG
		begin
			@conn.query(sql)
		rescue => ex
			Logger.error "DB error #{ex}, [#{sql}]"
			Logger.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 = create_fields(klass, TYPEMAP)

		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)
			Logger.info "Created table '#{klass::DBTABLE}'."
		rescue => ex
			if ex.errno == 1050 # table already exists.
				Logger.debug "Table already exists" if $DBG
			else
				raise
			end
		end
		
		# 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(/\(.*\)/, "")
				exec "CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx})"  
			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_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_src = "#{self.class.encode(klass)}_oid"
				join_dst = "#{self.class.encode(join_class)}_oid"
				begin
					exec "CREATE TABLE #{join_table} ( key1 integer NOT NULL, key2 integer NOT NULL )"
					exec "CREATE INDEX #{join_table}_key1_idx ON #{join_table} (key1)"
					exec "CREATE INDEX #{join_table}_key2_idx ON #{join_table} (key2)"
				rescue => ex
					if ex.errno == 1050 # table already exists.
						Logger.debug "Join table already exists" if $DBG
					else
						raise
					end
				end
			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 [] 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