| 1 |
package Obj_Srvr; |
| 2 |
|
| 3 |
# =========================================================================== |
| 4 |
# Obj_Srvr.pm |
| 5 |
# |
| 6 |
# Serves live high-level data from a low level DB, using code instead of a |
| 7 |
# Data Warehouse. Will run embedded in obj_srvr sql parser. obj_srvr |
| 8 |
# passes the parsed sql to sub get_data() in Obj_Srvr.pm, which returns |
| 9 |
# the data in delimited character format. This data may be passed to |
| 10 |
# oracle_obj_srvr.pl which translates it into Net8 packets for the BI- |
| 11 |
# Query desktop client. |
| 12 |
# |
| 13 |
# obj_srvr.pm makes use of various Student Information System (SIS) perl |
| 14 |
# modules located on the wouprd server (currently Spruce). |
| 15 |
# |
| 16 |
# obj_srvr source is generated from obj_srvr.l and obj_srvr.y using lex and |
| 17 |
# yacc, then compiled and linked with exec_sql.o to produce the obj_srvr |
| 18 |
# executable (see Makefile). |
| 19 |
# |
| 20 |
# obj_srvr and Obj_Srvr.pm will be installed on the same server as the |
| 21 |
# wouprd database (currently Spruce). |
| 22 |
# |
| 23 |
# Jeremy Hickerson, 5/8/2002 |
| 24 |
# |
| 25 |
# =========================================================================== |
| 26 |
|
| 27 |
use strict; |
| 28 |
|
| 29 |
BEGIN { |
| 30 |
use Exporter (); |
| 31 |
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); |
| 32 |
|
| 33 |
# set the version for version checking |
| 34 |
$VERSION = 1.00; |
| 35 |
@ISA = qw(Exporter); |
| 36 |
@EXPORT = qw(&obj_srvr_connect &get_data &get_yyin &send_yyout |
| 37 |
&connect2client &like2re &tr_op); |
| 38 |
%EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ], |
| 39 |
our @EXPORT_OK = qw($FH_OUT); |
| 40 |
|
| 41 |
if ($^O eq "VMS") { |
| 42 |
# jhjh ( grep /gen\$com/i, @INC ) || unshift @INC, "gen\$com"; |
| 43 |
( grep /woup:\[wou_sis_mods.com\]/i, @INC ) || unshift @INC, "woup:[wou_sis_mods.com]"; # jhjh |
| 44 |
} |
| 45 |
# default to Unix |
| 46 |
else { |
| 47 |
( grep /\/usr\/local\/bin/, @INC ) || unshift @INC, "/\/usr\/local\/bin"; |
| 48 |
} |
| 49 |
|
| 50 |
$DBI::drh->{debug} = 1; |
| 51 |
|
| 52 |
} |
| 53 |
|
| 54 |
our @EXPORT_OK; |
| 55 |
|
| 56 |
use subs qw(obj_srvr_connect get_data get_yyin send_yyout connect2client like2re |
| 57 |
tr_op); |
| 58 |
|
| 59 |
use DBI; |
| 60 |
use WOU_Admit; |
| 61 |
use WOU_Person; |
| 62 |
use WOU_Student; |
| 63 |
use WOU_AR; |
| 64 |
use WOU_SIS_Util; |
| 65 |
use WOU_Util; |
| 66 |
use Socket; |
| 67 |
use Safe; |
| 68 |
|
| 69 |
|
| 70 |
# ============================================================================ |
| 71 |
# Package-Level Stuff |
| 72 |
# ============================================================================ |
| 73 |
|
| 74 |
my $DBH; # needs to be package level (this package's sub's assume this) |
| 75 |
our $FH_OUT; # let obj_srvr.pl see this |
| 76 |
my $FH_IN; |
| 77 |
|
| 78 |
my (%table_objs, %obj_accessor, %methods); # these are populated in |
| 79 |
# obj_srvr.tables |
| 80 |
|
| 81 |
# jhjh !! make sure obj_srvr.tables is readonly; it contains perl code to be eval'ed |
| 82 |
|
| 83 |
if ($^O eq "VMS") { |
| 84 |
eval `type obj_srvr.tables`; # table layout file |
| 85 |
} |
| 86 |
# default to Unix |
| 87 |
else { |
| 88 |
eval `cat obj_srvr.tables`; |
| 89 |
} |
| 90 |
|
| 91 |
my $compartment = new Safe; |
| 92 |
|
| 93 |
$compartment->permit(qw( entereval )); # need for stuff like date_compare() |
| 94 |
|
| 95 |
#$compartment->permit_only(qw()); # nothing! jhjh - need to see what to put |
| 96 |
# in here to allow what we need but nothing |
| 97 |
# else. Even without this it seems to stop |
| 98 |
# things like system(). |
| 99 |
|
| 100 |
$compartment->share_from('WOU_Util', [ 'date_compare' ] ); |
| 101 |
|
| 102 |
|
| 103 |
# ============================================================================ |
| 104 |
# routines |
| 105 |
# (will be embedded in a C Program) |
| 106 |
# ============================================================================ |
| 107 |
|
| 108 |
sub obj_srvr_connect { |
| 109 |
my ($uid, $passwd) = @_; |
| 110 |
|
| 111 |
print STDERR "before DBI->connect...\n"; # jhjh |
| 112 |
$DBH = DBI->connect('dbi:Oracle:', qq{$uid/$passwd\@(DESCRIPTION= |
| 113 |
(ADDRESS_LIST = |
| 114 |
(ADDRESS = |
| 115 |
(COMMUNITY = tcp.cedar.osshe.edu) |
| 116 |
(PROTOCOL = TCP) |
| 117 |
(HOST = 140.211.10.26) |
| 118 |
(PORT = 1541) |
| 119 |
) |
| 120 |
) |
| 121 |
(CONNECT_DATA = |
| 122 |
(SID = wouprd) |
| 123 |
(SRVR = DEDICATED) |
| 124 |
) ) }, "", {debug => 1} ) |
| 125 |
or die "$!: Can't connect to DB"; |
| 126 |
|
| 127 |
print STDERR "after DBI->connect...\n"; # jhjh |
| 128 |
|
| 129 |
# help performance of select with join on remote spriden table |
| 130 |
$DBH->do("alter session set optimizer_goal = ALL_ROWS"); |
| 131 |
|
| 132 |
# defaults - will be redirected if connected to oracle_obj_srvr |
| 133 |
$FH_IN = \*STDIN; |
| 134 |
$FH_OUT = \*STDOUT; |
| 135 |
|
| 136 |
} |
| 137 |
|
| 138 |
sub connect2client { |
| 139 |
my ($remote, $port) = @_; |
| 140 |
|
| 141 |
print STDERR "host = $remote, port = $port\n"; # jhjh |
| 142 |
|
| 143 |
my ($iaddr, $paddr, $proto, $line, $pid, $cnt); |
| 144 |
|
| 145 |
$| = 1; |
| 146 |
|
| 147 |
if ($port =~ /\D/) { $port = getservbyname($port, 'tcp') } |
| 148 |
die "No port" unless $port; |
| 149 |
|
| 150 |
$iaddr = inet_aton($remote) || die "no host: $remote"; |
| 151 |
$paddr = sockaddr_in($port, $iaddr); |
| 152 |
|
| 153 |
$proto = getprotobyname('tcp'); |
| 154 |
socket(SOCK, PF_INET, SOCK_STREAM, $proto) || die "socket: $!"; |
| 155 |
|
| 156 |
$cnt = 0; |
| 157 |
CONNECT_LOOP: |
| 158 |
while ($cnt++ < 10 ) { |
| 159 |
sleep(2); |
| 160 |
|
| 161 |
if (connect(SOCK, $paddr) ) { |
| 162 |
print STDERR "Connected\n"; |
| 163 |
last CONNECT_LOOP; |
| 164 |
} |
| 165 |
else { print STDERR "$!: problem with connect\n" } # jhjh |
| 166 |
|
| 167 |
( die "can't connect to lsnr") if $cnt == 10; |
| 168 |
} |
| 169 |
|
| 170 |
select(SOCK); $| = 1; # make unbuffered |
| 171 |
select(STDERR); $| = 1; # make unbuffered |
| 172 |
|
| 173 |
$FH_IN = \*SOCK; |
| 174 |
$FH_OUT = \*SOCK; |
| 175 |
|
| 176 |
select($FH_OUT); $| = 1; # make unbuffered |
| 177 |
select(STDOUT); |
| 178 |
|
| 179 |
return 1; |
| 180 |
|
| 181 |
} |
| 182 |
|
| 183 |
sub get_yyin { |
| 184 |
|
| 185 |
my $size = shift; |
| 186 |
|
| 187 |
my $yychars; |
| 188 |
|
| 189 |
sysread($FH_IN, $yychars, $size); |
| 190 |
print STDERR "read $yychars\n"; # jhjh |
| 191 |
|
| 192 |
return $yychars; |
| 193 |
} |
| 194 |
|
| 195 |
sub send_yyout { |
| 196 |
|
| 197 |
my $str = shift; |
| 198 |
|
| 199 |
print $FH_OUT $str; |
| 200 |
|
| 201 |
return 1; |
| 202 |
} |
| 203 |
|
| 204 |
sub get_data { |
| 205 |
|
| 206 |
my ($cols, $tab, $where, $order) = @_; |
| 207 |
|
| 208 |
$cols = lc($cols); |
| 209 |
$tab = lc($tab); |
| 210 |
$order = lc($order); |
| 211 |
# handle "where" separately; it may have upper-case scalars that we |
| 212 |
# need to preserve |
| 213 |
|
| 214 |
# oracle_obj_srvr may have broken query up into 254 byte chunks separated by \n |
| 215 |
$cols =~ s/\n//g; |
| 216 |
$tab =~ s/\n//g; |
| 217 |
$where =~ s/\n//g; |
| 218 |
$order =~ s/\n//g; |
| 219 |
|
| 220 |
|
| 221 |
my ($rh_driver_objs, $ra_results, $ra_support_results, $rh_result, $rh_data, |
| 222 |
$rh_join_obj, $ra_join_objs, $col, $table, $subname, @field_order, $rh_stu, |
| 223 |
$rh_support_objs, $parm, $ref_key, @subst_parms, @save_parms, |
| 224 |
%filtered_results, $cnt, @save_where, $word, $subst_where, $col_info, |
| 225 |
@converted_order, $special_sort, %special_sort); |
| 226 |
|
| 227 |
if (!defined($cols) ) { $cols = "" } |
| 228 |
if (!defined($tab) ) { $tab = "" } |
| 229 |
if (!defined($where) ) { $where = "" } |
| 230 |
if (!defined($order) ) { $order = "" } |
| 231 |
|
| 232 |
my %query = ( "columns" => [ ], |
| 233 |
"table" => [ ], |
| 234 |
"where" => [ ], |
| 235 |
"order" => [ ] ); |
| 236 |
|
| 237 |
$special_sort = 0; |
| 238 |
print STDERR "cols = $cols\n"; # jhjh |
| 239 |
print STDERR "tab = $tab\n"; # jhjh |
| 240 |
print STDERR "where = $where\n"; # jhjh |
| 241 |
|
| 242 |
@{$query{"columns"} } = split(/,/, $cols); |
| 243 |
($query{"table"} ) = split(/,/, $tab); |
| 244 |
|
| 245 |
$where =~ s/([^\\]),/$1\|/g; # save escaped comma's |
| 246 |
@{$query{"where"} } = split(/\|/, $where); |
| 247 |
@{$query{"order"} } = split(/,/, $order); |
| 248 |
|
| 249 |
# convert numeric col refs to names |
| 250 |
if ( defined($query{order}->[0] ) ) { |
| 251 |
if ( $query{order}->[0] =~ /^\d+$/ ) { |
| 252 |
print STDERR "converting numeric col refs\n"; # jhjh |
| 253 |
while ( $col = shift @{ $query{order} } ) { |
| 254 |
push @converted_order, $query{columns}->[$col - 1]; |
| 255 |
print STDERR "$col: ", $query{columns}->[$col - 1], "\n"; # jhjh |
| 256 |
} |
| 257 |
push @{ $query{order} }, @converted_order; |
| 258 |
} |
| 259 |
|
| 260 |
# see if special sorts are needed |
| 261 |
foreach $col ( @{ $query{order} } ) { |
| 262 |
|
| 263 |
if (exists($obj_accessor{ $query{table} }->{$col}->{datatype}) ) { |
| 264 |
if ($obj_accessor{ $query{table} }->{$col}->{datatype} |
| 265 |
eq "numeric" ) { |
| 266 |
|
| 267 |
$special_sort = 1; |
| 268 |
$special_sort{$col} = \&num_sort; # defined in WOU_Util.pm |
| 269 |
} |
| 270 |
if ($obj_accessor{ $query{table} }->{$col}->{datatype} |
| 271 |
eq "date" ) { |
| 272 |
|
| 273 |
$special_sort = 1; |
| 274 |
$special_sort{$col} = \&date_sort; # defined in WOU_Util.pm |
| 275 |
} |
| 276 |
} |
| 277 |
|
| 278 |
# can add check for descending sort, etc. below if needed |
| 279 |
} |
| 280 |
} |
| 281 |
|
| 282 |
print STDERR "order = $order :", @{ $query{order} }, "\n"; # jhjh |
| 283 |
|
| 284 |
$rh_driver_objs = get_driver_objs(\%query); # rh_driver_objs now has |
| 285 |
# one or more sub refs as |
| 286 |
# hash keys and a hash ref |
| 287 |
# holding the sub ref in key |
| 288 |
# "sub" and an array ref of |
| 289 |
# args for the sub in key |
| 290 |
# "parms" |
| 291 |
$rh_support_objs = get_support_objs(\%query); |
| 292 |
|
| 293 |
# add any field_maps for all columns returned by supporting subs, even |
| 294 |
# if columns are not in "select" statement (they might be in "where" clause) |
| 295 |
add_field_maps($rh_support_objs, $query{table} ); |
| 296 |
|
| 297 |
# run the driver sub (Only allow 1 driver table) |
| 298 |
push @{$ra_results}, @{ &{ $rh_driver_objs->{"subref"} }( |
| 299 |
@{ $rh_driver_objs->{"parms"} }) |
| 300 |
}; |
| 301 |
|
| 302 |
# run the supporting subs |
| 303 |
foreach $rh_stu ( @{$ra_results} ) { |
| 304 |
|
| 305 |
foreach $subname (keys %{$rh_support_objs} ) { |
| 306 |
|
| 307 |
while ( shift @subst_parms ) { } # empty each time |
| 308 |
|
| 309 |
# substitute driver table column values for referential parms |
| 310 |
while ( $parm = shift @{ $rh_support_objs->{$subname}->{"parms"} } ) { |
| 311 |
|
| 312 |
push @save_parms, $parm; |
| 313 |
|
| 314 |
if ($parm =~ /^\$/ ) { |
| 315 |
|
| 316 |
$ref_key = $parm; |
| 317 |
$ref_key =~ s/^\$//; |
| 318 |
|
| 319 |
if (exists($rh_stu->{ $ref_key } ) ) { |
| 320 |
push @subst_parms, $rh_stu->{$ref_key}; |
| 321 |
} |
| 322 |
else { # error (can't find as driver field): leave "$pidm" |
| 323 |
# or whatever as parm |
| 324 |
push @subst_parms, $parm; |
| 325 |
} |
| 326 |
} |
| 327 |
else { |
| 328 |
push @subst_parms, $parm; |
| 329 |
} |
| 330 |
} |
| 331 |
|
| 332 |
# restore parms, including "$" parms |
| 333 |
while ( $parm = shift @save_parms ) { |
| 334 |
|
| 335 |
push @{ $rh_support_objs->{$subname}->{"parms"} }, $parm; |
| 336 |
|
| 337 |
} |
| 338 |
|
| 339 |
add2hash($rh_stu, |
| 340 |
\%{ &{ $rh_support_objs->{$subname}->{"subref"} }( |
| 341 |
@subst_parms) }, |
| 342 |
$rh_support_objs->{$subname}->{"field_map"} ); |
| 343 |
} |
| 344 |
|
| 345 |
} |
| 346 |
|
| 347 |
|
| 348 |
# Explanation: |
| 349 |
# 1. Will translate where-clause into perl expression, then use reval |
| 350 |
# to check for TRUE after all column values have been plugged in. |
| 351 |
# This will involve implementing all SQL predicate operators |
| 352 |
# (comparison, between, like, in, etc.) in (or for) obj_srvr.pl. |
| 353 |
# Some of these need no translation (most of the comparison operators |
| 354 |
# mean the same thing in perl, for instance.) |
| 355 |
|
| 356 |
# 3 passes through entire population so far... |
| 357 |
$cnt = 0; |
| 358 |
# we have substituted some words in $query{where} while we were getting params; |
| 359 |
# copy @{ $query{where} } onto $where putting spaces between words |
| 360 |
$where = ""; push @save_where, @{ $query{where} }; |
| 361 |
while ( defined($word = shift @{ $query{where} } ) ) { $where .= $word . " " } |
| 362 |
$where =~ s/\s*$//; |
| 363 |
push @{ $query{where} }, @save_where; |
| 364 |
|
| 365 |
$where =~ s/([^\\])'/$1"/g; # allow escaped single quotes to stay |
| 366 |
$where =~ s/\\//g; # remove escape char's, now that we're done w/ them |
| 367 |
$where =~ s/^/ /; # put space at beginning, makes substitution below |
| 368 |
# work for first word |
| 369 |
print STDERR "WHERE = ", $where, "\n"; # jhjh |
| 370 |
while ( $rh_stu = shift @{$ra_results} ) { |
| 371 |
|
| 372 |
$subst_where = $where; # reset |
| 373 |
|
| 374 |
# substitute column vals into where expression in order to evaluate |
| 375 |
# (only subst if word is delimited by spaces or reg. exp. slashes) |
| 376 |
foreach $col (keys %{$rh_stu} ) { |
| 377 |
$col = lc($col); # all hash column names are lower case, so |
| 378 |
# need this to make sure substitution references |
| 379 |
# the actual column name and not the capitalized |
| 380 |
# hash column name. Still need to do case-insensitive |
| 381 |
# substitution below because the query column name |
| 382 |
# may be upper case. |
| 383 |
|
| 384 |
# turn nulls into null strings |
| 385 |
if (!defined($rh_stu->{$col} ) ) { |
| 386 |
if (exists($obj_accessor{ $query{table} }->{$col}->{datatype}) and |
| 387 |
$obj_accessor{ $query{table} }->{$col}->{datatype} |
| 388 |
eq "numeric" ) { |
| 389 |
$rh_stu->{$col} = 0; |
| 390 |
} |
| 391 |
else { $rh_stu->{$col} = "" } |
| 392 |
} |
| 393 |
$subst_where =~ s/ $col / "$rh_stu->{$col}" /ig; # /i handles upper- |
| 394 |
# case query col names |
| 395 |
$subst_where =~ s/\/\^$col\$\//\/\^$rh_stu->{$col}\$\//ig; |
| 396 |
} |
| 397 |
|
| 398 |
if (where_clause_true($subst_where) ) { |
| 399 |
$filtered_results{++$cnt} = $rh_stu; |
| 400 |
} |
| 401 |
|
| 402 |
} |
| 403 |
|
| 404 |
# jhjh push @field_order, @{$query{columns} }; |
| 405 |
|
| 406 |
if ($order) { |
| 407 |
if ($special_sort) { |
| 408 |
$ra_results = compound_sort(\%filtered_results, $query{"order"}, |
| 409 |
\%special_sort ); |
| 410 |
} |
| 411 |
else { |
| 412 |
$ra_results = compound_sort(\%filtered_results, $query{"order"} ); |
| 413 |
} |
| 414 |
} |
| 415 |
else { push @{ $ra_results }, values %filtered_results } |
| 416 |
|
| 417 |
|
| 418 |
foreach $col ( @{ $query{columns} } ) { |
| 419 |
$col_info .= |
| 420 |
$col . ":" . $obj_accessor{ $query{table} }->{$col}->{size} . "|"; |
| 421 |
} |
| 422 |
$col_info =~ s/\|$//; |
| 423 |
|
| 424 |
print $FH_OUT "$col_info\n"; |
| 425 |
|
| 426 |
output_delimited($FH_OUT, $ra_results, $query{columns}, "", ""); |
| 427 |
|
| 428 |
print $FH_OUT "\n$cnt rows returned\n"; |
| 429 |
|
| 430 |
return 1; |
| 431 |
|
| 432 |
} |
| 433 |
|
| 434 |
|
| 435 |
sub get_driver_objs { |
| 436 |
|
| 437 |
my $rh_query = shift; |
| 438 |
my %driver_objs = |
| 439 |
( "subname" => $table_objs{ $rh_query->{table} }->{subname}, |
| 440 |
"subref" => $table_objs{ $rh_query->{table} }->{subref}, |
| 441 |
"parms" => get_parms(0, $table_objs{ $rh_query->{table} }->{subname}, |
| 442 |
$rh_query) ); |
| 443 |
|
| 444 |
return \%driver_objs; |
| 445 |
} |
| 446 |
|
| 447 |
|
| 448 |
sub get_support_objs { |
| 449 |
|
| 450 |
my $rh_query = shift; |
| 451 |
|
| 452 |
my ($subname, %support_objs, $col); |
| 453 |
|
| 454 |
|
| 455 |
foreach $col (@{$rh_query->{columns} } ) { |
| 456 |
|
| 457 |
# next if column is a driver sub column |
| 458 |
next if $obj_accessor{$rh_query->{table} }->{$col}->{subname} eq "SELF"; |
| 459 |
|
| 460 |
print STDERR "support_objs: col = $col\n"; # jhjh |
| 461 |
print STDERR "support_objs->subname = ", $obj_accessor{ $rh_query->{table} }->{$col}->{subname}, "\n"; # jhjh |
| 462 |
|
| 463 |
# these get populated the same way multiple times if several fields share |
| 464 |
# a subname |
| 465 |
# Data looks like this: |
| 466 |
# $support_objs{"get_addr_lo"}->{"subref"} = \&get_addr, for example |
| 467 |
|
| 468 |
$support_objs{ $obj_accessor{ $rh_query->{table} }->{ |
| 469 |
$col}->{subname} }->{"subref"} = |
| 470 |
$obj_accessor{ $rh_query->{table} }->{$col}->{subref}; |
| 471 |
|
| 472 |
$support_objs{ $obj_accessor{ $rh_query->{table} }->{ |
| 473 |
$col}->{subname} }->{"parms"} = |
| 474 |
get_parms(1, |
| 475 |
$obj_accessor{$rh_query->{table} }->{$col}->{subname}, |
| 476 |
$rh_query); |
| 477 |
|
| 478 |
# jhjh - don't need this, done elsewhere now |
| 479 |
# This adds a new field_map pair each time. |
| 480 |
# Data looks like this: |
| 481 |
# $support_objs{"get_addr_lo"}->{"field_map}->{"city"} = "city_lo", |
| 482 |
# for example, where "city is the fieldname returned by the subref and |
| 483 |
# "city_lo" is the fieldname to be used in the virtual table being created. |
| 484 |
# $support_objs{ |
| 485 |
# $obj_accessor{ $rh_query->{table} }->{$col}->{subname} |
| 486 |
# }->{"field_map"}->{ |
| 487 |
# $obj_accessor{$rh_query->{table} }->{$col}->{field} |
| 488 |
# } = $col; |
| 489 |
|
| 490 |
} |
| 491 |
|
| 492 |
# maybe there's a column in the "where" clause but not in the select column |
| 493 |
# list, and it's sub is not shared with any of the select list columns |
| 494 |
foreach $col (keys %{ $obj_accessor{ $rh_query->{table} } } ) { |
| 495 |
|
| 496 |
if ( grep /^$col$/i, @{ $rh_query->{where} } and |
| 497 |
$obj_accessor{ $rh_query->{table} }->{$col}->{subname} ne "SELF" ) { |
| 498 |
|
| 499 |
# Data looks like this: |
| 500 |
# $support_objs{"get_addr_lo"}->{"subref"} = \&get_addr, for example |
| 501 |
|
| 502 |
$support_objs{ $obj_accessor{ $rh_query->{table} }->{ |
| 503 |
$col}->{subname} }->{"subref"} = |
| 504 |
$obj_accessor{ $rh_query->{table} }->{$col}->{subref}; |
| 505 |
|
| 506 |
$support_objs{ $obj_accessor{ $rh_query->{table} }->{ |
| 507 |
$col}->{subname} }->{"parms"} = |
| 508 |
get_parms(1, |
| 509 |
$obj_accessor{$rh_query->{table} }->{$col}->{subname}, |
| 510 |
$rh_query); |
| 511 |
} |
| 512 |
|
| 513 |
} |
| 514 |
|
| 515 |
return \%support_objs; |
| 516 |
|
| 517 |
} |
| 518 |
|
| 519 |
|
| 520 |
sub get_parms { |
| 521 |
|
| 522 |
my ($rec_key, $subname, $rh_query) = @_; |
| 523 |
|
| 524 |
my ($parm, @parms); |
| 525 |
|
| 526 |
foreach $parm (@{$methods{$subname}->{parms} } ) { |
| 527 |
push @parms, get_parm_val($rec_key, $parm, $rh_query); |
| 528 |
} |
| 529 |
|
| 530 |
return \@parms; |
| 531 |
} |
| 532 |
|
| 533 |
|
| 534 |
sub get_parm_val { |
| 535 |
|
| 536 |
my ($rec_key, $parm, $rh_query) = @_; |
| 537 |
|
| 538 |
my ($word, $got_word, @subst_where); |
| 539 |
|
| 540 |
if ($parm eq "dbh") { return $DBH } # package var |
| 541 |
|
| 542 |
if ($rec_key == 0 ) { # i.e. sub is the driver ("table"), so we require |
| 543 |
# params to be scalar predicates in where-clause. |
| 544 |
|
| 545 |
# for drivers ("tables") we require single-valued, "=" params. |
| 546 |
# we will handle "!=", "in", "like" values at a higher level and |
| 547 |
# simply run the sub multiple times (maybe?). |
| 548 |
|
| 549 |
$got_word = 0; |
| 550 |
while ( $word = shift @{$rh_query->{where} } ) { |
| 551 |
if ($got_word) { $got_word++ } |
| 552 |
|
| 553 |
|
| 554 |
if (lc($word) eq $parm) { # so any future parms must also be lc |
| 555 |
$got_word = 1; |
| 556 |
$word = "TRUE"; # replace driver parms with true statements; |
| 557 |
# reval of where clause doesn't need to |
| 558 |
# look at these again, and any "%" values |
| 559 |
# will wrongly fail the revel |
| 560 |
|
| 561 |
# jhjh ! May want to rethink TRUE = TRUE |
| 562 |
# idea: need to handle conditions other than |
| 563 |
# "=" on driver parm columns (like "in", "!="). |
| 564 |
# Would be good to pass this to the eval like |
| 565 |
# everything else. Have to think of another |
| 566 |
# way to get around parms that accept "=%". |
| 567 |
} |
| 568 |
|
| 569 |
if ($got_word == 3) { |
| 570 |
|
| 571 |
push @subst_where, "TRUE"; |
| 572 |
unshift @{ $rh_query->{where} }, @subst_where; |
| 573 |
$word =~ s/'//g; # don't want single quotes as part of # the string |
| 574 |
|
| 575 |
return $word; |
| 576 |
|
| 577 |
} # 2 is "=" |
| 578 |
|
| 579 |
push @subst_where, $word; |
| 580 |
} |
| 581 |
# won't be reached unless parm not in where clause |
| 582 |
unshift @{ $rh_query->{where} }, @subst_where; |
| 583 |
} |
| 584 |
|
| 585 |
|
| 586 |
else { # Params are referential: they come from the driver object. |
| 587 |
# (We are representing a single table to the user, but pulling |
| 588 |
# the data from a driver object and whatever supporting |
| 589 |
# subroutines we need.) If a supporting subroutine requires |
| 590 |
# a parameter that is not referential (like gpa_type for all_gpa), |
| 591 |
# we will create additional columns for the possible values. I.e., |
| 592 |
# column cgpa_o is gpa_type 'O' (overall), column cgpa_t is |
| 593 |
# gpa_type 'T' (transfer), etc. |
| 594 |
|
| 595 |
return $parm; # substitute after getting driver records |
| 596 |
|
| 597 |
# jhjh - still need to handle scalar where-clause conditions for |
| 598 |
# support obj columns - i.e. a required param for a support object |
| 599 |
# cannot be figured out referentially. Handle these with higher |
| 600 |
# level wrapper subs. |
| 601 |
|
| 602 |
# fall-through |
| 603 |
return; |
| 604 |
} |
| 605 |
|
| 606 |
} |
| 607 |
|
| 608 |
|
| 609 |
sub add_field_maps { |
| 610 |
|
| 611 |
my ($rh_support_objs, $table) = @_; |
| 612 |
|
| 613 |
my ($col, $subname); |
| 614 |
|
| 615 |
COL_LOOP: |
| 616 |
foreach $col (keys %{ $obj_accessor{$table} } ) { |
| 617 |
next COL_LOOP if $obj_accessor{$table}->{$col}->{"subname"} eq "SELF"; |
| 618 |
|
| 619 |
# fall-through |
| 620 |
foreach $subname ( keys %{ $rh_support_objs } ) { |
| 621 |
|
| 622 |
# only put it in if we used it |
| 623 |
if ($obj_accessor{$table}->{$col}->{"subname"} eq $subname ) { |
| 624 |
|
| 625 |
if ( exists( $obj_accessor{$table}->{$col}->{"field"} ) ) { |
| 626 |
|
| 627 |
# Data looks like this: |
| 628 |
# $support_objs{"get_addr_lo"}->{"field_map}->{ |
| 629 |
# "city"} = "city_lo", for example |
| 630 |
$rh_support_objs->{$subname}->{"field_map"}->{ |
| 631 |
$obj_accessor{$table}->{$col}->{"field"} } = $col; |
| 632 |
|
| 633 |
} |
| 634 |
} |
| 635 |
} |
| 636 |
} |
| 637 |
} |
| 638 |
|
| 639 |
|
| 640 |
sub where_clause_true { |
| 641 |
|
| 642 |
my $where = shift; |
| 643 |
|
| 644 |
# note: we are guaranteed white space between operands and operators |
| 645 |
# because of how we processed the where clause earlier |
| 646 |
# (may come in handy to know this) |
| 647 |
|
| 648 |
# need to think about how to skip these substitutions if character is inside |
| 649 |
# a string (maybe in parser translate these chars to something else if they're |
| 650 |
# in a string, then translate them back further below |
| 651 |
|
| 652 |
# Need to use safe eval, or build in some checking for system() and |
| 653 |
# backticks, etc. (i.e. "where lname = `<dangerous os command>` " for |
| 654 |
# where clause... ). Safe->reval should do it. |
| 655 |
|
| 656 |
# print STDERR "EVAL where = $where\n"; # jhjh |
| 657 |
|
| 658 |
# use reval to see if substituted where clause is true; |
| 659 |
$compartment->reval( qq{ |
| 660 |
if ($where) { return 1 } |
| 661 |
|
| 662 |
# fall-through |
| 663 |
return 0; |
| 664 |
} ); |
| 665 |
} |
| 666 |
|
| 667 |
|
| 668 |
sub like2re { |
| 669 |
|
| 670 |
my ($str, $word, $negative); |
| 671 |
$negative = 0; |
| 672 |
|
| 673 |
# stop on LIKE for LIKE/NOT LIKE |
| 674 |
while ( ($word = shift) !~ /^like$/i ) { |
| 675 |
if ($word =~ /^not$/i) { |
| 676 |
$negative = 1; |
| 677 |
} |
| 678 |
else { $str .= $word . "," } # replace whitespace w/ comma's just like the |
| 679 |
# parser does |
| 680 |
} |
| 681 |
$str .= $word . ","; # add LIKE to $str |
| 682 |
|
| 683 |
$word = shift; # $word now holds the SQL LIKE expression |
| 684 |
|
| 685 |
|
| 686 |
# if LIKE expr contains SQL % wildcard, then turn into perl reg exp |
| 687 |
if ( $word =~ /'(.*%.*)'/i ) { |
| 688 |
|
| 689 |
$word = $1; |
| 690 |
$str =~ s/like,$/=~,/i; |
| 691 |
|
| 692 |
$negative && ( $str =~ s/=~/!~/ ); |
| 693 |
|
| 694 |
$word =~ s/^([^%])/\^$1/; |
| 695 |
$word =~ s/([^%])$/$1\$/; |
| 696 |
$word =~ s/%/\.\*/g; |
| 697 |
$word = "/" . $word . "/"; |
| 698 |
|
| 699 |
} |
| 700 |
|
| 701 |
# otherwise turn like into "=" ( we will turn "=" into "eq" in get_data(), |
| 702 |
# this sub is used by the yacc parser) |
| 703 |
else { |
| 704 |
$str =~ s/like,$/=,/i; |
| 705 |
} |
| 706 |
|
| 707 |
$str .= $word; |
| 708 |
|
| 709 |
return $str; |
| 710 |
|
| 711 |
} |
| 712 |
|
| 713 |
|
| 714 |
sub tr_op { |
| 715 |
my ($table, $lval, $op, $rval) = @_; |
| 716 |
print STDERR "\$lval = $lval, \$op = $op, \$rval = $rval\n"; # jhjh |
| 717 |
if ( $op eq '<>') { $op = "!=" } |
| 718 |
|
| 719 |
# translate $op for strings |
| 720 |
if ( $lval =~ /^'.*'$/ or |
| 721 |
$rval =~ /^'.*'$/ ) { |
| 722 |
|
| 723 |
$op = $op eq '=' ? 'eq' : |
| 724 |
$op eq '!=' ? 'ne' : |
| 725 |
$op eq '<' ? 'lt' : |
| 726 |
$op eq '<=' ? 'le' : |
| 727 |
$op eq '>' ? 'gt' : |
| 728 |
$op eq '>=' ? 'ge' : $op; |
| 729 |
} |
| 730 |
else { |
| 731 |
$op = $op eq '=' ? '==' : $op; |
| 732 |
} |
| 733 |
|
| 734 |
# translate $op for dates |
| 735 |
if (exists($obj_accessor{lc($table) }->{lc($lval) }->{datatype}) and |
| 736 |
$obj_accessor{lc($table) }->{lc($lval) }->{datatype} |
| 737 |
eq "date" ) { |
| 738 |
|
| 739 |
$op = "date_compare_$op"; |
| 740 |
} |
| 741 |
|
| 742 |
return $op; |
| 743 |
} |
| 744 |
|
| 745 |
return 1; |
Parent Directory
| 
Revision Log