module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements

Public Instance Methods

create_child(child_view, options) { |t| ... } click to toggle source

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
create_child_view(parent_table, child_view, child_table=nil) click to toggle source

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
create_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil) click to toggle source

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(child_view) click to toggle source

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
drop_view(name) click to toggle source

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
is_view?(name) click to toggle source

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
pk_and_sequence_for(relation) click to toggle source

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
primary_key(relation) click to toggle source
# 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() click to toggle source

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
rebuild_parent_and_children_views(parent_relation) click to toggle source

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
rebuild_single_table_inheritance_view(sti_aggregate_view, parent_relation, columns_for_view = nil) click to toggle source

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
remove_parent_and_children_views(parent_relation) click to toggle source

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
reset_pk_sequence!(table, pk = nil, sequence = nil) click to toggle source

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
supports_disable_referential_integrity?() click to toggle source

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
table_exists_with_updateable_views_inheritance_support?(name) click to toggle source
# 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) click to toggle source

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

create_system_table_records(parent_relation, child_aggregate_view, child_relation) click to toggle source
# 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
do_create_child_view(parent_table, parent_columns, parent_pk, child_view, child_columns, child_pk, child_table) click to toggle source
# 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
generate_single_table_inheritanche_union_clause(rel, column_names, conflict_column_names, columns_hash, quoted_inheritance_column) click to toggle source
# 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
get_leaves_relations(hierarchy) click to toggle source
# 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
get_view_hierarchy_for(parent_relation) click to toggle source

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
insert_returning_clause(parent_pk, child_pk, child_view) click to toggle source
# 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
make_child_view_updateable(parent_table, parent_columns, parent_pk, parent_pk_seq, child_view, child_columns, child_pk, child_table) click to toggle source

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
parent_table(relation) click to toggle source
# 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_defaults(view_name, table_name) click to toggle source

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