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