Attachment #44577 for bug #15152

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




# Copyright 2012 Catalyst IT Ltd.
# Copyright Koha Development Team
#
# This file is part of Koha.
#

# 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 warnings;

use Test::More tests => 4;
use Test::MockModule;
use Test::Warn;

use_ok('C4::Reports::Guided');

subtest 'prevent from breaking some subqueries' => sub {
    plan tests => 18;
    # This is the query I found that triggered bug 8594.
    my $sql = "SELECT aqorders.ordernumber, biblio.title, biblio.biblionumber, items.homebranch,
        aqorders.entrydate, aqorders.datereceived,
            (type='return' OR type='issue') LIMIT 1)
    AS shelvedate,
    DATEDIFF(COALESCE(
        (SELECT DATE(datetime) FROM statistics
            WHERE itemnumber=items.itemnumber AND
                (type='return' OR type='issue') LIMIT 1)
        AS shelvedate,
        DATEDIFF(COALESCE(
            (SELECT DATE(datetime) FROM statistics
                WHERE itemnumber=items.itemnumber AND
                (type='return' OR type='issue') LIMIT 1),
        aqorders.datereceived), aqorders.entrydate) AS totaldays
    FROM aqorders
    LEFT JOIN biblio USING (biblionumber)
    LEFT JOIN items ON (items.biblionumber = biblio.biblionumber
        AND dateaccessioned=aqorders.datereceived)
    WHERE (entrydate >= '2011-01-01' AND (datereceived < '2011-02-01' OR datereceived IS NULL))
        AND items.homebranch LIKE 'INFO'
    ORDER BY title";

    my ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
    is($res_sql, $sql, "Not breaking subqueries");
    is($res_lim1, 0, "Returns correct default offset");
    is($res_lim2, undef, "Returns correct default LIMIT");

    # Now the same thing, but we want it to remove the LIMIT from the end

    my $test_sql = $res_sql . " LIMIT 242";
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
    # The replacement drops a ' ' where the limit was
    is(trim($res_sql), $sql, "Correctly removes only final LIMIT");
    is($res_lim1, 0, "Returns correct default offset");
    is($res_lim2, 242, "Returns correct extracted LIMIT");

    $test_sql = $res_sql . " LIMIT 13,242";
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
    # The replacement drops a ' ' where the limit was
    is(trim($res_sql), $sql, "Correctly removes only final LIMIT (with offset)");
    is($res_lim1, 13, "Returns correct extracted offset");
    is($res_lim2, 242, "Returns correct extracted LIMIT");

    # After here is the simpler case, where there isn't a WHERE clause to worry
    # about.

    # First case with nothing to change
    $sql = "SELECT * FROM items";
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($sql);
    is($res_sql, $sql, "Not breaking simple queries");
    is($res_lim1, 0, "Returns correct default offset");
    is($res_lim2, undef, "Returns correct default LIMIT");

    $test_sql = $sql . " LIMIT 242";
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
    is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case");
    is($res_lim1, 0, "Returns correct default offset");
    is($res_lim2, 242, "Returns correct extracted LIMIT");

    $test_sql = $sql . " LIMIT 13,242";
    ($res_sql, $res_lim1, $res_lim2) = C4::Reports::Guided::strip_limit($test_sql);
    is(trim($res_sql), $sql, "Correctly removes LIMIT in simple case (with offset)");
    is($res_lim1, 13, "Returns correct extracted offset");
    is($res_lim2, 242, "Returns correct extracted LIMIT");
};

subtest "Test save_report and delete_report" => sub {
    plan tests => 12;
    my $context = new Test::MockModule('C4::Context');
    my $koha = new Test::MockModule('C4::Koha');

    sub MockedIsAuthorisedValueCategory {
        my $authorised_value = shift;

        if ( $authorised_value eq 'LOC' ) {
            return 1;
        } else {
            return 0;
        }
    }

    $koha->mock(
        'IsAuthorisedValueCategory',
        \&MockedIsAuthorisedValueCategory
    );

    {   # GetReservedAuthorisedValues tests
        # This one will catch new reserved words not added
        # to GetReservedAuthorisedValues
        my %test_authval = (
            'date' => 1,
            'branches' => 1,
            'itemtypes' => 1,
            'cn_source' => 1,
            'categorycode' => 1,
            'biblio_framework' => 1,
        );

        my $reserved_authorised_values = GetReservedAuthorisedValues();
        is_deeply(\%test_authval, $reserved_authorised_values,
                    'GetReservedAuthorisedValues returns a fixed list');
    }

    {
        ok( IsAuthorisedValueValid('LOC'),
            'User defined authorised value category is valid');

        ok( ! IsAuthorisedValueValid('XXX'),
            'Not defined authorised value category is invalid');

        # Loop through the reserved authorised values
        foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
            ok( IsAuthorisedValueValid($authorised_value),
                '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
        }
    }

    {   # GetParametersFromSQL tests

        my $test_query_1 = "
            SELECT date_due
            FROM old_issues
            WHERE YEAR(timestamp) = <<Year|custom_list>> AND
                  branchcode = <<Branch|branches>> AND
                  borrowernumber = <<Borrower>>
        ";

        my @test_parameters_with_custom_list = (
            { 'name' => 'Year', 'authval' => 'custom_list' },
            { 'name' => 'Branch', 'authval' => 'branches' },
            { 'name' => 'Borrower', 'authval' => undef }
        );

        is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
            'SQL params are correctly parsed');

        # ValidateSQLParameters tests
        my @problematic_parameters = ();
        push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
        is_deeply( ValidateSQLParameters( $test_query_1 ),
                   \@problematic_parameters,
                   '\'custom_list\' not a valid category' );

        my $test_query_2 = "
            SELECT date_due
            FROM old_issues
            WHERE YEAR(timestamp) = <<Year|date>> AND
                  branchcode = <<Branch|branches>> AND
                  borrowernumber = <<Borrower|LOC>>
        ";

        is_deeply( ValidateSQLParameters( $test_query_2 ),
            [],
            'All parameters valid, empty problematic authvals list');
    }
};

subtest "Test IsAuthorisedValueValid" => sub {
    plan tests => 17;
    can_ok(
        'C4::Reports::Guided',
        qw(save_report delete_report execute_query)
    );

    my $schema = Koha::Database->new->schema;
    $schema->storage->txn_begin;

    my $dbh = C4::Context->dbh;
    $dbh->do(q|DELETE FROM saved_sql|);
    $dbh->do(q|DELETE FROM saved_reports|);

    #Start tests

    #Test save_report
    my $count = scalar( @{ get_saved_reports() } );
    is( $count, 0, "There is no report" );

    my @report_ids;
    for my $id ( 1 .. 3 ) {
        push @report_ids, save_report({
            borrowernumber => $id,
            sql            => "SQL$id",
            name           => "Name$id",
            area           => "area$id",
            group          => "group$id",
            subgroup       => "subgroup$id",
            type           => "type$id",
            notes          => "note$id",
            cache_expiry   => "null",
            public         => "null"
        });
        $count++;
    }
    like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
    like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
    like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );

    is( scalar( @{ get_saved_reports() } ),
        $count, "$count reports have been added" );

    is( scalar( @{ get_saved_reports( $report_ids[0] ) } ),
        1, "filter takes report id" );

    #Test delete_report
    is (delete_report(),undef, "Without id delete_report returns undef");

    is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
    $count--;

    is( scalar( @{ get_saved_reports() } ), $count, "Report1 has been deleted" );

    is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
    $count -= 2;

    is( scalar( @{ get_saved_reports() } ),
        $count, "Report2 and report3 have been deleted" );

    my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
    my $results = $sth->fetchall_arrayref;
    is(scalar(@$results), 1, 'running a query returned a result');

    my $version = C4::Context->preference('Version');
    $sth = execute_query(
        'SELECT value FROM systempreferences WHERE variable = ?',
        0,
        10,
        [ 'Version' ],
    );
    $results = $sth->fetchall_arrayref;
    is_deeply(
        $results,
        [ [ $version ] ],
        'running a query with a parameter returned the expected result'
    );

    # for next test, we want to let execute_query capture any SQL errors
    $dbh->{RaiseError} = 0;
    my $errors;
    warning_like { ($sth, $errors) = execute_query(
            'SELECT surname FRM borrowers',  # error in the query is intentional
            0, 10 ) }
            qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
            "Wrong SQL syntax raises warning";
    ok(
        defined($errors) && exists($errors->{queryerr}),
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
    );

    is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
        "get_report_areas returns the correct array of report areas");

};

sub trim {
    my ($s) = @_;




#!/usr/bin/perl

use Modern::Perl;

use Test::More tests => 13;
use Test::MockModule;

use t::lib::Mocks;

use_ok('C4::Reports::Guided');

my $context = new Test::MockModule('C4::Context');
my $koha = new Test::MockModule('C4::Koha');

BEGIN {
    t::lib::Mocks::mock_dbh;
}

sub MockedIsAuthorisedValueCategory {
    my $authorised_value = shift;

    if ( $authorised_value eq 'LOC' ) {
        return 1;
    } else {
        return 0;
    }
}

$koha->mock(
    'IsAuthorisedValueCategory',
    \&MockedIsAuthorisedValueCategory
);

{   # GetReservedAuthorisedValues tests
    # This one will catch new reserved words not added
    # to GetReservedAuthorisedValues
    my %test_authval = (
        'date' => 1,
        'branches' => 1,
        'itemtypes' => 1,
        'cn_source' => 1,
        'categorycode' => 1,
        'biblio_framework' => 1,
    );

    my $reserved_authorised_values = GetReservedAuthorisedValues();
    is_deeply(\%test_authval, $reserved_authorised_values,
                'GetReservedAuthorisedValues returns a fixed list');
}

{
    ok( IsAuthorisedValueValid('LOC'),
        'User defined authorised value category is valid');

    ok( ! IsAuthorisedValueValid('XXX'),
        'Not defined authorised value category is invalid');

    # Loop through the reserved authorised values
    foreach my $authorised_value ( keys %{GetReservedAuthorisedValues()} ) {
        ok( IsAuthorisedValueValid($authorised_value),
            '\''.$authorised_value.'\' is a reserved word, and thus a valid authorised value');
    }
}

{   # GetParametersFromSQL tests

    my $test_query_1 = "
        SELECT date_due
        FROM old_issues
        WHERE YEAR(timestamp) = <<Year|custom_list>> AND
              branchcode = <<Branch|branches>> AND
              borrowernumber = <<Borrower>>
    ";

    my @test_parameters_with_custom_list = (
        { 'name' => 'Year', 'authval' => 'custom_list' },
        { 'name' => 'Branch', 'authval' => 'branches' },
        { 'name' => 'Borrower', 'authval' => undef }
    );

    is_deeply( GetParametersFromSQL($test_query_1), \@test_parameters_with_custom_list,
        'SQL params are correctly parsed');

    # ValidateSQLParameters tests
    my @problematic_parameters = ();
    push @problematic_parameters, { 'name' => 'Year', 'authval' => 'custom_list' };
    is_deeply( ValidateSQLParameters( $test_query_1 ),
               \@problematic_parameters,
               '\'custom_list\' not a valid category' );

    my $test_query_2 = "
        SELECT date_due
        FROM old_issues
        WHERE YEAR(timestamp) = <<Year|date>> AND
              branchcode = <<Branch|branches>> AND
              borrowernumber = <<Borrower|LOC>>
    ";

    is_deeply( ValidateSQLParameters( $test_query_2 ),
        [],
        'All parameters valid, empty problematic authvals list');
}




#!/usr/bin/perl

# This file is part of Koha.
#
# 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 => 18;
use Test::Warn;

use C4::Context;
use Koha::Database;

BEGIN {
    use_ok('C4::Reports::Guided');
}
can_ok(
    'C4::Reports::Guided',
    qw(save_report delete_report execute_query)
);

my $schema = Koha::Database->new->schema;
$schema->storage->txn_begin;

my $dbh = C4::Context->dbh;
$dbh->do(q|DELETE FROM saved_sql|);
$dbh->do(q|DELETE FROM saved_reports|);

#Start tests

#Test save_report
my $count = scalar( @{ get_saved_reports() } );
is( $count, 0, "There is no report" );

my @report_ids;
for my $id ( 1 .. 3 ) {
    push @report_ids, save_report({
        borrowernumber => $id,
        sql            => "SQL$id",
        name           => "Name$id",
        area           => "area$id",
        group          => "group$id",
        subgroup       => "subgroup$id",
        type           => "type$id",
        notes          => "note$id",
        cache_expiry   => "null",
        public         => "null"
    });
    $count++;
}
like( $report_ids[0], '/^\d+$/', "Save_report returns an id for first" );
like( $report_ids[1], '/^\d+$/', "Save_report returns an id for second" );
like( $report_ids[2], '/^\d+$/', "Save_report returns an id for third" );

is( scalar( @{ get_saved_reports() } ),
    $count, "$count reports have been added" );

is( scalar( @{ get_saved_reports( $report_ids[0] ) } ),
    1, "filter takes report id" );

#Test delete_report
is (delete_report(),undef, "Without id delete_report returns undef");

is( delete_report( $report_ids[0] ), 1, "report 1 is deleted" );
$count--;

is( scalar( @{ get_saved_reports() } ), $count, "Report1 has been deleted" );

is( delete_report( $report_ids[1], $report_ids[2] ), 2, "report 2 and 3 are deleted" );
$count -= 2;

is( scalar( @{ get_saved_reports() } ),
    $count, "Report2 and report3 have been deleted" );

my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
my $results = $sth->fetchall_arrayref;
is(scalar(@$results), 1, 'running a query returned a result');

my $version = C4::Context->preference('Version');
$sth = execute_query(
    'SELECT value FROM systempreferences WHERE variable = ?',
    0,
    10,
    [ 'Version' ],
);
$results = $sth->fetchall_arrayref;
is_deeply(
    $results,
    [ [ $version ] ],
    'running a query with a parameter returned the expected result'
);

# for next test, we want to let execute_query capture any SQL errors
$dbh->{RaiseError} = 0;
my $errors;
warning_like { ($sth, $errors) = execute_query(
        'SELECT surname FRM borrowers',  # error in the query is intentional
        0, 10 ) }
        qr/^DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
        "Wrong SQL syntax raises warning";
ok(
    defined($errors) && exists($errors->{queryerr}),
    'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
);

is_deeply( get_report_areas(), [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ],
    "get_report_areas returns the correct array of report areas");

$schema->storage->txn_rollback;
- 

Return to bug 15152

Lines 1-4 Link Here

(-)a/t/db_dependent/Reports/Guided.t (-63 / +255 lines)
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) = @_;

Lines 1-102 Link Here

(-)a/t/db_dependent/ReportsGuided.t (-102 lines)
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	}

Lines 1-121 Link Here

(-)a/t/db_dependent/Reports_Guided.t (-122 lines)
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	-