Debian Codegraph @

scripts/ubiq

raw
1 #!/usr/local/bin/perl -w
2
3 =head1 NAME
4
5 ubiq
6
7 =head1 SYNOPSIS
8
9 ubiq [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL
10
11 =head1 DESCRIPTION
12
13 Example:
14 ubiq -d "dbi:Pg:dbname=mydb;host=localhost"\
15 "SELECT * FROM a NATURAL JOIN b"
16
17
18 =head1 ARGUMENTS
19
20 =cut
21
22
23
24 use strict;
25
26 use Carp;
27 use DBIx::DBStag;
28 use Data::Dumper;
29 use Getopt::Long;
30 use Curses::UI;
31
32 my $debug;
33 my $help;
34 my $dbname;
35 my $nesting;
36 my $show;
37 my $file;
38 my $user;
39 my $pass;
40 my $template_name;
41 my $where;
42 GetOptions(
43 "help|h"=>\$help,
44 "dbname|d=s"=>\$dbname,
45 "show"=>\$show,
46 "nesting|n=s"=>\$nesting,
47 "file|f=s"=>\$file,
48 "user|u=s"=>\$user,
49 "pass|p=s"=>\$pass,
50 "template|t=s"=>\$template_name,
51 "where|w=s"=>\$where,
52 );
53 if ($help) {
54 system("perldoc $0");
55 exit 0;
56 }
57
58 my @C =
59 (config=>[
60 setting=>[
61 name => 'output_format',
62 default => 'xml',
63 allowed => 'xml',
64 allowed => 'sxpr',
65 allowed => 'itext',
66 ],
67 ]);
68
69
70 my $config =
71 Data::Stag->unflatten(@C);
72
73 my %confset =
74 map {
75 $_->get_name => $_->get_default
76 } $config->get_setting;
77
78 my $sdbh =
79 DBIx::DBStag->new;
80 my $dbh;
81 my $resources = $sdbh->resources_list;
82 my $resources_hash = $sdbh->resources_hash;
83 my @dbresl = grep {$_->{type} eq 'rdb'} @$resources;
84 my @dbnames = (map {$_->{name}} @dbresl);
85 my $templates = [];
86 my $template;
87
88 if (1) {
89
90 my $cui = Curses::UI->new;
91
92 my $schema = '';
93 my $loc = '';
94 my $screen = 'select_db';
95 my %w = ();
96 my $varnames = [];
97 my %exec_argh = ();
98 my $qr_obj;
99
100 my $file_menu = [
101 { -label => 'Quit program', -value => sub {exit(0)} },
102 ];
103 my $select_menu =
104 [
105 { -label => 'Choose Database', -value => sub{select_screen('select_db')}},
106 { -label => 'Choose Template', -value => sub{select_screen('select_template')}},
107 { -label => 'Query', -value => sub{select_screen('query')}},
108 ];
109 my $menu = [
110 { -label => 'File', -submenu => $file_menu },
111 { -label => 'Select', -submenu => $select_menu },
112 ];
113
114 $cui->add('menu', 'Menubar', -menu => $menu);
115
116 my $w0 = $cui->add(
117 'w0', 'Window',
118 -border => 1,
119 -y => -1,
120 -height => 3,
121 );
122
123 $w0->add('explain', 'Label',
124 -text => "CTRL+P: previous demo CTRL+N: next demo "
125 . "CTRL+X: menu CTRL+Q: quit"
126 );
127
128 my $w1 = $cui->add(
129 'w1', 'Window',
130 -border => 1,
131 -y => -4,
132 -height => 5,
133 );
134 $w1->add('label_varwin', 'Label',
135 -text => "ho\nho",
136 -width => 60,
137 -height => 3,
138 );
139
140 my $w2 = $cui->add(
141 'w2', 'Window',
142 -border => 1,
143 -y => -9,
144 -height => 6,
145 );
146 $w2->add('label_detailwin', 'Label',
147 -text => "hello",
148 -width => 60,
149 -height => 4,
150 -wrapping=>1,
151 );
152
153 my $button_template =
154 {
155 -label => '[ Select a template ]',
156 -value => 'template',
157 -onpress=> sub {
158 select_screen('select_template');
159 },
160 };
161
162 my $button_query =
163 {
164 -label => '[ Create Query ]',
165 -value => 'query',
166 -onpress=> sub {
167 select_screen('query');
168 },
169 };
170
171 my $button_exec_query =
172 {
173 -label => '[ Execute Query ]',
174 -value => 'exec_query',
175 -onpress=> sub {
176 execute_query();
177 },
178 };
179
180 my %args = (
181 -border => 1,
182 -titlereverse => 0,
183 -padtop => 2,
184 -padbottom => 14,
185 -ipad => 1,
186 );
187
188 $w{select_db} = $cui->add(
189 'window_select_db', 'Window',
190 -title => "Select Database",
191 %args
192 );
193
194 my $chooser_select_db =
195 $w{select_db}->add(
196 'chooser_select_db', 'Listbox',
197 -height => 10,
198 -values => [sort @dbnames],
199 -vscrollbar => 'right',
200 -onchange=> sub {
201 # my $pop = shift->parent->getobj('chooser_select_db');
202 set_dbname(shift->get);
203 # print "D=$dbname\n";
204 return;
205 },
206 );
207 # $chooser_select_db->set_binding(sub {
208 # my $pop = shift;
209 # my $db = $pop->get;
210 # my $res = $resources_hash->{$db};
211 # if ($res) {
212 # $cui->dialog("DB: $db\n".
213 # "LOC: $res->{loc}\n".
214 # "SCHENA: $res->{schema}\n");
215 # }
216 # else {
217 # $cui->dialog("No data for $db");
218 # }
219 # },
220 # '?');
221 $w{select_db}->add(undef, 'Buttonbox',
222 -y => -3,
223 -buttons => [
224 $button_template,
225 $button_query,
226 ]
227 );
228
229 $w{select_template} = $cui->add(
230 'window_select_template', 'Window',
231 -title => "Select Template",
232 %args
233 );
234
235 my $chooser_select_template =
236 $w{select_template}->add(
237 'chooser_select_template', 'Listbox',
238 -y=>5,
239 -height => 8,
240 -values => [1, 2],
241 -onchange=> sub {
242 my $pop = shift;
243 set_template($pop->get);
244 return;
245 },
246 );
247 $chooser_select_template->set_binding(\&dialog_template, '?');
248
249 $w{select_template}->add(undef, 'Buttonbox',
250 -y => -3,
251 -buttons => [
252 $button_query,
253 ]
254 );
255 $w{query} = $cui->add(
256 'window_query', 'Window',
257 -title => "Query Database Using Template",
258 %args
259 );
260
261 $w{query} = $cui->add(
262 'window_exec_query', 'Window',
263 -title => "Query Results",
264 %args
265 );
266
267 $w{query}->add(undef, 'Buttonbox',
268 -y => -3,
269 -buttons => [
270 $button_exec_query,
271 ]
272 );
273 $w{qr} = $cui->add(
274 'window_qr', 'Window',
275 -title => "Query Results",
276 %args
277 );
278
279
280 $w{select_db}->focus;
281 $cui->set_binding( sub{ exit }, "\cQ" );
282 # Bind <CTRL+X> to menubar.
283 $cui->set_binding( sub{ shift()->root->focus('menu') }, "\cX" );
284
285 if ($dbname) {
286 set_dbname($dbname);
287 }
288 if ($template_name) {
289 set_template($template_name);
290 }
291
292 update_varwin();
293 setup_query_options();
294
295 $cui->MainLoop;
296 $cui->dialog("ubiq quitting!");
297
298 sub set_template {
299 my $tn = shift;
300 $template = $sdbh->find_template($tn);
301 if ($template) {
302 $varnames = $template->get_varnames;
303 update_varwin();
304 setup_query_options();
305 $w{query}->intellidraw;
306 my $detail = $w2->getobj('label_detailwin');
307 my $sp = $template->stag_props;
308 my $desc = $sp->get_desc;
309 $detail->text($desc);
310 $w2->intellidraw;
311 }
312 else {
313 $cui->dialog("no such template $tn");
314 }
315 update_varwin();
316 }
317
318 sub set_dbname {
319 my $set = shift;
320 $dbname = $set;
321 my $res = $resources_hash->{$dbname};
322 if ($res) {
323 $schema = $res->{schema} || '';
324 $loc = $res->{loc} || '';
325 if ($schema) {
326 $templates = $sdbh->find_templates_by_schema($schema);
327 }
328 else {
329 $templates = $sdbh->template_list;
330 }
331 }
332 else {
333 $cui->dialog("Unknown $dbname");
334 }
335 $dbh = DBIx::DBStag->connect($dbname);
336 my $w = $cui->getobj('window_select_template');
337 my $chooser = $w->getobj('chooser_select_template');
338 my @tnames = map {$_->name} @$templates;
339 $chooser->values(\@tnames);
340 update_varwin();
341 }
342 sub update_varwin {
343 my $label = $w1->getobj('label_varwin');
344
345 $label->text(sprintf("DBNAME:%-20s SCHEMA:%-20s\nLOC:%-20s TMPL:%-20s",
346 $dbname || '', $schema, $loc,
347 $template ? $template->name : ''));
348 $w1->intellidraw;
349 }
350 sub setup_query_options {
351 for (my $i=0; $i<@$varnames; $i++) {
352 my $y = $i;
353 my $vn = $varnames->[$i];
354 # replace_widg($w{query},
355 # "query_label$i", 'TextViewer',
356 # -readonly => 1,
357 # -singleline => 1,
358 # -x => 1,
359 # -y => $y,
360 # -text => $vn,
361 # -width => 20,
362 # -sbborder => 1,
363 # );
364 my $label =
365 replace_widg($w{query},
366 "query_label$i", 'Label',
367 -x => 1,
368 -y => $y,
369 -width => 40,
370 -height => 1,
371 -bold => 1,
372 );
373 $label->text($vn);
374
375 replace_widg($w{query},
376 "query_val_popup$i", 'TextEntry',
377 -x => 42,
378 -y => $y,
379 -width => 30,
380 -sbborder => 1,
381 -onchange=> sub {
382 my $v = shift->get;
383 $exec_argh{$vn} = $v;
384 if (!$v) {
385 delete $exec_argh{$vn};
386 }
387 }
388 );
389
390 }
391 }
392 sub execute_query {
393 $qr_obj =
394 $dbh->selectall_stag(-template=>$template,
395 -bind=>\%exec_argh);
396 show_qr();
397 }
398 sub show_qr {
399 my $txt = $qr_obj->xml;
400 open(F, ">z");
401 print F $txt;
402 close(F);
403 # $txt = substr($txt, 0, 100);
404 replace_widg($w{qr},
405 "qr_textviewer", "TextViewer",
406 -sbborder=>1,
407 # -text=>"x\ny\n z\n 123\n",
408 -text=>$txt,
409 -wrapping=>1,
410 -showoverflow=>0,
411 );
412 $w{qr}->draw;
413 $w{qr}->focus;
414 }
415 sub setup_complex_query_options {
416 for (my $i=0; $i<10; $i++) {
417 my $y = $i;
418 replace_widg($w{cquery},
419 "cquery_att_popup$i", 'Popupmenu',
420 -y => $y,
421 -sbborder => 1,
422 -values => $varnames,
423 );
424
425 replace_widg($w{cquery},
426 "cquery_val_popup$i", 'TextEntry',
427 -x => 20,
428 -y => $y,
429 -width => 20,
430 -sbborder => 1,
431 );
432
433 replace_widg($w{cquery},
434 "cquery_bool_popup$i", 'Popupmenu',
435 -x => 50,
436 -y => $y,
437 -sbborder => 1,
438 -values => [ qw(AND OR) ],
439 );
440 }
441 }
442 sub replace_widg {
443 my $w = shift;
444 my $id = shift;
445 my @args = @_;
446 my $obj = $w->getobj($id);
447 if ($obj) {
448 $w->delete($id);
449 }
450 $w->add($id, @args);
451 return $w->getobj($id);
452 }
453
454 sub select_screen {
455 my $screen = shift;
456 $w{$screen}->focus;
457 }
458 sub dialog_template {
459 my $pop = shift;
460 my $tn = $pop->get_active_value;
461 my $t = $sdbh->find_template($tn);
462 if ($t) {
463 $cui->dialog("TEMPLATE: $tn\n");
464 }
465 else {
466 $cui->dialog("No data");
467 }
468 }
469 }