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
 
# File 'lib/mysql_framework/sql_query.rb', line 9

def initialize
  @sql = ''
  @params = []
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.



145
146
147
148
149
 
# File 'lib/mysql_framework/sql_query.rb', line 145

def and
  @sql += 'AND'

  self
end

#bulk_upsert(columns) ⇒ Object

This method is called to specify the columns to bulk upsert.



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

def bulk_upsert(columns)
  @sql += 'ON DUPLICATE KEY UPDATE '

  columns.each do |column|
    @sql += "#{column} = VALUES(#{column}), "
  end

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

  self
end

#bulk_values(bulk_values) ⇒ Object

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



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

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 



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

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



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

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.



127
128
129
130
131
132
 
# File 'lib/mysql_framework/sql_query.rb', line 127

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



206
207
208
209
210
 
# File 'lib/mysql_framework/sql_query.rb', line 206

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.



213
214
215
216
217
218
219
220
 
# File 'lib/mysql_framework/sql_query.rb', line 213

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

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

  self
end

#increment(values) ⇒ Object 



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

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



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

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.



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

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.



191
192
193
194
195
196
 
# File 'lib/mysql_framework/sql_query.rb', line 191

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



175
176
177
178
179
 
# File 'lib/mysql_framework/sql_query.rb', line 175

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

  self
end

#offset(offset) ⇒ Object

This method is called to add an offset to a query



182
183
184
185
186
187
188
 
# File 'lib/mysql_framework/sql_query.rb', line 182

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.



199
200
201
202
203
 
# File 'lib/mysql_framework/sql_query.rb', line 199

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

  self
end

#or ⇒ Object

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



152
153
154
155
156
 
# File 'lib/mysql_framework/sql_query.rb', line 152

def or
  @sql += 'OR'

  self
end

#order(*columns) ⇒ Object

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



159
160
161
162
163
 
# File 'lib/mysql_framework/sql_query.rb', line 159

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



166
167
168
169
170
171
172
 
# File 'lib/mysql_framework/sql_query.rb', line 166

def order_desc(*columns)
  order(*columns)

  @sql += ' DESC'

  self
end

#select(*columns) ⇒ Object

This method is called to start a select query



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

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

  self
end

#set(values) ⇒ Object

This method is called to specify the columns to update.



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

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.



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

def sql
  @sql.strip
end

#update(table, partition = nil) ⇒ Object

This method is called to start an update query



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

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.



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

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.



135
136
137
138
139
140
141
142
 
# File 'lib/mysql_framework/sql_query.rb', line 135

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

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

  self
end