PageRenderTime 23ms CodeModel.GetById 24ms RepoModel.GetById 0ms app.codeStats 0ms

/django/db/backends/postgresql/operations.py

http://github.com/django/django
Python | 288 lines | 213 code | 35 blank | 40 comment | 34 complexity | 1912a39793ae2098a2d49b3e18eb4756 MD5 | raw file
Possible License(s): BSD-3-Clause, MIT
  1. from psycopg2.extras import Inet
  2. from django.conf import settings
  3. from django.db.backends.base.operations import BaseDatabaseOperations
  4. class DatabaseOperations(BaseDatabaseOperations):
  5. cast_char_field_without_max_length = 'varchar'
  6. explain_prefix = 'EXPLAIN'
  7. cast_data_types = {
  8. 'AutoField': 'integer',
  9. 'BigAutoField': 'bigint',
  10. 'SmallAutoField': 'smallint',
  11. }
  12. def unification_cast_sql(self, output_field):
  13. internal_type = output_field.get_internal_type()
  14. if internal_type in ("GenericIPAddressField", "IPAddressField", "TimeField", "UUIDField"):
  15. # PostgreSQL will resolve a union as type 'text' if input types are
  16. # 'unknown'.
  17. # https://www.postgresql.org/docs/current/typeconv-union-case.html
  18. # These fields cannot be implicitly cast back in the default
  19. # PostgreSQL configuration so we need to explicitly cast them.
  20. # We must also remove components of the type within brackets:
  21. # varchar(255) -> varchar.
  22. return 'CAST(%%s AS %s)' % output_field.db_type(self.connection).split('(')[0]
  23. return '%s'
  24. def date_extract_sql(self, lookup_type, field_name):
  25. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
  26. if lookup_type == 'week_day':
  27. # For consistency across backends, we return Sunday=1, Saturday=7.
  28. return "EXTRACT('dow' FROM %s) + 1" % field_name
  29. elif lookup_type == 'iso_week_day':
  30. return "EXTRACT('isodow' FROM %s)" % field_name
  31. elif lookup_type == 'iso_year':
  32. return "EXTRACT('isoyear' FROM %s)" % field_name
  33. else:
  34. return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
  35. def date_trunc_sql(self, lookup_type, field_name):
  36. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
  37. return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
  38. def _prepare_tzname_delta(self, tzname):
  39. if '+' in tzname:
  40. return tzname.replace('+', '-')
  41. elif '-' in tzname:
  42. return tzname.replace('-', '+')
  43. return tzname
  44. def _convert_field_to_tz(self, field_name, tzname):
  45. if settings.USE_TZ:
  46. field_name = "%s AT TIME ZONE '%s'" % (field_name, self._prepare_tzname_delta(tzname))
  47. return field_name
  48. def datetime_cast_date_sql(self, field_name, tzname):
  49. field_name = self._convert_field_to_tz(field_name, tzname)
  50. return '(%s)::date' % field_name
  51. def datetime_cast_time_sql(self, field_name, tzname):
  52. field_name = self._convert_field_to_tz(field_name, tzname)
  53. return '(%s)::time' % field_name
  54. def datetime_extract_sql(self, lookup_type, field_name, tzname):
  55. field_name = self._convert_field_to_tz(field_name, tzname)
  56. return self.date_extract_sql(lookup_type, field_name)
  57. def datetime_trunc_sql(self, lookup_type, field_name, tzname):
  58. field_name = self._convert_field_to_tz(field_name, tzname)
  59. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
  60. return "DATE_TRUNC('%s', %s)" % (lookup_type, field_name)
  61. def time_trunc_sql(self, lookup_type, field_name):
  62. return "DATE_TRUNC('%s', %s)::time" % (lookup_type, field_name)
  63. def json_cast_text_sql(self, field_name):
  64. return '(%s)::text' % field_name
  65. def deferrable_sql(self):
  66. return " DEFERRABLE INITIALLY DEFERRED"
  67. def fetch_returned_insert_rows(self, cursor):
  68. """
  69. Given a cursor object that has just performed an INSERT...RETURNING
  70. statement into a table, return the tuple of returned data.
  71. """
  72. return cursor.fetchall()
  73. def lookup_cast(self, lookup_type, internal_type=None):
  74. lookup = '%s'
  75. # Cast text lookups to text to allow things like filter(x__contains=4)
  76. if lookup_type in ('iexact', 'contains', 'icontains', 'startswith',
  77. 'istartswith', 'endswith', 'iendswith', 'regex', 'iregex'):
  78. if internal_type in ('IPAddressField', 'GenericIPAddressField'):
  79. lookup = "HOST(%s)"
  80. elif internal_type in ('CICharField', 'CIEmailField', 'CITextField'):
  81. lookup = '%s::citext'
  82. else:
  83. lookup = "%s::text"
  84. # Use UPPER(x) for case-insensitive lookups; it's faster.
  85. if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'):
  86. lookup = 'UPPER(%s)' % lookup
  87. return lookup
  88. def no_limit_value(self):
  89. return None
  90. def prepare_sql_script(self, sql):
  91. return [sql]
  92. def quote_name(self, name):
  93. if name.startswith('"') and name.endswith('"'):
  94. return name # Quoting once is enough.
  95. return '"%s"' % name
  96. def set_time_zone_sql(self):
  97. return "SET TIME ZONE %s"
  98. def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
  99. if not tables:
  100. return []
  101. # Perform a single SQL 'TRUNCATE x, y, z...;' statement. It allows us
  102. # to truncate tables referenced by a foreign key in any other table.
  103. sql_parts = [
  104. style.SQL_KEYWORD('TRUNCATE'),
  105. ', '.join(style.SQL_FIELD(self.quote_name(table)) for table in tables),
  106. ]
  107. if reset_sequences:
  108. sql_parts.append(style.SQL_KEYWORD('RESTART IDENTITY'))
  109. if allow_cascade:
  110. sql_parts.append(style.SQL_KEYWORD('CASCADE'))
  111. return ['%s;' % ' '.join(sql_parts)]
  112. def sequence_reset_by_name_sql(self, style, sequences):
  113. # 'ALTER SEQUENCE sequence_name RESTART WITH 1;'... style SQL statements
  114. # to reset sequence indices
  115. sql = []
  116. for sequence_info in sequences:
  117. table_name = sequence_info['table']
  118. # 'id' will be the case if it's an m2m using an autogenerated
  119. # intermediate table (see BaseDatabaseIntrospection.sequence_list).
  120. column_name = sequence_info['column'] or 'id'
  121. sql.append("%s setval(pg_get_serial_sequence('%s','%s'), 1, false);" % (
  122. style.SQL_KEYWORD('SELECT'),
  123. style.SQL_TABLE(self.quote_name(table_name)),
  124. style.SQL_FIELD(column_name),
  125. ))
  126. return sql
  127. def tablespace_sql(self, tablespace, inline=False):
  128. if inline:
  129. return "USING INDEX TABLESPACE %s" % self.quote_name(tablespace)
  130. else:
  131. return "TABLESPACE %s" % self.quote_name(tablespace)
  132. def sequence_reset_sql(self, style, model_list):
  133. from django.db import models
  134. output = []
  135. qn = self.quote_name
  136. for model in model_list:
  137. # Use `coalesce` to set the sequence for each model to the max pk value if there are records,
  138. # or 1 if there are none. Set the `is_called` property (the third argument to `setval`) to true
  139. # if there are records (as the max pk value is already in use), otherwise set it to false.
  140. # Use pg_get_serial_sequence to get the underlying sequence name from the table name
  141. # and column name (available since PostgreSQL 8)
  142. for f in model._meta.local_fields:
  143. if isinstance(f, models.AutoField):
  144. output.append(
  145. "%s setval(pg_get_serial_sequence('%s','%s'), "
  146. "coalesce(max(%s), 1), max(%s) %s null) %s %s;" % (
  147. style.SQL_KEYWORD('SELECT'),
  148. style.SQL_TABLE(qn(model._meta.db_table)),
  149. style.SQL_FIELD(f.column),
  150. style.SQL_FIELD(qn(f.column)),
  151. style.SQL_FIELD(qn(f.column)),
  152. style.SQL_KEYWORD('IS NOT'),
  153. style.SQL_KEYWORD('FROM'),
  154. style.SQL_TABLE(qn(model._meta.db_table)),
  155. )
  156. )
  157. break # Only one AutoField is allowed per model, so don't bother continuing.
  158. for f in model._meta.many_to_many:
  159. if not f.remote_field.through:
  160. output.append(
  161. "%s setval(pg_get_serial_sequence('%s','%s'), "
  162. "coalesce(max(%s), 1), max(%s) %s null) %s %s;" % (
  163. style.SQL_KEYWORD('SELECT'),
  164. style.SQL_TABLE(qn(f.m2m_db_table())),
  165. style.SQL_FIELD('id'),
  166. style.SQL_FIELD(qn('id')),
  167. style.SQL_FIELD(qn('id')),
  168. style.SQL_KEYWORD('IS NOT'),
  169. style.SQL_KEYWORD('FROM'),
  170. style.SQL_TABLE(qn(f.m2m_db_table()))
  171. )
  172. )
  173. return output
  174. def prep_for_iexact_query(self, x):
  175. return x
  176. def max_name_length(self):
  177. """
  178. Return the maximum length of an identifier.
  179. The maximum length of an identifier is 63 by default, but can be
  180. changed by recompiling PostgreSQL after editing the NAMEDATALEN
  181. macro in src/include/pg_config_manual.h.
  182. This implementation returns 63, but can be overridden by a custom
  183. database backend that inherits most of its behavior from this one.
  184. """
  185. return 63
  186. def distinct_sql(self, fields, params):
  187. if fields:
  188. params = [param for param_list in params for param in param_list]
  189. return (['DISTINCT ON (%s)' % ', '.join(fields)], params)
  190. else:
  191. return ['DISTINCT'], []
  192. def last_executed_query(self, cursor, sql, params):
  193. # https://www.psycopg.org/docs/cursor.html#cursor.query
  194. # The query attribute is a Psycopg extension to the DB API 2.0.
  195. if cursor.query is not None:
  196. return cursor.query.decode()
  197. return None
  198. def return_insert_columns(self, fields):
  199. if not fields:
  200. return '', ()
  201. columns = [
  202. '%s.%s' % (
  203. self.quote_name(field.model._meta.db_table),
  204. self.quote_name(field.column),
  205. ) for field in fields
  206. ]
  207. return 'RETURNING %s' % ', '.join(columns), ()
  208. def bulk_insert_sql(self, fields, placeholder_rows):
  209. placeholder_rows_sql = (", ".join(row) for row in placeholder_rows)
  210. values_sql = ", ".join("(%s)" % sql for sql in placeholder_rows_sql)
  211. return "VALUES " + values_sql
  212. def adapt_datefield_value(self, value):
  213. return value
  214. def adapt_datetimefield_value(self, value):
  215. return value
  216. def adapt_timefield_value(self, value):
  217. return value
  218. def adapt_ipaddressfield_value(self, value):
  219. if value:
  220. return Inet(value)
  221. return None
  222. def subtract_temporals(self, internal_type, lhs, rhs):
  223. if internal_type == 'DateField':
  224. lhs_sql, lhs_params = lhs
  225. rhs_sql, rhs_params = rhs
  226. params = (*lhs_params, *rhs_params)
  227. return "(interval '1 day' * (%s - %s))" % (lhs_sql, rhs_sql), params
  228. return super().subtract_temporals(internal_type, lhs, rhs)
  229. def explain_query_prefix(self, format=None, **options):
  230. prefix = super().explain_query_prefix(format)
  231. extra = {}
  232. if format:
  233. extra['FORMAT'] = format
  234. if options:
  235. extra.update({
  236. name.upper(): 'true' if value else 'false'
  237. for name, value in options.items()
  238. })
  239. if extra:
  240. prefix += ' (%s)' % ', '.join('%s %s' % i for i in extra.items())
  241. return prefix
  242. def ignore_conflicts_suffix_sql(self, ignore_conflicts=None):
  243. return 'ON CONFLICT DO NOTHING' if ignore_conflicts else super().ignore_conflicts_suffix_sql(ignore_conflicts)