Class: QuoteSql

Inherits:
Object
  • Object
show all
Extended by:
Quoting
Includes:
Formater
Defined in:
lib/quote_sql.rb,
lib/quote_sql/error.rb,
lib/quote_sql/quoter.rb,
lib/quote_sql/quoting.rb,
lib/quote_sql/version.rb,
lib/quote_sql/formater.rb,
lib/quote_sql/connector.rb,
lib/quote_sql/extension.rb,
lib/quote_sql/connector/active_record_base.rb

Overview

Tool to build and run SQL queries easier

Defined Under Namespace

Modules: Connector, Extension, Formater, Quoting Classes: Error, Quoter, Raw, Test

Constant Summary collapse

DATA_TYPES_RE = 
%w(
(?:small|big)(?:int|serial)
bit bool(?:ean)? box bytea cidr circle date
(?:date|int[48]|num|ts(?:tz)?)(?:multi)?range
macaddr8?
jsonb?
ts(?:query|vector)
float[48] (?:int|serial)[248]?
double_precision  inet
integer  line lseg   money   path pg_lsn
pg_snapshot point polygon real  text timestamptz timetz
txid_snapshot uuid xml
(bit_varying|varbit|character|char|character varying|varchar)(_\\(\\d+\\))?
(numeric|decimal)(_\\(\d+_\d+\\))?
interval(_(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|YEAR_TO_MONTH|DAY_TO_HOUR|DAY_TO_MINUTE|DAY_TO_SECOND|HOUR_TO_MINUTE|HOUR_TO_SECOND|MINUTE_TO_SECOND))?(_\\(\d+\\))?
time(stamp)?(_\\(\d+\\))?(_with(out)?_time_zone)?
).join("|")
CASTS = 
Regexp.new("__(#{DATA_TYPES_RE})$", "i")
MIXIN_RE = 
/(%\{?([a-z][a-z0-9_]*)}|%([a-z][a-z0-9_]*)\b)/im
VERSION = 
"0.0.4"

Constants included from Formater

Formater::PG_FORMAT_BIN

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Quoting

escape, escape_array, escape_regex

Methods included from Formater

#dsql, #to_formatted_sql

Constructor Details

#initialize(sql = nil) ⇒ QuoteSql

Returns a new instance of QuoteSql.



34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/quote_sql.rb', line 34

def initialize(sql = nil)
  @original = sql.respond_to?(:to_sql) ? sql.to_sql : sql.to_s
  @sql = @original.dup
  @quotes = {}
  @resolved = {}
  @binds = []

  @tables = {}
  @columns = {}
  @casts = {}
end

Instance Attribute Details

#binds ⇒ Object (readonly)

Returns the value of attribute binds.



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

def binds
  @binds
end

#columns(name = nil) ⇒ Object (readonly)

Returns the value of attribute columns.



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

def columns
  @columns
end

#original ⇒ Object (readonly)

Returns the value of attribute original.



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

def original
  @original
end

#quotes ⇒ Object (readonly)

Returns the value of attribute quotes.



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

def quotes
  @quotes
end

#sql ⇒ Object (readonly)

Returns the value of attribute sql.



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

def sql
  @sql
end

#tables ⇒ Object (readonly)

Returns the value of attribute tables.



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

def tables
  @tables
end

Class Method Details

.conn ⇒ Object

Raises:

  • (ArgumentError) 


26
27
28
 
# File 'lib/quote_sql.rb', line 26

def self.conn
  raise ArgumentError, "You need to define a database connection function"
end

.db_connector=(conn) ⇒ Object 



30
31
32
 
# File 'lib/quote_sql.rb', line 30

def self.db_connector=(conn)
  Connector.set(conn)
end

.test(which = :all) ⇒ Object 



225
226
227
228
229
230
231
232
 
# File 'lib/quote_sql.rb', line 225

def self.test(which = :all)
  require __dir__ + "/quote_sql/test.rb"
  if which == :all
    Test.new.all
  else
    Test.new.run(which)
  end
end

Instance Method Details

#casts(name = nil) ⇒ Object 



56
57
58
59
60
61
62
63
 
# File 'lib/quote_sql.rb', line 56

def casts(name = nil)
  unless rv = @casts[name&.to_sym]
    table = table(name) or return
    return unless table.respond_to? :columns
    rv = table.columns.to_h { [_1.name.to_sym, _1.sql_type] }
  end
  rv
end

#errors ⇒ Object 



138
139
140
141
142
143
144
 
# File 'lib/quote_sql.rb', line 138

def errors
  @quotes.to_h do |k, v|
    r = @resolved[k]
    next [nil, nil] if r.nil? or not r.is_a?(Exception)
    [k, { @quotes[k].inspect => v.inspect, exc: r, backtrace: r.backtrace }]
  end.compact
end

#errors? ⇒ Boolean

Returns:

  • (Boolean) 


146
147
148
 
# File 'lib/quote_sql.rb', line 146

def errors?
  @resolved.any? { _2.is_a? Exception }
end

#execute(*records, batch: 1000) ⇒ Object

Executes a prepared statement Processes in batches records returns the array of the results depending on RETURNING is in the query

execute([1, "a", true, nil], ...)

execute({ id: 1, text: "a", bool: true, know: nil}, ...)

execute([1, "a", true, nil], ... batch: 500)
# set the batch size of 500

execute([1, "a", true, nil], ... batch: falss)
# processes all at once



119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/quote_sql.rb', line 119

def execute(*records, batch: 1000)
  sql = "EXECUTE #{@prepare_name}(#{([email protected]).map { "$#{_1}" }.join(",")})"
  records.map! do |record|
    if record.is_a?(Hash)
      raise NotImplementedError, "record hash not yet implemented"
    else
      record = Array(record)
    end
    if @binds.length != record.length
      next RuntimeError.new("binds are not equal arguments, #{record.inspect}")
    end
    _exec(sql, record, prepare: false, async: false)
  end
end

#key_matches ⇒ Object 



152
153
154
155
156
157
158
159
160
161
 
# File 'lib/quote_sql.rb', line 152

def key_matches
  @sql.scan(MIXIN_RE).map do |full, *key|
    key = key.compact[0]
    if m = key.match(/^(.+)#{CASTS}/i)
      _, key, cast = m.to_a
    end
    has_quote = @quotes.key?(key.to_sym) || key.match?(/(table|columns)$/)
    [full, key, cast, has_quote]
  end
end

#mixin! ⇒ Object 



163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/quote_sql.rb', line 163

def mixin!
  unresolved = Set.new(key_matches.map(&:second))
  last_unresolved = Set.new
  loop do
    s = StringScanner.new(@sql)
    sql = ""
    key_matches.each do |key_match, key, cast, has_quote|
      s.scan_until(/(.*?)#{key_match}([a-z0-9_]*)/im)
      matched, pre, post = s.matched, s[1], s[2]
      if m = key.match(/^bind(\d+)?/im)
        if m[1].present?
          bind_num = m[1].to_i
          @binds[bind_num - 1] ||= cast
          raise "bind #{bind_num} already set to #{@binds[bind_num - 1]}" unless @binds[bind_num - 1] == cast
        else
          @binds << cast
          bind_num = @binds.length
        end

        matched = "#{pre}$#{bind_num}#{"::#{cast}" if cast.present?}#{post}"
      elsif has_quote
        quoted = quoter(key)
        unresolved.delete key
        if (i = quoted.scan MIXIN_RE).present?
          unresolved += i.map(&:last)
        end
        matched = "#{pre}#{quoted}#{post}"
      end
    rescue TypeError
    ensure
      sql << matched.to_s
    end
    @sql = sql + s.rest
    break if unresolved.empty?
    break if unresolved == last_unresolved
    last_unresolved = unresolved.dup
  end
  self
end

#prepare(name) ⇒ Object

Raises:

  • (ArguemntError) 


98
99
100
101
102
103
104
 
# File 'lib/quote_sql.rb', line 98

def prepare(name)
  sql = to_sql
  raise ArguemntError, "binds not all casted e.g. %bind__CAST" if @binds.reject.any?
  name = quote_column_name(name)
  _exec_query("PREPARE #{name} (#{@binds.join(',')}) AS #{sql}")
  @prepare_name = name
end

#quote(quotes = {}) ⇒ Object

Add quotes keys are symbolized



66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/quote_sql.rb', line 66

def quote(quotes = {})
  re = /(?:^|(.*)_)(table|columns|casts)$/i
  quotes.keys.grep(re).each do |quote|
    _, name, type = quote.to_s.match(re)&.to_a
    value = quotes.delete quote
    value = Raw.sql(value) if value.class.to_s == "Arel::Nodes::SqlLiteral"
    instance_variable_get(:"@#{type.sub(/s*$/,'s')}")[name&.to_sym] = value
  end
  @quotes.update quotes.transform_keys(&:to_sym)
  self
end

#quoter(key) ⇒ Object 



203
204
205
206
207
208
209
 
# File 'lib/quote_sql.rb', line 203

def quoter(key)
  quoter = @resolved[key.to_sym] = Quoter.new(self, key, @quotes[key.to_sym])
  quoter.to_sql
rescue TypeError => exc
  @resolved[key.to_sym] = exc
  raise exc
end

#reset ⇒ Object 



134
135
136
 
# File 'lib/quote_sql.rb', line 134

def reset
  @sql = @original
end

#result(binds = [], prepare: false, async: false) ⇒ Object Also known as: exec 



85
86
87
88
89
90
91
92
93
94
 
# File 'lib/quote_sql.rb', line 85

def result(binds = [], prepare: false, async: false)
  sql = to_sql
  if binds.present? and sql.scan(/(?<=\$)\d+/).map(&:to_i).max + 1 != binds.length
    raise ArgumentError, "Wrong number of binds"
  end
  _exec(sql, binds, prepare: false, async: false)
rescue => exc
  STDERR.puts exc.sql
  raise exc
end

#table(name = nil) ⇒ Object 



48
49
50
 
# File 'lib/quote_sql.rb', line 48

def table(name = nil)
  @tables[name&.to_sym].dup
end

#to_sql ⇒ Object

Raises:



78
79
80
81
82
83
 
# File 'lib/quote_sql.rb', line 78

def to_sql
  mixin!
  raise Error.new(self, errors) if errors?
  return Arel.sql @sql if defined? Arel
  @sql
end