class QBuilder
this file is part of manqod manqod is distributed under the CDDL licence the author of manqod is Dobai-Pataky Balint(dpblnt@gmail.com)
Attributes
from[R]
groupby[R]
pc[R]
qobjects[R]
query_id[R]
rb[R]
select[R]
where[R]
Public Class Methods
new(pc)
click to toggle source
Calls superclass method
# 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
Public Instance Methods
base()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 316 def comment @comment.text end
group_by_clause()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 329 def group_by_clause @group_by.buffer.text end
inspect()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 353 def inspect "QBuilder" end
item()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 294 def item pc.item end
name()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 313 def name @name.text end
parentM()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 291 def parentM pc.parentM end
qobject_by_link(link)
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 298 def text if @query_id "#{@query_id}" else "" end end
to_s()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 357 def to_s inspect end
update(new_value=item['default'],notifier=nil)
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# File lib/FormHolder/Form/InputHolder/QBuilder.rb, line 326 def where_clause @where.buffer.text end