python/lib/django_1_4/django/db/backends/postgresql_psycopg2/operations.py

   1 from django.db.backends import BaseDatabaseOperations
   2
   3
   4 class DatabaseOperations(BaseDatabaseOperations):
   5     def __init__(self, connection):
   6         super(DatabaseOperations, self).__init__(connection)
   7
   8     def date_extract_sql(self, lookup_type, field_name):
   9         # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
  10         if lookup_type == 'week_day':
  11             # For consistency across backends, we return Sunday=1, Saturday=7.
  12             return "EXTRACT('dow' FROM %s) + 1" % field_name
  13         else:
  14             return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
  15
  16     def date_interval_sql(self, sql, connector, timedelta):
  17         """
  18         implements the interval functionality for expressions
  19         format for Postgres:
  20             (datefield + interval '3 days 200 seconds 5 microseconds')
  21         """
  22         modifiers = []
  23         if timedelta.days:
  24             modifiers.append(u'%s days' % timedelta.days)
  25         if timedelta.seconds:
  26             modifiers.append(u'%s seconds' % timedelta.seconds)
  27         if timedelta.microseconds:
  28             modifiers.append(u'%s microseconds' % timedelta.microseconds)
  29         mods = u' '.join(modifiers)
  30         conn = u' %s ' % connector
  31         return u'(%s)' % conn.join([sql, u'interval \'%s\'' % mods])
  32
  33     def date_trunc_sql(self, lookup_type, field_name):
  34         # http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
  35         return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
  36
  37     def deferrable_sql(self):
  38         return " DEFERRABLE INITIALLY DEFERRED"
  39
  40     def lookup_cast(self, lookup_type):
  41         lookup = '%s'
  42
  43         # Cast text lookups to text to allow things like filter(x__contains=4)
  44         if lookup_type in ('iexact', 'contains', 'icontains', 'startswith',
  45                            'istartswith', 'endswith', 'iendswith'):
  46             lookup = "%s::text"
  47
  48         # Use UPPER(x) for case-insensitive lookups; it's faster.
  49         if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'):
  50             lookup = 'UPPER(%s)' % lookup
  51
  52         return lookup
  53
  54     def field_cast_sql(self, db_type):
  55         if db_type == 'inet':
  56             return 'HOST(%s)'
  57         return '%s'
  58
  59     def last_insert_id(self, cursor, table_name, pk_name):
  60         # Use pg_get_serial_sequence to get the underlying sequence name
  61         # from the table name and column name (available since PostgreSQL 8)
  62         cursor.execute("SELECT CURRVAL(pg_get_serial_sequence('%s','%s'))" % (
  63             self.quote_name(table_name), pk_name))
  64         return cursor.fetchone()[0]
  65
  66     def no_limit_value(self):
  67         return None
  68
  69     def quote_name(self, name):
  70         if name.startswith('"') and name.endswith('"'):
  71             return name # Quoting once is enough.
  72         return '"%s"' % name
  73
  74     def set_time_zone_sql(self):
  75         return "SET TIME ZONE %s"
  76
  77     def sql_flush(self, style, tables, sequences):
  78         if tables:
  79             # Perform a single SQL 'TRUNCATE x, y, z...;' statement.  It allows
  80             # us to truncate tables referenced by a foreign key in any other
  81             # table.
  82             sql = ['%s %s;' % \
  83                 (style.SQL_KEYWORD('TRUNCATE'),
  84                     style.SQL_FIELD(', '.join([self.quote_name(table) for table in tables]))
  85             )]
  86
  87             # 'ALTER SEQUENCE sequence_name RESTART WITH 1;'... style SQL statements
  88             # to reset sequence indices
  89             for sequence_info in sequences:
  90                 table_name = sequence_info['table']
  91                 column_name = sequence_info['column']
  92                 if not (column_name and len(column_name) > 0):
  93                     # This will be the case if it's an m2m using an autogenerated
  94                     # intermediate table (see BaseDatabaseIntrospection.sequence_list)
  95                     column_name = 'id'
  96                 sql.append("%s setval(pg_get_serial_sequence('%s','%s'), 1, false);" % \
  97                     (style.SQL_KEYWORD('SELECT'),
  98                     style.SQL_TABLE(self.quote_name(table_name)),
  99                     style.SQL_FIELD(column_name))
 100                 )
 101             return sql
 102         else:
 103             return []
 104
 105     def tablespace_sql(self, tablespace, inline=False):
 106         if inline:
 107             return "USING INDEX TABLESPACE %s" % self.quote_name(tablespace)
 108         else:
 109             return "TABLESPACE %s" % self.quote_name(tablespace)
 110
 111     def sequence_reset_sql(self, style, model_list):
 112         from django.db import models
 113         output = []
 114         qn = self.quote_name
 115         for model in model_list:
 116             # Use `coalesce` to set the sequence for each model to the max pk value if there are records,
 117             # or 1 if there are none. Set the `is_called` property (the third argument to `setval`) to true
 118             # if there are records (as the max pk value is already in use), otherwise set it to false.
 119             # Use pg_get_serial_sequence to get the underlying sequence name from the table name
 120             # and column name (available since PostgreSQL 8)
 121
 122             for f in model._meta.local_fields:
 123                 if isinstance(f, models.AutoField):
 124                     output.append("%s setval(pg_get_serial_sequence('%s','%s'), coalesce(max(%s), 1), max(%s) %s null) %s %s;" % \
 125                         (style.SQL_KEYWORD('SELECT'),
 126                         style.SQL_TABLE(qn(model._meta.db_table)),
 127                         style.SQL_FIELD(f.column),
 128                         style.SQL_FIELD(qn(f.column)),
 129                         style.SQL_FIELD(qn(f.column)),
 130                         style.SQL_KEYWORD('IS NOT'),
 131                         style.SQL_KEYWORD('FROM'),
 132                         style.SQL_TABLE(qn(model._meta.db_table))))
 133                     break # Only one AutoField is allowed per model, so don't bother continuing.
 134             for f in model._meta.many_to_many:
 135                 if not f.rel.through:
 136                     output.append("%s setval(pg_get_serial_sequence('%s','%s'), coalesce(max(%s), 1), max(%s) %s null) %s %s;" % \
 137                         (style.SQL_KEYWORD('SELECT'),
 138                         style.SQL_TABLE(qn(f.m2m_db_table())),
 139                         style.SQL_FIELD('id'),
 140                         style.SQL_FIELD(qn('id')),
 141                         style.SQL_FIELD(qn('id')),
 142                         style.SQL_KEYWORD('IS NOT'),
 143                         style.SQL_KEYWORD('FROM'),
 144                         style.SQL_TABLE(qn(f.m2m_db_table()))))
 145         return output
 146
 147     def savepoint_create_sql(self, sid):
 148         return "SAVEPOINT %s" % sid
 149
 150     def savepoint_commit_sql(self, sid):
 151         return "RELEASE SAVEPOINT %s" % sid
 152
 153     def savepoint_rollback_sql(self, sid):
 154         return "ROLLBACK TO SAVEPOINT %s" % sid
 155
 156     def prep_for_iexact_query(self, x):
 157         return x
 158
 159     def check_aggregate_support(self, aggregate):
 160         """Check that the backend fully supports the provided aggregate.
 161
 162         The implementation of population statistics (STDDEV_POP and VAR_POP)
 163         under Postgres 8.2 - 8.2.4 is known to be faulty. Raise
 164         NotImplementedError if this is the database in use.
 165         """
 166         if aggregate.sql_function in ('STDDEV_POP', 'VAR_POP'):
 167             pg_version = self.connection.pg_version
 168             if pg_version >= 80200 and pg_version <= 80204:
 169                 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)
 170
 171     def max_name_length(self):
 172         """
 173         Returns the maximum length of an identifier.
 174
 175         Note that the maximum length of an identifier is 63 by default, but can
 176         be changed by recompiling PostgreSQL after editing the NAMEDATALEN
 177         macro in src/include/pg_config_manual.h .
 178
 179         This implementation simply returns 63, but can easily be overridden by a
 180         custom database backend that inherits most of its behavior from this one.
 181         """
 182
 183         return 63
 184
 185     def distinct_sql(self, fields):
 186         if fields:
 187             return 'DISTINCT ON (%s)' % ', '.join(fields)
 188         else:
 189             return 'DISTINCT'
 190
 191     def last_executed_query(self, cursor, sql, params):
 192         # http://initd.org/psycopg/docs/cursor.html#cursor.query
 193         # The query attribute is a Psycopg extension to the DB API 2.0.
 194         return cursor.query
 195
 196     def return_insert_id(self):
 197         return "RETURNING %s", ()
 198
 199     def bulk_insert_sql(self, fields, num_values):
 200         items_sql = "(%s)" % ", ".join(["%s"] * len(fields))
 201         return "VALUES " + ", ".join([items_sql] * num_values)