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