Skip to content

Commit 6ed275b

Browse files
perlDreamerperlDreamer
committedOct 6, 2011
Rework PA again to not try and sort the passiveLog. Instead, we iterate and store "last" data in a new database table.
·
v8.0.0plebgui7.10.24
1 parent 5314530 commit 6ed275b

File tree

5 files changed

+99
-46
lines changed

5 files changed

+99
-46
lines changed
 

‎docs/upgrades/upgrade_7.10.23-7.10.24.pl

+20
Original file line numberDiff line numberDiff line change
@@ -31,10 +31,30 @@ BEGIN
3131
my $session = start(); # this line required
3232

3333
# upgrade functions go here
34+
addPALastLogTable($session);
3435

3536
finish($session); # this line required
3637

3738

39+
#----------------------------------------------------------------------------
40+
# Describe what our function does
41+
sub addPALastLogTable {
42+
my $session = shift;
43+
print "\tAdd a table to keep track of additional Passive Analytics data... " unless $quiet;
44+
# and here's our code
45+
$session->db->write(<<EOSQL);
46+
CREATE TABLE `PA_lastLog` (
47+
`userId` char(22) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
48+
`assetId` char(22) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
49+
`sessionId` char(22) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
50+
`timeStamp` bigint(20) DEFAULT NULL,
51+
`url` char(255) NOT NULL,
52+
PRIMARY KEY (userId, sessionId)
53+
) ENGINE=MyISAM DEFAULT CHARSET=utf8
54+
EOSQL
55+
print "DONE!\n" unless $quiet;
56+
}
57+
3858
#----------------------------------------------------------------------------
3959
# Describe what our function does
4060
#sub exampleFunction {

‎lib/WebGUI/Workflow/Activity/BucketPassiveAnalytics.pm

+1-1
Original file line numberDiff line numberDiff line change
@@ -61,7 +61,7 @@ Return a statement handle at the desired offset.
6161

6262
sub get_statement {
6363
my ($session, $logIndex) = @_;
64-
my $deltaSql = q{select SQL_CALC_FOUND_ROWS userId, assetId, url, delta, from_unixtime(timeStamp) as stamp from deltaLog order by timestamp limit ?, 500000};
64+
my $deltaSql = q{select SQL_CALC_FOUND_ROWS userId, assetId, url, delta, from_unixtime(timeStamp) as stamp from deltaLog limit ?, 500000};
6565
my $sth = $session->db->read($deltaSql, [$logIndex+0]);
6666
return $sth;
6767
}

‎lib/WebGUI/Workflow/Activity/SummarizePassiveAnalytics.pm

+22-39
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ Return a statement handle at the desired offset.
5959

6060
sub get_statement {
6161
my ($session, $counter) = @_;
62-
my $passive = q{select SQL_CALC_FOUND_ROWS * from passiveLog where userId <> '1' order by userId, sessionId, timeStamp limit ?, 500000};
62+
my $passive = q{select SQL_CALC_FOUND_ROWS * from passiveLog where userId <> '1' limit ?, 500000};
6363
my $sth = $session->db->read($passive, [$counter+0]);
6464
return $sth;
6565
}
@@ -93,68 +93,51 @@ sub execute {
9393
my $lastUrl;
9494
my $counter = $instance->getScratch('counter');
9595
my $sth = get_statement($session, $counter);
96-
if ($counter) {
97-
$lastUserId = $instance->getScratch('lastUserId');
98-
$lastSessionId = $instance->getScratch('lastSessionId');
99-
$lastTimeStamp = $instance->getScratch('lastTimeStamp');
100-
$lastAssetId = $instance->getScratch('lastAssetId');
101-
$lastUrl = $instance->getScratch('lastUrl');
102-
}
103-
else {
104-
my $logLine = $sth->hashRef();
105-
$lastUserId = $logLine->{userId};
106-
$lastSessionId = $logLine->{sessionId};
107-
$lastTimeStamp = $logLine->{timeStamp};
108-
$lastAssetId = $logLine->{assetId};
109-
$lastUrl = $logLine->{url};
110-
$session->db->write('delete from deltaLog'); ##Only if we're starting out
96+
if (! $counter) { #Clean up from last time, just in case
97+
$session->db->write('delete from deltaLog');
98+
$session->db->write('delete from PA_lastLog');
11199
}
112100

113101
my $total_rows = $session->db->quickScalar('select found_rows()');
114102

115-
my $deltaLog = $session->db->prepare('insert into deltaLog (userId, assetId, delta, timeStamp, url) VALUES (?,?,?,?,?)');
103+
my $deltaLog = $session->db->prepare('insert into deltaLog (userId, assetId, timeStamp, url, delta) VALUES (?,?,?,?,?)');
104+
my $recordLast = $session->db->prepare('REPLACE INTO PA_lastLog (userId, sessionId, timeStamp, url) VALUES (?,?,?,?)');
105+
my $fetchLast = $session->db->prepare('select * from PA_lastLog where sessionId=? and userId=?');
116106

117107
my $expired = 0;
118108
LOG_CHUNK: while (1) {
119109
LOG_ENTRY: while (my $logLine = $sth->hashRef()) {
120110
$counter++;
121-
my $delta = $logLine->{timeStamp} - $lastTimeStamp;
122-
if ( $logLine->{userId} eq $lastUserId
123-
&& $logLine->{sessionId} eq $lastSessionId
124-
&& $delta < $deltaInterval ) {
125-
$deltaLog->execute([$lastUserId, $lastAssetId, $delta, $lastTimeStamp, $lastUrl]);
111+
$fetchLast->execute([@{$logLine}{qw/sessionId userId/}]);
112+
my $lastLine = $fetchLast->hashRef();
113+
$recordLast->execute([ (@{ $logLine }{qw/userId sessionId timeStamp url/}) ]);
114+
if ($lastLine->{timeStamp}) {
115+
my $delta = $logLine->{timeStamp} - $lastLine->{timeStamp};
116+
$deltaLog->execute([ (@{ $lastLine }{qw/userId assetId timeStamp url/}), $delta]);
126117
}
127-
$lastUserId = $logLine->{userId};
128-
$lastSessionId = $logLine->{sessionId};
129-
$lastTimeStamp = $logLine->{timeStamp};
130-
$lastAssetId = $logLine->{assetId};
131-
$lastUrl = $logLine->{url};
132118
if (time() > $endTime) {
133-
$instance->setScratch('lastUserId', $lastUserId);
134-
$instance->setScratch('lastSessionId', $lastSessionId);
135-
$instance->setScratch('lastTimeStamp', $lastTimeStamp);
136-
$instance->setScratch('lastAssetId', $lastAssetId);
137-
$instance->setScratch('lastUrl', $lastUrl);
138-
$instance->setScratch('counter', $counter);
139119
$expired = 1;
140120
last LOG_ENTRY;
141121
}
142122
}
143123

144-
$sth->finish;
145124
if ($expired) {
125+
$deltaLog->finish;
126+
$recordLast->finish;
127+
$fetchLast->finish;
128+
$sth->finish;
146129
return $self->WAITING(1);
147130
}
148131
last LOG_CHUNK if $counter >= $total_rows;
149132
$sth = get_statement($session, $counter);
150133
}
151134

152-
$instance->deleteScratch('lastUserId');
153-
$instance->deleteScratch('lastSessionId');
154-
$instance->deleteScratch('lastTimeStamp');
155-
$instance->deleteScratch('lastAssetId');
156-
$instance->deleteScratch('lastUrl');
157135
$instance->deleteScratch('counter');
136+
$deltaLog->finish;
137+
$recordLast->finish;
138+
$fetchLast->finish;
139+
$sth->finish;
140+
$session->db->write('delete from PA_lastLog');
158141
return $self->COMPLETE;
159142
}
160143

‎t/Workflow/Activity/BucketPassiveAnalytics.t

+48-6
Original file line numberDiff line numberDiff line change
@@ -5,14 +5,13 @@ use lib "$FindBin::Bin/../../lib";
55
#use DB;
66

77
use WebGUI::Test;
8-
use WebGUI::Asset;
98
use WebGUI::PassiveAnalytics::Rule;
10-
use WebGUI::Workflow::Activity::BucketPassiveAnalytics;
11-
use WebGUI::Text;
129

1310
use Test::More;
11+
use Test::Deep;
12+
use Data::Dumper;
1413

15-
plan tests => 1; # increment this value for each test you create
14+
plan tests => 2; # increment this value for each test you create
1615

1716
my $session = WebGUI::Test->session;
1817
$session->user({userId => 3});
@@ -21,6 +20,7 @@ WebGUI::Test->addToCleanup(SQL => 'delete from passiveLog');
2120
WebGUI::Test->addToCleanup(SQL => 'delete from deltaLog');
2221
WebGUI::Test->addToCleanup(SQL => 'delete from bucketLog');
2322
WebGUI::Test->addToCleanup(SQL => 'delete from analyticRule');
23+
WebGUI::Test->addToCleanup(SQL => 'delete from PA_lastLog');
2424

2525
my $workflow = WebGUI::Workflow->new($session, 'PassiveAnalytics000001');
2626
my $activities = $workflow->getActivities();
@@ -67,7 +67,8 @@ while (my $spec = shift @url2) {
6767
}
6868

6969
my @urls = map {$_->[1]} @ruleSets;
70-
loadLogData($session, @urls);
70+
#loadLogData($session, @urls);
71+
repeatableLogData($session, 'passiveAnalyticsLog');
7172

7273
##Build rulesets
7374

@@ -80,7 +81,28 @@ PAUSE: while (my $retval = $instance->run()) {
8081
}
8182
#DB::disable_profile();
8283

83-
ok(1, 'One test');
84+
cmp_ok $counter, '<', 16, 'Successful completion of PA';
85+
86+
my $get_line = $session->db->read('select userId, Bucket, duration from bucketLog');
87+
88+
my @database_dump = ();
89+
ROW: while ( 1 ) {
90+
my @datum = $get_line->array();
91+
last ROW unless @datum;
92+
push @database_dump, [ @datum ];
93+
}
94+
95+
cmp_bag(
96+
[ @database_dump ],
97+
[
98+
['user1', 'one', 10],
99+
['user1', 'two', 15],
100+
['user2', 'zero', 2],
101+
['user2', 'uno', 3],
102+
['user2', 'Other', 5],
103+
],
104+
'PA analysis completed, and calculated correctly'
105+
) or diag Dumper(\@database_dump);
84106

85107
sub loadLogData {
86108
my ($session, @urls) = @_;
@@ -100,4 +122,24 @@ sub loadLogData {
100122
}
101123
}
102124

125+
sub repeatableLogData {
126+
my ($session, $dataLogName) = @_;
127+
$session->db->write('delete from passiveLog');
128+
my $insert = $session->db->prepare(
129+
q!insert into passiveLog (userId, sessionId, timeStamp, url, assetId) VALUES (?,?,?,?,'assetId')!
130+
);
131+
my $data_name = WebGUI::Test::collateral('passiveAnalyticsLog');
132+
open my $log_data, '<', $data_name or
133+
die "Unable to open $data_name for reading: $!";
134+
local $_;
135+
while (<$log_data>) {
136+
next if /^\s*#/;
137+
s/#\.*$//;
138+
chomp;
139+
my @data = split;
140+
$insert->execute([@data]);
141+
}
142+
$insert->finish;
143+
}
144+
103145
#vim:ft=perl

‎t/supporting_collateral/passiveAnalyticsLog

+8
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
#user session timestamp url
2+
user1 session11 100 /one
3+
user1 session11 110 /two
4+
user1 session11 125 /three
5+
user2 session21 200 /yelnats
6+
user2 session21 202 /one/uno
7+
user2 session21 205 /whatever
8+
user2 session21 210 /something_else

0 commit comments

Comments
 (0)
Please sign in to comment.