# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 97 def primary_key(relation) res = pk_and_sequence_for(relation) res && res.first end
module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements
Public Instance Methods
Use this in migration to create child table and view. Options:
- :parent
-
parent relation
- :child_table_name
-
default is
"#{child_view}_data"
# 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
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.
# 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
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.
# 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 view and table
# 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
Drops a view from the database.
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 126 def drop_view(name) execute "DROP VIEW #{name}" end
Checks whether relation name
is a view.
# 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
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.
# 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
Recreates all views in all hierarchy chains
# 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
Recreates views in the part of the hierarchy chain starting from the parent_relation
.
# 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
Recreates the Single_Table_Inheritanche-like aggregate view sti_aggregate_view
for parent_relation
and all its descendants.
# 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
Recursively delete parent_relation
(if it is a view) and the children views the depend on it.
# 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
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).
# 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
Overriden - it must return false, otherwise deleting fixtures won't work
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 238 def supports_disable_referential_integrity? false end
# 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
Return the list of all views in the schema search path.
# 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
Private Instance Methods
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 359 def create_system_table_records(parent_relation, child_aggregate_view, child_relation) parent_relation, child_aggregate_view, child_relation = [parent_relation, child_aggregate_view, child_relation].collect{|rel| quote(rel.to_s)} exists = query <<-end_sql SELECT parent_relation, child_aggregate_view, child_relation FROM updateable_views_inheritance WHERE parent_relation = #{parent_relation} AND child_aggregate_view = #{child_aggregate_view} AND child_relation = #{child_relation} end_sql # log "res: #{exists}" if exists.nil? or exists.empty? execute "INSERT INTO updateable_views_inheritance (parent_relation, child_aggregate_view, child_relation)" + "VALUES( #{parent_relation}, #{child_aggregate_view}, #{child_relation} )" end end
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 287 def do_create_child_view(parent_table, parent_columns, parent_pk, child_view, child_columns, child_pk, child_table) view_columns = parent_columns + child_columns execute <<-end_sql CREATE OR REPLACE VIEW #{child_view} AS ( SELECT parent.#{parent_pk}, #{ view_columns.join(",") } FROM #{parent_table} parent INNER JOIN #{child_table} child ON ( parent.#{parent_pk}=child.#{child_pk} ) ) end_sql end
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 420 def generate_single_table_inheritanche_union_clause(rel, column_names, conflict_column_names, columns_hash, quoted_inheritance_column) relation_columns = columns(rel).collect{|c| c.name} columns_select = column_names.inject([]) do |arr, col_name| sql_type = conflict_column_names.include?(col_name) ? 'text' : columns_hash[col_name].sql_type value = "NULL::#{sql_type}" if(relation_columns.include?(col_name)) value = col_name value = "#{value}::text" if conflict_column_names.include?(col_name) end statement = " AS #{col_name}" statement = "#{value} #{statement}" arr << " #{statement}" end columns_select = columns_select.join(", ") rel_klass_name = Tutuf::ClassTableReflection.get_klass_for_table(rel) where_clause = " WHERE #{quoted_inheritance_column} = '#{rel_klass_name}'" ["SELECT", columns_select, "FROM #{rel} #{where_clause}"].join(" ") end
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 408 def get_leaves_relations(hierarchy) return [] if hierarchy.nil? || hierarchy.empty? head, hierarchy = hierarchy.first, hierarchy[1..(hierarchy.size)] if(head.is_a? Array) return (get_leaves_relations(head) + get_leaves_relations(hierarchy)).compact elsif(hierarchy.nil? || hierarchy.empty?) return [head] else return get_leaves_relations(hierarchy).compact end end
Nested list for the parent_relation
inheritance hierarchy Every descendant relation is presented as an array with relation's name as first element and the other elements are the relation's children presented in the same way as lists. For example: [[child_view1, [grandchild11,], [grandchild12]],
[child_view2, [...]
]
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 395 def get_view_hierarchy_for(parent_relation) hierarchy = [] children = query(<<-end_sql) SELECT parent_relation, child_aggregate_view, child_relation FROM updateable_views_inheritance WHERE parent_relation = '#{parent_relation}' end_sql children.each do |child| hierarchy << [child[1], *get_view_hierarchy_for(child[1])] end hierarchy end
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 342 def insert_returning_clause(parent_pk, child_pk, child_view) columns_cast_to_null = columns(child_view) .reject { |c| c.name == parent_pk} .map { |c| "CAST (NULL AS #{c.sql_type})" } .join(", ") "RETURNING #{child_pk}, #{columns_cast_to_null}" end
Creates rules for INSERT
, UPDATE
and DELETE
on the view
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 301 def make_child_view_updateable(parent_table, parent_columns, parent_pk, parent_pk_seq, child_view, child_columns, child_pk, child_table) # insert # NEW.#{parent_pk} can be explicitly specified and when it is null every call to it increments the sequence. # Setting the sequence to its value (explicitly supplied or the default) covers both cases. execute <<-end_sql CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_insert")} AS ON INSERT TO #{child_view} DO INSTEAD ( INSERT INTO #{parent_table} ( #{ [parent_pk, parent_columns].flatten.join(", ") } ) VALUES( DEFAULT #{ parent_columns.empty? ? '' : ' ,' + parent_columns.collect{ |col| "NEW." + col}.join(", ") } ) ; INSERT INTO #{child_table} ( #{ [child_pk, child_columns].flatten.join(",")} ) VALUES( currval('#{parent_pk_seq}') #{ child_columns.empty? ? '' : ' ,' + child_columns.collect{ |col| "NEW." + col}.join(", ") } ) #{insert_returning_clause(parent_pk, child_pk, child_view)} ) end_sql # delete execute <<-end_sql CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_delete")} AS ON DELETE TO #{child_view} DO INSTEAD DELETE FROM #{parent_table} WHERE #{parent_pk} = OLD.#{parent_pk} end_sql # update execute <<-end_sql CREATE OR REPLACE RULE #{quote_column_name("#{child_view}_update")} AS ON UPDATE TO #{child_view} DO INSTEAD ( #{ parent_columns.empty? ? '': "UPDATE #{parent_table} SET #{ parent_columns.collect{ |col| col + "= NEW." + col }.join(", ") } WHERE #{parent_pk} = OLD.#{parent_pk};"} #{ child_columns.empty? ? '': "UPDATE #{child_table} SET #{ child_columns.collect{ |col| col + " = NEW." + col }.join(", ") } WHERE #{child_pk} = OLD.#{parent_pk}" } ) end_sql end
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 375 def parent_table(relation) if table_exists?('updateable_views_inheritance') res = query(<<-end_sql, 'Parent relation')[0] SELECT parent_relation FROM updateable_views_inheritance WHERE child_aggregate_view = '#{relation}' end_sql res[0] if res end end
Set default values from the table columns for a view
# File lib/updateable_views_inheritance/postgresql_adapter.rb, line 351 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