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

Deprecated. Use :child_table instead

:child_table

Default is "#{child_view}_data"

:child_table_pk

Handy when :child_table is a view and PK cannot be inferred from the database.

:skip_creating_child_table

When given, :child_table option also must be specified



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

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(<<~SQL)[0][0]
                      SELECT child_relation
                      FROM updateable_views_inheritance
                      WHERE child_aggregate_view = #{quote(parent_relation)}
                    SQL
                  else
                    parent_relation
                  end

  child_table = options[:child_table] || options[:table] || "#{child_view}_data"
  child_table_pk = options[:child_table_pk].to_s if options[:child_table_pk]

  unless options.key?(:skip_creating_child_table)
  unqualified_child_view_name = Utils.extract_schema_qualified_name(child_view).identifier
  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"
  end

  create_child_view(parent_relation, child_view, child_table, child_table_pk)
end

#create_child_view(parent_table, child_view, child_table = nil, child_table_pk = 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.



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 63

def create_child_view(parent_table, child_view, child_table = nil, child_table_pk = nil)
  child_table ||= "#{child_view}_data"

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

  child_column_names = child_columns.map(&:name)
  parent_column_names = parent_columns.map(&:name)

  child_pk = child_table_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.



224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 224

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 <<-SQL
    CREATE VIEW #{sti_aggregate_view} AS (
      #{unioin_clauses}
    )
  SQL
end

#drop_child(child_view) ⇒ Object

Drop child view and table



54
55
56
57
58
59
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 54

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.



149
150
151
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 149

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

#is_view?(name) ⇒ Boolean

Checks whether relation name is a view.

Returns:

  • (Boolean) 


164
165
166
167
168
169
170
171
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 164

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

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



119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 119

def pk_and_sequence_for(relation)
  result = query(<<-SQL.squish, 'PK')[0]
    SELECT attr.attname
      FROM pg_attribute attr,
           pg_constraint cons
     WHERE cons.conrelid = attr.attrelid
       AND cons.conrelid = '#{relation}'::regclass
       AND cons.contype  = 'p'
       AND attr.attnum   = ANY(cons.conkey)
  SQL

  if result.nil? or result.empty?
    parent = parent_table(relation)
    pk_and_sequence_for(parent) if parent
  else
    pk = result[0]
    sequence = query("SELECT pg_get_serial_sequence('#{relation}', '#{result[0]}') ")[0][0]
    if sequence
      # ActiveRecord expects PostgreSQL::Name object as sequence, not a string
      sequence_with_schema = Utils.extract_schema_qualified_name(sequence)
      [pk, sequence_with_schema]
    else
      [pk, nil]
    end
  end
rescue
  nil
end

#primary_key(relation) ⇒ Object 



110
111
112
113
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 110

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



189
190
191
192
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 189

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.



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

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

  #if the parent is in the middle of the inheritance chain, it's a view that should be rebuilt as well
  parent = query(<<-SQL)[0]
    SELECT parent_relation, child_aggregate_view, child_relation
      FROM updateable_views_inheritance
     WHERE child_aggregate_view = '#{parent_relation}'
  SQL
  create_child_view(parent[0], parent[1], parent[2]) if (parent && !parent.empty?)

  children.each do |child|
    create_child_view(child[0], child[1], child[2])
    rebuild_parent_and_children_views(child[1])
  end
end

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



255
256
257
258
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 255

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.



174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 174

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

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



88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 88

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 <<-SQL, 'Reset sequence'
          SELECT setval('#{sequence}', (SELECT COALESCE(MAX(#{pk})+(SELECT increment_by FROM #{sequence}), (SELECT min_value FROM #{sequence})) FROM #{table}), false)
        SQL
      else
        @logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger
      end
    end
  end
end

#set_defaults(view_name, table_name) ⇒ Object

Set default values from the table columns for a view



311
312
313
314
315
316
317
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 311

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) 


261
262
263
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 261

def supports_disable_referential_integrity?
  false
end

#table_exists_with_updateable_views_inheritance_support?(name) ⇒ Boolean

Returns:

  • (Boolean) 


265
266
267
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 265

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.



154
155
156
157
158
159
160
161
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 154

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