the current behavior of SA is to utilize the databases "implicit" PK generation feature *if one is available*. the three databases that I know have this are sqlite (send None in to a PK INSERT, it gets generated), MySQL (AUTOINCREMENT) and Postgres (SERIAL/BIGSERIAL, which results in the creation of a sequence with a naming scheme). the other database im familiar with, Oracle, does not have such a feature. So with Oracle, you have to create an explicit Sequence object if you want PKs to get generated automatically upon INSERT, and SA will fire off that sequence. you can put a Sequence on any Column, and it will still work on mysql/sqlite, since in those cases its just ignored, and the normal autoincrementing behavior of those databases is used instead.
so we dont have anything that is creating named sequences automatically. im not sure what MS-SQL's needs are in this department. but the "autoincrement=True" on Column is always like that, it basically means "use the database's auto-incrementing feature, *if available*"...if MS-SQL doesnt have that then you wont get autoincrementing. Now, if we want to say, "SQLAlchemy should automatically create named sequences for databases that use them", thats a change we can look into making, and of course the question becomes "what do we name the sequences", and stuff like that. but at the moment thats not how its designed, so thats probably why its not working. On Jan 9, 2007, at 6:33 PM, Paul Johnston wrote: > > Hi Rick, > > I think we're actually agreeing about almost everything, although I > have > responded to your points individually. Lets see what Michael says > about > this. Actually reading over my post again, my preferred fix was a > little > hidden at the bottom. I will highlight it here: > > The fix that seems logical to me is to put > the code to create an implicit sequence in Column.__init__. It's > down to > the individual engines how they implement this sequence, but its > existence or not is engine independent. > >> If all you're looking for is an implicit generation of an IDENTITY >> column, you can use the autoincrement=True keyword to the Column >> constructor. > > Yep, that's true, but if I do that then session.flush() doesn't fetch > the pk, hence my bug report. > >> But be careful with the assumption of autoincrementing PKs. It's >> perfectly valid to have a PK that is not autoincrementing. That's why >> the pseudo-Sequence() mechanism is there in the first place, to >> distinguish between the two (and to allow the specification of the >> IDENTITY seed value). > > Yep, hence the fairly complicated code in MSSQLSchemaGenerator > > # install a IDENTITY Sequence if we have an implicit > IDENTITY column > if column.primary_key and column.autoincrement and > isinstance(column.type, sqltypes.Integer) and not column.foreign_key: > if column.default is None or (isinstance(column.default, > schema.Sequence) and column.default.optional): > column.sequence = schema.Sequence(column.name + > '_seq') > >> Issuing a spurious "SELECT @@IDENTITY" could be construed as a bit >> risky as well -- that will simply return the last inserted IDENTITY >> value on that DB connection -- which could be from a completely >> different query. Given that SA pools connection, it seems to me that >> this is bad-tasting recipe. > > I agree in principle. In fact, it returns null if your last insert > was a > table without an identity column, at least with SQL Server 2005. But > still, doing this seems risky. > >> I'm not sure I understand the motivation to check each query anew for >> identity keys. What's wrong with checking for them only at table >> definition time? > > Exactly, hence my last suggestion to move the sequence generation to > Column.__init__. The thing is, that's just not how SA is setup at the > moment, so this will need a little jiggery pokery. > > Regards, > > Paul > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
