Changes between Version 31 and Version 32 of TracDev/DatabaseApi
- Timestamp:
- Jan 18, 2016, 2:21:39 PM (8 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
TracDev/DatabaseApi
v31 v32 16 16 === API before Trac 0.12 17 17 18 //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). //19 20 Code accessing the database in Trac go through this layer simply by using the {{{Environment}}} method {{{get_db_cnx()}}} in orderto 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:18 //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. // 19 20 Code accessing the database in Trac go through this layer simply by using the {{{Environment}}} method {{{get_db_cnx()}}} 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: 21 21 22 22 {{{#!python … … 31 31 }}} 32 32 33 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 34 (avoid in particular doing `cursor = env.get_db_cnx().cursor()`, see r8878).33 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. 34 Avoid in particular doing `cursor = env.get_db_cnx().cursor()`, see r8878. 35 35 36 36 === Trac 0.12 API 37 37 38 // The 0.12 style can still be used in 1.0, but for new code we encourage you to focus on the Trac version 1.0 and to adopt the even simpler 1.0 style (see next section). //38 // The 0.12 style can still be used in 1.0, but for new code we encourage you to focus on the Trac version 1.0 and to adopt the even simpler 1.0 style. See next sections for details. // 39 39 40 40 As support for Python 2.3 has been dropped, we could make use of decorators to get rid of some of the boilerplate code: … … 51 51 }}} 52 52 53 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 54 place), but a `rollback` will be performed should an exception be triggered in that block. 53 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. 55 54 56 55 See more details in the [../ApiChanges/0.12#with_transaction] paragraph. … … 89 88 - Do **not** call `commit()` yourself in a transaction, even though this is still possible in the API for backward compatibility reasons. Not only is a commit performed automatically at the proper time (as explained above), but if you call it yourself, you risk to commit from within a nested transaction, possibly leading to an inconsistent state of the database. 90 89 - 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). 91 - 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. To be more precise, the exact detail of what is visible to other threads is backendspecific and depends to the //isolation level// used.90 - 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. To be more precise, the exact detail of what is visible to other threads is database specific and depends to the //isolation level// used. 92 91 93 92 === Trac 1.0 API … … 107 106 }}} 108 107 109 It 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.108 It does essentially the same thing as the 0.12 version above in a terser way. Nested transactions work the way you would expect: only the outermost one will actually do the `commit()`, upon normal exit. So, again, don't call `commit()` by yourself. 110 109 111 110 **The use of `env.get_db_cnx()` is now deprecated.** … … 126 125 }}} 127 126 128 This one enforces the notion of read-access, because the `db` connection bound to the context doesn't support the `commit()` or `rollback()` methods. As there's no `commit()` upon exit, one could question the usefulness of this syntactic construct. The main interest is in better locality of the connection: in orderto improve concurrency, the lifetime of a connection wrapper has to be as short as possible (see #3446). Therefore, we close the connection on exit, making it available to other threads. So even if Python gives you access to the `db` variable after the `with` block (at the //`# (continue)`// line in the above example), you should not (and can't) use it at that point.127 This one enforces the notion of read-access, because the `db` connection bound to the context doesn't support the `commit()` or `rollback()` methods. As there's no `commit()` upon exit, one could question the usefulness of this syntactic construct. The main interest is in better locality of the connection: to improve concurrency, the lifetime of a connection wrapper has to be as short as possible (see #3446). Therefore, we close the connection on exit, making it available to other threads. So even if Python gives you access to the `db` variable after the `with` block (at the //`# (continue)`// line in the above example), you should not (and can't) use it at that point. 129 128 130 129 Actually, if you don't need to do anything fancy with the cursor like calling `db.get_last_id(cursor, ...)`, then you can use a shorter form: … … 167 166 }}} 168 167 169 ''The connection URI syntax has been designed to be compatible with that provided by [http://sqlobject.org/SQLObject.html#declaring-the-class SQLObject] (see also the section on SQLObject [http://sqlobject.org/SQLObject.html#declaring-a-connection connections]). The only supported URI schemes at this point are `sqlite`, `postgres` and `mysql`.''168 ''The connection URI syntax has been designed to be compatible with that provided by [http://sqlobject.org/SQLObject.html#declaring-the-class SQLObject]. See also the section on SQLObject [http://sqlobject.org/SQLObject.html#declaring-a-connection connections]. The only supported URI schemes at this point are `sqlite`, `postgres` and `mysql`.'' 170 169 171 170 === Pooled Connections … … 219 218 }}} 220 219 221 At any cost, avoid [http://docs.python.org/library/stdtypes.html#string-formatting string formatting] to get values into the SQL statement. The database automatically escapes values you pass using {{{execute()}}} arguments, but 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.220 At any cost, avoid [http://docs.python.org/library/stdtypes.html#string-formatting string formatting] to get values into the SQL statement. The database automatically escapes values you pass using {{{execute()}}} arguments, but the same is not true if you use string formatting, opening your code up to [wikipedia:SQL_injection SQL injection] attacks. 222 221 223 222 On the other hand, you '''must''' use string formatting to dynamically specify names of tables or columns, ie anything that is not a value as such:
