Support for Oracle 12c auto increment (IDENTITY) columns?

[Piotr Dobrogost]

Hi all!

At http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#auto-increment-behavior we read that:

"With the Oracle dialect, a sequence must always be explicitly specified to enable autoincrement."

However, starting with version 12c Oracle supports autoincrement columns (see update in this answer http://stackoverflow.com/a/11296469/95735).

Does SQLAlchemy make use of this new feature of Oracle 12c so that it's not necessary to explicitly specify sequence in declaration of table columns (so that single declaration of such column works for Oracle and other databases)? If so, do I have to enable this somehow?

Regards,

Piotr Dobrogost

[Mike Bayer]

We've not started supporting new oracle 12c features as of yet, in this case it might be possible to get it working with some dialect flags since we already use "returning" to get at the newly generated primary key, although testing would be needed and other assumptions in the dialect might get in the way.

Is there a free XE download for 12c yet?   I can do nothing until I can at least install that, unless you were able to work up patches on your end (which would be great anyway because I have no time these days).

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michal Petrucha]

On Wed, Apr 13, 2016 at 07:15:27AM -0400, Mike Bayer wrote:
> We've not started supporting new oracle 12c features as of yet, in this
> case it might be possible to get it working with some dialect flags since
> we already use "returning" to get at the newly generated primary key,
> although testing would be needed and other assumptions in the dialect might
> get in the way.
>
> Is there a free XE download for 12c yet? I can do nothing until I can at
> least install that, unless you were able to work up patches on your end
> (which would be great anyway because I have no time these days).

There is a pre-installed VM with 12c available from
http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

It's ridiculously slow (orders of magnitude slower than PG/MySQL
running on bare metal), but it works...

Cheers,

Michal

[Piotr Dobrogost]

Mike,

Thanks for your reply!

On Wednesday, April 13, 2016 at 1:15:32 PM UTC+2, Mike Bayer wrote:

We've not started supporting new oracle 12c features as of yet, in this case it might be possible to get it working with some dialect flags since we already use "returning" to get at the newly generated primary key, although testing would be needed and other assumptions in the dialect might get in the way.

Which flags do you have in mind? Looking at http://docs.sqlalchemy.org/en/latest/dialects/oracle.html I don't see anything which might be useful to make it work.

I was surprised Oracle needs different syntax with explicit sequence in SA. Having one syntax for auto increment column (primary key) across all backends seems like very important feature to have.  What is the reason there's no Oracle dialect option to generate and use suitable sequence for such column? Is there some recipe solving this problem?

Regards,

Piotr Dobrogost

[David Moore]

--

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

From the license under which you can use this (http://www.oracle.com/technetwork/licenses/standard-license-152015.html):

License Rights and Restrictions
Oracle grants You a nonexclusive, nontransferable, limited license to internally use the Programs, subject to the restrictions stated in this Agreement, only for the purpose of developing, testing, prototyping, and demonstrating Your application and only as long as Your application has not been used for any data processing, business, commercial, or production purposes, and not for any other purpose.

------

I suspect you would not be able to use this for developing and testing sqlalchemy without breaking those terms.  Oracle can get really nasty about using Developer Days when they think you've broken this agreement.

regards,

Dave Moore

[Mike Bayer]

On 04/13/2016 07:50 AM, Piotr Dobrogost wrote:
> Mike,
>
> Thanks for your reply!
>
> On Wednesday, April 13, 2016 at 1:15:32 PM UTC+2, Mike Bayer wrote:
>
> We've not started supporting new oracle 12c features as of yet, in
> this case it might be possible to get it working with some dialect
> flags since we already use "returning" to get at the newly generated
> primary key, although testing would be needed and other assumptions
> in the dialect might get in the way.
>
>
> Which flags do you have in mind? Looking at
> http://docs.sqlalchemy.org/en/latest/dialects/oracle.html I don't see
> anything which might be useful to make it work.

they're module level flags on the dialect class itself like
"postfetch_lastrowid" and "preexecute_autoincrement_sequences"

>
> I was surprised Oracle needs different syntax with explicit sequence in
> SA. Having one syntax for auto increment column (primary key) across all
> backends seems like very important feature to have. What is the reason
> there's no Oracle dialect option to generate and use suitable sequence
> for such column? Is there some recipe solving this problem?

Oracle (and Firebird) is the only database backend that until 12c did
not provide any mechanism for auto-incrementing integer primary key
columns. SQLAlchemy doesn't want to get in the business of inventing an
opinionated system for this. We don't ever dictate any details about
how the schema is to be designed.

>
>
> Regards,
> Piotr Dobrogost
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

[Mike Bayer]

three people chimed in on this thread in 20 minutes. Surely some work
can be done amongst those interested parties (share email addresses!
get on irc!) to try to get this working that I can just review and
merge ? :)




On 04/13/2016 07:57 AM, David Moore wrote:
>
>
> ------------------------------------------------------------------------
>
> *From: *"Piotr Dobrogost" <p...@2016.groups.google.dobrogost.net>
> *To: *"sqlalchemy" <sqlal...@googlegroups.com>
> *Sent: *Wednesday, April 13, 2016 1:50:19 PM
> *Subject: *Re: [sqlalchemy] Support for Oracle 12c auto increment

> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
> From the license under which you can use this
> (http://www.oracle.com/technetwork/licenses/standard-license-152015.html):
>

> *License Rights and Restrictions *

> Oracle grants You a nonexclusive, nontransferable, limited license to
> internally use the Programs, subject to the restrictions stated in this
> Agreement, only for the purpose of developing, testing, prototyping, and
> demonstrating Your application and only as long as Your application has
> not been used for any data processing, business, commercial, or
> production purposes, and not for any other purpose.
>
> ------
>
> I suspect you would not be able to use this for developing and testing
> sqlalchemy without breaking those terms. Oracle can get really nasty
> about using Developer Days when they think you've broken this agreement.
>
> regards,
>
> Dave Moore
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.