Attachment #28022 for bug #10933

View | Details | Raw Unified | Return to bug 10933
Collapse All | Expand All




  &buildQuery
  &GetDistinctValues
  &enabled_staff_search_views
  &PurgeSearchHistory
);

# make all your functions, whether exported or not;

	);
}

sub PurgeSearchHistory{
    my ($pSearchhistory)=@_;
    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare("DELETE FROM search_history WHERE time < DATE_SUB( NOW(), INTERVAL ? DAY )");
    $sth->execute($pSearchhistory) or die $dbh->errstr;
}

=head2 z3950_search_args

$arrayref = z3950_search_args($matchpoints)




    my $query_cgi  = $params->{query_cgi};
    my $total      = $params->{total} // 0;
    my $type       = $params->{type} || 'biblio';
    my $time       = $params->{time};

    my $dbh = C4::Context->dbh;


        INSERT INTO search_history(
            userid, sessionid, query_desc, query_cgi, type, total, time
        ) VALUES(
            ?, ?, ?, ?, ?, ?, ?
        )
    };
    my $sth = $dbh->prepare($query);
    $sth->execute( $userid, $sessionid, $query_desc, $query_cgi, $type,
        $total, $time );
}

sub add_to_session {

    my $sessionid = $params->{sessionid};
    my $type      = $params->{type}     || q{};
    my $previous  = $params->{previous} || 0;
    my $interval  = $params->{interval} || 0;

    unless ($userid) {
        warn "ERROR: userid is required for history search";

    $query .= q{ AND type = ?}
      if $type;

    # FIXME DATE_SUB is a Mysql-ism. Postgres uses: datefield - INTERVAL '6 months'
    $query .= q{ AND time < DATE_SUB( NOW(), INTERVAL ? DAY )}
        if $interval;

    $dbh->do(
        $query, {}, $userid,
        ( $sessionid ? $sessionid : () ),
        ( $type      ? $type      : () ),
        ( $interval  ? $interval  : () ),
    );
}


Lines 209-215 if($pLogs) { Link Here

(-)a/misc/cronjobs/cleanup_database.pl (-1 / +1 lines)
209		209
210	if($pSearchhistory) {	210	if($pSearchhistory) {
211	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;	211	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;
212	PurgeSearchHistory($pSearchhistory);	212	C4::Search::History::delete({ interval => $pSearchhistory });
213	print "Done with purging search_history.\n" if $verbose;	213	print "Done with purging search_history.\n" if $verbose;
214	}	214	}
215		215





use Modern::Perl;

use Test::More tests => 22;
use URI::Escape;

use C4::Context;

is( scalar(@$all), 9, 'There are still 9 searches after calling delete without userid' );
delete_all( $userid );

# Test delete with interval
add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
C4::Search::History::delete({
    userid => $userid,
    interval => 10,
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 9, 'There are still 9 searches after calling delete with an interval = 10 days' );
C4::Search::History::delete({
    userid => $userid,
    interval => 6,
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 8, 'There are still 8 searches after calling delete with an interval = 6 days' );
C4::Search::History::delete({
    userid => $userid,
    interval => 2,
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 2, 'There are still 2 searches after calling delete with an interval = 2 days' );
delete_all( $userid );

add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
C4::Search::History::delete({
    userid => $userid,
    interval => 5,
    type => 'biblio',
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 8, 'There are still 9 searches after calling delete with an interval = 5 days for biblio' );
C4::Search::History::delete({
    userid => $userid,
    interval => 5,
    type => 'authority',
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 6, 'There are still 6 searches after calling delete with an interval = 5 days for authority' );
C4::Search::History::delete({
    userid => $userid,
    interval => -1,
});
$all = C4::Search::History::get({userid => $userid});
is( scalar(@$all), 0, 'There is no search after calling delete with an interval = -1 days' );
delete_all( $userid );

sub add {
    my ( $userid, $current_session_id, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a ) = @_;

    my $days_ago_2 = dt_from_string()->add_duration( DateTime::Duration->new( days => -2 ) );
    my $days_ago_4 = dt_from_string()->add_duration( DateTime::Duration->new( days => -4 ) );
    my $days_ago_6 = dt_from_string()->add_duration( DateTime::Duration->new( days => -6 ) );
    my $days_ago_8 = dt_from_string()->add_duration( DateTime::Duration->new( days => -8 ) );

    my $query_desc_b1_p = q{first previous biblio search};
    my $first_previous_biblio_search = {
        userid => $userid,

        query_cgi => $query_cgi_b,
        total => $total,
        type => 'biblio',
        time => $days_ago_2,
    };

    my $query_desc_a1_p = q{first previous authority search};

        query_cgi => $query_cgi_a,
        total => $total,
        type => 'authority',
        time => $days_ago_2,
    };

    my $query_desc_b2_p = q{second previous biblio search};

        query_cgi => $query_cgi_b,
        total => $total,
        type => 'biblio',
        time => $days_ago_4,
    };

    my $query_desc_a2_p = q{second previous authority search};

        query_cgi => $query_cgi_a,
        total => $total,
        type => 'authority',
        time => $days_ago_4,
    };



        query_cgi => $query_cgi_b,
        total => $total,
        type => 'biblio',
        time => $days_ago_4,
    };

    my $query_desc_a1_c = q{first current authority search};

        query_cgi => $query_cgi_a,
        total => $total,
        type => 'authority',
        time => $days_ago_4,
    };

    my $query_desc_b2_c = q{second current biblio search};

        query_cgi => $query_cgi_b,
        total => $total,
        type => 'biblio',
        time => $days_ago_6,
    };

    my $query_desc_a2_c = q{second current authority search};

        query_cgi => $query_cgi_a,
        total => $total,
        type => 'authority',
        time => $days_ago_6,
    };

    my $query_desc_a3_c = q{third current authority search};

        query_cgi => $query_cgi_a,
        total => $total,
        type => 'authority',
        time => $days_ago_8,
    };






#!/usr/bin/perl

# This file is part of Koha.
#
# Copyright 2013 Equinox Software, Inc.
#
# Koha is free software; you can redistribute it and/or modify it under the
# terms of the GNU General Public License as published by the Free Software
# Foundation; either version 3 of the License, or (at your option) any later
# version.
#
# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with Koha; if not, see <http://www.gnu.org/licenses>.

use Modern::Perl;

use Test::More tests => 6;

use C4::Context;
use_ok('C4::Search', qw/PurgeSearchHistory/); # GetSearchHistory is not exported
use C4::Search::History;

# Start transaction
my $dbh = C4::Context->dbh;
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;

# start with a clean slate
$dbh->do('DELETE FROM search_history');
is(_get_history_count(), 0, 'starting off with nothing in search_history');

# Add some history rows.  Note that we're ignoring the return value;
# since the search_history table doesn't have an auto_increment
# column, it appears that the value of $dbh->last_insert_id() is
# useless for determining if the insert failed.
C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_1', query_cgi => 'query_cgi_1', total => 5});
C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_2', query_cgi => 'query_cgi_3', total => 6});
C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_3', query_cgi => 'query_cgi_3', total => 7});
C4::Search::History::add({userid => 56789, sessionid => 'session_2', query_desc => 'query_desc_4', query_cgi => 'query_cgi_4', total => 8});
is(_get_history_count(), 4, 'successfully added four search_history rows');

# munge some dates
my $sth = $dbh->prepare('
    UPDATE search_history
    SET time = DATE_SUB(time, INTERVAL ? DAY)
    WHERE query_desc = ?
');
$sth->execute(46, 'query_desc_1');
$sth->execute(31, 'query_desc_2');

PurgeSearchHistory(45);
is(_get_history_count(), 3, 'purged history older than 45 days');
PurgeSearchHistory(30);
is(_get_history_count(), 2, 'purged history older than 30 days');
PurgeSearchHistory(-1);
is(_get_history_count(), 0, 'purged all history');

sub _get_history_count {
    my $count_sth = $dbh->prepare('SELECT COUNT(*) FROM search_history');
    $count_sth->execute();
    my $count = $count_sth->fetchall_arrayref();
    return $count->[0]->[0];
}

$dbh->rollback;
- 

Return to bug 10933

Lines 70-76 This module provides searching functions for Koha's bibliographic databases Link Here

(-)a/C4/Search.pm (-8 lines)
70	&buildQuery	70	&buildQuery
71	&GetDistinctValues	71	&GetDistinctValues
72	&enabled_staff_search_views	72	&enabled_staff_search_views
73	&PurgeSearchHistory
74	);	73	);
75		74
76	# make all your functions, whether exported or not;	75	# make all your functions, whether exported or not;
Lines 2222-2234 sub enabled_staff_search_views Link Here
2222	);	2221	);
2223	}	2222	}
2224		2223
2225	sub PurgeSearchHistory{
2226	my ($pSearchhistory)=@_;
2227	my $dbh = C4::Context->dbh;
2228	my $sth = $dbh->prepare("DELETE FROM search_history WHERE time < DATE_SUB( NOW(), INTERVAL ? DAY )");
2229	$sth->execute($pSearchhistory) or die $dbh->errstr;
2230	}
2231
2232	=head2 z3950_search_args	2224	=head2 z3950_search_args
2233		2225
2234	$arrayref = z3950_search_args($matchpoints)	2226	$arrayref = z3950_search_args($matchpoints)

Lines 2-8 Link Here

(-)a/t/db_dependent/Search/History.t (-1 / +60 lines)
2		2
3	use Modern::Perl;	3	use Modern::Perl;
4		4
5	use Test::More tests => 16;	5	use Test::More tests => 22;
6	use URI::Escape;	6	use URI::Escape;
7		7
8	use C4::Context;	8	use C4::Context;
Lines 127-135 $all = C4::Search::History::get({userid => $userid}); Link Here
127	is( scalar(@$all), 9, 'There are still 9 searches after calling delete without userid' );	127	is( scalar(@$all), 9, 'There are still 9 searches after calling delete without userid' );
128	delete_all( $userid );	128	delete_all( $userid );
129		129
		130	# Test delete with interval
		131	add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
		132	C4::Search::History::delete({
		133	userid => $userid,
		134	interval => 10,
		135	});
		136	$all = C4::Search::History::get({userid => $userid});
		137	is( scalar(@$all), 9, 'There are still 9 searches after calling delete with an interval = 10 days' );
		138	C4::Search::History::delete({
		139	userid => $userid,
		140	interval => 6,
		141	});
		142	$all = C4::Search::History::get({userid => $userid});
		143	is( scalar(@$all), 8, 'There are still 8 searches after calling delete with an interval = 6 days' );
		144	C4::Search::History::delete({
		145	userid => $userid,
		146	interval => 2,
		147	});
		148	$all = C4::Search::History::get({userid => $userid});
		149	is( scalar(@$all), 2, 'There are still 2 searches after calling delete with an interval = 2 days' );
		150	delete_all( $userid );
		151
		152	add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
		153	C4::Search::History::delete({
		154	userid => $userid,
		155	interval => 5,
		156	type => 'biblio',
		157	});
		158	$all = C4::Search::History::get({userid => $userid});
		159	is( scalar(@$all), 8, 'There are still 9 searches after calling delete with an interval = 5 days for biblio' );
		160	C4::Search::History::delete({
		161	userid => $userid,
		162	interval => 5,
		163	type => 'authority',
		164	});
		165	$all = C4::Search::History::get({userid => $userid});
		166	is( scalar(@$all), 6, 'There are still 6 searches after calling delete with an interval = 5 days for authority' );
		167	C4::Search::History::delete({
		168	userid => $userid,
		169	interval => -1,
		170	});
		171	$all = C4::Search::History::get({userid => $userid});
		172	is( scalar(@$all), 0, 'There is no search after calling delete with an interval = -1 days' );
		173	delete_all( $userid );
		174
130	sub add {	175	sub add {
131	my ( $userid, $current_session_id, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a ) = @_;	176	my ( $userid, $current_session_id, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a ) = @_;
132		177
		178	my $days_ago_2 = dt_from_string()->add_duration( DateTime::Duration->new( days => -2 ) );
		179	my $days_ago_4 = dt_from_string()->add_duration( DateTime::Duration->new( days => -4 ) );
		180	my $days_ago_6 = dt_from_string()->add_duration( DateTime::Duration->new( days => -6 ) );
		181	my $days_ago_8 = dt_from_string()->add_duration( DateTime::Duration->new( days => -8 ) );
		182
133	my $query_desc_b1_p = q{first previous biblio search};	183	my $query_desc_b1_p = q{first previous biblio search};
134	my $first_previous_biblio_search = {	184	my $first_previous_biblio_search = {
135	userid => $userid,	185	userid => $userid,
Lines 138-143 sub add { Link Here
138	query_cgi => $query_cgi_b,	188	query_cgi => $query_cgi_b,
139	total => $total,	189	total => $total,
140	type => 'biblio',	190	type => 'biblio',
		191	time => $days_ago_2,
141	};	192	};
142		193
143	my $query_desc_a1_p = q{first previous authority search};	194	my $query_desc_a1_p = q{first previous authority search};
Lines 148-153 sub add { Link Here
148	query_cgi => $query_cgi_a,	199	query_cgi => $query_cgi_a,
149	total => $total,	200	total => $total,
150	type => 'authority',	201	type => 'authority',
		202	time => $days_ago_2,
151	};	203	};
152		204
153	my $query_desc_b2_p = q{second previous biblio search};	205	my $query_desc_b2_p = q{second previous biblio search};
Lines 158-163 sub add { Link Here
158	query_cgi => $query_cgi_b,	210	query_cgi => $query_cgi_b,
159	total => $total,	211	total => $total,
160	type => 'biblio',	212	type => 'biblio',
		213	time => $days_ago_4,
161	};	214	};
162		215
163	my $query_desc_a2_p = q{second previous authority search};	216	my $query_desc_a2_p = q{second previous authority search};
Lines 168-173 sub add { Link Here
168	query_cgi => $query_cgi_a,	221	query_cgi => $query_cgi_a,
169	total => $total,	222	total => $total,
170	type => 'authority',	223	type => 'authority',
		224	time => $days_ago_4,
171	};	225	};
172		226
173		227
Lines 180-185 sub add { Link Here
180	query_cgi => $query_cgi_b,	234	query_cgi => $query_cgi_b,
181	total => $total,	235	total => $total,
182	type => 'biblio',	236	type => 'biblio',
		237	time => $days_ago_4,
183	};	238	};
184		239
185	my $query_desc_a1_c = q{first current authority search};	240	my $query_desc_a1_c = q{first current authority search};
Lines 190-195 sub add { Link Here
190	query_cgi => $query_cgi_a,	245	query_cgi => $query_cgi_a,
191	total => $total,	246	total => $total,
192	type => 'authority',	247	type => 'authority',
		248	time => $days_ago_4,
193	};	249	};
194		250
195	my $query_desc_b2_c = q{second current biblio search};	251	my $query_desc_b2_c = q{second current biblio search};
Lines 200-205 sub add { Link Here
200	query_cgi => $query_cgi_b,	256	query_cgi => $query_cgi_b,
201	total => $total,	257	total => $total,
202	type => 'biblio',	258	type => 'biblio',
		259	time => $days_ago_6,
203	};	260	};
204		261
205	my $query_desc_a2_c = q{second current authority search};	262	my $query_desc_a2_c = q{second current authority search};
Lines 210-215 sub add { Link Here
210	query_cgi => $query_cgi_a,	267	query_cgi => $query_cgi_a,
211	total => $total,	268	total => $total,
212	type => 'authority',	269	type => 'authority',
		270	time => $days_ago_6,
213	};	271	};
214		272
215	my $query_desc_a3_c = q{third current authority search};	273	my $query_desc_a3_c = q{third current authority search};
Lines 220-225 sub add { Link Here
220	query_cgi => $query_cgi_a,	278	query_cgi => $query_cgi_a,
221	total => $total,	279	total => $total,
222	type => 'authority',	280	type => 'authority',
		281	time => $days_ago_8,
223	};	282	};
224		283
225		284

Lines 18-23 sub add { Link Here

(-)a/C4/Search/History.pm (-3 / +10 lines)
18	my $query_cgi = $params->{query_cgi};	18	my $query_cgi = $params->{query_cgi};
19	my $total = $params->{total} // 0;	19	my $total = $params->{total} // 0;
20	my $type = $params->{type} \|\| 'biblio';	20	my $type = $params->{type} \|\| 'biblio';
		21	my $time = $params->{time};
21		22
22	my $dbh = C4::Context->dbh;	23	my $dbh = C4::Context->dbh;
23		24
Lines 26-37 sub add { Link Here
26	INSERT INTO search_history(	27	INSERT INTO search_history(
27	userid, sessionid, query_desc, query_cgi, type, total, time	28	userid, sessionid, query_desc, query_cgi, type, total, time
28	) VALUES(	29	) VALUES(
29	?, ?, ?, ?, ?, ?, NOW()	30	?, ?, ?, ?, ?, ?, ?
30	)	31	)
31	};	32	};
32	my $sth = $dbh->prepare($query);	33	my $sth = $dbh->prepare($query);
33	$sth->execute( $userid, $sessionid, $query_desc, $query_cgi, $type,	34	$sth->execute( $userid, $sessionid, $query_desc, $query_cgi, $type,
34	$total );	35	$total, $time );
35	}	36	}
36		37
37	sub add_to_session {	38	sub add_to_session {
Lines 62-67 sub delete { Link Here
62	my $sessionid = $params->{sessionid};	63	my $sessionid = $params->{sessionid};
63	my $type = $params->{type} \|\| q{};	64	my $type = $params->{type} \|\| q{};
64	my $previous = $params->{previous} \|\| 0;	65	my $previous = $params->{previous} \|\| 0;
		66	my $interval = $params->{interval} \|\| 0;
65		67
66	unless ($userid) {	68	unless ($userid) {
67	warn "ERROR: userid is required for history search";	69	warn "ERROR: userid is required for history search";
Lines 84-93 sub delete { Link Here
84	$query .= q{ AND type = ?}	86	$query .= q{ AND type = ?}
85	if $type;	87	if $type;
86		88
		89	# FIXME DATE_SUB is a Mysql-ism. Postgres uses: datefield - INTERVAL '6 months'
		90	$query .= q{ AND time < DATE_SUB( NOW(), INTERVAL ? DAY )}
		91	if $interval;
		92
87	$dbh->do(	93	$dbh->do(
88	$query, {}, $userid,	94	$query, {}, $userid,
89	( $sessionid ? $sessionid : () ),	95	( $sessionid ? $sessionid : () ),
90	( $type ? $type : () )	96	( $type ? $type : () ),
		97	( $interval ? $interval : () ),
91	);	98	);
92	}	99	}
93		100

Lines 1-69 Link Here

(-)a/t/db_dependent/Search_SearchHistory.t (-70 lines)
1	#!/usr/bin/perl
2
3	# This file is part of Koha.
4	#
5	# Copyright 2013 Equinox Software, Inc.
6	#
7	# Koha is free software; you can redistribute it and/or modify it under the
8	# terms of the GNU General Public License as published by the Free Software
9	# Foundation; either version 3 of the License, or (at your option) any later
10	# version.
11	#
12	# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14	# A PARTICULAR PURPOSE. See the GNU General Public License for more details.
15	#
16	# You should have received a copy of the GNU General Public License along
17	# with Koha; if not, see <http://www.gnu.org/licenses>.
18
19	use Modern::Perl;
20
21	use Test::More tests => 6;
22
23	use C4::Context;
24	use_ok('C4::Search', qw/PurgeSearchHistory/); # GetSearchHistory is not exported
25	use C4::Search::History;
26
27	# Start transaction
28	my $dbh = C4::Context->dbh;
29	$dbh->{AutoCommit} = 0;
30	$dbh->{RaiseError} = 1;
31
32	# start with a clean slate
33	$dbh->do('DELETE FROM search_history');
34	is(_get_history_count(), 0, 'starting off with nothing in search_history');
35
36	# Add some history rows. Note that we're ignoring the return value;
37	# since the search_history table doesn't have an auto_increment
38	# column, it appears that the value of $dbh->last_insert_id() is
39	# useless for determining if the insert failed.
40	C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_1', query_cgi => 'query_cgi_1', total => 5});
41	C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_2', query_cgi => 'query_cgi_3', total => 6});
42	C4::Search::History::add({userid => 12345, sessionid => 'session_1', query_desc => 'query_desc_3', query_cgi => 'query_cgi_3', total => 7});
43	C4::Search::History::add({userid => 56789, sessionid => 'session_2', query_desc => 'query_desc_4', query_cgi => 'query_cgi_4', total => 8});
44	is(_get_history_count(), 4, 'successfully added four search_history rows');
45
46	# munge some dates
47	my $sth = $dbh->prepare('
48	UPDATE search_history
49	SET time = DATE_SUB(time, INTERVAL ? DAY)
50	WHERE query_desc = ?
51	');
52	$sth->execute(46, 'query_desc_1');
53	$sth->execute(31, 'query_desc_2');
54
55	PurgeSearchHistory(45);
56	is(_get_history_count(), 3, 'purged history older than 45 days');
57	PurgeSearchHistory(30);
58	is(_get_history_count(), 2, 'purged history older than 30 days');
59	PurgeSearchHistory(-1);
60	is(_get_history_count(), 0, 'purged all history');
61
62	sub _get_history_count {
63	my $count_sth = $dbh->prepare('SELECT COUNT(*) FROM search_history');
64	$count_sth->execute();
65	my $count = $count_sth->fetchall_arrayref();
66	return $count->[0]->[0];
67	}
68
69	$dbh->rollback;
70	-