Module: DatabaseUtils

Defined in:
lib/buzzcore/database_utils.rb

Class Method Summary collapse

Class Method Details

.clear_database(aDbDetails) ⇒ Object 



35
36
37
 
# File 'lib/buzzcore/database_utils.rb', line 35

def self.clear_database(aDbDetails)
	response = POpen4::shell("mysqldump -u #{aDbDetails[:username]} -p#{aDbDetails[:password]} --add-drop-table --no-data #{aDbDetails[:database]} | grep ^DROP | mysql -u #{aDbDetails[:username]} -p#{aDbDetails[:password]} #{aDbDetails[:database]}")
end

.create_database(aDbDetails, aDatabase = nil) ⇒ Object 



39
40
41
42
43
 
# File 'lib/buzzcore/database_utils.rb', line 39

def self.create_database(aDbDetails,aDatabase=nil)
	aDbDetails[:database] = aDatabase if aDatabase
	return false if !aDbDetails[:database]
	response = POpen4::shell("mysqladmin -u #{aDbDetails[:username]} -p#{aDbDetails[:password]} create #{aDbDetails[:database]}")
end

.database_exists(aDbDetails, aDatabase = nil) ⇒ Object

www.cyberciti.biz/faq/how-do-i-empty-mysql-database/

drop all tables : mysqldump -uusername -ppassword -hhost \ –add-drop-table –no-data database | grep ^DROP | \ mysql -uusername -ppassword -hhost database



23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/buzzcore/database_utils.rb', line 23

def self.database_exists(aDbDetails,aDatabase=nil)
	aDbDetails[:database] = aDatabase if aDatabase
	return false if !aDbDetails[:database]
	response = POpen4::shell("mysql -u #{aDbDetails[:username]} -p#{aDbDetails[:password]} -e 'use #{aDbDetails[:database]}'") do |r|
		if r[:stderr] && r[:stderr].index("ERROR 1049 ")==0		# Unknown database
			r[:exitcode] = 0 
			return false
		end
	end
	return (response && response[:exitcode]==0)
end

.ensure_empty_database(aDbDetails, aDatabase = nil) ⇒ Object 



45
46
47
48
49
50
51
52
 
# File 'lib/buzzcore/database_utils.rb', line 45

def self.ensure_empty_database(aDbDetails,aDatabase=nil)
	aDbDetails[:database] = aDatabase if aDatabase
	if database_exists(aDbDetails)
		clear_database(aDbDetails)
	else
		create_database(aDbDetails)
	end
end

.execute_sql_file(filename, aUser = nil, aPassword = nil) ⇒ Object 



4
5
6
7
8
9
10
11
12
 
# File 'lib/buzzcore/database_utils.rb', line 4

def self.execute_sql_file(filename,aUser=nil,aPassword=nil)
  conf = ActiveRecord::Base.configurations[RAILS_ENV]
  pw = aPassword || conf['password'].to_s || ''
  user = aUser || conf['username'].to_s || ''
  cmd_line = "mysql -h #{conf['host']} -D #{conf['database']} #{user.empty? ? '' : '-u '+user} #{pw.empty? ? '' : '-p'+pw} <#{filename}"
  if !system(cmd_line)
    raise Exception, "Error executing "+cmd_line
  end
end

.load_database(aDbDetails, aSqlFile) ⇒ Object 



54
55
56
57
 
# File 'lib/buzzcore/database_utils.rb', line 54

def self.load_database(aDbDetails,aSqlFile)
	ensure_empty_database(aDbDetails)
	response = POpen4::shell("mysql -u #{aDbDetails[:username]} -p#{aDbDetails[:password]} #{aDbDetails[:database]} < #{aSqlFile}")
end

.process_rows(aTableName, aQuery) ⇒ Object

remember to select id column



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/buzzcore/database_utils.rb', line 83

def self.process_rows(aTableName,aQuery)
	rows = ActiveRecord::Base.connection.execute(aQuery).all_hashes
	# rows to hash
	rows.each do |r| 
		before = r.clone
		yield(r)	# modify r
		r[:delete] = true if r == before
	end
	rows.delete_if {|r| r[:delete]}
	rows.each do |r| 
		assigns = ''
		r.keys.filter_exclude(['id']).each do |k|
			assigns += ', ' unless assigns.empty?
			assigns += "#{k} = '#{r[k]}'"						# should do better escaping here for strings & numbers
		end
		ActiveRecord::Base.connection.execute("update `#{aTableName}` set #{assigns} where id=#{r['id']}")
	end	
end

.process_table(aTableName, aColumns, &block) ⇒ Object 



102
103
104
105
106
 
# File 'lib/buzzcore/database_utils.rb', line 102

def self.process_table(aTableName,aColumns,&block)
	aColumns.map!(&:to_s)
	aColumns << 'id' unless aColumns.include? 'id'
	process_rows(aTableName,"select #{aColumns.join(',')} from `#{aTableName}`",&block)
end

.save_database(aDbDetails, aSqlFile) ⇒ Object 



59
60
61
 
# File 'lib/buzzcore/database_utils.rb', line 59

def self.save_database(aDbDetails,aSqlFile)
	response = POpen4::shell("mysqldump --user=#{aDbDetails[:username]} --password=#{aDbDetails[:password]} --skip-extended-insert #{aDbDetails[:database]} > #{aSqlFile}")
end