Module: Rdt::SqlTemplateHelpers

Included in:
Model
Defined in:
lib/rdt/sql_template_helpers.rb

Instance Method Summary collapse

Instance Method Details

#j(key) ⇒ Object

JSON SQL helpers



10
11
12
 
# File 'lib/rdt/sql_template_helpers.rb', line 10

def j(key)
  "body ->> '#{key}' #{key.underscore}"
end

#j_except(*keys) ⇒ Object 



22
23
24
 
# File 'lib/rdt/sql_template_helpers.rb', line 22

def j_except *keys
  "body - '#{keys.join("','")}'"
end

#j_numeric(key) ⇒ Object 



14
15
16
 
# File 'lib/rdt/sql_template_helpers.rb', line 14

def j_numeric(key)
  "(body ->> '#{key}')::numeric #{key.underscore}"
end

#j_numeric_comma(key) ⇒ Object 



18
19
20
 
# File 'lib/rdt/sql_template_helpers.rb', line 18

def j_numeric_comma(key)
  "REPLACE(REPLACE((body ->> '#{key}'),'.',''),',','.')::numeric #{key.underscore}"
end

#star(relation, *exclued_columns) ⇒ Object 



4
5
6
7
 
# File 'lib/rdt/sql_template_helpers.rb', line 4

def star relation, *exclued_columns
  columns = ActiveRecord::Base.connection.execute("select * from #{relation} limit 0").fields - exclued_columns.map(&:to_s)
  columns.map { |column| "#{relation}.#{column}" }.join(', ')
end

#x(key) ⇒ Object

XML SQL helpers



27
28
29
 
# File 'lib/rdt/sql_template_helpers.rb', line 27

def x(key)
  "(xpath('//cmd[@t=''#{key}'']/text()', body))[1]::text AS #{key.underscore}"
end

#x_date(key) ⇒ Object 



43
44
45
 
# File 'lib/rdt/sql_template_helpers.rb', line 43

def x_date(key)
  "TO_DATE((xpath('/xjx/cmd[@t=''#{key}'']/text()', body))[1]::text, 'DD/MM/YYYY') #{key.underscore}"
end

#x_except(*keys) ⇒ Object 



47
48
49
50
51
 
# File 'lib/rdt/sql_template_helpers.rb', line 47

def x_except *keys
  not_in_clause = keys.map { |key| "''#{key}''" }
  .join('or @t = ')
  "xpath('//cmd[not(@t = #{not_in_clause})]', body)"
end

#x_numeric(key) ⇒ Object 



31
32
33
34
35
36
37
 
# File 'lib/rdt/sql_template_helpers.rb', line 31

def x_numeric(key)
  # x_numeric was replacing '.' and ',' to '' and '.' to convert to numeric
  # which should be done by x_numeric_comma.
  puts "WARNING: x_numeric will not change , to . in a future version. Use x_numeric_comma instead."
  #"(xpath('//cmd[@t=''#{key}'']/text()', body))[1]::numeric #{key.underscore}"
  x_numeric_comma(key)
end

#x_numeric_comma(key) ⇒ Object 



39
40
41
 
# File 'lib/rdt/sql_template_helpers.rb', line 39

def x_numeric_comma(key)
  "REPLACE(REPLACE((xpath('/xjx/cmd[@t=''#{key}'']/text()', body))[1]::text, '.', ''), ',', '.')::numeric #{key.underscore}"
end