module ActsAsManyTrees::HierarchyTable

Constants

UPPER_BOUND

Public Class Methods

debug_tree(hierarchy_scope='') click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 139
def self.debug_tree(hierarchy_scope='')
  puts '======================'
  self.where(hierarchy_scope:hierarchy_scope).order([:ancestor_id,:generation]).each{|r| puts r.attributes}
  puts '**********************'
end
set_parent_of(item:,new_parent:,hierarchy_scope:'',existing_scope:'',clone_sub_tree:false,after_node:nil,before_node:nil) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 83
def self.set_parent_of(item:,new_parent:,hierarchy_scope:'',existing_scope:'',clone_sub_tree:false,after_node:nil,before_node:nil)
  if new_parent
    wrk_parent = self.find_by(descendant_id:new_parent.id,ancestor_id:new_parent.id,generation: 0,hierarchy_scope: hierarchy_scope) 
    unless wrk_parent
      position = ((after_this(nil,nil,hierarchy_scope)+before_this(nil,hierarchy_scope))/2.0).round(15)
      wrk_parent=self.create(descendant_id:new_parent.id,ancestor_id:new_parent.id,generation: 0,hierarchy_scope: hierarchy_scope,position: position)
    end
  end
  # puts 'ppppppppppp'
  # debug_tree('')
  # puts 'pppppppp'
  if item
    after_position  = after_this(wrk_parent,after_node,hierarchy_scope)
    before_position = before_this(before_node,hierarchy_scope)
    position = ((after_position+before_position)/2.0).round(15)
    wrk_item = self.find_by(descendant_id:item.id,ancestor_id:item.id,generation: 0,hierarchy_scope: hierarchy_scope)
    if wrk_item
      wrk_item.position = position
    else
      wrk_item=self.create(descendant_id:item.id,ancestor_id:item.id,generation: 0,hierarchy_scope: hierarchy_scope,position: position)
    end
    temp_name = SecureRandom.hex
    # BEWARE this works when added default trees and adding default trees to a new scope but it won't work when moving
    # items within a named tree or when adding items from one named scope to another
    # puts "hierarchy_scope #{hierarchy_scope} existing_scope #{existing_scope}"
    if (hierarchy_scope != existing_scope)
       # debug_tree(existing_scope)
       # debug_tree(hierarchy_scope)
       # debug_tree(temp_name)
      if clone_sub_tree
        clone_sub_tree(item:wrk_item,temp_name:temp_name,existing_name:existing_scope)
      else
        clone_item_only(item:wrk_item,new_parent:wrk_parent,hierarchy_scope:temp_name)
      end
      add_all_ancestors(wrk_item,wrk_parent,temp_name)
      # debug_tree(temp_name)
    else
      # debug_tree
      # debug_tree(temp_name)
      # # add_all_ancestors(wrk_item,wrk_parent,temp_name)
      # puts wrk_item.attributes
      # puts wrk_parent.attributes
      create_tree(wrk_item:wrk_item,wrk_parent:wrk_parent,temp_name:temp_name)
       # debug_tree(temp_name)
      # delete_item_ancestors(wrk_item)
      #where({descendant_id: wrk_item.descendant_id,hierarchy_scope: hierarchy_scope }).delete_all
      # delete_ancestors(wrk_item,wrk_item.hierarchy_scope)
      delete_ancestors_of_item_children(wrk_item,hierarchy_scope)
    end
    reset_descendant_position(wrk_item,before_position,temp_name)
    rename_tree(temp_name,hierarchy_scope)
    # debug_tree('')
    # puts "***************\n\n"
  end
end

Private Class Methods

add_all_ancestors(item,parent,temp_name) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 201
      def self.add_all_ancestors(item,parent,temp_name)
        sql=<<-SQL
        insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
        select a.ancestor_id,b.descendant_id,a.generation+b.generation+1,'#{temp_name}',b.position
          from #{table_name} a, #{table_name} b
          where a.descendant_id=#{parent.descendant_id}
          and b.ancestor_id=#{item.descendant_id}
          and b.hierarchy_scope = '#{temp_name}'
          and a.hierarchy_scope = '#{parent.hierarchy_scope}'
        SQL
        connection.execute(sql)
      end
after_this(wrk_parent,after_node,hierarchy_scope) click to toggle source

the new position is after the maximum of the after_node, the parent, the current maximum of all

# File lib/acts_as_many_trees/hierarchy_table.rb, line 147
def self.after_this(wrk_parent,after_node,hierarchy_scope)
  if after_node
    position = after_node.position(hierarchy_scope)
  elsif wrk_parent
    position = wrk_parent.position
  else
    position = self.where(hierarchy_scope: hierarchy_scope).maximum(:position) || 0
  end
  position
end
before_this(before_node,hierarchy_scope) click to toggle source

and before the minimum of the before_node, the parent’s next sibling or 10**20

# File lib/acts_as_many_trees/hierarchy_table.rb, line 159
def self.before_this(before_node,hierarchy_scope)
  if before_node
    position = before_node.position(hierarchy_scope)
  else
    position = UPPER_BOUND
  end
  position
end
clone_item_only(item:,new_parent:,hierarchy_scope:) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 168
      def self.clone_item_only(item:,new_parent:,hierarchy_scope:)
        sql=<<-SQL
        insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
        select b.ancestor_id,a.descendant_id,b.generation+1,'#{hierarchy_scope}',a.position
        from #{table_name} a,#{table_name} b 
        where b.descendant_id = #{new_parent.descendant_id}
        and a.descendant_id = #{item.descendant_id}
        and a.hierarchy_scope = '#{item.hierarchy_scope}'
        and a.hierarchy_scope = b.hierarchy_scope
        and a.generation = 0
        SQL
        connection.execute(sql)
      end
clone_sub_tree(item:,temp_name:,existing_name:'') click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 182
      def self.clone_sub_tree(item:,temp_name:,existing_name:'')
        sql=<<-SQL
        insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
        WITH RECURSIVE sub_trees(ancestor_id,descendant_id,generation,hierarchy_scope,position) AS
          (select ancestor_id,descendant_id,generation,'#{temp_name}',position from #{table_name}
           where descendant_id = #{item.descendant_id} 
           and hierarchy_scope = '#{existing_name}'
        UNION
           select s.ancestor_id,s.descendant_id,
           s.generation,'#{temp_name}',s.position
           from #{table_name} s, sub_trees st
           where s.ancestor_id = st.descendant_id
           and s.hierarchy_scope = '#{existing_name}'
           )
           select * from sub_trees;
        SQL
        connection.execute(sql)
      end
create_tree(wrk_item:,wrk_parent:,temp_name:) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 214
      def self.create_tree(wrk_item:,wrk_parent:,temp_name:)
        if wrk_parent
          # if existing_name != wrk_item.hierarchy_scope
          #   puts 'new hierarchy'
          #   sql=<<-SQL
          # insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
          # select a.ancestor_id,b.descendant_id,a.generation+b.generation+1,'#{temp_name}',b.position
          # from #{table_name} a, #{table_name} b
          # where a.descendant_id=#{wrk_parent.descendant_id}
          # and b.ancestor_id=#{wrk_item.ancestor_id}
          # and a.hierarchy_scope = b.hierarchy_scope
          # and a.hierarchy_scope = '#{wrk_item.hierarchy_scope}'
          # union
          # select c.ancestor_id,c.descendant_id,c.generation,'#{temp_name}',c.position
          # from #{table_name} c
          # where c.ancestor_id = #{wrk_item.descendant_id}
          # and c.hierarchy_scope = '#{wrk_item.hierarchy_scope}'
          # union
          # select c.ancestor_id,c.descendant_id,c.generation,'#{temp_name}',c.position
          # from #{table_name} c
          # where c.ancestor_id = #{wrk_item.descendant_id}
          # and c.ancestor_id <> c.descendant_id
          # and c.hierarchy_scope = '#{existing_name}'
          # union
          # select #{wrk_parent.descendant_id},c.descendant_id,#{wrk_parent.generation}+c.generation+1,'#{temp_name}',c.position
          # from #{table_name} c
          # where c.ancestor_id = #{wrk_item.descendant_id}
          # and c.ancestor_id <> c.descendant_id
          # and c.hierarchy_scope = '#{existing_name}'
          # /* add existing descendants of descendants in the new tree */
          # union
          # select c.ancestor_id,c.descendant_id,c.generation,'#{temp_name}',c.position
          # from #{table_name} c, #{table_name} d
          # where c.ancestor_id = d.descendant_id
          # and d.ancestor_id =#{wrk_item.descendant_id}
          # and d.hierarchy_scope = c.hierarchy_scope
          # and c.ancestor_id != c.descendant_id
          # and c.hierarchy_scope = '#{existing_name}'
          #   SQL
          # else
          sql=<<-SQL
        insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
        /* add self and descendants to the new parent */
        select a.ancestor_id,b.descendant_id,a.generation+b.generation+1,'#{temp_name}',b.position
          from #{table_name} a, #{table_name} b
          where a.descendant_id=#{wrk_parent.descendant_id}
          and b.ancestor_id=#{wrk_item.ancestor_id}
          and a.hierarchy_scope = b.hierarchy_scope
          and a.hierarchy_scope = '#{wrk_item.hierarchy_scope}'

        /* add existing descendants in the new tree */
        union
        select c.ancestor_id,c.descendant_id,c.generation,'#{temp_name}',c.position
          from #{table_name} c
          where c.ancestor_id = #{wrk_item.descendant_id}
          and c.hierarchy_scope = '#{wrk_item.hierarchy_scope}'

          SQL
        else
          sql=<<-SQL
        insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
        select c.ancestor_id,c.descendant_id,c.generation,'#{temp_name}',c.position
          from #{table_name} c
          where c.ancestor_id = #{wrk_item.descendant_id}
          and c.hierarchy_scope = '#{wrk_item.hierarchy_scope}'
          SQL
        end
        connection.execute(sql)
        #        puts '============='
        #        wrk_item.class.find_by_sql("select * from #{table_name} where hierarchy_scope='#{temp_name}'").each do | i |
        #          puts "a=#{i.ancestor_id} d=#{i.descendant_id} scope = #{i.hierarchy_scope} wrk.a = #{wrk_item.ancestor_id} wrk.b=#{wrk_item.descendant_id} parent_scope = #{wrk_parent.hierarchy_scope} parent.a =#{wrk_parent.ancestor_id} parent.d =#{wrk_parent.descendant_id}"
        #        end
      end
delete_ancestors(item,hierarchy_scope='') click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 311
def self.delete_ancestors(item,hierarchy_scope='')
  puts "#{item.id}"
  self.delete(descendant_id: item.id,hierarchy_scope: hierarchy_scope )
end
delete_ancestors_of_item_children(item,hierarchy_scope) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 316
      def self.delete_ancestors_of_item_children(item,hierarchy_scope)
        sql = <<-SQL
    delete from #{table_name} as p using #{table_name} as p1, #{table_name} as p2 
    where p.descendant_id = p1.descendant_id 
    and p.ancestor_id = p2.ancestor_id
    and p2.descendant_id = #{item.descendant_id}
    and p1.ancestor_id = p2.descendant_id
    and p2.hierarchy_scope = p1.hierarchy_scope
    and p.hierarchy_scope = p1.hierarchy_scope
    and p1.hierarchy_scope = '#{hierarchy_scope}'
        SQL
        connection.execute(sql)
      end
delete_item_ancestors(wrk_item) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 288
      def self.delete_item_ancestors(wrk_item)
        sql=<<-SQL
          delete from #{table_name}
          where hierarchy_scope='#{wrk_item.hierarchy_scope}'
          and descendant_id=#{wrk_item.descendant_id}
        SQL
        connection.execute(sql)
      end
rename_tree(old_name,new_name) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 297
      def self.rename_tree(old_name,new_name)
        sql=<<-SQL
          update #{table_name} h1
          set hierarchy_scope='#{new_name}'
          where hierarchy_scope='#{old_name}'
          and not exists(select h2.ancestor_id from #{table_name} h2
                         where h1.ancestor_id = h2.ancestor_id
                         and h1.descendant_id = h2.descendant_id
                         and h2.hierarchy_scope = '#{new_name}'
                         )
        SQL
        connection.execute(sql)
      end
reset_descendant_position(parent,before_position,hierarchy_scope='') click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 344
      def self.reset_descendant_position(parent,before_position,hierarchy_scope='')
        after_position = parent.position
        gap = before_position - after_position
        #        p "before position: #{before_position}, after_position: #{after_position} gap: #{gap}"
        #        sql = <<-SQL
        #        select ancestor_id,descendant_id,hierarchy_scope,(#{after_position} + (
        #        (CAST ((rank() over (partition by ancestor_id order by position)-1) AS numeric))
        #        /( CAST (count(*) over (partition by ancestor_id) AS numeric)) * #{gap})) as position
        #        from #{table_name}
        #        where ancestor_id=#{parent.descendant_id}
        #        and hierarchy_scope='#{hierarchy_scope}'
        #        SQL
        #        res = connection.execute(sql)
        #        res.each_row do |row|
        #          p row
        #        end
        sql = <<-SQL
        with new_position as (select ancestor_id,descendant_id,hierarchy_scope,(#{after_position} + ( 
        (CAST ((rank() over (partition by ancestor_id order by position)-1) AS numeric))
        /( CAST (count(*) over (partition by ancestor_id) AS numeric)) * #{gap})) as position
        from #{table_name} 
        where ancestor_id=#{parent.descendant_id}
        and hierarchy_scope='#{hierarchy_scope}'
        )
        update  
        #{table_name} as t 
        set position = new_position.position
        from new_position
        where t.descendant_id = new_position.descendant_id
        and t.hierarchy_scope = new_position.hierarchy_scope
        SQL
        connection.execute(sql)
        #        sql=<<-SQL
        #        select * from #{table_name} where hierarchy_scope='#{hierarchy_scope}' order by position
        #        SQL
        #        res = connection.execute(sql)
        #        res.each_row do |row|
        #          p row
        #        end
      end
set_new_ancestors_of_item_children(item,hierarchy_scope) click to toggle source
# File lib/acts_as_many_trees/hierarchy_table.rb, line 330
      def self.set_new_ancestors_of_item_children(item,hierarchy_scope)
        sql=<<-SQL
       insert into #{table_name}(ancestor_id,descendant_id,generation,hierarchy_scope,position)
       select it.ancestor_id,ct.descendant_id,it.generation+ct.generation,ct.hierarchy_scope,ct.position 
       from #{table_name} it 
       join #{table_name} ct 
       on ct.ancestor_id = it.descendant_id
       and ct.hierarchy_scope = it.hierarchy_scope
       where it.descendant_id=#{item.id}
       and it.hierarchy_scope = '#{hierarchy_scope}'
        SQL
        connection.execute(sql)
      end