Debian Codegraph @

django/db/backends/postgresql/schema.py

raw
1 from django.db.backends.base.schema import BaseDatabaseSchemaEditor
2 from django.db.backends.ddl_references import IndexColumns
3 from django.db.backends.postgresql.psycopg_any import sql
4 from django.db.backends.utils import strip_quotes
5
6
7 class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
8 # Setting all constraints to IMMEDIATE to allow changing data in the same
9 # transaction.
10 sql_update_with_default = (
11 "UPDATE %(table)s SET %(column)s = %(default)s WHERE %(column)s IS NULL"
12 "; SET CONSTRAINTS ALL IMMEDIATE"
13 )
14 sql_alter_sequence_type = "ALTER SEQUENCE IF EXISTS %(sequence)s AS %(type)s"
15 sql_delete_sequence = "DROP SEQUENCE IF EXISTS %(sequence)s CASCADE"
16
17 sql_create_index = (
18 "CREATE INDEX %(name)s ON %(table)s%(using)s "
19 "(%(columns)s)%(include)s%(extra)s%(condition)s"
20 )
21 sql_create_index_concurrently = (
22 "CREATE INDEX CONCURRENTLY %(name)s ON %(table)s%(using)s "
23 "(%(columns)s)%(include)s%(extra)s%(condition)s"
24 )
25 sql_delete_index = "DROP INDEX IF EXISTS %(name)s"
26 sql_delete_index_concurrently = "DROP INDEX CONCURRENTLY IF EXISTS %(name)s"
27
28 # Setting the constraint to IMMEDIATE to allow changing data in the same
29 # transaction.
30 sql_create_column_inline_fk = (
31 "CONSTRAINT %(name)s REFERENCES %(to_table)s(%(to_column)s)%(deferrable)s"
32 "; SET CONSTRAINTS %(namespace)s%(name)s IMMEDIATE"
33 )
34 # Setting the constraint to IMMEDIATE runs any deferred checks to allow
35 # dropping it in the same transaction.
36 sql_delete_fk = (
37 "SET CONSTRAINTS %(name)s IMMEDIATE; "
38 "ALTER TABLE %(table)s DROP CONSTRAINT %(name)s"
39 )
40 sql_delete_procedure = "DROP FUNCTION %(procedure)s(%(param_types)s)"
41
42 def execute(self, sql, params=()):
43 # Merge the query client-side, as PostgreSQL won't do it server-side.
44 if params is None:
45 return super().execute(sql, params)
46 sql = self.connection.ops.compose_sql(str(sql), params)
47 # Don't let the superclass touch anything.
48 return super().execute(sql, None)
49
50 sql_add_identity = (
51 "ALTER TABLE %(table)s ALTER COLUMN %(column)s ADD "
52 "GENERATED BY DEFAULT AS IDENTITY"
53 )
54 sql_drop_indentity = (
55 "ALTER TABLE %(table)s ALTER COLUMN %(column)s DROP IDENTITY IF EXISTS"
56 )
57
58 def quote_value(self, value):
59 return sql.quote(value, self.connection.connection)
60
61 def _field_indexes_sql(self, model, field):
62 output = super()._field_indexes_sql(model, field)
63 like_index_statement = self._create_like_index_sql(model, field)
64 if like_index_statement is not None:
65 output.append(like_index_statement)
66 return output
67
68 def _field_data_type(self, field):
69 if field.is_relation:
70 return field.rel_db_type(self.connection)
71 return self.connection.data_types.get(
72 field.get_internal_type(),
73 field.db_type(self.connection),
74 )
75
76 def _field_base_data_types(self, field):
77 # Yield base data types for array fields.
78 if field.base_field.get_internal_type() == "ArrayField":
79 yield from self._field_base_data_types(field.base_field)
80 else:
81 yield self._field_data_type(field.base_field)
82
83 def _create_like_index_sql(self, model, field):
84 """
85 Return the statement to create an index with varchar operator pattern
86 when the column type is 'varchar' or 'text', otherwise return None.
87 """
88 db_type = field.db_type(connection=self.connection)
89 if db_type is not None and (field.db_index or field.unique):
90 # Fields with database column types of `varchar` and `text` need
91 # a second index that specifies their operator class, which is
92 # needed when performing correct LIKE queries outside the
93 # C locale. See #12234.
94 #
95 # The same doesn't apply to array fields such as varchar[size]
96 # and text[size], so skip them.
97 if "[" in db_type:
98 return None
99 # Non-deterministic collations on Postgresql don't support indexes
100 # for operator classes varchar_pattern_ops/text_pattern_ops.
101 collation_name = getattr(field, "db_collation", None)
102 if not collation_name and field.is_relation:
103 collation_name = getattr(field.target_field, "db_collation", None)
104 if collation_name and not self._is_collation_deterministic(collation_name):
105 return None
106 if db_type.startswith("varchar"):
107 return self._create_index_sql(
108 model,
109 fields=[field],
110 suffix="_like",
111 opclasses=["varchar_pattern_ops"],
112 )
113 elif db_type.startswith("text"):
114 return self._create_index_sql(
115 model,
116 fields=[field],
117 suffix="_like",
118 opclasses=["text_pattern_ops"],
119 )
120 return None
121
122 def _using_sql(self, new_field, old_field):
123 if new_field.generated:
124 return ""
125 using_sql = " USING %(column)s::%(type)s"
126 new_internal_type = new_field.get_internal_type()
127 old_internal_type = old_field.get_internal_type()
128 if new_internal_type == "ArrayField" and new_internal_type == old_internal_type:
129 # Compare base data types for array fields.
130 if list(self._field_base_data_types(old_field)) != list(
131 self._field_base_data_types(new_field)
132 ):
133 return using_sql
134 elif self._field_data_type(old_field) != self._field_data_type(new_field):
135 return using_sql
136 return ""
137
138 def _get_sequence_name(self, table, column):
139 with self.connection.cursor() as cursor:
140 for sequence in self.connection.introspection.get_sequences(cursor, table):
141 if sequence["column"] == column:
142 return sequence["name"]
143 return None
144
145 def _is_changing_type_of_indexed_text_column(self, old_field, old_type, new_type):
146 return (old_field.db_index or old_field.unique) and (
147 (old_type.startswith("varchar") and not new_type.startswith("varchar"))
148 or (old_type.startswith("text") and not new_type.startswith("text"))
149 or (old_type.startswith("citext") and not new_type.startswith("citext"))
150 )
151
152 def _alter_column_type_sql(
153 self, model, old_field, new_field, new_type, old_collation, new_collation
154 ):
155 # Drop indexes on varchar/text/citext columns that are changing to a
156 # different type.
157 old_db_params = old_field.db_parameters(connection=self.connection)
158 old_type = old_db_params["type"]
159 if self._is_changing_type_of_indexed_text_column(old_field, old_type, new_type):
160 index_name = self._create_index_name(
161 model._meta.db_table, [old_field.column], suffix="_like"
162 )
163 self.execute(self._delete_index_sql(model, index_name))
164
165 self.sql_alter_column_type = (
166 "ALTER COLUMN %(column)s TYPE %(type)s%(collation)s"
167 )
168 # Cast when data type changed.
169 if using_sql := self._using_sql(new_field, old_field):
170 self.sql_alter_column_type += using_sql
171 new_internal_type = new_field.get_internal_type()
172 old_internal_type = old_field.get_internal_type()
173 # Make ALTER TYPE with IDENTITY make sense.
174 table = strip_quotes(model._meta.db_table)
175 auto_field_types = {
176 "AutoField",
177 "BigAutoField",
178 "SmallAutoField",
179 }
180 old_is_auto = old_internal_type in auto_field_types
181 new_is_auto = new_internal_type in auto_field_types
182 if new_is_auto and not old_is_auto:
183 column = strip_quotes(new_field.column)
184 return (
185 (
186 self.sql_alter_column_type
187 % {
188 "column": self.quote_name(column),
189 "type": new_type,
190 "collation": "",
191 },
192 [],
193 ),
194 [
195 (
196 self.sql_add_identity
197 % {
198 "table": self.quote_name(table),
199 "column": self.quote_name(column),
200 },
201 [],
202 ),
203 ],
204 )
205 elif old_is_auto and not new_is_auto:
206 # Drop IDENTITY if exists (pre-Django 4.1 serial columns don't have
207 # it).
208 self.execute(
209 self.sql_drop_indentity
210 % {
211 "table": self.quote_name(table),
212 "column": self.quote_name(strip_quotes(new_field.column)),
213 }
214 )
215 column = strip_quotes(new_field.column)
216 fragment, _ = super()._alter_column_type_sql(
217 model, old_field, new_field, new_type, old_collation, new_collation
218 )
219 # Drop the sequence if exists (Django 4.1+ identity columns don't
220 # have it).
221 other_actions = []
222 if sequence_name := self._get_sequence_name(table, column):
223 other_actions = [
224 (
225 self.sql_delete_sequence
226 % {
227 "sequence": self.quote_name(sequence_name),
228 },
229 [],
230 )
231 ]
232 return fragment, other_actions
233 elif new_is_auto and old_is_auto and old_internal_type != new_internal_type:
234 fragment, _ = super()._alter_column_type_sql(
235 model, old_field, new_field, new_type, old_collation, new_collation
236 )
237 column = strip_quotes(new_field.column)
238 db_types = {
239 "AutoField": "integer",
240 "BigAutoField": "bigint",
241 "SmallAutoField": "smallint",
242 }
243 # Alter the sequence type if exists (Django 4.1+ identity columns
244 # don't have it).
245 other_actions = []
246 if sequence_name := self._get_sequence_name(table, column):
247 other_actions = [
248 (
249 self.sql_alter_sequence_type
250 % {
251 "sequence": self.quote_name(sequence_name),
252 "type": db_types[new_internal_type],
253 },
254 [],
255 ),
256 ]
257 return fragment, other_actions
258 else:
259 return super()._alter_column_type_sql(
260 model, old_field, new_field, new_type, old_collation, new_collation
261 )
262
263 def _alter_field(
264 self,
265 model,
266 old_field,
267 new_field,
268 old_type,
269 new_type,
270 old_db_params,
271 new_db_params,
272 strict=False,
273 ):
274 super()._alter_field(
275 model,
276 old_field,
277 new_field,
278 old_type,
279 new_type,
280 old_db_params,
281 new_db_params,
282 strict,
283 )
284 # Added an index? Create any PostgreSQL-specific indexes.
285 if (
286 (not (old_field.db_index or old_field.unique) and new_field.db_index)
287 or (not old_field.unique and new_field.unique)
288 or (
289 self._is_changing_type_of_indexed_text_column(
290 old_field, old_type, new_type
291 )
292 )
293 ):
294 like_index_statement = self._create_like_index_sql(model, new_field)
295 if like_index_statement is not None:
296 self.execute(like_index_statement)
297
298 # Removed an index? Drop any PostgreSQL-specific indexes.
299 if old_field.unique and not (new_field.db_index or new_field.unique):
300 index_to_remove = self._create_index_name(
301 model._meta.db_table, [old_field.column], suffix="_like"
302 )
303 self.execute(self._delete_index_sql(model, index_to_remove))
304
305 def _index_columns(self, table, columns, col_suffixes, opclasses):
306 if opclasses:
307 return IndexColumns(
308 table,
309 columns,
310 self.quote_name,
311 col_suffixes=col_suffixes,
312 opclasses=opclasses,
313 )
314 return super()._index_columns(table, columns, col_suffixes, opclasses)
315
316 def add_index(self, model, index, concurrently=False):
317 self.execute(
318 index.create_sql(model, self, concurrently=concurrently), params=None
319 )
320
321 def remove_index(self, model, index, concurrently=False):
322 self.execute(index.remove_sql(model, self, concurrently=concurrently))
323
324 def _delete_index_sql(self, model, name, sql=None, concurrently=False):
325 sql = (
326 self.sql_delete_index_concurrently
327 if concurrently
328 else self.sql_delete_index
329 )
330 return super()._delete_index_sql(model, name, sql)
331
332 def _create_index_sql(
333 self,
334 model,
335 *,
336 fields=None,
337 name=None,
338 suffix="",
339 using="",
340 db_tablespace=None,
341 col_suffixes=(),
342 sql=None,
343 opclasses=(),
344 condition=None,
345 concurrently=False,
346 include=None,
347 expressions=None,
348 ):
349 sql = sql or (
350 self.sql_create_index
351 if not concurrently
352 else self.sql_create_index_concurrently
353 )
354 return super()._create_index_sql(
355 model,
356 fields=fields,
357 name=name,
358 suffix=suffix,
359 using=using,
360 db_tablespace=db_tablespace,
361 col_suffixes=col_suffixes,
362 sql=sql,
363 opclasses=opclasses,
364 condition=condition,
365 include=include,
366 expressions=expressions,
367 )
368
369 def _is_collation_deterministic(self, collation_name):
370 with self.connection.cursor() as cursor:
371 cursor.execute(
372 """
373 SELECT collisdeterministic
374 FROM pg_collation
375 WHERE collname = %s
376 """,
377 [collation_name],
378 )
379 row = cursor.fetchone()
380 return row[0] if row else None