Module: ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements

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

:table

default is "#{child_view}_data"

:skip_creating_child_table

use together with :table option



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

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

  parent_relation = options[:parent].to_s
  parent_table =  if is_view?(parent_relation) # interpreted as inheritance chain deeper than two levels
                    query("                      SELECT child_relation\n                      FROM updateable_views_inheritance\n                      WHERE child_aggregate_view = \#{quote(parent_relation)}\n                    SQL\n                  else\n                    parent_relation\n                  end\n\n  child_table = options[:table] || quote_table_name(\"\#{child_view}_data\")\n\n  unless options.key?(:skip_creating_child_table)\n    unqualified_child_view_name = Utils.extract_schema_qualified_name(child_view).identifier\n    child_table_pk = \"\#{unqualified_child_view_name.singularize}_id\"\n\n    create_table(child_table, :id => false) do |t|\n      t.integer child_table_pk, :null => false\n      yield t\n    end\n    execute \"ALTER TABLE \#{child_table} ADD PRIMARY KEY (\#{child_table_pk})\"\n    execute \"ALTER TABLE \#{child_table} ADD FOREIGN KEY (\#{child_table_pk})\n            REFERENCES \#{parent_table} ON DELETE CASCADE ON UPDATE CASCADE\"\n  end\n\n  create_child_view(parent_relation, child_view, child_table)\nend\n")[0][0]

#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.



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 57

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.



208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 208

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  SQL\nend\n"

#drop_child(child_view) ⇒ Object

Drop child view and table



48
49
50
51
52
53
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 48

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.



133
134
135
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 133

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

#is_view?(name) ⇒ Boolean

Checks whether relation name is a view.

Returns:

  • (Boolean) 


148
149
150
151
152
153
154
155
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 148

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.



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

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



104
105
106
107
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 104

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

#rebuild_all_parent_and_children_views ⇒ Object

Recreates all views in all hierarchy chains



173
174
175
176
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 173

def rebuild_all_parent_and_children_views
  parent_relations = select_values('SELECT DISTINCT parent_relation FROM updateable_views_inheritance')
  parent_relations.each { |parent_relation| rebuild_parent_and_children_views(parent_relation) }
end

#rebuild_parent_and_children_views(parent_relation) ⇒ Object

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



179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 179

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  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(<<-SQL)[0]\n    SELECT parent_relation, child_aggregate_view, child_relation\n      FROM updateable_views_inheritance\n     WHERE child_aggregate_view = '\#{parent_relation}'\n  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.



239
240
241
242
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 239

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.



158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 158

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  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).



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 82

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

#set_defaults(view_name, table_name) ⇒ Object

Set default values from the table columns for a view



295
296
297
298
299
300
301
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 295

def set_defaults(view_name, table_name)
  column_definitions(table_name).each do |column_name, type, default, notnull|
    if !default.nil?
      execute("ALTER TABLE #{quote_table_name(view_name)} ALTER #{quote_column_name(column_name)} SET DEFAULT #{default}")
    end
  end
end

#supports_disable_referential_integrity? ⇒ Boolean

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

Returns:

  • (Boolean) 


245
246
247
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 245

def supports_disable_referential_integrity?
  false
end

#table_exists_with_updateable_views_inheritance_support?(name) ⇒ Boolean

Returns:

  • (Boolean) 


249
250
251
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 249

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.



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

def views(name=nil)
  schemas = schema_search_path.split(/,\s*/).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] }