95
95
class which each represent a particular database column
96
96
or relationship to a related class.
99
A mapping between Python objects and their database identities.
100
The identity map is a collection that's associated with an
101
ORM :term:`session` object, and maintains a single instance
102
of every database object keyed to its identity. The advantage
103
to this pattern is that all operations which occur for a particular
104
database identity are transparently coordinated onto a single
105
object instance. When using an identity map in conjunction with
106
an :term:`isolated` transaction, having a reference
107
to an object that's known to have a particular primary key can
108
be considered from a practical standpoint to be a
109
proxy to the actual database row.
113
Martin Fowler - Identity Map - http://martinfowler.com/eaaCatalog/identityMap.html
100
117
In object relational mapping, a "lazy load" refers to an
411
438
query via its ``FROM``
412
439
clause is not possible, because the correlation can only proceed once the
413
440
original source rows from the enclosing statement's FROM clause are available.
444
An acronym for "Atomicity, Consistency, Isolation,
445
Durability"; a set of properties that guarantee that
446
database transactions are processed reliably.
459
http://en.wikipedia.org/wiki/ACID_Model
462
Atomicity is one of the components of the :term:`ACID` model,
463
and requires that each transaction is "all or nothing":
464
if one part of the transaction fails, the entire transaction
465
fails, and the database state is left unchanged. An atomic
466
system must guarantee atomicity in each and every situation,
467
including power failures, errors, and crashes.
474
http://en.wikipedia.org/wiki/Atomicity_(database_systems)
477
Consistency is one of the compoments of the :term:`ACID` model,
478
and ensures that any transaction will
479
bring the database from one valid state to another. Any data
480
written to the database must be valid according to all defined
481
rules, including but not limited to :term:`constraints`, cascades,
482
triggers, and any combination thereof.
489
http://en.wikipedia.org/wiki/Consistency_(database_systems)
493
The isolation property of the :term:`ACID` model
494
ensures that the concurrent execution
495
of transactions results in a system state that would be
496
obtained if transactions were executed serially, i.e. one
497
after the other. Each transaction must execute in total
498
isolation i.e. if T1 and T2 execute concurrently then each
499
should remain independent of the other.
506
http://en.wikipedia.org/wiki/Isolation_(database_systems)
509
Durability is a property of the :term:`ACID` model
510
which means that once a transaction has been committed,
511
it will remain so, even in the event of power loss, crashes,
512
or errors. In a relational database, for instance, once a
513
group of SQL statements execute, the results need to be stored
514
permanently (even if the database crashes immediately
522
http://en.wikipedia.org/wiki/Durability_(database_systems)
525
This is a non-SQL standard clause provided in various forms by
526
certain backends, which provides the service of returning a result
527
set upon execution of an INSERT, UPDATE or DELETE statement. Any set
528
of columns from the matched rows can be returned, as though they were
529
produced from a SELECT statement.
531
The RETURNING clause provides both a dramatic performance boost to
532
common update/select scenarios, including retrieval of inline- or
533
default- generated primary key values and defaults at the moment they
534
were created, as well as a way to get at server-generated
535
default values in an atomic way.
537
An example of RETURNING, idiomatic to Postgresql, looks like::
539
INSERT INTO user_account (name) VALUES ('new name') RETURNING id, timestamp
541
Above, the INSERT statement will provide upon execution a result set
542
which includes the values of the columns ``user_account.id`` and
543
``user_account.timestamp``, which above should have been generated as default
544
values as they are not included otherwise (but note any series of columns
545
or SQL expressions can be placed into RETURNING, not just default-value columns).
547
The backends that currently support
548
RETURNING or a similar construct are Postgresql, SQL Server, Oracle,
549
and Firebird. The Postgresql and Firebird implementations are generally
550
full featured, whereas the implementations of SQL Server and Oracle
551
have caveats. On SQL Server, the clause is known as "OUTPUT INSERTED"
552
for INSERT and UPDATE statements and "OUTPUT DELETED" for DELETE statements;
553
the key caveat is that triggers are not supported in conjunction with this
554
keyword. On Oracle, it is known as "RETURNING...INTO", and requires that the
555
value be placed into an OUT paramter, meaning not only is the syntax awkward,
556
but it can also only be used for one row at a time.
558
SQLAlchemy's :meth:`.UpdateBase.returning` system provides a layer of abstraction
559
on top of the RETURNING systems of these backends to provide a consistent
560
interface for returning columns. The ORM also includes many optimizations
561
that make use of RETURNING when available.