Module: JDBCHelper::SQL

Defined in:
lib/jdbc-helper/sql/sql.rb,
lib/jdbc-helper/sql/expression.rb

Overview

WARNING: Does not perform SQL.check to minimize performance overhead

Defined Under Namespace

Classes: CriterionExpression, Expression, NotNullExpression, ScalarExpression

Class Method Summary collapse

Class Method Details

.check(expr, is_name = false) ⇒ Object

FIXME: Naive protection for SQL Injection TODO: check caching?

Raises:

  • (ArgumentError) 


104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/jdbc-helper/sql/sql.rb', line 104

def self.check expr, is_name = false
  return nil if expr.nil?

  tag = is_name ? 'Object name' : 'Expression'
  test = expr.gsub(/'[^']*'/, '').gsub(/`[^`]*`/, '').gsub(/"[^"]*"/, '').strip
  raise ArgumentError.new("#{tag} cannot contain (unquoted) semi-colons: #{expr}") if test.include?(';')
  raise ArgumentError.new("#{tag} cannot contain (unquoted) comments: #{expr}") if test.match(%r{--|/\*|\*/})
  raise ArgumentError.new("Unclosed quotation mark: #{expr}") if test.match(/['"`]/)
  raise ArgumentError.new("#{tag} is blank") if test.empty?

  if is_name
    raise ArgumentError.new(
      "#{tag} cannot contain (unquoted) parentheses: #{expr}") if test.match(%r{\(|\)})
  end

  return expr
end

.count(table, conds = nil) ⇒ Object

Generates count SQL with the given conditions



93
94
95
 
# File 'lib/jdbc-helper/sql/sql.rb', line 93

def self.count table, conds = nil
  check "select count(*) from #{table} #{where_internal conds}".strip
end

.delete(table, conds = nil) ⇒ Object

Generates delete SQL with the given conditions



98
99
100
 
# File 'lib/jdbc-helper/sql/sql.rb', line 98

def self.delete table, conds = nil
  check "delete from #{table} #{where_internal conds}".strip
end

.expr(sql) ⇒ JDBCHelper::SQL::Expression

Generate SQL snippet, prevents the string from being quoted.

Parameters:

  • SQL (String) —

    snippet

Returns:

Since:

  • 0.7.0



12
13
14
 
# File 'lib/jdbc-helper/sql/expression.rb', line 12

def self.expr sql
  ScalarExpression.new sql
end

.ge(v) ⇒ JDBCHelper::SQL::Expression

Greater-than-or-equal-to expression for where clauses

Returns:

Since:

  • 0.7.0



49
50
51
 
# File 'lib/jdbc-helper/sql/expression.rb', line 49

def self.ge v
  CriterionExpression.new '>=', v
end

.gt(v) ⇒ JDBCHelper::SQL::Expression

Greater-than expression for where clauses

Returns:

Since:

  • 0.7.0



28
29
30
 
# File 'lib/jdbc-helper/sql/expression.rb', line 28

def self.gt v
  CriterionExpression.new '>', v
end

.insert(table, data_hash) ⇒ Object

Generates insert SQL with hash



60
61
62
 
# File 'lib/jdbc-helper/sql/sql.rb', line 60

def self.insert table, data_hash
  insert_internal 'insert', table, data_hash
end

.insert_ignore(table, data_hash) ⇒ Object

Generates insert ignore SQL (Non-standard syntax)



65
66
67
 
# File 'lib/jdbc-helper/sql/sql.rb', line 65

def self.insert_ignore table, data_hash
  insert_internal 'insert ignore', table, data_hash
end

.le(v) ⇒ JDBCHelper::SQL::Expression

Less-than-or-equal-to expression for where clauses

Returns:

Since:

  • 0.7.0



42
43
44
 
# File 'lib/jdbc-helper/sql/expression.rb', line 42

def self.le v
  CriterionExpression.new '<=', v
end

.like(v) ⇒ JDBCHelper::SQL::Expression

Like expression for where clauses

Returns:

Raises:

  • (ArgumentError)

Since:

  • 0.7.0



63
64
65
66
 
# File 'lib/jdbc-helper/sql/expression.rb', line 63

def self.like v
  raise ArgumentError.new('Like expression must be given as a String') unless v.is_a?(String)
  CriterionExpression.new 'like', v
end

.lt(v) ⇒ JDBCHelper::SQL::Expression

Less-than expression for where clauses

Returns:

Since:

  • 0.7.0



35
36
37
 
# File 'lib/jdbc-helper/sql/expression.rb', line 35

def self.lt v
  CriterionExpression.new '<', v
end

.ne(v) ⇒ JDBCHelper::SQL::Expression

Not-equal expression for where clauses

Returns:

Since:

  • 0.7.0



56
57
58
 
# File 'lib/jdbc-helper/sql/expression.rb', line 56

def self.ne v
  CriterionExpression.new '<>', v
end

.not_like(v) ⇒ JDBCHelper::SQL::Expression

“Not like” expression for where clauses

Returns:

Raises:

  • (ArgumentError)

Since:

  • 0.7.0



71
72
73
74
 
# File 'lib/jdbc-helper/sql/expression.rb', line 71

def self.not_like v
  raise ArgumentError.new('Like expression must be given as a String') unless v.is_a?(String)
  CriterionExpression.new 'not like', v
end

.not_nil ⇒ JDBCHelper::SQL::Expression Also known as: not_null

“is not null” expression for where clauses

Returns:



18
19
20
 
# File 'lib/jdbc-helper/sql/expression.rb', line 18

def self.not_nil
  NotNullExpression.singleton
end

.order(*criteria) ⇒ Object

Generates SQL order by cluase with the given conditions.



51
52
53
54
 
# File 'lib/jdbc-helper/sql/sql.rb', line 51

def self.order *criteria
  str = criteria.map(&:to_s).reject(&:empty?).join(', ')
  str.empty? ? str : check('order by ' + str)
end

.replace(table, data_hash) ⇒ Object

Generates replace SQL (Non-standard syntax)



70
71
72
 
# File 'lib/jdbc-helper/sql/sql.rb', line 70

def self.replace table, data_hash
  insert_internal 'replace', table, data_hash
end

.select(table, opts = {}) ⇒ Object

Generates select SQL with the given conditions



83
84
85
86
87
88
89
90
 
# File 'lib/jdbc-helper/sql/sql.rb', line 83

def self.select table, opts = {}
  opts = opts.reject { |k, v| v.nil? }
  check [
    "select #{opts.fetch(:select, ['*']).join(', ')} from #{table}", 
    where_internal(opts.fetch(:where, {})),
    order(opts.fetch(:order, []).join(', '))
  ].reject(&:empty?).join(' ')
end

.update(table, data_hash, where) ⇒ Object

Generates update SQL with hash. :where element of the given hash is taken out to generate where clause.



76
77
78
79
80
 
# File 'lib/jdbc-helper/sql/sql.rb', line 76

def self.update table, data_hash, where
  where_clause = where_internal where
  updates = data_hash.map { |k, v| "#{k} = #{value v}" }.join(', ')
  check "update #{table} set #{updates} #{where_clause}".strip
end

.value(data) ⇒ Object

Formats the given data so that it can be injected into SQL



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/jdbc-helper/sql/sql.rb', line 23

def self.value data
  case data
  when BigDecimal
    data.to_s("F")
  when Numeric
    data
  when String, Symbol
    "'#{esc data}'"
  when NilClass
    'null'
  when JDBCHelper::SQL::ScalarExpression
    data.to_s
  else
    raise NotImplementedError.new("Unsupported datatype: #{data.class}")
  end
end

.where(*conds) ⇒ Object

Generates SQL where cluase with the given conditions. Parameter can be either Hash of String.



42
43
44
45
 
# File 'lib/jdbc-helper/sql/sql.rb', line 42

def self.where *conds
  where_clause = where_internal conds
  where_clause.empty? ? where_clause : check(where_clause)
end

.where_prepared(*conds) ⇒ Object 



47
48
 
# File 'lib/jdbc-helper/sql/sql.rb', line 47

def self.where_prepared *conds
end