Containers for testing

[ndmlny-qs]

I have been working locally on the effort of migrating tests for pyodbc to use pytest and have created a container to test things locally. Below I outline how I got a working Microsoft SQL Server 2017 running on my Linux machine. I am using podman, but the steps will work with docker if you replace podman with docker, and have docker installed. I'm making the assumption that you have unixODBC installed, and have set up odbcinst.ini correctly. If not, see the ini section below. To get the container working do the steps outlined in the container section.

The tests ultimately failed, but some worked for Microsoft SQL Server 2017. I mostly wanted to bring the idea of creating documentation around building containers so devs can run tests locally when working on the codebase. This is definitely useful for me, and wanted to gauge the community to see if this is something we wanted to add to the documentation.

container

# Pull the server image provided by Microsoft
podman pull mcr.microsoft.com/mssql/server:2017-latest

# Create and start a container from the image we just pulled.
podman run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=StrongPassword2017" -p 1401:1433 --name mssql2017 --hostname mssql2017 -d mcr.microsoft.com/mssql/server:2017-latest

# Log into the container so we can create a test database.
podman exec -it mssql2017 "bash"

# Once in the container, run `sqlcmd` with the user and password supplied in the GitHub actions file.
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "StrongPassword2017"

To create the test database in sqlcmd, enter each line below and press enter.

CREATE DATABASE test
GO
QUIT

tests

This will drop you back into the container command prompt, where you will use the exit command to get out of it. From here I ran the tests/sqlservertests.py using the following command from the root directory.

python ./tests/sqlservertests.py -vvv --sqlserver "DRIVER={ODBC Driver 17 for SQL Server};SERVER=127.0.0.1,1401;UID=sa;PWD=StrongPassword2017;DATABASE=test"

At this point I was able to run the tests/sqlservertests.py in the py3 branch, which ended up being aborted, but that is for a different discussion. I also ran the test module using pytest with the following command.

export PYODBC_CONN_STR="DRIVER={ODBC Driver 17 for SQL Server};SERVER=127.0.0.1,1401;UID=sa;PWD=StrongPassword2017;DATABASE=test"
pytest tests/sqlservertests.py -vvv

This also failed (looking into it atm), but the good news is that I was successful at getting a reproducible (although multi-step) way of running some tests locally. I'll report back with a PostgreSQL container for tests discussed in the HACKING.md file.

---

ini

To find the odbcinst.ini file, you need to run the following to find where it is located.

odbcinst -j

The output will look something like the following.

unixODBC 2.3.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: $HOME/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Add the following section to the odbcinst.ini file making sure to put the correct path to the driver found on your host system not the container.

; odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description = Microsoft ODBC driver for SQL Server 2017
Driver = <you/need/to/supply/the/path/to/libmsodbcsql-17*.so>

[mkleehammer]

Wow, thanks. I've ported the SQL Server, PostgreSQL, and MySQL unit tests to pytest and have now merged them into master for the 5.0 release. These 3 are automatically tested when a commit is pushed to Github thanks to the CI @keitherskine setup.

Since I don't have any of the other databases, I haven't been able to port others. If you have access to any other DBs and can port those tests, it would be great to be able to include them.

[ndmlny-qs]

I'm looking over the other databases and it looks like the following are in the tests.

• infromix
• spark
• azure sql dw -> do we want to update to using azure sql? they have a docker instance that I may be able to port to podman
• sqlite
• excel

If this is the list you want updated, then I can make tickets and start working on them.

[mkleehammer]

That would be great. I really should have ported the sqlite as I already have it on Ubuntu (Pop OS). I was concentrating on those supported by Github actions and Appveyor.