Module: UmbrellioUtils::Database

Extended by:
Database
Included in:
Database
Defined in:
lib/umbrellio_utils/database.rb

Constant Summary collapse

HandledConstaintError = 
Class.new(StandardError)
InvalidPkError = 
Class.new(StandardError)

Instance Method Summary collapse

Instance Method Details

#create_temp_table(dataset, primary_key: nil, primary_key_types: nil, temp_table_name: nil) ⇒ Object

rubocop:enable Metrics/ParameterLists



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
 
# File 'lib/umbrellio_utils/database.rb', line 81

def create_temp_table(dataset, primary_key: nil, primary_key_types: nil, temp_table_name: nil)
  time = Time.current
  query_table_name = dataset&.model&.table_name

  temp_table_name ||= :"temp_#{query_table_name}_#{time.to_i}_#{time.nsec}"
  return temp_table_name if DB.table_exists?(temp_table_name)

  primary_key = primary_key_from(dataset, primary_key:)
  primary_key_types ||= primary_key.map { |x| dataset.model.db_schema[x][:db_type] }

  DB.create_table(temp_table_name, unlogged: true) do
    primary_key.each.with_index do |field, i|
      column(field, primary_key_types[i])
    end

    primary_key(primary_key)
  end

  unless dataset.nil?
    insert_ds = dataset.select(*qualified_pk(query_table_name, primary_key))
    DB[temp_table_name].disable_insert_returning.insert(insert_ds)
  end

  temp_table_name
end

#each_record(dataset, primary_key: nil, **options, &block) ⇒ Object 



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

def each_record(dataset, primary_key: nil, **options, &block)
  primary_key = primary_key_from(dataset, primary_key:)
  eager_tables = Array(options.delete(:eager_load))

  with_temp_table(dataset, primary_key:, **options) do |ids|
    rows = ids.map { |id| row(id.is_a?(Hash) ? id.values : [id]) }
    records = dataset.model
           .eager(eager_tables)
           .where(row(primary_key) => rows)
           .reverse(row(primary_key)).all
    records.each(&block)
  end
end

#get_violated_constraint_name(exception) ⇒ Object 



20
21
22
23
 
# File 'lib/umbrellio_utils/database.rb', line 20

def get_violated_constraint_name(exception)
  error = exception.wrapped_exception
  error.result.error_field(PG::Result::PG_DIAG_CONSTRAINT_NAME)
end

#handle_constraint_error(constraint_name) ⇒ Object 



10
11
12
13
14
15
16
17
18
 
# File 'lib/umbrellio_utils/database.rb', line 10

def handle_constraint_error(constraint_name, &)
  DB.transaction(savepoint: true, &)
rescue Sequel::UniqueConstraintViolation => e
  if constraint_name.to_s == get_violated_constraint_name(e)
    raise HandledConstaintError
  else
    raise e
  end
end

#with_temp_table(dataset, page_size: 1_000, sleep: nil, primary_key: nil, temp_table_name: nil, transaction: true) ⇒ Object

Iterates over a dataset and yields batches of primary keys. First, a temporary table is created and populated with dataset primary keys. After that, a batch of rows is deleted from the temp table on each iteration and gets yielded to the caller. rubocop:disable Metrics/ParameterLists

Parameters:

  • [Integer] (Hash) —

    a customizable set of options

  • [Array] (Hash) —

    a customizable set of options

  • [Symbol, (Hash) —

    a customizable set of options



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
 
# File 'lib/umbrellio_utils/database.rb', line 49

def with_temp_table(
  dataset,
  page_size: 1_000,
  sleep: nil,
  primary_key: nil,
  temp_table_name: nil,
  transaction: true
)
  primary_key = primary_key_from(dataset, primary_key:)
  sleep_interval = sleep_interval_from(sleep)

  temp_table_name = create_temp_table(
    dataset, primary_key:, temp_table_name: temp_table_name&.to_sym
  )

  pk_set = []

  loop do
    conditional_transaction(transaction) do
      pk_set = pop_next_pk_batch(temp_table_name, primary_key, page_size)
      yield(pk_set) if pk_set.any?
    end

    break if pk_set.empty?

    Kernel.sleep(sleep_interval) if sleep_interval.positive?
  end

  DB.drop_table(temp_table_name)
end