1 =========

     2 Databases

     3 =========

     4 

     5 Django attempts to support as many features as possible on all database

     6 backends. However, not all database backends are alike, and we've had to make

     7 design decisions on which features to support and which assumptions we can make

     8 safely.

     9 

    10 This file describes some of the features that might be relevant to Django

    11 usage. Of course, it is not intended as a replacement for server-specific

    12 documentation or reference manuals.

    13 

    14 .. _postgresql-notes:

    15 

    16 PostgreSQL notes

    17 ================

    18 

    19 PostgreSQL 8.2 to 8.2.4

    20 -----------------------

    21 

    22 The implementation of the population statistics aggregates ``STDDEV_POP`` and

    23 ``VAR_POP`` that shipped with PostgreSQL 8.2 to 8.2.4 are `known to be

    24 faulty`_. Users of these releases of PostgreSQL are advised to upgrade to

    25 `Release 8.2.5`_ or later. Django will raise a ``NotImplementedError`` if you

    26 attempt to use the ``StdDev(sample=False)`` or ``Variance(sample=False)``

    27 aggregate with a database backend that falls within the affected release range.

    28 

    29 .. _known to be faulty: http://archives.postgresql.org/pgsql-bugs/2007-07/msg00046.php

    30 .. _Release 8.2.5: http://developer.postgresql.org/pgdocs/postgres/release-8-2-5.html

    31 

    32 Transaction handling

    33 ---------------------

    34 

    35 :doc:`By default </topics/db/transactions>`, Django starts a transaction when a

    36 database connection is first used and commits the result at the end of the

    37 request/response handling. The PostgreSQL backends normally operate the same

    38 as any other Django backend in this respect.

    39 

    40 Autocommit mode

    41 ~~~~~~~~~~~~~~~

    42 

    43 .. versionadded:: 1.1

    44 

    45 If your application is particularly read-heavy and doesn't make many

    46 database writes, the overhead of a constantly open transaction can

    47 sometimes be noticeable. For those situations, if you're using the

    48 ``postgresql_psycopg2`` backend, you can configure Django to use

    49 *"autocommit"* behavior for the connection, meaning that each database

    50 operation will normally be in its own transaction, rather than having

    51 the transaction extend over multiple operations. In this case, you can

    52 still manually start a transaction if you're doing something that

    53 requires consistency across multiple database operations. The

    54 autocommit behavior is enabled by setting the ``autocommit`` key in

    55 the :setting:`OPTIONS` part of your database configuration in

    56 :setting:`DATABASES`::

    57 

    58     'OPTIONS': {

    59         'autocommit': True,

    60     }

    61 

    62 In this configuration, Django still ensures that :ref:`delete()

    63 <topics-db-queries-delete>` and :ref:`update() <topics-db-queries-update>`

    64 queries run inside a single transaction, so that either all the affected

    65 objects are changed or none of them are.

    66 

    67 .. admonition:: This is database-level autocommit

    68 

    69     This functionality is not the same as the

    70     :ref:`topics-db-transactions-autocommit` decorator. That decorator

    71     is a Django-level implementation that commits automatically after

    72     data changing operations. The feature enabled using the

    73     :setting:`OPTIONS` option provides autocommit behavior at the

    74     database adapter level. It commits after *every* operation.

    75 

    76 If you are using this feature and performing an operation akin to delete or

    77 updating that requires multiple operations, you are strongly recommended to

    78 wrap you operations in manual transaction handling to ensure data consistency.

    79 You should also audit your existing code for any instances of this behavior

    80 before enabling this feature. It's faster, but it provides less automatic

    81 protection for multi-call operations.

    82 

    83 Indexes for ``varchar`` and ``text`` columns

    84 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    85 .. versionadded:: 1.1.2

    86 

    87 When specifying ``db_index=True`` on your model fields, Django typically

    88 outputs a single ``CREATE INDEX`` statement.  However, if the database type

    89 for the field is either ``varchar`` or ``text`` (e.g., used by ``CharField``,

    90 ``FileField``, and ``TextField``), then Django will create

    91 an additional index that uses an appropriate `PostgreSQL operator class`_

    92 for the column.  The extra index is necessary to correctly perfrom

    93 lookups that use the ``LIKE`` operator in their SQL, as is done with the

    94 ``contains`` and ``startswith`` lookup types.

    95 

    96 .. _PostgreSQL operator class: http://www.postgresql.org/docs/8.4/static/indexes-opclass.html

    97 

    98 .. _mysql-notes:

    99 

   100 MySQL notes

   101 ===========

   102 

   103 Django expects the database to support transactions, referential integrity, and

   104 Unicode (UTF-8 encoding). Fortunately, MySQL_ has all these features as

   105 available as far back as 3.23. While it may be possible to use 3.23 or 4.0,

   106 you'll probably have less trouble if you use 4.1 or 5.0.

   107 

   108 MySQL 4.1

   109 ---------

   110 

   111 `MySQL 4.1`_ has greatly improved support for character sets. It is possible to

   112 set different default character sets on the database, table, and column.

   113 Previous versions have only a server-wide character set setting. It's also the

   114 first version where the character set can be changed on the fly. 4.1 also has

   115 support for views, but Django currently doesn't use views.

   116 

   117 MySQL 5.0

   118 ---------

   119 

   120 `MySQL 5.0`_ adds the ``information_schema`` database, which contains detailed

   121 data on all database schema. Django's ``inspectdb`` feature uses this

   122 ``information_schema`` if it's available. 5.0 also has support for stored

   123 procedures, but Django currently doesn't use stored procedures.

   124 

   125 .. _MySQL: http://www.mysql.com/

   126 .. _MySQL 4.1: http://dev.mysql.com/doc/refman/4.1/en/index.html

   127 .. _MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/index.html

   128 

   129 Storage engines

   130 ---------------

   131 

   132 MySQL has several `storage engines`_ (previously called table types). You can

   133 change the default storage engine in the server configuration.

   134 

   135 The default engine is MyISAM_ [#]_. The main drawback of MyISAM is that it

   136 doesn't currently support transactions or foreign keys. On the plus side, it's

   137 currently the only engine that supports full-text indexing and searching.

   138 

   139 The InnoDB_ engine is fully transactional and supports foreign key references.

   140 

   141 The BDB_ engine, like InnoDB, is also fully transactional and supports foreign

   142 key references. However, its use seems to be deprecated.

   143 

   144 `Other storage engines`_, including SolidDB_ and Falcon_, are on the horizon.

   145 For now, InnoDB is probably your best choice.

   146 

   147 .. _storage engines: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

   148 .. _MyISAM: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

   149 .. _BDB: http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html

   150 .. _InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb.html

   151 .. _Other storage engines: http://dev.mysql.com/doc/refman/5.1/en/storage-engines-other.html

   152 .. _SolidDB: http://forge.mysql.com/projects/project.php?id=139

   153 .. _Falcon: http://dev.mysql.com/doc/falcon/en/index.html

   154 

   155 .. [#] Unless this was changed by the packager of your MySQL package. We've

   156    had reports that the Windows Community Server installer sets up InnoDB as

   157    the default storage engine, for example.

   158 

   159 MySQLdb

   160 -------

   161 

   162 `MySQLdb`_ is the Python interface to MySQL. Version 1.2.1p2 or later is

   163 required for full MySQL support in Django.

   164 

   165 .. note::

   166     If you see ``ImportError: cannot import name ImmutableSet`` when trying to

   167     use Django, your MySQLdb installation may contain an outdated ``sets.py``

   168     file that conflicts with the built-in module of the same name from Python

   169     2.4 and later. To fix this, verify that you have installed MySQLdb version

   170     1.2.1p2 or newer, then delete the ``sets.py`` file in the MySQLdb

   171     directory that was left by an earlier version.

   172 

   173 .. _MySQLdb: http://sourceforge.net/projects/mysql-python

   174 

   175 Creating your database

   176 ----------------------

   177 

   178 You can `create your database`_ using the command-line tools and this SQL::

   179 

   180   CREATE DATABASE <dbname> CHARACTER SET utf8;

   181 

   182 This ensures all tables and columns will use UTF-8 by default.

   183 

   184 .. _create your database: http://dev.mysql.com/doc/refman/5.0/en/create-database.html

   185 

   186 .. _mysql-collation:

   187 

   188 Collation settings

   189 ~~~~~~~~~~~~~~~~~~

   190 

   191 The collation setting for a column controls the order in which data is sorted

   192 as well as what strings compare as equal. It can be set on a database-wide

   193 level and also per-table and per-column. This is `documented thoroughly`_ in

   194 the MySQL documentation. In all cases, you set the collation by directly

   195 manipulating the database tables; Django doesn't provide a way to set this on

   196 the model definition.

   197 

   198 .. _documented thoroughly: http://dev.mysql.com/doc/refman/5.0/en/charset.html

   199 

   200 By default, with a UTF-8 database, MySQL will use the

   201 ``utf8_general_ci_swedish`` collation. This results in all string equality

   202 comparisons being done in a *case-insensitive* manner. That is, ``"Fred"`` and

   203 ``"freD"`` are considered equal at the database level. If you have a unique

   204 constraint on a field, it would be illegal to try to insert both ``"aa"`` and

   205 ``"AA"`` into the same column, since they compare as equal (and, hence,

   206 non-unique) with the default collation.

   207 

   208 In many cases, this default will not be a problem. However, if you really want

   209 case-sensitive comparisons on a particular column or table, you would change

   210 the column or table to use the ``utf8_bin`` collation. The main thing to be

   211 aware of in this case is that if you are using MySQLdb 1.2.2, the database

   212 backend in Django will then return bytestrings (instead of unicode strings) for

   213 any character fields it receive from the database. This is a strong variation

   214 from Django's normal practice of *always* returning unicode strings. It is up

   215 to you, the developer, to handle the fact that you will receive bytestrings if

   216 you configure your table(s) to use ``utf8_bin`` collation. Django itself should

   217 mostly work smoothly with such columns (except for the ``contrib.sessions``

   218 ``Session`` and ``contrib.admin`` ``LogEntry`` tables described below), but

   219 your code must be prepared to call ``django.utils.encoding.smart_unicode()`` at

   220 times if it really wants to work with consistent data -- Django will not do

   221 this for you (the database backend layer and the model population layer are

   222 separated internally so the database layer doesn't know it needs to make this

   223 conversion in this one particular case).

   224 

   225 If you're using MySQLdb 1.2.1p2, Django's standard

   226 :class:`~django.db.models.CharField` class will return unicode strings even

   227 with ``utf8_bin`` collation. However, :class:`~django.db.models.TextField`

   228 fields will be returned as an ``array.array`` instance (from Python's standard

   229 ``array`` module). There isn't a lot Django can do about that, since, again,

   230 the information needed to make the necessary conversions isn't available when

   231 the data is read in from the database. This problem was `fixed in MySQLdb

   232 1.2.2`_, so if you want to use :class:`~django.db.models.TextField` with

   233 ``utf8_bin`` collation, upgrading to version 1.2.2 and then dealing with the

   234 bytestrings (which shouldn't be too difficult) as described above is the

   235 recommended solution.

   236 

   237 Should you decide to use ``utf8_bin`` collation for some of your tables with

   238 MySQLdb 1.2.1p2 or 1.2.2, you should still use ``utf8_collation_ci_swedish``

   239 (the default) collation for the :class:`django.contrib.sessions.models.Session`

   240 table (usually called ``django_session``) and the

   241 :class:`django.contrib.admin.models.LogEntry` table (usually called

   242 ``django_admin_log``). Those are the two standard tables that use

   243 :class:`~django.db.model.TextField` internally.

   244 

   245 .. _fixed in MySQLdb 1.2.2: http://sourceforge.net/tracker/index.php?func=detail&aid=1495765&group_id=22307&atid=374932

   246 

   247 Connecting to the database

   248 --------------------------

   249 

   250 Refer to the :doc:`settings documentation </ref/settings>`.

   251 

   252 Connection settings are used in this order:

   253 

   254     1. :setting:`OPTIONS`.

   255     2. :setting:`NAME`, :setting:`USER`, :setting:`PASSWORD`,

   256        :setting:`HOST`, :setting:`PORT`

   257     3. MySQL option files.

   258 

   259 In other words, if you set the name of the database in ``OPTIONS``,

   260 this will take precedence over ``NAME``, which would override

   261 anything in a `MySQL option file`_.

   262 

   263 Here's a sample configuration which uses a MySQL option file::

   264 

   265     # settings.py

   266     DATABASES = {

   267         'default': {

   268             'ENGINE': 'django.db.backends.mysql',

   269             'OPTIONS': {

   270                 'read_default_file': '/path/to/my.cnf',

   271             },

   272         }

   273     }

   274 

   275 

   276     # my.cnf

   277     [client]

   278     database = NAME

   279     user = USER

   280     password = PASSWORD

   281     default-character-set = utf8

   282 

   283 Several other MySQLdb connection options may be useful, such as ``ssl``,

   284 ``use_unicode``, ``init_command``, and ``sql_mode``. Consult the

   285 `MySQLdb documentation`_ for more details.

   286 

   287 .. _MySQL option file: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

   288 .. _MySQLdb documentation: http://mysql-python.sourceforge.net/

   289 

   290 Creating your tables

   291 --------------------

   292 

   293 When Django generates the schema, it doesn't specify a storage engine, so

   294 tables will be created with whatever default storage engine your database

   295 server is configured for. The easiest solution is to set your database server's

   296 default storage engine to the desired engine.

   297 

   298 If you're using a hosting service and can't change your server's default

   299 storage engine, you have a couple of options.

   300 

   301     * After the tables are created, execute an ``ALTER TABLE`` statement to

   302       convert a table to a new storage engine (such as InnoDB)::

   303 

   304           ALTER TABLE <tablename> ENGINE=INNODB;

   305 

   306       This can be tedious if you have a lot of tables.

   307 

   308     * Another option is to use the ``init_command`` option for MySQLdb prior to

   309       creating your tables::

   310 

   311           'OPTIONS': {

   312              'init_command': 'SET storage_engine=INNODB',

   313           }

   314 

   315       This sets the default storage engine upon connecting to the database.

   316       After your tables have been created, you should remove this option.

   317 

   318     * Another method for changing the storage engine is described in

   319       AlterModelOnSyncDB_.

   320 

   321 .. _AlterModelOnSyncDB: http://code.djangoproject.com/wiki/AlterModelOnSyncDB

   322 

   323 Notes on specific fields

   324 ------------------------

   325 

   326 Boolean fields

   327 ~~~~~~~~~~~~~~

   328 

   329 .. versionchanged:: 1.2

   330 

   331 In previous versions of Django when running under MySQL ``BooleanFields`` would

   332 return their data as ``ints``, instead of true ``bools``.  See the release

   333 notes for a complete description of the change.

   334 

   335 Character fields

   336 ~~~~~~~~~~~~~~~~

   337 

   338 Any fields that are stored with ``VARCHAR`` column types have their

   339 ``max_length`` restricted to 255 characters if you are using ``unique=True``

   340 for the field. This affects :class:`~django.db.models.CharField`,

   341 :class:`~django.db.models.SlugField` and

   342 :class:`~django.db.models.CommaSeparatedIntegerField`.

   343 

   344 Furthermore, if you are using a version of MySQL prior to 5.0.3, all of those

   345 column types have a maximum length restriction of 255 characters, regardless

   346 of whether ``unique=True`` is specified or not.

   347 

   348 .. _sqlite-notes:

   349 

   350 SQLite notes

   351 ============

   352 

   353 SQLite_ provides an excellent development alternative for applications that

   354 are predominantly read-only or require a smaller installation footprint. As

   355 with all database servers, though, there are some differences that are

   356 specific to SQLite that you should be aware of.

   357 

   358 .. _SQLite: http://www.sqlite.org/

   359 

   360 .. _sqlite-string-matching:

   361 

   362 String matching for non-ASCII strings

   363 --------------------------------------

   364 

   365 SQLite doesn't support case-insensitive matching for non-ASCII strings. Some

   366 possible workarounds for this are `documented at sqlite.org`_, but they are

   367 not utilised by the default SQLite backend in Django. Therefore, if you are

   368 using the ``iexact`` lookup type in your queryset filters, be aware that it

   369 will not work as expected for non-ASCII strings.

   370 

   371 .. _documented at sqlite.org: http://www.sqlite.org/faq.html#q18

   372 

   373 SQLite 3.3.6 or newer strongly recommended

   374 ------------------------------------------

   375 

   376 Versions of SQLite 3.3.5 and older contains the following bugs:

   377 

   378  * A bug when `handling`_ ``ORDER BY`` parameters. This can cause problems when

   379    you use the ``select`` parameter for the ``extra()`` QuerySet method. The bug

   380    can be identified by the error message ``OperationalError: ORDER BY terms

   381    must not be non-integer constants``.

   382 

   383  * A bug when handling `aggregation`_ together with DateFields and

   384    DecimalFields.

   385 

   386 .. _handling: http://www.sqlite.org/cvstrac/tktview?tn=1768

   387 .. _aggregation: http://code.djangoproject.com/ticket/10031

   388 

   389 SQLite 3.3.6 was released in April 2006, so most current binary distributions

   390 for different platforms include newer version of SQLite usable from Python

   391 through either the ``pysqlite2`` or the ``sqlite3`` modules.

   392 

   393 However, some platform/Python version combinations include older versions of

   394 SQLite (e.g. the official binary distribution of Python 2.5 for Windows, 2.5.4

   395 as of this writing, includes SQLite 3.3.4). There are (as of Django 1.1) even

   396 some tests in the Django test suite that will fail when run under this setup.

   397 

   398 As described :ref:`below<using-newer-versions-of-pysqlite>`, this can be solved

   399 by downloading and installing a newer version of ``pysqlite2``

   400 (``pysqlite-2.x.x.win32-py2.5.exe`` in the described case) that includes and

   401 uses a newer version of SQLite. Python 2.6 for Windows ships with a version of

   402 SQLite that is not affected by these issues.

   403 

   404 Version 3.5.9

   405 -------------

   406 

   407 The Ubuntu "Intrepid Ibex" (8.10) SQLite 3.5.9-3 package contains a bug that

   408 causes problems with the evaluation of query expressions. If you are using

   409 Ubuntu "Intrepid Ibex", you will need to update the package to version

   410 3.5.9-3ubuntu1 or newer (recommended) or find an alternate source for SQLite

   411 packages, or install SQLite from source.

   412 

   413 At one time, Debian Lenny shipped with the same malfunctioning SQLite 3.5.9-3

   414 package. However the Debian project has subsequently issued updated versions

   415 of the SQLite package that correct these bugs. If you find you are getting

   416 unexpected results under Debian, ensure you have updated your SQLite package

   417 to 3.5.9-5 or later.

   418 

   419 The problem does not appear to exist with other versions of SQLite packaged

   420 with other operating systems.

   421 

   422 Version 3.6.2

   423 --------------

   424 

   425 SQLite version 3.6.2 (released August 30, 2008) introduced a bug into ``SELECT

   426 DISTINCT`` handling that is triggered by, amongst other things, Django's

   427 ``DateQuerySet`` (returned by the ``dates()`` method on a queryset).

   428 

   429 You should avoid using this version of SQLite with Django. Either upgrade to

   430 3.6.3 (released September 22, 2008) or later, or downgrade to an earlier

   431 version of SQLite.

   432 

   433 .. _using-newer-versions-of-pysqlite:

   434 

   435 Using newer versions of the SQLite DB-API 2.0 driver

   436 ----------------------------------------------------

   437 

   438 .. versionadded:: 1.1

   439 

   440 For versions of Python 2.5 or newer that include ``sqlite3`` in the standard

   441 library Django will now use a ``pysqlite2`` interface in preference to

   442 ``sqlite3`` if it finds one is available.

   443 

   444 This provides the ability to upgrade both the DB-API 2.0 interface or SQLite 3

   445 itself to versions newer than the ones included with your particular Python

   446 binary distribution, if needed.

   447 

   448 "Database is locked" errors

   449 -----------------------------------------------

   450 

   451 SQLite is meant to be a lightweight database, and thus can't support a high

   452 level of concurrency. ``OperationalError: database is locked`` errors indicate

   453 that your application is experiencing more concurrency than ``sqlite`` can

   454 handle in default configuration. This error means that one thread or process has

   455 an exclusive lock on the database connection and another thread timed out

   456 waiting for the lock the be released.

   457 

   458 Python's SQLite wrapper has

   459 a default timeout value that determines how long the second thread is allowed to

   460 wait on the lock before it times out and raises the ``OperationalError: database

   461 is locked`` error.

   462 

   463 If you're getting this error, you can solve it by:

   464 

   465     * Switching to another database backend. At a certain point SQLite becomes

   466       too "lite" for real-world applications, and these sorts of concurrency

   467       errors indicate you've reached that point.

   468 

   469     * Rewriting your code to reduce concurrency and ensure that database

   470       transactions are short-lived.

   471 

   472     * Increase the default timeout value by setting the ``timeout`` database

   473       option option::

   474 

   475           'OPTIONS': {

   476               # ...

   477               'timeout': 20,

   478               # ...

   479           }

   480 

   481       This will simply make SQLite wait a bit longer before throwing "database

   482       is locked" errors; it won't really do anything to solve them.

   483 

   484 .. _oracle-notes:

   485 

   486 Oracle notes

   487 ============

   488 

   489 Django supports `Oracle Database Server`_ versions 9i and

   490 higher. Oracle version 10g or later is required to use Django's

   491 ``regex`` and ``iregex`` query operators. You will also need at least

   492 version 4.3.1 of the `cx_Oracle`_ Python driver.

   493 

   494 Note that due to a Unicode-corruption bug in ``cx_Oracle`` 5.0, that

   495 version of the driver should **not** be used with Django;

   496 ``cx_Oracle`` 5.0.1 resolved this issue, so if you'd like to use a

   497 more recent ``cx_Oracle``, use version 5.0.1.

   498 

   499 ``cx_Oracle`` 5.0.1 or greater can optionally be compiled with the

   500 ``WITH_UNICODE`` environment variable.  This is recommended but not

   501 required.

   502 

   503 .. _`Oracle Database Server`: http://www.oracle.com/

   504 .. _`cx_Oracle`: http://cx-oracle.sourceforge.net/

   505 

   506 In order for the ``python manage.py syncdb`` command to work, your Oracle

   507 database user must have privileges to run the following commands:

   508 

   509     * CREATE TABLE

   510     * CREATE SEQUENCE

   511     * CREATE PROCEDURE

   512     * CREATE TRIGGER

   513 

   514 To run Django's test suite, the user needs these *additional* privileges:

   515 

   516     * CREATE USER

   517     * DROP USER

   518     * CREATE TABLESPACE

   519     * DROP TABLESPACE

   520     * CONNECT WITH ADMIN OPTION

   521     * RESOURCE WITH ADMIN OPTION

   522 

   523 Connecting to the database

   524 --------------------------

   525 

   526 Your Django settings.py file should look something like this for Oracle::

   527 

   528     DATABASES = {

   529         'default': {

   530             'ENGINE': 'django.db.backends.oracle',

   531             'NAME': 'xe',

   532             'USER': 'a_user',

   533             'PASSWORD': 'a_password',

   534             'HOST': '',

   535             'PORT': '',

   536         }

   537     }

   538 

   539 

   540 If you don't use a ``tnsnames.ora`` file or a similar naming method that

   541 recognizes the SID ("xe" in this example), then fill in both

   542 ``HOST`` and ``PORT`` like so::

   543 

   544     DATABASES = {

   545         'default': {

   546             'ENGINE': 'django.db.backends.oracle',

   547             'NAME': 'xe',

   548             'USER': 'a_user',

   549             'PASSWORD': 'a_password',

   550             'HOST': 'dbprod01ned.mycompany.com',

   551             'PORT': '1540',

   552         }

   553     }

   554 

   555 You should supply both ``HOST`` and ``PORT``, or leave both

   556 as empty strings.

   557 

   558 Threaded option

   559 ----------------

   560 

   561 If you plan to run Django in a multithreaded environment (e.g. Apache in Windows

   562 using the default MPM module), then you **must** set the ``threaded`` option of

   563 your Oracle database configuration to True::

   564 

   565             'OPTIONS': {

   566                 'threaded': True,

   567             },

   568 

   569 Failure to do this may result in crashes and other odd behavior.

   570 

   571 Tablespace options

   572 ------------------

   573 

   574 A common paradigm for optimizing performance in Oracle-based systems is the

   575 use of `tablespaces`_ to organize disk layout. The Oracle backend supports

   576 this use case by adding ``db_tablespace`` options to the ``Meta`` and

   577 ``Field`` classes.  (When you use a backend that lacks support for tablespaces,

   578 Django ignores these options.)

   579 

   580 .. _`tablespaces`: http://en.wikipedia.org/wiki/Tablespace

   581 

   582 A tablespace can be specified for the table(s) generated by a model by

   583 supplying the ``db_tablespace`` option inside the model's ``class Meta``.

   584 Additionally, you can pass the ``db_tablespace`` option to a ``Field``

   585 constructor to specify an alternate tablespace for the ``Field``'s column

   586 index. If no index would be created for the column, the ``db_tablespace``

   587 option is ignored::

   588 

   589     class TablespaceExample(models.Model):

   590         name = models.CharField(max_length=30, db_index=True, db_tablespace="indexes")

   591         data = models.CharField(max_length=255, db_index=True)

   592         edges = models.ManyToManyField(to="self", db_tablespace="indexes")

   593 

   594         class Meta:

   595             db_tablespace = "tables"

   596 

   597 In this example, the tables generated by the ``TablespaceExample`` model

   598 (i.e., the model table and the many-to-many table) would be stored in the

   599 ``tables`` tablespace. The index for the name field and the indexes on the

   600 many-to-many table would be stored in the ``indexes`` tablespace. The ``data``

   601 field would also generate an index, but no tablespace for it is specified, so

   602 it would be stored in the model tablespace ``tables`` by default.

   603 

   604 .. versionadded:: 1.0

   605 

   606 Use the :setting:`DEFAULT_TABLESPACE` and :setting:`DEFAULT_INDEX_TABLESPACE`

   607 settings to specify default values for the db_tablespace options.

   608 These are useful for setting a tablespace for the built-in Django apps and

   609 other applications whose code you cannot control.

   610 

   611 Django does not create the tablespaces for you. Please refer to `Oracle's

   612 documentation`_ for details on creating and managing tablespaces.

   613 

   614 .. _`Oracle's documentation`: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#SQLRF01403

   615 

   616 Naming issues

   617 -------------

   618 

   619 Oracle imposes a name length limit of 30 characters. To accommodate this, the

   620 backend truncates database identifiers to fit, replacing the final four

   621 characters of the truncated name with a repeatable MD5 hash value.

   622 

   623 When running syncdb, an ``ORA-06552`` error may be encountered if

   624 certain Oracle keywords are used as the name of a model field or the

   625 value of a ``db_column`` option.  Django quotes all identifiers used

   626 in queries to prevent most such problems, but this error can still

   627 occur when an Oracle datatype is used as a column name.  In

   628 particular, take care to avoid using the names ``date``,

   629 ``timestamp``, ``number`` or ``float`` as a field name.

   630 

   631 NULL and empty strings

   632 ----------------------

   633 

   634 Django generally prefers to use the empty string ('') rather than

   635 NULL, but Oracle treats both identically. To get around this, the

   636 Oracle backend coerces the ``null=True`` option on fields that have

   637 the empty string as a possible value. When fetching from the database,

   638 it is assumed that a NULL value in one of these fields really means

   639 the empty string, and the data is silently converted to reflect this

   640 assumption.

   641 

   642 ``TextField`` limitations

   643 -------------------------

   644 

   645 The Oracle backend stores ``TextFields`` as ``NCLOB`` columns. Oracle imposes

   646 some limitations on the usage of such LOB columns in general:

   647 

   648   * LOB columns may not be used as primary keys.

   649 

   650   * LOB columns may not be used in indexes.

   651 

   652   * LOB columns may not be used in a ``SELECT DISTINCT`` list. This means that

   653     attempting to use the ``QuerySet.distinct`` method on a model that

   654     includes ``TextField`` columns will result in an error when run against

   655     Oracle. As a workaround, use the ``QuerySet.defer`` method in conjunction

   656     with ``distinct()`` to prevent ``TextField`` columns from being included in

   657     the ``SELECT DISTINCT`` list.

   658 

   659 .. _third-party-notes:

   660 

   661 Using a 3rd-party database backend

   662 ==================================

   663 

   664 In addition to the officially supported databases, there are backends provided

   665 by 3rd parties that allow you to use other databases with Django:

   666 

   667 * `Sybase SQL Anywhere`_

   668 * `IBM DB2`_

   669 * `Microsoft SQL Server 2005`_

   670 * Firebird_

   671 * ODBC_

   672 

   673 The Django versions and ORM features supported by these unofficial backends

   674 vary considerably. Queries regarding the specific capabilities of these

   675 unofficial backends, along with any support queries, should be directed to

   676 the support channels provided by each 3rd party project.

   677 

   678 .. _Sybase SQL Anywhere: http://code.google.com/p/sqlany-django/

   679 .. _IBM DB2: http://code.google.com/p/ibm-db/

   680 .. _Microsoft SQL Server 2005: http://code.google.com/p/django-mssql/

   681 .. _Firebird: http://code.google.com/p/django-firebird/

   682 .. _ODBC: http://code.google.com/p/django-pyodbc/