From 37ebd3f2f3ce2f5f35889ca650341995dc753b08 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 16 Dec 2013 18:52:52 -0500 Subject: [PATCH] - fix up the FAQ regarding the "foo_id" issue - add session object states to the glossary Conflicts: doc/build/glossary.rst --- doc/build/faq.rst | 73 +++++-- doc/build/glossary.rst | 473 +++++++++++++++++++++++++++++++++++++++++ 2 files changed, 528 insertions(+), 18 deletions(-) diff --git a/doc/build/faq.rst b/doc/build/faq.rst index e16afb31cb..aed463d3a6 100644 --- a/doc/build/faq.rst +++ b/doc/build/faq.rst @@ -825,42 +825,79 @@ set ``o.foo`` is to do just that - set it!:: Manipulation of foreign key attributes is of course entirely legal. However, setting a foreign-key attribute to a new value currently does not trigger -an "expire" event of the :func:`.relationship` in which it's involved (this may -be implemented in the future). This means +an "expire" event of the :func:`.relationship` in which it's involved. This means that for the following sequence:: o = Session.query(SomeClass).first() - assert o.foo is None + assert o.foo is None # accessing an un-set attribute sets it to None o.foo_id = 7 -``o.foo`` is loaded when we checked it for ``None``. Setting -``o.foo_id=7`` will have the value of "7" as pending, but no flush -has occurred. +``o.foo`` is initialized to ``None`` when we first accessed it. Setting +``o.foo_id = 7`` will have the value of "7" as pending, but no flush +has occurred - so ``o.foo`` is still ``None``:: + + # attribute is already set to None, has not been + # reconciled with o.foo_id = 7 yet + assert o.foo is None For ``o.foo`` to load based on the foreign key mutation is usually achieved naturally after the commit, which both flushes the new foreign key value and expires all state:: - Session.commit() - assert o.foo is + Session.commit() # expires all attributes + + foo_7 = Session.query(Foo).get(7) + + assert o.foo is foo_7 # o.foo lazyloads on access + +A more minimal operation is to expire the attribute individually - this can +be performed for any :term:`persistent` object using :meth:`.Session.expire`:: + + o = Session.query(SomeClass).first() + o.foo_id = 7 + Session.expire(o, ['foo']) # object must be persistent for this + + foo_7 = Session.query(Foo).get(7) + + assert o.foo is foo_7 # o.foo lazyloads on access + +Note that if the object is not persistent but present in the :class:`.Session`, +it's known as :term:`pending`. This means the row for the object has not been +INSERTed into the database yet. For such an object, setting ``foo_id`` does not +have meaning until the row is inserted; otherwise there is no row yet:: + + new_obj = SomeClass() + new_obj.foo_id = 7 + + Session.add(new_obj) + + # accessing an un-set attribute sets it to None + assert new_obj.foo is None -A more minimal operation is to expire the attribute individually. The -:meth:`.Session.flush` is also needed if the object is pending (hasn't been INSERTed yet), -or if the relationship is many-to-one prior to 0.6.5:: + Session.flush() # emits INSERT + # expire this because we already set .foo to None Session.expire(o, ['foo']) - Session.flush() + assert new_obj.foo is foo_7 # now it loads - assert o.foo is -Where above, expiring the attribute triggers a lazy load on the next access of ``o.foo``. +.. topic:: Attribute loading for non-persistent objects -The object does not "autoflush" on access of ``o.foo`` if the object is pending, since -it is usually desirable that a pending object doesn't autoflush prematurely and/or -excessively, while its state is still being populated. + One variant on the "pending" behavior above is if we use the flag + ``load_on_pending`` on :func:`.relationship`. When this flag is set, the + lazy loader will emit for ``new_obj.foo`` before the INSERT proceeds; another + variant of this is to use the :meth:`.Session.enable_relationship_loading` + method, which can "attach" an object to a :class:`.Session` in such a way that + many-to-one relationships load as according to foreign key attributes + regardless of the object being in any particular state. + Both techniques are **not recommended for general use**; they were added to suit + specfic programming scenarios encountered by users which involve the repurposing + of the ORM's usual object states. -Also see the recipe `ExpireRelationshipOnFKChange `_, which features a mechanism to actually achieve this behavior to a reasonable degree in simple situations. +The recipe `ExpireRelationshipOnFKChange `_ features an example using SQLAlchemy events +in order to coordinate the setting of foreign key attributes with many-to-one +relationships. Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword? --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- diff --git a/doc/build/glossary.rst b/doc/build/glossary.rst index 278496d078..44aa589452 100644 --- a/doc/build/glossary.rst +++ b/doc/build/glossary.rst @@ -562,3 +562,476 @@ Glossary interface for returning columns. The ORM also includes many optimizations that make use of RETURNING when available. + one to many + A style of :func:`~sqlalchemy.orm.relationship` which links + the primary key of the parent mapper's table to the foreign + key of a related table. Each unique parent object can + then refer to zero or more unique related objects. + + The related objects in turn will have an implicit or + explicit :term:`many to one` relationship to their parent + object. + + An example one to many schema (which, note, is identical + to the :term:`many to one` schema): + + .. sourcecode:: sql + + CREATE TABLE department ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE employee ( + id INTEGER PRIMARY KEY, + name VARCHAR(30), + dep_id INTEGER REFERENCES department(id) + ) + + The relationship from ``department`` to ``employee`` is + one to many, since many employee records can be associated with a + single department. A SQLAlchemy mapping might look like:: + + class Department(Base): + __tablename__ = 'department' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + employees = relationship("Employee") + + class Employee(Base): + __tablename__ = 'employee' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + dep_id = Column(Integer, ForeignKey('department.id')) + + .. seealso:: + + :term:`relationship` + + :term:`many to one` + + :term:`backref` + + many to one + A style of :func:`~sqlalchemy.orm.relationship` which links + a foreign key in the parent mapper's table to the primary + key of a related table. Each parent object can + then refer to exactly zero or one related object. + + The related objects in turn will have an implicit or + explicit :term:`one to many` relationship to any number + of parent objects that refer to them. + + An example many to one schema (which, note, is identical + to the :term:`one to many` schema): + + .. sourcecode:: sql + + CREATE TABLE department ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE employee ( + id INTEGER PRIMARY KEY, + name VARCHAR(30), + dep_id INTEGER REFERENCES department(id) + ) + + + The relationship from ``employee`` to ``department`` is + many to one, since many employee records can be associated with a + single department. A SQLAlchemy mapping might look like:: + + class Department(Base): + __tablename__ = 'department' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + + class Employee(Base): + __tablename__ = 'employee' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + dep_id = Column(Integer, ForeignKey('department.id')) + department = relationship("Department") + + .. seealso:: + + :term:`relationship` + + :term:`one to many` + + :term:`backref` + + backref + bidirectional relationship + An extension to the :term:`relationship` system whereby two + distinct :func:`~sqlalchemy.orm.relationship` objects can be + mutually associated with each other, such that they coordinate + in memory as changes occur to either side. The most common + way these two relationships are constructed is by using + the :func:`~sqlalchemy.orm.relationship` function explicitly + for one side and specifying the ``backref`` keyword to it so that + the other :func:`~sqlalchemy.orm.relationship` is created + automatically. We can illustrate this against the example we've + used in :term:`one to many` as follows:: + + class Department(Base): + __tablename__ = 'department' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + employees = relationship("Employee", backref="department") + + class Employee(Base): + __tablename__ = 'employee' + id = Column(Integer, primary_key=True) + name = Column(String(30)) + dep_id = Column(Integer, ForeignKey('department.id')) + + A backref can be applied to any relationship, including one to many, + many to one, and :term:`many to many`. + + .. seealso:: + + :term:`relationship` + + :term:`one to many` + + :term:`many to one` + + :term:`many to many` + + many to many + A style of :func:`sqlalchemy.orm.relationship` which links two tables together + via an intermediary table in the middle. Using this configuration, + any number of rows on the left side may refer to any number of + rows on the right, and vice versa. + + A schema where employees can be associated with projects: + + .. sourcecode:: sql + + CREATE TABLE employee ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE project ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE employee_project ( + employee_id INTEGER PRIMARY KEY, + project_id INTEGER PRIMARY KEY, + FOREIGN KEY employee_id REFERENCES employee(id), + FOREIGN KEY project_id REFERENCES project(id) + ) + + Above, the ``employee_project`` table is the many-to-many table, + which naturally forms a composite primary key consisting + of the primary key from each related table. + + In SQLAlchemy, the :func:`sqlalchemy.orm.relationship` function + can represent this style of relationship in a mostly + transparent fashion, where the many-to-many table is + specified using plain table metadata:: + + class Employee(Base): + __tablename__ = 'employee' + + id = Column(Integer, primary_key) + name = Column(String(30)) + + projects = relationship( + "Project", + secondary=Table('employee_project', Base.metadata, + Column("employee_id", Integer, ForeignKey('employee.id'), + primary_key=True), + Column("project_id", Integer, ForeignKey('project.id'), + primary_key=True) + ), + backref="employees" + ) + + class Project(Base): + __tablename__ = 'project' + + id = Column(Integer, primary_key) + name = Column(String(30)) + + Above, the ``Employee.projects`` and back-referencing ``Project.employees`` + collections are defined:: + + proj = Project(name="Client A") + + emp1 = Employee(name="emp1") + emp2 = Employee(name="emp2") + + proj.employees.extend([emp1, emp2]) + + .. seealso:: + + :term:`association relationship` + + :term:`relationship` + + :term:`one to many` + + :term:`many to one` + + relationship + relationships + A connecting unit between two mapped classes, corresponding + to some relationship between the two tables in the database. + + The relationship is defined using the SQLAlchemy function + :func:`~sqlalchemy.orm.relationship`. Once created, SQLAlchemy + inspects the arguments and underlying mappings involved + in order to classify the relationship as one of three types: + :term:`one to many`, :term:`many to one`, or :term:`many to many`. + With this classification, the relationship construct + handles the task of persisting the appropriate linkages + in the database in response to in-memory object associations, + as well as the job of loading object references and collections + into memory based on the current linkages in the + database. + + .. seealso:: + + :ref:`relationship_config_toplevel` + + association relationship + A two-tiered :term:`relationship` which links two tables + together using an association table in the middle. The + association relationship differs from a :term:`many to many` + relationship in that the many-to-many table is mapped + by a full class, rather than invisibly handled by the + :func:`sqlalchemy.orm.relationship` construct as in the case + with many-to-many, so that additional attributes are + explicitly available. + + For example, if we wanted to associate employees with + projects, also storing the specific role for that employee + with the project, the relational schema might look like: + + .. sourcecode:: sql + + CREATE TABLE employee ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE project ( + id INTEGER PRIMARY KEY, + name VARCHAR(30) + ) + + CREATE TABLE employee_project ( + employee_id INTEGER PRIMARY KEY, + project_id INTEGER PRIMARY KEY, + role_name VARCHAR(30), + FOREIGN KEY employee_id REFERENCES employee(id), + FOREIGN KEY project_id REFERENCES project(id) + ) + + A SQLAlchemy declarative mapping for the above might look like:: + + class Employee(Base): + __tablename__ = 'employee' + + id = Column(Integer, primary_key) + name = Column(String(30)) + + + class Project(Base): + __tablename__ = 'project' + + id = Column(Integer, primary_key) + name = Column(String(30)) + + + class EmployeeProject(Base): + __tablename__ = 'employee_project' + + employee_id = Column(Integer, ForeignKey('employee.id'), primary_key=True) + project_id = Column(Integer, ForeignKey('project.id'), primary_key=True) + role_name = Column(String(30)) + + project = relationship("Project", backref="project_employees") + employee = relationship("Employee", backref="employee_projects") + + + Employees can be added to a project given a role name:: + + proj = Project(name="Client A") + + emp1 = Employee(name="emp1") + emp2 = Employee(name="emp2") + + proj.project_employees.extend([ + EmployeeProject(employee=emp1, role="tech lead"), + EmployeeProject(employee=emp2, role="account executive") + ]) + + .. seealso:: + + :term:`many to many` + + constraint + constraints + constrained + Rules established within a relational database that ensure + the validity and consistency of data. Common forms + of constraint include :term:`primary key constraint`, + :term:`foreign key constraint`, and :term:`check constraint`. + + candidate key + + A :term:`relational algebra` term referring to an attribute or set + of attributes that form a uniquely identifying key for a + row. A row may have more than one candidate key, each of which + is suitable for use as the primary key of that row. + The primary key of a table is always a candidate key. + + .. seealso:: + + :term:`primary key` + + http://en.wikipedia.org/wiki/Candidate_key + + primary key + primary key constraint + + A :term:`constraint` that uniquely defines the characteristics + of each :term:`row`. The primary key has to consist of + characteristics that cannot be duplicated by any other row. + The primary key may consist of a single attribute or + multiple attributes in combination. + (via Wikipedia) + + The primary key of a table is typically, though not always, + defined within the ``CREATE TABLE`` :term:`DDL`: + + .. sourcecode:: sql + + CREATE TABLE employee ( + emp_id INTEGER, + emp_name VARCHAR(30), + dep_id INTEGER, + PRIMARY KEY (emp_id) + ) + + .. seealso:: + + http://en.wikipedia.org/wiki/Primary_Key + + foreign key constraint + A referential constraint between two tables. A foreign key is a field or set of fields in a + relational table that matches a :term:`candidate key` of another table. + The foreign key can be used to cross-reference tables. + (via Wikipedia) + + A foreign key constraint can be added to a table in standard + SQL using :term:`DDL` like the following: + + .. sourcecode:: sql + + ALTER TABLE employee ADD CONSTRAINT dep_id_fk + FOREIGN KEY (employee) REFERENCES department (dep_id) + + .. seealso:: + + http://en.wikipedia.org/wiki/Foreign_key_constraint + + check constraint + + A check constraint is a + condition that defines valid data when adding or updating an + entry in a table of a relational database. A check constraint + is applied to each row in the table. + + (via Wikipedia) + + A check constraint can be added to a table in standard + SQL using :term:`DDL` like the following: + + .. sourcecode:: sql + + ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); + + .. seealso:: + + http://en.wikipedia.org/wiki/Check_constraint + + unique constraint + unique key index + A unique key index can uniquely identify each row of data + values in a database table. A unique key index comprises a + single column or a set of columns in a single database table. + No two distinct rows or data records in a database table can + have the same data value (or combination of data values) in + those unique key index columns if NULL values are not used. + Depending on its design, a database table may have many unique + key indexes but at most one primary key index. + + (via Wikipedia) + + .. seealso:: + + http://en.wikipedia.org/wiki/Unique_key#Defining_unique_keys + + transient + This describes one of the four major object states which + an object can have within a :term:`session`; a transient object + is a new object that doesn't have any database identity + and has not been associated with a session yet. When the + object is added to the session, it moves to the + :term:`pending` state. + + .. seealso:: + + :ref:`session_object_states` + + pending + This describes one of the four major object states which + an object can have within a :term:`session`; a pending object + is a new object that doesn't have any database identity, + but has been recently associated with a session. When + the session emits a flush and the row is inserted, the + object moves to the :term:`persistent` state. + + .. seealso:: + + :ref:`session_object_states` + + persistent + This describes one of the four major object states which + an object can have within a :term:`session`; a persistent object + is an object that has a database identity (i.e. a primary key) + and is currently associated with a session. Any object + that was previously :term:`pending` and has now been inserted + is in the persistent state, as is any object that's + been loaded by the session from the database. When a + persistent object is removed from a session, it is known + as :term:`detached`. + + .. seealso:: + + :ref:`session_object_states` + + detached + This describes one of the four major object states which + an object can have within a :term:`session`; a detached object + is an object that has a database identity (i.e. a primary key) + but is not associated with any session. An object that + was previously :term:`persistent` and was removed from its + session either because it was expunged, or the owning + session was closed, moves into the detached state. + The detached state is generally used when objects are being + moved between sessions or when being moved to/from an external + object cache. + + .. seealso:: + + :ref:`session_object_states` -- 2.47.3