Class: DbDiff

Inherits:
Object
  • Object
show all
Defined in:
lib/dbdiff.rb,
lib/dbdiff/key.rb,
lib/dbdiff/row.rb,
lib/dbdiff/view.rb,
lib/dbdiff/delta.rb,
lib/dbdiff/table.rb,
lib/dbdiff/column.rb,
lib/dbdiff/routine.rb,
lib/dbdiff/trigger.rb,
lib/dbdiff/database.rb,
lib/dbdiff/function.rb,
lib/dbdiff/procedure.rb,
lib/dbdiff/foreign_key.rb,
lib/dbdiff/table_element.rb,
lib/dbdiff/dbdiff_version.rb

Overview

Synopsis

DbDiff allows you to compare two different databases (currently MySQL), determine the differences between the two, and apply the deltas.

Currently the following types of deltas are supported: tables, 
columns, keys, foreign keys, views, triggers, procedures, functions,
and rows.  

Specifying tables in the constructor causes these tables to be 
replicated.

Nothing is dropped or removed unless you explicitly pass :drop_row, 
:drop_column or :drop_table.  

By default, calling apply_diffs does nothing - you must explicitly
set dry_run to false on the dbdiff object.

Row Replication

Two type of row replication is supported - update and replicate.  If you pass
the name of the table to the :replicate_tables array, that table will be kept
completely in sync with the source - table, row and column drops will be performed.

The update_tables only copies rows and updates and rows that already exist - 
no drops are performed.

Example

require 'rubygems'
require 'dbdiff'

dbdiff = DbDiff.new( {:host => 127.0.0.1, :password => nil, :user => 'root', :name => 'source_db'},
                     {:host => 127.0.0.1, :password => nil, :user => 'root', :name => 'target_db'},
                      :logger => Logger.new(STDOUT),
                      :replicate_tables => %w(isbns),
                      :update_tables    => %w(authors books)
                    )
# don't actually execute any SQL against the target
dbdiff.dry_run = true

# perform diff between source and target
dbiff.diff

# process diffs - arguments indicate that drop_column, drop_row (only for replicated tables), 
# and drop_table are allowed within diffs
# are allowed
diff.apply_diffs(:drop_column, :drop_row, :drop_table)

Defined Under Namespace

Classes: Column, Database, Delta, ForeignKey, Function, Key, Procedure, Routine, Row, Table, TableElement, Trigger, View

Constant Summary collapse

Version = 
'0.2.0'

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(s, t, params = {}) ⇒ DbDiff

Creates a new dbdiff object with the connect strings s/t (source/target)



57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/dbdiff.rb', line 57

def initialize(s, t, params = {})
  @source = DbDiff::Database.new(s)
  @target = DbDiff::Database.new(t)
  @update_tables = params[:update_tables] || []
  @replicate_tables = params[:replicate_tables] || []
  @logger = params[:logger] || Logger.new(nil)

  @source.load_rows(@replicate_tables + @update_tables)
  @target.load_rows(@replicate_tables + @update_tables)
  @dry_run = true
end

Instance Attribute Details

#dry_run ⇒ Object

Returns the value of attribute dry_run.



54
55
56
 
# File 'lib/dbdiff.rb', line 54

def dry_run
  @dry_run
end

#logger ⇒ Object (readonly)

Returns the value of attribute logger.



53
54
55
 
# File 'lib/dbdiff.rb', line 53

def logger
  @logger
end

#replicate_tables ⇒ Object (readonly)

Returns the value of attribute replicate_tables.



53
54
55
 
# File 'lib/dbdiff.rb', line 53

def replicate_tables
  @replicate_tables
end

#source ⇒ Object (readonly)

Returns the value of attribute source.



53
54
55
 
# File 'lib/dbdiff.rb', line 53

def source
  @source
end

#target ⇒ Object (readonly)

Returns the value of attribute target.



53
54
55
 
# File 'lib/dbdiff.rb', line 53

def target
  @target
end

#update_tables ⇒ Object (readonly)

Returns the value of attribute update_tables.



53
54
55
 
# File 'lib/dbdiff.rb', line 53

def update_tables
  @update_tables
end

Instance Method Details

#apply_diffs(*allow) ⇒ Object

Apply deltas stored on the target all SQL executed will be sent to the logger as info messages



82
83
84
85
86
87
88
89
90
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
121
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
152
153
154
155
156
157
158
159
 
# File 'lib/dbdiff.rb', line 82

def apply_diffs (*allow)


  drop_deltas = [DbDiff::Delta::DropRow, DbDiff::Delta::DropColumn,DbDiff::Delta::DropTable]

  # these deltas must be manually
  # resolved or explicitly allowed
  confirm_deltas = {
    :drop_row    => DbDiff::Delta::DropRow,
    :drop_column => DbDiff::Delta::DropColumn,
    :drop_table  => DbDiff::Delta::DropTable
  }

  allowed_deltas = allow.map {|k|confirm_deltas[k]}

  delta_order = [
    DbDiff::Delta::AddTable, 
    DbDiff::Delta::ModifyTable, 
    DbDiff::Delta::DropTrigger, 
    DbDiff::Delta::DropProcedure, 
    DbDiff::Delta::DropFunction, 
    DbDiff::Delta::DropView, 
    DbDiff::Delta::DropForeignKey, 
    DbDiff::Delta::DropTable,   # can only drop a table after foreign keys have been dropped

    DbDiff::Delta::AddColumn, 

    DbDiff::Delta::ModifyColumnRemoveAI, # ai must be removed before we can drop/modify a pk 

    DbDiff::Delta::ModifyKey,     # must have columns exist prior to adding a key
    DbDiff::Delta::AddKey,        # must have new keys before old ones can be dropped
                                  # to support FKs

    DbDiff::Delta::DropKey,       # must have FK dropped before we drop a key

    DbDiff::Delta::ModifyColumnAddAI, # ai must be added before we can add a pk 

    DbDiff::Delta::DropRow,  
    DbDiff::Delta::ModifyColumn,  

    DbDiff::Delta::AddForeignKey, 
    DbDiff::Delta::DropColumn,
    DbDiff::Delta::AddRow,  
    DbDiff::Delta::ModifyRow,
    DbDiff::Delta::AddView,
    DbDiff::Delta::AddFunction,
    DbDiff::Delta::AddProcedure,
    DbDiff::Delta::AddTrigger
  ]


  target.dbh.query("SET FOREIGN_KEY_CHECKS = 0")

  target.deltas.sort_by {|d| delta_order.index(d.class) }.each do |d|
      if drop_deltas.include?(d.class) && !allowed_deltas.include?(d.class)
        # we simply skip drop rows from update tables
        if d.class == DbDiff::Delta::DropRow && update_tables.include?(d.element.table_name)
          logger.info("skipping row for update table #{d.sql}")
          next
        elsif !replicate_tables.include?(d.element.table_name)
          logger.debug("skipping #{d.sql}")
          next
        end
      end

      d.process(target) 

      if dry_run
        logger.info("[DRY] #{d.sql}")
      else
        run_sql(d.sql) 
      end
  end

  target.dbh.query("SET FOREIGN_KEY_CHECKS = 1")

  target.deltas = []
end

#diff ⇒ Object

Compare tables and all elements (columns, rows, keys, foreign keys) differences are stored on the deltas attribute of the target database



72
73
74
75
76
77
78
 
# File 'lib/dbdiff.rb', line 72

def diff
  compare(:tables)
  compare(:views)
  compare(:functions)
  compare(:procedures)
  compare_elements(:columns, :rows, :keys, :foreign_keys, :triggers)
end