Database: date_modified, date_created

[eluser]Maglok[/eluser]
Greetings,

As some of you might know MySQL databases only allow one TIMESTAMP column to be auto-timestamped. Why? I have no clue, but it does.

If you have a field date_modified and date_created this is a problem, since you can only get the database to stamp one field to a CURRENT_TIME value when a row is inserted.

A friend of mine is into Python and uses Django and apparantly it supports auto updating in the framework, thus making the code he uses not reference this problem at all.

Is there any way in CodeIgniter this is handled? Almost everyone should have this issue if they use a date_modified and date_created.

[eluser]Dam1an[/eluser]
You can only have one auto timestamp for the same reason you can only have one auto increment per table.
If you have 2 fields which have the current timestamp applied, they will always have the same timestamp, so it's just pointless dublication.

I actually modified the core DB class (tut tut, naughty me) to automatically handle created_on, updated_on (and soon to do) accessed_on if they exist.
I don't have that code with me now (it's at home) but I'll look it up if I remember later on, or search the forums, as I'm sure I've seen someone post code that does the same

[eluser]Maglok[/eluser]
I tried searching, but it's tricky. Some people call it 'date_created' others 'created_at' 'created_on' etc.

So no native support. Shame, core class 'hacks' coming up.

[eluser]Dam1an[/eluser]
you could always look at this wiki article on extending the DB class... I've not actually used it, but someone referenced it yesterday (I'll probably look into it when I have the time, so in the next 6-12 months )

[eluser]n0xie[/eluser]
[quote author="Maglok" date="1244816947"]Greetings,
Is there any way in CodeIgniter this is handled? Almost everyone should have this issue if they use a date_modified and date_created.[/quote]
We set date_modified to timestamp with on update set current_timestamp. We just add a date_create datetime field for an insert (i.e. add it to the query/method inside a model) since this data is not going to change and will likely only affect 1 query/method.

[eluser]Colin Williams[/eluser]
You should have only one spot, the model, that is handling insert/update queries. Seems reasonable enough to do the time settings there.

[eluser]Dam1an[/eluser]
[quote author="Colin Williams" date="1244819815"]You should have only one spot, the model, that is handling insert/update queries. Seems reasonable enough to do the time settings there.[/quote]

I think what Maglok is getting at (or at least in my case) there are lots and lots of tables with created_on, updated_on etc fields, which you don't want to set manually every time in each model, and even if you moved this into a MY_Model, you would then still need to manually invoke that, as you would have probably overriden whatever method you put it in to make it do what you want. So adding it to the DB class was an easy fix (at least in my case)... Make sense?

[eluser]Maglok[/eluser]
True but you are still making the application take care of it instead of the database (bad Mysql!) or CodeIgniter. It's just 'neater' so to speak.

Extending the DB class, hmm perhaps aye.

EDIT: In the same style of thinking I can't seem to get the dbforge class to accept my TIMESTAMP columns. I can set created_at to some kind of default that I will update whenever I create a row. The modified_at needs to get the mysql `modified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,' though and it won't accept that at all.

I am really trying to use the forge, but I looked up the code and it seems it doesn't support it at all.

Code:

/**
     * Process Fields
     *
     * @access    private
     * @param    mixed    the fields
     * @return    string
     */
    function _process_fields($fields)
    {
        $current_field_count = 0;
        $sql = '';

        foreach ($fields as $field=>$attributes)
        {
            // Numeric field names aren't allowed in databases, so if the key is
            // numeric, we know it was assigned by PHP and the developer manually
            // entered the field information, so we'll simply add it to the list
            if (is_numeric($field))
            {
                $sql .= "\n\t$attributes";
            }
            else
            {
                $attributes = array_change_key_case($attributes, CASE_UPPER);
                
                $sql .= "\n\t".$this->db->_protect_identifiers($field);

                if (array_key_exists('NAME', $attributes))
                {
                    $sql .= ' '.$this->db->_protect_identifiers($attributes['NAME']).' ';
                }
                
                if (array_key_exists('TYPE', $attributes))
                {
                    $sql .=  ' '.$attributes['TYPE'];
                }
    
                if (array_key_exists('CONSTRAINT', $attributes))
                {
                    $sql .= '('.$attributes['CONSTRAINT'].')';
                }
    
                if (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE)
                {
                    $sql .= ' UNSIGNED';
                }
    
                if (array_key_exists('DEFAULT', $attributes))
                {
                    $sql .= ' DEFAULT \''.$attributes['DEFAULT'].'\'';
                }
    
                if (array_key_exists('NULL', $attributes))
                {
                    $sql .= ($attributes['NULL'] === TRUE) ? ' NULL' : ' NOT NULL';
                }
    
                if (array_key_exists('AUTO_INCREMENT', $attributes) && $attributes['AUTO_INCREMENT'] === TRUE)
                {
                    $sql .= ' AUTO_INCREMENT';
                }
            }
            
            // don't add a comma on the end of the last field
            if (++$current_field_count < count($fields))
            {
                $sql .= ',';
            }
        }
        
        return $sql;
    }

Am I missing something? Or should I go and not use forge?