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