= Trac SQL Database API =

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].

 ''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.''

You can find the specifics of the database API in the [source:/trunk/trac/db/ trac.db] package. This package provides:
 * Simple pooling of database connections
 * Iterable cursors
 * Selection of DB modules based of connection URIs.

== Accessing the Database ==
=== API before Trac 0.12

Code 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.  

{{{
#!python
from trac.env import Environment
def myFunc():
    env = Environment('/path/to/projenv')
    db = env.get_db_cnx()
    cursor = db.cursor()
    # Execute some SQL statements

    db.commit()
    return
}}}

Note that you should always make sure that `db` won't get garbage collected while  `cursor` is still used, as the collection will do a rollback and close the cursors 
(avoid in particular doing `cursor = env.get_db_cnx().cursor()`, see r8878).

=== Trac 0.12 API

As we dropped support for Python 2.3, we could make use of decorators to get rid of some of the boilerplate code.

{{{
#!python
from trac.env import Environment
def myFunc():
    env = Environment('/path/to/projenv')
    @with_transaction(env)
    def do_something(db):
        cursor = db.cursor()
        # Execute some SQL statements

    return
}}}

This is slightly simpler and more robust than the previous API, as not only the `commit()` is performed in any successful case (i.e. the `Execute some SQL statements` code can `return` at any 
place), but a `rollback` will be performed should an exception be triggered in that block.

See more details in the [../ApiChanges/0.12#with_transaction] paragraph.

=== Trac 0.13 API

As we dropped support for Python 2.4, we could simplify the code a bit more by using the `with` keyword and context managers:

{{{
#!python
from trac.env import Environment
def myFunc():
    env = Environment('/path/to/projenv')
    with env.db_transaction as db:
        cursor = db.cursor()
        # Execute some SQL statements

    return
}}}

It does essentially the same thing as the 0.12 version above in a terser way.

=== Configuration
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:

{{{
[trac]
database = sqlite:db/trac.db
}}}

 ''The connection URI syntax has been designed to be compatible with that provided by [http://sqlobject.org/docs/SQLObject.html#declaring-the-class SQLObject] (see also the Wiki page on SQLObject [http://wiki.sqlobject.org/connections.html connections]). The only supported URI schemes at this point are {{{sqlite}}} and {{{postgres}}}.''

=== Pooled Connections ===

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}}}

== Rules for DB API Usage ==

Different DB API modules have different ways to pass parameters to the cursors' {{{execute}}} method, and different ways to access query results. To keep the database API as thin as possible, the Trac team has decided to use a relatively common subset in all database code.

=== Parameter passing ===

Always use the "format" parameter style, and always with {{{%s}}} (because that's the only type that pyPgSQL supports). Statement parameters always need to be passed into execute as an actual sequence (list or tuple).

So the following statements are okay:
{{{
#!python
cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", [thename])
cursor.execute("SELECT id FROM ticket WHERE time>=%s AND time<=%s", (start, stop))

}}}

The following uses are '''not''' okay:
{{{
#!python
cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=?", thename)
cursor.execute("SELECT id FROM ticket WHERE time>=%i AND time<=%i", start, stop)

}}}

At any cost, try avoiding the use of [http://docs.python.org/lib/typesseq-strings.html string formatting] to get values into the SQL statement. The database automatically escapes values you pass using {{{execute()}}} arguments, the same is not true if you use string formatting, opening your code up to [http://en.wikipedia.org/wiki/SQL_injection SQL injection] attacks.

On the other hand, you '''must''' use string formatting to dynamically specify names of tables or columns, i.e. anything that is not a value as such:

{{{
#!python
cursor.execute("SELECT time FROM %s WHERE name=%%s" % table, (thename,))

}}}

=== Retrieving results ===

For convenience, cursors returned by the database connection object are iterable after having executed an SQL query. Individual fields in result rows may only be accessed using integer indices:
{{{
#!python
cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", (thename,))
for row in cursor:
    print 'Author: %s (%s)' % (row[0], row[1])
    print row[2]
}}}

Accessing fields using the column names is not supported by all database modules, so it should not be used. Automatically unpacking rows into tuples of named variables often provides better readability:
{{{
#!python
cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s", (thename,))
for author, ipnr, comment in cursor:
    print 'Author: %s (%s)' % (author, ipnr)
    print comment
}}}

== Guidelines for SQL Statements ==

As you may know, support for SQL varies among different database systems. The requirements of Trac are relatively trivial, so we try to stick to a common subset that is supported by the majority of databases.

 ''TODO: Need content''

----
See also: TracDev, TracDev/DatabaseSchema, TracDev/DatabaseUpgrades, TracDev/CodingStyle