Module: UmbrellioUtils::SQL

Extended by:
SQL
Included in:
SQL
Defined in:
lib/umbrellio_utils/sql.rb

Constant Summary collapse

UniqueConstraintViolation = 
Sequel::UniqueConstraintViolation

Instance Method Summary collapse

Instance Method Details

#[](*args) ⇒ Object 



9
10
11
 
# File 'lib/umbrellio_utils/sql.rb', line 9

def [](*args)
  Sequel[*args]
end

#abs(expr) ⇒ Object 



94
95
96
 
# File 'lib/umbrellio_utils/sql.rb', line 94

def abs(expr)
  func(:abs, expr)
end

#and(*conditions) ⇒ Object 



38
39
40
 
# File 'lib/umbrellio_utils/sql.rb', line 38

def and(*conditions)
  Sequel.&(*Array(conditions.flatten.presence || true))
end

#avg(expr) ⇒ Object 



78
79
80
 
# File 'lib/umbrellio_utils/sql.rb', line 78

def avg(expr)
  func(:avg, expr)
end

#case ⇒ Object 



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

def case(...)
  Sequel.case(...)
end

#cast ⇒ Object 



17
18
19
 
# File 'lib/umbrellio_utils/sql.rb', line 17

def cast(...)
  Sequel.cast(...)
end

#ch_count(*args) ⇒ Object 



74
75
76
 
# File 'lib/umbrellio_utils/sql.rb', line 74

def ch_count(*args)
  Sequel.function(:count, *args)
end

#ch_median(expr) ⇒ Object 



90
91
92
 
# File 'lib/umbrellio_utils/sql.rb', line 90

def ch_median(expr)
  func(:median, expr)
end

#ch_time_range(range) ⇒ Object 



135
136
137
 
# File 'lib/umbrellio_utils/sql.rb', line 135

def ch_time_range(range)
  Range.new(ch_timestamp(range.begin), ch_timestamp(range.end), range.exclude_end?)
end

#ch_timestamp(time) ⇒ Object 



126
127
128
 
# File 'lib/umbrellio_utils/sql.rb', line 126

def ch_timestamp(time)
  time&.strftime("%F %T.%6N")
end

#ch_timestamp_expr(time) ⇒ Object 



130
131
132
133
 
# File 'lib/umbrellio_utils/sql.rb', line 130

def ch_timestamp_expr(time)
  time = Time.zone.parse(time) if time.is_a?(String)
  func(:toDateTime64, Sequel[ch_timestamp(time)], 6)
end

#coalesce(*exprs) ⇒ Object 



98
99
100
 
# File 'lib/umbrellio_utils/sql.rb', line 98

def coalesce(*exprs)
  func(:coalesce, *exprs)
end

#coalesce0(*args) ⇒ Object 



102
103
104
 
# File 'lib/umbrellio_utils/sql.rb', line 102

def coalesce0(*args)
  coalesce(*args, 0)
end

#count(expr = nil) ⇒ Object 



70
71
72
 
# File 'lib/umbrellio_utils/sql.rb', line 70

def count(expr = nil)
  expr ? func(:count, expr) : func(:count).*
end

#date_trunc(truncate, expr) ⇒ Object 



122
123
124
 
# File 'lib/umbrellio_utils/sql.rb', line 122

def date_trunc(truncate, expr)
  func(:date_trunc, truncate.to_s, expr)
end

#distinct(expr) ⇒ Object 



110
111
112
 
# File 'lib/umbrellio_utils/sql.rb', line 110

def distinct(expr)
  func(:distinct, expr)
end

#empty_jsonb ⇒ Object 



147
148
149
 
# File 'lib/umbrellio_utils/sql.rb', line 147

def empty_jsonb
  Sequel.pg_jsonb({})
end

#false ⇒ Object 



182
183
184
 
# File 'lib/umbrellio_utils/sql.rb', line 182

def false
  Sequel.lit("false")
end

#func ⇒ Object 



13
14
15
 
# File 'lib/umbrellio_utils/sql.rb', line 13

def func(...)
  Sequel.function(...)
end

#greatest(*exprs) ⇒ Object 



118
119
120
 
# File 'lib/umbrellio_utils/sql.rb', line 118

def greatest(*exprs)
  func(:greatest, *exprs)
end

#intersect(left_expr, right_expr) ⇒ Object 



163
164
165
 
# File 'lib/umbrellio_utils/sql.rb', line 163

def intersect(left_expr, right_expr)
  Sequel.lit("SELECT ? INTERSECT SELECT ?", left_expr, right_expr)
end

#jsonb_dig(jsonb, path) ⇒ Object 



139
140
141
 
# File 'lib/umbrellio_utils/sql.rb', line 139

def jsonb_dig(jsonb, path)
  path.reduce(jsonb) { |acc, cur| acc[cur] }
end

#jsonb_typeof(jsonb) ⇒ Object 



143
144
145
 
# File 'lib/umbrellio_utils/sql.rb', line 143

def jsonb_typeof(jsonb)
  func(:jsonb_typeof, jsonb)
end

#jsonb_unsafe_set(jsonb, path, value) ⇒ Object

can rewrite scalar values



168
169
170
171
172
173
174
175
176
 
# File 'lib/umbrellio_utils/sql.rb', line 168

def jsonb_unsafe_set(jsonb, path, value)
  parent_path = path.slice(..-2)
  raw_parent = jsonb_dig(jsonb, parent_path)
  parent = jsonb_rewrite_scalar(raw_parent)
  last_path = path.slice(-1..-1)
  updated_parent = parent.set(last_path, value)
  result = self.case({ { value => nil } => parent }, updated_parent)
  jsonb.set(parent_path, result)
end

#least(*exprs) ⇒ Object 



114
115
116
 
# File 'lib/umbrellio_utils/sql.rb', line 114

def least(*exprs)
  func(:least, *exprs)
end

#map_to_expr(hash) ⇒ Object 



159
160
161
 
# File 'lib/umbrellio_utils/sql.rb', line 159

def map_to_expr(hash)
  hash.map { |aliaz, expr| expr.as(aliaz) }
end

#max(expr) ⇒ Object 



58
59
60
 
# File 'lib/umbrellio_utils/sql.rb', line 58

def max(expr)
  func(:max, expr)
end

#min(expr) ⇒ Object 



62
63
64
 
# File 'lib/umbrellio_utils/sql.rb', line 62

def min(expr)
  func(:min, expr)
end

#not ⇒ Object 



42
43
44
 
# File 'lib/umbrellio_utils/sql.rb', line 42

def not(...)
  Sequel.~(...)
end

#nullif(main_expr, checking_expr) ⇒ Object 



106
107
108
 
# File 'lib/umbrellio_utils/sql.rb', line 106

def nullif(main_expr, checking_expr)
  func(:nullif, main_expr, checking_expr)
end

#or(*conditions) ⇒ Object 



46
47
48
 
# File 'lib/umbrellio_utils/sql.rb', line 46

def or(*conditions)
  Sequel.|(*Array(conditions.flatten.presence || true))
end

#pg_jsonb ⇒ Object 



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

def pg_jsonb(...)
  Sequel.pg_jsonb(...)
end

#pg_median(expr) ⇒ Object 



86
87
88
 
# File 'lib/umbrellio_utils/sql.rb', line 86

def pg_median(expr)
  pg_percentile(expr, 0.5)
end

#pg_percentile(expr, percentile) ⇒ Object 



82
83
84
 
# File 'lib/umbrellio_utils/sql.rb', line 82

def pg_percentile(expr, percentile)
  func(:percentile_cont, percentile).within_group(expr)
end

#pg_range(from_value, to_value, **opts) ⇒ Object 



50
51
52
 
# File 'lib/umbrellio_utils/sql.rb', line 50

def pg_range(from_value, to_value, **opts)
  Sequel::Postgres::PGRange.new(from_value, to_value, **opts)
end

#pg_range_by_range(range) ⇒ Object 



54
55
56
 
# File 'lib/umbrellio_utils/sql.rb', line 54

def pg_range_by_range(range)
  Sequel::Postgres::PGRange.from_range(range)
end

#round(value, precision = 0) ⇒ Object 



151
152
153
 
# File 'lib/umbrellio_utils/sql.rb', line 151

def round(value, precision = 0)
  func(:round, value, precision)
end

#row(*values) ⇒ Object 



155
156
157
 
# File 'lib/umbrellio_utils/sql.rb', line 155

def row(*values)
  func(:row, *values)
end

#sum(expr) ⇒ Object 



66
67
68
 
# File 'lib/umbrellio_utils/sql.rb', line 66

def sum(expr)
  func(:sum, expr)
end

#to_timezone(zone, date) ⇒ Object 



33
34
35
36
 
# File 'lib/umbrellio_utils/sql.rb', line 33

def to_timezone(zone, date)
  utc_date = to_utc(date)
  func(:timezone, zone, cast(utc_date, :timestamptz))
end

#to_utc(date) ⇒ Object 



29
30
31
 
# File 'lib/umbrellio_utils/sql.rb', line 29

def to_utc(date)
  func(:timezone, "UTC", date)
end

#true ⇒ Object 



178
179
180
 
# File 'lib/umbrellio_utils/sql.rb', line 178

def true
  Sequel.lit("true")
end