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
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