Implementing tests on Postgres

Owned by David Jimenez
Last updated: May 18, 2020
3 min read

About the problem.

In our test, currently working for SQL Server only, we have a framework where we have a set of scenarios, and for each scenario we have a set of tests. With every scenario execution the database is reset. Where reset means creating the database if it does not exist, loading a snapshot if it does, then installing AMT, etc. After all that is completed, the tests can be executed.
In order to have an implementation for Postgres we need something similar. This was the main challenge.

About the implementation.

The database reset process we implemented involves deleting the database and recreating it (then of course install AMT).
Using the OneTimeTearDown attribute we can delete the database once the scenario has been tested.
To do this, we added a class named PowerShellHelper where we handle functions like deleting and recreating the database. For now, we have the commands hard coded in the code. Ideally these strings would be in an external file, where we can configure them without having to recompile.

Something to take into account is that, in order to these commands to work it is necessary to do a couple of things:

  1. Handle the password. This link describes the approach taken. Basically, we store the password in a file and that way we don’t need to enter the it every time a command is executed.
        https://www.postgresql.org/docs/current/libpq-pgpass.html
    2. We need a maintenance-db. This link describes why it is necessary, but essentially it specifies, for instance for the create database command, the name of the database to connect to when creating the new database. Some documentation that explains the usage of this parameter:
    1. https://www.postgresql.org/docs/current/app-createdb.html
    2. https://www.postgresql.org/docs/current/app-dropdb.html

As part of the Spike ticket. All the tests for DateDim are working fine for Postgres. And in the operations section, the tests that validate the installation of the EWS collection are working fine as well. 

Making the test work for DateDim meant having the change the tests query a little bit. Currently the queries look something like this:

SELECT CalendarQuarter FROM analytics.DateDim WHERE DateKey=20190401;

Here, there is an error, because DateKey is a string. This is not a problem for SQL Server, but it is for Postgres. So these query now look something like this:

SELECT CalendarQuarter FROM analytics.DateDim WHERE DateKey='20190401';

Another thing to take into account is that the file that inserts the data doesn’t work for Postgres. It was necessary to create another file and change it a little bit to make it work for Postgres. What changed?

  1. Postgres does not accept the Top 1 statement.
  2. Postgres does not accept commands like SET IDENTITY_INSERT

For this file, with those 2 changes, the file worked fine. But there may be other cases where we will need to make additional changes. One example are the tests for ChronicAbsenteeismAttendanceFact View. In this file we use the DateAdd statement, and this won’t work in Postgres.

In order to make the tests for the EWS collection work in Postgres it was necessary to make the test name lowercase. For instance, from ews_LetterGradeTranslation to ews_lettergradetranslation.

Considerations and things to take into account.

  1. In our TestHarness class we have a connection string variable, it would be better to have these strings in a separate file instead of hardcoded in the class.
  2. We have tests for checking that the columns returned by the view are correct in number and type. It is necessary to create tests specific for Postgres given the fact that the types not necessarily match.
  3. As mentioned above, it will be necessary to duplicate the file with the inserts and change it as necessary to make it work for Postgres. 
  4. In a similar, the tests associated to when uninstalling the view include some scripts. We will need to find the equivalent for Postgres.
    Nice to have.

Nice to have.

  1. Something we can think about is refactoring our TestHarness class. At this point we have a few validations to check if the test being executed is Postgres or SQL Server. It would be nice to have separate class for those using a base abstract class or something similar.

The code can be found here: https://github.com/Ed-Fi-Alliance/Ed-Fi-Analytics-Middle-Tier/tree/spike-postgres-tests