Class: MysqlFramework::SqlQuery

Inherits:
Object
  • Object
show all
Defined in:
lib/mysql_framework/sql_query.rb

Overview

This class is used to represent and build a sql query

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ SqlQuery

Returns a new instance of SqlQuery.



9
10
11
12
13
 
# File 'lib/mysql_framework/sql_query.rb', line 9

def initialize
  @sql = ''
  @params = []
  @lock = nil
end

Instance Attribute Details

#params ⇒ Object (readonly)

This method is called to get any params required to execute this query as a prepared statement.



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

def params
  @params
end

Instance Method Details

#and ⇒ Object

This method is called to add an ‘and` keyword to a query to provide additional where clauses.



142
143
144
145
146
 
# File 'lib/mysql_framework/sql_query.rb', line 142

def and
  @sql += 'AND'

  self
end

#bulk_values(bulk_values) ⇒ Object

This method is called to specify the values to bulk insert.



67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/mysql_framework/sql_query.rb', line 67

def bulk_values(bulk_values)
  @sql += ' VALUES'

  bulk_values.each do |values|
    @sql += "(#{values.map { '?' }.join(', ')}),"
    @params += values
  end

  @sql = @sql.chomp(',')

  self
end

#decrement(values) ⇒ Object 



104
105
106
107
108
109
110
111
112
 
# File 'lib/mysql_framework/sql_query.rb', line 104

def decrement(values)
  @sql += @sql.include?('SET') ? ', ' : ' SET '

  values.each { |key, by| @sql += "`#{key}` = `#{key}` - #{by}, " }

  @sql = @sql.chomp(', ')

  self
end

#delete ⇒ Object

This method is called to start a delete query



28
29
30
31
32
 
# File 'lib/mysql_framework/sql_query.rb', line 28

def delete
  @sql = 'DELETE'

  self
end

#from(table, partition = nil) ⇒ Object

This method is called to specify the table/partition a select/delete query is for.



115
116
117
118
119
120
 
# File 'lib/mysql_framework/sql_query.rb', line 115

def from(table, partition = nil)
  @sql += " FROM #{table}"
  @sql += " PARTITION (p#{partition})" unless partition.nil?

  self
end

#group_by(*columns) ⇒ Object

This method is called to add a ‘group by` statement to a query



203
204
205
206
207
 
# File 'lib/mysql_framework/sql_query.rb', line 203

def group_by(*columns)
  @sql += " GROUP BY #{columns.join(', ')}"

  self
end

#having(*conditions) ⇒ Object

This method is called to specify a having clause for a query.



210
211
212
213
214
215
216
217
 
# File 'lib/mysql_framework/sql_query.rb', line 210

def having(*conditions)
  @sql += ' HAVING' unless @sql.include?('HAVING')
  @sql += " (#{conditions.join(' AND ')}) "

  conditions.each { |condition| @params << condition.value }

  self
end

#increment(values) ⇒ Object 



94
95
96
97
98
99
100
101
102
 
# File 'lib/mysql_framework/sql_query.rb', line 94

def increment(values)
  @sql += @sql.include?('SET') ? ', ' : ' SET '

  values.each { |key, by| @sql += "`#{key}` = `#{key}` + #{by}, " }

  @sql = @sql.chomp(', ')

  self
end

#insert(table, partition = nil) ⇒ Object

This method is called to start an insert query



43
44
45
46
47
48
 
# File 'lib/mysql_framework/sql_query.rb', line 43

def insert(table, partition = nil)
  @sql += "INSERT INTO #{table}"
  @sql += " PARTITION (p#{partition})" unless partition.nil?

  self
end

#into(*columns) ⇒ Object

This method is called to specify the columns to insert into.



51
52
53
54
55
 
# File 'lib/mysql_framework/sql_query.rb', line 51

def into(*columns)
  @sql += " (#{columns.join(', ')})"

  self
end

#join(table, type: nil) ⇒ Object

This method is called to add a join statement to a query.



188
189
190
191
192
193
 
# File 'lib/mysql_framework/sql_query.rb', line 188

def join(table, type: nil)
  @sql += " #{type.upcase}" unless type.nil?
  @sql += " JOIN #{table}"

  self
end

#limit(count) ⇒ Object

This method is called to add a limit to a query



172
173
174
175
176
 
# File 'lib/mysql_framework/sql_query.rb', line 172

def limit(count)
  @sql += " LIMIT #{count}"

  self
end

#lock(condition = nil) ⇒ Object

This method allows you to add a pessimistic lock to the record. The default lock is ‘FOR UPDATE` If you require any custom lock, e.g. FOR SHARE, just pass that in as the condition query.lock(’FOR SHARE’)



223
224
225
226
227
228
 
# File 'lib/mysql_framework/sql_query.rb', line 223

def lock(condition = nil)
  raise 'This must be a SELECT query' unless @sql.start_with?('SELECT')

  @lock = ' ' + (condition || 'FOR UPDATE')
  self
end

#offset(offset) ⇒ Object

This method is called to add an offset to a query



179
180
181
182
183
184
185
 
# File 'lib/mysql_framework/sql_query.rb', line 179

def offset(offset)
  raise 'A limit clause must be supplied to use an offset' unless @sql.include?('LIMIT')

  @sql += " OFFSET #{offset}"

  self
end

#on(column_1, column_2) ⇒ Object

This method is called to add the ‘on` detail to a join statement.



196
197
198
199
200
 
# File 'lib/mysql_framework/sql_query.rb', line 196

def on(column_1, column_2)
  @sql += " ON #{column_1} = #{column_2}"

  self
end

#on_duplicate(update_values = {}) ⇒ Object

For insert queries if you need to handle that a primary key already exists and automatically do an update instead. If you do not pass in a hash specifying a column name and custom value for it. the column with the value specified in the insert. Otherwise any value will be interpreted literally via mysql. e.g. query.insert(‘users’) .into(‘id’, first_name’, ‘login_count’) .values(1, ‘Bob’, 1) .on_duplicate(

{
  first_name: nil,
  login_count: 'login_count + 5'
}

) This would first create a record like => ‘1, ’Bob’, 1`. The second time it would update it with => ‘’Bob’, 6` (Note the 1 is not used in the update)

Parameters:

  • update_values (Hash) (defaults to: {}) —

    key is a column name. A nil value will make the query update

Returns:

  • SqlQuery



248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/mysql_framework/sql_query.rb', line 248

def on_duplicate(update_values = {})
  raise 'This must be an INSERT query' unless @sql.start_with?('INSERT')

  duplicates = []
  update_values.each do |column, col_value|
    if col_value.nil?
      # value comes from what the INSERT intended
      updated_value = "#{column} = VALUES (#{column})"
    else
      # custom value specified by col_value
      updated_value = "#{column} = #{col_value}"
    end
    duplicates << updated_value
  end
  @dup_query = " ON DUPLICATE KEY UPDATE #{duplicates.join(', ')}"

  self
end

#or ⇒ Object

This method is called to add an ‘or` keyword to a query to provide alternate where clauses.



149
150
151
152
153
 
# File 'lib/mysql_framework/sql_query.rb', line 149

def or
  @sql += 'OR'

  self
end

#order(*columns) ⇒ Object

This method is called to add an ‘order by` statement to a query



156
157
158
159
160
 
# File 'lib/mysql_framework/sql_query.rb', line 156

def order(*columns)
  @sql += " ORDER BY #{columns.join(', ')}"

  self
end

#order_desc(*columns) ⇒ Object

This method is called to add an ‘order by … desc` statement to a query



163
164
165
166
167
168
169
 
# File 'lib/mysql_framework/sql_query.rb', line 163

def order_desc(*columns)
  order(*columns)

  @sql += ' DESC'

  self
end

#select(*columns) ⇒ Object

This method is called to start a select query



21
22
23
24
25
 
# File 'lib/mysql_framework/sql_query.rb', line 21

def select(*columns)
  @sql = "SELECT #{columns.join(', ')}"

  self
end

#set(values) ⇒ Object

This method is called to specify the columns to update.



81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/mysql_framework/sql_query.rb', line 81

def set(values)
  @sql += ' SET '

  values.each do |key, param|
    @sql += "`#{key}` = ?, "
    @params << param
  end

  @sql = @sql.chomp(', ')

  self
end

#sql ⇒ Object

This method is called to access the sql string for this query.



16
17
18
 
# File 'lib/mysql_framework/sql_query.rb', line 16

def sql
  (@sql + @lock.to_s + @dup_query.to_s).strip
end

#update(table, partition = nil) ⇒ Object

This method is called to start an update query



35
36
37
38
39
40
 
# File 'lib/mysql_framework/sql_query.rb', line 35

def update(table, partition = nil)
  @sql = "UPDATE #{table}"
  @sql += " PARTITION (p#{partition})" unless partition.nil?

  self
end

#values(*values) ⇒ Object

This method is called to specify the values to insert.



58
59
60
61
62
63
64
 
# File 'lib/mysql_framework/sql_query.rb', line 58

def values(*values)
  @sql += " VALUES (#{values.map { '?' }.join(', ')})"

  values.each { |value| @params << value }

  self
end

#where(*conditions) ⇒ Object

This method is called to specify a where clause for a query.

Condition values are added to @params unless the value is nil.



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
 
# File 'lib/mysql_framework/sql_query.rb', line 125

def where(*conditions)
  @sql += ' WHERE' unless @sql.include?('WHERE')
  @sql += " (#{conditions.join(' AND ')}) "

  conditions.each do |condition|
    next if condition.value.nil? && !skip_nil_validation?
    if condition.value.is_a?(Enumerable)
      @params.concat(condition.value)
    else
      @params << condition.value
    end
  end

  self
end