Attachment #21364 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 build_new_cookie_value {

    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 181-187 if($pLogs) { Link Here

(-)a/misc/cronjobs/cleanup_database.pl (-1 / +1 lines)
181		181
182	if($pSearchhistory) {	182	if($pSearchhistory) {
183	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;	183	print "Purging records older than $pSearchhistory from search_history.\n" if $verbose;
184	PurgeSearchHistory($pSearchhistory);	184	C4::Search::History::delete({ interval => $pSearchhistory });
185	print "Done with purging search_history.\n" if $verbose;	185	print "Done with purging search_history.\n" if $verbose;
186	}	186	}
187		187





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/AddSearchHistory PurgeSearchHistory/); # GetSearchHistory is not exported

# 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.
AddSearchHistory(12345, 'session_1', 'query_desc_1', 'query_cgi_1', 5);
AddSearchHistory(12345, 'session_1', 'query_desc_2', 'query_cgi_2', 6);
AddSearchHistory(12345, 'session_1', 'query_desc_3', 'query_cgi_3', 7);
AddSearchHistory(56789, 'session_2', 'query_desc_4', 'query_cgi_4', 8);
is(_get_history_count(), 4, 'successfully added four search_history rows');

# We're not testing GetSearchHistory at present because it is broken...
# see bug 10677

# 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 2188-2200 sub enabled_staff_search_views Link Here
2188	);	2187	);
2189	}	2188	}
2190		2189
2191	sub PurgeSearchHistory{
2192	my ($pSearchhistory)=@_;
2193	my $dbh = C4::Context->dbh;
2194	my $sth = $dbh->prepare("DELETE FROM search_history WHERE time < DATE_SUB( NOW(), INTERVAL ? DAY )");
2195	$sth->execute($pSearchhistory) or die $dbh->errstr;
2196	}
2197
2198	=head2 z3950_search_args	2190	=head2 z3950_search_args
2199		2191
2200	$arrayref = z3950_search_args($matchpoints)	2192	$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 17-22 sub add { Link Here

(-)a/C4/Search/History.pm (-3 / +10 lines)
17	my $query_cgi = $params->{query_cgi};	17	my $query_cgi = $params->{query_cgi};
18	my $total = $params->{total} // 0;	18	my $total = $params->{total} // 0;
19	my $type = $params->{type} \|\| 'biblio';	19	my $type = $params->{type} \|\| 'biblio';
		20	my $time = $params->{time};
20		21
21	my $dbh = C4::Context->dbh;	22	my $dbh = C4::Context->dbh;
22		23
Lines 25-35 sub add { Link Here
25	INSERT INTO search_history(	26	INSERT INTO search_history(
26	userid, sessionid, query_desc, query_cgi, type, total, time	27	userid, sessionid, query_desc, query_cgi, type, total, time
27	) VALUES(	28	) VALUES(
28	?, ?, ?, ?, ?, ?, NOW()	29	?, ?, ?, ?, ?, ?, ?
29	)	30	)
30	};	31	};
31	my $sth = $dbh->prepare($query);	32	my $sth = $dbh->prepare($query);
32	$sth->execute($userid, $sessionid, $query_desc, $query_cgi, $type, $total);	33	$sth->execute($userid, $sessionid, $query_desc, $query_cgi, $type, $total, $time);
33	}	34	}
34		35
35	sub build_new_cookie_value {	36	sub build_new_cookie_value {
Lines 69-74 sub delete { Link Here
69	my $sessionid = $params->{sessionid};	70	my $sessionid = $params->{sessionid};
70	my $type = $params->{type} \|\| q{};	71	my $type = $params->{type} \|\| q{};
71	my $previous = $params->{previous} \|\| 0;	72	my $previous = $params->{previous} \|\| 0;
		73	my $interval = $params->{interval} \|\| 0;
72		74
73	unless ( $userid ) {	75	unless ( $userid ) {
74	warn "ERROR: userid is required for history search";	76	warn "ERROR: userid is required for history search";
Lines 90-100 sub delete { Link Here
90	$query .= q{ AND type = ?}	92	$query .= q{ AND type = ?}
91	if $type;	93	if $type;
92		94
		95	# FIXME DATE_SUB is a Mysql-ism. Postgres uses: datefield - INTERVAL '6 months'
		96	$query .= q{ AND time < DATE_SUB( NOW(), INTERVAL ? DAY )}
		97	if $interval;
		98
93	$dbh->do(	99	$dbh->do(
94	$query, {},	100	$query, {},
95	$userid,	101	$userid,
96	( $sessionid ? $sessionid : () ),	102	( $sessionid ? $sessionid : () ),
97	( $type ? $type : () )	103	( $type ? $type : () ),
		104	( $interval ? $interval : () )
98	);	105	);
99	}	106	}
100		107

Lines 1-71 Link Here

(-)a/t/db_dependent/Search_SearchHistory.t (-72 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/AddSearchHistory PurgeSearchHistory/); # GetSearchHistory is not exported
25
26	# Start transaction
27	my $dbh = C4::Context->dbh;
28	$dbh->{AutoCommit} = 0;
29	$dbh->{RaiseError} = 1;
30
31	# start with a clean slate
32	$dbh->do('DELETE FROM search_history');
33	is(_get_history_count(), 0, 'starting off with nothing in search_history');
34
35	# Add some history rows. Note that we're ignoring the return value;
36	# since the search_history table doesn't have an auto_increment
37	# column, it appears that the value of $dbh->last_insert_id() is
38	# useless for determining if the insert failed.
39	AddSearchHistory(12345, 'session_1', 'query_desc_1', 'query_cgi_1', 5);
40	AddSearchHistory(12345, 'session_1', 'query_desc_2', 'query_cgi_2', 6);
41	AddSearchHistory(12345, 'session_1', 'query_desc_3', 'query_cgi_3', 7);
42	AddSearchHistory(56789, 'session_2', 'query_desc_4', 'query_cgi_4', 8);
43	is(_get_history_count(), 4, 'successfully added four search_history rows');
44
45	# We're not testing GetSearchHistory at present because it is broken...
46	# see bug 10677
47
48	# munge some dates
49	my $sth = $dbh->prepare('
50	UPDATE search_history
51	SET time = DATE_SUB(time, INTERVAL ? DAY)
52	WHERE query_desc = ?
53	');
54	$sth->execute(46, 'query_desc_1');
55	$sth->execute(31, 'query_desc_2');
56
57	PurgeSearchHistory(45);
58	is(_get_history_count(), 3, 'purged history older than 45 days');
59	PurgeSearchHistory(30);
60	is(_get_history_count(), 2, 'purged history older than 30 days');
61	PurgeSearchHistory(-1);
62	is(_get_history_count(), 0, 'purged all history');
63
64	sub _get_history_count {
65	my $count_sth = $dbh->prepare('SELECT COUNT(*) FROM search_history');
66	$count_sth->execute();
67	my $count = $count_sth->fetchall_arrayref();
68	return $count->[0]->[0];
69	}
70
71	$dbh->rollback;
72	-