views:

483

answers:

1

Hi everyone!
I have a simple "Invoices" class with a "Number" attribute that has to be assigned by the application when the user saves an invoice. There are some constraints:

1) the application is a (thin) client-server one, so whatever assigns the number must look out for collisions
2) Invoices has a "version" attribute too, so I can't use a simple DBMS-level autoincrementing field

I'm trying to build this using a custom Type that would kick in every time an invoice gets saved. Whenever process_bind_param is called with a None value, it will call a singleton of some sort to determine the number and avoid collisions. Is this a decent solution? Anyway, I'm having a problem.. Here's my custom Type:

class AutoIncrement(types.TypeDecorator):
   impl = types.Unicode

   def copy(self):
       return AutoIncrement()

   def process_bind_param(self, value, dialect):
       if not value:
           # Must find next autoincrement value
           value = "1" # Test value :)
       return value

My problem right now is that when I save an Invoice and AutoIncrement sets "1" as value for its number, the Invoice instance doesn't get updated with the new number.. Is this expected? Am I missing something? Many thanks for your time!

(SQLA 0.5.3 on Python 2.6, using postgreSQL 8.3)

Edit: Michael Bayer told me that this behaviour is expected, since TypeDecorators don't deal with default values.

+2  A: 

Is there any particular reason you don't just use a default= parameter in your column definition? (This can be an arbitrary Python callable).

def generate_invoice_number():
    # special logic to generate a unique invoice number

class Invoice(DeclarativeBase):
    __tablename__ = 'invoice'
    number = Column(Integer, unique=True, default=generate_invoice_number)
    ...
Rick Copeland
Ouch didn't know you could use a callable there, thanks! I'll try it right away :)
Joril
Say your default callable returns the max of the column in the DB plus one. Is there any way to assert there are no race conditions without relying on a error from the column being unique?
Mike Boers
In that case, you're better off writing your default as an inline SQL expression. This is covered in detail in the SQLAlchemy documentation at http://www.sqlalchemy.org/docs/05/metadata.html#pre-executed-and-inline-sql-expressions
Rick Copeland
You are going to get race conditions even with SQL expressions, it only makes the race window smaller. To avoid races you need locking, either explicit table level or row level via select for update.
Ants Aasma
Are you certain you'll get race conditions if your insert is a single statement (as it will be if you use an SQL expression as a default)? I was under the impression that single statements typically execute atomically.
Rick Copeland
Since only the serverside part of my application has access to the DB, I used a simple locking system.. I have a multithreaded testcase that hammers on the server and then checks the generated values afterwards, it looks ok... Am I missing something? ^^;
Joril