codekingpro/portable-devtools
114k
1�
2 ��j�S � �� � S r SSKJr SSKJr SSKJr SSKJr SSKJr SSKJr SSKJ r S S3K4J r S S K J
r
\( a S S KJr \ " S
\S\4 S9rSS jrSS jr " S S5 rg)a�A Provides an API for creation of custom ClauseElements and compilers.5 6Synopsis7========8 9Usage involves the creation of one or more10:class:`~sqlalchemy.sql.expression.ClauseElement` subclasses and one or11more callables defining its compilation::12 13 from sqlalchemy.ext.compiler import compiles14 from sqlalchemy.sql.expression import ColumnClause15 16 17 class MyColumn(ColumnClause):18 inherit_cache = True19 20 21 @compiles(MyColumn)22 def compile_mycolumn(element, compiler, **kw):23 return "[%s]" % element.name24 25Above, ``MyColumn`` extends :class:`~sqlalchemy.sql.expression.ColumnClause`,26the base expression element for named column objects. The ``compiles``27decorator registers itself with the ``MyColumn`` class so that it is invoked28when the object is compiled to a string::29 30 from sqlalchemy import select31 32 s = select(MyColumn("x"), MyColumn("y"))33 print(str(s))34 35Produces:36 37.. sourcecode:: sql38 39 SELECT [x], [y]40 41Dialect-specific compilation rules42==================================43 44Compilers can also be made dialect-specific. The appropriate compiler will be45invoked for the dialect in use::46 47 from sqlalchemy.schema import DDLElement48 49 50 class AlterColumn(DDLElement):51 inherit_cache = False52 53 def __init__(self, column, cmd):54 self.column = column55 self.cmd = cmd56 57 58 @compiles(AlterColumn)59 def visit_alter_column(element, compiler, **kw):60 return "ALTER COLUMN %s ..." % element.column.name61 62 63 @compiles(AlterColumn, "postgresql")64 def visit_alter_column(element, compiler, **kw):65 return "ALTER TABLE %s ALTER COLUMN %s ..." % (66 element.table.name,67 element.column.name,68 )69 70The second ``visit_alter_table`` will be invoked when any ``postgresql``71dialect is used.72 73.. _compilerext_compiling_subelements:74 75Compiling sub-elements of a custom expression construct76=======================================================77 78The ``compiler`` argument is the79:class:`~sqlalchemy.engine.interfaces.Compiled` object in use. This object80can be inspected for any information about the in-progress compilation,81including ``compiler.dialect``, ``compiler.statement`` etc. The82:class:`~sqlalchemy.sql.compiler.SQLCompiler` and83:class:`~sqlalchemy.sql.compiler.DDLCompiler` both include a ``process()``84method which can be used for compilation of embedded attributes::85 86 from sqlalchemy.sql.expression import Executable, ClauseElement87 88 89 class InsertFromSelect(Executable, ClauseElement):90 inherit_cache = False91 92 def __init__(self, table, select):93 self.table = table94 self.select = select95 96 97 @compiles(InsertFromSelect)98 def visit_insert_from_select(element, compiler, **kw):99 return "INSERT INTO %s (%s)" % (100 compiler.process(element.table, asfrom=True, **kw),101 compiler.process(element.select, **kw),102 )103 104 105 insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5))106 print(insert)107 108Produces (formatted for readability):109 110.. sourcecode:: sql111 112 INSERT INTO mytable (113 SELECT mytable.x, mytable.y, mytable.z114 FROM mytable115 WHERE mytable.x > :x_1116 )117 118.. note::119 120 The above ``InsertFromSelect`` construct is only an example, this actual121 functionality is already available using the122 :meth:`_expression.Insert.from_select` method.123 124 125Cross Compiling between SQL and DDL compilers126---------------------------------------------127 128SQL and DDL constructs are each compiled using different base compilers -129``SQLCompiler`` and ``DDLCompiler``. A common need is to access the130compilation rules of SQL expressions from within a DDL expression. The131``DDLCompiler`` includes an accessor ``sql_compiler`` for this reason, such as132below where we generate a CHECK constraint that embeds a SQL expression::133 134 @compiles(MyConstraint)135 def compile_my_constraint(constraint, ddlcompiler, **kw):136 kw["literal_binds"] = True137 return "CONSTRAINT %s CHECK (%s)" % (138 constraint.name,139 ddlcompiler.sql_compiler.process(constraint.expression, **kw),140 )141 142Above, we add an additional flag to the process step as called by143:meth:`.SQLCompiler.process`, which is the ``literal_binds`` flag. This144indicates that any SQL expression which refers to a :class:`.BindParameter`145object or other "literal" object such as those which refer to strings or146integers should be rendered **in-place**, rather than being referred to as147a bound parameter; when emitting DDL, bound parameters are typically not148supported.149 150 151Changing the default compilation of existing constructs152=======================================================153 154The compiler extension applies just as well to the existing constructs. When155overriding the compilation of a built in SQL construct, the @compiles156decorator is invoked upon the appropriate class (be sure to use the class,157i.e. ``Insert`` or ``Select``, instead of the creation function such158as ``insert()`` or ``select()``).159 160Within the new compilation function, to get at the "original" compilation161routine, use the appropriate visit_XXX method - this162because compiler.process() will call upon the overriding routine and cause163an endless loop. Such as, to add "prefix" to all insert statements::164 165 from sqlalchemy.sql.expression import Insert166 167 168 @compiles(Insert)169 def prefix_inserts(insert, compiler, **kw):170 return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)171 172The above compiler will prefix all INSERT statements with "some prefix" when173compiled.174 175.. _type_compilation_extension:176 177Changing Compilation of Types178=============================179 180``compiler`` works for types, too, such as below where we implement the181MS-SQL specific 'max' keyword for ``String``/``VARCHAR``::182 183 @compiles(String, "mssql")184 @compiles(VARCHAR, "mssql")185 def compile_varchar(element, compiler, **kw):186 if element.length == "max":187 return "VARCHAR('max')"188 else:189 return compiler.visit_VARCHAR(element, **kw)190 191 192 foo = Table("foo", metadata, Column("data", VARCHAR("max")))193 194Subclassing Guidelines195======================196 197A big part of using the compiler extension is subclassing SQLAlchemy198expression constructs. To make this easier, the expression and199schema packages feature a set of "bases" intended for common tasks.200A synopsis is as follows:201 202* :class:`~sqlalchemy.sql.expression.ClauseElement` - This is the root203 expression class. Any SQL expression can be derived from this base, and is204 probably the best choice for longer constructs such as specialized INSERT205 statements.206 207* :class:`~sqlalchemy.sql.expression.ColumnElement` - The root of all208 "column-like" elements. Anything that you'd place in the "columns" clause of209 a SELECT statement (as well as order by and group by) can derive from this -210 the object will automatically have Python "comparison" behavior.211 212 :class:`~sqlalchemy.sql.expression.ColumnElement` classes want to have a213 ``type`` member which is expression's return type. This can be established214 at the instance level in the constructor, or at the class level if its215 generally constant::216 217 class timestamp(ColumnElement):218 type = TIMESTAMP()219 inherit_cache = True220 221* :class:`~sqlalchemy.sql.functions.FunctionElement` - This is a hybrid of a222 ``ColumnElement`` and a "from clause" like object, and represents a SQL223 function or stored procedure type of call. Since most databases support224 statements along the line of "SELECT FROM <some function>"225 ``FunctionElement`` adds in the ability to be used in the FROM clause of a226 ``select()`` construct::227 228 from sqlalchemy.sql.expression import FunctionElement229 230 231 class coalesce(FunctionElement):232 name = "coalesce"233 inherit_cache = True234 235 236 @compiles(coalesce)237 def compile(element, compiler, **kw):238 return "coalesce(%s)" % compiler.process(element.clauses, **kw)239 240 241 @compiles(coalesce, "oracle")242 def compile(element, compiler, **kw):243 if len(element.clauses) > 2:244 raise TypeError(245 "coalesce only supports two arguments on " "Oracle Database"246 )247 return "nvl(%s)" % compiler.process(element.clauses, **kw)248 249* :class:`.ExecutableDDLElement` - The root of all DDL expressions,250 like CREATE TABLE, ALTER TABLE, etc. Compilation of251 :class:`.ExecutableDDLElement` subclasses is issued by a252 :class:`.DDLCompiler` instead of a :class:`.SQLCompiler`.253 :class:`.ExecutableDDLElement` can also be used as an event hook in254 conjunction with event hooks like :meth:`.DDLEvents.before_create` and255 :meth:`.DDLEvents.after_create`, allowing the construct to be invoked256 automatically during CREATE TABLE and DROP TABLE sequences.257 258 .. seealso::259 260 :ref:`metadata_ddl_toplevel` - contains examples of associating261 :class:`.DDL` objects (which are themselves :class:`.ExecutableDDLElement`262 instances) with :class:`.DDLEvents` event hooks.263 264* :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which265 should be used with any expression class that represents a "standalone"266 SQL statement that can be passed directly to an ``execute()`` method. It267 is already implicit within ``DDLElement`` and ``FunctionElement``.268 269Most of the above constructs also respond to SQL statement caching. A270subclassed construct will want to define the caching behavior for the object,271which usually means setting the flag ``inherit_cache`` to the value of272``False`` or ``True``. See the next section :ref:`compilerext_caching`273for background.274 275 276.. _compilerext_caching:277 278Enabling Caching Support for Custom Constructs279==============================================280 281SQLAlchemy as of version 1.4 includes a282:ref:`SQL compilation caching facility <sql_caching>` which will allow283equivalent SQL constructs to cache their stringified form, along with other284structural information used to fetch results from the statement.285 286For reasons discussed at :ref:`caching_caveats`, the implementation of this287caching system takes a conservative approach towards including custom SQL288constructs and/or subclasses within the caching system. This includes that289any user-defined SQL constructs, including all the examples for this290extension, will not participate in caching by default unless they positively291assert that they are able to do so. The :attr:`.HasCacheKey.inherit_cache`292attribute when set to ``True`` at the class level of a specific subclass293will indicate that instances of this class may be safely cached, using the294cache key generation scheme of the immediate superclass. This applies295for example to the "synopsis" example indicated previously::296 297 class MyColumn(ColumnClause):298 inherit_cache = True299 300 301 @compiles(MyColumn)302 def compile_mycolumn(element, compiler, **kw):303 return "[%s]" % element.name304 305Above, the ``MyColumn`` class does not include any new state that306affects its SQL compilation; the cache key of ``MyColumn`` instances will307make use of that of the ``ColumnClause`` superclass, meaning it will take308into account the class of the object (``MyColumn``), the string name and309datatype of the object::310 311 >>> MyColumn("some_name", String())._generate_cache_key()312 CacheKey(313 key=('0', <class '__main__.MyColumn'>,314 'name', 'some_name',315 'type', (<class 'sqlalchemy.sql.sqltypes.String'>,316 ('length', None), ('collation', None))317 ), bindparams=[])318 319For objects that are likely to be **used liberally as components within many320larger statements**, such as :class:`_schema.Column` subclasses and custom SQL321datatypes, it's important that **caching be enabled as much as possible**, as322this may otherwise negatively affect performance.323 324An example of an object that **does** contain state which affects its SQL325compilation is the one illustrated at :ref:`compilerext_compiling_subelements`;326this is an "INSERT FROM SELECT" construct that combines together a327:class:`_schema.Table` as well as a :class:`_sql.Select` construct, each of328which independently affect the SQL string generation of the construct. For329this class, the example illustrates that it simply does not participate in330caching::331 332 class InsertFromSelect(Executable, ClauseElement):333 inherit_cache = False334 335 def __init__(self, table, select):336 self.table = table337 self.select = select338 339 340 @compiles(InsertFromSelect)341 def visit_insert_from_select(element, compiler, **kw):342 return "INSERT INTO %s (%s)" % (343 compiler.process(element.table, asfrom=True, **kw),344 compiler.process(element.select, **kw),345 )346 347While it is also possible that the above ``InsertFromSelect`` could be made to348produce a cache key that is composed of that of the :class:`_schema.Table` and349:class:`_sql.Select` components together, the API for this is not at the moment350fully public. However, for an "INSERT FROM SELECT" construct, which is only351used by itself for specific operations, caching is not as critical as in the352previous example.353 354For objects that are **used in relative isolation and are generally355standalone**, such as custom :term:`DML` constructs like an "INSERT FROM356SELECT", **caching is generally less critical** as the lack of caching for such357a construct will have only localized implications for that specific operation.358 359 360Further Examples361================362 363"UTC timestamp" function364-------------------------365 366A function that works like "CURRENT_TIMESTAMP" except applies the367appropriate conversions so that the time is in UTC time. Timestamps are best368stored in relational databases as UTC, without time zones. UTC so that your369database doesn't think time has gone backwards in the hour when daylight370savings ends, without timezones because timezones are like character371encodings - they're best applied only at the endpoints of an application372(i.e. convert to UTC upon user input, re-apply desired timezone upon display).373 374For PostgreSQL and Microsoft SQL Server::375 376 from sqlalchemy.sql import expression377 from sqlalchemy.ext.compiler import compiles378 from sqlalchemy.types import DateTime379 380 381 class utcnow(expression.FunctionElement):382 type = DateTime()383 inherit_cache = True384 385 386 @compiles(utcnow, "postgresql")387 def pg_utcnow(element, compiler, **kw):388 return "TIMEZONE('utc', CURRENT_TIMESTAMP)"389 390 391 @compiles(utcnow, "mssql")392 def ms_utcnow(element, compiler, **kw):393 return "GETUTCDATE()"394 395Example usage::396 397 from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData398 399 metadata = MetaData()400 event = Table(401 "event",402 metadata,403 Column("id", Integer, primary_key=True),404 Column("description", String(50), nullable=False),405 Column("timestamp", DateTime, server_default=utcnow()),406 )407 408"GREATEST" function409-------------------410 411The "GREATEST" function is given any number of arguments and returns the one412that is of the highest value - its equivalent to Python's ``max``413function. A SQL standard version versus a CASE based version which only414accommodates two arguments::415 416 from sqlalchemy.sql import expression, case417 from sqlalchemy.ext.compiler import compiles418 from sqlalchemy.types import Numeric419 420 421 class greatest(expression.FunctionElement):422 type = Numeric()423 name = "greatest"424 inherit_cache = True425 426 427 @compiles(greatest)428 def default_greatest(element, compiler, **kw):429 return compiler.visit_function(element)430 431 432 @compiles(greatest, "sqlite")433 @compiles(greatest, "mssql")434 @compiles(greatest, "oracle")435 def case_greatest(element, compiler, **kw):436 arg1, arg2 = list(element.clauses)437 return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw)438 439Example usage::440 441 Session.query(Account).filter(442 greatest(Account.checking_balance, Account.savings_balance) > 10000443 )444 445"false" expression446------------------447 448Render a "false" constant expression, rendering as "0" on platforms that449don't have a "false" constant::450 451 from sqlalchemy.sql import expression452 from sqlalchemy.ext.compiler import compiles453 454 455 class sql_false(expression.ColumnElement):456 inherit_cache = True457 458 459 @compiles(sql_false)460 def default_false(element, compiler, **kw):461 return "false"462 463 464 @compiles(sql_false, "mssql")465 @compiles(sql_false, "mysql")466 @compiles(sql_false, "oracle")467 def int_false(element, compiler, **kw):468 return "0"469 470Example usage::471 472 from sqlalchemy import select, union_all473 474 exp = union_all(475 select(users.c.name, sql_false().label("enrolled")),476 select(customers.c.name, customers.c.enrolled),477 )478 479� )� annotations)�Any)�Callable)�Dict)�Type)�
TYPE_CHECKING)�TypeVar� )�exc)�sqltypes)� SQLCompiler�_F.)�boundc � ^ ^� SU U4S jjnU$ )zVRegister a function as a compiler for a480given :class:`_expression.ClauseElement` type.c �h >^^� TR R SS 5 m[ TSS 5 mT( dN [ 5 mT( a SU4S jjnUTR S' [ TSU4S j5 [ TST5 T( a T H nU TR U' M U $ U TR S' U $ )N�_compiler_dispatcher�_compiler_dispatchc � >� T" X40 UD6$ ! [ R a1 n[ R " U[ U 5 S[ U 5 - S9UeS nAff = f)N�0%s construct has no default compilation handler.��message)r �UnsupportedCompilationError�type)�element�compiler�kw�uce�existing_dispatchs ��]D:\code\apps\devtools\python\user_packages\Python313\site-packages\sqlalchemy/ext/compiler.py�_wrap_existing_dispatch�;compiles.<locals>.decorate.<locals>._wrap_existing_dispatch sb �� �#� 0�� I�b� I�I�� �:�:� #� !� =� =� $� ��M�%3�59�'�]�%C� �481 #� #��#�s � �A�,A � A�defaultc � >� T" U 0 UD6$ �N� )�argr �existings �r �<lambda>�,compiles.<locals>.decorate.<locals>.<lambda> s �� �8�S�#7�B�#7� �r r r r
r r �returnr )�__dict__�get�getattr� _dispatcher�specs�setattr)�fnr �sr' r �class_r1 s @@��r �decorate�compiles.<locals>.decorate� s� �� ��?�?�&�&�'=�t�D�� $�F�,@�$� G���"�}�H� � #� � #�,7� #�?B� #�� #� -D����y�)�
��$�7�
�482
�F� 2�H� =� ���$&����q�!� �483 � � )+�H�N�N�9� %�� r* )r3 r r, r r% )r5 r1 r6 s `` r �compilesr8 � s �� �*� *�X �Or* c �N � [ U S5 ( a U R U l U ?gg)z^Remove all custom compilers associated with a given484:class:`_expression.ClauseElement` type.485 486r N)�hasattr�_original_compiler_dispatchr r )r5 s r �487deregisterr< ) s+ � � �v�-�.�.�$*�$F�$F��!� � '� /r* c �( � \ rS rSrSS jrSS jrSrg)r0 i4 c � � 0 U l g r$ �r1 )�selfs r �__init__�_dispatcher.__init__5 s � �46��488r* c �� ^ � U R R UR R S 5 nU( d U R S nUR SS 5 nU( a / m U 4S jUS' U" X40 UD6nU( a9 T ( d$ T R S S U4[ R 45 T H nU" U6 M489 U$ ! [ a1 n[490 R " U[ U5 S[ U5 - S9UeS nAff = f)Nr" r r �add_to_result_mapc �& >� TR U 5 $ r$ )�append)�args�arm_collections �r r( �&_dispatcher.__call__.<locals>.<lambda>M s �� �N�4I�4I�$�4Or* ) r1 r. �dialect�name�KeyErrorr r r rF r �NULLTYPE)491r@ r r r r3 �ke�arm�expr�tuprH s492 @r �__call__�_dispatcher.__call__8 s� �� �
�Z�Z�^�^�H� ,� ,� 1� 1�4�
8���
��Z�Z� �*�� �f�f�(�$�/�� � �N�&O�B�"� #��'�*�r�*�� �!� �%�%��4�'��X�->�->�?�� &���S� � &�� ��5 �
��5�5� ���M� +�-1�'�]� ;��493 � ��
�s �B8 �8494C3�,C.�.C3r? N)r, �Noner+ )�__name__�495__module__� __qualname__�__firstlineno__rA rR �__static_attributes__r% r* r r0 r0 4 s � �7� r* r0 N)r5 � Type[Any]r1 �strr, zCallable[[_F], _F])r5 rZ r, rT )�__doc__�496__future__r �typingr r r r r r � r �sqlr � sql.compilerr
r r8 r< r0 r% r* r �<module>rb sY ��[�x #� � � � � � � � ��*� �T��#�s�(�+�,��0�f(�$� $r*