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

:child_table_name

default is "#{child_view}_data"



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

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

  unqualified_child_view_name = Utils.extract_schema_qualified_name(child_view).identifier

  parent_relation = options[:parent].to_s
  if is_view?(parent_relation) # 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]
  else
    parent_table = parent_relation
  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.



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 50

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.



201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 201

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

#drop_child(child_view) ⇒ Object

Drop child view and table



41
42
43
44
45
46
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 41

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.



126
127
128
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 126

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

#is_view?(name) ⇒ Boolean

Checks whether relation name is a view.

Returns:

  • (Boolean) 


141
142
143
144
145
146
147
148
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 141

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.



104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 104

def pk_and_sequence_for(relation)
  result = query(<<-end_sql, '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)
  end_sql
  if result.nil? or result.empty?
    parent = parent_table(relation)
    pk_and_sequence_for(parent) if parent
  else
    # log(result[0], "PK for #{relation}") {}
    [result[0], query("SELECT pg_get_serial_sequence('#{relation}', '#{result[0]}') ")[0][0]]
  end
rescue
  nil
end

#primary_key(relation) ⇒ Object 



97
98
99
100
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 97

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



166
167
168
169
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 166

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.



172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 172

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

  #if the parent is in the middle of the inheritance chain, it's a view that should be rebuilt as well
  parent = query(<<-end_sql)[0]
    SELECT parent_relation, child_aggregate_view, child_relation
      FROM updateable_views_inheritance
     WHERE child_aggregate_view = '#{parent_relation}'
  end_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.



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

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.



151
152
153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 151

def remove_parent_and_children_views(parent_relation)
  children_views = query(<<-end_sql).map{|row| row[0]}
    SELECT child_aggregate_view
      FROM updateable_views_inheritance
     WHERE parent_relation = '#{parent_relation}'
  end_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).



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 75

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

#supports_disable_referential_integrity? ⇒ Boolean

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

Returns:

  • (Boolean) 


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

def supports_disable_referential_integrity?
  false
end

#table_exists_with_updateable_views_inheritance_support?(name) ⇒ Boolean

Returns:

  • (Boolean) 


242
243
244
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 242

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.



131
132
133
134
135
136
137
138
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 131

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