I need to update about 40 fields from floats to decimals (need to be able to use exposed filters in views). The script is intended to only change/add info to complete the conversion in place, saving all original settings from the float version of the field. Realizing this is a major change, I thought it would be good to post here and ask the community to review what I have, just to be safe. As far as I can tell it works fine. Am I missing anything here?

function mymodule_update_6000() {
  $ret = array();
  $all_fields = content_fields();
  
  foreach ($all_fields as $name => $field) {
    //change all float type columns to decimal
    if ($field['type'] == 'number_float') {
      $db_info = content_database_info($field);
      $table_name = $db_info['table'];
      $data_column = $db_info['columns']['value']['column'];
      $alter = "ALTER TABLE {". $table_name . "} CHANGE $data_column $data_column DECIMAL (10,2)";
      $ret[] = update_sql($alter);
          
      //update settings
      $field_name = $field['field_name'];
      $type_name = $field['type_name'];
      //db name varies based on installed version of cck
      $sql = "SELECT * FROM {content_node_field} WHERE field_name = '%s'";
      $result = db_query($sql, $field_name);
      while ($row = db_fetch_array($result)) {
        $new_setting = array();
        $row['global_settings'] = unserialize($row['global_settings']);
        $row['db_columns'] = unserialize($row['db_columns']);
        
        $new_setting = $row;
        $new_setting['type'] = 'number_decimal';
        $new_setting['global_settings']['precision'] = '10';
        $new_setting['global_settings']['scale'] = '2';
        $new_setting['global_settings']['decimal'] = '.';
        $new_setting['global_settings'] = serialize($new_setting['global_settings']);
        
        $new_setting['db_columns']['value']['type'] = 'numeric';
        $new_setting['db_columns']['value']['precision'] = '10';
        $new_setting['db_columns']['value']['scale'] = '2';
        $new_setting['db_columns'] = serialize($new_setting['db_columns']);

        foreach($new_setting as $column => $setting) {
          if ($column != 'field_name') {
            $update = "UPDATE {content_node_field} SET %s = '%s' WHERE field_name = '%s'";
            db_query($update, $column, $setting, $field_name);
          }
        }
      }     
    }
  }

  content_clear_type_cache(TRUE);
  cache_clear_all();

  return $ret;
}

Comments

jayson’s picture

Worked like a charm - thank you so much for sharing and posting this!

John Carbone’s picture

No problem! Glad it helped someone out.