Class: Tutuf::VisualQuery::Base

Inherits:
Common
  • Object
show all
Defined in:
lib/tutuf/visual_query/base.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Common

connection, #filters_to_a, quote_ident, quote_relation_name

Constructor Details

#initialize(params) ⇒ Base

end class methods



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/tutuf/visual_query/base.rb', line 107

def initialize(params)
  # params were kept in array in an older version
  params = {} if params.blank? || Array === params
  @relations = params['relations']
  rows = (params['rows'].blank? ? [0] : params['rows'])
  if balanced = (rows.size > 1)
    @single_queries = rows.map do |row|
                        relations = params['relations'].select{|h| h['row'] == row} if params['relations']
                        join_conditions = params['join_conditions'].select{|jc| jc['row'] == row} if params['join_conditions']
                        Tutuf::VisualQuery::Single.new({'action' => params['action'],
                                                        'relations' => relations,
                                                        'join_conditions' => join_conditions,
                                                        'all_columns' => params['all_columns'],
                                                        'all_selected_columns' => params['columns'],
                                                        'sort_conditions' => params['sort_conditions'],
                                                        'balanced' => balanced}.merge(%w[columns filters].inject({}){|res,key| res[key] = select(relations,params[key]); res}))
                    end
  elsif params['sql']
    @single_queries = [Tutuf::VisualQuery::Sql.new(params)]
  else
    @single_queries = [Tutuf::VisualQuery::Single.new(params)]
  end

  name = params['query']['name'] if params['query']
  @metadata = params.delete('metadata') || Metadata.new(:name => name, :params => params)
end

Instance Attribute Details

#relations ⇒ Object (readonly)

Returns the value of attribute relations.



134
135
136
 
# File 'lib/tutuf/visual_query/base.rb', line 134

def relations
  @relations
end

#single_queries ⇒ Object (readonly)

Returns the value of attribute single_queries.



134
135
136
 
# File 'lib/tutuf/visual_query/base.rb', line 134

def single_queries
  @single_queries
end

Class Method Details

.columns(klass) ⇒ Object 



31
32
33
34
35
 
# File 'lib/tutuf/visual_query/base.rb', line 31

def columns(klass)
  inheritance_column = klass.columns.detect{|c|c.name == klass.inheritance_column}
  col_names = (klass.primary_key.is_a?(Array) ? klass.primary_key : [klass.primary_key]) + klass.content_columns.collect{ |col| col.name} + (inheritance_column ? [inheritance_column.name] : [])
  col_names.map(&:to_s)
end

.data_type(params) ⇒ Object 



66
67
68
69
70
71
72
73
74
 
# File 'lib/tutuf/visual_query/base.rb', line 66

def data_type(params)
  qualified_relation_name = quote_relation_name('schema' => params['schema'], 'rel_name' => params['rel_name'])
  res = connection.columns(qualified_relation_name).find{|c| c.name == params['col_name']}
  if res
    res.type
  else
    nil
  end
end

.disable ⇒ Object 



89
90
91
 
# File 'lib/tutuf/visual_query/base.rb', line 89

def disable
  connection.execute "DROP SCHEMA IF EXISTS #{quote_ident(schema)} CASCADE"
end

.enable ⇒ Object 



93
94
95
96
97
98
99
 
# File 'lib/tutuf/visual_query/base.rb', line 93

def enable
  connection.execute "CREATE SCHEMA #{quote_ident(schema)}"
  find_all.each do |metadata|
    vq = new(metadata.params.merge('metadata' => metadata))
    vq.save
  end
end

.find_all ⇒ Object

Returns array of metadata for all queries (Tutuf::VisualQuery::Metadata)



77
78
79
 
# File 'lib/tutuf/visual_query/base.rb', line 77

def find_all
  Metadata.all
end

.find_by_name(name) ⇒ Object 



81
82
83
84
85
86
87
 
# File 'lib/tutuf/visual_query/base.rb', line 81

def find_by_name(name)
  if metadata = Metadata.find_by_name(name)
    object = new(metadata.params)
    object.instance_variable_set("@metadata", metadata)
    object
  end
end

.join_condition(reflection) ⇒ Object

Join condition between reflection (ActiveRecord::Reflection::MacroReflection) and its klass in format [[=> reflection relation, ‘col_name’ => reflection column,

{'rel_name' => klass relation, 'col_name' => klass column}, ... ]]

The order of relations in the array is important for parsing afterwards



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/tutuf/visual_query/base.rb', line 50

def join_condition(reflection)
  case reflection.macro
  when :belongs_to
    [[{'rel_name' => reflection.active_record.table_name, 'col_name' => reflection.foreign_key},
      {'rel_name' => reflection.table_name,               'col_name' => reflection.klass.primary_key}]]
  when :has_many, :has_one
    [[{'rel_name' => reflection.active_record.table_name, 'col_name' => reflection.active_record.primary_key},
      {'rel_name' => reflection.table_name,               'col_name' => reflection.foreign_key}]]
  when :has_and_belongs_to_many
    [[ {'rel_name' => reflection.active_record.table_name, 'col_name' => reflection.active_record.primary_key},
       {'rel_name' => reflection.join_table,               'col_name' => reflection.foreign_key}],
     [ {'rel_name' => reflection.join_table,               'col_name' => reflection.association_foreign_key},
       {'rel_name' => reflection.klass.table_name,         'col_name' => reflection.klass.primary_key}]]
  end
end

.join_relations(reflection) ⇒ Object

Array of relations to be joinded



38
39
40
41
42
43
44
 
# File 'lib/tutuf/visual_query/base.rb', line 38

def join_relations(reflection)
  if reflection.macro == :has_and_belongs_to_many
    [reflection.join_table, reflection.table_name]
  else
    [reflection.klass.table_name]
  end
end

.joinable_relations(klass) ⇒ Object

List of all relations that the klass can be joined with. Currently self joins are not supported.



27
28
29
 
# File 'lib/tutuf/visual_query/base.rb', line 27

def joinable_relations(klass)
  klass.reflections.values.reject{ |reflection| reflection.options[:polymorphic] || reflection.klass == klass }
end

.klasses ⇒ Object

All direct ascendants of ActiveRecord::Base



13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/tutuf/visual_query/base.rb', line 13

def klasses
  return @@klasses if defined?(@@klasses)
  @@klasses = []
  all_classes = model_filenames.map do |model_filename|
    klass = model_filename.gsub(/\.rb$/, '').split(%r{/}).map { |c| c.camelize }.join('::')
    klass.constantize
  end
  all_classes.each do |klass|
    @@klasses << klass if klass.respond_to?(:superclass) && klass.superclass == ActiveRecord::Base
  end
  @@klasses.uniq
end

.schema ⇒ Object

PostgreSQL schema in which the saved queries live



8
9
10
 
# File 'lib/tutuf/visual_query/base.rb', line 8

def schema
  "tutuf::visual_query"
end

Instance Method Details

#columns ⇒ Object

Array of hashes representing qualified column names that look like { ‘rel_name’ => “some relation”, ‘col_name’ => “some column”}. Raises an exception if the query has a name and is not saved yet. Returns nil if it does not have a name and is not saved.



211
212
213
214
215
 
# File 'lib/tutuf/visual_query/base.rb', line 211

def columns
  if name
    connection.columns(quote_relation_name('schema' => schema, 'rel_name' => name)).map{|c| {'rel_name' => name, 'col_name' => c.name}.with_indifferent_access}
  end
end

#destroy ⇒ Object 



257
258
259
260
261
262
263
264
265
 
# File 'lib/tutuf/visual_query/base.rb', line 257

def destroy
  begin
    Metadata.transaction do
      @metadata.destroy
      connection.execute("DROP VIEW #{quote_ident(self.class.schema)}.#{quote_ident(name)}")
    end
    return true
  end
end

#errors ⇒ Object 



189
190
191
 
# File 'lib/tutuf/visual_query/base.rb', line 189

def errors
  @metadata.errors
end

#filters ⇒ Object 



140
141
142
 
# File 'lib/tutuf/visual_query/base.rb', line 140

def filters
  single_queries.map{|sq| sq.filters.nil? ? [] : sq.filters}.flatten
end

#large_result_set? ⇒ Boolean

Returns:

  • (Boolean) 


205
206
207
 
# File 'lib/tutuf/visual_query/base.rb', line 205

def large_result_set?
  @results && !@results.empty? && (@results.length * @results[0].length > max_result_length)
end

#max_result_length ⇒ Object 



201
202
203
 
# File 'lib/tutuf/visual_query/base.rb', line 201

def max_result_length
  20000
end

#name ⇒ Object 



185
186
187
 
# File 'lib/tutuf/visual_query/base.rb', line 185

def name
  @metadata.name
end

#new? ⇒ Boolean

Returns:

  • (Boolean) 


197
198
199
 
# File 'lib/tutuf/visual_query/base.rb', line 197

def new?
  @metadata.new_record?
end

#query ⇒ Object 



144
145
146
 
# File 'lib/tutuf/visual_query/base.rb', line 144

def query
  single_queries.size > 1 ? single_queries.map{|sq| "(#{sq.query})"}.join(" UNION ALL ") : single_queries.first.query
end

#save ⇒ Object 



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'lib/tutuf/visual_query/base.rb', line 217

def save
  if @metadata.valid?
    begin
      Metadata.transaction do
        @metadata.save
        connection.execute("CREATE VIEW #{view_name.to_s.force_encoding('UTF-8')} AS #{query.to_s.force_encoding('UTF-8')}")
        return true
      end
    rescue ActiveRecord::StatementInvalid => e
      raise e
    end
  else
    return false
  end
end

#schema ⇒ Object 



181
182
183
 
# File 'lib/tutuf/visual_query/base.rb', line 181

def schema
  Tutuf::VisualQuery::Base.schema
end

#sql? ⇒ Boolean

Returns:

  • (Boolean) 


136
137
138
 
# File 'lib/tutuf/visual_query/base.rb', line 136

def sql?
  single_queries.size == 1 && Tutuf::VisualQuery::Sql === single_queries.first
end

#to_a(headers = false) ⇒ Object 



167
168
169
 
# File 'lib/tutuf/visual_query/base.rb', line 167

def to_a(headers=false)
  results(headers)
end

#to_csv ⇒ Object 



171
172
173
174
 
# File 'lib/tutuf/visual_query/base.rb', line 171

def to_csv
  data = results(true)
  CSV.generate { |csv| data.each { |row| csv << row } }
end

#to_json ⇒ Object 



176
177
178
179
 
# File 'lib/tutuf/visual_query/base.rb', line 176

def to_json
  return [] if(!sql? && (relations.nil? || relations == ""))
  JSON.generate(connection.select_all(query))
end

#update_sql(params) ⇒ Object 



233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
 
# File 'lib/tutuf/visual_query/base.rb', line 233

def update_sql(params)
  @metadata.params = params
  @single_queries = [Tutuf::VisualQuery::Sql.new('sql' => params['sql'])]
  if @metadata.valid?
    begin
      Metadata.transaction do
        @metadata.save
        connection.execute("DROP VIEW #{view_name}")
        connection.execute("CREATE VIEW #{view_name} AS #{query}")
        return true
      end
    rescue ActiveRecord::StatementInvalid => e
      @metadata.errors.add(:base, e.to_s)
      return false
    end
  else
    return false
  end
end

#valid? ⇒ Boolean

Returns:

  • (Boolean) 


193
194
195
 
# File 'lib/tutuf/visual_query/base.rb', line 193

def valid?
  @metadata.valid?
end

#view_name ⇒ Object 



253
254
255
 
# File 'lib/tutuf/visual_query/base.rb', line 253

def view_name
  "#{quote_ident(self.class.schema)}.#{quote_ident(name)}"
end