Eulerian/EDW/UseCases/RollMap/Sql.pm untested

filter: Statementstmt 0.0% 0 / 133 Branchbran 0.0% 0 / 32 Conditioncond 0.0% 0 / 17 MC/DCmcdc 0.0% 0 / 9 Subroutinesub 0.0% 0 / 16 Podpod 0.0% 0 / 12 totaltotal 0.0% 0 / 219 CC 17 SCAR 30.5
CC
17
Cov
0%
AddPageview
56.0
AddFilter
30.0
AddClickview
20.0
CRAP 21.2
1  
2 package API::Eulerian::EDW::UseCases::RollMap;
3  
4 use API::Eulerian::EDW::Sql::Builder;
5 use strict; use warnings;
6 use base 'Exporter';
7  
8 our @EXPORT = qw( Sql );
9  
10 sub AddTimerange
11 {
12 my ( $builder, $setup ) = @_;
13 $builder->timerange(
14 $setup->{ from }, $setup->{ to }
15 );
16 }
17  
18 sub ToArray
19 {
20 my ( $hash, $key ) = @_;
21 my $value = $hash->{ $key } || [];
22 $value = [ $value ] if ref( $value ) ne 'ARRAY';
23 return $value;
24 }
25  
26 sub AddPageview
27 {
28 my ( $builder, $setup ) = @_;
29 my $devices = ToArray( $setup, 'mdevicetypefull-name' );
30 my $subkeys = ToArray( $setup, 'subkey2-name' );
31 my $ndevices = scalar( @$devices );
32 my $nsubkeys = scalar( @$subkeys );
33 my $filter = '';
34  
35 # Handle pageview filtering on devices if any
36 if( $ndevices == 1 ) {
37 $filter .= 'pageview.device.devicetype.type == ';
38 $filter .= "'";
39 $filter .= $devices->[ 0 ];
40 $filter .= "'";
41 } elsif( $ndevices > 1 ) {
42 $filter .= 'IN( pageview.device.devicetype.type, ';
43 $filter .= join( ', ', map { '"' . $_ . '"' } @$devices );
44 $filter .= ' )';
45 }
46  
47 # Handle pageview filtering on subkey2 if any
48 if( $nsubkeys == 1 ) {
49 if( $ndevices ) { $filter .= " && "; }
50 $filter .= 'pageview.subkey2.name == ';
51 $filter .= "'";
52 $filter .= $subkeys->[ 0 ];
53 $filter .= "'";
54 } elsif( $nsubkeys > 1 ) {
55 if( $ndevices ) { $filter .= " && "; }
56 $filter .= 'IN( pageview.subkey2.name, ';
57 $filter .= join( ', ', map { '"' . $_ . '"' } @$subkeys );
58 $filter .= ' )';
59 }
60  
61 # Add pageview reader
62 $builder->readers(
63 'pageview', 'ea:pageview', $setup->{ site }, $filter
64 );
65  
66 }
67  
68 sub AddClickview
69 {
70 my ( $builder, $setup ) = @_;
71 my $medias = ToArray( $setup, 'media-shortname' );
72 my $imedia = int( $setup->{ 'media-id' } || 0 );
73 my $nmedias = scalar( @$medias );
74 my $filter = '';
75  
76 # Handle clickview if need it */
77 if( $nmedias == 1 ) {
78 # Setup clickview filter
79 $filter .= "clickview.channel.odmedia == '";
80 $filter .= $medias->[ 0 ];
81 $filter .= "'";
82 } elsif( $nmedias > 1 ) {
83 $filter .= 'IN( clickview.channel.odmedia, ';
84 $filter .= join( ', ', map { '"' . $_ . '"' } @$medias );
85 $filter .= ' )';
86 }
87 # Add clickview reader
88 if( $filter ne '' ) {
89 $builder->readers(
90 'clickview', 'ea:clickview', $setup->{ site }, $filter
91 );
92 }
93  
94 }
95  
96 sub AddMerged
97 {
98 my ( $builder, $setup ) = @_;
99 my $filter;
100  
101 # Setup visit filter
102 $filter = 'merged.first.pageview.page.name != pageview.page.name';
103 $filter .= ' || pageview.rtvisit == 1';
104  
105 # Create visit
106 $builder->groups( 'merged', 'pageview', $filter );
107  
108 }
109  
110 sub AddVisit
111 {
112 my ( $builder, $setup ) = @_;
113 my $filter;
114  
115 # Setup visit filter
116 $filter = 'pageview.rtvisit == 1';
117
118 # Create visit
119 $builder->groups( 'visit', 'merged', $filter );
120  
121 }
122  
123 sub AddJoin
124 {
125 my ( $builder, $setup ) = @_;
126 my $media = $setup->{ 'media-shortname' } || undef;
127 my $imedia = int( $setup->{ 'media-id' } || 0 );
128  
129 if( $imedia ) {
130 # Setup join filter
131 my $filter = '';
132 $filter .= "clickview.timestamp >= visit.first.merged.first.pageview.timestamp && ";
133 $filter .= "clickview.timestamp <= visit.last.merged.last.pageview.timestamp && ";
134 $filter .= "join.count() < 1 ";
135 # Add join on clickview
136 $builder->joins( 'join', 'visit', 'clickview', $filter );
137 }
138  
139 return $imedia;
140 }
141  
142 sub AddOutputs
143 {
144 my ( $builder, $prefix, $setup ) = @_;
145 my $npages = int( $setup->{ npages } || 5 );
146  
147 # Add outputs
148 for my $ioutput ( 0 ... $npages - 1 ) {
149 $builder->outputs( "Page$ioutput" );
150 }
151  
152 }
153  
154 sub AddFilter
155 {
156 my ( $builder, $prefix, $setup ) = @_;
157 my $roots = ToArray( $setup, 'roots' );
158 my $filter = '';
159
160 # Add outputs filter
161 $filter .= $prefix;
162 $filter .= 'visit.first.merged.first.pageview.rtvisit == 1 ';
163
164 if( defined( $roots ) && scalar( @$roots ) > 0 ) {
165 $filter .= '&& ';
166 if( $prefix ne '' ) {
167 $filter .= $prefix;
168 $filter .= "clickview.timestamp != NULL && ";
169 }
170 if( scalar( @$roots ) == 1 ) {
171 $filter .= "Page0 == '";
172 $filter .= $roots->[ 0 ];
173 $filter .= "'";
174 } elsif( scalar( @$roots ) > 1 ) {
175 $filter .= "IN( ";
176 $filter .= 'Page0, ';
177 $filter .= join( ', ', map { '"' . $_ . '"' } @$roots );
178 $filter .= " )";
179 }
180 }
181 $builder->filter( $filter );
182 }
183  
184 sub AddThen
185 {
186 my ( $builder, $setup ) = @_;
187 my $palmares = int( $setup->{ npalmares } || 5 );
188 $builder->thens( "RollMap( $palmares )" );
189 }
190  
191 sub AddAliases
192 {
193 my ( $builder, $prefix, $setup ) = @_;
194 my $npages = int( $setup->{ npages } || 5 );
195  
196 for my $ialias ( 0 ... $npages - 1 ) {
197 $builder->aliases(
198 "Page$ialias", "${prefix}visit.items( $ialias ).merged.first.pageview.page.name"
199 );
200 }
201  
202 }
203  
204 sub Sql
205 {
206 my $builder = new API::Eulerian::EDW::Sql::Builder();
207 my $master = 'visit';
208 my ( $setup ) = @_;
209 my $prefix = '';
210  
211 $builder->mode( 'ROW' );
212 $builder->master( 'visit' );
213 AddTimerange( $builder, $setup );
214 AddPageview( $builder, $setup );
215 AddClickview( $builder, $setup );
216 AddMerged( $builder, $setup );
217 AddVisit( $builder, $setup );
218 if( AddJoin( $builder, $setup ) ) {
219 $builder->master( 'join' );
220 $prefix = 'join.';
221 }
222 AddAliases( $builder, $prefix, $setup );
223 AddOutputs( $builder, $prefix, $setup );
224 AddFilter( $builder, $prefix, $setup );
225 AddThen( $builder, $setup );
226  
227 return $builder->str();
228 }