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

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

#!/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 "<a href={}>{}</a>   <a href={}.$3>Image</a><br/>" \; >index.html

# 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

#!/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 <dmitry.severin (at) gmail.com>
#
# 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['