README.unittests.rst 8.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220
  1. =====================
  2. SQLALCHEMY UNIT TESTS
  3. =====================
  4. Updated for 1.1, 1.2
  5. Basic Test Running
  6. ==================
  7. A test target exists within the setup.py script. For basic test runs::
  8. python setup.py test
  9. Running with Tox
  10. ================
  11. For more elaborate CI-style test running, the tox script provided will
  12. run against various Python / database targets. For a basic run against
  13. Python 2.7 using an in-memory SQLite database::
  14. tox -e py27-sqlite
  15. The tox runner contains a series of target combinations that can run
  16. against various combinations of databases. The test suite can be
  17. run against SQLite with "backend" tests also running against a PostgreSQL
  18. database::
  19. tox -e py36-sqlite-postgresql
  20. Or to run just "backend" tests against a MySQL database::
  21. tox -e py36-mysql-backendonly
  22. Running against backends other than SQLite requires that a database of that
  23. vendor be available at a specific URL. See "Setting Up Databases" below
  24. for details.
  25. The pytest Engine
  26. ==================
  27. The tox runner is using pytest to invoke the test suite. Within the realm of
  28. pytest, SQLAlchemy itself is adding a large series of option and
  29. customizations to the pytest runner using plugin points, to allow for
  30. SQLAlchemy's multiple database support, database setup/teardown and
  31. connectivity, multi process support, as well as lots of skip / database
  32. selection rules.
  33. Running tests with pytest directly grants more immediate control over
  34. database options and test selection.
  35. A generic pytest run looks like::
  36. pytest -n4
  37. Above, the full test suite will run against SQLite, using four processes.
  38. If the "-n" flag is not used, the pytest-xdist is skipped and the tests will
  39. run linearly, which will take a pretty long time.
  40. The pytest command line is more handy for running subsets of tests and to
  41. quickly allow for custom database connections. Example::
  42. pytest --dburi=postgresql+psycopg2://scott:tiger@localhost/test test/sql/test_query.py
  43. Above will run the tests in the test/sql/test_query.py file (a pretty good
  44. file for basic "does this database work at all?" to start with) against a
  45. running PostgreSQL database at the given URL.
  46. The pytest frontend can also run tests against multiple kinds of databases at
  47. once - a large subset of tests are marked as "backend" tests, which will be run
  48. against each available backend, and additionally lots of tests are targeted at
  49. specific backends only, which only run if a matching backend is made available.
  50. For example, to run the test suite against both PostgreSQL and MySQL at the
  51. same time::
  52. pytest -n4 --db postgresql --db mysql
  53. Setting Up Databases
  54. ====================
  55. The test suite identifies several built-in database tags that run against
  56. a pre-set URL. These can be seen using --dbs::
  57. $ pytest --dbs
  58. Available --db options (use --dburi to override)
  59. default sqlite:///:memory:
  60. firebird firebird://sysdba:masterkey@localhost//Users/classic/foo.fdb
  61. mssql mssql+pyodbc://scott:tiger@ms_2008
  62. mssql_pymssql mssql+pymssql://scott:tiger@ms_2008
  63. mysql mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
  64. oracle oracle://scott:tiger@127.0.0.1:1521
  65. oracle8 oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
  66. pg8000 postgresql+pg8000://scott:tiger@127.0.0.1:5432/test
  67. postgresql postgresql://scott:tiger@127.0.0.1:5432/test
  68. postgresql_psycopg2cffi postgresql+psycopg2cffi://scott:tiger@127.0.0.1:5432/test
  69. pymysql mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
  70. sqlite sqlite:///:memory:
  71. sqlite_file sqlite:///querytest.db
  72. What those mean is that if you have a database running that can be accessed
  73. by the above URL, you can run the test suite against it using ``--db <name>``.
  74. The URLs are present in the ``setup.cfg`` file. You can make your own URLs by
  75. creating a new file called ``test.cfg`` and adding your own ``[db]`` section::
  76. # test.cfg file
  77. [db]
  78. my_postgresql=postgresql://username:pass@hostname/dbname
  79. Above, we can now run the tests with ``my_postgresql``::
  80. pytest --db my_postgresql
  81. We can also override the existing names in our ``test.cfg`` file, so that we can run
  82. with the tox runner also::
  83. # test.cfg file
  84. [db]
  85. postgresql=postgresql://username:pass@hostname/dbname
  86. Now when we run ``tox -e py27-postgresql``, it will use our custom URL instead
  87. of the fixed one in setup.cfg.
  88. Database Configuration
  89. ======================
  90. The test runner will by default create and drop tables within the default
  91. database that's in the database URL, *unless* the multiprocessing option is in
  92. use via the pytest "-n" flag, which invokes pytest-xdist. The
  93. multiprocessing option is **enabled by default** when using the tox runner.
  94. When multiprocessing is used, the SQLAlchemy testing framework will create a
  95. new database for each process, and then tear it down after the test run is
  96. complete. So it will be necessary for the database user to have access to
  97. CREATE DATABASE in order for this to work. Additionally, as mentioned
  98. earlier, the database URL must be formatted such that it can be rewritten on
  99. the fly to refer to these other databases, which means for pyodbc it must refer
  100. to a hostname/database name combination, not a DSN name.
  101. Several tests require alternate usernames or schemas to be present, which
  102. are used to test dotted-name access scenarios. On some databases such
  103. as Oracle or Sybase, these are usernames, and others such as PostgreSQL
  104. and MySQL they are schemas. The requirement applies to all backends
  105. except SQLite and Firebird. The names are::
  106. test_schema
  107. test_schema_2 (only used on PostgreSQL and mssql)
  108. Please refer to your vendor documentation for the proper syntax to create
  109. these namespaces - the database user must have permission to create and drop
  110. tables within these schemas. Its perfectly fine to run the test suite
  111. without these namespaces present, it only means that a handful of tests which
  112. expect them to be present will fail.
  113. Additional steps specific to individual databases are as follows::
  114. POSTGRESQL: To enable unicode testing with JSONB, create the
  115. database with UTF8 encoding::
  116. postgres=# create database test with owner=scott encoding='utf8' template=template0;
  117. To include tests for HSTORE, create the HSTORE type engine::
  118. postgres=# \c test;
  119. You are now connected to database "test" as user "postgresql".
  120. test=# create extension hstore;
  121. CREATE EXTENSION
  122. Full-text search configuration should be set to English, else
  123. several tests of ``.match()`` will fail. This can be set (if it isn't so
  124. already) with:
  125. ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english'
  126. For two-phase transaction support, the max_prepared_transactions
  127. configuration variable must be set to a non-zero value in postgresql.conf.
  128. See
  129. https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS
  130. for further background.
  131. ORACLE: a user named "test_schema" is created in addition to the default
  132. user.
  133. The primary database user needs to be able to create and drop tables,
  134. synonyms, and constraints within the "test_schema" user. For this
  135. to work fully, including that the user has the "REFERENCES" role
  136. in a remote schema for tables not yet defined (REFERENCES is per-table),
  137. it is required that the test the user be present in the "DBA" role:
  138. grant dba to scott;
  139. MSSQL: Tests that involve multiple connections require Snapshot Isolation
  140. ability implemented on the test database in order to prevent deadlocks that
  141. will occur with record locking isolation. This feature is only available
  142. with MSSQL 2005 and greater. You must enable snapshot isolation at the
  143. database level and set the default cursor isolation with two SQL commands:
  144. ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
  145. ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
  146. CONFIGURING LOGGING
  147. -------------------
  148. SQLAlchemy logs its activity and debugging through Python's logging package.
  149. Any log target can be directed to the console with command line options, such
  150. as::
  151. $ ./pytest test/orm/test_unitofwork.py -s \
  152. --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
  153. Above we add the pytest "-s" flag so that standard out is not suppressed.
  154. DEVELOPING AND TESTING NEW DIALECTS
  155. -----------------------------------
  156. See the file README.dialects.rst for detail on dialects.