| 1 |
#!/usr/local/bin/perl -w |
| 2 |
|
| 3 |
BEGIN{ |
| 4 |
eval{do "dbenv.pl"}; |
| 5 |
die $@ if $@; |
| 6 |
}; # end of sub: |
| 7 |
|
| 8 |
use strict; |
| 9 |
use lib split(/:/, $ENV{STAGLIB} || ''); |
| 10 |
|
| 11 |
use IO::String; |
| 12 |
use DBIx::DBStag; |
| 13 |
use CGI qw/:standard/; |
| 14 |
use vars qw(%IS_FORMAT_FLAT $cscheme); |
| 15 |
|
| 16 |
#$ENV{DBSTAG_TRACE}=1; |
| 17 |
|
| 18 |
|
| 19 |
# -------------------------- |
| 20 |
# MAIN |
| 21 |
ubiq(); |
| 22 |
exit 0; |
| 23 |
# -------------------------- |
| 24 |
|
| 25 |
|
| 26 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 27 |
# ubiq |
| 28 |
# |
| 29 |
# This is the core function. It does everything |
| 30 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 31 |
sub ubiq { |
| 32 |
|
| 33 |
# ============================================= |
| 34 |
# DECLARE VARIABLES |
| 35 |
# note: the functions below are lexically closed |
| 36 |
# and can thus access these variables. |
| 37 |
# |
| 38 |
# if you're not familiar with closures you might |
| 39 |
# find this a bit confusing... |
| 40 |
# ============================================= |
| 41 |
%IS_FORMAT_FLAT = |
| 42 |
map {$_=>1} qw(flat-CSV flat-TSV flat-HTML-table); |
| 43 |
$cscheme = |
| 44 |
{ |
| 45 |
'keyword'=>'cyan', |
| 46 |
'variable'=>'magenta', |
| 47 |
'text' => 'reset', |
| 48 |
'comment' => 'red', |
| 49 |
'block' => 'blue', |
| 50 |
'property' => 'green', |
| 51 |
}; |
| 52 |
|
| 53 |
my $cgi = CGI->new; |
| 54 |
|
| 55 |
my $sdbh = |
| 56 |
DBIx::DBStag->new; |
| 57 |
|
| 58 |
# child dbh |
| 59 |
my $dbh; |
| 60 |
|
| 61 |
my $stag; |
| 62 |
my $res; |
| 63 |
my $schema; |
| 64 |
my $loc; |
| 65 |
my $templates = []; |
| 66 |
my $varnames = []; |
| 67 |
my $example_input = {}; |
| 68 |
my $options = {}; |
| 69 |
my $nesting = ''; |
| 70 |
my $rows; |
| 71 |
my $template; |
| 72 |
my $template_name = ''; |
| 73 |
my %exec_argh = (); |
| 74 |
my $resources = $sdbh->resources_list; |
| 75 |
my $resources_hash = $sdbh->resources_hash; |
| 76 |
my @dbresl = grep {$_->{type} eq 'rdb'} @$resources; |
| 77 |
my @dbnames = (map {$_->{name}} @dbresl); |
| 78 |
my $W = Data::Stag->getformathandler('sxpr'); |
| 79 |
my $ofh = \*STDOUT; |
| 80 |
my $format; |
| 81 |
my $dbname; |
| 82 |
my $errmsg = ''; |
| 83 |
|
| 84 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 85 |
# keep |
| 86 |
# |
| 87 |
# |
| 88 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 89 |
sub keep; # |
| 90 |
*keep = sub { |
| 91 |
join('&', |
| 92 |
map {"$_=".param(myescapeHTML($_))} grep {param($_)} qw(dbname template format save mode)); |
| 93 |
}; # end of sub: keep |
| 94 |
|
| 95 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 96 |
# url |
| 97 |
# |
| 98 |
# |
| 99 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 100 |
sub url; # |
| 101 |
*url = sub { |
| 102 |
my $base = shift; |
| 103 |
my %p = @_; |
| 104 |
%p = map { |
| 105 |
my $v = param($_); |
| 106 |
$p{$_} ? |
| 107 |
($_ => $p{$_}) : |
| 108 |
($v ? ($_=>$v) : ()); |
| 109 |
} (keys %p, qw(dbname template format save mode)); |
| 110 |
return "$base?". |
| 111 |
join('&', |
| 112 |
map {"$_=".$p{$_}} keys %p); |
| 113 |
}; # end of sub: url |
| 114 |
|
| 115 |
|
| 116 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 117 |
# conn |
| 118 |
# |
| 119 |
# |
| 120 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 121 |
sub conn; # |
| 122 |
*conn = sub { |
| 123 |
$dbh = DBIx::DBStag->connect($dbname) unless $dbh; |
| 124 |
}; # end of sub: conn |
| 125 |
|
| 126 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 127 |
# is_format_flat |
| 128 |
# |
| 129 |
# |
| 130 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 131 |
sub is_format_flat; # |
| 132 |
*is_format_flat = sub { |
| 133 |
# my $f = shift; |
| 134 |
$IS_FORMAT_FLAT{$format}; |
| 135 |
}; # end of sub: is_format_flat |
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 140 |
# |
| 141 |
# BASIC LAYOUT |
| 142 |
# |
| 143 |
# headers, footers, help, etc |
| 144 |
# |
| 145 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 146 |
|
| 147 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 148 |
# g_title |
| 149 |
# |
| 150 |
# |
| 151 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 152 |
sub g_title; # |
| 153 |
*g_title = sub { |
| 154 |
"U * B * I * Q"; |
| 155 |
}; # end of sub: g_title |
| 156 |
|
| 157 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 158 |
# short_intro |
| 159 |
# |
| 160 |
# |
| 161 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 162 |
sub short_intro; # |
| 163 |
*short_intro = sub { |
| 164 |
"This is the generic UBIQ interface"; |
| 165 |
}; # end of sub: short_intro |
| 166 |
|
| 167 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 168 |
# top_of_page |
| 169 |
# |
| 170 |
# |
| 171 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 172 |
sub top_of_page; # |
| 173 |
*top_of_page = sub { |
| 174 |
(h1(g_title), |
| 175 |
href("ubiq.cgi", "Ubiq"), |
| 176 |
' | ', |
| 177 |
href("ubiq.cgi?help=1", "Help"), |
| 178 |
br, |
| 179 |
href('#templates', '>>Templates'), |
| 180 |
br, |
| 181 |
short_intro, |
| 182 |
hr, |
| 183 |
); |
| 184 |
|
| 185 |
}; # end of sub: top_of_page |
| 186 |
|
| 187 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 188 |
# footer |
| 189 |
# |
| 190 |
# |
| 191 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 192 |
sub footer; # |
| 193 |
*footer = sub { |
| 194 |
(hr, |
| 195 |
href('http://stag.sourceforge.net'), |
| 196 |
br, |
| 197 |
myfont('$Id: ubiq.cgi,v 1.8 2004/04/12 18:23:10 cmungall Exp $x', |
| 198 |
(size=>-2)), |
| 199 |
); |
| 200 |
}; # end of sub: footer |
| 201 |
|
| 202 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 203 |
# |
| 204 |
# VIEW WIDGETS |
| 205 |
# |
| 206 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 207 |
|
| 208 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 209 |
# template_detail |
| 210 |
# |
| 211 |
# |
| 212 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 213 |
sub template_detail; # |
| 214 |
*template_detail = sub { |
| 215 |
my $templates = shift; |
| 216 |
my @tbls = |
| 217 |
map { |
| 218 |
my $io = IO::String->new; |
| 219 |
$_->show($io, $cscheme, \&htmlcolor); |
| 220 |
my $sr = $io->string_ref; |
| 221 |
('<a name="'.$_->name.'"', |
| 222 |
'template: ', |
| 223 |
em($_->name), |
| 224 |
table({-border=>1}, |
| 225 |
Tr( |
| 226 |
[td(["<pre>$$sr</pre>"])]))) |
| 227 |
} @$templates; |
| 228 |
return '<a name="templates">'.join("\n", @tbls); |
| 229 |
}; # end of sub: template_detail |
| 230 |
|
| 231 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 232 |
# stag_detail |
| 233 |
# |
| 234 |
# |
| 235 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 236 |
sub stag_detail; # |
| 237 |
*stag_detail = sub { |
| 238 |
# my $W = Data::Stag->getformathandler($format || 'sxpr'); |
| 239 |
# $stag->events($W); |
| 240 |
# my $out = $W->popbuffer; |
| 241 |
my $out = $stag->generate(-fmt=>$format); |
| 242 |
return resultbox($out); |
| 243 |
}; # end of sub: stag_detail |
| 244 |
|
| 245 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 246 |
# rows_detail |
| 247 |
# |
| 248 |
# |
| 249 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 250 |
sub rows_detail; # |
| 251 |
*rows_detail = sub { |
| 252 |
if ($format eq 'flat-HTML-table') { |
| 253 |
my $hdr = shift @$rows; |
| 254 |
h2('Results'). |
| 255 |
table({-border=>1, -bgcolor=>'yellow'}, |
| 256 |
Tr({}, |
| 257 |
[th([@$hdr]), |
| 258 |
map {td([map {colval2cell($_)} @$_])} @$rows])); |
| 259 |
} else { |
| 260 |
my $j = "\t"; |
| 261 |
if ($format eq 'flat-CSV') { |
| 262 |
$j = ','; |
| 263 |
} |
| 264 |
my $out = join("\n", |
| 265 |
map { |
| 266 |
join($j, |
| 267 |
map {escape($_, ("\n"=>'\n', $j=>"\\$j"))} @$_) |
| 268 |
} @$rows); |
| 269 |
resultbox($out); |
| 270 |
} |
| 271 |
}; # end of sub: rows_detail |
| 272 |
|
| 273 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 274 |
# query_results |
| 275 |
# |
| 276 |
# |
| 277 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 278 |
sub query_results; # |
| 279 |
*query_results = sub { |
| 280 |
( |
| 281 |
($stag ? stag_detail() : ''), |
| 282 |
($rows ? rows_detail() : ''), |
| 283 |
); |
| 284 |
}; # end of sub: query_results |
| 285 |
|
| 286 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 287 |
# |
| 288 |
# CHOOSERS |
| 289 |
# |
| 290 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 291 |
|
| 292 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 293 |
# template_chooser |
| 294 |
# |
| 295 |
# |
| 296 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 297 |
sub template_chooser; # |
| 298 |
*template_chooser = sub { |
| 299 |
#my $templates = shift; |
| 300 |
return |
| 301 |
table(Tr({-valign=>"TOP"}, |
| 302 |
[ |
| 303 |
map { |
| 304 |
my $is_selected = $_->name eq $template_name; |
| 305 |
my $h = {}; |
| 306 |
if ($is_selected) { |
| 307 |
$h = {bgcolor=>'red'} |
| 308 |
} |
| 309 |
my $desc = $_->desc; |
| 310 |
my $name = $_->name; |
| 311 |
my $nl = "\n"; |
| 312 |
$desc =~ s/\n/\<br\>/gs; |
| 313 |
td($h, |
| 314 |
[ |
| 315 |
href("#$name", '[scroll]'), |
| 316 |
# href("#$name", '[view]'), |
| 317 |
href(url('ubiq.cgi', (template=>$name)), |
| 318 |
strong($name)), |
| 319 |
$desc.hr, |
| 320 |
]) |
| 321 |
} @$templates, |
| 322 |
|
| 323 |
])); |
| 324 |
}; # end of sub: template_chooser |
| 325 |
|
| 326 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 327 |
# attr_settings |
| 328 |
# |
| 329 |
# |
| 330 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 331 |
sub attr_settings; # |
| 332 |
*attr_settings = sub { |
| 333 |
return unless $template; |
| 334 |
my @vals = (); |
| 335 |
my @popups = (); |
| 336 |
my @extra = (); |
| 337 |
|
| 338 |
my $basic_tbl = |
| 339 |
table(Tr({}, |
| 340 |
[ |
| 341 |
map { |
| 342 |
my $examples = ''; |
| 343 |
my $ei = $example_input->{$_} || []; |
| 344 |
while (length("@$ei") > 100) { |
| 345 |
pop @$ei; |
| 346 |
} |
| 347 |
if (@$ei) { |
| 348 |
$examples = " Examples: ".em(join(', ', @$ei)); |
| 349 |
} |
| 350 |
td([$_, textfield("attr_$_").$examples]) |
| 351 |
} @$varnames |
| 352 |
])); |
| 353 |
my $adv_tbl = |
| 354 |
table(Tr({}, |
| 355 |
[td([ |
| 356 |
join(br, |
| 357 |
"Override SQL SELECT:", |
| 358 |
textarea(-name=>'select', |
| 359 |
-cols=>80, |
| 360 |
), |
| 361 |
"Override SQL WHERE:", |
| 362 |
textarea(-name=>'where', |
| 363 |
-cols=>80, |
| 364 |
), |
| 365 |
"Override Full SQL Query:", |
| 366 |
textarea(-name=>'sql', |
| 367 |
-cols=>80, |
| 368 |
), |
| 369 |
"Use nesting hierarchy:", |
| 370 |
textarea(-name=>'nesting', |
| 371 |
-cols=>80, |
| 372 |
), |
| 373 |
) |
| 374 |
|
| 375 |
])])); |
| 376 |
|
| 377 |
|
| 378 |
return |
| 379 |
( |
| 380 |
hr, |
| 381 |
"Selected Template: ", |
| 382 |
strong($template_name), |
| 383 |
br, |
| 384 |
submit(-name=>'submit', |
| 385 |
-value=>'exectemplate'), |
| 386 |
$basic_tbl, |
| 387 |
$adv_tbl, |
| 388 |
# table({-border=>1}, |
| 389 |
# Tr({-valign=>"TOP"}, |
| 390 |
# [td([ |
| 391 |
|
| 392 |
# ])])), |
| 393 |
|
| 394 |
("Tree/Flat format: ", |
| 395 |
popup_menu(-name=>'format', |
| 396 |
-values=>[qw(sxpr itext XML nested-HTML flat-TSV flat-CSV flat-HTML-table)]), |
| 397 |
checkbox(-name=>'save', |
| 398 |
-value=>1, |
| 399 |
-label=>'Save Results to Disk'), |
| 400 |
' ', |
| 401 |
checkbox(-name=>'showsql', |
| 402 |
-value=>1, |
| 403 |
-label=>'Show SQL Statement'), |
| 404 |
), |
| 405 |
|
| 406 |
br, |
| 407 |
submit(-name=>'submit', |
| 408 |
-value=>'exectemplate'), |
| 409 |
hr); |
| 410 |
}; # end of sub: attr_settings |
| 411 |
|
| 412 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 413 |
# |
| 414 |
# SETTERS |
| 415 |
# |
| 416 |
# these set variables depending on users selections |
| 417 |
# |
| 418 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 419 |
|
| 420 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 421 |
# setdb |
| 422 |
# |
| 423 |
# |
| 424 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 425 |
sub setdb; # |
| 426 |
*setdb = sub { |
| 427 |
#$dbname = shift; |
| 428 |
return unless $dbname; |
| 429 |
msg("Set dbname to $dbname"); |
| 430 |
$res = $resources_hash->{$dbname}; |
| 431 |
if ($res) { |
| 432 |
$schema = $res->{schema} || ''; |
| 433 |
$loc = $res->{loc} || ''; |
| 434 |
msg("loc: $loc") if $loc; |
| 435 |
if ($schema) { |
| 436 |
$templates = $sdbh->find_templates_by_schema($schema); |
| 437 |
msg("schema: $schema"); |
| 438 |
} else { |
| 439 |
msg("schema not known; templates unrestricted"); |
| 440 |
$templates = $sdbh->template_list; |
| 441 |
} |
| 442 |
msg("Templates available: " . scalar(@$templates)); |
| 443 |
} else { |
| 444 |
warnmsg("Unknown $dbname"); |
| 445 |
} |
| 446 |
$res; |
| 447 |
}; # end of sub: setdb |
| 448 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 449 |
# settemplate |
| 450 |
# |
| 451 |
# |
| 452 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 453 |
sub settemplate; # |
| 454 |
*settemplate = sub { |
| 455 |
my $n = shift; |
| 456 |
my @matches = grep {$_->name eq $n} @$templates; |
| 457 |
die "looking for $n, got @matches" unless @matches == 1; |
| 458 |
$template = shift @matches; |
| 459 |
$varnames = $template->get_varnames; |
| 460 |
conn; |
| 461 |
my $cachef = "./cache/cache-$dbname-$n"; |
| 462 |
$example_input = $template->get_example_input($dbh, |
| 463 |
$cachef, |
| 464 |
1); |
| 465 |
system("chmod 777 $cachef"); |
| 466 |
$template_name = $n; |
| 467 |
}; # end of sub: settemplate |
| 468 |
|
| 469 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 470 |
# resultbox |
| 471 |
# |
| 472 |
# |
| 473 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 474 |
sub resultbox; # |
| 475 |
*resultbox = sub { |
| 476 |
my $out = shift; |
| 477 |
if (param('save')) { |
| 478 |
return $out; |
| 479 |
} |
| 480 |
h2('Results'). |
| 481 |
table({-border=>1}, |
| 482 |
Tr({}, |
| 483 |
td({bgcolor=>"yellow"},["<pre>$out</pre>"]))); |
| 484 |
}; # end of sub: resultbox |
| 485 |
|
| 486 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 487 |
# msg |
| 488 |
# |
| 489 |
# |
| 490 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 491 |
sub msg; # |
| 492 |
*msg = sub { |
| 493 |
}; # end of sub: msg |
| 494 |
|
| 495 |
|
| 496 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 497 |
# htmlcolor |
| 498 |
# |
| 499 |
# |
| 500 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 501 |
sub htmlcolor; # |
| 502 |
*htmlcolor = sub { |
| 503 |
my $c = shift; |
| 504 |
if ($c eq 'reset') { |
| 505 |
'</font>'; |
| 506 |
} else { |
| 507 |
"<font color=\"$c\">"; |
| 508 |
} |
| 509 |
}; # end of sub: htmlcolor |
| 510 |
|
| 511 |
|
| 512 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 513 |
# display_helppage |
| 514 |
# |
| 515 |
# |
| 516 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 517 |
sub display_helppage; # |
| 518 |
*display_helppage = sub { |
| 519 |
print(header, |
| 520 |
start_html("UBIQ: Instructions"), |
| 521 |
h1("UBIQ: Instructions for use"), |
| 522 |
|
| 523 |
h3("What is this?"), |
| 524 |
p("UBIQ is a generic interface to any relational database.", |
| 525 |
"It allows web-based queries either through SQL or an", |
| 526 |
"extensible set of",strong("SQL Templates"), |
| 527 |
"which must be defined for the database of interest."), |
| 528 |
p("UBIQ will take the SQL query results and make a", |
| 529 |
strong("hierarchical"), "data structure which is displayed", |
| 530 |
"in a format such as XML or indented text"), |
| 531 |
p("This is achieved using the ", |
| 532 |
href("http://stag.sourceforge.net", "DBStag"), |
| 533 |
"perl module"), |
| 534 |
p("UBIQ is intended for advanced, generic queries.", |
| 535 |
"If you want user-friendly queries you should use an", |
| 536 |
"interface that has been custom-designed for the database", |
| 537 |
"you are interested in."), |
| 538 |
|
| 539 |
h3("Using UBIQ"), |
| 540 |
p("First of all select the database of interest and", |
| 541 |
"click 'selectdb'. (There may only be one database,", |
| 542 |
"in which case you can skip this part)."), |
| 543 |
p("Next, choose a template from the list available for", |
| 544 |
"That database. Each template should have a description of", |
| 545 |
"what kind of query it is. You can also scroll down to the full", |
| 546 |
"SQL Template definition. For a description of the SQL Template", |
| 547 |
"syntax, see", |
| 548 |
href("http://stag.sourceforge.net", "Stag Documentation"), |
| 549 |
), |
| 550 |
p("After you have selected a template, you can paste in settings", |
| 551 |
"for template attributes.", |
| 552 |
"The character '*' gets treated as a wildcard.", |
| 553 |
), |
| 554 |
|
| 555 |
p("You can now choose a format for the results.", |
| 556 |
"Most of the formats are ", strong("hierarchical."), |
| 557 |
"if a hierarchical format is selected, then UBIQ will", |
| 558 |
"perform a transformation on the flat, tabular query results", |
| 559 |
"and build a tree-type structure that should reflect the", |
| 560 |
"natural organisation of the data."), |
| 561 |
p("Hierarchical formats are XML, sxpr (Lisp S-Expressions),", |
| 562 |
"itext (indented text)."), |
| 563 |
p("Non-hierarchical formats are tables of comma/tab seperated fields,", |
| 564 |
"which can optionally formatted into an HTML table"), |
| 565 |
p("You can also choose to see the actual SQL that gets executed"), |
| 566 |
p("When you have set the parameters, you can execute the template"), |
| 567 |
p(em("Note"), "As yet, UBIQ has no means of prioritising queries,", |
| 568 |
"it is possible to launcg queries that put a large load on the", |
| 569 |
"server, please be careful"), |
| 570 |
p( |
| 571 |
"If you receive an internal server error it probably means your query was terminated", |
| 572 |
"because it was not fully constrained. If this happens, pass in more constraints.", |
| 573 |
"DO NOT keep hitting reload - this will cause the database server to slow down.", |
| 574 |
"If this service becomes overloaded, it will have to be removed" |
| 575 |
), |
| 576 |
|
| 577 |
h3("Advanced use"), |
| 578 |
p("Yes, a SOAP interface would be nice. No plans as yet.", |
| 579 |
), |
| 580 |
|
| 581 |
p(href("ubiq.cgi", "Start UBIQ")), |
| 582 |
|
| 583 |
); |
| 584 |
}; # end of sub: display_helppage |
| 585 |
|
| 586 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 587 |
# display_htmlpage |
| 588 |
# |
| 589 |
# MAIN PAGE |
| 590 |
# |
| 591 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 592 |
sub display_htmlpage; # |
| 593 |
*display_htmlpage = sub { |
| 594 |
print( |
| 595 |
header, |
| 596 |
start_html(g_title), |
| 597 |
top_of_page, |
| 598 |
start_form(-action=>'ubiq.cgi', -method=>'GET'), |
| 599 |
|
| 600 |
# DATABASE SELECTION |
| 601 |
("Database", |
| 602 |
popup_menu(-name=>'dbname', |
| 603 |
-values=>[sort {$a cmp $b} @dbnames], |
| 604 |
-onChange=>"submit()", |
| 605 |
), |
| 606 |
submit(-name=>'submit', |
| 607 |
-value=>"selectdb")), |
| 608 |
|
| 609 |
# QUERY RESULTS - if present |
| 610 |
(query_results), |
| 611 |
|
| 612 |
# ERRORS - if present |
| 613 |
($errmsg), |
| 614 |
|
| 615 |
# ATTRIBUTE CHOOSER - if template is set |
| 616 |
(attr_settings(), |
| 617 |
($template ? template_detail([$template]) : ''), |
| 618 |
hr), |
| 619 |
|
| 620 |
# TEMPLATE CHOOSER |
| 621 |
(h3("Choose a template:"), |
| 622 |
template_chooser, |
| 623 |
hr), |
| 624 |
|
| 625 |
# TEMPLATES - all or just selected |
| 626 |
($template ? '' : template_detail($templates)), |
| 627 |
|
| 628 |
# PERSISTENT VARS |
| 629 |
hidden('template', param('template')), |
| 630 |
|
| 631 |
end_form, |
| 632 |
footer, |
| 633 |
); |
| 634 |
}; # end of sub: display_htmlpage |
| 635 |
|
| 636 |
# ================================ |
| 637 |
# |
| 638 |
# SETTING THINGS UP |
| 639 |
# |
| 640 |
# ================================ |
| 641 |
|
| 642 |
my @initfuncs = (); |
| 643 |
|
| 644 |
*add_initfunc = sub { |
| 645 |
push(@initfuncs, shift); |
| 646 |
}; |
| 647 |
|
| 648 |
add_initfunc(sub { |
| 649 |
$format = param('format') || 'sxpr'; |
| 650 |
$dbname = param('dbname'); |
| 651 |
if (@dbnames == 1) { |
| 652 |
# only one to choose from; autoselect |
| 653 |
$dbname = $dbnames[0]; |
| 654 |
} |
| 655 |
|
| 656 |
setdb; # sets $dbh |
| 657 |
|
| 658 |
# sets $template $varnames |
| 659 |
settemplate(param('template')) |
| 660 |
if param('template') && param('submit') ne 'selectdb'; |
| 661 |
|
| 662 |
# set variable bindings |
| 663 |
foreach (@$varnames) { |
| 664 |
my $v = param("attr_$_"); |
| 665 |
if ($v) { |
| 666 |
$v =~ s/\*/\%/g; |
| 667 |
$exec_argh{$_} = $v; |
| 668 |
} |
| 669 |
} |
| 670 |
}); |
| 671 |
|
| 672 |
if (-f 'ubiq-customize.pl') { |
| 673 |
eval `cat ubiq-customize.pl`; |
| 674 |
die $@ if $@; |
| 675 |
} |
| 676 |
|
| 677 |
|
| 678 |
$_->() foreach @initfuncs; |
| 679 |
|
| 680 |
# execute query |
| 681 |
if ($template && param('submit') eq 'exectemplate') { |
| 682 |
eval { |
| 683 |
conn(); |
| 684 |
|
| 685 |
my $no_query_params_set = !scalar(keys %exec_argh); |
| 686 |
if (param('where')) { |
| 687 |
$template->set_clause(where=>param('where')); |
| 688 |
$no_query_params_set = 0; |
| 689 |
} |
| 690 |
if (param('select')) { |
| 691 |
$template->set_clause(where=>param('select')); |
| 692 |
} |
| 693 |
if ($no_query_params_set) { |
| 694 |
$errmsg = h2("No Query Constraints Set"); |
| 695 |
} |
| 696 |
else { |
| 697 |
# kill killer queries |
| 698 |
my $tag = "kill$$"."TAG"; |
| 699 |
my $tagf = "/tmp/$tag"; |
| 700 |
my $t=time; |
| 701 |
print STDERR "Launched killer $tagf at $t\n"; |
| 702 |
system("touch $tagf && chmod 777 $tagf && sleep 15 && test -f $tagf && kill -9 $$ && rm $tagf &"); |
| 703 |
if (is_format_flat) { |
| 704 |
$rows = |
| 705 |
$dbh->selectall_rows( |
| 706 |
-template=>$template, |
| 707 |
-bind=>\%exec_argh, |
| 708 |
); |
| 709 |
} else { |
| 710 |
$stag = |
| 711 |
$dbh->selectall_stag( |
| 712 |
-template=>$template, |
| 713 |
-bind=>\%exec_argh, |
| 714 |
-nesting=>$nesting, |
| 715 |
); |
| 716 |
} |
| 717 |
# inactivate killer (killer only kills if $tagf is present) |
| 718 |
$t=time; |
| 719 |
print STDERR "Inactivated killer $tagf at $t\n"; |
| 720 |
system("rm $tagf &"); |
| 721 |
} |
| 722 |
}; |
| 723 |
if ($@) { |
| 724 |
my $err = $@; |
| 725 |
$errmsg = |
| 726 |
br.strong("Database Error:")."<pre>$err</pre>"; |
| 727 |
} |
| 728 |
} |
| 729 |
|
| 730 |
# WRITE HTML TO BROWSER |
| 731 |
if (param('save')) { |
| 732 |
# WRITE TO FILE |
| 733 |
print(header({-type=>"text/text"}), |
| 734 |
query_results); |
| 735 |
} |
| 736 |
if (param('help')) { |
| 737 |
# WRITE TO FILE |
| 738 |
display_helppage |
| 739 |
} |
| 740 |
else { |
| 741 |
# WRITE HTML |
| 742 |
display_htmlpage; |
| 743 |
|
| 744 |
} |
| 745 |
|
| 746 |
} |
| 747 |
|
| 748 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 749 |
# |
| 750 |
# CGI UTILITY FUNCTIONS |
| 751 |
# |
| 752 |
# %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% |
| 753 |
|
| 754 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 755 |
# href |
| 756 |
# |
| 757 |
# |
| 758 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 759 |
sub href { |
| 760 |
my $url = shift; |
| 761 |
my $n = shift || $url; |
| 762 |
"<a href=\"$url\">$n</a>"; |
| 763 |
} # end of sub: href |
| 764 |
|
| 765 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 766 |
# myfont |
| 767 |
# |
| 768 |
# |
| 769 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 770 |
sub myfont ($%) { |
| 771 |
my $str = shift; |
| 772 |
my %h = @_; |
| 773 |
sprintf("<font %s>$str</font>", |
| 774 |
join(' ', |
| 775 |
map {sprintf('%s="%s"', |
| 776 |
$_, $h{$_})} keys %h)); |
| 777 |
} # end of sub: myfont |
| 778 |
|
| 779 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 780 |
# escape |
| 781 |
# |
| 782 |
# escapes characters using a map |
| 783 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 784 |
sub escape ($@) { |
| 785 |
my $s = shift || ''; |
| 786 |
my %cmap = @_; |
| 787 |
$cmap{'\\'} = '\\\\'; |
| 788 |
my @from = keys %cmap; |
| 789 |
my @to = map{$cmap{$_}} @from; |
| 790 |
my $f = join('', @from); |
| 791 |
my $t = join('', @to); |
| 792 |
$s =~ tr/$f/$t/; |
| 793 |
$s; |
| 794 |
} # end of sub: escape |
| 795 |
|
| 796 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 797 |
# myescapeHTML |
| 798 |
# |
| 799 |
# |
| 800 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 801 |
sub myescapeHTML ($) { |
| 802 |
my $s = shift; |
| 803 |
return $s; |
| 804 |
} # end of sub: myescapeHTML |
| 805 |
|
| 806 |
|
| 807 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 808 |
# colval2cell |
| 809 |
# |
| 810 |
# |
| 811 |
# ++++++++++++++++++++++++++++++++++++++++++++++++++ |
| 812 |
sub colval2cell ($) { |
| 813 |
my $cell = shift; |
| 814 |
if (!defined($cell)) { |
| 815 |
return '<font color="red">NULL</font>'; |
| 816 |
} |
| 817 |
$cell; |
| 818 |
} # end of sub: colval2cell |
| 819 |
|