Using Transactions in Yii & Exceptions

I have a script that allows me to upload a CSV file, each line of the CSV is read and if valid (e.g it validates) will insert a new row to a ‘users’ database table using a model.

On some occasions it is possible for duplicates to be found within a CSV, obviously these should not be inserted into the table for data integrity, therefore I am using transactions to basically do a ‘rollback’ if a duplicate is found. This works so far however I’d like to improve this further by being able to highlight each of the problem lines in the CSV by adding this to the $model->getErrors() so it can be used within the flash message for example.

Currently what I have works, however it only provides the csv line of the very first ‘error’ and not any others - is there any way to achieve this to return more than just than first error to the errors array.

Currently at the bottom of the script, the following line is what displays the errors, I can only seem to get errors to return one item (even though it is in a foreach) it only ever contains a single item, but want this to contain multiple items (if multiple duplicates exist)

Yii::app()->user->setFlash(‘importerrors’, $errors);

I am not sure if this is possible in the current structure as I am throwing an Exception…

Any advice would be appreciated

P.S For clarity I have not included the entire function… just the element that is relevant to my question.




<?php

$transaction = Yii::app()->db->beginTransaction();

$errors = array();


    try

    {

        if (($handle = fopen($path, "r")) !== false) {

            while (($data = fgetcsv($handle)) !== FALSE) {

                if ($currentRow == 1) {

                    $header = $this->import_fields(array_map('strtolower', $data));

                    $currentRow++;

                    continue;

                } else {

                    $data = array_combine($header, $data);

                    $csv_import_model = null;


                    if (!empty($data['username'])) {

                        $csv_import_model = StudentImportForm::model()->findByAttributes(array(

                            'username' => $data['username'],

                            'organisation_id' => user()->data->organisation->getViewOrgId()

                        ));

                    }


                    if (is_null($csv_import_model)) {

                        $csv_import_model = new StudentImportForm();

                        $isNew = true;

                    } else {

                        $isNew = false;

                    }


                    $csv_import_model->scenario = 'import';

                    $csv_import_model->setAttributes($data);

                    $csv_import_model->unsetAttributes(array('password'));


                    if ($csv_import_model->validate()) {


                        if (in_array($csv_import_model->username, $processedUsername)) {

                            $errors[$currentRow] = sprintf('Duplicate username (%1$s) found in csv file. which may already exists on row number %2$s.', $csv_import_model->username, (array_search($csv_import_model->username, $processedUsername) + 1));


                            throw new Exception('Problem with your CSV - No Student data saved.');

                        } else {


                            if ($csv_import_model->save()) {


                                if ($isNew) {

                                    $this->csv_results['inserted'] = $this->csv_results['inserted']+1;

                                } else {

                                    $this->csv_results['updated'] = $this->csv_results['updated']+1;

                                }


                            } else {

                                $this->csv_results['error'][$currentRow] = $csv_import_model->getErrors();

                                throw new Exception('Problem with your CSV - No Student data saved.');

                            }

                        }

                    } else {

                        $csv_import_model->csv_index = $currentRow;

                        $this->csv_results['error'][$currentRow] = $csv_import_model->getErrors();

                        throw new Exception('Problem with your CSV - No Student data saved.');

                    }


                    $processedUsername[] = $csv_import_model->username;


                    $currentRow++;

                    Yii::getLogger()->flush(false);

                }

            }

            fclose($handle);

        }


        $transaction->commit();

    }

    catch(Exception $e)

    {

        $transaction->rollback();

        $this->csv_results = array('inserted' => 0, 'updated' => 0);


        Yii::app()->user->setFlash('importerrors', $errors);

        Yii::app()->user->setFlash('error', "{$e->getMessage()}");

    }



You probably already know this but the try/catch outside your loop is the reason why you stop at the first exception thrown. Following your code’s logic, it looks like you are only allowing a CSV file without any duplicate rows because you stop parsing the CSV and saving to your database when encountering one and I think that goes against what you actually want to do (keep inserting and skipping duplicates). If the CSV isn’t too large, what I would do is remove your throw statements and just check the database for duplicate records at every loop. You could add a rule for this inside your model so that validate() would populate your model’s _errors attribute accordingly, which you would then parse and insert inside your $errors array and use after your loop ends (if not empty, then use it in a flash message or something). This should get you to bypass yii throwing its own db exception and allow you to handle it without killing your loop. If you want to locate duplicates and their line number within the CSV, then just keep what you had for that and do it before running your validate() method

Without doing what I suggested, you could probably get what you have up there to work by removing your throw statements and placing your flash errors code inside an if condition that checks if $errors isn’t empty after the loop, but it doesn’t look like your validate() code checks for duplicate database entries so yii would still throw an exception and end your csv processing.

Also, I think CDbException would be a better place to put your rollback code since this is what yii would throw when encountering a database related error (like running into a constraint error).