class DBLeftovers::PostgresDatabaseInterface
Public Class Methods
new(conn=nil)
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 5 def initialize(conn=nil) @conn = conn || ActiveRecord::Base.connection end
Public Instance Methods
lookup_all_constraints()
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 117 def lookup_all_constraints ret = {} sql = <<-EOQ SELECT c.conname, t.relname, pg_get_expr(c.conbin, c.conrelid) FROM pg_catalog.pg_constraint c, pg_catalog.pg_class t, pg_catalog.pg_namespace n WHERE c.contype = 'c' AND c.conrelid = t.oid AND t.relkind = 'r' AND n.oid = t.relnamespace AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(t.oid) EOQ @conn.select_rows(sql).each do |constr_name, on_table, check_expr| ret[constr_name] = Constraint.new(constr_name, on_table, remove_outer_parens(check_expr)) end return ret end
lookup_all_foreign_keys()
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 63 def lookup_all_foreign_keys # confdeltype: a=nil, c=cascade, n=null ret = {} # TODO: Support multi-column foreign keys: sql = <<-EOQ SELECT c.conname, t1.relname AS from_table, a1.attname AS from_column, t2.relname AS to_table, a2.attname AS to_column, c.confdeltype, c.condeferrable AS deferrable, c.condeferred AS deferred FROM pg_catalog.pg_constraint c, pg_catalog.pg_class t1, pg_catalog.pg_class t2, pg_catalog.pg_attribute a1, pg_catalog.pg_attribute a2, pg_catalog.pg_namespace n1, pg_catalog.pg_namespace n2 WHERE c.conrelid = t1.oid AND c.confrelid = t2.oid AND c.contype = 'f' AND a1.attrelid = t1.oid AND a1.attnum = ANY(c.conkey) AND a2.attrelid = t2.oid AND a2.attnum = ANY(c.confkey) AND t1.relkind = 'r' AND t2.relkind = 'r' AND n1.oid = t1.relnamespace AND n2.oid = t2.relnamespace AND n1.nspname NOT IN ('pg_catalog', 'pg_toast') AND n2.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(t1.oid) AND pg_catalog.pg_table_is_visible(t2.oid) EOQ @conn.select_rows(sql).each do |constr_name, from_table, from_column, to_table, to_column, del_type, deferrable, deferred| del_type = case del_type when 'a'; nil when 'c'; :cascade when 'n'; :set_null else; raise "Unknown del type: #{del_type}" end deferrable = deferrable == 't' deferred = deferred == 't' defer_type = if deferrable and deferred; :deferred elsif deferrable; :immediate else; nil end ret[constr_name] = ForeignKey.new(from_table, from_column, to_table, to_column, :name => constr_name, :on_delete => del_type, :deferrable => defer_type) end return ret end
lookup_all_indexes()
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 9 def lookup_all_indexes ret = {} sql = <<-EOQ SELECT ix.indexrelid, ix.indrelid, t.relname AS table_name, i.relname AS index_name, ix.indisunique AS is_unique, array_to_string(ix.indkey, ',') AS column_numbers, am.amname AS index_type, pg_get_expr(ix.indpred, ix.indrelid) AS where_clause, pg_get_expr(ix.indexprs, ix.indrelid) AS index_function FROM pg_class t, pg_class i, pg_index ix, pg_namespace n, pg_am am WHERE t.oid = ix.indrelid AND n.oid = t.relnamespace AND i.oid = ix.indexrelid AND t.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(t.oid) AND NOT ix.indisprimary AND i.relam = am.oid GROUP BY t.relname, i.relname, ix.indisunique, ix.indexrelid, ix.indrelid, ix.indkey, am.amname, ix.indpred, ix.indexprs ORDER BY t.relname, i.relname EOQ @conn.select_rows(sql).each do |indexrelid, indrelid, table_name, index_name, is_unique, column_numbers, index_method, where_clause, index_function| where_clause = remove_outer_parens(where_clause) if where_clause index_method = nil if index_method == 'btree' ret[index_name] = Index.new( table_name, column_names_for_index(indrelid, column_numbers.split(",")), unique: is_unique == 't', where: where_clause, function: index_function, using: index_method, name: index_name ) end return ret end
Private Instance Methods
column_names_for_index(table_id, column_numbers)
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 141 def column_names_for_index(table_id, column_numbers) return [] if column_numbers == ['0'] column_numbers.map do |c| sql = <<-EOQ SELECT attname FROM pg_attribute WHERE attrelid = #{table_id} AND attnum = #{c} EOQ @conn.select_value(sql) end end
remove_outer_parens(str)
click to toggle source
# File lib/db_leftovers/postgres_database_interface.rb, line 154 def remove_outer_parens(str) str ? str.gsub(/^\((.*)\)$/, '\1') : nil end