| ========= |
| Databases |
| ========= |
| |
| Django attempts to support as many features as possible on all database |
| backends. However, not all database backends are alike, and we've had to make |
| design decisions on which features to support and which assumptions we can make |
| safely. |
| |
| This file describes some of the features that might be relevant to Django |
| usage. Of course, it is not intended as a replacement for server-specific |
| documentation or reference manuals. |
| |
| .. _postgresql-notes: |
| |
| PostgreSQL notes |
| ================ |
| |
| PostgreSQL 8.2 to 8.2.4 |
| ----------------------- |
| |
| The implementation of the population statistics aggregates ``STDDEV_POP`` and |
| ``VAR_POP`` that shipped with PostgreSQL 8.2 to 8.2.4 are `known to be |
| faulty`_. Users of these releases of PostgreSQL are advised to upgrade to |
| `Release 8.2.5`_ or later. Django will raise a ``NotImplementedError`` if you |
| attempt to use the ``StdDev(sample=False)`` or ``Variance(sample=False)`` |
| aggregate with a database backend that falls within the affected release range. |
| |
| .. _known to be faulty: http://archives.postgresql.org/pgsql-bugs/2007-07/msg00046.php |
| .. _Release 8.2.5: http://developer.postgresql.org/pgdocs/postgres/release-8-2-5.html |
| |
| Transaction handling |
| --------------------- |
| |
| :doc:`By default </topics/db/transactions>`, Django starts a transaction when a |
| database connection is first used and commits the result at the end of the |
| request/response handling. The PostgreSQL backends normally operate the same |
| as any other Django backend in this respect. |
| |
| Autocommit mode |
| ~~~~~~~~~~~~~~~ |
| |
| .. versionadded:: 1.1 |
| |
| If your application is particularly read-heavy and doesn't make many |
| database writes, the overhead of a constantly open transaction can |
| sometimes be noticeable. For those situations, if you're using the |
| ``postgresql_psycopg2`` backend, you can configure Django to use |
| *"autocommit"* behavior for the connection, meaning that each database |
| operation will normally be in its own transaction, rather than having |
| the transaction extend over multiple operations. In this case, you can |
| still manually start a transaction if you're doing something that |
| requires consistency across multiple database operations. The |
| autocommit behavior is enabled by setting the ``autocommit`` key in |
| the :setting:`OPTIONS` part of your database configuration in |
| :setting:`DATABASES`:: |
| |
| 'OPTIONS': { |
| 'autocommit': True, |
| } |
| |
| In this configuration, Django still ensures that :ref:`delete() |
| <topics-db-queries-delete>` and :ref:`update() <topics-db-queries-update>` |
| queries run inside a single transaction, so that either all the affected |
| objects are changed or none of them are. |
| |
| .. admonition:: This is database-level autocommit |
| |
| This functionality is not the same as the |
| :ref:`topics-db-transactions-autocommit` decorator. That decorator |
| is a Django-level implementation that commits automatically after |
| data changing operations. The feature enabled using the |
| :setting:`OPTIONS` option provides autocommit behavior at the |
| database adapter level. It commits after *every* operation. |
| |
| If you are using this feature and performing an operation akin to delete or |
| updating that requires multiple operations, you are strongly recommended to |
| wrap you operations in manual transaction handling to ensure data consistency. |
| You should also audit your existing code for any instances of this behavior |
| before enabling this feature. It's faster, but it provides less automatic |
| protection for multi-call operations. |
| |
| Indexes for ``varchar`` and ``text`` columns |
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| .. versionadded:: 1.1.2 |
| |
| When specifying ``db_index=True`` on your model fields, Django typically |
| outputs a single ``CREATE INDEX`` statement. However, if the database type |
| for the field is either ``varchar`` or ``text`` (e.g., used by ``CharField``, |
| ``FileField``, and ``TextField``), then Django will create |
| an additional index that uses an appropriate `PostgreSQL operator class`_ |
| for the column. The extra index is necessary to correctly perfrom |
| lookups that use the ``LIKE`` operator in their SQL, as is done with the |
| ``contains`` and ``startswith`` lookup types. |
| |
| .. _PostgreSQL operator class: http://www.postgresql.org/docs/8.4/static/indexes-opclass.html |
| |
| .. _mysql-notes: |
| |
| MySQL notes |
| =========== |
| |
| Django expects the database to support transactions, referential integrity, and |
| Unicode (UTF-8 encoding). Fortunately, MySQL_ has all these features as |
| available as far back as 3.23. While it may be possible to use 3.23 or 4.0, |
| you'll probably have less trouble if you use 4.1 or 5.0. |
| |
| MySQL 4.1 |
| --------- |
| |
| `MySQL 4.1`_ has greatly improved support for character sets. It is possible to |
| set different default character sets on the database, table, and column. |
| Previous versions have only a server-wide character set setting. It's also the |
| first version where the character set can be changed on the fly. 4.1 also has |
| support for views, but Django currently doesn't use views. |
| |
| MySQL 5.0 |
| --------- |
| |
| `MySQL 5.0`_ adds the ``information_schema`` database, which contains detailed |
| data on all database schema. Django's ``inspectdb`` feature uses this |
| ``information_schema`` if it's available. 5.0 also has support for stored |
| procedures, but Django currently doesn't use stored procedures. |
| |
| .. _MySQL: http://www.mysql.com/ |
| .. _MySQL 4.1: http://dev.mysql.com/doc/refman/4.1/en/index.html |
| .. _MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/index.html |
| |
| Storage engines |
| --------------- |
| |
| MySQL has several `storage engines`_ (previously called table types). You can |
| change the default storage engine in the server configuration. |
| |
| The default engine is MyISAM_ [#]_. The main drawback of MyISAM is that it |
| doesn't currently support transactions or foreign keys. On the plus side, it's |
| currently the only engine that supports full-text indexing and searching. |
| |
| The InnoDB_ engine is fully transactional and supports foreign key references |
| and is probably the best choice at this point in time. |
| |
| .. _storage engines: http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html |
| .. _MyISAM: http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html |
| .. _InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb.html |
| |
| .. [#] Unless this was changed by the packager of your MySQL package. We've |
| had reports that the Windows Community Server installer sets up InnoDB as |
| the default storage engine, for example. |
| |
| MySQLdb |
| ------- |
| |
| `MySQLdb`_ is the Python interface to MySQL. Version 1.2.1p2 or later is |
| required for full MySQL support in Django. |
| |
| .. note:: |
| If you see ``ImportError: cannot import name ImmutableSet`` when trying to |
| use Django, your MySQLdb installation may contain an outdated ``sets.py`` |
| file that conflicts with the built-in module of the same name from Python |
| 2.4 and later. To fix this, verify that you have installed MySQLdb version |
| 1.2.1p2 or newer, then delete the ``sets.py`` file in the MySQLdb |
| directory that was left by an earlier version. |
| |
| .. _MySQLdb: http://sourceforge.net/projects/mysql-python |
| |
| Creating your database |
| ---------------------- |
| |
| You can `create your database`_ using the command-line tools and this SQL:: |
| |
| CREATE DATABASE <dbname> CHARACTER SET utf8; |
| |
| This ensures all tables and columns will use UTF-8 by default. |
| |
| .. _create your database: http://dev.mysql.com/doc/refman/5.0/en/create-database.html |
| |
| .. _mysql-collation: |
| |
| Collation settings |
| ~~~~~~~~~~~~~~~~~~ |
| |
| The collation setting for a column controls the order in which data is sorted |
| as well as what strings compare as equal. It can be set on a database-wide |
| level and also per-table and per-column. This is `documented thoroughly`_ in |
| the MySQL documentation. In all cases, you set the collation by directly |
| manipulating the database tables; Django doesn't provide a way to set this on |
| the model definition. |
| |
| .. _documented thoroughly: http://dev.mysql.com/doc/refman/5.0/en/charset.html |
| |
| By default, with a UTF-8 database, MySQL will use the |
| ``utf8_general_ci_swedish`` collation. This results in all string equality |
| comparisons being done in a *case-insensitive* manner. That is, ``"Fred"`` and |
| ``"freD"`` are considered equal at the database level. If you have a unique |
| constraint on a field, it would be illegal to try to insert both ``"aa"`` and |
| ``"AA"`` into the same column, since they compare as equal (and, hence, |
| non-unique) with the default collation. |
| |
| In many cases, this default will not be a problem. However, if you really want |
| case-sensitive comparisons on a particular column or table, you would change |
| the column or table to use the ``utf8_bin`` collation. The main thing to be |
| aware of in this case is that if you are using MySQLdb 1.2.2, the database |
| backend in Django will then return bytestrings (instead of unicode strings) for |
| any character fields it receive from the database. This is a strong variation |
| from Django's normal practice of *always* returning unicode strings. It is up |
| to you, the developer, to handle the fact that you will receive bytestrings if |
| you configure your table(s) to use ``utf8_bin`` collation. Django itself should |
| mostly work smoothly with such columns (except for the ``contrib.sessions`` |
| ``Session`` and ``contrib.admin`` ``LogEntry`` tables described below), but |
| your code must be prepared to call ``django.utils.encoding.smart_unicode()`` at |
| times if it really wants to work with consistent data -- Django will not do |
| this for you (the database backend layer and the model population layer are |
| separated internally so the database layer doesn't know it needs to make this |
| conversion in this one particular case). |
| |
| If you're using MySQLdb 1.2.1p2, Django's standard |
| :class:`~django.db.models.CharField` class will return unicode strings even |
| with ``utf8_bin`` collation. However, :class:`~django.db.models.TextField` |
| fields will be returned as an ``array.array`` instance (from Python's standard |
| ``array`` module). There isn't a lot Django can do about that, since, again, |
| the information needed to make the necessary conversions isn't available when |
| the data is read in from the database. This problem was `fixed in MySQLdb |
| 1.2.2`_, so if you want to use :class:`~django.db.models.TextField` with |
| ``utf8_bin`` collation, upgrading to version 1.2.2 and then dealing with the |
| bytestrings (which shouldn't be too difficult) as described above is the |
| recommended solution. |
| |
| Should you decide to use ``utf8_bin`` collation for some of your tables with |
| MySQLdb 1.2.1p2 or 1.2.2, you should still use ``utf8_collation_ci_swedish`` |
| (the default) collation for the :class:`django.contrib.sessions.models.Session` |
| table (usually called ``django_session``) and the |
| :class:`django.contrib.admin.models.LogEntry` table (usually called |
| ``django_admin_log``). Those are the two standard tables that use |
| :class:`~django.db.model.TextField` internally. |
| |
| .. _fixed in MySQLdb 1.2.2: http://sourceforge.net/tracker/index.php?func=detail&aid=1495765&group_id=22307&atid=374932 |
| |
| Connecting to the database |
| -------------------------- |
| |
| Refer to the :doc:`settings documentation </ref/settings>`. |
| |
| Connection settings are used in this order: |
| |
| 1. :setting:`OPTIONS`. |
| 2. :setting:`NAME`, :setting:`USER`, :setting:`PASSWORD`, |
| :setting:`HOST`, :setting:`PORT` |
| 3. MySQL option files. |
| |
| In other words, if you set the name of the database in ``OPTIONS``, |
| this will take precedence over ``NAME``, which would override |
| anything in a `MySQL option file`_. |
| |
| Here's a sample configuration which uses a MySQL option file:: |
| |
| # settings.py |
| DATABASES = { |
| 'default': { |
| 'ENGINE': 'django.db.backends.mysql', |
| 'OPTIONS': { |
| 'read_default_file': '/path/to/my.cnf', |
| }, |
| } |
| } |
| |
| |
| # my.cnf |
| [client] |
| database = NAME |
| user = USER |
| password = PASSWORD |
| default-character-set = utf8 |
| |
| Several other MySQLdb connection options may be useful, such as ``ssl``, |
| ``use_unicode``, ``init_command``, and ``sql_mode``. Consult the |
| `MySQLdb documentation`_ for more details. |
| |
| .. _MySQL option file: http://dev.mysql.com/doc/refman/5.0/en/option-files.html |
| .. _MySQLdb documentation: http://mysql-python.sourceforge.net/ |
| |
| Creating your tables |
| -------------------- |
| |
| When Django generates the schema, it doesn't specify a storage engine, so |
| tables will be created with whatever default storage engine your database |
| server is configured for. The easiest solution is to set your database server's |
| default storage engine to the desired engine. |
| |
| If you're using a hosting service and can't change your server's default |
| storage engine, you have a couple of options. |
| |
| * After the tables are created, execute an ``ALTER TABLE`` statement to |
| convert a table to a new storage engine (such as InnoDB):: |
| |
| ALTER TABLE <tablename> ENGINE=INNODB; |
| |
| This can be tedious if you have a lot of tables. |
| |
| * Another option is to use the ``init_command`` option for MySQLdb prior to |
| creating your tables:: |
| |
| 'OPTIONS': { |
| 'init_command': 'SET storage_engine=INNODB', |
| } |
| |
| This sets the default storage engine upon connecting to the database. |
| After your tables have been created, you should remove this option. |
| |
| * Another method for changing the storage engine is described in |
| AlterModelOnSyncDB_. |
| |
| .. _AlterModelOnSyncDB: http://code.djangoproject.com/wiki/AlterModelOnSyncDB |
| |
| Notes on specific fields |
| ------------------------ |
| |
| Boolean fields |
| ~~~~~~~~~~~~~~ |
| |
| .. versionchanged:: 1.2 |
| |
| In previous versions of Django when running under MySQL ``BooleanFields`` would |
| return their data as ``ints``, instead of true ``bools``. See the release |
| notes for a complete description of the change. |
| |
| Character fields |
| ~~~~~~~~~~~~~~~~ |
| |
| Any fields that are stored with ``VARCHAR`` column types have their |
| ``max_length`` restricted to 255 characters if you are using ``unique=True`` |
| for the field. This affects :class:`~django.db.models.CharField`, |
| :class:`~django.db.models.SlugField` and |
| :class:`~django.db.models.CommaSeparatedIntegerField`. |
| |
| Furthermore, if you are using a version of MySQL prior to 5.0.3, all of those |
| column types have a maximum length restriction of 255 characters, regardless |
| of whether ``unique=True`` is specified or not. |
| |
| DateTime fields |
| ~~~~~~~~~~~~~~~ |
| |
| MySQL does not have a timezone-aware column type. If an attempt is made to |
| store a timezone-aware ``time`` or ``datetime`` to a |
| :class:`~django.db.models.TimeField` or :class:`~django.db.models.DateTimeField` |
| respectively, a ``ValueError`` is raised rather than truncating data. |
| |
| .. _sqlite-notes: |
| |
| SQLite notes |
| ============ |
| |
| SQLite_ provides an excellent development alternative for applications that |
| are predominantly read-only or require a smaller installation footprint. As |
| with all database servers, though, there are some differences that are |
| specific to SQLite that you should be aware of. |
| |
| .. _SQLite: http://www.sqlite.org/ |
| |
| .. _sqlite-string-matching: |
| |
| String matching for non-ASCII strings |
| -------------------------------------- |
| |
| SQLite doesn't support case-insensitive matching for non-ASCII strings. Some |
| possible workarounds for this are `documented at sqlite.org`_, but they are |
| not utilised by the default SQLite backend in Django. Therefore, if you are |
| using the ``iexact`` lookup type in your queryset filters, be aware that it |
| will not work as expected for non-ASCII strings. |
| |
| .. _documented at sqlite.org: http://www.sqlite.org/faq.html#q18 |
| |
| SQLite 3.3.6 or newer strongly recommended |
| ------------------------------------------ |
| |
| Versions of SQLite 3.3.5 and older contains the following bugs: |
| |
| * A bug when `handling`_ ``ORDER BY`` parameters. This can cause problems when |
| you use the ``select`` parameter for the ``extra()`` QuerySet method. The bug |
| can be identified by the error message ``OperationalError: ORDER BY terms |
| must not be non-integer constants``. |
| |
| * A bug when handling `aggregation`_ together with DateFields and |
| DecimalFields. |
| |
| .. _handling: http://www.sqlite.org/cvstrac/tktview?tn=1768 |
| .. _aggregation: http://code.djangoproject.com/ticket/10031 |
| |
| SQLite 3.3.6 was released in April 2006, so most current binary distributions |
| for different platforms include newer version of SQLite usable from Python |
| through either the ``pysqlite2`` or the ``sqlite3`` modules. |
| |
| However, some platform/Python version combinations include older versions of |
| SQLite (e.g. the official binary distribution of Python 2.5 for Windows, 2.5.4 |
| as of this writing, includes SQLite 3.3.4). There are (as of Django 1.1) even |
| some tests in the Django test suite that will fail when run under this setup. |
| |
| As described :ref:`below<using-newer-versions-of-pysqlite>`, this can be solved |
| by downloading and installing a newer version of ``pysqlite2`` |
| (``pysqlite-2.x.x.win32-py2.5.exe`` in the described case) that includes and |
| uses a newer version of SQLite. Python 2.6 for Windows ships with a version of |
| SQLite that is not affected by these issues. |
| |
| Version 3.5.9 |
| ------------- |
| |
| The Ubuntu "Intrepid Ibex" (8.10) SQLite 3.5.9-3 package contains a bug that |
| causes problems with the evaluation of query expressions. If you are using |
| Ubuntu "Intrepid Ibex", you will need to update the package to version |
| 3.5.9-3ubuntu1 or newer (recommended) or find an alternate source for SQLite |
| packages, or install SQLite from source. |
| |
| At one time, Debian Lenny shipped with the same malfunctioning SQLite 3.5.9-3 |
| package. However the Debian project has subsequently issued updated versions |
| of the SQLite package that correct these bugs. If you find you are getting |
| unexpected results under Debian, ensure you have updated your SQLite package |
| to 3.5.9-5 or later. |
| |
| The problem does not appear to exist with other versions of SQLite packaged |
| with other operating systems. |
| |
| Version 3.6.2 |
| -------------- |
| |
| SQLite version 3.6.2 (released August 30, 2008) introduced a bug into ``SELECT |
| DISTINCT`` handling that is triggered by, amongst other things, Django's |
| ``DateQuerySet`` (returned by the ``dates()`` method on a queryset). |
| |
| You should avoid using this version of SQLite with Django. Either upgrade to |
| 3.6.3 (released September 22, 2008) or later, or downgrade to an earlier |
| version of SQLite. |
| |
| .. _using-newer-versions-of-pysqlite: |
| |
| Using newer versions of the SQLite DB-API 2.0 driver |
| ---------------------------------------------------- |
| |
| .. versionadded:: 1.1 |
| |
| For versions of Python 2.5 or newer that include ``sqlite3`` in the standard |
| library Django will now use a ``pysqlite2`` interface in preference to |
| ``sqlite3`` if it finds one is available. |
| |
| This provides the ability to upgrade both the DB-API 2.0 interface or SQLite 3 |
| itself to versions newer than the ones included with your particular Python |
| binary distribution, if needed. |
| |
| "Database is locked" errors |
| ----------------------------------------------- |
| |
| SQLite is meant to be a lightweight database, and thus can't support a high |
| level of concurrency. ``OperationalError: database is locked`` errors indicate |
| that your application is experiencing more concurrency than ``sqlite`` can |
| handle in default configuration. This error means that one thread or process has |
| an exclusive lock on the database connection and another thread timed out |
| waiting for the lock the be released. |
| |
| Python's SQLite wrapper has |
| a default timeout value that determines how long the second thread is allowed to |
| wait on the lock before it times out and raises the ``OperationalError: database |
| is locked`` error. |
| |
| If you're getting this error, you can solve it by: |
| |
| * Switching to another database backend. At a certain point SQLite becomes |
| too "lite" for real-world applications, and these sorts of concurrency |
| errors indicate you've reached that point. |
| |
| * Rewriting your code to reduce concurrency and ensure that database |
| transactions are short-lived. |
| |
| * Increase the default timeout value by setting the ``timeout`` database |
| option option:: |
| |
| 'OPTIONS': { |
| # ... |
| 'timeout': 20, |
| # ... |
| } |
| |
| This will simply make SQLite wait a bit longer before throwing "database |
| is locked" errors; it won't really do anything to solve them. |
| |
| .. _oracle-notes: |
| |
| Oracle notes |
| ============ |
| |
| Django supports `Oracle Database Server`_ versions 9i and |
| higher. Oracle version 10g or later is required to use Django's |
| ``regex`` and ``iregex`` query operators. You will also need at least |
| version 4.3.1 of the `cx_Oracle`_ Python driver. |
| |
| Note that due to a Unicode-corruption bug in ``cx_Oracle`` 5.0, that |
| version of the driver should **not** be used with Django; |
| ``cx_Oracle`` 5.0.1 resolved this issue, so if you'd like to use a |
| more recent ``cx_Oracle``, use version 5.0.1. |
| |
| ``cx_Oracle`` 5.0.1 or greater can optionally be compiled with the |
| ``WITH_UNICODE`` environment variable. This is recommended but not |
| required. |
| |
| .. _`Oracle Database Server`: http://www.oracle.com/ |
| .. _`cx_Oracle`: http://cx-oracle.sourceforge.net/ |
| |
| In order for the ``python manage.py syncdb`` command to work, your Oracle |
| database user must have privileges to run the following commands: |
| |
| * CREATE TABLE |
| * CREATE SEQUENCE |
| * CREATE PROCEDURE |
| * CREATE TRIGGER |
| |
| To run Django's test suite, the user needs these *additional* privileges: |
| |
| * CREATE USER |
| * DROP USER |
| * CREATE TABLESPACE |
| * DROP TABLESPACE |
| * CONNECT WITH ADMIN OPTION |
| * RESOURCE WITH ADMIN OPTION |
| |
| Connecting to the database |
| -------------------------- |
| |
| Your Django settings.py file should look something like this for Oracle:: |
| |
| DATABASES = { |
| 'default': { |
| 'ENGINE': 'django.db.backends.oracle', |
| 'NAME': 'xe', |
| 'USER': 'a_user', |
| 'PASSWORD': 'a_password', |
| 'HOST': '', |
| 'PORT': '', |
| } |
| } |
| |
| |
| If you don't use a ``tnsnames.ora`` file or a similar naming method that |
| recognizes the SID ("xe" in this example), then fill in both |
| ``HOST`` and ``PORT`` like so:: |
| |
| DATABASES = { |
| 'default': { |
| 'ENGINE': 'django.db.backends.oracle', |
| 'NAME': 'xe', |
| 'USER': 'a_user', |
| 'PASSWORD': 'a_password', |
| 'HOST': 'dbprod01ned.mycompany.com', |
| 'PORT': '1540', |
| } |
| } |
| |
| You should supply both ``HOST`` and ``PORT``, or leave both |
| as empty strings. |
| |
| Threaded option |
| ---------------- |
| |
| If you plan to run Django in a multithreaded environment (e.g. Apache in Windows |
| using the default MPM module), then you **must** set the ``threaded`` option of |
| your Oracle database configuration to True:: |
| |
| 'OPTIONS': { |
| 'threaded': True, |
| }, |
| |
| Failure to do this may result in crashes and other odd behavior. |
| |
| Tablespace options |
| ------------------ |
| |
| A common paradigm for optimizing performance in Oracle-based systems is the |
| use of `tablespaces`_ to organize disk layout. The Oracle backend supports |
| this use case by adding ``db_tablespace`` options to the ``Meta`` and |
| ``Field`` classes. (When you use a backend that lacks support for tablespaces, |
| Django ignores these options.) |
| |
| .. _`tablespaces`: http://en.wikipedia.org/wiki/Tablespace |
| |
| A tablespace can be specified for the table(s) generated by a model by |
| supplying the ``db_tablespace`` option inside the model's ``class Meta``. |
| Additionally, you can pass the ``db_tablespace`` option to a ``Field`` |
| constructor to specify an alternate tablespace for the ``Field``'s column |
| index. If no index would be created for the column, the ``db_tablespace`` |
| option is ignored:: |
| |
| class TablespaceExample(models.Model): |
| name = models.CharField(max_length=30, db_index=True, db_tablespace="indexes") |
| data = models.CharField(max_length=255, db_index=True) |
| edges = models.ManyToManyField(to="self", db_tablespace="indexes") |
| |
| class Meta: |
| db_tablespace = "tables" |
| |
| In this example, the tables generated by the ``TablespaceExample`` model |
| (i.e., the model table and the many-to-many table) would be stored in the |
| ``tables`` tablespace. The index for the name field and the indexes on the |
| many-to-many table would be stored in the ``indexes`` tablespace. The ``data`` |
| field would also generate an index, but no tablespace for it is specified, so |
| it would be stored in the model tablespace ``tables`` by default. |
| |
| Use the :setting:`DEFAULT_TABLESPACE` and :setting:`DEFAULT_INDEX_TABLESPACE` |
| settings to specify default values for the db_tablespace options. |
| These are useful for setting a tablespace for the built-in Django apps and |
| other applications whose code you cannot control. |
| |
| Django does not create the tablespaces for you. Please refer to `Oracle's |
| documentation`_ for details on creating and managing tablespaces. |
| |
| .. _`Oracle's documentation`: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#SQLRF01403 |
| |
| Naming issues |
| ------------- |
| |
| Oracle imposes a name length limit of 30 characters. To accommodate this, the |
| backend truncates database identifiers to fit, replacing the final four |
| characters of the truncated name with a repeatable MD5 hash value. |
| |
| When running syncdb, an ``ORA-06552`` error may be encountered if |
| certain Oracle keywords are used as the name of a model field or the |
| value of a ``db_column`` option. Django quotes all identifiers used |
| in queries to prevent most such problems, but this error can still |
| occur when an Oracle datatype is used as a column name. In |
| particular, take care to avoid using the names ``date``, |
| ``timestamp``, ``number`` or ``float`` as a field name. |
| |
| NULL and empty strings |
| ---------------------- |
| |
| Django generally prefers to use the empty string ('') rather than |
| NULL, but Oracle treats both identically. To get around this, the |
| Oracle backend coerces the ``null=True`` option on fields that have |
| the empty string as a possible value. When fetching from the database, |
| it is assumed that a NULL value in one of these fields really means |
| the empty string, and the data is silently converted to reflect this |
| assumption. |
| |
| ``TextField`` limitations |
| ------------------------- |
| |
| The Oracle backend stores ``TextFields`` as ``NCLOB`` columns. Oracle imposes |
| some limitations on the usage of such LOB columns in general: |
| |
| * LOB columns may not be used as primary keys. |
| |
| * LOB columns may not be used in indexes. |
| |
| * LOB columns may not be used in a ``SELECT DISTINCT`` list. This means that |
| attempting to use the ``QuerySet.distinct`` method on a model that |
| includes ``TextField`` columns will result in an error when run against |
| Oracle. As a workaround, use the ``QuerySet.defer`` method in conjunction |
| with ``distinct()`` to prevent ``TextField`` columns from being included in |
| the ``SELECT DISTINCT`` list. |
| |
| .. _third-party-notes: |
| |
| Using a 3rd-party database backend |
| ================================== |
| |
| In addition to the officially supported databases, there are backends provided |
| by 3rd parties that allow you to use other databases with Django: |
| |
| * `Sybase SQL Anywhere`_ |
| * `IBM DB2`_ |
| * `Microsoft SQL Server 2005`_ |
| * Firebird_ |
| * ODBC_ |
| |
| The Django versions and ORM features supported by these unofficial backends |
| vary considerably. Queries regarding the specific capabilities of these |
| unofficial backends, along with any support queries, should be directed to |
| the support channels provided by each 3rd party project. |
| |
| .. _Sybase SQL Anywhere: http://code.google.com/p/sqlany-django/ |
| .. _IBM DB2: http://code.google.com/p/ibm-db/ |
| .. _Microsoft SQL Server 2005: http://code.google.com/p/django-mssql/ |
| .. _Firebird: http://code.google.com/p/django-firebird/ |
| .. _ODBC: http://code.google.com/p/django-pyodbc/ |