Top Level Namespace

Defined Under Namespace

Modules: TableSyncer Classes: Hash

Instance Method Summary

• #sync_structure(db_to, db_from, table, actually_run_queries, my_options) ⇒ Object

Instance Method Details

#sync_structure(db_to, db_from, table, actually_run_queries, my_options) ⇒ Object

# File 'lib/table_syncer.rb', line 179

def sync_structure(db_to, db_from, table, actually_run_queries, my_options)
  print "structure syncing #{table}\n"
  good_structure = db_from.query("desc #{table}")
  all_from_columns = {}
  good_structure.each_hash{|h| all_from_columns[h['Field']] = h }
  good_structure.free
  # we basically cheat and just fakely recreate mismatched columns by "modifying them" to match the creation script given by 'show create table x' for that column
  good_creation_query = db_from.query("show create table #{table}")
  create_whole_table_script = good_creation_query.fetch_hash['Create Table']
  good_creation_script = create_whole_table_script.split("\n")
  good_creation_query.free

  questionable_to_structure = db_to.query("desc #{table}") rescue nil
  unless questionable_to_structure 
      if actually_run_queries
	db_to.query(create_whole_table_script)
      else
	print "would have created new table #{table} thus: #{create_whole_table_script}\n"
	db_to = db_from # fake it that they match so we don't raise any errors for the duration of this method call
      end
      questionable_to_structure = db_to.query("desc #{table}")
  end

  all_to_columns = {}
  questionable_to_structure.each_hash{|h| all_to_columns[h['Field']] = h }
  questionable_to_structure.free

  for column_name, specs in all_from_columns do

  	matching_creation_line = good_creation_script.find{|line| line =~ /^\s*`#{column_name}`/} # starts with column name--kind of fallible, but hey, we're working with english single words here

  	matching_to_column_specs = all_to_columns[column_name]
  	matching_creation_line = matching_creation_line[0..-2] if matching_creation_line[-1..-1] == ','
  	unless matching_to_column_specs # get it from the script
  		# create it
  		if specs['Extra'] != ''
  			raise "uh oh currently we don't sync id's they're assumed to exist already! Try deleting the old column #{column_name} or table #{table} entirely"
  		end

  		running = "ALTER TABLE #{table} ADD COLUMN #{matching_creation_line}"
  		print "running #{running}-- for #{column_name}\n"
  		db_to.query running if actually_run_queries
          else
  		# we don't want key differences to make a difference--those are handle after
  		to_specs_non_keyed = matching_to_column_specs.dup
                  specs_non_keyed = specs.dup
  		to_specs_non_keyed.delete('Key')
  		specs_non_keyed.delete('Key')
  		if specs_non_keyed != to_specs_non_keyed
  			line = "ALTER TABLE #{table} CHANGE #{column_name} #{matching_creation_line}"

			# for some reason the create table script doesn't include defaults if they're NULL or ''
			unless line =~ /default/i
				if specs_non_keyed['Default'] == nil
				   line += " DEFAULT NULL"
				else
				   line += " DEFAULT '#{ specs_non_keyed['Default'] }'"
			 	end
			end
  			print "modifying #{column_name} -- #{line} \n"
			print "#{specs_non_keyed.inspect} != the to guy: #{to_specs_non_keyed.inspect}"
  			db_to.query line if actually_run_queries
  	  end	
  		all_to_columns.delete(column_name)
  	end
   end

   for column_name, description in all_to_columns # left overs
		print "REMOVING COLUMN #{column_name}"
		db_to.query("ALTER TABLE #{table} DROP #{column_name}") if actually_run_queries
   end

   indices = db_from.query("show index from #{table};")
   all_indices = []
   indices.each_hash{|h| h.delete('Cardinality'); all_indices << h } # Cardinality doesn't make a difference...AFAIK
   indices.free

   existing_indices = db_to.query("show index from #{table}")
   all_existing_indices = []
   existing_indices.each_hash{|h| h.delete('Cardinality'); all_existing_indices << h }
   existing_indices.free
   different = []
   all_different = all_indices - all_existing_indices #.each{|hash| different << hash['Column_name'] unless existing_indices.include?(hash) }
   apparently_lacking = all_indices.map{|index| index['Column_name']} - all_existing_indices.map{|index| index['Column_name']}

   

   for index in apparently_lacking
	    # ltodo if it looks nice and generic then go ahead and add it
   end



   if all_indices != all_existing_indices # this is right
     print "\n\nWARNING #{table}: you are missing some indexes now or there is some type of discrepancy [indices aren't handled yet]-- you may want to add them a la\nCREATE INDEX some_name_usually_column_name_here ON #{table} (column_name_here)\n for apparently at least the following missing indices: #{apparently_lacking.inspect} 

            you have apparently mismatched indices for: #{all_different.map{|h| h['Column_name']}.inspect}\n\n
             --you might get away with dropping the old table and letting it be recreated -- that might add the right indices -- run with --verbose to see more info"

     if my_options[:verbose]
	print "the 'good' one is #{all_indices.inspect}, yours is #{all_existing_indices.inspect}"
     end
   end 
end