Thread

Khoa
What database are you using?  I’m pretty certain this works under PostGreSQL (with manual transactions), but I don’t use MySQL, so maybe someone else could pipe in on that.  DataMapper relies entirely on the database (and CodeIgniter) for transactions.

Also, do you have automatic transactions enabled on DataMapper?  That may or may not have an effect.

Finally, you might just want to do a manual check before attempting the delete.  I know the library is supposed to handle it, but it might be the only option if your database is incorrectly handling transactions.

This is a more complete sample of the manual transaction code that should work:

$entry = new Entry();

// start a transaction
$entry->trans_start();
$entry->get_by_id($id);
// delete all tags
$entry->tag->delete_all();

// Attempt to save, this should throw an error
if($entry->save()) {
    $entry->trans_commit();
    // redirect, etc.
} else {
    $entry->trans_rollback();
    // show error
}

Hi Phil,

I use MySQL and I set automatic transactions DISABLED.

I notice one thing in your code, it says:

$entry->trans_start();

But inside the documentation, it says:

$u->trans_begin();

Is it just because you typed it on the rush 😊 or it is what you actually do and it works?

Khoa

Yup - that’s just me typing from memory quickly.  It’s start / complete or begin / rollback / commit!
😊

Otherwise, it looks like everything else is correct.  I would try to test the MySQL code directly, by running something like this (on a test DB):

START TRANSACTION;
DELETE FROM `entries_users` WHERE `user_id` = 1;
ROLLBACK;
SELECT * FROM `entries_users` WHERE `user_id` = 1;

(Again, this is hand-written from memory, so it may be incorrect.)

If you get the correct output, then the issue might be with CodeIgniter’s transaction code.  If you get an error, or the incorrect result, make sure you are using transaction-capable tables (which I’m sure you already are!).

Beyond that, I can’t think of any other reasons it would fail.  I haven’t heard of anyone else having trouble, but that doesn’t mean too much!

Phil, I think I figure out what’s wrong! It is because of the table type. It has to be set as InnoDB in order for it to support transactions as only innoDB and BDB are transaction-safe tables while others are nontransaction-safe. By default, the table type is MyISAM which is nontransaction-safe or in other words, DOES NOT SUPPORT tractions.

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

I’m at work now, so just tried it directly with phpmyadmin. Will go home and test it out with DM. But it seems to be the solution 😊

However, I notice on thing with transactions. Even records are not changed when transaction rolls back, if there is INSERT statement happened, the id column does increase and this does not roll back. Eg, if we have 10 inserts inside transactions, then roll back, the next time it inserts successfully, the id is not continuous anymore (there is a gap of 10). Not a big deal, but is it normal?

Phil, I think we just typed our 2 posts at the same time, LOL! :D

Regarding the serial/autonum column: yes that’s normal.  To ensure database integrity, a database will never reuse an automatically incremented number.  The reason is concurrency.  Example:
Say the last ID was 5.  These events happen in this order:
1) User A makes a request for a new object.  ID: 6
2) User B makes a request for a new object.  ID: 7
3) User B uses the new ID in another query, but hasn’t committed yet.
4) User A gets rolled back.
5) User B commits.

Because of the concurrent transactions, there is no way to roll the unused ID back safely.  Therefore, the IDs simply get incremented even if not used.

I just changed my db type from MyISAM to InnoDB and the transactions work now! 😊 Thank you so much Phil.

Plus, that ID theory makes sense. Rolling back the ID would cause a disaster in data integrity for large scale applications.

Hi,

It seems to me that this is a bug, but not sure if it’s because I’m doing something wrong.

The bug is: after deleting one of the “has many” relationships, that relationship becomes NULL entirely. Eg, an entry has 5 tags, deleting one of tags makes $entry->tag NULL while it should remain a valid array with 4 tags in it

SITUATION:

- Entry and Tag are 2 models with many-to-many relationships
- An entry currently has 5 tags (eg. tag1, tag2, tag3, tag4, tag5)

WHAT I DID:

- I open that entry in the edit form, remove one tag (eg. tag1) and click save with the following code:

// I compare what is submitted with what exists in the DB to find out
// what will be deleted and what will stay. I dont want to blindly remove
// everything and just add in whatever new, just to keep my Id column small :blush:
// Plus, by doing this comparison, in case user just updates the entry details
// but not changing the tags, I dont want to remove and add back in the same list
// of tags as they are redundant.
// After running through all the comparisons, I got a few arrays like this:

// What will be deleted
$delete = array (
            'tag'    => array(
                            TAG_1_ID => TAG_1_OBJECT
                        )
        );
        

// What will be added (nothing in this example)
$added = array (
            'tag'    => array()
        );


// What will stay (unchanged)
$keep = array (
            'tag'    => array(
                            TAG_2_ID => TAG_2_OBJECT,
                            TAG_3_ID => TAG_3_OBJECT,
                            TAG_4_ID => TAG_4_OBJECT,
                            TAG_5_ID => TAG_5_OBJECT,
                    )
        );
        

// Now I delete those that have been marked for deletion
$entry->delete($delete);

// ERROR HERE: Trying to access the tag again raises error
foreach ($entry->tag->get()->all as $k => $v)
{
    // ...
}

WHAT I GET:

The foreach loop above will throw the following error because $entry->tag is now NULL:

Fatal error: Call to a member function get() on a non-object

WHAT I EXPECT:

- It should remove 1 tag and keep the remaining 4 tags
- $entry->tag should still remain a valid object of class/model and calling $entry->tag->get()->all should give me an array with 4 elements

I also tried to call $entry->refresh_all() directly after the delete(), but the same result.

Whta did I do wrong?

Thanks,
Khoa

Sorry, my bad :(. I just look into the datamapper code, it IS set to NULL after deleting a relationship. Here is a brief of delete relation function:

function _delete_relation($object)
{
    if ( ! empty($object->model) && ! empty($this->id) && ! empty($object->id))
    {
        // ...
        
        // Clear related object so it is refreshed on next access
        $this->{$object->model} = NULL;

        return TRUE;
    }

    return FALSE;
}

May I ask why we cannot refresh the array right here? maybe using unset somehow 😊? Performance issue?

If so, how could I achieve what I want as in the example above?

Thanks,
Khoa

There is no practical way for DM to know when to refresh a related array.  If the array is refreshed each time, this could lead to performance problems (as you mentioned).  This change was made a while back to fix some subtle bugs.

It is best to handle refreshing the array manually (ie: call get() after you are done deleting one or more items).

The other reason it has to set it to NULL is that DM uses the same code to manage $has_one and $has_many relationships.  The first item returned when calling get() is set as the top item (see below).  This means that when you delete an item, you may or may not be deleting the “top” item.

Layout:

entry
  ->tag == all[0]
      ->all[]

A simple way to check for an item and load it if necessary is to use this line of code:

if( ! $entry->tag->exists() ) { $entry->tag->get(); }

I use this a lot, so I added this to my DataMapperExt(ension) class as get_once:

function get_once() {
    if(!$this->exists()) {
        $this->get();
    }
    return $this;
}

However, this method has some sneaky bugs (for example, it doesn’t clear other query information if the item is already loaded), so I cannot include it in DMZ directly.

Hi Phil, thanks for the reply.

I dont want to call get() again (or actually I can’t) because I want to have an “image” of the object in the “failed condition” in case user doesn’t pass the validation so that I could refill the form with their failed data. By calling get() again, it does solve the NULL problem but now the way it solves is to roll back everything to the good/original condition.

For some reason, I have the impression that it is very hard to keep 2 copies of the same model/object: the correct one inside DB and the current one inside memory with whatever data we are assigning to it during the process. Do you come across this problem when you first started with DM?

I think I’m still trying to get my head around how DM works 😊

BTW, I just did a little hack to workaround my issue, that is to check for $_POST array as my problem only happens when user FAILED the validation (I got no idea why). The code looks odd, but it does the job.

Just an off-topic question, do you think that the $this->input->post() function should be updated to differentiate between submitted field with empty value and non-submitted field? I have to use $_POST all the time for this purpose while I think it should be gone and replaced by $this->input->post().

Khoa

The post() nmethod actually does distingush, you probably aren’t checking correctly:

if( $this->input->post('value') === FALSE) {
    // no value was sent by the browser
}

You always have to use a triple equals (===) to correctly test against the empty string, 0, ‘0’, NULL or FALSE.  This is also true in JavaScript.  In otherwords, those items are all equal when using a normal double equals (==).

Finally, I understand what you are saying (and can see the benefits), but I just don’t think that the few times it is really useful is worth the effort to search through the (possibly non-existant) all array, and top object, and reset the values.  At least, not at this time.  (Also, I don’t have that issue because my application saves using AJAX calls, so there is no need to repopulate the form.)

Ohhh, never know there is a “===” operator! LOL! Need to go back and study more PHP then 😛

Thanks for understanding! I know, long post can either make is really clear or really confusing!

I used Ajax before with my previous application but it’s just way too much trouble for me. Especially when I have to build it in a way that it can degrade gracefully when user turns off JS. I also find it too much distracting when trying build PHP and Ajax together at the start. I will eventually put Ajax in for what I’m doing, but I would love to focus on PHP for now to have an application that functions and does equally EVERYTHING for non-js users.

Khoa

I totally understand that (I was trying to explain why I don’t have the issue), and wouldn’t suggest anyone add too much AJAX without understanding the repercussions.

Since I’m not building a general-purpose web app — it’s a narrowly-targeted web application — I can safely limit (at the start) my user base.

If there ends up being a benefit for the effort, then I will eventually look ate developing a light-weight, non-JS portal as well. 😉

Good Luck!

You’re right, I totally agree with you.

It all depends on what our audience is and what we can assume about them. Plus, make sure we know what we’re doing 😊

So you’re building a portal? What site is it?

Khoa