Module: SQLHelper

Defined in:
lib/sql_helper.rb,
lib/sql_helper/version.rb

Constant Summary collapse

VERSION = 
"0.1.1"

Class Method Summary collapse

Class Method Details

.check(expr) ⇒ Object

A naive check

Raises:

  • (SyntaxError) 


12
13
14
15
16
17
18
19
20
21
22
 
# File 'lib/sql_helper.rb', line 12

def check expr
  expr = expr.to_s
  test = expr.to_s.gsub(/(['`"]).*?\1/, '').
                   gsub(%r{/\*.*?\*/}, '').
                   strip
  raise SyntaxError.new("cannot contain unquoted semi-colons: #{expr}") if test.include?(';')
  raise SyntaxError.new("cannot contain unquoted comments: #{expr}") if test.match(%r{--|/\*|\*/})
  raise SyntaxError.new("unclosed quotation mark: #{expr}") if test.match(/['"`]/)
  raise SyntaxError.new("empty expression") if expr.strip.empty?
  expr
end

.count(args) ⇒ Object 



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

def count args
  check_keys args, Set[:prepared, :where, :table]
  select args.merge(:project => 'count(*)')
end

.delete(args) ⇒ Object 



53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/sql_helper.rb', line 53

def delete args
  check_keys args, Set[:table, :where, :prepared]

  wc, *wp = where_internal args[:prepared], args[:where]
  sql = "delete from #{args.fetch :table} #{wc}".strip
  if args[:prepared]
    [sql, *wp]
  else
    sql
  end
end

.escape(arg) ⇒ Object 



7
8
9
 
# File 'lib/sql_helper.rb', line 7

def escape arg
  arg.to_s.gsub("'", "''")
end

.insert(arg) ⇒ Object 



41
42
43
 
# File 'lib/sql_helper.rb', line 41

def insert arg
  insert_internal 'insert into', arg
end

.insert_ignore(arg) ⇒ Object 



45
46
47
 
# File 'lib/sql_helper.rb', line 45

def insert_ignore arg
  insert_internal 'insert ignore into', arg
end

.limit(*args) ⇒ Object 



135
136
137
138
139
140
141
142
143
144
145
146
147
148
 
# File 'lib/sql_helper.rb', line 135

def limit *args
  limit, offset = args.reverse.map { |e|
    s = e.to_s.strip
    s.empty? ? nil : s
  }
  arg = arg.to_s.strip
  if offset
    check "limit #{offset}, #{limit}"
  elsif limit
    check "limit #{limit}"
  else
    ''
  end
end

.order(*args) ⇒ Object 



126
127
128
129
130
131
132
133
 
# File 'lib/sql_helper.rb', line 126

def order *args
  args = args.compact.map(&:to_s).map(&:strip).reject(&:empty?)
  if args.empty?
    ''
  else
    check "order by #{args.join ', '}"
  end
end

.project(*args) ⇒ Object 



117
118
119
120
121
122
123
124
 
# File 'lib/sql_helper.rb', line 117

def project *args
  args = args.compact.map(&:to_s).map(&:strip).reject(&:empty?)
  if args.empty?
    '*'
  else
    check args.join ', '
  end
end

.quote(arg) ⇒ Object 



24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/sql_helper.rb', line 24

def quote arg
  case arg
  when String, Symbol
    "'#{arg.to_s.gsub "'", "''"}'"
  when BigDecimal
    arg.to_s('F')
  when nil
    'null'
  else
    if expr?(arg)
      arg.values.first
    else
      arg.to_s
    end
  end
end

.replace(arg) ⇒ Object 



49
50
51
 
# File 'lib/sql_helper.rb', line 49

def replace arg
  insert_internal 'replace into', arg
end

.select(args) ⇒ Object 



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/sql_helper.rb', line 98

def select args
  check_keys args, Set[:prepared, :project, :where, :order, :limit, :top, :table]

  top       = args[:top] ? "top #{args[:top]}" : ''
  project   = project(*args[:project])
  where,
    *params = args[:where] ? where_internal(args[:prepared], args[:where]) : ['']
  order     = order(*args[:order])
  limit     = limit(*args[:limit])

  sql = ['select', top, project, 'from', args.fetch(:table),
         where, order, limit].reject(&:empty?).join(' ')
  if args[:prepared]
    [ sql, *params ]
  else
    sql
  end
end

.update(args) ⇒ Object 



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/sql_helper.rb', line 65

def update args
  check_keys args, Set[:prepared, :where, :table, :data]
  table    = args.fetch(:table)
  data     = args.fetch(:data)
  prepared = args[:prepared]
  where,
    *wbind = where_internal(args[:prepared], args[:where])
  bind     = []
  vals     = data.map { |k, v|
    if prepared
      if expr?(v)
        [k, v.values.first].join(' = ')
      else
        bind << v
        "#{k} = ?"
      end
    else
      [k, quote(v)].join(' = ')
    end
  }
  sql = "update #{check table} set #{vals.join ', '} #{where}".strip
  if prepared
    [sql] + bind + wbind
  else
    sql
  end
end

.where(*conds) ⇒ Object 



150
151
152
 
# File 'lib/sql_helper.rb', line 150

def where *conds
  where_internal false, conds
end

.where_prepared(*conds) ⇒ Object 



154
155
156
 
# File 'lib/sql_helper.rb', line 154

def where_prepared *conds
  where_internal true, conds
end