Module: UmbrellioUtils::Database

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

Defined Under Namespace

Classes: HandledConstaintError, InvalidPkError

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



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

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 



28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/umbrellio_utils/database.rb', line 28

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 



23
24
25
26
 
# File 'lib/umbrellio_utils/database.rb', line 23

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 



13
14
15
16
17
18
19
20
21
 
# File 'lib/umbrellio_utils/database.rb', line 13

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



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

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