Class: Praxis::Mapper::Query::Sql

Inherits:
Base
  • Object
show all
Defined in:
lib/praxis-mapper/query/sql.rb

Overview

An SQL ‘SELECT’ statement assembler. Assumes ISO SQL:2008 unless otherwise noted. TODO: rename to MySql or MySql5 or MySql51 or something

The SQL SELECT statement returns a result set of records from one or more tables.

The SELECT statement has two mandatory clauses:

  • SELECT specifies which columns/aliases to return.

  • FROM specifies which tables/views to query.

The SELECT statement has many optional clauses:

  • WHERE specifies which rows to retrieve.

  • GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

  • HAVING selects among the groups defined by the GROUP BY clause.

  • ORDER BY specifies an order in which to return the rows.

  • LIMIT specifies how many rows to return (non-standard).

Currently only SELECT, FROM, WHERE and LIMIT has been implemented.

Examples:

“SELECT column1, column2 FROM table1 WHERE column1=value1 AND column2=value2”

See Also:

Constant Summary

Constants inherited from Base

Base::MULTI_GET_BATCH_SIZE

Instance Attribute Summary

Attributes inherited from Base

#contexts, #identity_map, #model, #statistics, #where

Instance Method Summary collapse

Methods inherited from Base

#apply_selector, #connection, #context, #default_select, #execute, #initialize, #limit, #load, #multi_get, #select, #to_records, #track, #tracked_associations

Constructor Details

This class inherits a constructor from Praxis::Mapper::Query::Base

Instance Method Details

#_execute ⇒ Array

Executes this SQL statement. Does not perform any validation of the statement before execution.

Returns:

  • (Array) —

    result-set



73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/praxis-mapper/query/sql.rb', line 73

def _execute
  Praxis::Mapper.logger.debug "SQL:\n#{self.describe}\n"
  self.statistics[:datastore_interactions] += 1
  start_time = Time.now

  if @where && @raw_query
    warn 'WARNING: Query::Sql#_execute ignoring requested `where` clause due to specified raw SQL'
  end
  rows = connection.fetch(self.sql).to_a

  self.statistics[:datastore_interaction_time] += (Time.now - start_time)
  return rows
end

#_multi_get(identity, values) ⇒ Array

Executes a ‘SELECT’ statement.

Examples:

numeric key

_multi_get(:id, [1, 2])

string key

_multi_get(:uid, ['foo', 'bar'])

composite key (possibly a combination of numeric and string keys)

_multi_get([:cloud_id, :account_id], [['foo1', 'bar1'], ['foo2', 'bar2']])

Parameters:

  • identity (Symbol|Array) —

    a simple or composite key for this model

  • values (Array) —

    list of identifier values (ideally a sorted set)

Returns:

  • (Array) —

    SQL result set



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/praxis-mapper/query/sql.rb', line 42

def _multi_get(identity, values)
  dataset = connection[model.table_name.to_sym].where(identity => values)

  # MySQL 5.1 won't use an index for a multi-column IN clause. Consequently, when adding
  # multi-column IN clauses, we also add a single-column IN clause for the first column of
  # the multi-column IN-clause. In this way, MySQL will be able to use an index for the
  # single-column IN clause but will use the multi-column IN clauses to limit which
  # records are returned.
  if identity.kind_of?(Array)
    dataset = dataset.where(identity.first => values.collect(&:first))
  end

  # preserve existing where condition from query
  if @where
    dataset = dataset.where(@where)
  end

  clause = dataset.opts[:where].sql_literal(dataset)

  original_where = @where

  self.where clause
  _execute
ensure
  @where = original_where
end

#describe ⇒ Object

See Also:



88
89
90
 
# File 'lib/praxis-mapper/query/sql.rb', line 88

def describe
  self.sql
end

#from_clause ⇒ String

FIXME: use ANSI SQL double quotes instead of MySQL backticks

Returns:

  • (String) —

    SQL ‘FROM’ clause

See Also:



133
134
135
 
# File 'lib/praxis-mapper/query/sql.rb', line 133

def from_clause
  "FROM `#{model.table_name}`"
end

#limit_clause ⇒ String

NOTE: implementation-dependent; not part of ANSI SQL TODO: replace with ISO SQL:2008 FETCH FIRST clause

Returns:

  • (String) —

    SQL ‘LIMIT’ clause or nil



141
142
143
144
145
 
# File 'lib/praxis-mapper/query/sql.rb', line 141

def limit_clause
  if self.limit
    return "LIMIT #{self.limit}"
  end
end

#raw(sql_text) ⇒ Object

Constructs a raw SQL statement. No validation is performed here (security risk?).

Parameters:

  • sql_text —

    a custom SQL query



97
98
99
 
# File 'lib/praxis-mapper/query/sql.rb', line 97

def raw(sql_text)
  @raw_query = sql_text
end

#select_clause ⇒ String

Returns SQL ‘SELECT’ clause.

Returns:

  • (String) —

    SQL ‘SELECT’ clause



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/praxis-mapper/query/sql.rb', line 111

def select_clause
  columns = []
  if select && select != true
    select.each do |alias_name, column_name|
      if column_name
        # alias_name is always a String, not a Symbol
        columns << "#{column_name} AS #{alias_name}"
      else
        columns << (alias_name.is_a?(Symbol) ? alias_name.to_s : alias_name)
      end
    end
  else
    columns << '*'
  end

  "SELECT #{columns.join(', ')}"
end

#sql ⇒ String

Returns raw or assembled SQL statement.

Returns:

  • (String) —

    raw or assembled SQL statement



102
103
104
105
106
107
108
 
# File 'lib/praxis-mapper/query/sql.rb', line 102

def sql
  if @raw_query
    @raw_query
  else
    [select_clause, from_clause, where_clause, limit_clause].compact.join("\n")
  end
end

#where_clause ⇒ String

Constructs the ‘WHERE’ clause with all active scopes (read: named conditions).

FIXME: use ANSI SQL double quotes instead of MySQL backticks FIXME: Doesn’t sanitize any values. Could be “fun” later (where fun means a horrible security hole) TODO: add per-model scopes, ie, servers might have a scope for type = “GenericServer”

Returns:

  • (String) —

    an SQL ‘WHERE’ clause or nil if no conditions



154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/praxis-mapper/query/sql.rb', line 154

def where_clause
  # collects and compacts conditions as defined in identity map and model
  conditions = identity_map.scope.collect do |name, condition|
    # checks if this condition has been banned for this model
    unless model.excluded_scopes.include? name
      column, value = condition # example: "user_id", 123
      case value
      when Integer
        "`#{column}`=#{value}"
      when String
        "`#{column}`='#{value}'"
      when NilClass
        "`#{column}` IS NULL"
      else
        raise "unknown type for scope #{name} with condition #{condition}"
      end
    end
  end.compact

  conditions << where if where

  if conditions.any?
    return "WHERE #{conditions.join(" AND ")}"
  else
    nil
  end
end