Class: QBuilder

Inherits:
Gtk::HPaned
  • Object
show all
Includes:
Conf, ManqodCommon
Defined in:
lib/FormHolder/Form/InputHolder/QBuilder.rb

Overview

this file is part of manqod manqod is distributed under the CDDL licence the author of manqod is Dobai-Pataky Balint([email protected])

Constant Summary

Constants included from ManqodCommon

ManqodCommon::CRITICAL, ManqodCommon::DEBUG, ManqodCommon::ERROR, ManqodCommon::INFO, ManqodCommon::NORMAL, ManqodCommon::WARNING

Constants included from Eprint

Eprint::DOMAIN, Eprint::LEVEL

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Conf

#get_conf, #load_conf, #save_conf, #set_conf

Methods included from ManqodCommon

#add_where, #admin, #admin_cache, #admin_qrow, #admin_rows, #backtrace_to_debug, #cache, #changed_ids_of_base, #client, #client_fields, #client_image_of_id, #client_qrow, #client_query, #client_rows, #eeval, #escape_string, #getBinding, #guess_base, #guess_table, #image_of_id, #lzero, #manqod_db, #measure, #myexec, #nick, #nick_id, #number_format, #qrow, #query, #reconnect_manqod_db, #rows, #run_events, #send_message, #sendmail, #set_manqod_db_uri, #set_nick

Methods included from Eprint

#ecode, #edebug, #eerror, #einfo, #enormal, #eprint, #ewarn, #gtk_set_edebug, #set_edebug, #tell_exception

Constructor Details

#initialize(pc) ⇒ QBuilder

Returns a new instance of QBuilder.



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 9

def initialize(pc)
@pc=pc
@qobjects=Array.new
super()

whsv=false
begin
  whsv=Gtk::SourceView::BUILD_VERSION
  rescue => err
  einfo("no Gtk::SourceView, falling back")
end

add1(Gtk::VBox.new(false,1).
  pack_start(@info=Gtk::Table.new(2,3),false).
  pack_start(Gtk::ScrolledWindow.new.set_policy(Gtk::PolicyType::AUTOMATIC,Gtk::PolicyType::AUTOMATIC).add_with_viewport(@holder=Gtk::VBox.new(false,0))).
  pack_start(@controls=Gtk::VBox.new,false,true))
add2(@rb=RelationBuilder.new(false))

@info.attach_defaults(Gtk::Label.new("name"),0,1,0,1).attach_defaults(@name=Gtk::Entry.new,1,2,0,1).
  attach_defaults(Gtk::Label.new("base"),0,1,1,2).attach_defaults(Gtk::HBox.new.pack_start(@base=Gtk::ComboBox.new(Gtk::ListStore.new(Integer,String))).pack_end(@goto_button=Gtk::Button.new.set_image(Gtk::Image.new(Gtk::Stock::JUMP_TO,Gtk::IconSize.from_name(get_conf(0,0,"button-size")))),false,false),1,2,1,2).
  attach_defaults(Gtk::Label.new("comment"),0,1,2,3).attach_defaults(@comment=Gtk::Entry.new,1,2,2,3)
update_base_selector
@goto_button.signal_connect('clicked'){|me|
  base_object.center_on_link
}
@base.pack_start(renderer=Gtk::CellRendererText.new,false).add_attribute(renderer,:text,1).signal_connect("changed"){|me|
  save_query
  if bo=base_object
    bo.set_obj_id(me.active_iter[0]) unless me.active_iter.nil?
    else
    bo=QObject.new(self,rb.table_by_id(me.active_iter[0]))
  end
  update(self)
#     base_object.center_on_link
  @rb.set_base_object(bo.link)
  false
}
@name.signal_connect("activate"){|me|save_query;false}
@comment.signal_connect("activate"){|me|save_query;false}

@where=whsv ? Gtk::SourceView.new : Gtk::TextView.new
@group_by=whsv ? Gtk::SourceView.new : Gtk::TextView.new

@holder.pack_start(
  Gtk::Frame.new.
    set_label_widget(Gtk::HBox.new(false,10).pack_start(Gtk::Label.new("SELECT")).pack_end(select_button=Gtk::Button.new)).
    set_shadow_type(Gtk::ShadowType::IN).
    add(@select=Gtk::VBox.new),false,true).
  pack_start(Gtk::Frame.new.
    set_label_widget(Gtk::HBox.new(false,10).pack_start(Gtk::Label.new("FROM")).pack_end(from_button=Gtk::Button.new)).
    set_shadow_type(Gtk::ShadowType::IN).
    add(@from=Gtk::VBox.new),false,true).
  pack_start(Gtk::Frame.new("WHERE").set_shadow_type(Gtk::ShadowType::IN).add(@where),false,true).
  pack_start(Gtk::Frame.new("GROUP BY").set_shadow_type(Gtk::ShadowType::IN).add(@group_by),false,true).
  pack_start(Gtk::Label.new,true,true)

@controls.pack_start(Gtk::HBox.new(false).
    pack_start(@autotest=Gtk::ToggleButton.new("auto")).
    pack_start(@test_now=Gtk::Button.new("test")).
    pack_start(@view_sql=Gtk::Button.new("show")).
    pack_start(@explain_sql=Gtk::Button.new("explain")).
    pack_start(@save_as_view=Gtk::Button.new("store")),false,true).
  pack_start(Gtk::ScrolledWindow.new.set_policy(Gtk::PolicyType::AUTOMATIC,Gtk::PolicyType::AUTOMATIC).add_with_viewport(@result=Gtk::Button.new),false)
@where.buffer.set_language(Gtk::SourceLanguageManager.new.get_language("sql")) if whsv
@where.signal_connect("focus-out-event"){|me,ev|
  if @autotest.active?
    save_query
    test_query
  end
  false
}
@group_by.buffer.set_language(Gtk::SourceLanguageManager.new.get_language("sql")) if whsv
@group_by.signal_connect("focus-out-event"){|me,ev|
  if @autotest.active?
    save_query
    test_query
  end
  false
}
@test_now.signal_connect("pressed"){|me|
    save_query
    test_query
}
@view_sql.signal_connect("pressed"){|me|
    save_query
    w=Gtk::Dialog.new(@name.text,parentM.get_ancestor(Gtk::Window),Gtk::Dialog::DESTROY_WITH_PARENT ,[ Gtk::Stock::CLOSE, Gtk::Dialog::RESPONSE_NONE ])
    w.vbox.add(Gtk::ScrolledWindow.new.add(sview=Gtk::SourceView.new.set_editable(false))).set_size_request(800,400)
    sview.buffer.set_text(sql).set_language(Gtk::SourceLanguageManager.new.get_language("sql"))
    sview.set_highlight_current_line(true).
      set_show_line_numbers(true)
    w.show_all
    w.run{|response| w.destroy}
}
@explain_sql.signal_connect("pressed"){|me|
    save_query
    w=Gtk::Dialog.new(@name.text,parentM.get_ancestor(Gtk::Window),Gtk::Dialog::DESTROY_WITH_PARENT ,[ Gtk::Stock::CLOSE, Gtk::Dialog::RESPONSE_NONE ])
    w.vbox.add(Gtk::ScrolledWindow.new.add(eview=Gtk::TreeView.new(emodel=Gtk::ListStore.new(String,String,String,String,String,String,String,String,String)))).set_size_request(1000,400)
    eheader={0=>{:data=>'id',:header=>'Id'},
      1=>{:data=>'select_type',:header=>'Select Type'},
      2=>{:data=>'table',:header=>'Table'},
      3=>{:data=>'type',:header=>'Type'},
      4=>{:data=>'possible_keys',:header=>'Possible Keys'},
      5=>{:data=>'key',:header=>'Key'},
      6=>{:data=>'ref',:header=>'Ref'},
      7=>{:data=>'rows',:header=>'Rows'},
      8=>{:data=>'Extra',:header=>'Extra'}
    }
    eheader.each_pair{|hid,h|
      eview.append_column(Gtk::TreeViewColumn.new(h[:header],Gtk::CellRendererText.new, {:text => hid}))
    }
    begin
    client_rows("explain #{sql}"){|row|
      iter=emodel.append
      eheader.each_pair{|hid,h| iter[hid]=row[h[:data]] }
    }
    rescue
    end
    w.show_all
    w.run{|response| w.destroy}
}
@result.signal_connect("pressed"){|me,ev|
    begin
      w=Gtk::Dialog.new(@name.text,parentM.get_ancestor(Gtk::Window),Gtk::Dialog::DESTROY_WITH_PARENT ,[ Gtk::Stock::CLOSE, Gtk::Dialog::RESPONSE_NONE ])
      res=ManqodDB.instance.manqod_db.client.query(sql)
      htypes=Array.new
      headers=Hash.new
      row=res.fetch_hash(true)
      #build headertypes
      i=0
      row.each_key{|k|
        htypes.push(String)
        headers[i]=k
        i+=1
      }
      model=Gtk::ListStore.new(*htypes)
      sview=Gtk::TreeView.new(model)
      #create and add columns
      headers.sort.each{|k|
        col=Gtk::TreeViewColumn.new(k[1],Gtk::CellRendererText.new, {:text => k[0]}).set_resizable(true).set_sort_column_id(k[0]).set_reorderable(true)
        sview.append_column(col)
      }
      begin
        i=model.append
        row.each{|k,v| i[headers.key(k)]=v}
      end while (row=res.fetch_hash(true))
      sview.set_model(model)
      w.vbox.add(Gtk::ScrolledWindow.new.add(sview).set_policy(Gtk::POLICY_AUTOMATIC,Gtk::POLICY_AUTOMATIC)).set_size_request(600,400)
      w.show_all
      w.run{|response| w.destroy}
    rescue =>err
      warn(err,backtrace_to_debug(err))
    end
}
@save_as_view.signal_connect("pressed"){|me|
  store_query
}


select_button.set_image(Gtk::Image.new(Gtk::Stock::ADD,Gtk::IconSize::MENU)).set_relief(Gtk::ReliefStyle::NONE)
select_button.signal_connect('clicked'){|me|
  QObject.new(self,{"custom"=>"true", "obj_type"=>"f", "field"=>"*"}).store
  update(self)
}

from_button.set_image(Gtk::Image.new(Gtk::Stock::ADD,Gtk::IconSize::MENU)).set_relief(Gtk::ReliefStyle::NONE)
from_button.signal_connect('clicked'){|me|
  QObject.new(self,{"custom"=>"true", "field"=>"left join ", "obj_type"=>"r"})
  update(self)
}
# holder.pack_end(button_holder,false,false,1)
  
#   add(rb_holder)

#   set_query_id(query_id)
@rb.add_observer(self)
signal_connect("destroy"){|me| save_query}
end

Instance Attribute Details

#from ⇒ Object (readonly)

Returns the value of attribute from.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def from
  @from
end

#groupby ⇒ Object (readonly)

Returns the value of attribute groupby.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def groupby
  @groupby
end

#pc ⇒ Object (readonly)

Returns the value of attribute pc.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def pc
  @pc
end

#qobjects ⇒ Object (readonly)

Returns the value of attribute qobjects.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def qobjects
  @qobjects
end

#query_id ⇒ Object (readonly)

Returns the value of attribute query_id.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def query_id
  @query_id
end

#rb ⇒ Object (readonly)

Returns the value of attribute rb.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def rb
  @rb
end

#select ⇒ Object (readonly)

Returns the value of attribute select.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def select
  @select
end

#where ⇒ Object (readonly)

Returns the value of attribute where.



186
187
188
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 186

def where
  @where
end

Instance Method Details

#base ⇒ Object 



306
307
308
309
310
311
312
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 306

def base
  if @base.active_iter
    @base.active_iter[0]
    else
    0
  end
end

#base_object ⇒ Object 



283
284
285
286
287
288
289
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 283

def base_object
  found=nil
  @qobjects.each{|qobject|
    found=qobject if qobject.obj_type == "b"
  }
  found
end

#comment ⇒ Object 



316
317
318
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 316

def comment
  @comment.text
end

#group_by_clause ⇒ Object 



329
330
331
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 329

def group_by_clause
  @group_by.buffer.text
end

#inspect ⇒ Object 



353
354
355
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 353

def inspect
  "QBuilder"
end

#item ⇒ Object 



294
295
296
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 294

def item
  pc.item
end

#name ⇒ Object 



313
314
315
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 313

def name
  @name.text
end

#parentM ⇒ Object 



291
292
293
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 291

def parentM
  pc.parentM
end

#qobject_by_link(link) ⇒ Object 



319
320
321
322
323
324
325
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 319

def qobject_by_link(link)
  found=nil
  @qobjects.each{|qobject|
    found=qobject if qobject.link == link
  }
  found
end

#save_query ⇒ Object 



227
228
229
230
231
232
233
234
235
236
237
238
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 227

def save_query
  if @query_id == 0 or @query_id == -1
    query("insert into `queries` set `base`='#{base}', name='#{name}', comment='#{comment}', `where_clause` = '#{escape_string(where_clause)}', `group_by` = '#{escape_string(group_by_clause)}', `sql`='#{escape_string(sql)}'")
    @query_id=qrow("select id from queries order by id desc limit 1")["id"]
    edebug("new query created: #{name}(#{@query_id})","relation-builder","debug")
    parentM.parentselected=@query_id.to_i
    parentM.runmode="modify"
  else
    query("update `queries` set `base`='#{base}', name='#{name}', comment='#{comment}', `where_clause` = '#{escape_string(where_clause)}', `group_by` = '#{escape_string(group_by_clause)}', `sql`='#{escape_string(sql)}' where id='#{@query_id}'")
    edebug("query updated: #{name}(#{@query_id})","relation-builder","debug")
  end
end

#set_query_id(query_id) ⇒ Object 



260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 260

def set_query_id(query_id)
  @qobjects.each{|key,qobject| qobject.destroy}
  @qobjects.clear
  @query_id=query_id
  if @query_id and row=qrow("select * from queries where id='#{@query_id}'")
    @name.set_text(row["name"])
    @comment.set_text(row["comment"])
    @where.buffer.set_text(row["where_clause"])
    @group_by.buffer.set_text(row["group_by"])
    found=nil
    rows("select * from qobjects where query_id='#{query_id}' order by id"){|qo|
      #@qobjects[qo["obj_id"]]=
      QObject.new(self,qo)
#       @holder.pack_start_defaults(@qobjects[qo["obj_id"]])
    }
    @base.model.each{|model,path,iter| if iter[0] == row["base"].to_i then found=iter;break;end}
    if found
      @base.set_active_iter(found)
    end
  end
  @qobjects.each{|qobject| qobject.update}
end

#sql ⇒ Object 



332
333
334
335
336
337
338
339
340
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 332

def sql
  s="select "
  @select.each{|so| s="#{s}\n#{so.text}"}
  s="#{s}\nfrom "
  @from.each{|so| s="#{s}\n#{so.text}"}
  s="#{s}\nwhere #{where_clause}" unless where_clause.length == 0
  s="#{s}\ngroup by #{group_by_clause}" unless group_by_clause.length == 0
  s
end

#store_query ⇒ Object 



248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 248

def store_query
  save_query
  vsql="create or replace view #{@name.text} as #{sql}"
  begin
    res=ManqodDB.instance.manqod_db.client.query(vsql)
    ret="stored"
  rescue =>err
    ret="#{err}"
  end
  @result.set_label(ret)
end

#test_query ⇒ Object 



239
240
241
242
243
244
245
246
247
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 239

def test_query
  begin
    res=ManqodDB.instance.manqod_db.client.query(sql)
    ret="fields: #{res.num_fields}\nrows: #{res.num_rows}"
  rescue =>err
    ret="#{err}"
  end
  @result.set_label(ret)
end

#text ⇒ Object 



298
299
300
301
302
303
304
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 298

def text
  if @query_id
    "#{@query_id}"
  else
    ""
  end
end

#to_s ⇒ Object 



357
358
359
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 357

def to_s
  inspect
end

#update(new_value = , notifier = nil) ⇒ Object 



188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 188

def update(new_value=item['default'],notifier=nil)
=begin
first argument:
  - new_value - for FormItem compatibility
  - notifier - we are RelationBuilder's observer
second argument
  subnotifier - if notification comes from RelationBuilder, this will be
    RField Button - pressed, so we create or remove qobject for it
    RTable - added or removed
=end

  case new_value.class.name
    when "String"
      item['default']=new_value
      run_events(item['id'],'form_item-BeforeUpdate')
      @rb.update(self)
      set_query_id(item['default'].to_i)
      run_events(item['id'],'form_item-AfterUpdate')
    when "RelationBuilder"
      if notifier.class.name == "RTable" || notifier.nil? #if nil then removed table
        #update base combo
        update_base_selector
      else
        qobject=qobject_by_link(notifier)
        if notifier.active?
          QObject.new(self,notifier) unless qobject
        else
          qobject.delete if qobject
        end
      end
    when "QBuilder"
      #we update ourself
    else
      ewarn("notifier: #{new_value.class.name}","relation-builder")
  end
  @qobjects.each{|qobject| qobject.update}
  test_query if @autotest.active?
  show_all
end

#update_base_selector ⇒ Object 



342
343
344
345
346
347
348
349
350
351
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 342

def update_base_selector
  exval=@base.active_iter[0].to_i unless @base.active_iter.nil?
  @base.model.clear
  rows("select id,name from tables order by name"){|row|
    iter=@base.model.append
    iter[0]=row["id"].to_i
    iter[1]=row["name"]
    @base.set_active_iter(iter) if iter[0].to_i == exval
  }
end

#where_clause ⇒ Object 



326
327
328
 
# File 'lib/FormHolder/Form/InputHolder/QBuilder.rb', line 326

def where_clause
  @where.buffer.text
end