Class: ActiveRecord::ConnectionAdapters::PostgreSQLAdapter

Inherits:
Object
  • Object
show all
Defined in:
lib/updateable_views_inheritance/postgresql_adapter.rb

Defined Under Namespace

Modules: Tutuf

Instance Method Summary collapse

Instance Method Details

#create_child(child_view, options) ⇒ Object

Use this in migration to create child table and view. Options:

:parent

parent relation

:child_table_name

default is "#{child_view}_data"



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
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 10

def create_child(child_view, options)
  raise 'Please call me with a parent, for example: create_child(:steam_locomotives, :parent => :locomotives)' unless options[:parent]

  schema, unqualified_child_view_name = Utils.extract_schema_and_table(child_view)

  parent_relation = options[:parent].to_s
  if tables.include?(parent_relation)
    parent_table = parent_relation
  else # view, interpreted as inheritance chain deeper than two levels
    parent_table = query("SELECT child_relation FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(parent_relation)}")[0][0]
  end

  child_table = options[:table] || quote_table_name("#{child_view}_data")
  child_table_pk = "#{unqualified_child_view_name.singularize}_id"

  create_table(child_table, :id => false) do |t|
    t.integer child_table_pk, :null => false
    yield t
  end
  execute "ALTER TABLE #{child_table} ADD PRIMARY KEY (#{child_table_pk})"
  execute "ALTER TABLE #{child_table} ADD FOREIGN KEY (#{child_table_pk})
           REFERENCES #{parent_table} ON DELETE CASCADE ON UPDATE CASCADE"
  
  create_child_view(parent_relation, child_view, child_table)
end

#create_child_view(parent_table, child_view, child_table = nil) ⇒ Object

Creates aggregate updateable view of parent and child relations. The convention for naming child tables is "#{child_view}_data". If you don’t follow it, supply child_table_name as third argument.



46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 46

def create_child_view(parent_table, child_view, child_table=nil)
  child_table ||= child_view.to_s + "_data"

  parent_columns = columns(parent_table)
  child_columns  = columns(child_table)

  child_column_names = child_columns.collect{|c| c.name}
  parent_column_names = parent_columns.collect{|c| c.name}

  child_pk = pk_and_sequence_for(child_table)[0]
  child_column_names.delete(child_pk)

  parent_pk, parent_pk_seq = pk_and_sequence_for(parent_table)
  parent_column_names.delete(parent_pk)

  do_create_child_view(parent_table, parent_column_names, parent_pk, child_view, child_column_names, child_pk, child_table)
  make_child_view_updateable(parent_table, parent_column_names, parent_pk, parent_pk_seq, child_view, child_column_names, child_pk, child_table)

  # assign default values for table columns on the view - it is not automatic in Postgresql 8.1
  set_defaults(child_view, parent_table)
  set_defaults(child_view, child_table)
  create_system_table_records(parent_table, child_view, child_table)
end

#create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil) ⇒ Object

Creates Single Table Inheritanche-like aggregate view called sti_aggregate_view for parent_relation and all its descendants. The view isn’t updateable. The order of all or just the first few columns in the aggregate view can be explicitly set by passing array of column names as third argument. If there are columns with the same name but different types in two or more relations they will appear as a single column of type text in the view.



191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 191

def create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil)
  columns_for_view ||= []
  relations_heirarchy = get_view_hierarchy_for(parent_relation)
  relations = relations_heirarchy.flatten
  leaves_relations = get_leaves_relations(relations_heirarchy)
  all_columns = leaves_relations.map{|rel| columns(rel)}.flatten
  columns_hash = {}
  conflict_column_names = []
  all_columns.each do |col|
    c = columns_hash[col.name]
    if(c && col.sql_type != c.sql_type)
      conflict_column_names << col.name
    else 
      columns_hash[col.name] = col
    end
  end
  conflict_column_names = conflict_column_names.uniq.sort if !conflict_column_names.empty?
  sorted_column_names = (columns_for_view + columns_hash.keys.sort).uniq
  parent_klass_name = Tutuf::ClassTableReflection.get_klass_for_table(parent_relation)
  quoted_inheritance_column = quote_column_name(parent_klass_name.inheritance_column)
  queries = relations.map{|rel| generate_single_table_inheritanche_union_clause(rel, sorted_column_names, conflict_column_names, columns_hash, quoted_inheritance_column)}
  unioin_clauses = queries.join("\n UNION ")
  execute "    CREATE VIEW \#{sti_aggregate_view} AS (\n      \#{unioin_clauses}\n    )\n  end_sql\nend\n"

#drop_child(child_view) ⇒ Object

Drop child view and table



37
38
39
40
41
42
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 37

def drop_child(child_view)
  drop_view(child_view)
  child_table = query("SELECT child_relation FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(child_view)}")[0][0]
  drop_table(child_table)
  execute "DELETE FROM updateable_views_inheritance WHERE child_aggregate_view = #{quote(child_view)}"
end

#drop_view(name) ⇒ Object

Drops a view from the database.



122
123
124
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 122

def drop_view(name)
  execute "DROP VIEW #{name}"
end

#is_view?(name) ⇒ Boolean

Checks whether relation name is a view.

Returns:

  • (Boolean) 


137
138
139
140
141
142
143
144
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 137

def is_view?(name)
  result = query("    SELECT viewname\n      FROM pg_views\n     WHERE viewname = '\#{name}'\n  SQL\n  !result.empty?\nend\n", name).map { |row| row[0] }

#pk_and_sequence_for(relation) ⇒ Object

Returns a relation’s primary key and belonging sequence. If relation is a table the result is its PK and sequence. When it is a view, PK and sequence of the table at the root of the inheritance chain are returned.



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 100

def pk_and_sequence_for(relation)
  result = query("    SELECT attr.attname\n      FROM pg_attribute attr,\n           pg_constraint cons\n     WHERE cons.conrelid = attr.attrelid\n       AND cons.conrelid = '\#{relation}'::regclass\n       AND cons.contype  = 'p'\n       AND attr.attnum   = ANY(cons.conkey)\n  end_sql\n  if result.nil? or result.empty?\n    parent = parent_table(relation)\n    pk_and_sequence_for(parent) if parent\n  else\n    # log(result[0], \"PK for \#{relation}\")\n    [result[0], query(\"SELECT pg_get_serial_sequence('\#{relation}', '\#{result[0]}') \")[0][0]]\n  end\nrescue\n  nil\nend\n", 'PK')[0]

#primary_key(relation) ⇒ Object 



93
94
95
96
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 93

def primary_key(relation)
  res = pk_and_sequence_for(relation)
  res && res.first
end

#rebuild_parent_and_children_views(parent_relation) ⇒ Object

Recreates views in the part of the hierarchy chain starting from the parent_relation.



162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 162

def rebuild_parent_and_children_views(parent_relation)
  # Current implementation is not very efficient - it can drop and recreate one and the same view in the bottom of the hierarchy many times.
  remove_parent_and_children_views(parent_relation)
  children = query("    SELECT parent_relation, child_aggregate_view, child_relation\n      FROM updateable_views_inheritance\n     WHERE parent_relation = '\#{parent_relation}'\n  end_sql\n\n  #if the parent is in the middle of the inheritance chain, it's a view that should be rebuilt as well\n  parent = query(<<-end_sql)[0]\n    SELECT parent_relation, child_aggregate_view, child_relation\n      FROM updateable_views_inheritance\n     WHERE child_aggregate_view = '\#{parent_relation}'\n  end_sql\n  create_child_view(parent[0], parent[1], parent[2]) if (parent && !parent.empty?)\n\n  children.each do |child|\n    create_child_view(child[0], child[1], child[2])\n    rebuild_parent_and_children_views(child[1])\n  end\nend\n")

#rebuild_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil) ⇒ Object

Recreates the Single_Table_Inheritanche-like aggregate view sti_aggregate_view for parent_relation and all its descendants.



222
223
224
225
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 222

def rebuild_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil)
  drop_view(sti_aggregate_view)
  create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view)
end

#remove_parent_and_children_views(parent_relation) ⇒ Object

Recursively delete parent_relation (if it is a view) and the children views the depend on it.



147
148
149
150
151
152
153
154
155
156
157
158
159
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 147

def remove_parent_and_children_views(parent_relation)
  children_views = query("    SELECT child_aggregate_view\n      FROM updateable_views_inheritance\n     WHERE parent_relation = '\#{parent_relation}'\n  end_sql\n  children_views.each do |cv|\n    remove_parent_and_children_views(cv)\n    # drop the view only if it wasn't dropped beforehand in recursive call from other method.\n    drop_view(cv) if is_view?(cv)\n  end\n  drop_view(parent_relation) if is_view?(parent_relation)\nend\n").map{|row| row[0]}

#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object

Resets sequence to the max value of the table’s pk if present respecting inheritance (i.e. one sequence can be shared by many tables).



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 71

def reset_pk_sequence!(table, pk = nil, sequence = nil)
  parent = parent_table(table)
  if parent
    reset_pk_sequence!(parent, pk, sequence)
  else
    unless pk and sequence
      default_pk, default_sequence = pk_and_sequence_for(table)
      pk ||= default_pk
      sequence ||= default_sequence
    end
    if pk
      if sequence
        select_value "          SELECT setval('\#{sequence}', (SELECT COALESCE(MAX(\#{pk})+(SELECT increment_by FROM \#{sequence}), (SELECT min_value FROM \#{sequence})) FROM \#{table}), false)\n        end_sql\n      else\n        @logger.warn \"\#{table} has primary key \#{pk} with no default sequence\" if @logger\n      end\n    end\n  end\nend\n", 'Reset sequence'

#supports_disable_referential_integrity? ⇒ Boolean

Overriden - it must return false, otherwise deleting fixtures won’t work

Returns:

  • (Boolean) 


228
229
230
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 228

def supports_disable_referential_integrity?
  false
end

#table_exists_with_updateable_views_inheritance_support?(name) ⇒ Boolean

Returns:

  • (Boolean) 


232
233
234
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 232

def table_exists_with_updateable_views_inheritance_support?(name)
  is_view?(name) ? true : table_exists_without_updateable_views_inheritance_support?(name)
end

#views(name = nil) ⇒ Object

Return the list of all views in the schema search path.



127
128
129
130
131
132
133
134
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 127

def views(name=nil)
  schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
  query("    SELECT viewname\n      FROM pg_views\n     WHERE schemaname IN (\#{schemas})\n  SQL\nend\n", name).map { |row| row[0] }