DZone Snippets: postgresql code

PostgreSQL: generate DDL to alter views with dependencies

dseverin () — Thu, 17 May 2007 14:19:15 GMT

Determine dependencies on given table/view or their columns, types (base/domain/composite), functions, rules and show DROP/ALTER/CREATE series to update their definitions.



#!/usr/bin/ruby -W0

#

# This script tries to determine all dependencies on given table/view or their columns, 

# types (base/domain/composite), functions, rules.

#

# Usage:

#       ruby show_obj_deps conn_string object_id alter_stmt 

# e.g:

#       ruby show_obj_deps dbname=db1 "VIEW public.base_stats" "DROP VIEW public.base_stats; CREATE VIEW public.base_stats AS SELECT ..."

#

# Result will be series of DDL DROP statements for dependent objects, then alter_stmt, and

# then series of DDL CREATE statements for dropped objects.

#

# Format of object_id:

#   TABLE schema_name.table_name

#   VIEW schema_name.view_name

#   TABLE schema_name.table_name COLUMN column_name

#   VIEW schema_name.view_name COLUMN column_name

#   FUNCTION schema_name.func_name(type_1, type2, ...)

#   RULE rule_name ON schema_name.obj_name

#

# This script is somewhat rewritten version of http://snippets.dzone.com/posts/show/2105

#

# Developed using PostgreSQL v8.0.3, v8.1 with ruby-postgres libpq binding

# 



require 'postgres'

require 'tsort'

require 'pp'

PGconn.translate_results = true



$PG_CLASSES = {}

class PgDependencyGraph            

class DBObject

  attr_accessor :row, :o_type, :nsp

  def initialize(conn, class_id, obj_id, sub_id)

    @o_type   = $PG_CLASSES[class_id]

    @row   = conn.query(sql_for(@o_type, obj_id, sub_id)).first

    @nsp = row['nsp']

    if @o_type == 'pg_proc'

      arg_types = row.last.split(" ")

      unless arg_types.empty?

        arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")

        row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"

      else 

	     row[-1] = "()"

      end

    end

  end

  def sql_for(pg_class, obj_id, sub_id)

  base_sql = case pg_class 

    when 'pg_type' 

      "select       (case 

      	when typtype = 'b' then 'BASE '

	when typtype = 'c' then 'COMPOSITE '

	when typtype = 'd' then 'DOMAIN '

	when typtype = 'p' then 'PSEUDO '

      end) || coalesce( 

      (select (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

	    WHEN relkind = 'c' THEN ' '

        END) from pg_class c where c.oid = typrelid), ' '), 

(select nspname from pg_namespace n where n.oid = typnamespace) as nsp,       typname as obj_name,

      #{sub_id} from pg_type  "

    when 'pg_proc' 

      "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "

    when 'pg_class'

      "select 

  (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

        END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp, 

       relname as obj_name,  

      (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"

    when 'pg_rewrite'

      "select (select nspname from pg_namespace n where n.oid = (select relnamespace from pg_class c where c.oid = ev_class) ) as nsp, rulename, (select relname from pg_class c where c.oid = ev_class)  from pg_rewrite"

    else

        puts "IGNORE: #{pg_class}, #{obj_id}, #{sub_id}"

        nil

  end

  base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil

  

end

 

  def to_s

    case @o_type

    	when 'pg_proc' : "FUNCTION #{@row[0]}.#{@row[1]}#{@row[2]}"

      when 'pg_type': "TYPE: #{@row[0]} #{@row[1]}.#{@row[2]}"

      when 'pg_class' : "#{@row[0]} #{@row[1]}.#{@row[2]}" + (row[3] ? " COLUMN #{@row[3]}" : "")

      when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW #{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON #{@row[0]}.#{@row[2]}"

    end 

  end

end

  attr_accessor :depend_graph

    ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']

  def initialize(conn)

    conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|

      $PG_CLASSES[row['classid']] = row['relname']

    end

    conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|

      $PG_CLASSES[row['refclassid']] = row['relname']

    end

    dep_graphs = {}

    conn.query("SELECT * FROM pg_catalog.pg_depend   where  true or ( deptype<> 'i' and deptype <> 'p')").each do |row|

    if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])

      this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])

      that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])

      #dep_string = this_obj.nsp !~ /^pg_toast$/ ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil

      dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/  ? this_obj.to_s : nil

    

      if dep_string && this_obj.to_s !~ /^INDEX /

        dep_graphs[that_obj.to_s] ||=[]

        dep_graphs[that_obj.to_s] << dep_string

        if that_obj.to_s =~ /^(VIEW|TABLE) (.+?) COLUMN /m

          ds2 = that_obj.to_s.sub(/ COLUMN.*/m, '')

          dep_graphs[ds2] ||=[]

          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2

        elsif that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW) /

          ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW) /, '\1 ')

          dep_graphs[ds2] ||=[]

          dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2

        end

      end

    end

  end

  dep_graphs.each do |that, values|

    values.uniq!

    values.reject! {|item| item == that}

  end

  @depend_graph = dep_graphs

 end

 def list_dependencies(obj)

    dep_list = []

    if @depend_graph[obj]

      @depend_graph[obj].each do |v|

        k = list_dependencies(v)

        k.empty? ? dep_list << v : dep_list << [v, k]

      end

    end

    dep_list

 end

end



class DG

 include TSort

 def initialize(dep_graph, node_list)

   @nodes = node_list

   @dg = dep_graph

 end

 def tsort_each_node(&block)

   @nodes.each {|x| yield x}

 end

 def tsort_each_child(node, &block)

   (@dg[node]||[]).each(&block)

 end

end    



class Function

  attr_reader :typed_head

  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

      @arnames = tuple['function_arg_names']

    else

      @arnames = [""] * @tipes.length

    end

    alist = []

    atypelist = [] 

    @tipes.each_with_index do |typ,idx|

      ft = format_type(conn, typ)

      alist << (@arnames[idx] +" " + ft)

      atypelist << ft

    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

    @typed_head = @name+"("+atypelist.join(", ")+")"

  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

  def self.find(conn, schema, name, args)

    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|pg_temp_'

     AND nspname = $1

     AND proname = $2

     AND oidvectortypes(proargtypes) = $3

    EOT



    Function.new(conn, conn.query(func_query, schema, name, args).first)

  end

end



conn = PGconn.new(ARGV[0])

graph = PgDependencyGraph.new(conn)

#pp graph.list_dependencies(ARGV[1])

dep_list = []

ARGV[1].split(/\|/).each do |obj_id|

  dep_list += graph.list_dependencies(obj_id).flatten.uniq

end

dep_list.uniq!



top_sorted = DG.new(graph.depend_graph, dep_list).tsort

top_sorted.each do |line|

  case line

        when /^TYPE:/, /^VIEW (\S+) COLUMN/; 

        when /^VIEW (.+)/ then puts "DROP VIEW #$1;"

        when /^FUNCTION (.+)/ then puts "DROP FUNCTION #$1;"

        when /^RULE / then puts "DROP "+line+";"

        else 

        puts "-- SKIP #{line}"

  end

end



puts "", "--- ", "--- ALTER: ", "---"

puts ARGV[2]

puts "---", "---", ""

def view_def(conn, name)

  conn.select_value("SELECT pg_catalog.pg_get_viewdef('#{name}'::regclass, true)")

end

def rule_def(conn, tablename, rule_name)

 conn.select_value("select definition from pg_rules  where schemaname || '.' ||  tablename = $1  and rulename = $2", tablename, rule_name)

end



def func_def(conn, *args)

 Function.find(conn, *args).definition

end





top_sorted.reverse.each do |line|

  case line

        when /^TYPE:/, /^VIEW (\S+) COLUMN/ then puts "-- SKIP #{line}"

        when /^VIEW (.+)/ then puts "CREATE VIEW #$1 AS "+view_def(conn, $1)

        when /^FUNCTION (\w+)\.(\w+)\((.*)\)/ then puts func_def(conn, $1, $2, $3)

        when /^RULE (.+) ON (.+)/ then puts "CREATE "+rule_def(conn, $2, $1)

        else

          puts "-- SKIP #{line}"

  end

  puts 

end

pg_show_deps: outputs dependencies graph for tables, types, proc, views in PostgreSQL database schema.

dseverin () — Fri, 26 May 2006 12:28:54 GMT

pg_show_deps: outputs dependencies graph for tables, types, proc, views in PostgreSQL database schema.
output is produced in dot format and can be processed using graphviz (http://www.graphviz.org/) toolset
# to get a picture of dependencies.



#!/bin/env ruby

#

# pg_show_deps: outputs dependencies graph for objects (tables, types, proc, views) in PostgreSQL database schema.

# output is produced in dot format and can be processed using graphviz (http://www.graphviz.org/) toolset

# to get a picture of dependencies.

#

# Sample usage:

#    pg_show_deps 'dbname=template1 port=5432 user=looser' >template1.dot

#    ccomps -otemplate_split.dot -x template1.dot

#    dot -Tps -o template_2.ps template_split_2.dot

#

# Developed using PostgreSQL 8.1, 

# requires ruby-postgres driver (http://rubyforge.org/projects/ruby-postgres)



require 'postgres'

$PG_CLASSES = {}

            

class DBObject

  attr_accessor :row, :o_type, :nsp

  def initialize(conn, class_id, obj_id, sub_id)

    @o_type   = $PG_CLASSES[class_id]

    @row   = conn.query(sql_for(@o_type, obj_id, sub_id)).first

    @nsp = row['nsp']

    if @o_type == 'pg_proc'

      arg_types = row.last.split(" ")

      unless arg_types.empty?

        arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")

        row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"

      else 

	     row[-1] = "()"

      end

    end

  end

  def sql_for(pg_class, obj_id, sub_id)

  base_sql = case pg_class 

    when 'pg_type' 

      "select       (case 

      	when typtype = 'b' then 'BASE '

	when typtype = 'c' then 'COMPOSITE '

	when typtype = 'd' then 'DOMAIN '

	when typtype = 'p' then 'PSEUDO '

      end) || coalesce( 

      (select (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

	    WHEN relkind = 'c' THEN ' '

        END) from pg_class c where c.oid = typrelid), ' '), 

(select nspname from pg_namespace n where n.oid = typnamespace) as nsp,       typname as obj_name,

      #{sub_id} from pg_type  "

    when 'pg_proc' 

      "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "

    when 'pg_class'

      "select 

  (CASE WHEN relkind = 'r' THEN 'TABLE'

            WHEN relkind = 'v' THEN 'VIEW'

            WHEN relkind = 'i' THEN 'INDEX'

            WHEN relkind = 'S' THEN 'SEQUENCE'

            WHEN relkind = 's' THEN 'SPECIAL'

            WHEN relkind = 't' THEN 'TOAST'

        END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp, 

       relname as obj_name,  

      (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"

    when 'pg_rewrite'

      "select (select nspname from pg_namespace n where n.oid = (select relnamespace from pg_class c where c.oid = ev_class) ) as nsp, rulename, (select relname from pg_class c where c.oid = ev_class)  from pg_rewrite"

    else nil

  end

  base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil

  

end

 

  def to_s

    case @o_type

    	when 'pg_proc' : "FUNCTION\\n#{@row[0]}.#{@row[1]}#{@row[2]}"

	when 'pg_type': "TYPE: #{@row[0]}\\n#{@row[1]}.#{@row[2]}"

	when 'pg_class' : "#{@row[0]}\\n#{@row[1]}.#{@row[2]}" + (row[3] ? "\\nCOLUMN #{@row[3]}" : "")

	when 'pg_rewrite' :  @row[1] == '_RETURN' ? "VIEW\\n#{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON \\n#{@row[0]}.#{@row[2]}"

    end 

  end

end



# connection to database

conn=PGconn.new(ARGV[0])



conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|

  $PG_CLASSES[row['classid']] = row['relname']

end

conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|

  $PG_CLASSES[row['refclassid']] = row['relname']

end

ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']

dep_graphs = {}

conn.query("SELECT * FROM pg_catalog.pg_depend   where   deptype<> 'i' and deptype <> 'p'").each do |row|

  if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])

    this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])

    that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])

    #dep_string = this_obj.nsp !~ /^pg_toast$/ ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil

    dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/  ? "\"#{this_obj.to_s}\" -> \"#{that_obj.to_s}\"; \n" : nil

    

    if dep_string && this_obj.to_s !~ /^INDEX\\n/

      dep_graphs[that_obj.to_s] ||=[]

      dep_graphs[that_obj.to_s] << dep_string

      if that_obj.to_s =~ /^(VIEW|TABLE)\\n(.+?)\\nCOLUMN /m

        ds2 = that_obj.to_s.sub(/\\nCOLUMN.*/m, '')

        dep_graphs[ds2] ||=[]

        dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"

      end

      if that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW)\\n/

        ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW)\\n/, '\1\n')

        dep_graphs[ds2] ||=[]

        dep_graphs[ds2] << "\"#{that_obj.to_s}\" -> \"#{ds2}\"; \n"

      end



    end

  end

end

print <<-EOT

digraph g {

  graph [rankdir = "RL",concentrate = true,ratio = auto, overlap=false];

  node [fontsize = "10"];

  edge [];

EOT

dep_graphs.each do |that, values|

 puts "subgraph \"#{that}\" {"

    values.uniq.each do |v|

	    a = v.split( / -> /)

	    puts v unless a[0] == "\"#{that}\""

    end

 puts "}"

end

print "}"

Simple helper script to quickly create images for dependencies subgraphs.



#!/bin/sh

# mk_dep_doc.sh conninfo splitprefix output_type

# e.g.:

# mk_dep_doc.sh "dbname=template1" template_split png

./pg_show_deps "$1" | ccomps -o "$2.dot" -x

find -name "$2*dot" -exec dot -T$3 -o{}.$3 {} \;

find -name "$2*dot" -exec echo "{}   Image
" \; >index.html

PgProc - call PostgreSQL functions from Rails app

dseverin () — Thu, 22 Dec 2005 18:52:12 GMT



# Just for fun and horror: make database functions a part of your Rails model! :)

#

# Class to access to PostgreSQL functions. Returned value depends on params and query result, see below.

#

# Currently supported options are:

#     :order => '1 desc' # to add order clause

#     :use_from => true  # to add "* from" for non-model function queries, which return records

#     :all => true       # to return not first but all found models

#     :cast => string    # to cast result (useful for functions, returning +setof record+)

#

# Call-patterns:

#

# A) Model loading from functions that return setof system known rowtype

#     PgProc.function(ModelClass[, options])

#         PgProc.get_descendants(ContentNode, 123)

#

#     PgProc.function(ModelClass, value[, options ])

#         PgProc.get_children(ContentNode, 123, :order => 'position', :all => true)

#

# returns either:

# * empty array if nothing found

# * first found model object, if found only one and +:all+ option is not set

# * array of model objects

#

# B) Values from functions

#

# PgProc.function(:type_symbol, value, [type_symbol2, value2, ...[, options]]) - for explicit parameter typecast

#     PgProc.array_append(:"int[]", '{1,2,3,4}', :int, 5) # => {1,2,3,4,5}

#

# PgProc.function(*args [, options])

#     PgProc.generate_series(1,10,2, :order => '1 desc') # => [9,5,7,3,2,1]

#

# PgProc.function() - for functions w/o params

#     PgProc.now()

#

# returns either:

# * empty string for +void+ functions

# * single value, if resultset has 1x1 dimension

# * array of values if resultset has Nx1 dimension (N>1)

# * array of rows otherwise

#

# Throws PGError, if function doesn't exist or wrong params supplied



class PgProc < ActiveRecord::Base

    set_table_name 'pg_catalog.pg_proc'

    set_primary_key 'oid'

    def readonly?

      true

    end



private

    def self.method_missing(meth_sym, *args)

      func_name = meth_sym.id2name

      super unless find(:first, :conditions => ['proname = ?', func_name])

      if ! args.empty? && args.last.is_a?(Hash)

        options = args.pop

        order_str = " ORDER BY #{options[:order]}" if options[:order]

      else

        options = {}

        order_str = nil

      end

      from_str = " * FROM " if options[:use_from]

      if args.empty?

        temp = connection.query("select #{from_str} #{func_name}() #{options[:cast]} #{order_str}")

      elsif args.first.is_a?(Class)

        model_klass = args.shift

        if args.length == 0

          temp = model_klass.find_by_sql("select * from  #{func_name}()  #{options[:cast]} #{order_str}")

        else

          temp = model_klass.find_by_sql("select * from  #{func_name}(#{quote_bound_value(args)})  #{options[:cast]} #{order_str}")

        end

        return temp if options[:all]

        return temp.length == 1 ? temp.first : temp

      else

          if args.length % 2 == 0 && args.first.is_a?(Symbol)

            temp = connection.query("select #{from_str} #{func_name}(#{quote_bound_value_types(args)}) #{options[:cast]} #{order_str}")

          else

            temp = connection.query("select #{from_str} #{func_name}(#{quote_bound_value(args)})  #{options[:cast]} #{order_str}")

          end

      end

      return temp.first.first if temp.length == 1 && temp.first.length == 1

      return temp.flatten if temp.length > 1 && temp.first.length == 1

      return temp

    end

    def self.quote_bound_value_types(value)

      i = true

      value.partition {|v| i = !i }.transpose.map{|v| "#{connection.quote(v[0])}::#{v[1]}"}.join(',')

    end

end

pg_diff - compare two PostgreSQL database schemas

dseverin () — Thu, 08 Dec 2005 01:07:07 GMT



#!/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