Decimal Type does not render scale or precision

[txnaidaa_sqlalchemy]

Hi all,

I have noticed that the DECIMAL type is not rendered with precision or scale:

>>> sa.__version__
'0.7.9'
>>> import sqlalchemy as sa
>>> print sa.NUMERIC(6, 4)
NUMERIC(6, 4)
>>> print sa.DECIMAL(6, 4)
DECIMAL

This causes problems in eg alembic where a table definition that uses DECIMAL(x, y) will silently lose the scale and precision eg an upgrade script such as

def upgrade():
op.create_table("x",
sa.Column("x", sa.DECIMAL(6, 4)))

produces:

CREATE TABLE x (
x DECIMAL NULL
);

To have precision you must use the NUMERIC type.

I'm mainly encountering this issue when generating the sql for an existing database where I reflect the metadata and then issue a create_all via a mock engine ... it then renders all of the DECIMAL columns in the existing model (that I have no control over) without their scale or precision.

Is there a reason for this or is it a bug? Currently I am monkey patching sqlalchemy.sql.compiler.GenericTypeCompiler.visit_DECIMAL in order to get what I view as "correct" output.

Cheers,
d.


--
E-Mail sent with anti-spam site TrashMail.net!
Free disposable email addresses: http://www.trashmail.net

[Michael Bayer]

On Nov 26, 2012, at 2:51 AM, txnaidaa_sqlalchemy wrote:

> Hi all,
>
> I have noticed that the DECIMAL type is not rendered with precision or scale:
>
>>>> sa.__version__
> '0.7.9'
>>>> import sqlalchemy as sa
>>>> print sa.NUMERIC(6, 4)
> NUMERIC(6, 4)
>>>> print sa.DECIMAL(6, 4)
> DECIMAL
>
> This causes problems in eg alembic where a table definition that uses DECIMAL(x, y) will silently lose the scale and precision eg an upgrade script such as
>
> def upgrade():
> op.create_table("x",
> sa.Column("x", sa.DECIMAL(6, 4)))
>
> produces:
>
> CREATE TABLE x (
> x DECIMAL NULL
> );
>
> To have precision you must use the NUMERIC type.
>
> I'm mainly encountering this issue when generating the sql for an existing database where I reflect the metadata and then issue a create_all via a mock engine ... it then renders all of the DECIMAL columns in the existing model (that I have no control over) without their scale or precision.
>
> Is there a reason for this or is it a bug? Currently I am monkey patching sqlalchemy.sql.compiler.GenericTypeCompiler.visit_DECIMAL in order to get what I view as "correct" output.

which platform allows for DECIMAL with scale and precision? the ultimate fix would be to supply the appropriate type compilation to the target backend.

also no monkeypatching required, please see http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#overriding-type-compilation

[Derek Harland]

My understanding was that ANSI sql defines both numeric(s, p) and decimal(s, p) and makes them "almost" identical.  A vendor must guarantee exact precision for numeric, and precision >= p for decimal.

Practically, most implementations treat them as exact synonyms eg

- postgresql

- ms sql server

- sybase

- informix

Just had a look around ... here is what the SQL2003 standard has to say:

21) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the    

<precision> and <scale>.

22) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the <scale> and the

    implementation-defined decimal precision equal to or greater than the value of the specified <precision>.

So it would seem correct to always retain scale and precision when rendered (if they exist)

also no monkeypatching required, please see http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#overriding-type-compilation

Great!  Much easier ... I'm also monkey patching parts of the mssql.ddl_compiler to allow:

- an index to be specified as clustered

- an identity to be marked as NOT FOR REPLICATION

- an index to be marked with sort order eg: create index <idx> on <table> (<col_1> DESC, ...)

Are there easier ways to do this also, or if not, should I push these as patches to you?

d.

[Michael Bayer]

On Nov 26, 2012, at 4:19 PM, Derek Harland wrote:

which platform allows for DECIMAL with scale and precision?  the ultimate fix would be to supply the appropriate type compilation to the target backend.

My understanding was that ANSI sql defines both numeric(s, p) and decimal(s, p) and makes them "almost" identical.  A vendor must guarantee exact precision for numeric, and precision >= p for decimal.

Yeah I just did a poll of the "main" DBs I have here, mysql, PG, SQL server, and these days they're all allowing it.  must have had some obsolete data in my head when I made that type.   ticket http://www.sqlalchemy.org/trac/ticket/2618

also no monkeypatching required, please see http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#overriding-type-compilation

Great!  Much easier ... I'm also monkey patching parts of the mssql.ddl_compiler to allow:

- an index to be specified as clustered

- an identity to be marked as NOT FOR REPLICATION

- an index to be marked with sort order eg: create index <idx> on <table> (<col_1> DESC, ...)

Are there easier ways to do this also, or if not, should I push these as patches to you?

index options are usually platform specific, and these are implemented in the target dialect's compiler, looking for dialect-named kw args:

Index('myindex', x, y, z, mssql_clustered=True)

it's important that these little APIs are designed with a mind towards the bigger picture for that database.  In the past I've accepted a lot of little tweaks like these only to learn that the solution we committed doesn't really take into account the full syntax (like flags that only accommodate one column, when the feature can actually be applied to each column individually).

The NOT FOR REPLICATION might need to be a mssql_option flag on Sequence.  You can use Sequence() right now with MSSQL to declare options on the IDENTITY so it would go there.

Patches for these, in order for me to commit them, need:

1. tests in test/dialect/test_mssql (or wherever is appropriate)

2. docs added to doc/build/dialect/mssql.rst, under sections for "index options" and "identity", or if some section is already there where things like this can go

you can add patches to trac or send pullrequests on the bitbucket clone at https://bitbucket.org/sqlalchemy/sqlalchemy.   thanks !