The Perl Toolchain Summit 2025 Needs You: You can help 🙏 Learn more

DAMI / SQL-Abstract-FromQuery-0.11 / lib / SQL / Abstract / FromQuery / Oracle.pm 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
—
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

              
package SQL::Abstract::FromQuery::Oracle;
use strict;
use warnings;
use parent 'SQL::Abstract::FromQuery';
use UNIVERSAL::DOES  qw/does/;
our $time_fmt         = q{HH24:MI:SS};
our $datetime_fmt_ISO = qq{YYYY-MM-DD"T"$time_fmt};
#======================================================================
# override actions hooked to the grammar
#======================================================================
sub date {
  my ($self, $h) = @_;
  my $date = $self->next::method($h); # returns date in ISO format YYYY-MM-DD
  # datetime format is OK for a date without time, Oracle accepts it
  return \ ["TO_DATE(?, '$datetime_fmt_ISO')", $date];
}
sub time {
  my ($self, $h) = @_;
  my $time = $self->next::method($h); # returns time in ISO format HH:MM:SS
  return \ ["TO_DATE(?, '$time_fmt')", $time];
}
sub datetime {
  my ($self, $h) = @_;
  # hack : we remove the "TO_DATE()" parts before calling the parent method ...
  # and then re-inject the "TO_DATE()" on the global result
  $_ = @{$$_}[1] for @{$h}{qw/date time/}; # just keep the bind values
  my $datetime = $self->next::method($h); # returns dt as YYYY-MM-DDTHH:MM:SS
  return \ ["TO_DATE(?, '$datetime_fmt_ISO')", $datetime];
}
#======================================================================
# override finalization callback
#======================================================================
sub finalize {
  my ($self, $result) = @_;
  # if to_date(..) was used without any comparison operator, we must add
  # an '=' because SQL::Abstract won't do it automatically for values
  # of shape \[..] (i.e. literal SQL with placeholders and bind values)
  foreach my $val (values %$result) {
    if (does($val, 'REF') && does($$val, 'ARRAY')) {
      $$val->[0] =~ s/^TO_DATE/= TO_DATE/;
    }
  }
  return $self->next::method($result);
}
1; # End of SQL::Abstract::FromQuery::Oracle
__END__
=head1 NAME
SQL::Abstract::FromQuery::Oracle - SQL::Abstract::FromQuery extension for Oracle queries
=head1 SYNOPSIS
  my $parser = SQL::Abstract::FromQuery->new(
    -components => [qw/Oracle/],
  );
=head1 DESCRIPTION
This subclass automatically adds Oracle C<TO_DATE(...)> conversions
in SQL generated from date and time fields.
=head1 AUTHOR
Laurent Dami, C<< <laurent.dami AT justice.ge.ch> >>
=cut