= Django on Windows (bug actually caused by Sqlite <=3.3.4 (3.3.5?)) = (work in progress) [[TOC]] == Development == === Test suite failures === As of r8747 the test suite is showing the following failures when run on sqlite 3.2.7 and 3.3.4: * On a test from {{{tests/regressiontests/queries/models.py}}} when using the sqlite3 Django DB backend. ==== tests/regressiontests/queries/models.py ==== This has been reported as ticket #7570. This test fails: {{{ #!python Bug #7087 -- dates with extra select columns >>> Item.objects.dates('created', 'day').extra(select={'a': 1}) [datetime.datetime(2007, 12, 19, 0, 0), datetime.datetime(2007, 12, 20, 0, 0)] }}} Error is: {{{ ====================================================================== FAIL: Doctest: regressiontests.queries.models.__test__.API_TESTS ---------------------------------------------------------------------- Traceback (most recent call last): File "C:\ramiro\django-trunk\django\test\_doctest.py", line 2180, in runTest raise self.failureException(self.format_failure(new.getvalue())) AssertionError: Failed doctest test for regressiontests.queries.models.__test__.API_TESTS File "C:\ramiro\django-trunk\tests\regressiontests\queries\models.py", line unknown line number, in API_TESTS ---------------------------------------------------------------------- File "C:\ramiro\django-trunk\tests\regressiontests\queries\models.py", line ?, in regressiontests.queries.models.__test__.API_TESTS Failed example: Item.objects.dates('created', 'day').extra(select={'a': 1}) Exception raised: Traceback (most recent call last): File "C:\ramiro\django-trunk\django\test\_doctest.py", line 1267, in __run compileflags, 1) in test.globs File "", line 1, in Item.objects.dates('created', 'day').extra(select={'a': 1}) File "c:\ramiro\django-trunk\django\db\models\query.py", line 129, in __repr__ return repr(list(self)) File "c:\ramiro\django-trunk\django\db\models\query.py", line 141, in __len__ self._result_cache.extend(list(self._iter)) File "c:\ramiro\django-trunk\django\db\models\sql\subqueries.py", line 351, in results_iter for rows in self.execute_sql(MULTI): File "c:\ramiro\django-trunk\django\db\models\sql\query.py", line 1607, in execute_sql cursor.execute(sql, params) File "c:\ramiro\django-trunk\django\db\backends\sqlite3\base.py", line 136, in execute return Database.Cursor.execute(self, query, params) OperationalError: ORDER BY terms must not be non-integer constants ---------------------------------------------------------------------- Ran 253 tests in 433.032s FAILED (failures=1) }}} A minimal models.py file that shows the problem (extracted from the above regression test): {{{ #!python """ >>> time1 = datetime.datetime(2007, 12, 19, 22, 25, 0) >>> time2 = datetime.datetime(2007, 12, 19, 21, 0, 0) >>> time3 = datetime.datetime(2007, 12, 20, 22, 25, 0) >>> time4 = datetime.datetime(2007, 12, 20, 21, 0, 0) >>> i1 = Item(name='one', created=time1, modified=time1) >>> i1.save() >>> i2 = Item(name='two', created=time2) >>> i2.save() >>> i3 = Item(name='three', created=time3) >>> i3.save() >>> i4 = Item(name='four', created=time4) >>> i4.save() >>> Item.objects.dates('created', 'day').extra(select={'a': 1}) [datetime.datetime(2007, 12, 19, 0, 0), datetime.datetime(2007, 12, 20, 0, 0)] """ from django.db import models import datetime class Item(models.Model): name = models.CharField(max_length=10) created = models.DateTimeField() modified = models.DateTimeField(blank=True, null=True) class Meta: ordering = ['name'] def __unicode__(self): return self.name }}} The SQL being generated by Django is correct: {{{ #!python >>> Item.objects.dates('created', 'day').extra(select={'a': 1}).query.as_sql() ('SELECT DISTINCT (1) AS "a", django_date_trunc("day", "sqlite3_dates_item"."created") FROM "sqlite3_dates_item" ORDER BY 1 ASC', ()) }}} Problem seems to be related to some bug in the version of pysqlite2 or sqlite3 (main suspect) included with the official Python 2.5.2 win32 installer (2.3.2 and 3.3.4 respectively): {{{ #!python Python 2.5.2 (r252:60911, Feb 21 2008, 13:11:45) [MSC v.1310 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> from sqlite3 import dbapi2 >>> print dbapi2.version_info (2, 3, 2) >>> print dbapi2.sqlite_version_info (3, 3, 4) }}} The win32 installer for Python 2.5 ships the same combination of pysqlite/sqlite versions and exhibits the same problem. The tests were also run under some other Windows- and Linux-based scenarios. Summarizing: || '''Platform''' || '''Python version''' || '''pysqlite2 version''' || '''sqlite version''' || '''works?''' || '''Notes''' || || win32 || 2.4.4 || 2.3.0 (external) || 3.3.6 || YES || || || Debian Linux || 2.4.4 || 2.3.2 (external) || 3.3.8 || YES || || || win32 || 2.5 || 2.3.2 (built-in sqlite3)|| 3.3.4 || NO || || || Debian Linux || 2.5 || 2.3.2 (built-in sqlite3)|| 3.3.8 || YES || || || win32 || 2.5.2 || 2.3.2 (built-in sqlite3)|| 3.3.4 || NO || || || win32 || 2.5.2 || 2.4.1 (external) || 3.5.2 || YES || '''*''' || || Debian Linux || 2.5.2 || 2.3.2 (built-in sqlite3)|| 3.5.9 || YES || || || custom Linux || 2.5.2 || ? || 3.2.7 || NO || || Note that the tests also fail under Linux with early sqlite versions. ===== Possible solution ===== As the table above shows, version <=3.3.4 of sqlite seems to be affected by this bug whilst version >=3.3.6 isn't (see http://sqlite.org/changes.html for possible hints). As sqlite/pysqlite development continues, the pysqlite project keeps publishing new win32 binary installers for Python 2.5. Latest as of June 29, 2008 is version 2.4.1 ({{{pysqlite-2.4.1.win32-py2.5.exe}}}) that uses sqlite version 3.5.2 (see entry marked with '''*''' in the table above.) ===== How does this affect Django ===== The problem doesn't lie within Django itself, but leads to ask oneself if the order being used by the sqlite3 backed to try loading the sqlite DB-API2 modules ({{{django/db/backends/sqlite3/base.py}}}) shouldn't be inverted: Try loading pysqlite2 first and if this fails then try loading sqlite3: {{{ #!diff --- base.py 2008-04-27 00:58:35.000000000 -0300 +++ base.py-proposed 2008-06-29 20:48:29.000000000 -0300 @@ -3,15 +3,16 @@ Python 2.3 and 2.4 require pysqlite2 (http://pysqlite.org/). -Python 2.5 and later use the sqlite3 module in the standard library. +Python 2.5 and later use pysqlite2 or the sqlite3 module in the standard +library. """ from django.db.backends import BaseDatabaseWrapper, BaseDatabaseFeatures, BaseDatabaseOperations, util try: try: - from sqlite3 import dbapi2 as Database - except ImportError: from pysqlite2 import dbapi2 as Database + except ImportError: + from sqlite3 import dbapi2 as Database except ImportError, e: import sys from django.core.exceptions import ImproperlyConfigured }}} Reasoning behind this is that this would allow the user to take advantage of newer pysqlite2/sqlite3 versions he/she may have installed even if using Python 2.5.x. This might be true and desirable regardless of platform. == Deployment == * WindowsInstall * wiki:django_apache_and_mod_wsgi * ServerArrangements