Edgewall Software

Changes between Version 27 and Version 28 of TracDev/DatabaseApi


Ignore:
Timestamp:
Sep 8, 2012, 2:32:11 PM (12 years ago)
Author:
Christian Boos
Comment:

further clarifications

Legend:

Unmodified
Added
Removed
Modified

  • TracDev/DatabaseApi

    v27 v28  
    11= Trac SQL Database API =
    22
    3 Trac uses a very thin layer on top of the standard [http://www.python.org/peps/pep-0249.html Python Database API 2.0] for interfacing with supported relational database systems such as [http://sqlite.org/ SQLite] or [http://www.postgresql.org/ PostgreSQL].
    4 
    5  ''Note that Trac prior to version 0.9 used the PySQLite APIs directly, and has no support for other database systems. This document describes the thin DB API abstraction layer introduced in version 0.9.''
     3Trac uses a very thin layer on top of the standard [http://www.python.org/peps/pep-0249.html Python Database API 2.0] for interfacing with supported relational database systems such as [http://sqlite.org/ SQLite], [http://www.postgresql.org/ PostgreSQL] or the MySqlDb systems.
    64
    75You can find the specifics of the database API in the [source:/trunk/trac/db/ trac.db] package. This package provides:
    … …  
    108 * Selection of DB modules based of connection URIs.
    119
     10
    1211== Accessing the Database ==
    1312
    1413The DB API has evolved quite a lot in the recent versions of Trac, and the usage is substantially different whether you're working with Trac 0.11, 0.12 or 1.0 and up.
    1514
     15If you don't need to be encumbered with the legacy styles, skip directly to the [#Trac1.0API] style described below.
    1616
    1717=== API before Trac 0.12
    1818
    19 //This is how things used to be for Trac 0.5 - 0.11, and is still supported in Trac 0.12, but in new code this style should be definitely **not** used, as it introduces all the problems that the new styles are solving (see next sections for details). //
     19//This is how things used to be for Trac 0.5 - 0.11, and is still supported in Trac 0.12, but in new code this style should definitely **not** be used, as it introduces all the problems that the new styles are solving (see next sections for details). //
    2020
    2121Code accessing the database in Trac go through this layer simply by using the {{{Environment}}} method {{{get_db_cnx()}}} in order to get a pooled database connection.  A database cursor can be obtained from the pooled database connection, and the code uses the cursor in conjunction with SQL statements to implement the behavior. 
    … …  
    9191This means that either the ''whole'' (outer) transaction is committed or aborted. So even if the nested transaction succeeded but the outer transaction fails (an exception is raised at the line with the "commit or rollback" comment), the ''whole'' transaction will be rolled back (i.e. including the changes made by the nested transaction).
    9292
    93 ''Note:'' Using `env.get_read_db()` within a transaction reuses the same connection as used for the connection. So uncommitted changes made by the transaction will already be visible to the caller of `get_read_db()` (but not outside of the transaction - that is in another thread).
    94 
    95 ''Note:'' Uncommited changes of a transaction are only visible to nested transactions in the same thread. Different threads use different database connections and therefore different transactions.
    96 
    97 === Trac 1.0 API #Trac0.13API
    98 
     93''Notes:''
     94 - While still possible in the API (for backward compatibility reasons), you should **not** call `commit()` yourself in transaction; not only does this happen automatically at the proper time (as explained above), if you'd call it yourself you will risk to commit from within a nested transaction, possibly leading to inconsistent state of the DB
     95 - Using `env.get_read_db()` within a transaction reuses the same connection as used for the connection. So uncommitted changes made by the transaction will already be visible to the caller of `get_read_db()` (but not outside of the transaction - that is in another thread).
     96 - Uncommited changes of a transaction are only visible to nested transactions in the same thread. Different threads use different database connections and therefore different transactions (well, to be more precise, the exact detail of what is visible to other threads is backend specific and depends to the //isolation level// used)
     97
     98=== Trac 1.0 API
     99[=#Trac0.13API]
    99100// This style is supported for version 1.0.x, 1.1.x and will be supported beyond as well. //
    100101
    … …  
    111112}}}
    112113
    113 It does essentially the same thing as the 0.12 version above in a terser way.
     114It does essentially the same thing as the 0.12 version above in a terser way. Nested transactions work the way you'd expect (only the outermost one will actually do the `commit()`, upon normal exit) so, again, don't call `commit()` by yourself.
    114115
    115116**The use of `env.get_db_cnx()` is now strongly discouraged.**
    … …  
    166167
    167168=== Configuration
    168 The {{{get_db_cnx}}} method looks at the value of the {{{database}}} configuration option in [wiki:TracIni trac.ini], which should contain a database connection URI. The default value for this option tells Trac to use an SQLite database inside the environment directory:
     169For determining which database has to be used, Trac looks at the value of the {{{database}}} configuration option in [wiki:TracIni trac.ini], which should contain a database connection URI. The default value for this option tells Trac to use an SQLite database inside the environment directory:
    169170
    170171{{{
    … …  
    177178=== Pooled Connections ===
    178179
    179 Trac used to operate the following way:
     180Prior to version 1.0, Trac used to operate the following way:
    180181 The {{{Environment}}} method {{{get_db_cnx()}}} returns a connection from the pool of connections.  This connection needs to be returned, and Trac is written so that the return will happen automatically by the garbage collector if the code is written to follow a simple rule.  When the garbage collector determines the pooled database connection is no longer being used, its `__del__` method will return the pooled connection to the pool for reuse.  If you have set a lexical variable in the function's body to the pooled connection, this typically occurs when the function is returning.  In the example above of {{{myFunc}}} it occurs at the return statement since {{{db}}} is a variable local to {{{myFunc}}}
    181182
    182 With the context managers introduced in Trac 0.13, we're able to return this Connection to the pool in a much more robust and direct way:
     183With the context managers introduced in Trac 1.0, we're able to return this Connection to the pool in a much more robust and direct way:
    183184 When the control flow exits a context manager (either `Environment.db_query` or `Environment.db_transaction`), and if that context manager is the "outermost" one in case multiple contexts where nested, then the `Connection` is immediately returned to the pool, regardless of the behavior of the garbage collector.
    184185