class MysqlFramework::SqlQuery

This class is used to represent and build a sql query

Attributes

params[R]

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

Public Class Methods

new() click to toggle source
# File lib/mysql_framework/sql_query.rb, line 9
def initialize
  @sql = ''
  @params = []
  @lock = nil
end

Public Instance Methods

and() click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 142
def and
  @sql += 'AND'

  self
end
bulk_values(bulk_values) click to toggle source

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

# 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) click to toggle source
# 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() click to toggle source

This method is called to start a delete query

# File lib/mysql_framework/sql_query.rb, line 28
def delete
  @sql = 'DELETE'

  self
end
from(table, partition = nil) click to toggle source

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

# 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) click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 203
def group_by(*columns)
  @sql += " GROUP BY #{columns.join(', ')}"

  self
end
having(*conditions) click to toggle source

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

# 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) click to toggle source
# 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) click to toggle source

This method is called to start an insert query

# 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) click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 51
def into(*columns)
  @sql += " (#{columns.join(', ')})"

  self
end
join(table, type: nil) click to toggle source

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

# 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) click to toggle source

This method is called to add a limit to a query

# File lib/mysql_framework/sql_query.rb, line 172
def limit(count)
  @sql += " LIMIT #{count}"

  self
end
lock(condition = nil) click to toggle source

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')

# 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) click to toggle source

This method is called to add an offset to a query

# 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) click to toggle source

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

# 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 = {}) click to toggle source

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. @param update_values [Hash] key is a column name. A nil value will make the query update the column with the value specified in the insert. Otherwise any value will be interpreted literally via mysql. @return SqlQuery 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)

# 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() click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 149
def or
  @sql += 'OR'

  self
end
order(*columns) click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 156
def order(*columns)
  @sql += " ORDER BY #{columns.join(', ')}"

  self
end
order_desc(*columns) click to toggle source

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

# File lib/mysql_framework/sql_query.rb, line 163
def order_desc(*columns)
  order(*columns)

  @sql += ' DESC'

  self
end
select(*columns) click to toggle source

This method is called to start a select query

# File lib/mysql_framework/sql_query.rb, line 21
def select(*columns)
  @sql = "SELECT #{columns.join(', ')}"

  self
end
set(values) click to toggle source

This method is called to specify the columns to update.

# 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() click to toggle source

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

# 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) click to toggle source

This method is called to start an update query

# 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) click to toggle source

This method is called to specify the values to insert.

# 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) click to toggle source

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

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

# 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?
    if condition.value.is_a?(Enumerable)
      @params.concat(condition.value)
    else
      @params << condition.value
    end
  end

  self
end