Module: ThinkingSphinx::Source::SQL

Included in:
ThinkingSphinx::Source
Defined in:
lib/thinking_sphinx/source/sql.rb

Instance Method Summary collapse

Instance Method Details

#crc_column ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/thinking_sphinx/source/sql.rb', line 133

def crc_column
  if @model.table_exists? &&
      @model.column_names.include?(@model.inheritance_column)

    adapter.cast_to_unsigned(adapter.convert_nulls(
                               adapter.crc(adapter.quote_with_table(@model.inheritance_column), true),
                               @model.to_crc32
    ))
  else
    @model.to_crc32.to_s
  end
end

#quote_column(column) ⇒ Object 



129
130
131
 
# File 'lib/thinking_sphinx/source/sql.rb', line 129

def quote_column(column)
  @model.connection.quote_column_name(column)
end

#sql_group_clause ⇒ Object 



102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/thinking_sphinx/source/sql.rb', line 102

def sql_group_clause
  internal_groupings = []
  if @model.column_names.include?(@model.inheritance_column)
    internal_groupings << "#{@model.quoted_table_name}.#{quote_column(@model.inheritance_column)}"
  end

  (
    ["#{@model.quoted_table_name}.#{quote_column(@model.primary_key_for_sphinx)}"] +
    @fields.collect     { |field|     field.to_group_sql     }.compact +
    @attributes.collect { |attribute| attribute.to_group_sql }.compact +
    @groupings + internal_groupings
  ).join(", ")
end

#sql_query_pre_for_core ⇒ Object 



116
117
118
119
120
121
122
123
 
# File 'lib/thinking_sphinx/source/sql.rb', line 116

def sql_query_pre_for_core
  if !options[:delta] and self.delta?
    #REPLACE INTO sph_counter SELECT 'sos', Max(id) FROM sos
    ["REPLACE INTO sph_counter SELECT '#{@model.table_name}', Max(id) FROM #{@model.quoted_table_name}"]
  else
    []
  end
end

#sql_query_pre_for_delta ⇒ Object 



125
126
127
 
# File 'lib/thinking_sphinx/source/sql.rb', line 125

def sql_query_pre_for_delta
  [""]
end

#sql_select_clause(offset) ⇒ Object 



77
78
79
80
81
82
83
84
85
 
# File 'lib/thinking_sphinx/source/sql.rb', line 77

def sql_select_clause(offset)
  unique_id_expr = ThinkingSphinx.unique_id_expression(offset)

  (
    ["#{@model.quoted_table_name}.#{quote_column(@model.primary_key_for_sphinx)} #{unique_id_expr} AS #{quote_column(@model.primary_key_for_sphinx)} "] +
    @fields.collect     { |field|     field.to_select_sql     } +
    @attributes.collect { |attribute| attribute.to_select_sql }
  ).compact.join(", ")
end

#sql_where_clause(options) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/thinking_sphinx/source/sql.rb', line 87

def sql_where_clause(options)
  logic = []
  logic += [
    "#{@model.quoted_table_name}.#{quote_column(@model.primary_key_for_sphinx)} >= $start",
    "#{@model.quoted_table_name}.#{quote_column(@model.primary_key_for_sphinx)} <= $end"
  ] unless @index.options[:disable_range]

  # if self.delta? && [email protected]_object.clause(@model, options[:delta]).blank?
  #          logic << "#{@index.delta_object.clause(@model, options[:delta])}"
  #        end

  logic += (@conditions || [])
  logic.empty? ? "" : "WHERE #{logic.join(' AND ')}"
end

#to_sql(options = {}) ⇒ Object

Generates the big SQL statement to get the data back for all the fields and attributes, using all the relevant association joins. If you want the version filtered for delta values, send through :delta => true in the options. Won’t do much though if the index isn’t set up to support a delta sibling.

Examples:

source.to_sql
source.to_sql(:delta => true)



15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/thinking_sphinx/source/sql.rb', line 15

def to_sql(options={})
  sql = "SELECT "
  sql += "SQL_NO_CACHE " if adapter.sphinx_identifier == "mysql"
  sql += <<-SQL
  #{ sql_select_clause options[:offset] }
  FROM #{ @model.quoted_table_name }
  #{ all_associations.collect { |assoc| assoc.to_sql }.join(' ') }
  #{ sql_where_clause(options) }
  GROUP BY #{ sql_group_clause }
  SQL

  sql += " ORDER BY id desc" if adapter.sphinx_identifier == "mysql"
  sql
end

#to_sql_query_info(offset) ⇒ Object

Simple helper method for the query info SQL - which is a statement that returns the single row for a corresponding id.



72
73
74
75
 
# File 'lib/thinking_sphinx/source/sql.rb', line 72

def to_sql_query_info(offset)
        "SELECT * FROM #{@model.quoted_table_name} WHERE " +
"#{quote_column(@model.primary_key_for_sphinx)} = (($id - #{offset}) / #{ThinkingSphinx.context.indexed_models.size})"
end

#to_sql_query_range(options = {}) ⇒ Object

Simple helper method for the query range SQL - which is a statement that returns minimum and maximum id values. These can be filtered by delta - so pass in :delta => true to get the delta version of the SQL.



34
35
36
37
38
39
40
41
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/thinking_sphinx/source/sql.rb', line 34

def to_sql_query_range(options={})
  return nil if @index.options[:disable_range]
  if !options[:delta] and self.delta?
    #SELECT Min(id), Max(id) FROM sos WHERE id <= (SELECT last_id FROM sph_counter WHERE table_name = 'sos')
    min_statement = adapter.convert_nulls(
      "MIN(#{quote_column(@model.primary_key_for_sphinx)})", 1
    )
    max_statement = adapter.convert_nulls(
      "MAX(#{quote_column(@model.primary_key_for_sphinx)})", 1
    )

    sql = "SELECT #{min_statement}, #{max_statement} " +
      "FROM #{@model.quoted_table_name} WHERE #{@model.primary_key_for_sphinx} <= (SELECT last_id FROM sph_counter WHERE table_name = '#{@model.table_name}')"
  else


    min_statement = adapter.convert_nulls(
      "MIN(#{quote_column(@model.primary_key_for_sphinx)})", 1
    )
    max_statement = adapter.convert_nulls(
      "MAX(#{quote_column(@model.primary_key_for_sphinx)})", 1
    )

    sql = "SELECT #{min_statement}, #{max_statement} " +
      "FROM #{@model.quoted_table_name} "

    if self.delta?
      #sql_query_range = SELECT Min(id), Max(id) FROM sos WHERE id > (SELECT last_id FROM sph_counter WHERE table_name = 'sos')
      sql +=  "WHERE #{@model.primary_key_for_sphinx} > (SELECT last_id FROM sph_counter WHERE table_name = '#{@model.table_name}')"
    end

  end
  sql
end