Repository: dbeatty10/dbt-mysql (language: python)

Issue:
dbt-adapter-tests for MySQL
### Describe the bug
[dbt-adapter-tests](https://github.com/fishtown-analytics/dbt-adapter-tests) assumes support for certain SQL syntax that is not supported by different versions of MySQL.

### Steps To Reproduce
Something similar to the following should expose this issue (for the `data_test` test):

```bash
$ pip install pytest-dbt-adapter
$ pytest test/mysql.dbtspec
```

### Expected behavior
This shouldn't fail for SQL syntax reasons.

### Example
An example that doesn't work for MySQL 8.0:
```sql
select 1 as id where id = 2
```

The following alternative works for MySQL 8.0 but not for 5.6 or 5.7:
```sql
select 1 id where 1 = 1
```

This alternative works for all versions of MySQL:
```sql
select 1 id from dual where 1 = 1
```

Unfortunately, a simple pull request to `dbt-adapter-tests` won't resolve this since not all databases have a `dual` table. A notable example is MS SQL Server.


Discussion / hints:
Here's a branch with the necessary changes:
- https://github.com/dbeatty10/dbt-adapter-tests/tree/mysql

Fix patch:
diff --git a/.gitignore b/.gitignore
index 28912e1..c84b16b 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,5 +1,7 @@
 *.egg-info
 env/
+.env
+.envrc
 *.pyc
 __pycache__
 .tox/
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 31a6be0..3f67d63 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,6 @@
 ## dbt-mysql 0.19.0 (TBD)
 - Manage MySQL connections via a self-contained DB API 2.0 compliant Python driver (instead of ODBC) ([#38](https://github.com/dbeatty10/dbt-mysql/pull/38))
+- Integration tests via (custom) dbt-adapter-tests ([#45](https://github.com/dbeatty10/dbt-mysql/pull/45))
 
 ## dbt-mysql 0.18.1 (December 6, 2020)
 
diff --git a/README.md b/README.md
index f39ff3e..424200e 100644
--- a/README.md
+++ b/README.md
@@ -157,20 +157,14 @@ Additionally, many DBMS have relation names with three parts whereas MySQL has o
 | Postgres           |  `database.schema.table`      | 3          |
 | MySQL              |  `database.table`             | 2          |
 
-
-dbt-mysql borrows from [dbt-spark](https://github.com/fishtown-analytics/dbt-spark) and [dbt-sqlite](https://github.com/codeforkjeff/dbt-sqlite) since Spark and SQLite also use two-part relation names.
-
 ### Running Tests
 
-1. Modify `test/mysql.dbtspec` with your `server`, `username`, `password`, and (optionally) `port`
-1. Install the `pytest-dbt-adapter` package
-1. Run the test specs in this repository
-
-```bash
-$ pip install pytest-dbt-adapter
-$ pytest test/mysql.dbtspec
-```
+See [test/README.md](test/README.md) for details on running the integration tests.
 
 ### Reporting bugs and contributing code
 
 -   Want to report a bug or request a feature? See the [contributing guidelines](https://github.com/dbeatty10/dbt-mysql/blob/main/CONTRIBUTING.rst#contributing), or open [an issue](https://github.com/dbeatty10/dbt-mysql/issues/new).
+
+### Credits
+
+dbt-mysql borrows from [dbt-spark](https://github.com/fishtown-analytics/dbt-spark) and [dbt-sqlite](https://github.com/codeforkjeff/dbt-sqlite) since Spark and SQLite also use two-part relation names.


Test patch:
diff --git a/test/README.md b/test/README.md
new file mode 100644
index 0000000..8a01bf5
--- /dev/null
+++ b/test/README.md
@@ -0,0 +1,83 @@
+# Testing dbt-mysql
+
+## Overview
+
+Here are the steps to run the integration tests:
+1. Install the pytest-dbt-adapter package
+1. Set environment variables
+1. Run Docker containers (optional)
+1. Run tests
+
+## Simple example
+
+Assuming the applicable `pytest-dbt-adapter` package is installed and environment variables are set:
+```bash
+pytest test/mysql.dbtspec
+```
+
+## Full example
+
+### Install the pytest-dbt-adapter package
+
+The official `pytest-dbt-adapter` package from Fishtown Analytics uses some SQL syntax not supported by MySQL.
+
+This fork (and branch) contains the necessary changes: https://github.com/dbeatty10/dbt-adapter-tests/tree/mysql
+
+#### Install via SSH
+
+`pip install git+git@github.com:dbeatty10/dbt-adapter-tests.git@mysql`
+
+#### Install via HTTPS
+`pip install git+https://github.com/dbeatty10/dbt-adapter-tests.git@mysql`
+
+### Environment variables
+
+Create the following environment variables (e.g., `export {VARIABLE}={value}` in a bash shell or via a tool like [`direnv`](https://direnv.net/)):
+    * `DBT_MYSQL_SERVER_NAME`
+    * `DBT_MYSQL_USERNAME`
+    * `DBT_MYSQL_PASSWORD`
+
+### Docker
+
+[Here](https://medium.com/@crmcmullen/how-to-run-mysql-in-a-docker-container-on-macos-with-persistent-local-data-58b89aec496a) is one guide on "How to Run MySQL in a Docker Container on macOS with Persistent Local Data".
+
+In the docker commands below, the default MySQL username is `root` and the default server name is `localhost`. If they are used unaltered, then you should set the following environment variable values:
+```
+DBT_MYSQL_SERVER_NAME=localhost
+DBT_MYSQL_USERNAME=root
+```
+
+If you use any bash special characters in your password (like `$`), then you will need to escape them (like `DBT_MYSQL_PASSWORD=pas\$word` instead of `DBT_MYSQL_PASSWORD=pas$word`).
+
+#### MySQL 8.0
+`docker run --name mysql8.0 --net dev-network -v /Users/YOUR_USERNAME/Develop/mysql_data/8.0:/var/lib/mysql -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=$DBT_MYSQL_PASSWORD mysql:8.0`
+
+#### MySQL 5.6
+
+Contents of `/Users/YOUR_USERNAME/Develop/mysql_data/5.6/my.cnf`:
+```
+[mysqld]
+explicit_defaults_for_timestamp = true
+```
+
+`docker run --name mysql5.6 --net dev-network -v /Users/YOUR_USERNAME/Develop/mysql_data/5.6:/var/lib/mysql -v /Users/YOUR_USERNAME/Develop/mysql_data/5.6/my.cnf:/etc/my.cnf -p 3308:3306 -d -e MYSQL_ROOT_PASSWORD=$DBT_MYSQL_PASSWORD mysql:5.6`
+
+#### MySQL 5.7
+
+Contents of `/Users/YOUR_USERNAME/Develop/mysql_data/5.7/my.cnf`:
+```
+[mysqld]
+explicit_defaults_for_timestamp = true
+sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
+```
+
+`docker run --name mysql5.7 --net dev-network -v /Users/YOUR_USERNAME/Develop/mysql_data/5.7:/var/lib/mysql -v /Users/YOUR_USERNAME/Develop/mysql_data/5.7/my.cnf:/etc/my.cnf -p 3307:3306 -d -e MYSQL_ROOT_PASSWORD=$DBT_MYSQL_PASSWORD mysql:5.7`
+
+### Run tests
+
+Run the test specs in this repository:
+```
+pytest -v test/integration/mysql-5.6-odbc.dbtspec && \
+pytest -v test/integration/mysql-5.7-odbc.dbtspec && \
+pytest -v test/integration/mysql-8.0-odbc.dbtspec
+```
diff --git a/test/integration/mysql-5.6-odbc.dbtspec b/test/integration/mysql-5.6-odbc.dbtspec
new file mode 100644
index 0000000..0ee920c
--- /dev/null
+++ b/test/integration/mysql-5.6-odbc.dbtspec
@@ -0,0 +1,25 @@
+
+target:
+  type: mysql
+  port: 3308
+  schema: dbt_test_{{ var('_dbt_random_suffix') }}
+  server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
+
+sequences:
+
+  # List of sequences:
+  # https://github.com/fishtown-analytics/dbt-adapter-tests/tree/master/pytest_dbt_adapter/sequences
+
+  test_dbt_empty: empty
+  test_dbt_base: base
+  # Ephemeral materializations not supported for MySQL 5.6
+  # test_dbt_ephemeral: ephemeral
+  test_dbt_incremental: incremental
+  test_dbt_snapshot_strategy_timestamp: snapshot_strategy_timestamp
+  test_dbt_snapshot_strategy_check_cols: snapshot_strategy_check_cols
+  test_dbt_data_test: data_test
+  test_dbt_schema_test: schema_test
+  # Ephemeral materializations not supported for MySQL 5.6
+  # test_dbt_ephemeral_data_tests: data_test_ephemeral_models
diff --git a/test/integration/mysql-5.7-odbc.dbtspec b/test/integration/mysql-5.7-odbc.dbtspec
new file mode 100644
index 0000000..ce820e0
--- /dev/null
+++ b/test/integration/mysql-5.7-odbc.dbtspec
@@ -0,0 +1,25 @@
+
+target:
+  type: mysql
+  port: 3307
+  schema: dbt_test_{{ var('_dbt_random_suffix') }}
+  server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
+
+sequences:
+
+  # List of sequences:
+  # https://github.com/fishtown-analytics/dbt-adapter-tests/tree/master/pytest_dbt_adapter/sequences
+
+  test_dbt_empty: empty
+  test_dbt_base: base
+  # Ephemeral materializations not supported for MySQL 5.7
+  # test_dbt_ephemeral: ephemeral
+  test_dbt_incremental: incremental
+  test_dbt_snapshot_strategy_timestamp: snapshot_strategy_timestamp
+  test_dbt_snapshot_strategy_check_cols: snapshot_strategy_check_cols
+  test_dbt_data_test: data_test
+  test_dbt_schema_test: schema_test
+  # Ephemeral materializations not supported for MySQL 5.7
+  # test_dbt_ephemeral_data_tests: data_test_ephemeral_models
diff --git a/test/integration/mysql-8.0-odbc.dbtspec b/test/integration/mysql-8.0-odbc.dbtspec
new file mode 100644
index 0000000..efe9034
--- /dev/null
+++ b/test/integration/mysql-8.0-odbc.dbtspec
@@ -0,0 +1,23 @@
+
+target:
+  type: mysql
+  port: 3306
+  schema: dbt_test_{{ var('_dbt_random_suffix') }}
+  server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
+
+sequences:
+
+  # List of sequences:
+  # https://github.com/fishtown-analytics/dbt-adapter-tests/tree/master/pytest_dbt_adapter/sequences
+
+  test_dbt_empty: empty
+  test_dbt_base: base
+  test_dbt_ephemeral: ephemeral
+  test_dbt_incremental: incremental
+  test_dbt_snapshot_strategy_timestamp: snapshot_strategy_timestamp
+  test_dbt_snapshot_strategy_check_cols: snapshot_strategy_check_cols
+  test_dbt_data_test: data_test
+  test_dbt_schema_test: schema_test
+  test_dbt_ephemeral_data_tests: data_test_ephemeral_models
diff --git a/test/mysql.dbtspec b/test/mysql.dbtspec
index 9213829..d25af88 100644
--- a/test/mysql.dbtspec
+++ b/test/mysql.dbtspec
@@ -1,11 +1,11 @@
 
 target:
   type: mysql
-  server: localhost
+  server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
-  username: your_username
-  password: your_password
-  driver: MySQL ODBC 8.0 ANSI Driver
+
 sequences:
 
   # List of sequences: