Class: JDBCHelper::SQL

Inherits:
Object
  • Object
show all
Defined in:
lib/jdbc-helper/sql.rb

Overview

Class representing an SQL snippet. Also has many SQL generator class methods.

Direct Known Subclasses

SQLPrepared

Defined Under Namespace

Classes: NotNilClass

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.check(expr, is_name = false) ⇒ Object

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

Raises:

  • (ArgumentError) 


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

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



96
97
98
 
# File 'lib/jdbc-helper/sql.rb', line 96

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



101
102
103
 
# File 'lib/jdbc-helper/sql.rb', line 101

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

.insert(table, data_hash) ⇒ Object

Generates insert SQL with hash



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

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)



68
69
70
 
# File 'lib/jdbc-helper/sql.rb', line 68

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

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

Returns NotNilClass singleton object

Returns:



20
21
22
 
# File 'lib/jdbc-helper/sql.rb', line 20

def self.not_nil
	NotNilClass.singleton
end

.order(*criteria) ⇒ Object

Generates SQL order by cluase with the given conditions.



54
55
56
57
 
# File 'lib/jdbc-helper/sql.rb', line 54

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)



73
74
75
 
# File 'lib/jdbc-helper/sql.rb', line 73

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

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

Generates select SQL with the given conditions



86
87
88
89
90
91
92
93
 
# File 'lib/jdbc-helper/sql.rb', line 86

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.



79
80
81
82
83
 
# File 'lib/jdbc-helper/sql.rb', line 79

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



28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'lib/jdbc-helper/sql.rb', line 28

def self.value data
	case data
	when NilClass
		'null'
	when Fixnum, Bignum, Float
		data
	when JDBCHelper::SQL
		data.to_s
	when String
		"'#{esc data}'"
	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.



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

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

.where_prepared(*conds) ⇒ Object 



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

def self.where_prepared *conds
end

Instance Method Details

#==(other) ⇒ Object 



129
130
131
 
# File 'lib/jdbc-helper/sql.rb', line 129

def == other
  self.to_s == other.to_s
end

#eql?(other) ⇒ Boolean

Returns:

  • (Boolean) 


133
134
135
 
# File 'lib/jdbc-helper/sql.rb', line 133

def eql? other
  self.class == other.class && self.to_s == other.to_s
end

#to_s ⇒ Object 



125
126
127
 
# File 'lib/jdbc-helper/sql.rb', line 125

def to_s
	@expr
end