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(
(?>character\\s+varying|bit\\s+varying|character|varbit|varchar|char|bit|interval)(?>\\s*\\(\\s*\\d+\\s*\\))?
(?>numeric|decimal)(?>\\s*\\(\\s*\\d+\\s*,\\s*\\d+\\s*\\))?
timestamptz timetz
time(?>stamp)?(?>\\s*\\(\\s*\\d+\\s*\\))?(?>\\s+with(?>out)?\\s+time\\s+zone)?
integer
(?>small|big)(?>int|serial)
bool(?>ean)? box bytea cidr circle date
(?:date|int[48]|num|ts(?:tz)?)(?:multi)?range
macaddr8?
ts(?>query|vector)
float[48]
(?:int|serial)[248]?
double\\s+precision
jsonb json
 inet
line lseg   money   path
pg_lsn pg_snapshot txid_snapshot
point polygon real text
uuid xml hstore
).join("|")
CASTS = 
Regexp.new("::(#{DATA_TYPES_RE})((?:\\s*\\[\\s*\\d?\\s*\\])*)", "i")
MIXIN_RE = 
/(%\{?([a-z][a-z0-9_]*)}|%([a-z][a-z0-9_]*)\b)/im
VERSION = 
"0.0.9"

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.



38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/quote_sql.rb', line 38

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.



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

def binds
  @binds
end

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

Returns the value of attribute columns.



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

def columns
  @columns
end

#original ⇒ Object (readonly)

Returns the value of attribute original.



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

def original
  @original
end

#quotes ⇒ Object (readonly)

Returns the value of attribute quotes.



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

def quotes
  @quotes
end

#sql ⇒ Object (readonly)

Returns the value of attribute sql.



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

def sql
  @sql
end

#tables ⇒ Object (readonly)

Returns the value of attribute tables.



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

def tables
  @tables
end

Class Method Details

.conn ⇒ Object

Raises:

  • (ArgumentError) 


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

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

.db_connector=(conn) ⇒ Object 



34
35
36
 
# File 'lib/quote_sql.rb', line 34

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

.test(which = :all) ⇒ Object 



259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/quote_sql.rb', line 259

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

Instance Method Details

#cast=(value) ⇒ Object Also known as: casts=

Raises:

  • (ArgumentError) 


80
81
82
83
84
85
 
# File 'lib/quote_sql.rb', line 80

def cast=(value)
  name, cast = value
  name = name&.to_sym
  raise ArgumentError unless cast.is_a?(Hash)
  (@casts[name] ||= {}).update(cast.transform_values { _1.is_a?(Hash) ? _1 : { sql_type: _1 } })
end

#casts(name = nil) ⇒ Object 



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

def casts(name = nil)
  @casts[name&.to_sym]
end

#column=(value) ⇒ Object Also known as: columns= 



72
73
74
75
76
 
# File 'lib/quote_sql.rb', line 72

def column=(value)
  name, column = value
  name = name&.to_sym
  @columns[name] = column
end

#errors ⇒ Object 



174
175
176
177
178
179
180
 
# File 'lib/quote_sql.rb', line 174

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) 


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

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



155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/quote_sql.rb', line 155

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)
  end
end

#key_matches ⇒ Object 



188
189
190
191
192
193
194
 
# File 'lib/quote_sql.rb', line 188

def key_matches
  @sql.scan(MIXIN_RE).map do |full, *key|
    key = key.compact[0]
    has_quote = @quotes.key?(key.to_sym) || key.match?(/(table|columns)$/)
    [full, key, has_quote]
  end
end

#mixin! ⇒ Object 



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/quote_sql.rb', line 196

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, has_quote|
      s.scan_until(/(.*?)#{key_match}(#{CASTS}?)/im)
      matched, pre, cast = 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}"
      # els
      if has_quote
        quoted = quoter(key, cast)
        unresolved.delete key
        if (i = quoted.scan MIXIN_RE).present?
          unresolved += i.map(&:last)
        end
        matched = "#{pre}#{quoted}#{cast}"
      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) 


134
135
136
137
138
139
140
 
# File 'lib/quote_sql.rb', line 134

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



99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/quote_sql.rb', line 99

def quote(quotes = {})
  quotes = quotes.transform_keys(&:to_sym)
  re = /(?:^|(.*)_)(table|columns|casts)$/i
  (table_name = quotes[:table_name].presence) and (@tables[:table] ||= table_name)
  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"
    send(:"#{type}=", [name, value])
    # instance_variable_get(:"@#{type.sub(/s*$/,'s')}")[name&.to_sym] = value
  end
  @quotes.update quotes
  self
end

#quoter(key, cast) ⇒ Object 



237
238
239
240
241
242
243
 
# File 'lib/quote_sql.rb', line 237

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

#reset ⇒ Object 



170
171
172
 
# File 'lib/quote_sql.rb', line 170

def reset
  @sql = @original
end

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



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

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

#table(name = nil) ⇒ Object 



52
53
54
55
 
# File 'lib/quote_sql.rb', line 52

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

#table=(value) ⇒ Object Also known as: tables= 



57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/quote_sql.rb', line 57

def table=(value)
  name, table = value
  name = name&.to_sym
  @tables[name] = table
  if table.respond_to?(:columns)
    @casts[name] = table.columns.to_h do |c|
      [c.name.to_sym, { sql_type: c.sql_type, default: (c.default || c.default_function rescue nil).present?, virtual: c.type == :virtual }]
    end if @casts[name].blank?
  elsif table.respond_to?(:column_names)
    @casts[name] = table.column_names.to_h { [_1.to_sym, nil] } if @casts[name].blank?
  end
end

#to_sql ⇒ Object

Raises:



114
115
116
117
118
119
 
# File 'lib/quote_sql.rb', line 114

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