Class: MyDiff

Inherits:
Object
  • Object
show all
Defined in:
lib/mydiff.rb,
lib/mydiff/cli.rb,
lib/mydiff/change.rb

Overview

MyDiff helps you to apply changes from one MySQL database to another

It has some helper methods to

Example

md = MyDiff.new(:host => "localhost", :user => "root", :newdb => "mydiff_new", :olddb => "mydiff_old") # => <MyDiff>
md.newdb           #=> "mydiff_new"
md.olddb           #=> "mydiff_old"
md.new_tables      #=> ["new_table1", "new_table2"]
md.dropped_tables  #=> ["old_table"]

Defined Under Namespace

Classes: CLI, Change

Constant Summary collapse

VERSION = 
'0.0.1'

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config) ⇒ MyDiff

Creates a new MyDiff instance

Config options

  • :host - MySQL host

  • :user - MySQL user

  • :password - MySQL password

  • :newdb - Name of the database with the changes to apply

  • :olddb - Name of the database to apply the changes

Returns MyDiff



39
40
41
42
43
44
45
 
# File 'lib/mydiff.rb', line 39

def initialize(config)
  @my = Mysql::new(config[:host], config[:user], config[:password])
  @newdb = config[:newdb]
  @olddb = config[:olddb]
  @cli = CLI.new(self)
  @fields = {}
end

Instance Attribute Details

#cli ⇒ Object

Command Line Interface. See MyDiff::CLI



26
27
28
 
# File 'lib/mydiff.rb', line 26

def cli
  @cli
end

#my ⇒ Object

:nodoc:



27
28
29
 
# File 'lib/mydiff.rb', line 27

def my
  @my
end

#newdb ⇒ Object

Name of the database with changes



22
23
24
 
# File 'lib/mydiff.rb', line 22

def newdb
  @newdb
end

#olddb ⇒ Object

Name of the current database. Changes will be applied here



24
25
26
 
# File 'lib/mydiff.rb', line 24

def olddb
  @olddb
end

Instance Method Details

#changed_rows(table) ⇒ Object 



153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/mydiff.rb', line 153

def changed_rows(table)
  fields = fields_from(table)
  pkey, fields = extract_pkey_from(fields)
  my.select_db(@olddb)
  
  query = "SELECT "
  query << pkey.collect do |f|
    "o.#{f["Field"]} #{f["Field"]}"
  end.join(",")
  query << ","
  query << fields.collect do |f|
    "o.#{f["Field"]} o_#{f["Field"]}, n.#{f["Field"]} n_#{f["Field"]}"
  end.join(",")
  
  query << " FROM #{@olddb}.#{table} AS o INNER JOIN #{@newdb}.#{table} AS n ON "
  query << pkey.collect do |f|
    "n.#{f["Field"]} = o.#{f["Field"]}"
  end.join(" AND ")    
  
  result = my.query(query)
  changed_rows = []
  while row = result.fetch_hash
    changed_rows << row
  end
  changed_rows.select do |row|
    fields.inject(true) do |s,f|
      s and row["o_#{f["Field"]}"] == row["n_#{f["Field"]}"]
    end
  end
end

#changed_tables ⇒ Object

Returns an array with table names present on newdb and olddb which are different in content



83
84
85
86
87
88
 
# File 'lib/mydiff.rb', line 83

def changed_tables
  ntables = list_tables(@newdb)
  otables = list_tables(@olddb)
  
  ntables.select {|t| otables.include?(t) and table_changed?(t) }
end

#checksum_table(db, table) ⇒ Object 



214
215
216
217
 
# File 'lib/mydiff.rb', line 214

def checksum_table(db, table)
  @my.select_db(db)
  @my.query("CHECKSUM TABLE #{table}").fetch_row[1]
end

#count_rows(db, table) ⇒ Object 



196
197
198
199
200
 
# File 'lib/mydiff.rb', line 196

def count_rows(db, table)
  @my.select_db(db)
  res = @my.query("SELECT COUNT(*) FROM #{table}")
  res.fetch_row[0]
end

#data_fields_of(table) ⇒ Object 



210
211
212
 
# File 'lib/mydiff.rb', line 210

def data_fields_of(table)
  fields_from(table).select {|f| f["Key"] != "PRI"}.map {|f| f["Field"]}
end

#deleted_rows(table) ⇒ Object 



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/mydiff.rb', line 122

def deleted_rows(table)
  fields = fields_from(table)
  pkey, fields = extract_pkey_from(fields)
  my.select_db(@olddb)
  
  query = "SELECT "
  query << pkey.collect do |f|
    "o.#{f["Field"]} #{f["Field"]}"
  end.join(",")
  query << ","
  query << fields.collect do |f|
    "o.#{f["Field"]} o_#{f["Field"]}"
  end.join(",")
  
  query << " FROM #{@olddb}.#{table} AS o LEFT JOIN #{@newdb}.#{table} AS n ON "
  query << pkey.collect do |f|
    "n.#{f["Field"]} = o.#{f["Field"]}"
  end.join(" AND ")
  query << " WHERE "
  query << pkey.collect do |f|
    "n.#{f["Field"]} IS NULL"
  end.join(" AND ")
  
  result = my.query(query)
  deleted_rows = []
  while row = result.fetch_hash
    deleted_rows << row
  end
  deleted_rows
end

#dropped_tables ⇒ Object

Returns an array with table names present on olddb but not on newdb



74
75
76
77
78
79
 
# File 'lib/mydiff.rb', line 74

def dropped_tables
  ntables = list_tables(@newdb)
  otables = list_tables(@olddb)
  
  otables.select {|t| not ntables.include?(t) }
end

#extract_pkey_from(fields) ⇒ Object 



202
203
204
 
# File 'lib/mydiff.rb', line 202

def extract_pkey_from(fields)
  fields.partition {|f| f["Key"] == "PRI" }    
end

#fields_from(table) ⇒ Object 



184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/mydiff.rb', line 184

def fields_from(table)
  return @fields[table] if @fields[table]
  @my.select_db(@newdb)
  res = @my.query("DESCRIBE #{table}")
  fields = []
  while (field = res.fetch_hash)
    fields << field
  end
  
  @fields[table] ||= fields
end

#list_tables(db) ⇒ Object

Returns an array with table names for the database given in db



60
61
62
63
 
# File 'lib/mydiff.rb', line 60

def list_tables(db)
  @my.select_db(db)
  @my.list_tables
end

#new_rows(table) ⇒ Object

Returns an array with rows present in newdb but not in olddb, using the table given



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/mydiff.rb', line 91

def new_rows(table)
  fields = fields_from(table)
  pkey, fields = extract_pkey_from(fields)
  my.select_db(@newdb)
  
  query = "SELECT "
  query << pkey.collect do |f|
    "n.#{f["Field"]} #{f["Field"]}"
  end.join(",")
  query << ","
  query << fields.collect do |f|
    "n.#{f["Field"]} n_#{f["Field"]}"
  end.join(",")
  
  query << " FROM #{@newdb}.#{table} AS n LEFT JOIN #{@olddb}.#{table} AS o ON "
  query << pkey.collect do |f|
    "n.#{f["Field"]} = o.#{f["Field"]}"
  end.join(" AND ")
  query << " WHERE "
  query << pkey.collect do |f|
    "o.#{f["Field"]} IS NULL"
  end.join(" AND ")
  
  result = my.query(query)
  new_rows = []
  while row = result.fetch_hash
    new_rows << row
  end
  new_rows
end

#new_tables ⇒ Object

Returns an array with table names present on newdb but not on olddb



66
67
68
69
70
71
 
# File 'lib/mydiff.rb', line 66

def new_tables
  ntables = list_tables(@newdb)
  otables = list_tables(@olddb)
  
  ntables.select {|t| not otables.include?(t) }
end

#pkey_of(table) ⇒ Object 



206
207
208
 
# File 'lib/mydiff.rb', line 206

def pkey_of(table)
  fields_from(table).select {|f| f["Key"] == "PRI"}.map {|f| f["Field"]}
end

#prepare! ⇒ Object

Recreates the new database

WARNING: This method drops and recreates the newdb database, you may lose data!



50
51
52
53
54
55
56
57
 
# File 'lib/mydiff.rb', line 50

def prepare!
  begin
    @my.query("DROP DATABASE #{@newdb}")
  rescue
  end

  @my.query("CREATE DATABASE #{@newdb}")
end

#select_new ⇒ Object 



223
224
225
 
# File 'lib/mydiff.rb', line 223

def select_new
  @my.select_db(@newdb)
end

#select_old ⇒ Object 



227
228
229
 
# File 'lib/mydiff.rb', line 227

def select_old
  @my.select_db(@olddb)
end

#table_changed?(table) ⇒ Boolean

Returns:

  • (Boolean) 


219
220
221
 
# File 'lib/mydiff.rb', line 219

def table_changed?(table)
  checksum_table(@olddb, table) != checksum_table(@newdb, table)
end