Debian Codegraph @

scripts/stag-qsh

raw
1 #!/usr/local/bin/perl
2
3 # stag-q
4 # [email protected]
5
6 use strict;
7 no strict qw(vars);
8 use Carp;
9 use DBIx::DBStag;
10 use Data::Stag qw(:all);
11 use Data::Dumper;
12 use Getopt::Long;
13 use FileHandle;
14 use Term::ANSIColor;
15
16 my $h = {};
17 $| = 1;
18
19 my $dbname = '';
20 my $connect;
21 my $term;
22 my @hist = ();
23 my $XTERMMODE = 0;
24 my $MLINEMODE = 0;
25 my $TBLMODE = 0;
26 my $PAGER = "less -fR";
27
28 my $cscheme =
29 {
30 'keyword'=>'cyan',
31 'variable'=>'magenta',
32 'text' => 'reset',
33 'comment' => 'red',
34 'block' => 'blue',
35 'property' => 'green',
36 };
37
38 my $dbimap;
39 my $schema;
40 GetOptions(
41 "dbname|d=s"=>\$dbname,
42 "connect|c"=>\$connect,
43 "dbimap=s"=>\$dbimap,
44 "schema|s=s"=>\$schema,
45 );
46 if ($dbimap) {
47 $ENV{DBSTAG_DBIMAP_FILE} = $dbimap;
48 }
49
50 # parent dbh
51 my $sdbh =
52 DBIx::DBStag->new;
53
54 # child dbh
55 my $dbh;
56
57 my $stag;
58 my $res;
59 my $loc;
60 my $templates = [];
61 my $varnames = [];
62 my $options = {};
63 my $nesting;
64 my $rows = [];
65 my $template;
66 my $template_name = '';
67 my %exec_argh = ();
68 my $resources = $sdbh->resources_list;
69 my $resources_hash = $sdbh->resources_hash;
70 my @dbresl = grep {$_->{type} eq 'rdb'} @$resources;
71 my @dbnames = (map {$_->{name}} @dbresl);
72 my $W = Data::Stag->getformathandler('sxpr');
73 my $ofh = \*STDOUT;
74 $W->fh($ofh);
75 $W->use_color(1);
76
77 if ($connect) {
78 db($dbname);
79 }
80 else {
81 setdb($dbname) if $dbname;
82 }
83
84 shell();
85 exit 0;
86
87
88 sub shell {
89 my $prompt = $ENV{STAG_SHELL} || "Stag[\$dbname]\$template_name> ";
90 my $quit = 0;
91 my @lines = ();
92 my $r;
93 my $rv;
94 my $outfh;
95 my $return;
96 my $echo;
97 my $line;
98
99 # welcome;
100 require Term::ReadLine;
101 require Shell;
102
103 welcome();
104
105 # checkoptions;
106 $term = shift || Term::ReadLine->new($prompt);
107
108 my $rcfile = "$ENV{HOME}/.stagshellrc";
109 if (-f $rcfile) {
110 open(F, $rcfile);
111 @lines = <F>;
112 close(F);
113 }
114
115 my $end_signal = ";";
116 while (!$quit) {
117 my $line;
118 if ($MLINEMODE) {
119 $line = '';
120 my $thisline =
121 $term->readline(prompt($prompt));
122 if ($thisline =~ /^\\/ || $thisline =~ /^\w$/ ||
123 $thisline =~ /^\w\s+/ || $thisline =~ /^\//) {
124 $line = $thisline;
125 }
126 else {
127 while (1) {
128 if($thisline !~ /(.*)$end_signal/) {
129 $line.= "$thisline\n";
130 }
131 else {
132 $line.= "$1\n";
133 last;
134 }
135 $thisline = $term->readline('- ');
136 }
137 }
138 }
139 else {
140 $line =
141 @lines ? shift @lines : $term->readline(prompt($prompt));
142 }
143 if ($line =~ /^\^/) {
144 $line =~ s/^\^//;
145 print "$prompt$line";
146 my $e = <STDIN>;
147 if ($e =~ /^q/) {
148 $line = "";
149 @lines = ();
150 }
151 }
152 if ($options->{echo} && $line !~ /[\+]wait/) {
153 if ($line =~ /^\#/) {
154 print "$line\n";
155 }
156 else {
157 print "$prompt$line\n";
158 }
159 if ($options->{sleep}) {
160 sleep $options->{sleep};
161 }
162 if ($options->{wait}) {
163 sleep $options->{wait};
164 }
165 }
166 my ($cmd, @w) = split(' ',$line);
167 my $rest = join(" ", @w);
168 $_ = $cmd;
169
170 addhist($line);
171
172 $line =~ s/\#[^\n]*\n*/\n/gs;
173 $line =~ s/^\s*select/:SELECT/i;
174
175 # : - sql tunnel and escape everything after in quotes
176 if ($line =~ /^:/) {
177 if ($line =~ /\!\s*$/) {
178 $line =~ s/\!\s*$//;
179 # interpolate
180 $line = eval("\"$line\"");
181 }
182 $line =~ s/^:/ sqlselect q\[/;
183 $line .= ']';
184 }
185 if ($line =~ /^\/\//) {
186 # $line =~ s/^\/\/(.*)/\@r = apph\-\>$1; print tree2xml\(\@r\)/;
187 }
188 if ($line =~ /^\//) {
189 # $line =~ s/^\//\@r = apph\-\>/;
190 }
191 if ($line =~ /^\\dd\s*(.*)/) {
192 my $arg = $1 ? "('$1')" : '';
193 $line = "dshowdbs $arg";
194 }
195 if ($line =~ /^\\d\s*(.*)/) {
196 my $arg = $1 ? "('$1')" : '';
197 $line = "showdbs $arg;";
198 }
199 if ($line =~ /^\\tt\s*(.*)/) {
200 my $arg = $1 ? "('$1')" : '';
201 $line = "dshowtemplates $arg;";
202 }
203 if ($line =~ /^\\t\s*(.*)/) {
204 my $arg = $1 ? "('$1')" : '';
205 $line = "showtemplates $arg;";
206 }
207 if ($line =~ /^\\v\s*(.*)/) {
208 my $arg = $1 ? "('$1')" : '';
209 $line = "showvars $arg;";
210 }
211 if ($line =~ /^d\s+(.*)/) {
212 $line = "db '$1';";
213 }
214 if ($line =~ /^w\s+(.*)/) {
215 $line = "writer '$1';";
216 }
217 if ($line =~ /^t\s+(.*)/) {
218 $line = "template '$1';";
219 }
220 if ($line =~ /^\/$/) {
221 ex();
222 next;
223 }
224 if ($line =~ /^\/\s*(.*)$/) {
225 ex(split(' ', $1));
226 next;
227 }
228 if ($line =~ /^\\l/) {
229 $MLINEMODE = !$MLINEMODE;
230 printf "MLINEMODE = %s\n", $MLINEMODE ? 'MULTI' : 'SINGLE';
231 $line = '';
232 }
233 if ($line =~ /^\\x/) {
234 $XTERMMODE = !$XTERMMODE;
235 printf "XTERMMODE = %s\n", $XTERMMODE ? 'ON' : 'OFF';
236 $line = '';
237 }
238 if ($line =~ /^\\r/) {
239 $TBLMODE = !$TBLMODE;
240 printf "TBLMODE = %s\n", $TBLMODE ? 'ON' : 'OFF';
241 $line = '';
242 }
243 if ($line =~ /^\\c/) {
244 showresourcesfile();
245 next;
246 }
247 if ($line =~ /^\\q/) {
248 $quit = 1;
249 last;
250 }
251 if ($line =~ /^(\w+)\s*=\s*(.*)/) {
252 $exec_argh{"$1"} = $2;
253 msg("SETTING $1 to $2");
254 next;
255 }
256 # ! - shell and escape everything after in quotes
257 if ($line =~ /^\!/) {
258 $line =~ s/^\!/sh q\[/;
259 $line .= ']';
260 }
261 # ? - show
262 if ($line =~ /^\?/) {
263 $line =~ s/^\?/help /;
264 }
265 # ? - show
266 if ($line =~ /^\#/) {
267 next;
268 }
269 # + is the set command
270 if ($line =~ /^\+/) {
271 $line =~ s/\+//;
272 $line = "set ".join(",", map {"q[$_]"} split(' ', $line));
273 }
274
275 # --- EXECUTE ---
276
277
278 print "Echo:$line\n" if $echo;
279 $rv = eval $line;
280
281 if ($@) {
282 print STDERR $@;
283 }
284 print "\n";
285 print "$rv\n" if $return;
286 if ($options->{sleep}) {
287 sleep $options->{sleep};
288 }
289 if ($options->{wait}) {
290 sleep $options->{wait};
291 $options->{wait} = 0;
292 }
293
294 }
295 }
296 sub trace {
297 $ENV{DBSTAG_TRACE} = !$ENV{DBSTAG_TRACE};
298 }
299 sub setdb {
300 $dbname = shift;
301 msg("Set dbname to $dbname");
302 $res = $resources_hash->{$dbname};
303 if ($res) {
304 $schema = $res->{schema};
305 $loc = $res->{loc} || '';
306 msg("loc: $loc") if $loc;
307 }
308 else {
309 warnmsg("Unknown $dbname");
310 }
311 if ($schema) {
312 $templates = $sdbh->find_templates_by_schema($schema);
313 msg("schema: $schema");
314 }
315 else {
316 msg("schema not known; templates unrestricted");
317 $templates = $sdbh->template_list;
318 }
319 msg("Templates available: " . scalar(@$templates));
320 $res;
321 }
322 sub db {
323 $dbname = shift;
324 if ($dbh) {
325 $dbh->disconnect;
326 }
327 eval {
328 $dbh = DBIx::DBStag->connect($dbname);
329 msg("Connected to $dbname");
330 setdb($dbname);
331 };
332 if ($@) {
333 print STDERR "Could not connect to database '$dbname'\n";
334 }
335 $dbh;
336 }
337 sub conn {
338 $dbname = shift if @_;
339 if (!$dbh) {
340 if (!$dbname) {
341 warnmsg("You need to set a database with 'd' first");
342 }
343 else {
344 db($dbname);
345 }
346 }
347 }
348 sub addhist {
349 my $line = shift;
350 next unless $line;
351 # $term->addhistory($line);
352 push(@hist, $line);
353 }
354 sub showhist {
355 print "$_\n" foreach @hist;
356 }
357 sub showdbs {
358 showfilter(shift, [sort @dbnames]);
359 }
360 sub dshowdbs {
361 my @N = filter(shift, \@dbnames);
362 my @R =
363 map {$resources_hash->{$_}} @N;
364 page(sub {
365 my $fh = shift;
366 my $r = shift @R;
367 return 0 unless $r;
368 printf $fh "%-20s %s\n", $r->{name}, hilite('keyword', $r->{schema});
369 return 1;
370 });
371 }
372 sub showfilter {
373 foreach my $item (filter(@_)) {
374 printf "$item\n";
375 }
376 }
377 sub writer {
378 $W = Data::Stag->getformathandler(shift);
379 $W->use_color(1);
380 $W->fh($ofh);
381 }
382 sub filter {
383 my $re = shift;
384 my $list = shift || [];
385 return
386 grep { !$re || $_ =~ /$re/ } @$list;
387 }
388 sub showresourcesfile {
389 `xterm -e less $ENV{DBSTAG_DBIMAP_FILE}`;
390 }
391 sub showtemplates {
392 if ($templates) {
393 showfilter(shift, [map {$_->name} @$templates]);
394 }
395 else {
396 warnmsg("no templates for $dbname");
397 }
398 }
399 sub dshowtemplates {
400 my @T = map {$sdbh->find_template($_)} filter(shift, [map {$_->name} @$templates]);
401 page(sub {
402 my $fh = shift;
403 my $t = shift @T;
404 return 0 unless $t;
405 my $n = $t->name;
406 my $hdr =
407 hilite('comment',
408 join("\n",
409 "+" x 60,
410 "++++ $n". (' ' x (50 - length($n))). "++++",
411 ("+" x 60),
412 "\n"));
413 my $ftr =
414 hilite('comment',
415 "// -- END OF TEMPLATE --\n". ("=" x 60));
416 print $fh $hdr;
417 $t->show($fh, $cscheme, sub { Term::ANSIColor::color(@_)});
418 print $fh $ftr;
419 print $fh "\n";
420 });
421 # my $fn;
422 # my $fh = \*STDOUT;
423 # ($fn, $fh) = opentmp();
424 # foreach (@t) {
425 # $_->show($fh, $cscheme);
426 # }
427 # $fh->close;
428 # if ($XTERMMODE) {
429 # my ($pfn, $pfh) = opentmp();
430 # print $pfh "more $fn && sleep 3600";
431 # system("xterm -e sh $pfn");
432 # $pfh->close;
433 # }
434 # else {
435 # system("more $fn");
436 # }
437 # unlink $fn;
438
439 }
440 sub page {
441 my $sub = shift;
442 my $fn;
443 my $fh = \*STDOUT;
444 ($fn, $fh) = opentmp();
445 while ($sub->($fh)) {
446 #
447 }
448 # print $fh "\n\L";
449 $fh->close;
450 if ($XTERMMODE) {
451 my ($pfn, $pfh) = opentmp();
452 print $pfh "$PAGER $fn";
453 $pfh->close;
454 system("xterm -e sh $pfn &");
455 sleep(1);
456 unlink $pfn;
457 }
458 else {
459 system("$PAGER $fn");
460 }
461 unlink $fn;
462 }
463 sub prompt {
464 my $p = shift;
465 $p =~ s/(\$\w+)/eval($1)/eg;
466 $p;
467 }
468 sub template {
469 my $n = shift;
470 my @matches = grep {$_->name eq $n} @$templates;
471 if (@matches) {
472 if (@matches > 1) {
473 msg("not set - these are the possibilities");
474 showfilter(undef, \@matches);
475 }
476 elsif (!@matches) {
477 warnmsg("No templates match: $n");
478 }
479 else {
480 $template = shift @matches;
481 $varnames = $template->get_varnames;
482 $template_name = $n;
483 msg("Set template to \"$n\"");
484 msg("varnames:");
485 %exec_argh = ();
486 showvars();
487 }
488 }
489 }
490 sub showvars {
491 foreach my $vn (@$varnames) {
492 printf("%-20s => %s\n",
493 hilite('keyword', $vn),
494 defined($exec_argh{$vn}) ?
495 hilite('variable', $exec_argh{$vn}) : ' - NOT SET - ');
496 }
497 }
498 sub ex {
499 my @args = @_;
500 my $bind = {%exec_argh};
501 if (@args) {
502 foreach my $arg (@args) {
503 if ($arg =~ /(\w+)=(\S+)/) {
504 $bind->{$1} = $2;
505 }
506 else {
507 $bind = [] unless ref($bind) eq 'ARRAY';
508 push(@$bind, $arg);
509 }
510 }
511 }
512 conn();
513 $stag =
514 $dbh->selectall_stag(-template=>$template,
515 -bind=>$bind,
516 -nesting=>$nesting,
517 );
518
519 showstag();
520 }
521 sub sqlselect {
522 my $sql = shift;
523 conn();
524 if ($TBLMODE) {
525 $rows =
526 $dbh->selectall_arrayref($sql);
527 if ($rows) {
528 showrows();
529 }
530 else {
531 print STDERR "Query failed\n";
532 }
533 }
534 else {
535 $stag =
536 $dbh->selectall_stag(-sql=>$sql,
537 -nesting=>$nesting,
538 );
539 showstag();
540
541 }
542 }
543 sub sql {
544 my $sql = shift;
545 $dbh->do($sql);
546 }
547 sub hilite {
548 my $cn = shift;
549 my $str = shift;
550 color($cscheme->{$cn}) . $str . color('reset');
551 }
552 sub showstag {
553 my @kids = $stag->kids;
554 if (!@kids) {
555 msg("NO DATA");
556 return;
557 }
558 page(sub {
559 my $fh = shift;
560 $W->fh($fh);
561 $stag->sax($W);
562 return 0;
563 });
564 }
565 sub showrows {
566 my @R = @$rows;
567 page(sub {
568 my $fh = shift;
569 my $r = shift @R;
570 return 0 unless $r;
571 my @C = values %$cscheme;
572 for (my $i=0; $i<@$r; $i++) {
573 printf $fh "%s%s\t", color($C[$i%3]), $r->[$i];
574 # printf $fh $r->[$i], "\t";
575 }
576 print $fh color('reset') . "\n";
577 return 1;
578 });
579 }
580 sub x {
581 print Dumper shift;
582 }
583 sub msg {
584 print "@_\n";
585 }
586 sub warnmsg {
587 print "WARNING:@_\n";
588 }
589 sub welcome {
590 msg("Hello. This is the command line interface to DBStag.");
591 msg("Type ? or help if you are ever confused.\n");
592 }
593 sub make_offering {
594 print "Thank you! You are very kind\n";
595 `xv /users/cjm/stag/stag-god.jpg`;
596 }
597 sub help {
598 my $s =
599 join("\n",
600 'COMMAND SUMMARY',
601 '===============',
602 ' ? displays this help message',
603 ' \d displays available databases',
604 ' \d MATCH available databases containing regexp MATCH',
605 ' \dd displays available databases - DETAILED',
606 ' \dd MATCH available databases containing regexp MATCH - DETAILED',
607 ' \t [MATCH] lists available templates (optionally matching MATCH regexp)',
608 ' \tt [MATCH] as above, but detailed view',
609 ' \x toggle pager - from shell to popup xterm',
610 ' \r toggle queryresult mode - rows vs trees',
611 ' \c show resources conf file',
612 ' d DBNAME set the database',
613 ' w WRITER set the tree writer (xml, sxpr or itext)',
614 ' t TEMPLATE set the template',
615 ' x VARIABLE show perl variable using Data::Dump',
616 ' trace toggle DBSTAG_TRACE',
617 ' \v show template variable bindings',
618 ' \l toggle multi/single line mode',
619 ' \q QUIT',
620 ' :SQL runs SQL',
621 ' / [ARGS] execute the template; bind using ARGS',
622 'INTRO',
623 'This is the dbstag interactive query shell',
624 'You can issue SQL queries or call dbstag parameterized',
625 'canned query templates here. This is also a perl interpreter',
626 '',
627 'TEMPLATED QUERIES',
628 'See DBIx::DBStag::SQLTemplate for more details',
629 'You can execute the current template using forward-slash',
630 'Use the t command to set a template by name',
631 '',
632 'Binding to template args:',
633 '',
634 'You can bind any template argument on the command line by saying',
635 'VARIABLE = VALUE',
636 '(spaces around the = are optional)',
637 ' you do not need a $ prefix',
638 ' you do not need quotes around the value',
639 '',
640 'you can also bind variables when you execute the query, like this',
641 '',
642 ' /VAR1=VAL1 VAR2=VAL2',
643 '',
644 'or sequentially:',
645 ' / VAL1 VAL2',
646 'single value:',
647 ' /VAL1',
648 'example with wildcard:',
649 ' /*foo*',
650 '',
651 'if you have already bound the variables using =, then just type "q"',
652 '',
653 "\n\n\n",
654 "Other possibilities",
655 "-------------------",
656 'SQL',
657 ' type in any SQL SELECT statement, and the results will be turned',
658 ' into a Stag tree and displayed as either xml, sxpr or itext',
659 ' the resulting tree goes into the $stag global variable',
660 ' You can also switch to table mode using the \\r toggle',
661 ' (in this case results go into the $rows variable)',
662 ' perl variable will not be interpolated by default - you',
663 ' can force interpolation by ending your SQL with !',
664 ' $x="foo"',
665 ' select * from bar where name=\'$x\'!',
666 '',
667 'Perl',
668 ' type in any perl and it will be evaluated. don\'t use my to declare',
669 ' variables though!',
670 '',
671 'Weird stuff',
672 '-----------',
673 ' the xterm pager acts kind of weird depending on the stag god\'s whims',
674 ' try running the same command again',
675 '',
676 'you will need xhost+ set if you want to use the xterm pager',
677 '',
678 'have fun and be careful out there',
679 );
680 page(sub {
681 my $fh = shift;
682 print $fh $s, "\n";
683 return 0;
684 });
685 }
686 our $ID = 0;
687 sub opentmp {
688 my $fn = "/tmp/stag-tmp-$ID-$$";
689 $ID++;
690 my $fh = new FileHandle "> $fn";
691 $fh || die($fn);
692 return ($fn, $fh);
693 }
694 sub closetmp {
695 my ($fn, $fh) = shift;
696 $fh->close || die($fn);
697 unlink($fn);
698 }
699
700 =head1 NAME
701
702 stag-qsh - DBStag query shell
703
704 =head1 SYNOPSIS
705
706 stag-qsh
707 help
708
709 =head1 DESCRIPTION
710
711 This is an interactive database query shell. For a full description,
712 execute this script and type 'help'
713
714 =head2 ARGUMENTS
715
716 =head3 -d B<DBNAME>
717
718 This is either a DBI locator or the logical name of a database in the
719 DBSTAG_DBIMAP_FILE config file
720
721 =cut
722