Module: UmbrellioUtils::Database

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

Defined Under Namespace

Classes: HandledConstaintError, InvalidPkError

Constant Summary collapse

DEFAULT_ORDER = 
:desc

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



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

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 



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

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

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

#get_violated_constraint_name(exception) ⇒ Object 



25
26
27
28
 
# File 'lib/umbrellio_utils/database.rb', line 25

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 



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

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, order: DEFAULT_ORDER) ⇒ 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

  • [Symbol] (Hash) —

    a customizable set of options

Raises:

  • (ArgumentError) 


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

def with_temp_table(
  dataset,
  page_size: 1_000,
  sleep: nil,
  primary_key: nil,
  temp_table_name: nil,
  transaction: true,
  order: DEFAULT_ORDER
)
  raise ArgumentError, "invalid order: #{order.inspect} (expected :asc or :desc)" \
    unless %i[asc desc].include?(order)

  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, order)
      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