The Altinity Way to Building Enterprise QA Process in ClickHouse – Part 2

By Vitaliy Zakaznikov on October 21st, 2020

The Altinity Way to Building Enterprise QA Process in ClickHouse – Part 2

Vitaliy Zakaznikov ClickHouseDatabase TestingQA Process

In the previous article, we introduced the approach, technologies, and tools to build enterprise QA process in ClickHouse. Specifically, we described how we work with requirements at the center of our enterprise QA process. You can find an example of how we use requirements in ClickHouse GitHub repository inside the [tests/testflows/] folder. For example, you can look at the requirements for testing the LDAP user authentication feature.

In this article, we will continue our journey into our enterprise QA process and show how we use TestFlows open source testing framework to help us author our automated tests. We will show how we link tests with the requirements and combine test code with explicit test procedures to provide us auto-generated test specifications and requirements coverage reports for each test run. You can find the source code that we use in this article at our enterprise QA repo on Gitlab.

Requirements To Be Tested

To write tests, we need to know what requirements we need to verify. If we write tests that do not cover the requirements, then we are not focusing our testing on the desired functionality. In the same way, if we write requirements that cannot be verified by tests, then our requirements are not focused on the desired functionality as we cannot verify it.

There is no point in writing tests that don’t cover requirements in the same way as there is no point in writing requirements that can’t be verified by tests.

In our case, in the previous article, we did define a couple of verifiable requirements for the CREATE USER statement. Armed with those requirements, we can proceed to the next step and write automated tests to verify each of them.

Let’s Write The Tests

First, we need a folder for our test project to place our requirements and tests.

mkdir ~/TestingClickHouse

Then we need to move our requirements document inside the test project folder.

mv requirements.md ~/TestingClickHouse
cd ~/TestingClickHouse

With the requirements document inside the test project folder, we can generate the requirements.py file that will contain Python objects for each requirement that we can use to link with our tests.

cat requirements.md | tfs requirements generate > requirements.py

Now we can write a test program that will define and run our tests. We will call our test program regression.py. It will contain the definition of a Feature named create user that will serve as a test suite that will run our test scenarios. For now, we will force it to fail with a not implemented message as we don’t have any test scenarios yet. Note that because we are focused on testing the CREATE USER feature, we can link our first generic requirement RQ_SRS001_User_Create to this feature to indicate that this feature’s scenarios will be used to verify the creation of user accounts using the CREATE USER statement.

cat << EOF > regression.py
from testflows.core import *
from requirements import *

@TestFeature
@Name("create user")
@Requirements(RQ_SRS001_User_Create("1.0"))
def feature(self):
    fail("not implemented")

if main():
    feature()
EOF

We can run our test program and see the following output.

$ python3 regression.py 
 Oct 14,2020 9:09:53   ⟥  Feature create user
                            Requirements
                              RQ.SRS001.User.Create
                                version 1.0
                 4ms   ⟥⟤ Fail create user, /create user, not implemented

Failing

✘ [ Fail ] /create user

1 suite (1 failed)

Total time 4ms

As expected, our create user feature has failed, but the test program has executed without errors and therefore we are ready to proceed to implement our test scenarios to verify the RQ.SRS001.User.Create.IfNotExists and the RQ.SRS001.User.Create.Replace specific requirements.

Verifying Requirement RQ.SRS001.User.Create.IfNotExists

Our first specific requirement is the RQ.SRS001.User.Create.IfNotExists, and it requires us to verify that the IF NOT EXISTS clause in the CREATE USER statement causes the statement not to raise an error if a user with the same name already exists. To simplify our test, we will use Python’s ClickHouse driver clickhouse_driver that you can install using the following command.

pip3 install clickhouse_driver

Because we will be executing RBAC commands using the default user, we must set the <access_management> parameter to 1 in the users.xml configuration file.

    <!-- Users and ACL. -->
    <users>
        <!-- If user name was not specified, 'default' user is used. -->
        <default>
            <!-- User can create other users and grant rights to them. -->
            <access_management>1</access_management>
        </default>
    </users>

Now the first test can be written and added to our regression.py test program as follows.

#!/usr/bin/env python3
from clickhouse_driver import Client
from clickhouse_driver.errors import ServerException

from testflows.core import *
from testflows.asserts import error
from requirements import *

@TestStep(When)
def execute_query(self, client, query):
    """Execute query and capture exception if any.
    """
    r = None
    query_exc = None

    with By("executing query", description=f"{query}", format_description=False):
        try:
            r = client.execute(query)
        except ServerException as exc:
            query_exc = exc

    return r, query_exc

@TestStep(Given)
def open_client(self, host="localhost"):
    """Open client connection.
    """
    try:
        client = Client(host)
        yield client
    finally:
        with By("closing client connection"):
            client.disconnect()

@TestScenario
@Requirements(RQ_SRS001_User_Create_IfNotExists("1.0"))
def verify_if_not_exists_clause(self, host="localhost"):
    """Verify that the `CREATE USER` statement does not raise an exception
    if the `IF NOT EXISTS` clause is specified and a user with the same name
    already exists and raises exception if `IF NOT EXISTS` clause is not specified.
    """
    client = None
    user = "test_user"
    error_code = 493
    error_message = f"DB::Exception: user `{user}`: cannot insert because user `{user}` already exists in local directory"

    try:
        with Given("I have a client connection", description=f"to {host}"):
            client = open_client(host=host)

        with When("I create a user"):
            r, exc = execute_query(client=client, query=f"CREATE USER {user}")

            with Then("there should be no error"):
                assert exc is None, error()

        with When("I check user definition"):
            r, exc = execute_query(client=client, query=f"SHOW CREATE USER {user}")

            expected = f"CREATE USER {user}"
            with Then("it should match the expected", description=f"{expected}"):
                assert expected == r[0][0], error()

        with When("I try to create a user with the same name without the `IF NOT EXISTS` clause"):
            r, exc = execute_query(client=client, query=f"CREATE USER {user}")

            with Then("I expect an error", description=f"{error_code} {error_message}"):
                assert exc is not None and error_code == exc.code and error_message in exc.message, error()

        with When("I try to create a user with the same name with the `IF NOT EXISTS` clause"):
            r, exc = execute_query(client=client, query=f"CREATE USER IF NOT EXISTS {user}")

            with Then("there should be no error"):
                assert exc is None, error()

    finally:
        if client:
            with Finally("I delete the user", flags=TE):
                execute_query(client=client, query=f"DROP USER IF EXISTS {user}")


@TestFeature
@Name("create user")
@Requirements(RQ_SRS001_User_Create("1.0"))
def feature(self):
    for scenario in loads(current_module(), Scenario):
        scenario()

if main():
    feature()

We will not detail each line of the test code. Instead, let’s look at the high-level features of the test that we have written.

Requirement Link

First, before writing the test, we have picked a requirement that we want to verify. Therefore, we link our test scenario with the requirement. We do it using the Requirements decorator right above the function that defines our test scenario.

@Requirements(RQ_SRS001_User_Create_IfNotExists("1.0"))

Note that we call the requirement object with the version string to check that the version of the requirement is correct. If the version that we want to test does not match the actual version of the requirement then an exception will be raised.

Description

One of the obvious things that we add to our test scenario is a Python documentation string. It seems like an obvious thing to do, but you would be surprised how many tests are out there that are missing a clear description to help us understand the purpose of the test. Therefore, we make sure each test has a clear description.

Structure

From the test code above, it is clear that our test has a well-defined structure. The concept that we use is simple and very powerful. We break the test into individual steps. In this case, we use step names similar to the ones used in Behavior-driven development as they help identify the purpose of each step. In this case, we use Given, When, By, Then, and Finally steps. Where the Given step defines a mandatory setup, the When step defines an action, the By step defines a sub-action, the Then step defines a positive assertion, and the Finally step defines a mandatory cleanup.

Embedded Test Procedure

The highlight of the test scenario that we have written is that the test procedure is embedded and clearly defined inside the test scenario. The procedure is defined by combining the appropriate step type with the corresponding name. For example,

        with When("I try to create a user with the same name without the `IF NOT EXISTS` clause"):
            r, exc = execute_query(client=client, query=f"CREATE USER {user}")

The procedure helps document our test code. No longer we have to write separate test specification documents that define a procedure for each test. Instead, using TestFlows, our test procedure and the test code is combined. Our procedure helps document the code, and the code implements the test procedure that we define.

The result is that our test scenario is readable. Give it a try! It also makes writing tests much more fun. We are not only writing the code, but we are also telling a story of what the test does and how it does it. More importantly, we don’t have to write test specifications by hand, and TestFlows can auto-generate test specifications for the whole test run.

Verifying Requirement RQ.SRS001.User.Create.Replace

Similar to how we have written our first test, we can write another test to verify the RQ.SRS001.User.Create.Replace requirement. Here it is.

@TestScenario
@Requirements(RQ_SRS001_User_Create_Replace("1.0"))
def verify_replace_clause(self, host="localhost"):
    """Verify that the `CREATE USER` statement with the `OR REPLACE` clause
    replaces the definition of the user if a user with the same name
    already exists.
    """
    client = None
    user = "test_user"
    error_code = 493
    error_message = f"DB::Exception: user `{user}`: cannot insert because user `{user}` already exists in local directory"

    try:
        with Given("I have a client connection", description=f"to {host}"):
            client = open_client(host=host)

        with When("I create a user"):
            r, exc = execute_query(client=client, query=f"CREATE USER {user}")

            with Then("there should be no error"):
                assert exc is None, error()

        with When("I check user definition"):
            r, exc = execute_query(client=client, query=f"SHOW CREATE USER {user}")

            expected = f"CREATE USER {user}"
            with Then("it should match the expected", description=f"{expected}"):
                assert expected == r[0][0], error()

        with When("I try to create a user with the same name without the `OR REPLACE` clause"):
            r, exc = execute_query(client=client, query=f"CREATE USER {user}")

            with Then("I expect an error", description=f"{error_code} {error_message}"):
                assert exc is not None and error_code == exc.code and error_message in exc.message, error()

        with When("I try to create a user with the same name with the `OR REPLACE` clause with plaintext password specified"):
            r, exc = execute_query(client=client, query=f"CREATE USER OR REPLACE {user} IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'hello there'")

            with Then("there should be no error"):
                assert exc is None, error()

        with When("I again check user definition"):
            r, exc = execute_query(client=client, query=f"SHOW CREATE USER {user}")

            expected = f"CREATE USER {user} IDENTIFIED WITH plaintext_password"
            with Then("it should contain the new definition", description=f"{expected}"):
                assert expected == r[0][0], error()

    finally:
        if client:
            with Finally("I delete the user", flags=TE):
                execute_query(client=client, query=f"DROP USER IF EXISTS {user}")

We again link our test scenario with the requirement. Then we provide a test description and break our test into steps to define our test procedure. As you can see, the test is again readable, and what the test does and how it does it is again clearly defined.

Running The Tests

Let’s again run the regression.py file. This time we will use the short output format provided by TestFlows so that the test procedure is more evident.

$ python3 regression.py -o short

For the first test, the output should be as follows.

  Scenario verify if not exists clause
    Verify that the `CREATE USER` statement does not raise an exception
    if the `IF NOT EXISTS` clause is specified and a user with the same name
    already exists and raises exception if `IF NOT EXISTS` clause is not specified.
    Requirements
      RQ.SRS001.User.Create.IfNotExists
        version 1.0
    Arguments
      host
        localhost
    Given I have a client connection
      to localhost
    OK
    When I create a user
      By executing query
        CREATE USER test_user
      OK
      Then there should be no error
      OK
    OK
    When I check user definition
      By executing query
        SHOW CREATE USER test_user
      OK
      Then it should match the expected
        CREATE USER test_user
      OK
    OK
    When I try to create a user with the same name without the `IF NOT EXISTS` clause
      By executing query
        CREATE USER test_user
      OK
      Then I expect an error
        493 DB::Exception: user `test_user`: cannot insert because user `test_user` already exists in local directory
      OK
    OK
    When I try to create a user with the same name with the `IF NOT EXISTS` clause
      By executing query
        CREATE USER IF NOT EXISTS test_user
      OK
      Then there should be no error
      OK
    OK
    Finally I delete the user
      By executing query
        DROP USER IF EXISTS test_user
      OK
    OK
    Finally I clean up
      By closing client connection
      OK
    OK
  OK

For the second test, it should be similar.

  Scenario verify replace clause
    Verify that the `CREATE USER` statement with the `OR REPLACE` clause
    replaces the definition of the user if a user with the same name
    already exists.
    Requirements
      RQ.SRS001.User.Create.Replace
        version 1.0
    Arguments
      host
        localhost
    Given I have a client connection
      to localhost
    OK
    When I create a user
      By executing query
        CREATE USER test_user
      OK
      Then there should be no error
      OK
    OK
    When I check user definition
      By executing query
        SHOW CREATE USER test_user
      OK
      Then it should match the expected
        CREATE USER test_user
      OK
    OK
    When I try to create a user with the same name without the `OR REPLACE` clause
      By executing query
        CREATE USER test_user
      OK
      Then I expect an error
        493 DB::Exception: user `test_user`: cannot insert because user `test_user` already exists in local directory
      OK
    OK
    When I try to create a user with the same name with the `OR REPLACE` clause with plaintext password specified
      By executing query
        CREATE USER OR REPLACE test_user IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'hello there'
      OK
      Then there should be no error
      OK
    OK
    When I again check user definition
      By executing query
        SHOW CREATE USER test_user
      OK
      Then it should contain the new definition
        CREATE USER test_user IDENTIFIED WITH plaintext_password
      OK
    OK
    Finally I delete the user
      By executing query
        DROP USER IF EXISTS test_user
      OK
    OK
    Finally I clean up
      By closing client connection
      OK
    OK
  OK

Note that if we remove the OK result messages, we get our pretty test procedure.

$ python3 regression.py -o short | grep -v OK

The output below is what we get now for the first test.

  Scenario verify if not exists clause
    Verify that the `CREATE USER` statement does not raise an exception
    if the `IF NOT EXISTS` clause is specified and a user with the same name
    already exists and raises exception if `IF NOT EXISTS` clause is not specified.
    Requirements
      RQ.SRS001.User.Create.IfNotExists
        version 1.0
    Arguments
      host
        localhost
    Given I have a client connection
      to localhost
    When I create a user
      By executing query
        CREATE USER test_user
      Then there should be no error
    When I check user definition
      By executing query
        SHOW CREATE USER test_user
      Then it should match the expected
        CREATE USER test_user
    When I try to create a user with the same name without the `IF NOT EXISTS` clause
      By executing query
        CREATE USER test_user
      Then I expect an error
        493 DB::Exception: user `test_user`: cannot insert because user `test_user` already exists in local directory
    When I try to create a user with the same name with the `IF NOT EXISTS` clause
      By executing query
        CREATE USER IF NOT EXISTS test_user
      Then there should be no error
    Finally I delete the user
      By executing query
        DROP USER IF EXISTS test_user
    Finally I clean up
      By closing client connection

As we can see that with some basic formatting and syntax highlighting, we can get a pretty test procedure that documents how the test verifies the requirement that it is linked with. You can find the full project that contains the code for both of the tests above at https://gitlab.com/altinity-public/blogs/enterprise-qa-process-in-clickhouse.

Conclusion

This article looked at how we author our tests for ClickHouse using TestFlows open source test framework. We saw how TestFlows helps us write readable and documented tests. Breaking up our tests into steps and explicit definition of test procedures helps us manage and maintain many complex test cases. We also saw how we link each test scenario with one or more requirements defined in our software requirements specification. All of the above provides the needed ingredients for writing and maintaining enterprise quality test cases to verify that ClickHouse simply works.

Stay tuned for the next article in which we will describe the last step in our enterprise QA process, which is reporting. We will look at our test results, test specifications, and requirements coverage reports. These reports help us track our QA progress for each feature that we test and link us back to the functionality that we verify works as expected for our customers and our ClickHouse users.

Subscribe to our newsletter, or follow us on Twitter/LinkedIn, so you do not miss the next update!

Leave a Reply

Your email address will not be published. Required fields are marked *