Add decimal storage method

On a side note (perhaps?), I've been using CCK to track decimals and noticed that I cannot store large decimal numbers in a decimal number field without losing the decimal digits... 450250.28 becomes 450250. Smaller decimals are stored and manipulated fine (like 7039.31 or 128.79)

I agree we need a way of storing decimals. Certainly two places is a minimum (for money), but I can see applications needing more, so would hesitate to limit storage to two places, particularly for those applications requiring calculations.

Some days ago I created my own money.module field-type-module based on number.module, which sets up a database column to be a decimal with length (10,2).
But I really would like to see this feature integrated into number.module. So please rename decimal to float in number.module and implement a real decimal field type. There should be widget settings fields, that allow the setting of M and D.

From the MySQL Reference Manual (http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html):

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the ‘-’ sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

UNSIGNED, if specified, disallows negative values.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

Ok, I started to work on a patch which allows to define a real decimal storage method.
Here's what I did so far:
- renamed all occurrences of 'number_decimal' to 'number_float'
- added 'number_decimal'
- added advanced setup options for 'length' (M,D) and 'unsigned'
- added validation for lenght setup
- added validation for number field if 'unsigned' is set but users submits a negative value
- (fixed a small bug where validation of 'min' and 'max' fields occurs... first param of form_set_error() was not correct. Maybe I should make a new bug report for that!?)

Problem I need help with:
The database column seams to be added to the database BEFORE the field-settings form is validated and submitted. This means that the code in "case 'database columns':" is executed BEFORE "case 'form':", where the user sets up the length and unsigned setting. So with the current implementation there is no chance to pass any advanced user settings to the column creation function.

   case 'database columns':
      if ($field['type'] == 'number_integer') {
        return array(
          'value' => array(
            'type'     => 'int',

            'length'   => $field['db_length'],
            'unsigned' => $field['db_unsigned'],

            'not null' => FALSE,
            'default'  => NULL,
            'sortable' => TRUE
          ),
        );
      }
      if ($field['type'] == 'number_float') {
        return array(
          'value' => array(
            'type'     => 'float',

            'length'   => $field['db_length'],
            'unsigned' => $field['db_unsigned'],

            'not null' => FALSE,
            'default'  => NULL,
            'sortable' => TRUE
          ),
        );
      }
      if ($field['type'] == 'number_decimal') {
        return array(
          'value' => array(
            'type'     => 'decimal',

            'length'   => $field['db_length'],
            'unsigned' => $field['db_unsigned'],

            'not null' => FALSE,
            'default'  => NULL,
            'sortable' => TRUE
          ),
        );
      }

$field['db_length'] and $field['db_unsigned'] are always NULL when the column is created in the database, because the user had no chance to setup and submit these values, yet. Additionally 'length' => $field['db_length'] causes an SQL error: ALTER TABLE node_foo ADD COLUMN field_bar_value decimal()...
There is no value inside the round brackets after the 'decimal' declaration. Correct would be something like decimal(10) or decimal(10,2), but decimal() causes an SQL error. Thats why the corresponding lines in my patch are currently commented out.

Any ideas / solutions?

I also added a number_double type, because I need double precision for storing GoogleMaps latitude and longitude coordinates.
- added number_double
- changed '#maxlength' of textfields to 16 to be able to submit double precision values
- changed the default formatter. I don't think that $value = number_format($item['value']) was a good choice. I think the default formatter should not do any number_format() on the value, but just append the prefix/suffix and leave the value as it is.

I still have no idea how to dynamically setup the length (M,D) of a column... although the form elements for configuration and validation of the length are ready and available.

still have no idea how to dynamically setup the length (M,D) of a column
That's the trickiest part, obviously. CCK 'data definition layer' currently does not support the concept of 'parametric data type', and I'm not sure it can without non-negligible rewriting :-/...

Trying to fix the HTML...

That's the trickiest part, obviously. CCK 'data definition layer' currently does not support the concept of 'parametric data type'...
If you think about implementing this concept of 'parametric data type', we have to consider if we want this to be a one-time-option (only configurable the first time on creation) or if the parameters could be changed anytime. The latter is certainly even more trickier. At least needs more careful consideration, given the case, that several content types use the same number-field. CCKs dynamic database optimization/reorganization ensures, that this field is outsourced to a separate table. If you can dynamically change the parameters (e.g. from signed to unsigned) you eventually don't want to do that for all content-types, that use this field...

_If_ this becomes real some day, this will obviously be a 'field' setting (shared amongst all field instances, like 'required' or 'multiple'), and not a 'field instance' setting (like 'widget type', 'label', etc)

Ok, maybe we should open a new feature request for 'parametric data types'. To provide a solution for this specific feature request 'Add decimal storage method', I propose the following patch as a solution:

As interim solution I commented out all the 'parametric data type'-specific form elements and validation in my patch. I disabled the number_decimal type, because a Decimal(10,0), as it is initialized by default, is useless (it's just like an integer). I just set the label of the deactivated number_decimal type to 'old Decimal (actually Float)' for backwards compatibility. So in existing applications, that already use the number_decimal type, it's indicated, that this is actually a float. But number_decimal can no longer be used to create NEW fields.

As interim solution for what most users need a real decimal for: monetary values, I added a 'number_decimal_10_2' type (Label: 'Decimal(10,2)') with the fixed length of '10,2'. This allows exact values from -99999999.99 to 99999999.99 and should be sufficient for many cases. This patch still introduces the number_float type (which is the same as the previous number_decimal) and also introduces the number_double type.

It seems to me, that there hasn't been any progress / interest on this, recently. I'm about to deploy a website needing this functionality (real decimal and double data types) in the next few weeks. As I want to stay synced with CCK development, it wouldn't be a good practice if I apply my number patch each time a new CCK release comes out. Therefore I would like to request your advice how to proceed?

- Will my patch be reviewed/applied in the near future, but none of the active CCK developers had the time to review it, yet?
- Don't you want this functionality in number.module, but should I rather create an advanced_number.module? Actually I don't think this would be a good choice too, because advanced_number.module would use 95% of number.module's code...

Actually it just occurred to me that, contrary to what I wrote in #5 above, content.module actually has a notion of 'parametric datatype' : varchar types accept a 'length' attribute (see content_admin.inc, lines 1308 and 1408)
Thus having decimal or double field types with one or two params would not require _that_ much of a change.

I'm not really familiar with db numeric types (and the pgsql compatibility issues), and I did not have much time to keep this thread in mind, so I don't know for sure where this remark puts us regarding the patches you submitted. Is it possible for you to summarize this ?

Ok, here we go... I'll summarize my proposals.

1. The current 'number_decimal' field type is actually a float in the database, but is labeled 'Decimal' nevertheless. I think this is really irritating (it was at least for me). My proposal is to clear things up and let people know, that it is a float.

• In order to not break existing instances of this field, my proposal is renaming the label 'Decimal' to 'old Decimal(actually Float)' and removing 'number_decimal' from number_widget_info(). This way existing instances are not affected, but people can't create new instances of this wrong labeled data type.
• I introduced a 'number_float' field type, which is actually the same as the old number_decimal, but correctly named 'number_float' with the label 'Float'.

In my country (Hungary) the "decimal point" is a , (comma).
So it would be great, if the decimal module could support this kind of localization (for the input/output of course, in the database, the decimal would be stored "normaly").

Sorry my hunglish.

Tyrael

Im also generating a site that needs to store GPS coordinates for latitude and longitude so I'm very interested in the progress of the double/decimal discussion going on here.

I'll try out the patch for number.module 3 and see how this goes.

Has there been any decision if this functionality will be taken on board for Drupal 6.x?

Subscribing

What about inifinity?

I have an application with decimal values for distances and valid data can include infinity. Is there some way to include infinity in the decimal field? I'd like for it to be accepted as an input, and validated, and printed on output.

Has this been solved for 5.x also or just the 6.x version...?

As for 6.x-2.x-dev 2009-Apr-24, I am still experiencing comma not allowed.
"Only numbers and the decimal character (.) are allowed in Minimum financing amount."

Will the patch at #9 work for 5.x, or 6.x? We need this at least in the short term-- we're migrating to 6.x shortly, but as part of the migration cleanup we need to get some of our data converted to numbers.

I use D6/ CCK number 2.4 and tried to store the value of 0.45 into Float and Decimal field types - it stores it in the database successfully, but it doesn't output anything. Why is that, can I fix it somehow?

I use Contemplates module and a disc based template for body. So I use this function to print the field:

<?php
           // this checks to see if the user has permission to view the information in the field
           if ($node->field_gamma_correction[0]['view']['#access']) {
           // then displays it if they have permission
           print $node->field_gamma_correction[0]['view']; 
            }
          ?>

Unfortunately it does not print anything ?! If I change the value from 0.45 to 45 - then it prints normally. But will not print 0.45

Can somebody help me, please?