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.7"

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.



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

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.



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

def binds
  @binds
end

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

Returns the value of attribute columns.



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

def columns
  @columns
end

#original ⇒ Object (readonly)

Returns the value of attribute original.



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

def original
  @original
end

#quotes ⇒ Object (readonly)

Returns the value of attribute quotes.



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

def quotes
  @quotes
end

#sql ⇒ Object (readonly)

Returns the value of attribute sql.



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

def sql
  @sql
end

#tables ⇒ Object (readonly)

Returns the value of attribute tables.



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

def tables
  @tables
end

Class Method Details

.conn ⇒ Object

Raises:

  • (ArgumentError) 


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

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

.db_connector=(conn) ⇒ Object 



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

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

.test(which = :all) ⇒ Object 



228
229
230
231
232
233
234
235
236
237
238
 
# File 'lib/quote_sql.rb', line 228

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

#casts(name = nil) ⇒ Object 



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

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 



143
144
145
146
147
148
149
 
# File 'lib/quote_sql.rb', line 143

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) 


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

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



124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/quote_sql.rb', line 124

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 



157
158
159
160
161
162
163
 
# File 'lib/quote_sql.rb', line 157

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 



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
202
203
204
 
# File 'lib/quote_sql.rb', line 165

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) 


103
104
105
106
107
108
109
 
# File 'lib/quote_sql.rb', line 103

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



71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/quote_sql.rb', line 71

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, cast) ⇒ Object 



206
207
208
209
210
211
212
 
# File 'lib/quote_sql.rb', line 206

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 



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

def reset
  @sql = @original
end

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



90
91
92
93
94
95
96
97
98
99
 
# File 'lib/quote_sql.rb', line 90

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, async: false)
rescue => exc
  STDERR.puts exc.inspect, self.inspect
  raise exc
end

#table(name = nil) ⇒ Object 



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

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

#to_sql ⇒ Object

Raises:



83
84
85
86
87
88
 
# File 'lib/quote_sql.rb', line 83

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