2

I need to check whether a certain table field is of type 'int' (I am referring to table schema)

e.g. for table field_data_field_myfield field_data_field_myfield_value is of type int(11)

I actually need a generic function that check if a certain field is of type in the schema.

[Edit]

I want to explain better my difficulty:

I have a feature that holds a certain field of type 'datetime'. Now as this type creates performence problems on database sides, I decided to change it to 'datestamp' [= unix format]. I did this editing the file MYFEATURE.features.field.inc that is a database field table mirror.

Now the reports are:

FieldException: Cannot change an existing field's type. in field_update_field()

This is an exception raised due to the fact I am not supposed to alter schema while data is in. Therefore I create a hook_update_N for data migration.

Now I believe I need to do a hook_update_N for schema migration too. HERE is the point I need to alter schema in a hook_update_N.

4
  • In MySQL, it's column, not field. Unless you are talking about Drupal schema, then it's field, but no need for it to be MySQL... Please clarify a bit.
    – Mołot
    Commented Oct 29, 2013 at 13:35
  • @Molot in his defense like show columns for mysql returns a column called 'field' dev.mysql.com/doc/refman/5.0/en/show-columns.html ... while the rest of the page talks about "columns" so its all snafu :)
    – tenken
    Commented Oct 29, 2013 at 15:29
  • @tenken I just meant that interacting with schema seems way easier and more portable, so if he wants schema, it should be clear. Well, for the same reason it should be clear if he does not want it. Totally different solutions. And no need to defend - not a single downvote on this question, so nobody was attacking him.
    – Mołot
    Commented Oct 29, 2013 at 15:32
  • Thanks for your efforts. I re-edited my question. Yes I need to produce a hook_update_N to alter the schema of a table after another hook_update_N that migrated the data of that table field. Commented Oct 31, 2013 at 15:26

1 Answer 1

3

You want something like:

function _mymodule_is_db_column_int11($drupal_field_name) {
  // See example output below.
  $drupal_field_info = field_info_field($drupal_field_name);

  // From this table you can get the table name, which follows a known
  // convention in the Field API (but this lets you get it grammatically)
  // and also value field names from the definition array.
  $sql_details = $drupal_field_info['storage']['details']['sql'];
  $table_name = key($sql_details[FIELD_LOAD_CURRENT]);
  $value_col_name = $sql_details[FIELD_LOAD_CURRENT]['value'];

  // Then in MySQL check the column type. I see no way to do this using
  // the drupal api.
  $raw_table_info = db_query("SHOW COLUMNS FROM :tbl;", array(':tbl' => $table_name)->fetchAll();

  $is_int11_field = FALSE;
  foreach ($raw_table_info as $result_id => $recObject) {
    if ($recObject->field == $value_col_name && $recObject->Type == 'int(11)') {
      $is_int11_field = TRUE;
    }
  }
  return $is_int11_field;
}

An example of output of field_info_fields() for 1 field:

/*
[field_foo_feedback023] => Array
    (
        [entity_types] => Array
            (
            )

        [foreign keys] => Array
            (
                [format] => Array
                    (
                        [table] => filter_format
                        [columns] => Array
                            (
                                [format] => format
                            )

                    )

            )

        [indexes] => Array
            (
                [format] => Array
                    (
                        [0] => format
                    )

            )

        [settings] => Array
            (
            )

        [translatable] => 0
        [storage] => Array
            (
                [type] => field_sql_storage
                [settings] => Array
                    (
                    )

                [module] => field_sql_storage
                [active] => 1
                [details] => Array
                    (
                        [sql] => Array
                            (
                                [FIELD_LOAD_CURRENT] => Array
                                    (
                                        [field_data_field_foo_feedback023] => Array
                                            (
                                                [value] => field_foo_feedback023_value
                                                [format] => field_foo_feedback023_format
                                            )

                                    )

                                [FIELD_LOAD_REVISION] => Array
                                    (
                                        [field_revision_field_foo_feedback023] => Array
                                            (
                                                [value] => field_foo_feedback023_value
                                                [format] => field_foo_feedback023_format
                                            )

                                    )

                            )

                    )

            )

*/

If you're going to try and use this you'd probably want to add caching, probably want to assure your type check is always forced to lower-case etc. Also check your mysql credentials allow for this sql usage of SHOW.

EDIT: A slightly simpler solution

The only problem I see with this solution is for complex or custom fields you will always have to know what the value field you want to check is; and require a custom check -- for default simple value fields this should work without needing custom SQL:

function _mymodule_is_db_column_int11($drupal_field_name) {
  // See example output below.
  $fields = field_read_fields();
  $my_field = &$fields[$field_name];

  $field_uses_sql_storage = $my_field['storage']['type'] = 'field_sql_storage';
  $field_value_uses_int_11 = $my_field['columns']['value']['type'] == 'int' && $my_field['columns']['value']['length'] == 11;

  return $field_uses_sql_storage && $field_value_uses_int_11;
}
4
  • This is spot on, but invoking hook_field_schema() and checking the schema array might be easier and more 'Drupal'-y (might be, I haven't checked it)
    – Clive
    Commented Oct 29, 2013 at 15:33
  • @Clive open to better alternatives :)
    – tenken
    Commented Oct 29, 2013 at 15:54
  • @clive what do you think of this other alternative in my edit ?
    – tenken
    Commented Oct 30, 2013 at 4:49
  • Missed your comment somehow, yes that looks a very good solution
    – Clive
    Commented Nov 6, 2013 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.