On 27/11/2012, at 3:01 AM, Michael Bayer wrote:
>
> 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.
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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
