class Praxis::Mapper::Query::Sql
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.
@example “SELECT column1, column2 FROM table1 WHERE column1=value1 AND column2=value2”
Public Instance Methods
Executes this SQL statement. Does not perform any validation of the statement before execution.
@return [Array] result-set
# 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
Executes a 'SELECT' statement.
@param identity [Symbol|Array] a simple or composite key for this model @param values [Array] list of identifier values (ideally a sorted set) @return [Array] SQL result set
@example numeric key
_multi_get(:id, [1, 2])
@example string key
_multi_get(:uid, ['foo', 'bar'])
@example composite key (possibly a combination of numeric and string keys)
_multi_get([:cloud_id, :account_id], [['foo1', 'bar1'], ['foo2', 'bar2']])
# 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.literal(dataset.opts[:where]) original_where = @where self.where clause _execute ensure @where = original_where end
@see sql
# File lib/praxis-mapper/query/sql.rb, line 88 def describe self.sql end
@return [String] SQL 'FROM' clause
FIXME: use ANSI SQL double quotes instead of MySQL backticks @see stackoverflow.com/questions/261455/using-backticks-around-field-names
# File lib/praxis-mapper/query/sql.rb, line 133 def from_clause "FROM `#{model.table_name}`" end
@return [String] SQL 'LIMIT' clause or nil
NOTE: implementation-dependent; not part of ANSI SQL TODO: replace with ISO SQL:2008 FETCH FIRST clause
# File lib/praxis-mapper/query/sql.rb, line 141 def limit_clause if self.limit return "LIMIT #{self.limit}" end end
Constructs a raw SQL statement. No validation is performed here (security risk?).
@param sql_text a custom SQL query
# File lib/praxis-mapper/query/sql.rb, line 97 def raw(sql_text) @raw_query = sql_text end
@return [String] SQL 'SELECT' clause
# 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
@return [String] raw or assembled SQL statement
# 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
Constructs the 'WHERE' clause with all active scopes (read: named conditions).
@return [String] an SQL 'WHERE' clause or nil if no 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”
# 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