README.dialects.rst
1========================
2Developing new Dialects
3========================
4
5.. note::
6
7 When studying this file, it's probably a good idea to also
8 familiarize with the README.unittests.rst file, which discusses
9 SQLAlchemy's usage and extension of the Nose test runner.
10
11While SQLAlchemy includes many dialects within the core distribution, the
12trend for new dialects should be that they are published as external
13projects. SQLAlchemy has since version 0.5 featured a "plugin" system
14which allows external dialects to be integrated into SQLAlchemy using
15standard setuptools entry points. As of version 0.8, this system has
16been enhanced, so that a dialect can also be "plugged in" at runtime.
17
18On the testing side, SQLAlchemy as of 0.8 also includes a "dialect
19compliance suite" that is usable by third party libraries. There is no
20longer a strong need for a new dialect to run through SQLAlchemy's full
21testing suite, as a large portion of these tests do not have
22dialect-sensitive functionality. The "dialect compliance suite" should
23be viewed as the primary target for new dialects, and as it continues
24to grow and mature it should become a more thorough and efficient system
25of testing new dialects.
26
27As of SQLAlchemy 0.9.4, both nose and pytest are supported for running tests,
28and pytest is now preferred.
29
30Dialect Layout
31===============
32
33The file structure of a dialect is typically similar to the following::
34
35 sqlalchemy-<dialect>/
36 setup.py
37 setup.cfg
38 sqlalchemy_<dialect>/
39 __init__.py
40 base.py
41 <dbapi>.py
42 requirements.py
43 test/
44 conftest.py
45 __init__.py
46 test_suite.py
47 test_<dialect_specific_test>.py
48 ...
49
50An example of this structure can be seen in the Access dialect at
51https://bitbucket.org/zzzeek/sqlalchemy-access/.
52
53Key aspects of this file layout include:
54
55* setup.py - should specify setuptools entrypoints, allowing the
56 dialect to be usable from create_engine(), e.g.::
57
58 entry_points={
59 'sqlalchemy.dialects': [
60 'access = sqlalchemy_access.pyodbc:AccessDialect_pyodbc',
61 'access.pyodbc = sqlalchemy_access.pyodbc:AccessDialect_pyodbc',
62 ]
63 }
64
65 Above, the two entrypoints ``access`` and ``access.pyodbc`` allow URLs to be
66 used such as::
67
68 create_engine("access://user:pw@dsn")
69
70 create_engine("access+pyodbc://user:pw@dsn")
71
72* setup.cfg - this file contains the traditional contents such as [egg_info],
73 [pytest] and [nosetests] directives, but also contains new directives that are used
74 by SQLAlchemy's testing framework. E.g. for Access::
75
76 [egg_info]
77 tag_build = dev
78
79 [tool:pytest]
80 addopts= --tb native -v -r fxX --maxfail=25 -p no:warnings
81 python_files=test/*test_*.py
82
83 [sqla_testing]
84 requirement_cls=sqlalchemy_access.requirements:Requirements
85 profile_file=test/profiles.txt
86
87 [db]
88 default=access+pyodbc://admin@access_test
89 sqlite=sqlite:///:memory:
90
91 Above, the ``[sqla_testing]`` section contains configuration used by
92 SQLAlchemy's test plugin. The ``[tool:pytest]`` section
93 include directives to help with these runners; in the case of
94 Nose, the directive ``with-sql_testing = true``, which indicates to Nose that
95 the SQLAlchemy nose plugin should be used. In the case of pytest, the
96 test/conftest.py file will bootstrap SQLAlchemy's plugin.
97
98* test/conftest.py - This script bootstraps SQLAlchemy's pytest plugin
99 into the pytest runner. This
100 script can also be used to install your third party dialect into
101 SQLAlchemy without using the setuptools entrypoint system; this allows
102 your dialect to be present without any explicit setup.py step needed.
103 The other portion invokes SQLAlchemy's pytest plugin::
104
105 from sqlalchemy.dialects import registry
106
107 registry.register("access", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc")
108 registry.register("access.pyodbc", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc")
109
110 from sqlalchemy.testing.plugin.pytestplugin import *
111
112 Where above, the ``registry`` module, introduced in SQLAlchemy 0.8, provides
113 an in-Python means of installing the dialect entrypoints without the use
114 of setuptools, using the ``registry.register()`` function in a way that
115 is similar to the ``entry_points`` directive we placed in our ``setup.py``.
116
117* requirements.py - The ``requirements.py`` file is where directives
118 regarding database and dialect capabilities are set up.
119 SQLAlchemy's tests are often annotated with decorators that mark
120 tests as "skip" or "fail" for particular backends. Over time, this
121 system has been refined such that specific database and DBAPI names
122 are mentioned less and less, in favor of @requires directives which
123 state a particular capability. The requirement directive is linked
124 to target dialects using a ``Requirements`` subclass. The custom
125 ``Requirements`` subclass is specified in the ``requirements.py`` file
126 and is made available to SQLAlchemy's test runner using the
127 ``requirement_cls`` directive inside the ``[sqla_testing]`` section.
128
129 For a third-party dialect, the custom ``Requirements`` class can
130 usually specify a simple yes/no answer for a particular system. For
131 example, a requirements file that specifies a database that supports
132 the RETURNING construct but does not support reflection of tables
133 might look like this::
134
135 # sqlalchemy_access/requirements.py
136
137 from sqlalchemy.testing.requirements import SuiteRequirements
138
139 from sqlalchemy.testing import exclusions
140
141 class Requirements(SuiteRequirements):
142 @property
143 def table_reflection(self):
144 return exclusions.closed()
145
146 @property
147 def returning(self):
148 return exclusions.open()
149
150 The ``SuiteRequirements`` class in
151 ``sqlalchemy.testing.requirements`` contains a large number of
152 requirements rules, which attempt to have reasonable defaults. The
153 tests will report on those requirements found as they are run.
154
155 The requirements system can also be used when running SQLAlchemy's
156 primary test suite against the external dialect. In this use case,
157 a ``--dburi`` as well as a ``--requirements`` flag are passed to SQLAlchemy's
158 main test runner ``./sqla_nose.py`` so that exclusions specific to the
159 dialect take place::
160
161 cd /path/to/sqlalchemy
162 py.test -v \
163 --requirements sqlalchemy_access.requirements:Requirements \
164 --dburi access+pyodbc://admin@access_test
165
166* test_suite.py - Finally, the ``test_suite.py`` module represents a
167 stub test suite, which pulls in the actual SQLAlchemy test suite.
168 To pull in the suite as a whole, it can be imported in one step::
169
170 # test/test_suite.py
171
172 from sqlalchemy.testing.suite import *
173
174 That's all that's needed - the ``sqlalchemy.testing.suite`` package
175 contains an ever expanding series of tests, most of which should be
176 annotated with specific requirement decorators so that they can be
177 fully controlled. To specifically modify some of the tests, they can
178 be imported by name and subclassed::
179
180 from sqlalchemy.testing.suite import *
181
182 from sqlalchemy.testing.suite import ComponentReflectionTest as _ComponentReflectionTest
183
184 class ComponentReflectionTest(_ComponentReflectionTest):
185 @classmethod
186 def define_views(cls, metadata, schema):
187 # bypass the "define_views" section of the
188 # fixture
189 return
190
191Going Forward
192==============
193
194The third-party dialect can be distributed like any other Python
195module on Pypi. Links to prominent dialects can be featured within
196SQLAlchemy's own documentation; contact the developers (see AUTHORS)
197for help with this.
198
199While SQLAlchemy includes many dialects built in, it remains to be
200seen if the project as a whole might move towards "plugin" model for
201all dialects, including all those currently built in. Now that
202SQLAlchemy's dialect API is mature and the test suite is not far
203behind, it may be that a better maintenance experience can be
204delivered by having all dialects separately maintained and released.
205
206As new versions of SQLAlchemy are released, the test suite and
207requirements file will receive new tests and changes. The dialect
208maintainer would normally keep track of these changes and make
209adjustments as needed.
210
211
README.rst
1SQLAlchemy
2==========
3
4The Python SQL Toolkit and Object Relational Mapper
5
6Introduction
7-------------
8
9SQLAlchemy is the Python SQL toolkit and Object Relational Mapper
10that gives application developers the full power and
11flexibility of SQL. SQLAlchemy provides a full suite
12of well known enterprise-level persistence patterns,
13designed for efficient and high-performing database
14access, adapted into a simple and Pythonic domain
15language.
16
17Major SQLAlchemy features include:
18
19* An industrial strength ORM, built
20 from the core on the identity map, unit of work,
21 and data mapper patterns. These patterns
22 allow transparent persistence of objects
23 using a declarative configuration system.
24 Domain models
25 can be constructed and manipulated naturally,
26 and changes are synchronized with the
27 current transaction automatically.
28* A relationally-oriented query system, exposing
29 the full range of SQL's capabilities
30 explicitly, including joins, subqueries,
31 correlation, and most everything else,
32 in terms of the object model.
33 Writing queries with the ORM uses the same
34 techniques of relational composition you use
35 when writing SQL. While you can drop into
36 literal SQL at any time, it's virtually never
37 needed.
38* A comprehensive and flexible system
39 of eager loading for related collections and objects.
40 Collections are cached within a session,
41 and can be loaded on individual access, all
42 at once using joins, or by query per collection
43 across the full result set.
44* A Core SQL construction system and DBAPI
45 interaction layer. The SQLAlchemy Core is
46 separate from the ORM and is a full database
47 abstraction layer in its own right, and includes
48 an extensible Python-based SQL expression
49 language, schema metadata, connection pooling,
50 type coercion, and custom types.
51* All primary and foreign key constraints are
52 assumed to be composite and natural. Surrogate
53 integer primary keys are of course still the
54 norm, but SQLAlchemy never assumes or hardcodes
55 to this model.
56* Database introspection and generation. Database
57 schemas can be "reflected" in one step into
58 Python structures representing database metadata;
59 those same structures can then generate
60 CREATE statements right back out - all within
61 the Core, independent of the ORM.
62
63SQLAlchemy's philosophy:
64
65* SQL databases behave less and less like object
66 collections the more size and performance start to
67 matter; object collections behave less and less like
68 tables and rows the more abstraction starts to matter.
69 SQLAlchemy aims to accommodate both of these
70 principles.
71* An ORM doesn't need to hide the "R". A relational
72 database provides rich, set-based functionality
73 that should be fully exposed. SQLAlchemy's
74 ORM provides an open-ended set of patterns
75 that allow a developer to construct a custom
76 mediation layer between a domain model and
77 a relational schema, turning the so-called
78 "object relational impedance" issue into
79 a distant memory.
80* The developer, in all cases, makes all decisions
81 regarding the design, structure, and naming conventions
82 of both the object model as well as the relational
83 schema. SQLAlchemy only provides the means
84 to automate the execution of these decisions.
85* With SQLAlchemy, there's no such thing as
86 "the ORM generated a bad query" - you
87 retain full control over the structure of
88 queries, including how joins are organized,
89 how subqueries and correlation is used, what
90 columns are requested. Everything SQLAlchemy
91 does is ultimately the result of a developer-
92 initiated decision.
93* Don't use an ORM if the problem doesn't need one.
94 SQLAlchemy consists of a Core and separate ORM
95 component. The Core offers a full SQL expression
96 language that allows Pythonic construction
97 of SQL constructs that render directly to SQL
98 strings for a target database, returning
99 result sets that are essentially enhanced DBAPI
100 cursors.
101* Transactions should be the norm. With SQLAlchemy's
102 ORM, nothing goes to permanent storage until
103 commit() is called. SQLAlchemy encourages applications
104 to create a consistent means of delineating
105 the start and end of a series of operations.
106* Never render a literal value in a SQL statement.
107 Bound parameters are used to the greatest degree
108 possible, allowing query optimizers to cache
109 query plans effectively and making SQL injection
110 attacks a non-issue.
111
112Documentation
113-------------
114
115Latest documentation is at:
116
117http://www.sqlalchemy.org/docs/
118
119Installation / Requirements
120---------------------------
121
122Full documentation for installation is at
123`Installation <http://www.sqlalchemy.org/docs/intro.html#installation>`_.
124
125Getting Help / Development / Bug reporting
126------------------------------------------
127
128Please refer to the `SQLAlchemy Community Guide <http://www.sqlalchemy.org/support.html>`_.
129
130License
131-------
132
133SQLAlchemy is distributed under the `MIT license
134<http://www.opensource.org/licenses/mit-license.php>`_.
135
136
README.unittests.rst
1=====================
2SQLALCHEMY UNIT TESTS
3=====================
4
5Updated for 1.1, 1.2
6
7Basic Test Running
8==================
9
10A test target exists within the setup.py script. For basic test runs::
11
12 python setup.py test
13
14
15Running with Tox
16================
17
18For more elaborate CI-style test running, the tox script provided will
19run against various Python / database targets. For a basic run against
20Python 2.7 using an in-memory SQLite database::
21
22 tox -e py27-sqlite
23
24The tox runner contains a series of target combinations that can run
25against various combinations of databases. The test suite can be
26run against SQLite with "backend" tests also running against a Postgresql
27database::
28
29 tox -e py36-sqlite-postgresql
30
31Or to run just "backend" tests against a MySQL database::
32
33 tox -e py36-mysql-backendonly
34
35Running against backends other than SQLite requires that a database of that
36vendor be available at a specific URL. See "Setting Up Databases" below
37for details.
38
39The py.test Engine
40==================
41
42Both the tox runner and the setup.py runner are using py.test to invoke
43the test suite. Within the realm of py.test, SQLAlchemy itself is adding
44a large series of option and customizations to the py.test runner using
45plugin points, to allow for SQLAlchemy's multiple database support,
46database setup/teardown and connectivity, multi process support, as well as
47lots of skip / database selection rules.
48
49Running tests with py.test directly grants more immediate control over
50database options and test selection.
51
52A generic py.test run looks like::
53
54 py.test -n4
55
56Above, the full test suite will run against SQLite, using four processes.
57If the "-n" flag is not used, the pytest-xdist is skipped and the tests will
58run linearly, which will take a pretty long time.
59
60The py.test command line is more handy for running subsets of tests and to
61quickly allow for custom database connections. Example::
62
63 py.test --dburi=postgresql+psycopg2://scott:tiger@localhost/test test/sql/test_query.py
64
65Above will run the tests in the test/sql/test_query.py file (a pretty good
66file for basic "does this database work at all?" to start with) against a
67running Postgresql database at the given URL.
68
69The py.test frontend can also run tests against multiple kinds of databases
70at once - a large subset of tests are marked as "backend" tests, which will
71be run against each available backend, and additionally lots of tests are targeted
72at specific backends only, which only run if a matching backend is made available.
73For example, to run the test suite against both Postgresql and MySQL at the same time::
74
75 py.test -n4 --db postgresql --db mysql
76
77
78Setting Up Databases
79====================
80
81The test suite identifies several built-in database tags that run against
82a pre-set URL. These can be seen using --dbs::
83
84 $ py.test --dbs=.
85 Available --db options (use --dburi to override)
86 default sqlite:///:memory:
87 firebird firebird://sysdba:masterkey@localhost//Users/classic/foo.fdb
88 mssql mssql+pyodbc://scott:tiger@ms_2008
89 mssql_pymssql mssql+pymssql://scott:tiger@ms_2008
90 mysql mysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
91 oracle oracle://scott:tiger@127.0.0.1:1521
92 oracle8 oracle://scott:tiger@127.0.0.1:1521/?use_ansi=0
93 pg8000 postgresql+pg8000://scott:tiger@127.0.0.1:5432/test
94 postgresql postgresql://scott:tiger@127.0.0.1:5432/test
95 postgresql_psycopg2cffi postgresql+psycopg2cffi://scott:tiger@127.0.0.1:5432/test
96 pymysql mysql+pymysql://scott:tiger@127.0.0.1:3306/test?charset=utf8
97 sqlite sqlite:///:memory:
98 sqlite_file sqlite:///querytest.db
99
100What those mean is that if you have a database running that can be accessed
101by the above URL, you can run the test suite against it using ``--db <name>``.
102
103The URLs are present in the ``setup.cfg`` file. You can make your own URLs by
104creating a new file called ``test.cfg`` and adding your own ``[db]`` section::
105
106 # test.cfg file
107 [db]
108 my_postgresql=postgresql://username:pass@hostname/dbname
109
110Above, we can now run the tests with ``my_postgresql``::
111
112 py.test --db my_postgresql
113
114We can also override the existing names in our ``test.cfg`` file, so that we can run
115with the tox runner also::
116
117 # test.cfg file
118 [db]
119 postgresql=postgresql://username:pass@hostname/dbname
120
121Now when we run ``tox -e py27-postgresql``, it will use our custom URL instead
122of the fixed one in setup.cfg.
123
124Database Configuration
125======================
126
127The test runner will by default create and drop tables within the default
128database that's in the database URL, *unless* the multiprocessing option
129is in use via the py.test "-n" flag, which invokes pytest-xdist. The
130multiprocessing option is **enabled by default** for both the tox runner
131and the setup.py frontend. When multiprocessing is used, the SQLAlchemy
132testing framework will create a new database for each process, and then
133tear it down after the test run is complete. So it will be necessary
134for the database user to have access to CREATE DATABASE in order for this
135to work.
136
137Several tests require alternate usernames or schemas to be present, which
138are used to test dotted-name access scenarios. On some databases such
139as Oracle or Sybase, these are usernames, and others such as PostgreSQL
140and MySQL they are schemas. The requirement applies to all backends
141except SQLite and Firebird. The names are::
142
143 test_schema
144 test_schema_2 (only used on PostgreSQL)
145
146Please refer to your vendor documentation for the proper syntax to create
147these namespaces - the database user must have permission to create and drop
148tables within these schemas. Its perfectly fine to run the test suite
149without these namespaces present, it only means that a handful of tests which
150expect them to be present will fail.
151
152Additional steps specific to individual databases are as follows::
153
154 POSTGRESQL: To enable unicode testing with JSONB, create the
155 database with UTF8 encoding::
156
157 postgres=# create database test with owner=scott encoding='utf8' template=template0;
158
159 To include tests for HSTORE, create the HSTORE type engine::
160
161 postgres=# \c test;
162 You are now connected to database "test" as user "postgresql".
163 test=# create extension hstore;
164 CREATE EXTENSION
165
166 Full-text search configuration should be set to English, else
167 several tests of ``.match()`` will fail. This can be set (if it isn't so
168 already) with:
169
170 ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english'
171
172 ORACLE: a user named "test_schema" is created in addition to the default
173 user.
174
175 The primary database user needs to be able to create and drop tables,
176 synonyms, and constraints within the "test_schema" user. For this
177 to work fully, including that the user has the "REFERENCES" role
178 in a remote schema for tables not yet defined (REFERENCES is per-table),
179 it is required that the test the user be present in the "DBA" role:
180
181 grant dba to scott;
182
183 MSSQL: Tests that involve multiple connections require Snapshot Isolation
184 ability implemented on the test database in order to prevent deadlocks that
185 will occur with record locking isolation. This feature is only available
186 with MSSQL 2005 and greater. You must enable snapshot isolation at the
187 database level and set the default cursor isolation with two SQL commands:
188
189 ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
190
191 ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
192
193
194CONFIGURING LOGGING
195-------------------
196SQLAlchemy logs its activity and debugging through Python's logging package.
197Any log target can be directed to the console with command line options, such
198as::
199
200 $ ./py.test test/orm/test_unitofwork.py -s \
201 --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
202
203Above we add the py.test "-s" flag so that standard out is not suppressed.
204
205
206DEVELOPING AND TESTING NEW DIALECTS
207-----------------------------------
208
209See the file README.dialects.rst for detail on dialects.
210
211
212