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



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.



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

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



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



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



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_all_parent_and_children_views ⇒ Object

Recreates all views in all hierarchy chains



162
163
164
165
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 162

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.



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/updateable_views_inheritance/postgresql_adapter.rb', line 168

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.



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

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



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


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

def supports_disable_referential_integrity?
  false
end

#table_exists_with_updateable_views_inheritance_support?(name) ⇒ Boolean

Returns:

  • (Boolean) 


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

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(/,\s*/).map { |p| quote(p) }.join(',')
  query(<<-SQL, name).map { |row| row[0] }
    SELECT viewname
      FROM pg_views
     WHERE schemaname IN (#{schemas})
  SQL
end