Codeigniter: DBFORGE error using default CURRENT_TIMESTAMP

[AmitMY]

When adding a field to DBFORGE with type "timestamp" and default "CURRENT_TIMESTAMP" it renders a query:

time timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT ''

However, this should be a special case, and render: (no single quotes)

time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''

This matters for migrations. When writing a migration, this is necessary if you have a timestamp column.
Note: I don't know what is the case for "datetime" columns

[narfbg]

It may be strange, but who is to say that somebody doesn't want to set the string 'CURRENT_TIMESTAMP' as a default value?

[AmitMY]

@narfbg Well, he can't do that.. Sure you may be able to do it if the column type is varchar or something, but the special case is:

type "timestamp" and default "CURRENT_TIMESTAMP"

Which if you try to insert a string as default, will just crash the SQL

[narfbg]

For a timestamp field - sure, but the problem is that we don't really differentiate between field types aside from field size constraints.
I guess we can change that, but have it in mind that we're not MySQL only - such improvements must be applied to all drivers (as long as the feature is supported).

[AmitMY]

Ok, so for now this throws a database error.
The safest case, according to what you say is

driver=mysql & type=timestamp & default = CURRENT_TIMESTAMP

I understand your point, and agree with it, but unfortunetly I do not know how does this work in other drivers, so I can not help/suggest a better solution

I am currently running with a fix I did in the core's dbforge
Line 909: if($attributes["TYPE"] == "timestamp" && $attributes['DEFAULT'] == "CURRENT_TIMESTAMP")

[Seb33300]

Another way to do this could be to add another parameter to specify if the default value is a string:

$fields = ['date_field' => ['type' => 'TIMESTAMP', 'default' => 'CURRENT_TIMESTAMP']];
// or
$fields = ['date_field' => ['type' => 'TIMESTAMP', 'default' => 'CURRENT_TIMESTAMP', 'default_string' => true]];

will return

date_field timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT ''

but

$fields = ['date_field' => ['type' => 'TIMESTAMP', 'default' => 'CURRENT_TIMESTAMP', 'default_string' => false]];

will return

date_field timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''

So it will works for every case, but not automatically.

Code to change may looks to:

elseif ($attributes['DEFAULT_STRING'] === false)
{
    $field['default'] = $this->_default.$attributes['DEFAULT'];
}
else
{
    $field['default'] = $this->_default.$this->db->escape($attributes['DEFAULT']);
}

[AmitMY]

@narfbg Is @Seb33300 's suggestion acceptable?

[Seb33300]

If we cannot add an extra parameter, we can also do something like:

$fields = [
    'date_field' => [
        'type' => 'TIMESTAMP', 
        'default' => ['value' => 'CURRENT_TIMESTAMP', 'string' => false]
    ]
];

[narfbg]

That's a work-around, not a fix ... And we already do have a work-around - you could simply pass the entire SQL field definition as a string.

[narfbg]

Please test with the commit referenced above.

[Seb33300]

I am not able to test it right now, but if I understand your code, you are not escaping the default value when the field type is DATE or TIME.

But, it will also be applied to default values like 2016-01-01 00:00:00 which have to be escaped since it should not?

[narfbg]

The code in quesion is very simple ... I shouldn't have to answer this.

[Seb33300]

I hadn't pay attention on the second check on the default value.

It seems ok. Will try it later.

[Seb33300]

Tested and it's ok for me...

[narfbg]

@AmitMY Did you test this?

[AmitMY]

@narfbg Just tested, it works.
Thanks!

[mariolucasdev]

When adding a field to DBFORGE with type "timestamp" and default "CURRENT_TIMESTAMP" it renders a query:

time timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT ''

However, this should be a special case, and render: (no single quotes)

time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''

This matters for migrations. When writing a migration, this is necessary if you have a timestamp column.
Note: I don't know what is the case for "datetime" columns

Na pasta system/database/DB_forge.php

Substituir essa linha:
$field['default'] = $this->_default.$this->db->escape($attributes['DEFAULT']);

por essa:
$field['default'] = $this->_default.$attributes['DEFAULT'];

Obs.: esse método $this->db->escape() não sei exatamente o que ele faz, mas estava interferindo no caso citado acima. e no meu migration ficou o seguinte:

'ocorrencia' => array(
'type' => 'TIMESTAMP',
'default' => 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
),

[Seb33300]

This has been added to the 3.2 milestone which has not be released since 3 years

[PedroRuiz]

it works for me:
$this->db->query("ALTER TABLE {$this->tables['phones']} CHANGE COLUMN update_time update_time TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP() ;");