From 82dfcf43de1d1647306ee2840b79be4f9df7588a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 9 Aug 2018 13:34:03 -0400 Subject: [PATCH] Write a much better section about server defaults Include information about eager_defaults etc. These docs were written before we had all the features we do now. Change-Id: Ie62e58c5986698824f717e9f8c802b07a27517d4 Fixes: #4317 --- doc/build/core/defaults.rst | 62 +++----- doc/build/orm/persistence_techniques.rst | 185 +++++++++++++++++++++++ lib/sqlalchemy/orm/mapper.py | 4 + 3 files changed, 214 insertions(+), 37 deletions(-) diff --git a/doc/build/core/defaults.rst b/doc/build/core/defaults.rst index 4b52641384..0d5a1e89bc 100644 --- a/doc/build/core/defaults.rst +++ b/doc/build/core/defaults.rst @@ -214,8 +214,8 @@ column primary keys are represented in the same format). .. _server_defaults: -Server-invoked default expressions ----------------------------------- +Server-invoked DDL-Explicit Default Expressions +----------------------------------------------- A variant on the SQL expression default is the :paramref:`.Column.server_default`, which gets placed in the CREATE TABLE statement during a :meth:`.Table.create` operation: @@ -250,50 +250,38 @@ functions and expressions, and not the more complex cases like an embedded SELEC .. _triggered_columns: -Triggered Columns ------------------ +Marking Implicitly Generated Values, timestamps, and Triggered Columns +---------------------------------------------------------------------- -Columns with values set by a database trigger or other external process may be -called out using :class:`.FetchedValue` as a marker:: +Columns which generate a new value on INSERT or UPDATE based on other +server-side database mechanisms, such as database-specific auto-generating +behaviors such as seen with TIMESTAMP columns on some platforms, as well as +custom triggers that invoke upon INSERT or UPDATE to generate a new value, +may be called out using :class:`.FetchedValue` as a marker:: t = Table('test', meta, - Column('abc', String(20), server_default=FetchedValue()), + Column('id', Integer, primary_key=True), + Column('abc', TIMESTAMP, server_default=FetchedValue()), Column('def', String(20), server_onupdate=FetchedValue()) ) -These markers do not emit a "default" clause when the table is created, -however they do set the same internal flags as a static ``server_default`` -clause, providing hints to higher-level tools that a "post-fetch" of these -rows should be performed after an insert or update. +The :class:`.FetchedValue` indicator does not affect the rendered DDL for the +CREATE TABLE. Instead, it marks the column as one that will have a new value +populated by the database during the process of an INSERT or UPDATE statement, +and for supporting databases may be used to indicate that the column should be +part of a RETURNING or OUTPUT clause for the statement. Tools such as the +SQLAlchemy ORM then make use of this marker in order to know how to get at the +value of the column after such an operation. In particular, the +:meth:`.ValuesBase.return_defaults` method can be used with an :class:`.Insert` +or :class:`.Update` construct to indicate that these values should be +returned. -.. note:: +For details on using :class:`.FetchedValue` with the ORM, see +:ref:`orm_server_defaults`. - It's generally not appropriate to use :class:`.FetchedValue` in - conjunction with a primary key column, particularly when using the - ORM or any other scenario where the :attr:`.ResultProxy.inserted_primary_key` - attribute is required. This is becaue the "post-fetch" operation requires - that the primary key value already be available, so that the - row can be selected on its primary key. - - For a server-generated primary key value, all databases provide special - accessors or other techniques in order to acquire the "last inserted - primary key" column of a table. These mechanisms aren't affected by the presence - of :class:`.FetchedValue`. For special situations where triggers are - used to generate primary key values, and the database in use does not - support the ``RETURNING`` clause, it may be necessary to forego the usage - of the trigger and instead apply the SQL expression or function as a - "pre execute" expression:: - - t = Table('test', meta, - Column('abc', MyType, default=func.generate_new_value(), primary_key=True) - ) +.. seealso:: - Where above, when :meth:`.Table.insert` is used, - the ``func.generate_new_value()`` expression will be pre-executed - in the context of a scalar ``SELECT`` statement, and the new value will - be applied to the subsequent ``INSERT``, while at the same time being - made available to the :attr:`.ResultProxy.inserted_primary_key` - attribute. + :ref:`orm_server_defaults` Defining Sequences diff --git a/doc/build/orm/persistence_techniques.rst b/doc/build/orm/persistence_techniques.rst index 7a7d9042d4..541f3e8fe1 100644 --- a/doc/build/orm/persistence_techniques.rst +++ b/doc/build/orm/persistence_techniques.rst @@ -183,6 +183,191 @@ value and pass it through, rather than omitting it as a "missing" value:: .. versionadded:: 1.1 added the :meth:`.TypeEngine.evaluates_none` method in order to indicate that a "None" value should be treated as significant. +.. _orm_server_defaults: + +Fetching Server-Generated Defaults +=================================== + +As introduced in the sections :ref:`server_defaults` and :ref:`triggered_columns`, +the Core supports the notion of database columns for which the database +itself generates a value upon INSERT and in less common cases upon UPDATE +statements. The ORM features support for such columns regarding being +able to fetch these newly generated values upon flush. This behavior is +required in the case of primary key columns that are generated by the server, +since the ORM has to know the primary key of an object once it is persisted. + +In the vast majority of cases, primary key columns that have their value +generated automatically by the database are simple integer columns, which are +implemented by the database as either a so-called "autoincrement" column, or +from a sequence associated with the column. Every database dialect within +SQLAlchemy Core supports a method of retrieving these primary key values which +is often native to the Python DBAPI, and in general this process is automatic, +with the exception of a database like Oracle that requires us to specify a +:class:`.Sequence` explicitly. There is more documentation regarding this +at :paramref:`.Column.autoincrement`. + +For server-generating columns that are not primary key columns or that are not +simple autoincrementing integer columns, the ORM requires that these columns +are marked with an appropriate server_default directive that allows the ORM to +retrieve this value. Not all methods are supported on all backends, however, +so care must be taken to use the appropriate method. The two questions to be +answered are, 1. is this column part of the primary key or not, and 2. does the +database support RETURNING or an equivalent, such as "OUTPUT inserted"; these +are SQL phrases which return a server-generated value at the same time as the +INSERT or UPDATE statement is invoked. Databases that support RETURNING or +equivalent include PostgreSQL, Oracle, and SQL Server. Databases that do not +include SQLite and MySQL. + +Case 1: non primary key, RETURNING or equivalent is supported +------------------------------------------------------------- + +In this case, columns should be marked as :class:`.FetchedValue` or with an +explicit :paramref:`.Column.server_default`. The +:paramref:`.orm.mapper.eager_defaults` flag may be used to indicate that these +columns should be fetched immediately upon INSERT and sometimes UPDATE:: + + + class MyModel(Base): + __tablename__ = 'my_table' + + id = Column(Integer, primary_key=True) + timestamp = Column(DateTime(), server_default=func.now()) + + # assume a database trigger populates a value into this column + # during INSERT + special_identifier = Column(String(50), server_default=FetchedValue()) + + __mapper_args__ = {"eager_defaults": True} + +Above, an INSERT statement will render "NOW()" as the value of "timestamp", +and the statement will also include the "timestamp" and "special_identifier" +columns within the RETURNING clause so they are available immediately. +On the PostgreSQL database, an INSERT for the above table will look like: + +.. sourcecode:: sql + + INSERT INTO my_table DEFAULT VALUES RETURNING my_table.id, my_table.timestamp, my_table.special_identifier + +Case 2: non primary key, RETURNING or equivalent is not supported or not needed +-------------------------------------------------------------------------------- + +This case is the same as case 1 above, except we don't specify +:paramref:`.orm.mapper.eager_defaults`:: + + class MyModel(Base): + __tablename__ = 'my_table' + + id = Column(Integer, primary_key=True) + timestamp = Column(DateTime(), server_default=func.now()) + + # assume a database trigger populates a value into this column + # during INSERT + special_identifier = Column(String(50), server_default=FetchedValue()) + +After a record with the above mapping is INSERTed, the "timestamp" and +"special_identifier" columns will remain empty, and will be fetched via +a second SELECT statement when they are first accessed after the flush, e.g. +they are marked as "expired". + +If the :paramref:`.orm.mapper.eager_defaults` is still used, and the backend +database does not support RETURNING or an equivalent, the ORM will emit this +SELECT statement immediately following the INSERT statement. This is often +undesireable as it adds additional SELECT statements to the flush process that +may not be needed. Using the above mapping with the +:paramref:`.orm.mapper.eager_defaults` flag set to True against MySQL results +in SQL like this upon flush (minus the comment, which is for clarification only): + +.. sourcecode:: sql + + INSERT INTO my_table () VALUES () + + -- when eager_defaults **is** used, but RETURNING is not supported + SELECT my_table.timestamp AS my_table_timestamp, my_table.special_identifier AS my_table_special_identifier + FROM my_table WHERE my_table.id = %s + +Case 3: primary key, RETURNING or equivalent is supported +---------------------------------------------------------- + +A primary key column with a server-generated value must be fetched immediately +upon INSERT; the ORM can only access rows for which it has a primary key value, +so if the primary key is generated by the server, the ORM needs a way for the +database to give us that new value immediately upon INSERT. + +As mentioned above, for integer "autoincrement" columns as well as +PostgreSQL SERIAL, these types are handled automatically by the Core; databases +include functions for fetching the "last inserted id" where RETURNING +is not supported, and where RETURNING is supported SQLAlchemy will use that. + +However, for non-integer values, as well as for integer values that must be +explicitly linked to a sequence or other triggered routine, the server default +generation must be marked in the table metadata. + +For an explicit sequence as we use with Oracle, this just means we are using +the :class:`.Sequence` construct:: + + class MyOracleModel(Base): + __tablename__ = 'my_table' + + id = Column(Integer, Sequence("my_sequence"), primary_key=True) + data = Column(String(50)) + +The INSERT for a model as above on Oracle looks like: + +.. sourcecode:: sql + + INSERT INTO my_table (id, data) VALUES (my_sequence.nextval, :data) RETURNING my_table.id INTO :ret_0 + +Where above, SQLAlchemy renders ``my_sequence.nextval`` for the primary key column +and also uses RETURNING to get the new value back immediately. + +For datatypes that generate values automatically, or columns that are populated +by a trigger, we use :class:`.FetchedValue`. Below is a model that uses a +SQL Server TIMESTAMP column as the primary key, which generates values automatically:: + + class MyModel(Base): + __tablename__ = 'my_table' + + timestamp = Column(TIMESTAMP(), server_default=FetchedValue(), primary_key=True) + +An INSERT for the above table on SQL Server looks like: + +.. sourcecode:: sql + + INSERT INTO my_table OUTPUT inserted.timestamp DEFAULT VALUES + +Case 4: primary key, RETURNING or equivalent is not supported +-------------------------------------------------------------- + +In this area we are generating rows for a database such as SQLite or +more typically MySQL where some means of generating a default is occurring +on the server, but is outside of the database's usual autoincrement routine. +In this case, we have to make sure SQLAlchemy can "pre-execute" the default, +which means it has to be an explicit SQL expression. Again using the example +of TIMESTAMP for MySQL, we unfortunately need to use our own explicit default:: + + class MyModel(Base): + __tablename__ = 'my_table' + + timestamp = Column(TIMESTAMP(), default=func.cast(func.now(), Binary), primary_key=True) + +Where above, we select the "NOW()" function and also cast to binary to +be used with MySQL's TIMESTAMP column, that is in fact a binary datatype. +The SQL generated by the above is: + +.. sourcecode:: sql + + SELECT CAST(now() AS BINARY) AS anon_1 + INSERT INTO my_table (timestamp) VALUES (%s) + (b'2018-08-09 13:08:46',) + +The Core currently does not support a means of fetching the timestamp value +after the fact without using RETURNING, so on MySQL must run a SELECT ahead of +time to pre-select the value. + +.. seealso:: + + :ref:`metadata_defaults_toplevel` + .. _session_partitioning: diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py index e856c6c797..f2b326359e 100644 --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -239,6 +239,10 @@ class Mapper(InspectionAttr): greatly enhance performance for an application that needs frequent access to just-generated server defaults. + .. seealso:: + + :ref:`orm_server_defaults` + .. versionchanged:: 0.9.0 The ``eager_defaults`` option can now make use of :term:`RETURNING` for backends which support it. -- 2.47.2