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.



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

def and
  @sql += 'AND'

  self
end

#decrement(values) ⇒ Object 



89
90
91
92
93
94
95
96
97
 
# File 'lib/mysql_framework/sql_query.rb', line 89

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.



100
101
102
103
104
105
 
# File 'lib/mysql_framework/sql_query.rb', line 100

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



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

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.



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

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

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

  self
end

#increment(values) ⇒ Object 



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

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.



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

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



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

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

  self
end

#offset(offset) ⇒ Object

This method is called to add an offset to a query



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

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.



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

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.



125
126
127
128
129
 
# File 'lib/mysql_framework/sql_query.rb', line 125

def or
  @sql += 'OR'

  self
end

#order(*columns) ⇒ Object

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



132
133
134
135
136
 
# File 'lib/mysql_framework/sql_query.rb', line 132

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



139
140
141
142
143
144
145
 
# File 'lib/mysql_framework/sql_query.rb', line 139

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.



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

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.



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

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

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

  self
end