Websocket based SQLA Dialect (Beta Version)

[raayu83]

I wonder if it would be possible to add a pyexasol backend as an alternative to odbc?

[jank]

@xyzy-web, supporting multiple drivers should not be an issue. SQLAlchemy supports identification of drivers via the URL: https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls.
In folder sqlalchemy_exasol you can find how support for pyodbc and turbodbc is added. Following that schema, adding pyexasol should be possible.
Would you be willing to conribute this?

[Nicoretti]

Status Update

In order to implement an SQLA dialect/driver based on pyexasol a dbapi interface is required (pep-249).
Due to the fact that pyexasol just provides this interface partially, we started implementing a dbapi wrapper as a first step.

What have been done so far

• Implement/Setup SQLA independent test setup for integration and unit tests
• Implement/Setup stubs for dbapi
• Implement/Setup pyexasol based wrapper for connection required by dbapi

TODO's (Pending)

• Implement/Setup pyexasol based wrapper for cursor required by dbapi
• Implement/Setup a SQLA driver based on the dbapi wrappers
• Implement/Setup the newly added test setup with the CI/CD infrastructure
• Cleanup, clarify open question's and align structure and naming of the components

Questions

• Should the fact that pyexasol is used be exposed to the end-user? "exa+pyexasol..." vs "exa+ws..." or "exa+websocket"
• Where should we put the dbapi wrapper and what should it's naming/structure be?

For further details on the current status, see also PR-278.

[Nicoretti]

Status Update

The DBAPI2 compliant web-socket driver has been implemented (see also PR-278).
As next step the actual SQLA dialect/driver implementation will be started.

What have been done so far

• Implement/Setup SQLA independent test setup for integration and unit tests
• Implement/Setup stubs for dbapi
• Implement/Setup pyexasol based wrapper for connection required by dbapi
• Implement/Setup pyexasol based wrapper for cursor required by dbapi
• Clarify open question's and align structure and naming of the components
  • We use exa+ws... which will be websockets with the provided "default" implementation
  • If we want to add others in the future it can be reflected by specific names

TODO's (Pending)

• Implement/Setup a SQLA driver based on the dbapi wrapper (exasol.driver.websocket)
• Implement/Setup the newly added test setup with the CI/CD infrastructure
• Cleanup

[Nicoretti]

Status Update

First draft of a websocket based dialect was added (see #320).
The current results of the SQLA compliance test suite are the following:

SQLA - Compliance Test Suite

1 ✔️ Passed , 287 ✖️ Failed , 305 🟡 Ignored

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

142 ✔️ Passed , 50 ✖️ Failed , 365 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

147 ✔️ Passed , 45 ✖️ Failed , 365 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

149 ✔️ Passed , 43 ✖️ Failed , 365 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

152 ✔️ Passed , 40 ✖️ Failed , 365 🟡 Ignored

for further details see #320

🗒️ Note
Looks like most of the remaining failing tests are due to incorrect/missing DBAPI type conversions.

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

162 ✔️ Passed , 30 ✖️ Failed , 365 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

172 ✔️ Passed , 20 ✖️ Failed , 365 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

176 ✔️ Passed , 15 ✖️ Failed , 366 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

SQLA - Compliance Test Suite

178 ✔️ Passed , 13 ✖️ Failed , 366 🟡 Ignored

for further details see #320

[Nicoretti]

Status Update

As of today, the websocket dialect, did not pass 100% of the expected SQLA compliance test suite. Still we expect the basic features to work. Therefore, the decision was made to release a beta version. This will give us a faster feedback cycle and allow users to test and play around with the new functionality. Once the websocket driver is stabilized it will be announced and
marked accordingly.

Test Status

SQLA - Compliance Test Suite

178 ✔️ Passed , 13 ✖️ Failed , 366 🟡 Ignored

Exasol - Test Suite

181 ✔️ Passed , 1 ✖️ Failed , 29 🟡 Ignored

for further details see #320

Remaining Todo's

• Create follow up issues
  • 🐞 Prepared statements send the wrong types as parameters to the server #341
  • 🐞 Various SQLA compliance tests are failing for the websocket based dialect #342
• Fix the remaining failing test of the Exasol test suite (TLS)
• Add notes regarding the Beta status of the Dialect to the README
• Add basic usage information to the README
• Add xfail exceptions for the remaining failing tests in the SQLA test suite

Once the related PR #320 is merged s new release, containing the beta should be created.

[Nicoretti]

Status Update

Test Status

Exasol - Test Suite ✔️

SQLA - Compliance Test Suite

178 ✔️ Passed , 13 ✖️ Failed , 366 🟡 Ignored

---

for further details see #320

Remaining Todo's

• Add xfail exceptions for the remaining failing tests in the SQLA test suite
  • Update information in follow up tickets
    • 🐞 Prepared statements send the wrong types as parameters to the server #341
    • 🐞 Various SQLA compliance tests are failing for the websocket based dialect #342

Once the related PR #320 is merged s new release, containing the beta should be created.