Pg_diff - Compare Two PostgreSQL Database Schemas
#!/bin/env ruby # pg_diff - compare two PostgreSQL database schemas # # URL: http://snippets.dzone.com/posts/show/949 # # This is a simple approach to track database schema changes in PostgreSQL. # In some way it is similar to diff program, finding out structure changes # and results in SQL script to upgrade to new schema. # # Differences are tracked on schemas, domains, sequences, views, tables, indices, constraints, rules, functions, triggers. # Two objects with the same name are considered equal if they have the same definitions. # # Missing features: tracking of ownership, user rights, object dependencies, table inheritance, type casts, aggregates, operators. # # Usage: # ./pg_diff dbname=db_v03_dev dbname=db_v04_dev # # Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding (20051127 snapshot). # # This software is released under MIT License # # Copyright (c) 2005 Dmitry Severin # # Permission is hereby granted, free of charge, to any person obtaining a copy # of this software and associated documentation files (the "Software"), to deal # in the Software without restriction, including without limitation the rights # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell # copies of the Software, and to permit persons to whom the Software is # furnished to do so, subject to the following conditions: # # The above copyright notice and this permission notice shall be included in # all copies or substantial portions of the Software. # # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN # THE SOFTWARE. # require 'postgres' module PostgreSqlSchema class Attribute attr_accessor :name, :type_def, :notnull, :default def initialize(name, typedef, notnull, default) @name = name @type_def = typedef @notnull = notnull @default = default end def definition out = [' ', @name, @type_def] out << 'NOT NULL' if @notnull out << 'DEFAULT ' + @default if @default out.join(" ") end def == (other) definition == other.definition end end class Table attr_accessor :table_name, :schema, :attributes, :constraints, :indexes def initialize(conn, schema, table_name) @schema = schema @table_name = table_name @attributes = {} @constraints = {} @indexes = {} @atlist = [] att_query = <<-EOT select attname, format_type(atttypid, atttypmod) as a_type, attnotnull, pg_get_expr(adbin, attrelid) as a_default from pg_attribute left join pg_attrdef on (adrelid = attrelid and adnum = attnum) where attrelid = '#{schema}.#{table_name}'::regclass and not attisdropped and attnum > 0 order by attnum EOT conn.query(att_query).each do |row| attname = row[0] @attributes[attname] = Attribute.new(attname, row[1], row[2], row[3]) @atlist << attname end ind_query = <<-EOT select indexrelid::regclass as indname, pg_get_indexdef(indexrelid) as def from pg_index where indrelid = '#{schema}.#{table_name}'::regclass and not indisprimary EOT conn.query(ind_query).each do |row| @indexes[row[0]] = row[1] end cons_query = <<-EOT select conname, pg_get_constraintdef(oid) from pg_constraint where conrelid = '#{schema}.#{table_name}'::regclass EOT conn.query(cons_query).each do |row| @constraints[row[0]] = row[1] end @constraints.keys.each do |cname| @indexes.delete("#{schema}.#{cname}") if has_index?(cname) end end def has_attribute?(name) @attributes.has_key?(name) end def has_index?(name) @indexes.has_key?(name) || @indexes.has_key?("#{schema}.#{name}") end def has_constraint?(name) @constraints.has_key?(name) end def table_creation out = ["CREATE TABLE #{name} ("] stmt = [] @atlist.each do |attname| stmt << @attributes[attname].definition end out << stmt.join(",\n") out << ");" out.join("\n") end def name "#{schema}.#{table_name}" end def constr_creation out = [] @constraints.each do |n, c| out << "ALTER TABLE #{name} ADD CONSTRAINT #{n} #{c};" end out.join("\n") end def index_creation out = [] @indexes.values.each do |c| out << (c+";") end out.join("\n") end end class Sequence def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" end def definition "CREATE SEQUENCE #{@name} ;" end end class View attr_reader :def, :name def initialize(conn, sch, relname) @name = "#{sch}.#{relname}" view_qery = <<-EOT SELECT pg_catalog.pg_get_viewdef('#{@name}'::regclass, true) EOT @def = conn.query(view_qery)[0][0] end def definition "CREATE VIEW #{@name} AS #{@def}" end end class Database attr_accessor :tables, :views, :sequences, :schemas, :domains, :rules, :functions, :triggers def initialize(conn) cls_query = <<-EOT SELECT n.nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','S','v') AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ORDER BY 1,2; EOT @views = {} @tables = {} @sequences = {} @schemas = {} @domains = {} @functions = {} @rules = {} @triggers = {} conn.query(cls_query).each do |row| schema, relname, relkind = row case relkind when 'r' then @tables["#{schema}.#{relname}"] = Table.new(conn, schema, relname) when 'v' then @views ["#{schema}.#{relname}"] = View.new(conn, schema, relname) when 'S' then @sequences["#{schema}.#{relname}"] = Sequence.new(conn, schema, relname) end end domain_qry = <<-EOT SELECT n.nspname, t.typname, pg_catalog.format_type(t.typbasetype, t.typtypmod) || ' ' || CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null' WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault ELSE '' END FROM pg_catalog.pg_type t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'd' ORDER BY 1, 2 EOT conn.query(domain_qry).each do |row| @domains["#{row[0]}.#{row[1]}"] = row[2] end schema_qry = <<-EOT select nspname from pg_namespace EOT conn.query(schema_qry).each do |row| @schemas[row[0]]=row[0] end func_query = <<-EOT SELECT proname AS function_name , nspname AS namespace , lanname AS language_name , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment , proargtypes AS function_args , proargnames AS function_arg_names , prosrc AS source_code , proretset AS returns_set , prorettype AS return_type, provolatile, proisstrict, prosecdef FROM pg_catalog.pg_proc JOIN pg_catalog.pg_language ON (pg_language.oid = prolang) JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid) JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid) WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema' AND proname != 'plpgsql_call_handler' AND proname != 'plpgsql_validator' EOT conn.exec(func_query).result.each do |tuple| func = Function.new(conn, tuple) @functions[func.signature] = func end rule_query = <<-EOT select schemaname || '.' || tablename || '.' || rulename as rule_name, schemaname || '.' || tablename as tab_name, rulename, definition from pg_rules where schemaname !~ 'pg_catalog|information_schema' EOT conn.exec(rule_query).result.each do |tuple| @rules[tuple['rule_name']] = Rule.new(tuple['tab_name'], tuple['rulename'], tuple['definition']) end trigger_query = <<-EOT select nspname || '.' || relname as tgtable, tgname, pg_get_triggerdef(t.oid) as tg_def from pg_trigger t join pg_class c ON (tgrelid = c.oid ) JOIN pg_namespace n ON (c.relnamespace = n.oid) where not tgisconstraint and nspname !~ 'pg_catalog|information_schema' EOT conn.exec(trigger_query).result.each do |tuple| @triggers[tuple['tgtable'] + "." + tuple['tgname']] = Trigger.new(tuple['tgtable'], tuple['tgname'], tuple['tg_def']) end end end class Rule attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df end def == (other) other.definition == definition end end class Trigger attr_reader :table_name, :name, :definition def initialize(table_name, name, df) @table_name = table_name @name = name @definition = df + ";" end def == (other) other.definition == definition end end class Function def initialize(conn, tuple) @name = tuple['namespace'] + "." + tuple['function_name'] @language = tuple['language_name'] @src = tuple['source_code'] @returns_set = tuple['returns_set'] @return_type = format_type(conn, tuple['return_type']) @tipes = tuple['function_args'].split(" ") if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/ @arnames = $1.split(',') elsif tuple['function_arg_names'].is_a? Array # my version of ruby-postgres @arnames = tuple['function_arg_names'] else @arnames = [""] * @tipes.length end alist = [] @tipes.each_with_index do |typ,idx| alist << (@arnames[idx] +" " + format_type(conn, typ)) end @arglist = alist.join(" , ") @strict = tuple['proisstrict'] ? ' STRICT' : '' @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : '' @volatile = case tuple['provolatile'] when 'i' then ' IMMUTABLE' when 's' then ' STABLE' else '' end end def signature "#{@name}(#{@arglist})" end def definition <<-EOT CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef}; EOT end def == (other) definition == other.definition end def format_type(conn, oid) t_query = <<-EOT SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace) WHERE pg_type.oid = EOT return conn.query(t_query + oid.to_s)[0][0] end end class Diff def initialize(old_db_spec, new_db_spec) @old_conn = PGconn.new(old_db_spec) @new_conn = PGconn.new(new_db_spec) @sections = [ :triggers_drop, :rules_drop, :functions_drop, :indices_drop , :constraints_drop, :views_drop, :sequences_drop , :tables_drop , :domains_drop , :schemas_drop , :schemas_create, :domains_create, :sequences_create, :tables_create , :table_changes , :views_create , :functions_create , :rules_create , :triggers_create , :indices_create, :constraints_create ] @script = {} @sections.each {|s| @script[s] = []} end def run_compare @old_database = Database.new(@old_conn) @new_database = Database.new(@new_conn) compare_schemas compare_domains compare_sequences compare_triggers_drop compare_rules_drop compare_views_drop compare_table_attrs compare_views_create compare_functions compare_rules_create compare_triggers_create compare_table_constraints end def add_script(section, statement) @script[section] << statement end def compare_schemas @old_database.schemas.keys.each do |name| add_script(:schemas_drop , "DROP SCHEMA #{name};") unless @new_database.schemas.has_key?(name) end @new_database.schemas.keys.each do |name| add_script(:schemas_create , "CREATE SCHEMA #{name};") unless @old_database.schemas.has_key?(name) end end def compare_domains @old_database.domains.keys.each do |name| add_script(:domains_drop , "DROP DOMAIN #{name} CASCADE;") unless @new_database.domains.has_key?(name) end @new_database.domains.each do |name, df| add_script(:domains_create , "CREATE DOMAIN #{name} AS #{df};") unless @old_database.domains.has_key?(name) old_domain = @old_database.domains[name] if old_domain && old_domain != df add_script(:domains_drop, "DROP DOMAIN #{name} CASCADE;") add_script(:domains_create, "-- [changed domain] :") add_script(:domains_create, "-- OLD: #{old_domain}") add_script(:domains_create, "CREATE DOMAIN #{name} AS #{df};") end end end def compare_sequences @old_database.sequences.keys.each do |name| add_script(:sequences_drop , "DROP SEQUENCE #{name} CASCADE;") unless @new_database.sequences.has_key?(name) end @new_database.sequences.keys.each do |name| add_script(:sequences_create , "CREATE SEQUENCE #{name};") unless @old_database.sequences.has_key?(name) end end def compare_functions @old_database.functions.keys.each do |name| add_script(:functions_drop , "DROP FUNCTION #{name} CASCADE;") unless @new_database.functions.has_key?(name) end @new_database.functions.each do |name, func| add_script(:functions_create , func.definition) unless @old_database.functions.has_key?(name) old_function = @old_database.functions[name] if old_function && old_function.definition != func.definition add_script(:functions_create , '-- [changed function] :') add_script(:functions_create , '-- OLD :') add_script(:functions_create , old_function.definition.gsub(/^/, "--> ") ) add_script(:functions_create , func.definition) end end end def compare_rules_drop @old_database.rules.each do |name, rule| add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") unless @new_database.rules.has_key?(name) end end def compare_rules_create @new_database.rules.each do |name, rule| add_script(:rules_create , rule.definition) unless @old_database.rules.has_key?(name) old_rule = @old_database.rules[name] if old_rule && old_rule != rule add_script(:rules_drop , "DROP RULE #{rule.name} ON #{rule.table_name} CASCADE;") add_script(:rules_create , "-- [changed rule] :") add_script(:rules_create , "-- OLD: #{old_rule.definition}") add_script(:rules_create , rule.definition ) end end end def compare_triggers_drop @old_database.triggers.each do |name, trigger| add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") unless @new_database.triggers.has_key?(name) end end def compare_triggers_create @new_database.triggers.each do |name, trigger| add_script(:triggers_create , trigger.definition) unless @old_database.triggers.has_key?(name) old_trigger = @old_database.triggers[name] if old_trigger && old_trigger != trigger add_script(:triggers_drop , "DROP trigger #{trigger.name} ON #{trigger.table_name} CASCADE;") add_script(:triggers_create , "-- [changed trigger] :") add_script(:triggers_create , "-- OLD #{old_trigger.definition}") add_script(:triggers_create , trigger.definition) end end end def compare_views_drop @old_database.views.keys.each do |name| add_script(:views_drop , "DROP VIEW #{name};") unless @new_database.views.has_key?(name) end end def compare_views_create @new_database.views.each do |name, df| add_script(:views_create , df.definition) unless @old_database.views.has_key?(name) old_view = @old_database.views[name] if old_view && df.definition != old_view.definition add_script(:views_drop , "DROP VIEW #{name};") add_script(:views_create , "-- [changed view] :") add_script(:views_create , "-- #{old_view.definition.gsub(/\n/, ' ')}") add_script(:views_create , df.definition) end end end def compare_table_attrs @old_database.tables.each do |name, table| add_script(:tables_drop, "DROP TABLE #{name} CASCADE;") unless @new_database.tables.has_key?(name) end @to_compare = [] @new_database.tables.each do |name, table| unless @old_database.tables.has_key?(name) add_script(:tables_create , table.table_creation) add_script(:indices_create , table.index_creation) unless table.indexes.empty? @to_compare << name else diff_attributes(@old_database.tables[name], table) diff_indexes(@old_database.tables[name], table) @to_compare << name end end end def compare_table_constraints @c_check = [] @c_primary = [] @c_unique = [] @c_foreign = [] @to_compare.each do |name| if @old_database.tables[name] diff_constraints(@old_database.tables[name], @new_database.tables[name]) else @new_database.tables[name].constraints.each do |cname, cdef| add_cnstr(name, cname, cdef) end end end @script[:constraints_create] += @c_check @script[:constraints_create] += @c_primary @script[:constraints_create] += @c_unique @script[:constraints_create] += @c_foreign end def output out = [] @sections.each do |sect| if @script[sect].empty? out << "-- [SKIP SECTION : #{sect.to_s.upcase}] : no changes\n" else out << "-- [START SECTION : #{sect.to_s.upcase}]" out += @script[sect] out << "-- [END SECTION : #{sect.to_s.upcase}]\n" end end out.join("\n") end def diff_attributes(old_table, new_table) dropped = [] added = [] changed = [] old_table.attributes.keys.each do |attname| if new_table.has_attribute?(attname) changed << attname if old_table.attributes[attname] != new_table.attributes[attname] else dropped << attname end end new_table.attributes.keys.each do |attname| added << attname unless old_table.has_attribute?(attname) end add_script(:table_changes , "-- [#{old_table.name}] dropped attributes") unless dropped.empty? dropped.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} DROP COLUMN #{attname} CASCADE;") end add_script(:table_changes , "-- [#{old_table.name}] added attributes") unless added.empty? added.each do |attname| add_script(:table_changes , "ALTER TABLE #{old_table.name} ADD COLUMN #{new_table.attributes[attname].definition};") end add_script(:table_changes , "-- [#{old_table.name}] changed attributes") unless changed.empty? changed.each do |attname| old_att = old_table.attributes[attname] new_att = new_table.attributes[attname] add_script(:table_changes , "-- attribute: #{attname}") add_script(:table_changes , "-- OLD : #{old_att.definition}") add_script(:table_changes , "-- NEW : #{new_att.definition}") if old_att.type_def != new_att.type_def add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} TYPE #{new_att.type_def};") end if old_att.default != new_att.default if new_att.default.nil? add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} DROP DEFAULT;") else add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} SET DEFAULT #{new_att.default};") end end if old_att.notnull != new_att.notnull add_script(:table_changes , "ALTER TABLE #{old_table.name} ALTER COLUMN #{attname} #{new_att.notnull ? 'SET' : 'DROP'} NOT NULL;") end end end def diff_constraints(old_table, new_table) dropped = [] added = [] old_table.constraints.keys.each do |conname| if new_table.has_constraint?(conname) if old_table.constraints[conname] != new_table.constraints[conname] dropped << conname added << conname end else dropped << conname end end new_table.constraints.keys.each do |conname| added << conname unless old_table.has_constraint?(conname) end dropped.each do |name| add_script(:constraints_drop , "ALTER TABLE #{old_table.name} DROP CONSTRAINT #{name};") end added.each do |name| add_cnstr(old_table.name, name, new_table.constraints[name]) end end def add_cnstr(tablename, cnstrname, cnstrdef) c_string = "ALTER TABLE #{tablename} ADD CONSTRAINT #{cnstrname} #{cnstrdef} ;" case cnstrdef when /^CHECK / then @c_check << c_string when /^PRIMARY / then @c_primary << c_string when /^FOREIGN / then @c_foreign << c_string when /^UNIQUE / then @c_unique << c_string end end def diff_indexes(old_table, new_table) dropped = [] added = [] old_table.indexes.keys.each do |name| if new_table.has_index?(name) if old_table.indexes[name] != new_table.indexes[name] dropped << name added << name end else dropped << name end end new_table.indexes.each do |name| added << name unless old_table.has_index?(name) end dropped.each do |name| add_script(:indices_drop , "DROP INDEX #{name};") end added.each do |name| add_script(:indices_create , (new_table.indexes[name] + ";")) if new_table.indexes[name] end end end end def parse_conn_params(str) h = {} str.split(/:/).each{|pair| key, value = pair.split('=', 2); h[key]=value} h end diff = PostgreSqlSchema::Diff.new(parse_conn_params(ARGV[0]), parse_conn_params(ARGV[1]) ) diff.run_compare puts diff.output
December 8, 2005
by Snippets Manager
·
7,917 Views
