class Coopy::SqlCompare
Attributes
align[RW]
alt[RW]
alt_peered[RW]
at0[RW]
at1[RW]
at2[RW]
db[RW]
diff_ct[RW]
local[RW]
needed[RW]
peered[RW]
remote[RW]
Public Class Methods
new(db,local,remote,alt,align = nil)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 7 def initialize(db,local,remote,alt,align = nil) @db = db @local = local @remote = remote @alt = alt @align = align @peered = false @alt_peered = false if local != nil && remote != nil if @remote.get_database.get_name_for_attachment != nil if @remote.get_database.get_name_for_attachment != @local.get_database.get_name_for_attachment local.get_database.get_helper.attach(db,"__peer__",@remote.get_database.get_name_for_attachment) @peered = true end end end if @alt != nil && local != nil if @alt.get_database.get_name_for_attachment != nil if @alt.get_database.get_name_for_attachment != @local.get_database.get_name_for_attachment local.get_database.get_helper.attach(db,"__alt__",@alt.get_database.get_name_for_attachment) @alt_peered = true end end end end
Public Instance Methods
apply()
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 230 def apply return nil if @db == nil @align = ::Coopy::Alignment.new if @align == nil return nil if !self.validate_schema rowid_name = @db.rowid key_cols = [] data_cols = [] all_cols = [] all_cols1 = [] all_cols2 = [] all_cols3 = [] common = @local if @local != nil key_cols = @local.get_primary_key data_cols = @local.get_all_but_primary_key all_cols = @local.get_column_names all_cols1 = @local.get_column_names end if @remote != nil all_cols2 = @remote.get_column_names common = @remote if common == nil end if @alt != nil all_cols3 = @alt.get_column_names common = @alt if common == nil else all_cols3 = all_cols2 end all_common_cols = Array.new data_common_cols = Array.new present1 = {} present2 = {} present3 = {} present_primary = {} has_column_add = false begin _g1 = 0 _g = key_cols.length while(_g1 < _g) i = _g1 _g1+=1 present_primary[key_cols[i]] = i end end begin _g11 = 0 _g2 = all_cols1.length while(_g11 < _g2) i1 = _g11 _g11+=1 key = all_cols1[i1] present1[key] = i1 end end begin _g12 = 0 _g3 = all_cols2.length while(_g12 < _g3) i2 = _g12 _g12+=1 key1 = all_cols2[i2] has_column_add = true if !present1.include?(key1) present2[key1] = i2 end end begin _g13 = 0 _g4 = all_cols3.length while(_g13 < _g4) i3 = _g13 _g13+=1 key2 = all_cols3[i3] has_column_add = true if !present1.include?(key2) present3[key2] = i3 if present1.include?(key2) if present2.include?(key2) all_common_cols.push(key2) data_common_cols.push(key2) if !present_primary.include?(key2) end end end end @align.meta = ::Coopy::Alignment.new begin _g14 = 0 _g5 = all_cols1.length while(_g14 < _g5) i4 = _g14 _g14+=1 key3 = all_cols1[i4] if present2.include?(key3) @align.meta.link(i4,present2[key3]) else @align.meta.link(i4,-1) end end end begin _g15 = 0 _g6 = all_cols2.length while(_g15 < _g6) i5 = _g15 _g15+=1 key4 = all_cols2[i5] @align.meta.link(-1,i5) if !present1.include?(key4) end end self.scan_columns(all_cols1,all_cols2,key_cols,present1,present2,@align) @align.tables(@local,@remote) if @alt != nil self.scan_columns(all_cols1,all_cols3,key_cols,present1,present3,@align.reference) @align.reference.tables(@local,@alt) end sql_table1 = "" sql_table2 = "" sql_table3 = "" sql_table1 = @local.get_quoted_table_name if @local != nil sql_table2 = @remote.get_quoted_table_name if @remote != nil sql_table3 = @alt.get_quoted_table_name if @alt != nil if @peered sql_table1 = "main." + _hx_str(sql_table1) sql_table2 = "__peer__." + _hx_str(sql_table2) end sql_table2 = "__alt__." + _hx_str(sql_table3) if @alt_peered sql_key_cols = "" begin _g16 = 0 _g7 = key_cols.length while(_g16 < _g7) i6 = _g16 _g16+=1 sql_key_cols += "," if i6 > 0 sql_key_cols += common.get_quoted_column_name(key_cols[i6]) end end sql_all_cols = "" begin _g17 = 0 _g8 = all_common_cols.length while(_g17 < _g8) i7 = _g17 _g17+=1 sql_all_cols += "," if i7 > 0 sql_all_cols += common.get_quoted_column_name(all_common_cols[i7]) end end sql_all_cols1 = "" begin _g18 = 0 _g9 = all_cols1.length while(_g18 < _g9) i8 = _g18 _g18+=1 sql_all_cols1 += "," if i8 > 0 sql_all_cols1 += @local.get_quoted_column_name(all_cols1[i8]) end end sql_all_cols2 = "" begin _g19 = 0 _g10 = all_cols2.length while(_g19 < _g10) i9 = _g19 _g19+=1 sql_all_cols2 += "," if i9 > 0 sql_all_cols2 += @remote.get_quoted_column_name(all_cols2[i9]) end end sql_all_cols3 = "" if @alt != nil _g110 = 0 _g20 = all_cols3.length while(_g110 < _g20) i10 = _g110 _g110+=1 sql_all_cols3 += "," if i10 > 0 sql_all_cols3 += @alt.get_quoted_column_name(all_cols3[i10]) end end sql_key_match2 = "" begin _g111 = 0 _g21 = key_cols.length while(_g111 < _g21) i11 = _g111 _g111+=1 sql_key_match2 += " AND " if i11 > 0 n = common.get_quoted_column_name(key_cols[i11]) sql_key_match2 += _hx_str(sql_table1) + "." + _hx_str(n) + " IS " + _hx_str(sql_table2) + "." + _hx_str(n) end end sql_key_match3 = "" if @alt != nil _g112 = 0 _g22 = key_cols.length while(_g112 < _g22) i12 = _g112 _g112+=1 sql_key_match3 += " AND " if i12 > 0 n1 = common.get_quoted_column_name(key_cols[i12]) sql_key_match3 += _hx_str(sql_table1) + "." + _hx_str(n1) + " IS " + _hx_str(sql_table3) + "." + _hx_str(n1) end end sql_data_mismatch = "" begin _g113 = 0 _g23 = data_common_cols.length while(_g113 < _g23) i13 = _g113 _g113+=1 sql_data_mismatch += " OR " if i13 > 0 n2 = common.get_quoted_column_name(data_common_cols[i13]) sql_data_mismatch += _hx_str(sql_table1) + "." + _hx_str(n2) + " IS NOT " + _hx_str(sql_table2) + "." + _hx_str(n2) end end begin _g114 = 0 _g24 = all_cols2.length while(_g114 < _g24) i14 = _g114 _g114+=1 key5 = all_cols2[i14] if !present1.include?(key5) sql_data_mismatch += " OR " if sql_data_mismatch != "" n3 = common.get_quoted_column_name(key5) sql_data_mismatch += _hx_str(sql_table2) + "." + _hx_str(n3) + " IS NOT NULL" end end end if @alt != nil begin _g115 = 0 _g25 = data_common_cols.length while(_g115 < _g25) i15 = _g115 _g115+=1 sql_data_mismatch += " OR " if sql_data_mismatch.length > 0 n4 = common.get_quoted_column_name(data_common_cols[i15]) sql_data_mismatch += _hx_str(sql_table1) + "." + _hx_str(n4) + " IS NOT " + _hx_str(sql_table3) + "." + _hx_str(n4) end end begin _g116 = 0 _g26 = all_cols3.length while(_g116 < _g26) i16 = _g116 _g116+=1 key6 = all_cols3[i16] if !present1.include?(key6) sql_data_mismatch += " OR " if sql_data_mismatch != "" n5 = common.get_quoted_column_name(key6) sql_data_mismatch += _hx_str(sql_table3) + "." + _hx_str(n5) + " IS NOT NULL" end end end end sql_dbl_cols = "" dbl_cols = [] begin _g117 = 0 _g27 = all_cols1.length while(_g117 < _g27) i17 = _g117 _g117+=1 sql_dbl_cols += "," if sql_dbl_cols != "" buf = "__coopy_" + _hx_str(i17) n6 = common.get_quoted_column_name(all_cols1[i17]) sql_dbl_cols += _hx_str(sql_table1) + "." + _hx_str(n6) + " AS " + _hx_str(buf) dbl_cols.push(buf) end end begin _g118 = 0 _g28 = all_cols2.length while(_g118 < _g28) i18 = _g118 _g118+=1 sql_dbl_cols += "," if sql_dbl_cols != "" buf1 = "__coopy_" + _hx_str(i18) + "b" n7 = common.get_quoted_column_name(all_cols2[i18]) sql_dbl_cols += _hx_str(sql_table2) + "." + _hx_str(n7) + " AS " + _hx_str(buf1) dbl_cols.push(buf1) end end if @alt != nil _g119 = 0 _g29 = all_cols3.length while(_g119 < _g29) i19 = _g119 _g119+=1 sql_dbl_cols += "," if sql_dbl_cols != "" buf2 = "__coopy_" + _hx_str(i19) + "c" n8 = common.get_quoted_column_name(all_cols3[i19]) sql_dbl_cols += _hx_str(sql_table3) + "." + _hx_str(n8) + " AS " + _hx_str(buf2) dbl_cols.push(buf2) end end sql_order = "" begin _g120 = 0 _g30 = key_cols.length while(_g120 < _g30) i20 = _g120 _g120+=1 sql_order += "," if i20 > 0 n9 = common.get_quoted_column_name(key_cols[i20]) sql_order += n9 end end rowid = "-3" rowid1 = "-3" rowid2 = "-3" rowid3 = "-3" if rowid_name != nil rowid = rowid_name rowid1 = _hx_str(sql_table1) + "." + _hx_str(rowid_name) if @local != nil rowid2 = _hx_str(sql_table2) + "." + _hx_str(rowid_name) if @remote != nil rowid3 = _hx_str(sql_table3) + "." + _hx_str(rowid_name) if @alt != nil end @at0 = 1 @at1 = 1 @at2 = 1 @diff_ct = 0 if @remote != nil sql_inserts = "SELECT DISTINCT 0 AS __coopy_code, NULL, " + _hx_str(rowid) + " AS rowid, NULL, " + _hx_str(sql_all_cols2) + " FROM " + _hx_str(sql_table2) sql_inserts += " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table1) + _hx_str(self.where(sql_key_match2)) + ")" if @local != nil sql_inserts_order = ["__coopy_code","NULL","rowid","NULL"].concat(all_cols2) self.link_query(sql_inserts,sql_inserts_order) end if @alt != nil sql_inserts1 = "SELECT DISTINCT 1 AS __coopy_code, NULL, NULL, " + _hx_str(rowid) + " AS rowid, " + _hx_str(sql_all_cols3) + " FROM " + _hx_str(sql_table3) sql_inserts1 += " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table1) + _hx_str(self.where(sql_key_match3)) + ")" if @local != nil sql_inserts_order1 = ["__coopy_code","NULL","NULL","rowid"].concat(all_cols3) self.link_query(sql_inserts1,sql_inserts_order1) end if @local != nil && @remote != nil sql_updates = "SELECT DISTINCT 2 AS __coopy_code, " + _hx_str(rowid1) + " AS __coopy_rowid0, " + _hx_str(rowid2) + " AS __coopy_rowid1, " if @alt != nil sql_updates += _hx_str(rowid3) + " AS __coopy_rowid2," else sql_updates += " NULL," end sql_updates += _hx_str(sql_dbl_cols) + " FROM " + _hx_str(sql_table1) sql_updates += " INNER JOIN " + _hx_str(sql_table2) + " ON " + _hx_str(sql_key_match2) if sql_table1 != sql_table2 sql_updates += " INNER JOIN " + _hx_str(sql_table3) + " ON " + _hx_str(sql_key_match3) if @alt != nil && sql_table1 != sql_table3 sql_updates += self.where(sql_data_mismatch) sql_updates_order = ["__coopy_code","__coopy_rowid0","__coopy_rowid1","__coopy_rowid2"].concat(dbl_cols) self.link_query(sql_updates,sql_updates_order) end if @alt == nil if @local != nil sql_deletes = "SELECT DISTINCT 0 AS __coopy_code, " + _hx_str(rowid) + " AS rowid, NULL, NULL, " + _hx_str(sql_all_cols1) + " FROM " + _hx_str(sql_table1) sql_deletes += " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table2) + _hx_str(self.where(sql_key_match2)) + ")" if @remote != nil sql_deletes_order = ["__coopy_code","rowid","NULL","NULL"].concat(all_cols1) self.link_query(sql_deletes,sql_deletes_order) end end if @alt != nil sql_deletes1 = "SELECT 2 AS __coopy_code, " + _hx_str(rowid1) + " AS __coopy_rowid0, " + _hx_str(rowid2) + " AS __coopy_rowid1, " sql_deletes1 += _hx_str(rowid3) + " AS __coopy_rowid2, " sql_deletes1 += sql_dbl_cols sql_deletes1 += " FROM " + _hx_str(sql_table1) sql_deletes1 += " LEFT OUTER JOIN " + _hx_str(sql_table2) + " ON " + _hx_str(sql_key_match2) if @remote != nil sql_deletes1 += " LEFT OUTER JOIN " + _hx_str(sql_table3) + " ON " + _hx_str(sql_key_match3) sql_deletes1 += " WHERE __coopy_rowid1 IS NULL OR __coopy_rowid2 IS NULL" sql_deletes_order1 = ["__coopy_code","__coopy_rowid0","__coopy_rowid1","__coopy_rowid2"].concat(dbl_cols) self.link_query(sql_deletes1,sql_deletes_order1) end @align.mark_identical if @diff_ct == 0 @align end
scan_columns(all_cols1,all_cols2,key_cols,present1,present2,align)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 192 def scan_columns(all_cols1,all_cols2,key_cols,present1,present2,align) align.meta = ::Coopy::Alignment.new begin _g1 = 0 _g = all_cols1.length while(_g1 < _g) i = _g1 _g1+=1 key = all_cols1[i] if present2.include?(key) align.meta.link(i,present2[key]) else align.meta.link(i,-1) end end end begin _g11 = 0 _g2 = all_cols2.length while(_g11 < _g2) i1 = _g11 _g11+=1 key1 = all_cols2[i1] align.meta.link(-1,i1) if !present1.include?(key1) end end align.meta.range(all_cols1.length,all_cols2.length) begin _g3 = 0 while(_g3 < key_cols.length) key2 = key_cols[_g3] _g3+=1 unit = ::Coopy::Unit.new(present1[key2],present2[key2]) align.add_index_columns(unit) end end end
validate_schema()
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 65 def validate_schema all_cols1 = [] key_cols1 = [] access_error = false pk_missing = false if @local != nil all_cols1 = @local.get_column_names key_cols1 = @local.get_primary_key access_error = true if all_cols1.length == 0 pk_missing = true if key_cols1.length == 0 end all_cols2 = [] key_cols2 = [] if @remote != nil all_cols2 = @remote.get_column_names key_cols2 = @remote.get_primary_key access_error = true if all_cols2.length == 0 pk_missing = true if key_cols2.length == 0 end all_cols3 = all_cols2 key_cols3 = key_cols2 if @alt != nil all_cols3 = @alt.get_column_names key_cols3 = @alt.get_primary_key access_error = true if all_cols3.length == 0 pk_missing = true if key_cols3.length == 0 end raise hx_raise("Error accessing SQL table") if access_error raise hx_raise("sql diff not possible when primary key not available") if pk_missing pk_change = false if @local != nil && @remote != nil pk_change = true if !self.equal_array(key_cols1,key_cols2) end if @local != nil && @alt != nil pk_change = true if !self.equal_array(key_cols1,key_cols3) end raise hx_raise("sql diff not possible when primary key changes") if pk_change true end
Protected Instance Methods
denull(x)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 107 def denull(x) return -1 if x == nil x end
equal_array(a1,a2)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 49 def equal_array(a1,a2) return false if a1.length != a2.length begin _g1 = 0 _g = a1.length while(_g1 < _g) i = _g1 _g1+=1 return false if a1[i] != a2[i] end end true end
link()
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 112 def link @diff_ct+=1 mode = @db.get(0) i0 = self.denull(@db.get(1)) i1 = self.denull(@db.get(2)) i2 = self.denull(@db.get(3)) if i0 == -3 i0 = @at0 @at0+=1 end if i1 == -3 i1 = @at1 @at1+=1 end if i2 == -3 i2 = @at2 @at2+=1 end offset = 4 if i0 >= 0 begin _g1 = 0 _g = @local.get_width while(_g1 < _g) x = _g1 _g1+=1 @local.set_cell_cache(x,i0,@db.get(x + offset)) end end offset += @local.get_width end if i1 >= 0 begin _g11 = 0 _g2 = @remote.get_width while(_g11 < _g2) x1 = _g11 _g11+=1 @remote.set_cell_cache(x1,i1,@db.get(x1 + offset)) end end offset += @remote.get_width end if i2 >= 0 _g12 = 0 _g3 = @alt.get_width while(_g12 < _g3) x2 = _g12 _g12+=1 @alt.set_cell_cache(x2,i2,@db.get(x2 + offset)) end end if mode == 0 || mode == 2 @align.link(i0,i1) @align.add_to_order(i0,i1) end if @alt != nil if mode == 1 || mode == 2 @align.reference.link(i0,i2) @align.reference.add_to_order(i0,i2) end end end
link_query(query,order)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 176 def link_query(query,order) if @db._begin(query,nil,order) while(@db.read) self.link end @db._end end end
where(txt)
click to toggle source
# File lib/lib/coopy/sql_compare.rb, line 185 def where(txt) return " WHERE 1 = 0" if txt == "" " WHERE " + _hx_str(txt) end