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

(-)a/t/db_dependent/Reports/Guided.t (-63 / +255 lines)
Lines 1-4 Link Here
1
# Copyright 2012 Catalyst IT Ltd.
1
# Copyright 2012 Catalyst IT Ltd.
2
# Copyright Koha Development Team
2
#
3
#
3
# This file is part of Koha.
4
# This file is part of Koha.
4
#
5
#
Lines 15-89 Link Here
15
# You should have received a copy of the GNU General Public License
16
# You should have received a copy of the GNU General Public License
16
# along with Koha; if not, see <http://www.gnu.org/licenses>.
17
# along with Koha; if not, see <http://www.gnu.org/licenses>.
17
18
18
use strict;
19
use Modern::Perl;
19
use warnings;
20
20
21
use Test::More tests => 19;                      # last test to print
21
use Test::More tests => 4;
22
use Test::MockModule;
23
use Test::Warn;
22
24
23
use_ok('C4::Reports::Guided');
25
use_ok('C4::Reports::Guided');
24
26
25
# This is the query I found that triggered bug 8594.
27
subtest 'prevent from breaking some subqueries' => sub {
26
my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
28
    plan tests => 18;
27
    aqorders.entrydate, aqorders.datereceived,
29
    # This is the query I found that triggered bug 8594.
28
    (SELECT DATE(datetime) FROM statistics
30
    my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
29
        WHERE itemnumber=items.itemnumber AND
31
        aqorders.entrydate, aqorders.datereceived,
30
            (type='return' OR type='issue') LIMIT 1)
31
    AS shelvedate,
32
    DATEDIFF(COALESCE(
33
        (SELECT DATE(datetime) FROM statistics
32
        (SELECT DATE(datetime) FROM statistics
34
            WHERE itemnumber=items.itemnumber AND
33
            WHERE itemnumber=items.itemnumber AND
35
            (type='return' OR type='issue') LIMIT 1),
34
                (type='return' OR type='issue') LIMIT 1)
36
    aqorders.datereceived), aqorders.entrydate) AS totaldays
35
        AS shelvedate,
37
FROM aqorders
36
        DATEDIFF(COALESCE(
38
LEFT JOIN biblio USING (biblionumber)
37
            (SELECT DATE(datetime) FROM statistics
39
LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
38
                WHERE itemnumber=items.itemnumber AND
40
    AND dateaccessioned=aqorders.datereceived)
39
                (type='return' OR type='issue') LIMIT 1),
41
WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
40
        aqorders.datereceived), aqorders.entrydate) AS totaldays
42
    AND items.homebranch LIKE 'INFO'
41
    FROM aqorders
43
ORDER BY title";
42
    LEFT JOIN biblio USING (biblionumber)
44
43
    LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
45
my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
44
        AND dateaccessioned=aqorders.datereceived)
46
is($res_sql, $sql, "Not breaking subqueries");
45
    WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
47
is($res_lim1, 0, "Returns correct default offset");
46
        AND items.homebranch LIKE 'INFO'
48
is($res_lim2, undef, "Returns correct default LIMIT");
47
    ORDER BY title";
49
48
50
# Now the same thing, but we want it to remove the LIMIT from the end
49
    my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
51
50
    is($res_sql, $sql, "Not breaking subqueries");
52
my $test_sql = $res_sql . " LIMIT 242";
51
    is($res_lim1, 0, "Returns correct default offset");
53
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
52
    is($res_lim2, undef, "Returns correct default LIMIT");
54
# The replacement drops a ' ' where the limit was
53
55
is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
54
    # Now the same thing, but we want it to remove the LIMIT from the end
56
is($res_lim1, 0, "Returns correct default offset");
55
57
is($res_lim2, 242, "Returns correct extracted LIMIT");
56
    my $test_sql = $res_sql . " LIMIT 242";
58
57
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
59
$test_sql = $res_sql . " LIMIT 13,242";
58
    # The replacement drops a ' ' where the limit was
60
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
59
    is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
61
# The replacement drops a ' ' where the limit was
60
    is($res_lim1, 0, "Returns correct default offset");
62
is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
61
    is($res_lim2, 242, "Returns correct extracted LIMIT");
63
is($res_lim1, 13, "Returns correct extracted offset");
62
64
is($res_lim2, 242, "Returns correct extracted LIMIT");
63
    $test_sql = $res_sql . " LIMIT 13,242";
65
64
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
66
# After here is the simpler case, where there isn't a WHERE clause to worry
65
    # The replacement drops a ' ' where the limit was
67
# about.
66
    is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
68
67
    is($res_lim1, 13, "Returns correct extracted offset");
69
# First case with nothing to change
68
    is($res_lim2, 242, "Returns correct extracted LIMIT");
70
$sql = "SELECT * FROM items";
69
71
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
70
    # After here is the simpler case, where there isn't a WHERE clause to worry
72
is($res_sql, $sql, "Not breaking simple queries");
71
    # about.
73
is($res_lim1, 0, "Returns correct default offset");
72
74
is($res_lim2, undef, "Returns correct default LIMIT");
73
    # First case with nothing to change
75
74
    $sql = "SELECT * FROM items";
76
$test_sql = $sql . " LIMIT 242";
75
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
77
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
76
    is($res_sql, $sql, "Not breaking simple queries");
78
is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
77
    is($res_lim1, 0, "Returns correct default offset");
79
is($res_lim1, 0, "Returns correct default offset");
78
    is($res_lim2, undef, "Returns correct default LIMIT");
80
is($res_lim2, 242, "Returns correct extracted LIMIT");
79
81
80
    $test_sql = $sql . " LIMIT 242";
82
$test_sql = $sql . " LIMIT 13,242";
81
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
83
($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
82
    is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
84
is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
83
    is($res_lim1, 0, "Returns correct default offset");
85
is($res_lim1, 13, "Returns correct extracted offset");
84
    is($res_lim2, 242, "Returns correct extracted LIMIT");
86
is($res_lim2, 242, "Returns correct extracted LIMIT");
85
86
    $test_sql = $sql . " LIMIT 13,242";
87
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
88
    is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
89
    is($res_lim1, 13, "Returns correct extracted offset");
90
    is($res_lim2, 242, "Returns correct extracted LIMIT");
91
};
92
93
subtest "Test save_report and delete_report" => sub {
94
    plan tests => 12;
95
    my $context = new Test::MockModule('C4::Context');
96
    my $koha = new Test::MockModule('C4::Koha');
97
98
    sub MockedIsAuthorisedValueCategory {
99
        my $authorised_value = shift;
100
101
        if ( $authorised_value eq 'LOC' ) {
102
            return 1;
103
        } else {
104
            return 0;
105
        }
106
    }
107
108
    $koha->mock(
109
        'IsAuthorisedValueCategory',
110
        \&MockedIsAuthorisedValueCategory
111
    );
112
113
    {   # GetReservedAuthorisedValues tests
114
        # This one will catch new reserved words not added
115
        # to GetReservedAuthorisedValues
116
        my %test_authval = (
117
            'date' => 1,
118
            'branches' => 1,
119
            'itemtypes' => 1,
120
            'cn_source' => 1,
121
            'categorycode' => 1,
122
            'biblio_framework' => 1,
123
        );
124
125
        my $reserved_authorised_values = GetReservedAuthorisedValues();
126
        is_deeply(\%test_authval, $reserved_authorised_values,
127
                    'GetReservedAuthorisedValues returns a fixed list');
128
    }
129
130
    {
131
        ok( IsAuthorisedValueValid('LOC'),
132
            'User defined authorised value category is valid');
133
134
        ok( ! IsAuthorisedValueValid('XXX'),
135
            'Not defined authorised value category is invalid');
136
137
        # Loop through the reserved authorised values
138
        foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
139
            ok( IsAuthorisedValueValid($authorised_value),
140
                '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
141
        }
142
    }
143
144
    {   # GetParametersFromSQL tests
145
146
        my $test_query_1 = "
147
            SELECT date_due
148
            FROM old_issues
149
            WHERE YEAR(timestamp) = <<Year|custom_list>> AND
150
                  branchcode = <<Branch|branches>> AND
151
                  borrowernumber = <<Borrower>>
152
        ";
153
154
        my @test_parameters_with_custom_list = (
155
            { 'name' => 'Year', 'authval' => 'custom_list' },
156
            { 'name' => 'Branch', 'authval' => 'branches' },
157
            { 'name' => 'Borrower', 'authval' => undef }
158
        );
159
160
        is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
161
            'SQL params are correctly parsed');
162
163
        # ValidateSQLParameters tests
164
        my @problematic_parameters = ();
165
        push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
166
        is_deeply( ValidateSQLParameters( $test_query_1 ),
167
                   \@problematic_parameters,
168
                   '\'custom_list\' not a valid category' );
169
170
        my $test_query_2 = "
171
            SELECT date_due
172
            FROM old_issues
173
            WHERE YEAR(timestamp) = <<Year|date>> AND
174
                  branchcode = <<Branch|branches>> AND
175
                  borrowernumber = <<Borrower|LOC>>
176
        ";
177
178
        is_deeply( ValidateSQLParameters( $test_query_2 ),
179
            [],
180
            'All parameters valid, empty problematic authvals list');
181
    }
182
};
183
184
subtest "Test IsAuthorisedValueValid" => sub {
185
    plan tests => 17;
186
    can_ok(
187
        'C4::Reports::Guided',
188
        qw(save_report delete_report execute_query)
189
    );
190
191
    my $schema = Koha::Database->new->schema;
192
    $schema->storage->txn_begin;
193
194
    my $dbh = C4::Context->dbh;
195
    $dbh->do(q|DELETE FROM saved_sql|);
196
    $dbh->do(q|DELETE FROM saved_reports|);
197
198
    #Start tests
199
200
    #Test save_report
201
    my $count = scalar( @{ get_saved_reports() } );
202
    is( $count, 0, "There is no report" );
203
204
    my @report_ids;
205
    for my $id ( 1 .. 3 ) {
206
        push @report_ids, save_report({
207
            borrowernumber => $id,
208
            sql            => "SQL$id",
209
            name           => "Name$id",
210
            area           => "area$id",
211
            group          => "group$id",
212
            subgroup       => "subgroup$id",
213
            type           => "type$id",
214
            notes          => "note$id",
215
            cache_expiry   => "null",
216
            public         => "null"
217
        });
218
        $count++;
219
    }
220
    like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
221
    like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
222
    like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );
223
224
    is( scalar( @{ get_saved_reports() } ),
225
        $count, "$count reports have been added" );
226
227
    is( scalar( @{ get_saved_reports( $report_ids[0] ) } ),
228
        1, "filter takes report id" );
229
230
    #Test delete_report
231
    is (delete_report(),undef, "Without id delete_report returns undef");
232
233
    is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
234
    $count--;
235
236
    is( scalar( @{ get_saved_reports() } ), $count, "Report1 has been deleted" );
237
238
    is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
239
    $count -= 2;
240
241
    is( scalar( @{ get_saved_reports() } ),
242
        $count, "Report2 and report3 have been deleted" );
243
244
    my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
245
    my $results = $sth->fetchall_arrayref;
246
    is(scalar(@$results), 1, 'running a query returned a result');
247
248
    my $version = C4::Context->preference('Version');
249
    $sth = execute_query(
250
        'SELECT value FROM systempreferences WHERE variable = ?',
251
        0,
252
        10,
253
        [ 'Version' ],
254
    );
255
    $results = $sth->fetchall_arrayref;
256
    is_deeply(
257
        $results,
258
        [ [ $version ] ],
259
        'running a query with a parameter returned the expected result'
260
    );
261
262
    # for next test, we want to let execute_query capture any SQL errors
263
    $dbh->{RaiseError} = 0;
264
    my $errors;
265
    warning_like { ($sth, $errors) = execute_query(
266
            'SELECT surname FRM borrowers',  # error in the query is intentional
267
            0, 10 ) }
268
            qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
269
            "Wrong SQL syntax raises warning";
270
    ok(
271
        defined($errors) && exists($errors->{queryerr}),
272
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
273
    );
274
275
    is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
276
        "get_report_areas returns the correct array of report areas");
277
278
};
87
279
88
sub trim {
280
sub trim {
89
    my ($s) = @_;
281
    my ($s) = @_;
(-)a/t/db_dependent/ReportsGuided.t (-102 lines)
Lines 1-102 Link Here
1
#!/usr/bin/perl
2
3
use Modern::Perl;
4
5
use Test::More tests => 13;
6
use Test::MockModule;
7
8
use t::lib::Mocks;
9
10
use_ok('C4::Reports::Guided');
11
12
my $context = new Test::MockModule('C4::Context');
13
my $koha = new Test::MockModule('C4::Koha');
14
15
BEGIN {
16
    t::lib::Mocks::mock_dbh;
17
}
18
19
sub MockedIsAuthorisedValueCategory {
20
    my $authorised_value = shift;
21
22
    if ( $authorised_value eq 'LOC' ) {
23
        return 1;
24
    } else {
25
        return 0;
26
    }
27
}
28
29
$koha->mock(
30
    'IsAuthorisedValueCategory',
31
    \&MockedIsAuthorisedValueCategory
32
);
33
34
{   # GetReservedAuthorisedValues tests
35
    # This one will catch new reserved words not added
36
    # to GetReservedAuthorisedValues
37
    my %test_authval = (
38
        'date' => 1,
39
        'branches' => 1,
40
        'itemtypes' => 1,
41
        'cn_source' => 1,
42
        'categorycode' => 1,
43
        'biblio_framework' => 1,
44
    );
45
46
    my $reserved_authorised_values = GetReservedAuthorisedValues();
47
    is_deeply(\%test_authval, $reserved_authorised_values,
48
                'GetReservedAuthorisedValues returns a fixed list');
49
}
50
51
{
52
    ok( IsAuthorisedValueValid('LOC'),
53
        'User defined authorised value category is valid');
54
55
    ok( ! IsAuthorisedValueValid('XXX'),
56
        'Not defined authorised value category is invalid');
57
58
    # Loop through the reserved authorised values
59
    foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
60
        ok( IsAuthorisedValueValid($authorised_value),
61
            '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
62
    }
63
}
64
65
{   # GetParametersFromSQL tests
66
67
    my $test_query_1 = "
68
        SELECT date_due
69
        FROM old_issues
70
        WHERE YEAR(timestamp) = <<Year|custom_list>> AND
71
              branchcode = <<Branch|branches>> AND
72
              borrowernumber = <<Borrower>>
73
    ";
74
75
    my @test_parameters_with_custom_list = (
76
        { 'name' => 'Year', 'authval' => 'custom_list' },
77
        { 'name' => 'Branch', 'authval' => 'branches' },
78
        { 'name' => 'Borrower', 'authval' => undef }
79
    );
80
81
    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
82
        'SQL params are correctly parsed');
83
84
    # ValidateSQLParameters tests
85
    my @problematic_parameters = ();
86
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
87
    is_deeply( ValidateSQLParameters( $test_query_1 ),
88
               \@problematic_parameters,
89
               '\'custom_list\' not a valid category' );
90
91
    my $test_query_2 = "
92
        SELECT date_due
93
        FROM old_issues
94
        WHERE YEAR(timestamp) = <<Year|date>> AND
95
              branchcode = <<Branch|branches>> AND
96
              borrowernumber = <<Borrower|LOC>>
97
    ";
98
99
    is_deeply( ValidateSQLParameters( $test_query_2 ),
100
        [],
101
        'All parameters valid, empty problematic authvals list');
102
}
(-)a/t/db_dependent/Reports_Guided.t (-122 lines)
Lines 1-121 Link Here
1
#!/usr/bin/perl
2
3
# This file is part of Koha.
4
#
5
# Koha is free software; you can redistribute it and/or modify it
6
# under the terms of the GNU General Public License as published by
7
# the Free Software Foundation; either version 3 of the License, or
8
# (at your option) any later version.
9
#
10
# Koha is distributed in the hope that it will be useful, but
11
# WITHOUT ANY WARRANTY; without even the implied warranty of
12
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13
# GNU General Public License for more details.
14
#
15
# You should have received a copy of the GNU General Public License
16
# along with Koha; if not, see <http://www.gnu.org/licenses>.
17
18
use Modern::Perl;
19
20
use Test::More tests => 18;
21
use Test::Warn;
22
23
use C4::Context;
24
use Koha::Database;
25
26
BEGIN {
27
    use_ok('C4::Reports::Guided');
28
}
29
can_ok(
30
    'C4::Reports::Guided',
31
    qw(save_report delete_report execute_query)
32
);
33
34
my $schema = Koha::Database->new->schema;
35
$schema->storage->txn_begin;
36
37
my $dbh = C4::Context->dbh;
38
$dbh->do(q|DELETE FROM saved_sql|);
39
$dbh->do(q|DELETE FROM saved_reports|);
40
41
#Start tests
42
43
#Test save_report
44
my $count = scalar( @{ get_saved_reports() } );
45
is( $count, 0, "There is no report" );
46
47
my @report_ids;
48
for my $id ( 1 .. 3 ) {
49
    push @report_ids, save_report({
50
        borrowernumber => $id,
51
        sql            => "SQL$id",
52
        name           => "Name$id",
53
        area           => "area$id",
54
        group          => "group$id",
55
        subgroup       => "subgroup$id",
56
        type           => "type$id",
57
        notes          => "note$id",
58
        cache_expiry   => "null",
59
        public         => "null"
60
    });
61
    $count++;
62
}
63
like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
64
like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
65
like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );
66
67
is( scalar( @{ get_saved_reports() } ),
68
    $count, "$count reports have been added" );
69
70
is( scalar( @{ get_saved_reports( $report_ids[0] ) } ),
71
    1, "filter takes report id" );
72
73
#Test delete_report
74
is (delete_report(),undef, "Without id delete_report returns undef");
75
76
is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
77
$count--;
78
79
is( scalar( @{ get_saved_reports() } ), $count, "Report1 has been deleted" );
80
81
is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
82
$count -= 2;
83
84
is( scalar( @{ get_saved_reports() } ),
85
    $count, "Report2 and report3 have been deleted" );
86
87
my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
88
my $results = $sth->fetchall_arrayref;
89
is(scalar(@$results), 1, 'running a query returned a result');
90
91
my $version = C4::Context->preference('Version');
92
$sth = execute_query(
93
    'SELECT value FROM systempreferences WHERE variable = ?',
94
    0,
95
    10,
96
    [ 'Version' ],
97
);
98
$results = $sth->fetchall_arrayref;
99
is_deeply(
100
    $results,
101
    [ [ $version ] ],
102
    'running a query with a parameter returned the expected result'
103
);
104
105
# for next test, we want to let execute_query capture any SQL errors
106
$dbh->{RaiseError} = 0;
107
my $errors;
108
warning_like { ($sth, $errors) = execute_query(
109
        'SELECT surname FRM borrowers',  # error in the query is intentional
110
        0, 10 ) }
111
        qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
112
        "Wrong SQL syntax raises warning";
113
ok(
114
    defined($errors) && exists($errors->{queryerr}),
115
    'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
116
);
117
118
is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
119
    "get_report_areas returns the correct array of report areas");
120
121
$schema->storage->txn_rollback;
122
- 

Return to bug 15152