codekingpro/portable-devtools
114k
1# ext/compiler.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8r"""Provides an API for creation of custom ClauseElements and compilers.
9
10Synopsis
11========
12
13Usage involves the creation of one or more
14:class:`~sqlalchemy.sql.expression.ClauseElement` subclasses and one or
15more callables defining its compilation::
16
17 from sqlalchemy.ext.compiler import compiles
18 from sqlalchemy.sql.expression import ColumnClause
19
20
21 class MyColumn(ColumnClause):
22 inherit_cache = True
23
24
25 @compiles(MyColumn)
26 def compile_mycolumn(element, compiler, **kw):
27 return "[%s]" % element.name
28
29Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`,
30the base expression element for named column objects. The ``compiles``
31decorator registers itself with the ``MyColumn`` class so that it is invoked
32when the object is compiled to a string::
33
34 from sqlalchemy import select
35
36 s = select(MyColumn("x"), MyColumn("y"))
37 print(str(s))
38
39Produces:
40
41.. sourcecode:: sql
42
43 SELECT [x], [y]
44
45Dialect-specific compilation rules
46==================================
47
48Compilers can also be made dialect-specific. The appropriate compiler will be
49invoked for the dialect in use::
50
51 from sqlalchemy.schema import DDLElement
52
53
54 class AlterColumn(DDLElement):
55 inherit_cache = False
56
57 def __init__(self, column, cmd):
58 self.column = column
59 self.cmd = cmd
60
61
62 @compiles(AlterColumn)
63 def visit_alter_column(element, compiler, **kw):
64 return "ALTER COLUMN %s ..." % element.column.name
65
66
67 @compiles(AlterColumn, "postgresql")
68 def visit_alter_column(element, compiler, **kw):
69 return "ALTER TABLE %s ALTER COLUMN %s ..." % (
70 element.table.name,
71 element.column.name,
72 )
73
74The second ``visit_alter_table`` will be invoked when any ``postgresql``
75dialect is used.
76
77.. _compilerext_compiling_subelements:
78
79Compiling sub-elements of a custom expression construct
80=======================================================
81
82The ``compiler`` argument is the
83:class:`~sqlalchemy.engine.interfaces.Compiled` object in use. This object
84can be inspected for any information about the in-progress compilation,
85including ``compiler.dialect``, ``compiler.statement`` etc. The
86:class:`~sqlalchemy.sql.compiler.SQLCompiler` and
87:class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()``
88method which can be used for compilation of embedded attributes::
89
90 from sqlalchemy.sql.expression import Executable, ClauseElement
91
92
93 class InsertFromSelect(Executable, ClauseElement):
94 inherit_cache = False
95
96 def __init__(self, table, select):
97 self.table = table
98 self.select = select
99
100
101 @compiles(InsertFromSelect)
102 def visit_insert_from_select(element, compiler, **kw):
103 return "INSERT INTO %s (%s)" % (
104 compiler.process(element.table, asfrom=True, **kw),
105 compiler.process(element.select, **kw),
106 )
107
108
109 insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5))
110 print(insert)
111
112Produces (formatted for readability):
113
114.. sourcecode:: sql
115
116 INSERT INTO mytable (
117 SELECT mytable.x, mytable.y, mytable.z
118 FROM mytable
119 WHERE mytable.x > :x_1
120 )
121
122.. note::
123
124 The above ``InsertFromSelect`` construct is only an example, this actual
125 functionality is already available using the
126 :meth:`_expression.Insert.from_select` method.
127
128
129Cross Compiling between SQL and DDL compilers
130---------------------------------------------
131
132SQL and DDL constructs are each compiled using different base compilers -
133``SQLCompiler`` and ``DDLCompiler``. A common need is to access the
134compilation rules of SQL expressions from within a DDL expression. The
135``DDLCompiler`` includes an accessor ``sql_compiler`` for this reason, such as
136below where we generate a CHECK constraint that embeds a SQL expression::
137
138 @compiles(MyConstraint)
139 def compile_my_constraint(constraint, ddlcompiler, **kw):
140 kw["literal_binds"] = True
141 return "CONSTRAINT %s CHECK (%s)" % (
142 constraint.name,
143 ddlcompiler.sql_compiler.process(constraint.expression, **kw),
144 )
145
146Above, we add an additional flag to the process step as called by
147:meth:`.SQLCompiler.process`, which is the ``literal_binds`` flag. This
148indicates that any SQL expression which refers to a :class:`.BindParameter`
149object or other "literal" object such as those which refer to strings or
150integers should be rendered **in-place**, rather than being referred to as
151a bound parameter; when emitting DDL, bound parameters are typically not
152supported.
153
154
155Changing the default compilation of existing constructs
156=======================================================
157
158The compiler extension applies just as well to the existing constructs. When
159overriding the compilation of a built in SQL construct, the @compiles
160decorator is invoked upon the appropriate class (be sure to use the class,
161i.e. ``Insert`` or ``Select``, instead of the creation function such
162as ``insert()`` or ``select()``).
163
164Within the new compilation function, to get at the "original" compilation
165routine, use the appropriate visit_XXX method - this
166because compiler.process() will call upon the overriding routine and cause
167an endless loop. Such as, to add "prefix" to all insert statements::
168
169 from sqlalchemy.sql.expression import Insert
170
171
172 @compiles(Insert)
173 def prefix_inserts(insert, compiler, **kw):
174 return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)
175
176The above compiler will prefix all INSERT statements with "some prefix" when
177compiled.
178
179.. _type_compilation_extension:
180
181Changing Compilation of Types
182=============================
183
184``compiler`` works for types, too, such as below where we implement the
185MS-SQL specific 'max' keyword for ``String``/``VARCHAR``::
186
187 @compiles(String, "mssql")
188 @compiles(VARCHAR, "mssql")
189 def compile_varchar(element, compiler, **kw):
190 if element.length == "max":
191 return "VARCHAR('max')"
192 else:
193 return compiler.visit_VARCHAR(element, **kw)
194
195
196 foo = Table("foo", metadata, Column("data", VARCHAR("max")))
197
198Subclassing Guidelines
199======================
200
201A big part of using the compiler extension is subclassing SQLAlchemy
202expression constructs. To make this easier, the expression and
203schema packages feature a set of "bases" intended for common tasks.
204A synopsis is as follows:
205
206* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root
207 expression class. Any SQL expression can be derived from this base, and is
208 probably the best choice for longer constructs such as specialized INSERT
209 statements.
210
211* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all
212 "column-like" elements. Anything that you'd place in the "columns" clause of
213 a SELECT statement (as well as order by and group by) can derive from this -
214 the object will automatically have Python "comparison" behavior.
215
216 :class:`~sqlalchemy.sql.expression.ColumnElement` classes want to have a
217 ``type`` member which is expression's return type. This can be established
218 at the instance level in the constructor, or at the class level if its
219 generally constant::
220
221 class timestamp(ColumnElement):
222 type = TIMESTAMP()
223 inherit_cache = True
224
225* :class:`~sqlalchemy.sql.functions.FunctionElement` - This is a hybrid of a
226 ``ColumnElement`` and a "from clause" like object, and represents a SQL
227 function or stored procedure type of call. Since most databases support
228 statements along the line of "SELECT FROM <some function>"
229 ``FunctionElement`` adds in the ability to be used in the FROM clause of a
230 ``select()`` construct::
231
232 from sqlalchemy.sql.expression import FunctionElement
233
234
235 class coalesce(FunctionElement):
236 name = "coalesce"
237 inherit_cache = True
238
239
240 @compiles(coalesce)
241 def compile(element, compiler, **kw):
242 return "coalesce(%s)" % compiler.process(element.clauses, **kw)
243
244
245 @compiles(coalesce, "oracle")
246 def compile(element, compiler, **kw):
247 if len(element.clauses) > 2:
248 raise TypeError(
249 "coalesce only supports two arguments on " "Oracle Database"
250 )
251 return "nvl(%s)" % compiler.process(element.clauses, **kw)
252
253* :class:`.ExecutableDDLElement` - The root of all DDL expressions,
254 like CREATE TABLE, ALTER TABLE, etc. Compilation of
255 :class:`.ExecutableDDLElement` subclasses is issued by a
256 :class:`.DDLCompiler` instead of a :class:`.SQLCompiler`.
257 :class:`.ExecutableDDLElement` can also be used as an event hook in
258 conjunction with event hooks like :meth:`.DDLEvents.before_create` and
259 :meth:`.DDLEvents.after_create`, allowing the construct to be invoked
260 automatically during CREATE TABLE and DROP TABLE sequences.
261
262 .. seealso::
263
264 :ref:`metadata_ddl_toplevel` - contains examples of associating
265 :class:`.DDL` objects (which are themselves :class:`.ExecutableDDLElement`
266 instances) with :class:`.DDLEvents` event hooks.
267
268* :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which
269 should be used with any expression class that represents a "standalone"
270 SQL statement that can be passed directly to an ``execute()`` method. It
271 is already implicit within ``DDLElement`` and ``FunctionElement``.
272
273Most of the above constructs also respond to SQL statement caching. A
274subclassed construct will want to define the caching behavior for the object,
275which usually means setting the flag ``inherit_cache`` to the value of
276``False`` or ``True``. See the next section :ref:`compilerext_caching`
277for background.
278
279
280.. _compilerext_caching:
281
282Enabling Caching Support for Custom Constructs
283==============================================
284
285SQLAlchemy as of version 1.4 includes a
286:ref:`SQL compilation caching facility <sql_caching>` which will allow
287equivalent SQL constructs to cache their stringified form, along with other
288structural information used to fetch results from the statement.
289
290For reasons discussed at :ref:`caching_caveats`, the implementation of this
291caching system takes a conservative approach towards including custom SQL
292constructs and/or subclasses within the caching system. This includes that
293any user-defined SQL constructs, including all the examples for this
294extension, will not participate in caching by default unless they positively
295assert that they are able to do so. The :attr:`.HasCacheKey.inherit_cache`
296attribute when set to ``True`` at the class level of a specific subclass
297will indicate that instances of this class may be safely cached, using the
298cache key generation scheme of the immediate superclass. This applies
299for example to the "synopsis" example indicated previously::
300
301 class MyColumn(ColumnClause):
302 inherit_cache = True
303
304
305 @compiles(MyColumn)
306 def compile_mycolumn(element, compiler, **kw):
307 return "[%s]" % element.name
308
309Above, the ``MyColumn`` class does not include any new state that
310affects its SQL compilation; the cache key of ``MyColumn`` instances will
311make use of that of the ``ColumnClause`` superclass, meaning it will take
312into account the class of the object (``MyColumn``), the string name and
313datatype of the object::
314
315 >>> MyColumn("some_name", String())._generate_cache_key()
316 CacheKey(
317 key=('0', <class '__main__.MyColumn'>,
318 'name', 'some_name',
319 'type', (<class 'sqlalchemy.sql.sqltypes.String'>,
320 ('length', None), ('collation', None))
321 ), bindparams=[])
322
323For objects that are likely to be **used liberally as components within many
324larger statements**, such as :class:`_schema.Column` subclasses and custom SQL
325datatypes, it's important that **caching be enabled as much as possible**, as
326this may otherwise negatively affect performance.
327
328An example of an object that **does** contain state which affects its SQL
329compilation is the one illustrated at :ref:`compilerext_compiling_subelements`;
330this is an "INSERT FROM SELECT" construct that combines together a
331:class:`_schema.Table` as well as a :class:`_sql.Select` construct, each of
332which independently affect the SQL string generation of the construct. For
333this class, the example illustrates that it simply does not participate in
334caching::
335
336 class InsertFromSelect(Executable, ClauseElement):
337 inherit_cache = False
338
339 def __init__(self, table, select):
340 self.table = table
341 self.select = select
342
343
344 @compiles(InsertFromSelect)
345 def visit_insert_from_select(element, compiler, **kw):
346 return "INSERT INTO %s (%s)" % (
347 compiler.process(element.table, asfrom=True, **kw),
348 compiler.process(element.select, **kw),
349 )
350
351While it is also possible that the above ``InsertFromSelect`` could be made to
352produce a cache key that is composed of that of the :class:`_schema.Table` and
353:class:`_sql.Select` components together, the API for this is not at the moment
354fully public. However, for an "INSERT FROM SELECT" construct, which is only
355used by itself for specific operations, caching is not as critical as in the
356previous example.
357
358For objects that are **used in relative isolation and are generally
359standalone**, such as custom :term:`DML` constructs like an "INSERT FROM
360SELECT", **caching is generally less critical** as the lack of caching for such
361a construct will have only localized implications for that specific operation.
362
363
364Further Examples
365================
366
367"UTC timestamp" function
368-------------------------
369
370A function that works like "CURRENT_TIMESTAMP" except applies the
371appropriate conversions so that the time is in UTC time. Timestamps are best
372stored in relational databases as UTC, without time zones. UTC so that your
373database doesn't think time has gone backwards in the hour when daylight
374savings ends, without timezones because timezones are like character
375encodings - they're best applied only at the endpoints of an application
376(i.e. convert to UTC upon user input, re-apply desired timezone upon display).
377
378For PostgreSQL and Microsoft SQL Server::
379
380 from sqlalchemy.sql import expression
381 from sqlalchemy.ext.compiler import compiles
382 from sqlalchemy.types import DateTime
383
384
385 class utcnow(expression.FunctionElement):
386 type = DateTime()
387 inherit_cache = True
388
389
390 @compiles(utcnow, "postgresql")
391 def pg_utcnow(element, compiler, **kw):
392 return "TIMEZONE('utc', CURRENT_TIMESTAMP)"
393
394
395 @compiles(utcnow, "mssql")
396 def ms_utcnow(element, compiler, **kw):
397 return "GETUTCDATE()"
398
399Example usage::
400
401 from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData
402
403 metadata = MetaData()
404 event = Table(
405 "event",
406 metadata,
407 Column("id", Integer, primary_key=True),
408 Column("description", String(50), nullable=False),
409 Column("timestamp", DateTime, server_default=utcnow()),
410 )
411
412"GREATEST" function
413-------------------
414
415The "GREATEST" function is given any number of arguments and returns the one
416that is of the highest value - its equivalent to Python's ``max``
417function. A SQL standard version versus a CASE based version which only
418accommodates two arguments::
419
420 from sqlalchemy.sql import expression, case
421 from sqlalchemy.ext.compiler import compiles
422 from sqlalchemy.types import Numeric
423
424
425 class greatest(expression.FunctionElement):
426 type = Numeric()
427 name = "greatest"
428 inherit_cache = True
429
430
431 @compiles(greatest)
432 def default_greatest(element, compiler, **kw):
433 return compiler.visit_function(element)
434
435
436 @compiles(greatest, "sqlite")
437 @compiles(greatest, "mssql")
438 @compiles(greatest, "oracle")
439 def case_greatest(element, compiler, **kw):
440 arg1, arg2 = list(element.clauses)
441 return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw)
442
443Example usage::
444
445 Session.query(Account).filter(
446 greatest(Account.checking_balance, Account.savings_balance) > 10000
447 )
448
449"false" expression
450------------------
451
452Render a "false" constant expression, rendering as "0" on platforms that
453don't have a "false" constant::
454
455 from sqlalchemy.sql import expression
456 from sqlalchemy.ext.compiler import compiles
457
458
459 class sql_false(expression.ColumnElement):
460 inherit_cache = True
461
462
463 @compiles(sql_false)
464 def default_false(element, compiler, **kw):
465 return "false"
466
467
468 @compiles(sql_false, "mssql")
469 @compiles(sql_false, "mysql")
470 @compiles(sql_false, "oracle")
471 def int_false(element, compiler, **kw):
472 return "0"
473
474Example usage::
475
476 from sqlalchemy import select, union_all
477
478 exp = union_all(
479 select(users.c.name, sql_false().label("enrolled")),
480 select(customers.c.name, customers.c.enrolled),
481 )
482
483"""
484from __future__ import annotations
485
486from typing import Any
487from typing import Callable
488from typing import Dict
489from typing import Type
490from typing import TYPE_CHECKING
491from typing import TypeVar
492
493from .. import exc
494from ..sql import sqltypes
495
496if TYPE_CHECKING:
497 from ..sql.compiler import SQLCompiler
498
499_F = TypeVar("_F", bound=Callable[..., Any])
500
501
502def compiles(class_: Type[Any], *specs: str) -> Callable[[_F], _F]:
503 """Register a function as a compiler for a
504 given :class:`_expression.ClauseElement` type."""
505
506 def decorate(fn: _F) -> _F:
507 # get an existing @compiles handler
508 existing = class_.__dict__.get("_compiler_dispatcher", None)
509
510 # get the original handler. All ClauseElement classes have one
511 # of these, but some TypeEngine classes will not.
512 existing_dispatch = getattr(class_, "_compiler_dispatch", None)
513
514 if not existing:
515 existing = _dispatcher()
516
517 if existing_dispatch:
518
519 def _wrap_existing_dispatch(
520 element: Any, compiler: SQLCompiler, **kw: Any
521 ) -> Any:
522 try:
523 return existing_dispatch(element, compiler, **kw)
524 except exc.UnsupportedCompilationError as uce:
525 raise exc.UnsupportedCompilationError(
526 compiler,
527 type(element),
528 message="%s construct has no default "
529 "compilation handler." % type(element),
530 ) from uce
531
532 existing.specs["default"] = _wrap_existing_dispatch
533
534 # TODO: why is the lambda needed ?
535 setattr(
536 class_,
537 "_compiler_dispatch",
538 lambda *arg, **kw: existing(*arg, **kw),
539 )
540 setattr(class_, "_compiler_dispatcher", existing)
541
542 if specs:
543 for s in specs:
544 existing.specs[s] = fn
545
546 else:
547 existing.specs["default"] = fn
548 return fn
549
550 return decorate
551
552
553def deregister(class_: Type[Any]) -> None:
554 """Remove all custom compilers associated with a given
555 :class:`_expression.ClauseElement` type.
556
557 """
558
559 if hasattr(class_, "_compiler_dispatcher"):
560 class_._compiler_dispatch = class_._original_compiler_dispatch
561 del class_._compiler_dispatcher
562
563
564class _dispatcher:
565 def __init__(self) -> None:
566 self.specs: Dict[str, Callable[..., Any]] = {}
567
568 def __call__(self, element: Any, compiler: SQLCompiler, **kw: Any) -> Any:
569 # TODO: yes, this could also switch off of DBAPI in use.
570 fn = self.specs.get(compiler.dialect.name, None)
571 if not fn:
572 try:
573 fn = self.specs["default"]
574 except KeyError as ke:
575 raise exc.UnsupportedCompilationError(
576 compiler,
577 type(element),
578 message="%s construct has no default "
579 "compilation handler." % type(element),
580 ) from ke
581
582 # if compilation includes add_to_result_map, collect add_to_result_map
583 # arguments from the user-defined callable, which are probably none
584 # because this is not public API. if it wasn't called, then call it
585 # ourselves.
586 arm = kw.get("add_to_result_map", None)
587 if arm:
588 arm_collection = []
589 kw["add_to_result_map"] = lambda *args: arm_collection.append(args)
590
591 expr = fn(element, compiler, **kw)
592
593 if arm:
594 if not arm_collection:
595 arm_collection.append(
596 (None, None, (element,), sqltypes.NULLTYPE)
597 )
598 for tup in arm_collection:
599 arm(*tup)
600 return expr
601