Class: Coopy::SqlCompare

Inherits:

Object

• Object
• Coopy::SqlCompare

Defined in:

lib/lib/coopy/sql_compare.rb

Instance Attribute Summary

• #alt ⇒ Object

  Returns the value of attribute alt.

• #db ⇒ Object

  Returns the value of attribute db.

• #local ⇒ Object

  Returns the value of attribute local.

• #remote ⇒ Object

  Returns the value of attribute remote.

Instance Method Summary

• #apply ⇒ Object
• #initialize(db, local, remote, alt, align = nil) ⇒ SqlCompare constructor

  A new instance of SqlCompare.

• #scan_columns(all_cols1, all_cols2, key_cols, present1, present2, align) ⇒ Object
• #validate_schema ⇒ Object

Constructor Details

#initialize(db, local, remote, alt, align = nil) ⇒ SqlCompare

Returns a new instance of SqlCompare.

# 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
  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
  @alt_peered = false
  if @alt != 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

Instance Attribute Details

#alt ⇒ Object

Returns the value of attribute alt.

# File 'lib/lib/coopy/sql_compare.rb', line 34

def alt
  @alt
end

#db ⇒ Object

Returns the value of attribute db.

# File 'lib/lib/coopy/sql_compare.rb', line 31

def db
  @db
end

#local ⇒ Object

Returns the value of attribute local.

# File 'lib/lib/coopy/sql_compare.rb', line 32

def local
  @local
end

#remote ⇒ Object

Returns the value of attribute remote.

# File 'lib/lib/coopy/sql_compare.rb', line 33

def remote
  @remote
end

Instance Method Details

#apply ⇒ Object

# File 'lib/lib/coopy/sql_compare.rb', line 209

def apply 
  return nil if @db == nil
  return nil if !self.validate_schema
  rowid_name = @db.rowid
  @align = ::Coopy::Alignment.new if @align == 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
  all_cols2 = @remote.get_column_names
  all_cols3 = all_cols2
  all_cols3 = @alt.get_column_names if @alt != nil
  data_cols1 = @local.get_all_but_primary_key
  data_cols2 = @remote.get_all_but_primary_key
  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 = @local.get_quoted_table_name
  sql_table2 = @remote.get_quoted_table_name
  sql_table3 = ""
  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 += @local.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 += @local.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 += @local.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 += @local.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 = @local.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 = @local.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 = @local.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 = @remote.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 = @local.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 = @alt.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 = @local.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 = @local.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 = @local.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 = @local.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)
    rowid2 = _hx_str(sql_table2) + "." + _hx_str(rowid_name)
    rowid3 = _hx_str(sql_table3) + "." + _hx_str(rowid_name)
  end
  @at0 = 1
  @at1 = 1
  @at2 = 1
  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) + " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table1) + _hx_str(self.where(sql_key_match2)) + ")"
  sql_inserts_order = ["__coopy_code","NULL","rowid","NULL"].concat(all_cols2)
  self.link_query(sql_inserts,sql_inserts_order)
  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) + " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table1) + _hx_str(self.where(sql_key_match3)) + ")"
    sql_inserts_order1 = ["__coopy_code","NULL","NULL","rowid"].concat(all_cols3)
    self.link_query(sql_inserts1,sql_inserts_order1)
  end
  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)
  if @alt == 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) + " WHERE NOT EXISTS (SELECT 1 FROM " + _hx_str(sql_table2) + _hx_str(self.where(sql_key_match2)) + ")"
    sql_deletes_order = ["__coopy_code","rowid","NULL","NULL"].concat(all_cols1)
    self.link_query(sql_deletes,sql_deletes_order)
  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)
    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
end

#scan_columns(all_cols1, all_cols2, key_cols, present1, present2, align) ⇒ Object

# File 'lib/lib/coopy/sql_compare.rb', line 171

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 ⇒ Object

# File 'lib/lib/coopy/sql_compare.rb', line 62

def validate_schema 
  all_cols1 = @local.get_column_names
  all_cols2 = @remote.get_column_names
  all_cols3 = all_cols2
  key_cols1 = @local.get_primary_key
  key_cols2 = @remote.get_primary_key
  key_cols3 = key_cols2
  if @alt != nil 
    all_cols3 = @alt.get_column_names
    key_cols3 = @alt.get_primary_key
  end
  raise hx_raise("Error accessing SQL table") if all_cols1.length == 0 || all_cols2.length == 0 || all_cols3.length == 0
  if !(self.equal_array(key_cols1,key_cols2) && self.equal_array(key_cols1,key_cols3)) 
    puts "sql diff not possible when primary key changes"
    return false
  end
  if key_cols1.length == 0 
    puts "sql diff not possible when primary key not available"
    return false
  end
  true
end