Add annotation @ExpectMaxUpdatedColumn by ablanchard · Pull Request #2 · quick-perf/quickperf

ablanchard · 2019-06-17T12:13:42Z

Motivation

Users might want to ensure that a method to not update more columns that required. By putting @ExpectMaxUpdatedColumn(3) quickperf wil assert that no more than 3 columns have been updated.

Implementation

Context

We want to count columns passed in the update SQL query. We will count the parameter setted in the set clause of the query. We suppose also that with are using PreparedStatement to execute this update. This is what hibernate do.

Counting

How to return 2 in this example UPDATE book SET isbn = ?, title = ? WHERE id = ?
To count updated columns, we cannot rely on query.getParametersList().get(0) because this list will contains all 3 parameters of the query.
Instead we can count the occurrences of the character ? which represent a parameter. We must count those occurrences only on the substring SET isbn = ?, title = ? to exclude ? placeholders used elsewhere in the query.

Testing

It would be great to unit test the method SqlExecutions#countUpdatedColumn with different string query. But the module sql-annotations do not have yet a testing package neither a testing framework.
So @jeanbisutti what do you think about adding one ?

Not implemented

According to postgres documentation of Update statement (https://www.postgresql.org/docs/9.1/sql-update.html) :

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ * ] [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Update can also contains FROM or RETURNING. We should consider those keywords also to extract the substring on which we do the count.

jeanbisutti · 2019-06-24T21:07:51Z

Many thanks for your contribution!

With the current implementation, we find that the following tests don't fail but they should. In these cases, the update queries don't contain bind parameters.

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        String sql = "UPDATE book SET isbn ='12EEE', title = 'Book title'";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
       String sql = "UPDATE book SET isbn ='12,EEE', title = 'Book title'";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        SString sql = "UPDATE book SET isbn ='12EEE', title = ' '', '";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

Could you please write automatic tests for the previous use cases and update the implementation to have the expected behavior?

The number of updated columns may be found considering the number of commas not between two quotes. You may transform the sql String into a char array and iterate on this array to do this.

We prefer fast (memory database) integration tests to unit tests because integration tests check the whole implementation. In some specific cases, some unit tests could be interesting. For example, in MaxUpdatedColumnsPerMeasureExtractor we could mock SqlExecutions and write tests for update syntaxes specific to PostgreSQL (not asked in the frame of this pull request but if you are interested you could submit another pull request, we would be happy of that :))

One more time, thanks a lot for your contribution.

jeanbisutti · 2019-07-07T18:29:12Z

I wrote the additional tests:

   @Test
    public void
    should_fail_if_no_bind_parameters_and_the_number_of_updated_columns_is_greater_than_the_expected_number() {

        // GIVEN
        Class<?> testClass = AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParameters.class;

        // WHEN
        PrintableResult printableResult = PrintableResult.testResult(testClass);

        // THEN
        SoftAssertions softAssertions = new SoftAssertions();

        softAssertions.assertThat(printableResult.failureCount()).isEqualTo(1);

        softAssertions.assertThat(printableResult.toString())
                      .contains("Maximum expected number of updated columns <1> but is <2>.")
                      .contains("UPDATE")
                      .contains("book")
        ;

        softAssertions.assertAll();

    }

    @Test
    public void
    should_fail_if_no_bind_parameters_and_comma_in_updated_column_and_the_number_of_updated_columns_is_greater_than_the_expected_number() {

        // GIVEN
        Class<?> testClass = AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParametersAndAQuoteInUpdatedColumn.class;

        // WHEN
        PrintableResult printableResult = PrintableResult.testResult(testClass);

        // THEN
        SoftAssertions softAssertions = new SoftAssertions();

        softAssertions.assertThat(printableResult.failureCount()).isEqualTo(1);

        softAssertions.assertThat(printableResult.toString())
                      .contains("Maximum expected number of updated columns <1> but is <2>.")
                      .contains("UPDATE")
                      .contains("book")
        ;

        softAssertions.assertAll();

    }

    @Test
    public void
    should_fail_if_no_bind_parameters_and_quote_in_updated_column_and_the_number_of_updated_columns_is_greater_than_the_expected_number() {

        // GIVEN
        Class<?> testClass = AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParametersAndAQuoteInUpdatedColumn.class;

        // WHEN
        PrintableResult printableResult = PrintableResult.testResult(testClass);

        // THEN
        SoftAssertions softAssertions = new SoftAssertions();

        softAssertions.assertThat(printableResult.failureCount()).isEqualTo(1);

        softAssertions.assertThat(printableResult.toString())
                      .contains("Maximum expected number of updated columns <1> but is <2>.")
                      .contains("UPDATE")
                      .contains("book")
        ;

        softAssertions.assertAll();

    }

/*
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 *
 * Copyright 2019-2019 the original author or authors.
 */

package org.quickperf.sql.update.column;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.quickperf.junit4.QuickPerfJUnitRunner;
import org.quickperf.sql.SqlTestBase;
import org.quickperf.sql.annotation.ExpectMaxUpdatedColumn;

import javax.persistence.EntityManager;
import javax.persistence.Query;

@RunWith(QuickPerfJUnitRunner.class)
public class AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParameters extends SqlTestBase {

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        String sql = "UPDATE book SET isbn ='12EEE', title = 'Book title'";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

}

/*
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 *
 * Copyright 2019-2019 the original author or authors.
 */

package org.quickperf.sql.update.column;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.quickperf.junit4.QuickPerfJUnitRunner;
import org.quickperf.sql.SqlTestBase;
import org.quickperf.sql.annotation.ExpectMaxUpdatedColumn;

import javax.persistence.EntityManager;
import javax.persistence.Query;

@RunWith(QuickPerfJUnitRunner.class)
public class AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParametersAndACommaInUpdatedColumn extends SqlTestBase {

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        String sql = "UPDATE book SET isbn ='12,EEE', title = 'Book title'";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

}

/*
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations under the License.
 *
 * Copyright 2019-2019 the original author or authors.
 */

package org.quickperf.sql.update.column;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.quickperf.junit4.QuickPerfJUnitRunner;
import org.quickperf.sql.SqlTestBase;
import org.quickperf.sql.annotation.ExpectMaxUpdatedColumn;

import javax.persistence.EntityManager;
import javax.persistence.Query;

@RunWith(QuickPerfJUnitRunner.class)
public class AClassAnnotatedWithExpectMaxUpdatedColumnFailingInCaseOfNoBindParametersAndAQuoteInUpdatedColumn extends SqlTestBase {

    @Test
    @ExpectMaxUpdatedColumn(1)
    public void execute_update_with_two_columns_updated() {
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        String sql = "UPDATE book SET isbn ='12,EEE', title = 'Book title'";
        Query nativeQuery = em.createNativeQuery(sql);
        nativeQuery.executeUpdate();
        em.getTransaction().commit();
    }

}

Artus2b approved these changes Jun 24, 2019

View reviewed changes

jeanbisutti force-pushed the master branch from 6d2082d to b8b9a2d Compare July 14, 2019 14:31

ablanchard force-pushed the add-expect-updated-column branch from 6d35d6c to 324c10e Compare January 28, 2020 12:33

Add annotation @ExpectMaxUpdatedColumn

a876998

ablanchard force-pushed the add-expect-updated-column branch from 43261de to a876998 Compare January 30, 2020 13:28

jeanbisutti merged commit eb81497 into quick-perf:master Jan 30, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add annotation @ExpectMaxUpdatedColumn#2

Add annotation @ExpectMaxUpdatedColumn#2
jeanbisutti merged 1 commit intoquick-perf:masterfrom
ablanchard:add-expect-updated-column

ablanchard commented Jun 17, 2019

Uh oh!

jeanbisutti commented Jun 24, 2019

Uh oh!

jeanbisutti commented Jul 7, 2019

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

ablanchard commented Jun 17, 2019

Motivation

Implementation

Context

Counting

Testing

Not implemented

Uh oh!

jeanbisutti commented Jun 24, 2019

Uh oh!

jeanbisutti commented Jul 7, 2019

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants