Class: DBLeftovers::PostgresDatabaseInterface

Inherits:
GenericDatabaseInterface show all
Defined in:
lib/db_leftovers/postgres_database_interface.rb

Instance Method Summary collapse

Methods inherited from GenericDatabaseInterface

#execute_add_constraint, #execute_add_foreign_key, #execute_add_index, #execute_drop_constraint, #execute_drop_foreign_key, #execute_drop_index, #execute_sql

Constructor Details

#initialize(conn = nil) ⇒ PostgresDatabaseInterface

Returns a new instance of PostgresDatabaseInterface.



5
6
7
 
# File 'lib/db_leftovers/postgres_database_interface.rb', line 5

def initialize(conn=nil)
  @conn = conn || ActiveRecord::Base.connection
end

Instance Method Details

#lookup_all_constraints ⇒ Object 



109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/db_leftovers/postgres_database_interface.rb', line 109

def lookup_all_constraints
  ret = {}
  sql = "      SELECT  c.conname,\n              t.relname,\n              pg_get_expr(c.conbin, c.conrelid)\n      FROM    pg_catalog.pg_constraint c,\n              pg_catalog.pg_class t,\n              pg_catalog.pg_namespace n\n      WHERE   c.contype = 'c'\n      AND     c.conrelid = t.oid\n      AND     t.relkind = 'r'\n      AND     n.oid = t.relnamespace\n      AND     n.nspname NOT IN ('pg_catalog', 'pg_toast')\n      AND     pg_catalog.pg_table_is_visible(t.oid)\n  EOQ\n  @conn.select_rows(sql).each do |constr_name, on_table, check_expr|\n    ret[constr_name] = Constraint.new(constr_name, on_table, remove_outer_parens(check_expr))\n  end\n  return ret\nend\n"

#lookup_all_foreign_keys ⇒ Object 



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# 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 = "      SELECT  c.conname,\n              t1.relname AS from_table,\n              a1.attname AS from_column,\n              t2.relname AS to_table,\n              a2.attname AS to_column,\n              c.confdeltype\n      FROM    pg_catalog.pg_constraint c,\n              pg_catalog.pg_class t1,\n              pg_catalog.pg_class t2,\n              pg_catalog.pg_attribute a1,\n              pg_catalog.pg_attribute a2,\n              pg_catalog.pg_namespace n1,\n              pg_catalog.pg_namespace n2\n      WHERE   c.conrelid = t1.oid\n      AND     c.confrelid = t2.oid\n      AND     c.contype = 'f'\n      AND     a1.attrelid = t1.oid\n      AND     a1.attnum = ANY(c.conkey)\n      AND     a2.attrelid = t2.oid\n      AND     a2.attnum = ANY(c.confkey)\n      AND     t1.relkind = 'r'\n      AND     t2.relkind = 'r'\n      AND     n1.oid = t1.relnamespace\n      AND     n2.oid = t2.relnamespace\n      AND     n1.nspname NOT IN ('pg_catalog', 'pg_toast')\n      AND     n2.nspname NOT IN ('pg_catalog', 'pg_toast')\n      AND     pg_catalog.pg_table_is_visible(t1.oid)\n      AND     pg_catalog.pg_table_is_visible(t2.oid)\n  EOQ\n  @conn.select_rows(sql).each do |constr_name, from_table, from_column, to_table, to_column, del_type|\n    del_type = case del_type\n               when 'a'; nil\n               when 'c'; :cascade\n               when 'n'; :set_null\n               else; raise \"Unknown del type: \#{del_type}\"\n               end\n    ret[constr_name] = ForeignKey.new(constr_name, from_table, from_column, to_table, to_column, :on_delete => del_type)\n  end\n  return ret\nend\n"

#lookup_all_indexes ⇒ Object 



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/db_leftovers/postgres_database_interface.rb', line 9

def lookup_all_indexes
  ret = {}
  sql = "      SELECT  ix.indexrelid,\n              ix.indrelid,\n              t.relname AS table_name,\n              i.relname AS index_name,\n              ix.indisunique AS is_unique,\n              array_to_string(ix.indkey, ',') AS column_numbers,\n              am.amname AS index_type,\n              pg_get_expr(ix.indpred, ix.indrelid) AS where_clause,\n              pg_get_expr(ix.indexprs, ix.indrelid) AS index_function\n      FROM    pg_class t,\n              pg_class i,\n              pg_index ix,\n              pg_namespace n,\n              pg_am am\n      WHERE   t.oid = ix.indrelid\n      AND     n.oid = t.relnamespace\n      AND     i.oid = ix.indexrelid\n      AND     t.relkind = 'r'\n      AND     n.nspname NOT IN ('pg_catalog', 'pg_toast')\n      AND     pg_catalog.pg_table_is_visible(t.oid)\n      AND     NOT ix.indisprimary\n      AND     i.relam = am.oid\n      GROUP BY  t.relname,\n                i.relname,\n                ix.indisunique,\n                ix.indexrelid,\n                ix.indrelid,\n                ix.indkey,\n                am.amname,\n                ix.indpred,\n                ix.indexprs\n      ORDER BY t.relname, i.relname\n  EOQ\n  @conn.select_rows(sql).each do |indexrelid, indrelid, table_name, index_name, is_unique, column_numbers, index_method, where_clause, index_function|\n    where_clause = remove_outer_parens(where_clause) if where_clause\n    index_method = nil if index_method == 'btree'\n    ret[index_name] = Index.new(\n      table_name,\n      column_names_for_index(indrelid, column_numbers.split(\",\")),\n      unique: is_unique == 't',\n      where: where_clause,\n      function: index_function,\n      using: index_method,\n      name: index_name\n    )\n  end\n  return ret\nend\n"