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, temp_table_name: nil) ⇒ Object 



73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/umbrellio_utils/database.rb', line 73

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

  temp_table_name ||= :"temp_#{model.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:)

  DB.create_table(temp_table_name, unlogged: true) do
    primary_key.each do |field|
      type = model.db_schema[field][:db_type]
      column(field, type)
    end

    primary_key(primary_key)
  end

  insert_ds = dataset.select(*qualified_pk(model.table_name, primary_key))
  DB[temp_table_name].disable_insert_returning.insert(insert_ds)

  temp_table_name
end

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



25
26
27
28
29
30
31
32
 
# 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:)

  with_temp_table(dataset, **options) do |ids|
    rows = ids.map { |id| row(id.is_a?(Hash) ? id.values : [id]) }
    dataset.model.where(row(primary_key) => rows).reverse(row(primary_key)).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) ⇒ 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.

Parameters:

  • [Integer] (Hash) —

    a customizable set of options

  • [Array] (Hash) —

    a customizable set of options

  • [Symbol, (Hash) —

    a customizable set of options



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

def with_temp_table(
  dataset,
  page_size: 1_000,
  sleep: nil,
  primary_key: nil,
  temp_table_name: nil
)
  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
    DB.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