Class: Card::Query::SqlStatement

Inherits:
Object
  • Object
show all
Defined in:
lib/card/query/sql_statement.rb

Instance Method Summary collapse

Constructor Details

#initialize(query) ⇒ SqlStatement

Returns a new instance of SqlStatement.



4
5
6
7
 
# File 'lib/card/query/sql_statement.rb', line 4

def initialize query
  @query = query
  @mods = query.mods
end

Instance Method Details

#basic_conditions(conditions) ⇒ Object 



131
132
133
134
135
136
137
138
139
140
 
# File 'lib/card/query/sql_statement.rb', line 131

def basic_conditions conditions
  conditions.map do |condition|
    if condition.is_a? String
      condition
    else
      field, val = condition
      val.to_sql field
    end
  end
end

#build ⇒ Object 



9
10
11
12
13
14
15
16
17
18
 
# File 'lib/card/query/sql_statement.rb', line 9

def build
  @fields = fields
  @tables = tables
  @joins  = joins @query.all_joins
  @where  = where
  @group  = group
  @order  = order
  @limit_and_offset = limit_and_offset
  self
end

#cast_type(type) ⇒ Object 



222
223
224
225
 
# File 'lib/card/query/sql_statement.rb', line 222

def cast_type(type)
  cxn ||= ActiveRecord::Base.connection
  (val = cxn.cast_types[type.to_sym]) ? val[:name] : safe_sql(type)
end

#deeper_joins(join) ⇒ Object 



72
73
74
75
76
 
# File 'lib/card/query/sql_statement.rb', line 72

def deeper_joins join
  deeper_joins = join.subjoins
  deeper_joins += join.to.all_joins if join.to.is_a? Card::Query
  deeper_joins
end

#fields ⇒ Object 



35
36
37
38
39
40
41
 
# File 'lib/card/query/sql_statement.rb', line 35

def fields
  table = @query.table_alias
  field = @mods[:return]
  field = field.blank? ? :card : field.to_sym
  field = full_field(table, field)
  [field, @mods[:sort_join_field]].compact * ', '
end

#full_field(table, field) ⇒ Object 



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'lib/card/query/sql_statement.rb', line 43

def full_field table, field
  case field
  when :raw      then "#{table}.*"
  when :card     then "#{table}.name"
  when :content  then "#{table}.db_content"
  when :count
    "coalesce(count( distinct #{table}.id),0) as count"
  else
    if ATTRIBUTES[field.to_sym] == :basic
      "#{table}.#{field}"
    else
      safe_sql field
    end
  end
end

#full_syntax ⇒ Object 



174
175
176
177
 
# File 'lib/card/query/sql_statement.rb', line 174

def full_syntax
  return if @query.superquery || @mods[:return] == 'count'
  yield
end

#group ⇒ Object 



157
158
159
160
 
# File 'lib/card/query/sql_statement.rb', line 157

def group
  group = @mods[:group]
  "GROUP BY #{safe_sql group}" if group.present?
end

#join_clause(join) ⇒ Object 



78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/card/query/sql_statement.rb', line 78

def join_clause join
  to_table = join.to_table
  to_table = "(#{to_table.sql})" if to_table.is_a? Card::Query
  table_segment = [to_table, join.to_alias].join ' '

  if join.left?
    djoins = deeper_joins(join)
    unless djoins.empty?
      table_segment = "(#{table_segment} #{joins djoins})"
    end
  end
  [join.side, 'JOIN', table_segment].compact.join ' '
end

#join_on_clause(join) ⇒ Object 



68
69
70
 
# File 'lib/card/query/sql_statement.rb', line 68

def join_on_clause join
  [join_clause(join), 'ON', on_clause(join)].join ' '
end

#joins(join_list) ⇒ Object 



59
60
61
62
63
64
65
66
 
# File 'lib/card/query/sql_statement.rb', line 59

def joins join_list
  clauses = []
  join_list.each do |join|
    clauses << join_on_clause(join)
    clauses << joins(deeper_joins join) unless join.left?
  end
  clauses.flatten * "\n"
end

#limit_and_offset ⇒ Object 



162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/card/query/sql_statement.rb', line 162

def limit_and_offset
  full_syntax do
    limit = @mods[:limit]
    offset = @mods[:offset]
    if limit.to_i > 0
      string =  "LIMIT  #{limit.to_i} "
      string += "OFFSET #{offset.to_i} " if offset.present?
      string
    end
  end
end

#on_clause(join) ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'lib/card/query/sql_statement.rb', line 92

def on_clause join
  on_conditions = join.conditions
  on_ids = [
    "#{join.from_alias}.#{join.from_field}",
    "#{join.to_alias}.#{join.to_field}"
  ].join ' = '
  on_conditions.unshift on_ids
  if join.to.is_a? Card::Query
    if join.to.conditions_on_join == join
      on_conditions.push query_conditions(join.to)
    end
    on_conditions.push standard_conditions(join.to)
  end
  basic_conditions(on_conditions) * ' AND '
end

#order ⇒ Object 



179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/card/query/sql_statement.rb', line 179

def order
  full_syntax do
    order_key ||= @mods[:sort].blank? ? 'update' : @mods[:sort]

    order_directives = [order_key].flatten.map do |key|
      dir = if @mods[:dir].blank?
              DEFAULT_ORDER_DIRS[key.to_sym] || 'asc'
            else
              safe_sql @mods[:dir]
            end
      sort_field key, @mods[:sort_as], dir
    end.join ', '
    "ORDER BY #{order_directives}"
  end
end

#permission_conditions(query) ⇒ Object 



150
151
152
153
154
155
 
# File 'lib/card/query/sql_statement.rb', line 150

def permission_conditions query
  return if Auth.always_ok?
  read_rules = Auth.as_card.read_rules
  read_rule_list = read_rules.nil? ? 1 : read_rules.join(',')
  "#{query.table_alias}.read_rule_id IN (#{read_rule_list})"
end

#query_conditions(query) ⇒ Object 



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/card/query/sql_statement.rb', line 114

def query_conditions query
  cond_list = basic_conditions query.conditions
  cond_list +=
    query.subqueries.map do |subquery|
      next if subquery.conditions_on_join
      query_conditions subquery
    end
  cond_list.reject!(&:blank?)

  if cond_list.size > 1
    cond_list = cond_list.join "\n#{query.current_conjunction.upcase} "
    "(#{cond_list})"
  else
    cond_list.join
  end
end

#safe_sql(txt) ⇒ Object 



213
214
215
216
217
218
219
220
 
# File 'lib/card/query/sql_statement.rb', line 213

def safe_sql(txt)
  txt = txt.to_s
  if txt.match(/[^\w\*\(\)\s\.\,]/)
    fail "WQL contains disallowed characters: #{txt}"
  else
    txt
  end
end

#sort_field(key, as, dir) ⇒ Object 



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/card/query/sql_statement.rb', line 195

def sort_field key, as, dir
  table = @query.table_alias
  order_field =
    case key
    when 'id'             then "#{table}.id"
    when 'update'         then "#{table}.updated_at"
    when 'create'         then "#{table}.created_at"
    when /^(name|alpha)$/ then "LOWER( #{table}.key )"
    when 'content'        then "#{table}.db_content"
    when 'relevance'      then "#{table}.updated_at" # deprecated
    else
      safe_sql(key)
    end
  order_field = "CAST(#{order_field} AS #{cast_type(safe_sql as)})" if as
  @fields += ", #{order_field}"
  "#{order_field} #{dir}"
end

#standard_conditions(query) ⇒ Object 



142
143
144
 
# File 'lib/card/query/sql_statement.rb', line 142

def standard_conditions query
  [trash_condition(query), permission_conditions(query)].compact * ' AND '
end

#tables ⇒ Object 



31
32
33
 
# File 'lib/card/query/sql_statement.rb', line 31

def tables
  "cards #{@query.table_alias}"
end

#to_s ⇒ Object 



20
21
22
23
24
25
26
27
28
29
 
# File 'lib/card/query/sql_statement.rb', line 20

def to_s
  ["SELECT DISTINCT #{@fields}",
   "FROM #{@tables}",
   @joins,
   @where,
   @group,
   @order,
   @limit_and_offset
  ].compact * "\n"
end

#trash_condition(query) ⇒ Object 



146
147
148
 
# File 'lib/card/query/sql_statement.rb', line 146

def trash_condition query
  "#{query.table_alias}.trash is false"
end

#where ⇒ Object 



108
109
110
111
112
 
# File 'lib/card/query/sql_statement.rb', line 108

def where
  conditions = [query_conditions(@query), standard_conditions(@query)]
  conditions = conditions.reject(&:blank?).join "\nAND "
  "WHERE #{conditions}" unless conditions.blank?
end