1 import re

     2 

     3 from django.db.backends import BaseDatabaseOperations

     4 

     5 # This DatabaseOperations class lives in here instead of base.py because it's

     6 # used by both the 'postgresql' and 'postgresql_psycopg2' backends.

     7 

     8 class DatabaseOperations(BaseDatabaseOperations):

     9     def __init__(self, connection):

    10         super(DatabaseOperations, self).__init__()

    11         self._postgres_version = None

    12         self.connection = connection

    13 

    14     def _get_postgres_version(self):

    15         if self._postgres_version is None:

    16             from django.db.backends.postgresql.version import get_version

    17             cursor = self.connection.cursor()

    18             self._postgres_version = get_version(cursor)

    19         return self._postgres_version

    20     postgres_version = property(_get_postgres_version)

    21 

    22     def date_extract_sql(self, lookup_type, field_name):

    23         # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

    24         if lookup_type == 'week_day':

    25             # For consistency across backends, we return Sunday=1, Saturday=7.

    26             return "EXTRACT('dow' FROM %s) + 1" % field_name

    27         else:

    28             return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)

    29 

    30     def date_trunc_sql(self, lookup_type, field_name):

    31         # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

    32         return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)

    33 

    34     def deferrable_sql(self):

    35         return " DEFERRABLE INITIALLY DEFERRED"

    36 

    37     def lookup_cast(self, lookup_type):

    38         lookup = '%s'

    39 

    40         # Cast text lookups to text to allow things like filter(x__contains=4)

    41         if lookup_type in ('iexact', 'contains', 'icontains', 'startswith',

    42                            'istartswith', 'endswith', 'iendswith'):

    43             lookup = "%s::text"

    44 

    45         # Use UPPER(x) for case-insensitive lookups; it's faster.

    46         if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'):

    47             lookup = 'UPPER(%s)' % lookup

    48 

    49         return lookup

    50 

    51     def field_cast_sql(self, db_type):

    52         if db_type == 'inet':

    53             return 'HOST(%s)'

    54         return '%s'

    55 

    56     def last_insert_id(self, cursor, table_name, pk_name):

    57         cursor.execute("SELECT CURRVAL('\"%s_%s_seq\"')" % (table_name, pk_name))

    58         return cursor.fetchone()[0]

    59 

    60     def no_limit_value(self):

    61         return None

    62 

    63     def quote_name(self, name):

    64         if name.startswith('"') and name.endswith('"'):

    65             return name # Quoting once is enough.

    66         return '"%s"' % name

    67 

    68     def sql_flush(self, style, tables, sequences):

    69         if tables:

    70             if self.postgres_version[0:2] >= (8,1):

    71                 # Postgres 8.1+ can do 'TRUNCATE x, y, z...;'. In fact, it *has to*

    72                 # in order to be able to truncate tables referenced by a foreign

    73                 # key in any other table. The result is a single SQL TRUNCATE

    74                 # statement.

    75                 sql = ['%s %s;' % \

    76                     (style.SQL_KEYWORD('TRUNCATE'),

    77                      style.SQL_FIELD(', '.join([self.quote_name(table) for table in tables]))

    78                 )]

    79             else:

    80                 # Older versions of Postgres can't do TRUNCATE in a single call, so

    81                 # they must use a simple delete.

    82                 sql = ['%s %s %s;' % \

    83                         (style.SQL_KEYWORD('DELETE'),

    84                          style.SQL_KEYWORD('FROM'),

    85                          style.SQL_FIELD(self.quote_name(table))

    86                          ) for table in tables]

    87 

    88             # 'ALTER SEQUENCE sequence_name RESTART WITH 1;'... style SQL statements

    89             # to reset sequence indices

    90             for sequence_info in sequences:

    91                 table_name = sequence_info['table']

    92                 column_name = sequence_info['column']

    93                 if column_name and len(column_name) > 0:

    94                     sequence_name = '%s_%s_seq' % (table_name, column_name)

    95                 else:

    96                     sequence_name = '%s_id_seq' % table_name

    97                 sql.append("%s setval('%s', 1, false);" % \

    98                     (style.SQL_KEYWORD('SELECT'),

    99                     style.SQL_FIELD(self.quote_name(sequence_name)))

   100                 )

   101             return sql

   102         else:

   103             return []

   104 

   105     def sequence_reset_sql(self, style, model_list):

   106         from django.db import models

   107         output = []

   108         qn = self.quote_name

   109         for model in model_list:

   110             # Use `coalesce` to set the sequence for each model to the max pk value if there are records,

   111             # or 1 if there are none. Set the `is_called` property (the third argument to `setval`) to true

   112             # if there are records (as the max pk value is already in use), otherwise set it to false.

   113             for f in model._meta.local_fields:

   114                 if isinstance(f, models.AutoField):

   115                     output.append("%s setval('%s', coalesce(max(%s), 1), max(%s) %s null) %s %s;" % \

   116                         (style.SQL_KEYWORD('SELECT'),

   117                         style.SQL_FIELD(qn('%s_%s_seq' % (model._meta.db_table, f.column))),

   118                         style.SQL_FIELD(qn(f.column)),

   119                         style.SQL_FIELD(qn(f.column)),

   120                         style.SQL_KEYWORD('IS NOT'),

   121                         style.SQL_KEYWORD('FROM'),

   122                         style.SQL_TABLE(qn(model._meta.db_table))))

   123                     break # Only one AutoField is allowed per model, so don't bother continuing.

   124             for f in model._meta.many_to_many:

   125                 if not f.rel.through:

   126                     output.append("%s setval('%s', coalesce(max(%s), 1), max(%s) %s null) %s %s;" % \

   127                         (style.SQL_KEYWORD('SELECT'),

   128                         style.SQL_FIELD(qn('%s_id_seq' % f.m2m_db_table())),

   129                         style.SQL_FIELD(qn('id')),

   130                         style.SQL_FIELD(qn('id')),

   131                         style.SQL_KEYWORD('IS NOT'),

   132                         style.SQL_KEYWORD('FROM'),

   133                         style.SQL_TABLE(qn(f.m2m_db_table()))))

   134         return output

   135 

   136     def savepoint_create_sql(self, sid):

   137         return "SAVEPOINT %s" % sid

   138 

   139     def savepoint_commit_sql(self, sid):

   140         return "RELEASE SAVEPOINT %s" % sid

   141 

   142     def savepoint_rollback_sql(self, sid):

   143         return "ROLLBACK TO SAVEPOINT %s" % sid

   144 

   145     def prep_for_iexact_query(self, x):

   146         return x

   147 

   148     def check_aggregate_support(self, aggregate):

   149         """Check that the backend fully supports the provided aggregate.

   150 

   151         The population and sample statistics (STDDEV_POP, STDDEV_SAMP,

   152         VAR_POP, VAR_SAMP) were first implemented in Postgres 8.2.

   153 

   154         The implementation of population statistics (STDDEV_POP and VAR_POP)

   155         under Postgres 8.2 - 8.2.4 is known to be faulty. Raise

   156         NotImplementedError if this is the database in use.

   157         """

   158         if aggregate.sql_function in ('STDDEV_POP', 'STDDEV_SAMP', 'VAR_POP', 'VAR_SAMP'):

   159             if self.postgres_version[0:2] < (8,2):

   160                 raise NotImplementedError('PostgreSQL does not support %s prior to version 8.2. Please upgrade your version of PostgreSQL.' % aggregate.sql_function)

   161 

   162         if aggregate.sql_function in ('STDDEV_POP', 'VAR_POP'):

   163             if self.postgres_version[0:2] == (8,2):

   164                 if self.postgres_version[2] is None or self.postgres_version[2] <= 4:

   165                     raise NotImplementedError('PostgreSQL 8.2 to 8.2.4 is known to have a faulty implementation of %s. Please upgrade your version of PostgreSQL.' % aggregate.sql_function)

   166 

   167     def max_name_length(self):

   168         """

   169         Returns the maximum length of an identifier.

   170 

   171         Note that the maximum length of an identifier is 63 by default, but can

   172         be changed by recompiling PostgreSQL after editing the NAMEDATALEN

   173         macro in src/include/pg_config_manual.h .

   174 

   175         This implementation simply returns 63, but can easily be overridden by a

   176         custom database backend that inherits most of its behavior from this one.

   177         """

   178 

   179         return 63