Module: Partitionable::ActsAsPartitionable::ClassMethods

Defined in:
lib/partitionable/acts_as_partitionable.rb

Instance Method Summary collapse

Instance Method Details

#acts_as_partitionable(options = {}) ⇒ Object 



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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 10

def acts_as_partitionable(options = {})
  cattr_accessor :index_fields
  cattr_accessor :logdate_attr
  self.index_fields = options[:index_fields]
  self.logdate_attr = options[:logdate_attr]

  def partition_name(month, year)
    formatted_month = sprintf('%02d', month.to_i)
    "#{self.table_name}_y#{year}m#{formatted_month}"
  end

  def create_partition(month, year)
    ActiveRecord::Base.connection.execute create_table_statement(month, year)
  end

  def create_table_statement(month, year)
    table = partition_name(month, year)
    index_name = "#{table}_#{index_fields.join('_')}"
    first_day_of_month = Date.civil(year, month, 1)
    first_day_next_month = (first_day_of_month + 1.month)
    <<-SQL
    CREATE TABLE #{table} (
        CHECK ( #{self.logdate_attr} >= DATE '#{first_day_of_month.to_s}' AND #{self.logdate_attr} < DATE '#{first_day_next_month.to_s}' )
    ) INHERITS (#{self.table_name});
    CREATE INDEX #{index_name} ON #{table} (#{index_fields.join(',')});
    SQL
  end

  def drop_partition(month, year)
    name = partition_name(month, year)
    index_name = "#{name}_#{index_fields.join('_')}"
    function_name = "#{name}_insert_trigger_function()"
    trigger_name = "#{name}_trigger"
    ActiveRecord::Base.connection.execute(
      <<-SQL
    DROP TABLE IF EXISTS #{name};
    DROP INDEX IF EXISTS #{index_name};
    DROP FUNCTION IF EXISTS #{function_name} CASCADE;
    DROP TRIGGER IF EXISTS #{trigger_name} ON #{self.table_name} CASCADE;
      SQL
    )
  end

  def trigger_statement months_and_years
    trigger_body = get_trigger_body(months_and_years)
    statement = ""
    statement += <<-SQL
    CREATE OR REPLACE FUNCTION #{self.table_name}_insert_trigger()
    RETURNS TRIGGER AS $$
    SQL

    statement += trigger_body
    statement += <<-SQL
    $$
    LANGUAGE plpgsql;

    DROP TRIGGER IF EXISTS insert_#{self.table_name}_trigger ON #{self.table_name};
    CREATE TRIGGER insert_#{self.table_name}_trigger
        BEFORE INSERT ON #{self.table_name}
        FOR EACH ROW EXECUTE PROCEDURE #{self.table_name}_insert_trigger();
    SQL
    statement
  end

  def get_trigger_body months_and_years

    statement = ""
    statement += <<-eos
    BEGIN
    eos

    months_and_years.each_with_index do |data, index|

      first_day_of_month = Date.civil(data[1].to_i, data[0].to_i, 1)
      first_day_next_month = (first_day_of_month + 1.month)
      if index == 0
        statement += <<-eos
        IF ( NEW.#{self.logdate_attr} >= DATE '#{first_day_of_month}' AND
             NEW.#{self.logdate_attr} < DATE '#{first_day_next_month}' ) THEN
            INSERT INTO #{partition_name(data[0], data[1])} VALUES (NEW.*);
        eos
      else
        statement += <<-eos
        ELSIF ( NEW.#{self.logdate_attr} >= DATE '#{first_day_of_month}' AND
             NEW.#{self.logdate_attr} < DATE '#{first_day_next_month}' ) THEN
            INSERT INTO #{partition_name(data[0], data[1])} VALUES (NEW.*);
        eos
      end
    end
    statement += <<-eos
        END IF;
        RETURN NULL;
    END;
    eos
  end

  def partition_exists?(month, year)
    ActiveRecord::Base.connection.data_source_exists? partition_name(month, year)
  end

  def update_trigger
    ActiveRecord::Base.connection.execute updated_trigger_statement
  end

  def updated_trigger_statement
    tables = ActiveRecord::Base.connection.tables.select{|t| t =~ /#{self.table_name}_y[0-9]{4}m[0-9]{2}/}
    months_and_years = tables.map {|t| [t.match(/m\K[0-9]{2}/)[0], t.match(/y\K[0-9]{4}/)[0]]}
    trigger_statement months_and_years.sort_by{|month, year| [year.to_i, month.to_i]}
  end

  include Partitionable::ActsAsPartitionable::LocalInstanceMethods
end

#create_partition(month, year) ⇒ Object 



21
22
23
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 21

def create_partition(month, year)
  ActiveRecord::Base.connection.execute create_table_statement(month, year)
end

#create_table_statement(month, year) ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 25

def create_table_statement(month, year)
  table = partition_name(month, year)
  index_name = "#{table}_#{index_fields.join('_')}"
  first_day_of_month = Date.civil(year, month, 1)
  first_day_next_month = (first_day_of_month + 1.month)
  <<-SQL
  CREATE TABLE #{table} (
      CHECK ( #{self.logdate_attr} >= DATE '#{first_day_of_month.to_s}' AND #{self.logdate_attr} < DATE '#{first_day_next_month.to_s}' )
  ) INHERITS (#{self.table_name});
  CREATE INDEX #{index_name} ON #{table} (#{index_fields.join(',')});
  SQL
end

#drop_partition(month, year) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 38

def drop_partition(month, year)
  name = partition_name(month, year)
  index_name = "#{name}_#{index_fields.join('_')}"
  function_name = "#{name}_insert_trigger_function()"
  trigger_name = "#{name}_trigger"
  ActiveRecord::Base.connection.execute(
    <<-SQL
  DROP TABLE IF EXISTS #{name};
  DROP INDEX IF EXISTS #{index_name};
  DROP FUNCTION IF EXISTS #{function_name} CASCADE;
  DROP TRIGGER IF EXISTS #{trigger_name} ON #{self.table_name} CASCADE;
    SQL
  )
end

#get_trigger_body(months_and_years) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 74

def get_trigger_body months_and_years

  statement = ""
  statement += <<-eos
  BEGIN
  eos

  months_and_years.each_with_index do |data, index|

    first_day_of_month = Date.civil(data[1].to_i, data[0].to_i, 1)
    first_day_next_month = (first_day_of_month + 1.month)
    if index == 0
      statement += <<-eos
      IF ( NEW.#{self.logdate_attr} >= DATE '#{first_day_of_month}' AND
           NEW.#{self.logdate_attr} < DATE '#{first_day_next_month}' ) THEN
          INSERT INTO #{partition_name(data[0], data[1])} VALUES (NEW.*);
      eos
    else
      statement += <<-eos
      ELSIF ( NEW.#{self.logdate_attr} >= DATE '#{first_day_of_month}' AND
           NEW.#{self.logdate_attr} < DATE '#{first_day_next_month}' ) THEN
          INSERT INTO #{partition_name(data[0], data[1])} VALUES (NEW.*);
      eos
    end
  end
  statement += <<-eos
      END IF;
      RETURN NULL;
  END;
  eos
end

#partition_exists?(month, year) ⇒ Boolean

Returns:

  • (Boolean) 


106
107
108
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 106

def partition_exists?(month, year)
  ActiveRecord::Base.connection.data_source_exists? partition_name(month, year)
end

#partition_name(month, year) ⇒ Object 



16
17
18
19
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 16

def partition_name(month, year)
  formatted_month = sprintf('%02d', month.to_i)
  "#{self.table_name}_y#{year}m#{formatted_month}"
end

#trigger_statement(months_and_years) ⇒ Object 



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

def trigger_statement months_and_years
  trigger_body = get_trigger_body(months_and_years)
  statement = ""
  statement += <<-SQL
  CREATE OR REPLACE FUNCTION #{self.table_name}_insert_trigger()
  RETURNS TRIGGER AS $$
  SQL

  statement += trigger_body
  statement += <<-SQL
  $$
  LANGUAGE plpgsql;

  DROP TRIGGER IF EXISTS insert_#{self.table_name}_trigger ON #{self.table_name};
  CREATE TRIGGER insert_#{self.table_name}_trigger
      BEFORE INSERT ON #{self.table_name}
      FOR EACH ROW EXECUTE PROCEDURE #{self.table_name}_insert_trigger();
  SQL
  statement
end

#update_trigger ⇒ Object 



110
111
112
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 110

def update_trigger
  ActiveRecord::Base.connection.execute updated_trigger_statement
end

#updated_trigger_statement ⇒ Object 



114
115
116
117
118
 
# File 'lib/partitionable/acts_as_partitionable.rb', line 114

def updated_trigger_statement
  tables = ActiveRecord::Base.connection.tables.select{|t| t =~ /#{self.table_name}_y[0-9]{4}m[0-9]{2}/}
  months_and_years = tables.map {|t| [t.match(/m\K[0-9]{2}/)[0], t.match(/y\K[0-9]{4}/)[0]]}
  trigger_statement months_and_years.sort_by{|month, year| [year.to_i, month.to_i]}
end