# = Database Tools
#
# Useful methods wor working with the Db BAckend
#
# code: 
# George Moschovitis  <gm@navel.gr>
# Elias Athanasopoulos  <elathan@navel.gr>
#
# (c) 2004 Navel, all rights reserved.
# $Id: tools.rb 71 2004-10-18 10:50:22Z gmosx $

module N; 

require "n/db"

# = DbConnection
#
# A Connection to the Database.
#
module DbTools

	# Lets say you have changed the definition of a managed object
	# (for example you have added a new prop_accessor, and want to 
	# automatically update the database schema. 
	# Compares the given class to the actual db schema and
	# automatically alters the backend table if needed.
	# No need to optimize this method.
	#
	def self.alter_table(klass)
		#
		# Use the following query to see the fields in system level:
		#
		# sql  = "SELECT attname, attnum FROM pg_attribute, pg_class "
		# sql << "WHERE attrelid = pg_class.oid " 
		# sql << "AND relname = '#{klass::DBTABLE}' AND attnum > 0;"
		#
		# In orer to test alter_table() use the following tests:
		#
		# klass.__props << N::Property.new("new_field", String)
		# klass.__props.delete_if{ |member| member.name == "new_field" }
		#
		# Remember you must not uncomment both of the above statements.
		# Uncomment the first one, use alter_table(), the uncomment the
		# seconde one and re-use alter_table().
		#
		$db.open { |db|
			
			sql = "SELECT * FROM #{klass::DBTABLE} LIMIT 1;"			
			table_fields = db.exec(sql).fields
	
			# find fields to be added.
			properties_to_add = []
			klass.__props.each { |klass_member|
				properties_to_add << klass_member unless table_fields.include? klass_member.name	
			}
	
			# find fields to be removed.
			properties_to_remove = []
			table_fields.each { |field|
				properties_to_remove << field unless klass.__props.map{ |member| member.name }.include? field
			}
	
			# Add new fields
			fields = []
			properties_to_add.each { |p|
				field = "#{p.symbol}"
				if p.sql
					field << " #{p.sql}"
				else
					field << " #{db.class::TYPEMAP[p.klass]}"
				end
				field << " #{p.sql}" if p.sql
			
				fields << field 
			}
		
			for field in fields
				sql = "ALTER TABLE #{klass::DBTABLE} ADD COLUMN #{field}"
				db.exec_clear(sql)
			end
			
			removed = nil
			properties_to_remove.each { |p|
				sql = "ALTER TABLE #{klass::DBTABLE} DROP COLUMN #{p} RESTRICT"
				db.exec_clear(sql)
				removed = true
			}
	
			if removed
				sql = "UPDATE #{klass::DBTABLE}; VACUUM FULL #{klass::DBTABLE};"
				db.exec_clear(sql)
			end
			
		}
	end
	
	# NOT IMPLEMENTED
	#
	def self.alter_column(klass, old_field, new_field)
	end
	 
	# NOT IMPLEMENTED
	#
	def self.rename_table(old_klass, new_klass)
	end

end

end # module