Oops, sorry about the garbage at the end
On 1/10/07, Rick Morrison <[EMAIL PROTECTED]> wrote:
>
> A bit of background might be helpful here. The psuedo-sequence mechanism
> that the MSSQL modules uses may seem a bit complex, but by leveraging the
> same Sequence() mechanism that the PG and Oracle modules use, it allows a
> single SA Table definition to correctly create() with the proper sequences
> on PostgresSQL (via SERIAL, a kind of automatic named sequence), Oracle (via
> named sequences), and MSSQL (via the weird IDENTITY stuff they took from
> Sybase).
>
> If you really need an implicit PK, I think best way to handle this is to
> leave the Sequence stuff in place so that it continues to play that role,
> and to put a hook into the Column initialization as Paul has suggested. Not
> sure __init__ is the right place, but that's a nit. The MSSQL module could
> override the hook method and implement the same Sequence logic as it does
> for the SchemaGenerator to create implicit autoincrementing Integer PKs.
>
> That, or simply use the Sequence() that's already in place. Instead of:
>
> users = Table('users', metadata,
> Column('user_id', Integer, primary_key=True), # Sequence('paj')
> Column('user_name, String))
>
> use:
>
> users = Table('users', metadata,
> Column('user_id', Integer, Sequence('paj'), primary_key=True),
> Column('user_name, String))
>
> and all the @@IDENTITY machinery will jump into life as expected.
>
> Rick
>
>
>
> users = Table(*'users'*, metadata,
> 8<http://www.sqlalchemy.org/trac/attachment/ticket/415/bug4.py#L8>
> Column(*'user_id'*, Integer, primary_key=True), *# Sequence('paj'), *
> 9<http://www.sqlalchemy.org/trac/attachment/ticket/415/bug4.py#L9>
> Column(*'user_name'*, String))
>
>
>
> The point was the particular app I was writing needed more or less
> transparent access to PostgresSQL, MS-SQL Server and Oracle. Once I found
> that SA had t
>
> On 1/9/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >
> >
> > 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
-~----------~----~----~----~------~----~------~--~---
