Attachment #42114 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 ( ref( $id ) ) {
        $id = $id ? [ $id ] : [];

    $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, {},
        ( @$id ? ( @$id ) : () ),
        ( $userid ? $userid : () ),
        ( $sessionid ? $sessionid : () ),
        ( $type      ? $type      : () ),
        ( $interval  ? $interval  : () ),
    );
}


Lines 243-249 if ($pLogs) { Link Here

(-)a/misc/cronjobs/cleanup_database.pl (-1 / +1 lines)
243		243
244	if ($pSearchhistory) {	244	if ($pSearchhistory) {
245	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;	245	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;
246	PurgeSearchHistory($pSearchhistory);	246	C4::Search::History::delete({ interval => $pSearchhistory });
247	print "Done with purging search_history.\n" if $verbose;	247	print "Done with purging search_history.\n" if $verbose;
248	}	248	}
249		249





use Modern::Perl;

use Test::More tests => 25;
use Test::Warn;
use URI::Escape;
use List::Util qw( shuffle );

C4::Search::History::delete({ id => [ @$ids[0..4] ] });
$all = C4::Search::History::get({ userid => $userid });
is( scalar(@$all), 4, 'There are 4 searches after calling delete with 5 ids' );

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 69-75 This module provides searching functions for Koha's bibliographic databases Link Here

(-)a/C4/Search.pm (-8 lines)
69	&buildQuery	69	&buildQuery
70	&GetDistinctValues	70	&GetDistinctValues
71	&enabled_staff_search_views	71	&enabled_staff_search_views
72	&PurgeSearchHistory
73	);	72	);
74		73
75	# make all your functions, whether exported or not;	74	# make all your functions, whether exported or not;
Lines 2428-2440 sub enabled_staff_search_views Link Here
2428	);	2427	);
2429	}	2428	}
2430		2429
2431	sub PurgeSearchHistory{
2432	my ($pSearchhistory)=@_;
2433	my $dbh = C4::Context->dbh;
2434	my $sth = $dbh->prepare("DELETE FROM search_history WHERE time < DATE_SUB( NOW(), INTERVAL ? DAY )");
2435	$sth->execute($pSearchhistory) or die $dbh->errstr;
2436	}
2437
2438	=head2 z3950_search_args	2430	=head2 z3950_search_args
2439		2431
2440	$arrayref = z3950_search_args($matchpoints)	2432	$arrayref = z3950_search_args($matchpoints)

Lines 2-8 Link Here

(-)a/t/db_dependent/Search/History.t (-1 / +62 lines)
2		2
3	use Modern::Perl;	3	use Modern::Perl;
4		4
5	use Test::More tests => 19;	5	use Test::More tests => 25;
6	use Test::Warn;	6	use Test::Warn;
7	use URI::Escape;	7	use URI::Escape;
8	use List::Util qw( shuffle );	8	use List::Util qw( shuffle );
Lines 150-160 $ids = [ shuffle map { $_->{id} } @$all ]; Link Here
150	C4::Search::History::delete({ id => [ @$ids[0..4] ] });	150	C4::Search::History::delete({ id => [ @$ids[0..4] ] });
151	$all = C4::Search::History::get({ userid => $userid });	151	$all = C4::Search::History::get({ userid => $userid });
152	is( scalar(@$all), 4, 'There are 4 searches after calling delete with 5 ids' );	152	is( scalar(@$all), 4, 'There are 4 searches after calling delete with 5 ids' );
		153
		154	delete_all( $userid );
		155
		156	# Test delete with interval
		157	add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
		158	C4::Search::History::delete({
		159	userid => $userid,
		160	interval => 10,
		161	});
		162	$all = C4::Search::History::get({userid => $userid});
		163	is( scalar(@$all), 9, 'There are still 9 searches after calling delete with an interval = 10 days' );
		164	C4::Search::History::delete({
		165	userid => $userid,
		166	interval => 6,
		167	});
		168	$all = C4::Search::History::get({userid => $userid});
		169	is( scalar(@$all), 8, 'There are still 8 searches after calling delete with an interval = 6 days' );
		170	C4::Search::History::delete({
		171	userid => $userid,
		172	interval => 2,
		173	});
		174	$all = C4::Search::History::get({userid => $userid});
		175	is( scalar(@$all), 2, 'There are still 2 searches after calling delete with an interval = 2 days' );
		176	delete_all( $userid );
		177
		178	add( $userid, $current_sessionid, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a );
		179	C4::Search::History::delete({
		180	userid => $userid,
		181	interval => 5,
		182	type => 'biblio',
		183	});
		184	$all = C4::Search::History::get({userid => $userid});
		185	is( scalar(@$all), 8, 'There are still 9 searches after calling delete with an interval = 5 days for biblio' );
		186	C4::Search::History::delete({
		187	userid => $userid,
		188	interval => 5,
		189	type => 'authority',
		190	});
		191	$all = C4::Search::History::get({userid => $userid});
		192	is( scalar(@$all), 6, 'There are still 6 searches after calling delete with an interval = 5 days for authority' );
		193	C4::Search::History::delete({
		194	userid => $userid,
		195	interval => -1,
		196	});
		197	$all = C4::Search::History::get({userid => $userid});
		198	is( scalar(@$all), 0, 'There is no search after calling delete with an interval = -1 days' );
		199
153	delete_all( $userid );	200	delete_all( $userid );
154		201
155	sub add {	202	sub add {
156	my ( $userid, $current_session_id, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a ) = @_;	203	my ( $userid, $current_session_id, $previous_sessionid, $total, $query_cgi_b, $query_cgi_a ) = @_;
157		204
		205	my $days_ago_2 = dt_from_string()->add_duration( DateTime::Duration->new( days => -2 ) );
		206	my $days_ago_4 = dt_from_string()->add_duration( DateTime::Duration->new( days => -4 ) );
		207	my $days_ago_6 = dt_from_string()->add_duration( DateTime::Duration->new( days => -6 ) );
		208	my $days_ago_8 = dt_from_string()->add_duration( DateTime::Duration->new( days => -8 ) );
		209
158	my $query_desc_b1_p = q{first previous biblio search};	210	my $query_desc_b1_p = q{first previous biblio search};
159	my $first_previous_biblio_search = {	211	my $first_previous_biblio_search = {
160	userid => $userid,	212	userid => $userid,
Lines 163-168 sub add { Link Here
163	query_cgi => $query_cgi_b,	215	query_cgi => $query_cgi_b,
164	total => $total,	216	total => $total,
165	type => 'biblio',	217	type => 'biblio',
		218	time => $days_ago_2,
166	};	219	};
167		220
168	my $query_desc_a1_p = q{first previous authority search};	221	my $query_desc_a1_p = q{first previous authority search};
Lines 173-178 sub add { Link Here
173	query_cgi => $query_cgi_a,	226	query_cgi => $query_cgi_a,
174	total => $total,	227	total => $total,
175	type => 'authority',	228	type => 'authority',
		229	time => $days_ago_2,
176	};	230	};
177		231
178	my $query_desc_b2_p = q{second previous biblio search};	232	my $query_desc_b2_p = q{second previous biblio search};
Lines 183-188 sub add { Link Here
183	query_cgi => $query_cgi_b,	237	query_cgi => $query_cgi_b,
184	total => $total,	238	total => $total,
185	type => 'biblio',	239	type => 'biblio',
		240	time => $days_ago_4,
186	};	241	};
187		242
188	my $query_desc_a2_p = q{second previous authority search};	243	my $query_desc_a2_p = q{second previous authority search};
Lines 193-198 sub add { Link Here
193	query_cgi => $query_cgi_a,	248	query_cgi => $query_cgi_a,
194	total => $total,	249	total => $total,
195	type => 'authority',	250	type => 'authority',
		251	time => $days_ago_4,
196	};	252	};
197		253
198		254
Lines 205-210 sub add { Link Here
205	query_cgi => $query_cgi_b,	261	query_cgi => $query_cgi_b,
206	total => $total,	262	total => $total,
207	type => 'biblio',	263	type => 'biblio',
		264	time => $days_ago_4,
208	};	265	};
209		266
210	my $query_desc_a1_c = q{first current authority search};	267	my $query_desc_a1_c = q{first current authority search};
Lines 215-220 sub add { Link Here
215	query_cgi => $query_cgi_a,	272	query_cgi => $query_cgi_a,
216	total => $total,	273	total => $total,
217	type => 'authority',	274	type => 'authority',
		275	time => $days_ago_4,
218	};	276	};
219		277
220	my $query_desc_b2_c = q{second current biblio search};	278	my $query_desc_b2_c = q{second current biblio search};
Lines 225-230 sub add { Link Here
225	query_cgi => $query_cgi_b,	283	query_cgi => $query_cgi_b,
226	total => $total,	284	total => $total,
227	type => 'biblio',	285	type => 'biblio',
		286	time => $days_ago_6,
228	};	287	};
229		288
230	my $query_desc_a2_c = q{second current authority search};	289	my $query_desc_a2_c = q{second current authority search};
Lines 235-240 sub add { Link Here
235	query_cgi => $query_cgi_a,	294	query_cgi => $query_cgi_a,
236	total => $total,	295	total => $total,
237	type => 'authority',	296	type => 'authority',
		297	time => $days_ago_6,
238	};	298	};
239		299
240	my $query_desc_a3_c = q{third current authority search};	300	my $query_desc_a3_c = q{third current authority search};
Lines 245-250 sub add { Link Here
245	query_cgi => $query_cgi_a,	305	query_cgi => $query_cgi_a,
246	total => $total,	306	total => $total,
247	type => 'authority',	307	type => 'authority',
		308	time => $days_ago_8,
248	};	309	};
249		310
250		311

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 66-71 sub delete { Link Here
66	my $sessionid = $params->{sessionid};	67	my $sessionid = $params->{sessionid};
67	my $type = $params->{type} \|\| q{};	68	my $type = $params->{type} \|\| q{};
68	my $previous = $params->{previous} \|\| 0;	69	my $previous = $params->{previous} \|\| 0;
		70	my $interval = $params->{interval} \|\| 0;
69		71
70	unless ( ref( $id ) ) {	72	unless ( ref( $id ) ) {
71	$id = $id ? [ $id ] : [];	73	$id = $id ? [ $id ] : [];
Lines 99-110 sub delete { Link Here
99	$query .= q{ AND type = ?}	101	$query .= q{ AND type = ?}
100	if $type;	102	if $type;
101		103
		104	# FIXME DATE_SUB is a Mysql-ism. Postgres uses: datefield - INTERVAL '6 months'
		105	$query .= q{ AND time < DATE_SUB( NOW(), INTERVAL ? DAY )}
		106	if $interval;
		107
102	$dbh->do(	108	$dbh->do(
103	$query, {},	109	$query, {},
104	( @$id ? ( @$id ) : () ),	110	( @$id ? ( @$id ) : () ),
105	( $userid ? $userid : () ),	111	( $userid ? $userid : () ),
106	( $sessionid ? $sessionid : () ),	112	( $sessionid ? $sessionid : () ),
107	( $type ? $type : () )	113	( $type ? $type : () ),
		114	( $interval ? $interval : () ),
108	);	115	);
109	}	116	}
110		117

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	-