# sqlalchemy

 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10 Mike Bayer 1cf7485 2012-10-10 Mike Bayer c8a2ef3 2012-10-10   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 ======================== Developing new Dialects ======================== .. note:: When studying this file, it's probably a good idea to also familiarize with the README.unittests.rst file, which discusses SQLAlchemy's usage and extension of the Nose test runner. While SQLAlchemy includes many dialects within the core distribution, the trend for new dialects should be that they are published as external projects. SQLAlchemy has since version 0.5 featured a "plugin" system which allows external dialects to be integrated into SQLAlchemy using standard setuptools entry points. As of version 0.8, this system has been enhanced, so that a dialect can also be "plugged in" at runtime. On the testing side, SQLAlchemy as of 0.8 also includes a "dialect compliance suite" that is usable by third party libraries. There is no longer a strong need for a new dialect to run through SQLAlchemy's full testing suite, as a large portion of these tests do not have dialect-sensitive functionality. The "dialect compliance suite" should be viewed as the primary target for new dialects, and as it continues to grow and mature it should become a more thorough and efficient system of testing new dialects. Dialect Layout =============== The file structure of a dialect is typically similar to the following:: sqlalchemy-/ setup.py setup.cfg run_tests.py sqlalchemy_/ __init__.py base.py .py test/ __init__.py requirements.py test_suite.py test_.py ... An example of this structure can be seen in the Access dialect at https://bitbucket.org/zzzeek/sqlalchemy-access/. Key aspects of this file layout include: * setup.py - should specify setuptools entrypoints, allowing the dialect to be usable from create_engine(), e.g.:: entry_points={ 'sqlalchemy.dialects': [ 'access = sqlalchemy_access.pyodbc:AccessDialect_pyodbc', 'access.pyodbc = sqlalchemy_access.pyodbc:AccessDialect_pyodbc', ] } Above, the two entrypoints access and access.pyodbc allow URLs to be used such as:: create_engine("access://user:pw@dsn") create_engine("access+pyodbc://user:pw@dsn") * setup.cfg - this file contains the traditional contents such as [egg_info] and [nosetests] directives, but also contains new directives that are used by SQLAlchemy's testing framework. E.g. for Access:: [egg_info] tag_build = dev [nosetests] with-sqla_testing = true where = test cover-package = sqlalchemy-access with-coverage = 1 cover-erase = 1 [sqla_testing] requirement_cls=test.requirements:Requirements profile_file=.profiles.txt [db] default=access+pyodbc://admin@access_test sqlite=sqlite:///:memory: Above, the [sqla_testing] section contains configuration used by SQLAlchemy's test plugin.The [nosetests] section includes the directive with-sql_testing = true, which indicates to Nose that the SQLAlchemy nose plugin should be used. * run_tests.py - The plugin is provided with SQLAlchemy, however is not plugged into Nose automatically; instead, a run_tests.py script should be composed as a front end to Nose, such that SQLAlchemy's plugin will be correctly installed. run_tests.py has two parts. One optional, but probably helpful, step is that it installs your third party dialect into SQLAlchemy without using the setuptools entrypoint system; this allows your dialect to be present without any explicit setup.py step needed. The other step is to import SQLAlchemy's nose runner and invoke it. An example run_tests.py file looks like the following:: from sqlalchemy.dialects import registry registry.register("access", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc") registry.register("access.pyodbc", "sqlalchemy_access.pyodbc", "AccessDialect_pyodbc") from sqlalchemy.testing import runner runner.main() Where above, the registry module, introduced in SQLAlchemy 0.8, provides an in-Python means of installing the dialect entrypoints without the use of setuptools, using the registry.register() function in a way that is similar to the entry_points directive we placed in our setup.py. The call to runner.main() then runs the Nose front end, which installs SQLAlchemy's testing plugins. Invoking our custom runner looks like the following:: \$ python run_tests.py -v * requirements.py - The requirements.py file is where directives regarding database and dialect capabilities are set up. SQLAlchemy's tests are often annotated with decorators that mark tests as "skip" or "fail" for particular backends. Over time, this system has been refined such that specific database and DBAPI names are mentioned less and less, in favor of @requires directives which state a particular capability. The requirement directive is linked to target dialects using a Requirements subclass. The custom Requirements subclass is specified in the requirements.py file and is made available to SQLAlchemy's test runner using the requirement_cls directive inside the [sqla_testing] section. For a third-party dialect, the custom Requirements class can usually specify a simple yes/no answer for a particular system. For example, a requirements file that specifies a database that supports the RETURNING construct but does not support reflection of tables might look like this:: # test/requirements.py from sqlalchemy.testing.requirements import SuiteRequirements from sqlalchemy.testing import exclusions class Requirements(SuiteRequirements): @property def table_reflection(self): return exclusions.closed() @property def returning(self): return exclusions.open() The SuiteRequirements class in sqlalchemy.testing.requirements contains a large number of requirements rules, which attempt to have reasonable defaults. The tests will report on those requirements found as they are run. * test_suite.py - Finally, the test_suite.py module represents a Nose test suite, which pulls in the actual SQLAlchemy test suite. To pull in the suite as a whole, it can be imported in one step:: # test/test_suite.py from sqlalchemy.testing.suite import * That's all that's needed - the sqlalchemy.testing.suite package contains an ever expanding series of tests, most of which should be annotated with specific requirement decorators so that they can be fully controlled. To specifically modify some of the tests, they can be imported by name and subclassed:: from sqlalchemy.testing.suite import * from sqlalchemy.testing.suite import ComponentReflectionTest as _ComponentReflectionTest class ComponentReflectionTest(_ComponentReflectionTest): @classmethod def define_views(cls, metadata, schema): # bypass the "define_views" section of the # fixture return Going Forward ============== The third-party dialect can be distributed like any other Python module on Pypi. Links to prominent dialects can be featured within SQLAlchemy's own documentation; contact the developers (see AUTHORS) for help with this. While SQLAlchemy includes many dialects built in, it remains to be seen if the project as a whole might move towards "plugin" model for all dialects, including all those currently built in. Now that SQLAlchemy's dialect API is mature and the test suite is not far behind, it may be that a better maintenance experience can be delivered by having all dialects separately maintained and released. As new versions of SQLAlchemy are released, the test suite and requirements file will receive new tests and changes. The dialect maintainer would normally keep track of these changes and make adjustments as needed. Continuous Integration ====================== The most ideal scenario for ongoing dialect testing is continuous integration, that is, an automated test runner that runs in response to changes not just in the dialect itself but to new pushes to SQLAlchemy as well. The SQLAlchemy project features a Jenkins installation that runs tests on Amazon EC2 instances. It is possible for third-party dialect developers to provide the SQLAlchemy project either with AMIs or EC2 instance keys which feature test environments appropriate to the dialect - SQLAlchemy's own Jenkins suite can invoke tests on these environments. Contact the developers for further info.