[DB-SIG] Optional DB API Extensions

[Stuart Bishop]

On Thursday, October 25, 2001, at 09:11  PM, Federico Di Gregorio wrote:
> this is, imo, wrong. you can expect quote() to be able to cope with
> *any* object. lets stick to the dbapi official types:
>
> 	>>> print con.quote(module.TimestampFromTicks(time.time()))
>         TO_DATE('01-Jan-2001 12:01','DD-MMM-YYYY H24:MI')

Yup. My mistake. int, float, string, date for sure. Possibly others 
depending
on the driver, such as raw.

> but we need dictionaries able to cope with multiple equal keys (some db
> allow multiple columns with the same name). also, should the keys be
> compared ci or cs?

I would keep it simple - dictionary keys are as taken from 
cursor.description,
and behavior undefined if your SQL manages to return columns with the 
same
name. I havn't used a DB that actually lets me do this that I know of.

> i am working on this for psycopg. it is somewhat simplier that your
> proposal, just a way to ask the db for a given isolation level. or are
> isolation levels defined at the SQL standard level?

The transaction isolation levels are defined by SQL99 (I think - I 
cheated
and used the JDBC docs rather than wade through the official specs. The
proposal was pretty much identical to what JDBC offers in fact).

What I had proposed was fairly simple when summarised.
Methods:
     con.default_transaction_level
     con.set_transaction_level(lev)
     lev is one of:
         TRANSACTION_NONE
         TRANSACTION_READ_UNCOMMITTED
         TRANSACTION_READ_COMMITTED
         TRANSACTION_REPEATABLE_READ

     con.set_transaction_level sets the level as requested, or a higher 
level if it can't.

I think most drivers would simply stay in TRANSACTION_READ_UNCOMMITTED 
mode and
raise an exception if an attempt is made to set it higher.




Here are the relevant definitions and such if anyone is interested...

     Transaction Isolation Levels

         Dirty reads occur when transactions are allowed to see 
uncommitted
         changes to the data. In other words, changes made inside a 
transaction are
         visible outside the transaction before they are committed. If 
the changes
         are rolled back instead of being committed, it is possible for 
other
         transactions to have done work based on incorrect, transient 
data.

         Nonrepeatable reads occur when:
           a. Transaction A reads a row
           b. Transaction B changes the row
           c. Transaction A reads the same row a second time and gets
              different results

         Phantom reads occur hen:
           a. Transaction A reads all rows that satisfy a WHERE condition
           b. Transaction B inserts an additional row that satisfies the
              same condition.
           c. Transaction A reevaluates the WHERE condition and picks up 
the
              additional 'phantom' row.

         The following symbolic constants are defined to describe the
         four transaction isolation levels defined by SQL99. Note that
         they are in numeric order so comparison operators can be
         safely used to test for restrictivness. Note that these 
definitions
         are taken from the JDBC API 3.0 documentation by Sun.

         TRANSACTION_NONE

             No transaction isolation is guarenteed.

         TRANSACTION_READ_UNCOMMITTED

             Allows transactions to see uncommitted changes to the data.
             This means that dirty reads, nonrepeatable reads, and
             phantom reads are possible.

         TRANSACTION_READ_COMMITTED

             Means that any changes made inside a transaction are not 
visible
             outside the transaction until the transaction is committed. 
This
             prevents dirty reads, but nonrepeatable reads and phantom
             reads are still possible.

         TRANSACTION_REPEATABLE_READ

             Disallows dirty reads and nonrepeatable reads. Phantom read 
are
             still possible.

         TRANSACTION_SERIALIZABLE

             Specifies that dirty reads, nonrepeatable reads, and phantom
             reads are prevented.



--
Stuart Bishop <zen@shangri-la.dropbear.id.au>
http://shangri-la.dropbear.id.au/