Update README.unittests.rst

Change-Id: I3262688c4ceb6de48959e36c92b1c55b7c0a3ba6

1 files changed, 130 insertions, 38 deletions

diff --git a/README.unittests.rst b/README.unittests.rst
index e03d780..8747fe4 100644
--- a/README.unittests.rst
+++ b/README.unittests.rst
@@ -6,10 +6,7 @@ Alembic makes use of SQLAlchemy's test framework for its test suite,
 so working with Alembic's suite is similar to that of working with
 SQLAlchemy.
 
-This document is mostly copied directly from that of SQLAlchemy.  Note that
-Alembic's test suite currently has "backend" tests (e.g., tests that require a
-real database) only for PostgreSQL  and MySQL; other backends like Oracle and
-SQL Server are not required.
+This document is mostly copied directly from that of SQLAlchemy.
 
 
 Basic Test Running
@@ -20,14 +17,15 @@ a single Python interpreter::
 
     tox
 
+
 Advanced Tox Options
 ====================
 
 For more elaborate CI-style test running, the tox script provided will
 run against various Python / database targets.   For a basic run against
-Python 2.7 using an in-memory SQLite database::
+Python 3.8 using an in-memory SQLite database::
 
-    tox -e py27-sqlite
+    tox -e py38-sqlite
 
 The tox runner contains a series of target combinations that can run
 against various combinations of databases.  The test suite can be
@@ -36,8 +34,7 @@ database::
 
     tox -e py38-sqlite-postgresql
 
-Or to run just "backend" tests (NOTE: Alembic has no tests marked this
-way so this option is not important) against a MySQL databases::
+Or to run just "backend" tests against a MySQL database::
 
     tox -e py38-mysql-backendonly
 
@@ -45,44 +42,44 @@ Running against backends other than SQLite requires that a database of that
 vendor be available at a specific URL.  See "Setting Up Databases" below
 for details.
 
-The py.test Engine
-==================
+The pytest Engine
+=================
 
-The tox runner is using py.test to invoke the test suite.   Within the realm of
-py.test, SQLAlchemy itself is adding a large series of option and
-customizations to the py.test runner using plugin points, to allow for
+The tox runner is using pytest to invoke the test suite.   Within the realm of
+pytest, SQLAlchemy itself is adding a large series of option and
+customizations to the pytest runner using plugin points, to allow for
 SQLAlchemy's multiple database support, database setup/teardown and
 connectivity, multi process support, as well as lots of skip / database
 selection rules.
 
-Running tests with py.test directly grants more immediate control over
+Running tests with pytest directly grants more immediate control over
 database options and test selection.
 
-A generic py.test run looks like::
+A generic pytest run looks like::
 
-    py.test -n4
+    pytest -n4
 
 Above, the full test suite will run against SQLite, using four processes.
 If the "-n" flag is not used, the pytest-xdist is skipped and the tests will
 run linearly, which will take a pretty long time.
 
-The py.test command line is more handy for running subsets of tests and to
+The pytest command line is more handy for running subsets of tests and to
 quickly allow for custom database connections.  Example::
 
-    py.test --dburi=postgresql+psycopg2://scott:tiger@localhost/test  test/sql/test_query.py
+    pytest --dburi=postgresql+psycopg2://scott:tiger@localhost/test  test/sql/test_query.py
 
 Above will run the tests in the test/sql/test_query.py file (a pretty good
 file for basic "does this database work at all?" to start with) against a
 running PostgreSQL database at the given URL.
 
-The py.test frontend can also run tests against multiple kinds of databases at
+The pytest frontend can also run tests against multiple kinds of databases at
 once - a large subset of tests are marked as "backend" tests, which will be run
 against each available backend, and additionally lots of tests are targeted at
 specific backends only, which only run if a matching backend is made available.
 For example, to run the test suite against both PostgreSQL and MySQL at the
 same time::
 
-    py.test -n4 --db postgresql --db mysql
+    pytest -n4 --db postgresql --db mysql
 
 
 Setting Up Databases
@@ -91,22 +88,28 @@ Setting Up Databases
 The test suite identifies several built-in database tags that run against
 a pre-set URL.  These can be seen using --dbs::
 
-    $ py.test --dbs
+    $ pytest --dbs
     Available --db options (use --dburi to override)
                  default    sqlite:///:memory:
                 firebird    firebird://sysdba:masterkey@localhost//Users/classic/foo.fdb
-                   mssql    mssql+pyodbc://scott:tiger@ms_2008
+                 mariadb    mariadb://scott:tiger@192.168.0.199:3307/test
+                   mssql    mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=ODBC+Driver+17+for+SQL+Server
            mssql_pymssql    mssql+pymssql://scott:tiger@ms_2008
-                   mysql    mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
+                   mysql    mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
                   oracle    oracle://scott:tiger@127.0.0.1:1521
                  oracle8    oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
                   pg8000    postgresql+pg8000://scott:tiger@127.0.0.1:5432/test
               postgresql    postgresql://scott:tiger@127.0.0.1:5432/test
     postgresql_psycopg2cffi postgresql+psycopg2cffi://scott:tiger@127.0.0.1:5432/test
-                 pymysql    mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
+                 pymysql    mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8mb4
                   sqlite    sqlite:///:memory:
              sqlite_file    sqlite:///querytest.db
 
+Note that a pyodbc URL **must be against a hostname / database name
+combination, not a DSN name** when using the multiprocessing option; this is
+because the test suite needs to generate new URLs to refer to per-process
+databases that are created on the fly.
+
 What those mean is that if you have a database running that can be accessed
 by the above URL, you can run the test suite against it using ``--db <name>``.
 
@@ -119,10 +122,10 @@ creating a new file called ``test.cfg`` and adding your own ``[db]`` section::
 
 Above, we can now run the tests with ``my_postgresql``::
 
-    py.test --db my_postgresql
+    pytest --db my_postgresql
 
-We can also override the existing names in our ``test.cfg`` file, so that we
-can run with the tox runner also::
+We can also override the existing names in our ``test.cfg`` file, so that we can run
+with the tox runner also::
 
     # test.cfg file
     [db]
@@ -145,25 +148,29 @@ all existing tables in the target database.
 The above paragraph changes somewhat when the multiprocessing option
 is used, in that separate databases will be created instead, however
 in the case of Postgresql, the starting database is used as a template,
-so the starting database must still be empty.
+so the starting database must still be empty.  See below for example
+configurations using docker.
 
 The test runner will by default create and drop tables within the default
 database that's in the database URL, *unless* the multiprocessing option is in
-use via the py.test "-n" flag, which invokes pytest-xdist.   The
+use via the pytest "-n" flag, which invokes pytest-xdist.   The
 multiprocessing option is **enabled by default** when using the tox runner.
 When multiprocessing is used, the SQLAlchemy testing framework will create a
 new database for each process, and then tear it down after the test run is
 complete.    So it will be necessary for the database user to have access to
-CREATE DATABASE in order for this to work.
+CREATE DATABASE in order for this to work.   Additionally, as mentioned
+earlier, the database URL must be formatted such that it can be rewritten on
+the fly to refer to these other databases, which means for pyodbc it must refer
+to a hostname/database name combination, not a DSN name.
 
-Several tests require alternate usernames or schemas to be present, which are
-used to test dotted-name access scenarios.  On some databases such as Oracle or
-Sybase, these are usernames, and others such as PostgreSQL and MySQL they are
-schemas (created using CREATE SCHEMA).   The requirement applies to all
-backends except SQLite and Firebird.  The names are::
+Several tests require alternate usernames or schemas to be present, which
+are used to test dotted-name access scenarios.  On some databases such
+as Oracle these are usernames, and others such as PostgreSQL
+and MySQL they are schemas.   The requirement applies to all backends
+except SQLite and Firebird.  The names are::
 
     test_schema
-    test_schema_2 (only used on PostgreSQL)
+    test_schema_2 (only used on PostgreSQL and mssql)
 
 Please refer to your vendor documentation for the proper syntax to create
 these namespaces - the database user must have permission to create and drop
@@ -191,6 +198,12 @@ Additional steps specific to individual databases are as follows::
 
      ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english'
 
+    For two-phase transaction support, the max_prepared_transactions
+    configuration variable must be set to a non-zero value in postgresql.conf.
+    See
+    https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS
+    for further background.
+
     ORACLE: a user named "test_schema" is created in addition to the default
     user.
 
@@ -212,6 +225,85 @@ Additional steps specific to individual databases are as follows::
 
      ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
 
+Docker Configurations
+---------------------
+
+The SQLAlchemy test can run against database running in Docker containers.
+This ensures that they are empty and that their configuration is not influenced
+by any local usage.
+
+The following configurations are just examples that developers can use to
+quickly set up a local environment for SQLAlchemy development. They are **NOT**
+intended for production use!
+
+**PostgreSQL configuration**::
+
+    # only needed if a local image of postgres is not already present
+    docker pull postgres:12
+
+    # create the container with the proper configuration for sqlalchemy
+    docker run --rm -e POSTGRES_USER='scott' -e POSTGRES_PASSWORD='tiger' -e POSTGRES_DB='test' -p 127.0.0.1:5432:5432 -d --name postgres postgres:12-alpine
+
+    # configure the database
+    sleep 10
+    docker exec -ti postgres psql -U scott -c 'CREATE SCHEMA test_schema; CREATE SCHEMA test_schema_2;' test
+    # this last command is optional
+    docker exec -ti postgres sed -i 's/#max_prepared_transactions = 0/max_prepared_transactions = 10/g' /var/lib/postgresql/data/postgresql.conf
+
+    # To stop the container. It will also remove it.
+    docker stop postgres
+
+**MySQL configuration**::
+
+    # only needed if a local image of mysql is not already present
+    docker pull mysql:8
+
+    # create the container with the proper configuration for sqlalchemy
+    docker run --rm -e MYSQL_USER='scott' -e MYSQL_PASSWORD='tiger' -e MYSQL_DATABASE='test' -e MYSQL_ROOT_PASSWORD='password' -p 127.0.0.1:3306:3306 -d --name mysql mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
+
+    # configure the database
+    sleep 20
+    docker exec -ti mysql mysql -u root -ppassword -D test -w -e "GRANT ALL ON *.* TO scott@'%'; CREATE DATABASE test_schema CHARSET utf8mb4; CREATE DATABASE test_schema_2 CHARSET utf8mb4;"
+
+    # To stop the container. It will also remove it.
+    docker stop mysql
+
+**MariaDB configuration**::
+
+    # only needed if a local image of MariaDB is not already present
+    docker pull mariadb
+
+    # create the container with the proper configuration for sqlalchemy
+    docker run --rm -e MYSQL_USER='scott' -e MYSQL_PASSWORD='tiger' -e MYSQL_DATABASE='test' -e MYSQL_ROOT_PASSWORD='password' -p 127.0.0.1:3306:3306 -d --name mariadb mariadb --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
+
+    # configure the database
+    sleep 20
+    docker exec -ti mariadb mysql -u root -ppassword -D test -w -e "GRANT ALL ON *.* TO scott@'%'; CREATE DATABASE test_schema CHARSET utf8mb4; CREATE DATABASE test_schema_2 CHARSET utf8mb4;"
+
+    # To stop the container. It will also remove it.
+    docker stop mariadb
+
+**MSSQL configuration**::
+
+    # only needed if a local image of mssql is not already present
+    docker pull mcr.microsoft.com/mssql/server:2019-CU1-ubuntu-16.04
+
+    # create the container with the proper configuration for sqlalchemy
+    # it will use the Developer version
+    docker run --rm -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 127.0.0.1:1433:1433 -d --name mssql mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04
+
+    # configure the database
+    sleep 20
+    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -Q "sp_configure 'contained database authentication', 1; RECONFIGURE; CREATE DATABASE test CONTAINMENT = PARTIAL; ALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON; CREATE LOGIN scott WITH PASSWORD = 'tiger^5HHH'; ALTER SERVER ROLE sysadmin ADD MEMBER scott;"
+    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema"
+    docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema_2"
+
+    # To stop the container. It will also remove it.
+    docker stop mssql
+
+NOTE: with this configuration the url to use is not the default one configured
+in setup, but ``mssql+pymssql://scott:tiger^5HHH@127.0.0.1:1433/test``.  It can
+be used with pytest by using ``--db docker_mssql``.
 
 CONFIGURING LOGGING
 -------------------
@@ -219,10 +311,10 @@ SQLAlchemy logs its activity and debugging through Python's logging package.
 Any log target can be directed to the console with command line options, such
 as::
 
-    $ ./py.test test/orm/test_unitofwork.py -s \
+    $ ./pytest test/orm/test_unitofwork.py -s \
       --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
 
-Above we add the py.test "-s" flag so that standard out is not suppressed.
+Above we add the pytest "-s" flag so that standard out is not suppressed.
 
 
 DEVELOPING AND TESTING NEW DIALECTS  (SQLAlchemy Only)