🔧 Rework Integration Test Setup

[Nicoretti]

Summary

Rework the integration tests and setup to use exasol libraries (e.g. pyexasol or pytest-exasol) for preparing the DB
and setting up the tests written and maintained by exasol.

Details

Currently either SQLA, pyodbc, turbodbc or any combination of them is used to setup and prepare the test DB and it's
contents. This has at least two major flaws (shortcomings):

1. Chicken Egg Problem

   While the core functionality of the exasol SQLA dialect is not working properly, it's very hard or impossible
   to execute some or any tests with the appropriate DB and data setup.

2. It is not possible (without workarounds) to additionaly run integration tests which are not using the sqla test infrastructure.

   ⚠️ Problems ⚠️
   * Test collection works differently (Tests won't get picked up)
   * Test runs always need to provide db settings etc. to the sqla test plugin, otherwise the "pytest cli" will crash.

3. Eat Your Own Dog-food

   We test and rely on and implicitly test third party libraries, while we could implicitly test and get a feel (feedback) on our own libraries, APIs, ...

4. All the issues which come with third party libraries

   • dependencies
   • maintenance
   • ...

Background & Context

Having the tests within test/integration/sqlalchemy use SQLA primitives is fine because even our adjusted test cases there
should be looked at as part of SQLA and therefore meet their criteria.

Relying on SQLA tests fixtures and primitives within the test setup of our custom tests (test/integration/exasol) needs to be avoided though.

Examples

Replace/Remove SQLA test fixtures/classes e.g. sqlalchemy.testing.fixtures.TablesTest in test located in test/integration/exasol.

References

• pyexasol
• pytest_itde
• pytest-exasol maybe in the future

Task(s)

• Add/Use pytest_itde for integration tests
• Refactor tests
• Refactor nox targets
  • Add DBAPI target
  • Add regression target
• Add configuration parameters (pytest) for sqla-exasol project