DBCacheTests.test_cull fails under Oracle

[Aymeric Augustin]

$ python runtests.py --settings=test_settings.oracle cache
Creating test database for alias 'default'...
Creating test user...
Creating test database for alias 'other'...
Creating test user...
...........................E................................................................................................................ssssssssssssssssssssssssssssssss.......
======================================================================
ERROR: test_cull (regressiontests.cache.tests.DBCacheTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 750, in test_cull
    self.perform_cull_test(50, 29)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/tests/regressiontests/cache/tests.py", line 417, in perform_cull_test
    self.cache.set('cull%d' % i, 'value', 1000)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 65, in set
    self._base_set('set', key, value, timeout)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 84, in _base_set
    self._cull(db, cursor, now)
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/core/cache/backends/db.py", line 138, in _cull
    cursor.execute("SELECT cache_key FROM %s ORDER BY cache_key LIMIT 1 OFFSET %%s" % table, [num / self._cull_frequency])
  File "/var/lib/jenkins/jobs/Django/workspace/settings/oracle/django/db/backends/oracle/base.py", line 638, in execute
    return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-00933: SQL command not properly ended


----------------------------------------------------------------------
Ran 179 tests in 26.809s

FAILED (errors=1, skipped=32)
Destroying test database for alias 'default'...
Destroying test user...
Destroying test database tables...
Destroying test database for alias 'other'...
Destroying test user...
Destroying test database tables...

[Aymeric Augustin]

We now have a CI server for Oracle, see ​http://ci.django-cms.org/job/Django/database=oracle,python=python2.7/lastCompletedBuild/testReport/regressiontests.cache.tests/DBCacheTests/test_cull/

[Aymeric Augustin]

Oracle doesn't support the combination of LIMIT and OFFSET. This is a very common source of question in forums.

I found this page which provides an analysis of this problem on several RDBMS: ​http://troels.arvin.dk/db/rdbms/#select-limit-offset

It says that the SQL we currently use, ORDER BY ... LIMIT ... OFFSET ..., isn't standard. But it's supported by PostgreSQL, MySQL and SQLite at least (the test passes on those engines). Also, it's the only solution supported by MySQL, and it's simple. As a consequence, I chose to add a special case for Oracle, and a comment to warn about the use of non-standard SQL.

Since the database cache backend isn't an industrial grade concept, I don't believe someone who has the resources to purchase an Oracle license will ever use it. This patch may be a bit hackish but it fixes the test suite with limited changes.