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 = nil) ⇒ SqlStatement

Returns a new instance of SqlStatement.



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

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

Instance Method Details

#basic_conditions(conditions) ⇒ Object 



137
138
139
140
141
142
143
144
145
146
 
# File 'lib/card/query/sql_statement.rb', line 137

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 



229
230
231
232
 
# File 'lib/card/query/sql_statement.rb', line 229

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

#comment ⇒ Object 



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

def comment
  return nil unless Card.config.sql_comments && @query.comment
  "/* #{@query.comment} */"
end

#deeper_joins(join) ⇒ Object 



78
79
80
81
82
 
# File 'lib/card/query/sql_statement.rb', line 78

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 



41
42
43
44
45
46
47
 
# File 'lib/card/query/sql_statement.rb', line 41

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 



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/card/query/sql_statement.rb', line 49

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 



181
182
183
184
 
# File 'lib/card/query/sql_statement.rb', line 181

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

#group ⇒ Object 



164
165
166
167
 
# File 'lib/card/query/sql_statement.rb', line 164

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

#join_clause(join) ⇒ Object 



84
85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'lib/card/query/sql_statement.rb', line 84

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 



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

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

#joins(join_list) ⇒ Object 



65
66
67
68
69
70
71
72
 
# File 'lib/card/query/sql_statement.rb', line 65

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 



169
170
171
172
173
174
175
176
177
178
179
 
# File 'lib/card/query/sql_statement.rb', line 169

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 



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/card/query/sql_statement.rb', line 98

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 



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/card/query/sql_statement.rb', line 186

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(table) ⇒ Object 



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

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

#query_conditions(query) ⇒ Object 



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'lib/card/query/sql_statement.rb', line 120

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 



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

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

#sort_field(key, as, dir) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/card/query/sql_statement.rb', line 202

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 "#{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 



148
149
150
151
 
# File 'lib/card/query/sql_statement.rb', line 148

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

#tables ⇒ Object 



37
38
39
 
# File 'lib/card/query/sql_statement.rb', line 37

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

#to_s ⇒ Object 



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

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

#trash_condition(table) ⇒ Object 



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

def trash_condition table
  "#{table}.trash is false"
end

#where ⇒ Object 



114
115
116
117
118
 
# File 'lib/card/query/sql_statement.rb', line 114

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