What is the correct way of handling duplicate errors in db

I'm implementing a subscripti开发者_开发技巧on in a DB. The email must be unique, so I have a UNIQUE index in the database. I have this code in my page init:

$f = $p->add('MVCForm');
$f->setModel('Account',array('name','surname','email'));
$f->elements['Save']->setLabel('Subscribe');

if($f->isSubmitted())
{
    try
    {
         $f->update();

         //More useful code to execute when all is ok :)

    }
    catch(Exception_ValidityCheck $v)
    {
        //Handles validity constraint from the model
        $f->getElement($v->getField())->displayFieldError($v->getMessage());
    }
    catch(SQLException $se)
    {
        //If I'm here there is a problem with the db/query or a duplicate email
    }
}

The only information in SQLException is a formatted HTML message, is this the only way to detect if the error is from a duplicated entry?

Here is one way to do it:

https://github.com/atk4/atk4-web/blob/master/lib/Model/ATK/User.php#L95

Although if you want to perform custom action on duplication, you should move getBy outside of the model, into page's logic.

As @Col suggested, we want to use "insert ignore".

$form->update() relies on Model->update() which then relies on DSQL class for building query. DSQL does support options, but model would generate fresh SQL for you.

Model->dsql() builds a Query for the Model. It can function with several "instances", where each instance has a separate query. I don't particularly like this approach and might add new model class, but it works for now.

Take a look here: https://github.com/atk4/atk4-addons/blob/master/mvc/Model/MVCTable.php#L933

insertRecord() function calls dsql('modify',false) several times to build the query. The simplest thing you could do, probably, is:

function insertRecord($data=array()){
    $this->dsql('modify',false)->option('IGNORE');
    return parent::insertRecord($data);
}

after record is inserted, Agile Toolkit will automatically attempt to load newly added record. It will, however, use the relevant conditions. I think than if record is ignored, you'll get exception raised anyway. If possible, avoid exceptions in your workflow. Exceptions are CPU intensive since they capture backtrace.

The only way might be for you to redefine insertRecord completely. It's not ideal, but it would allow you to do a single query like you want.

I prefer to manually check the condition with loadBy (or getBy) because it takes model conditions and joins into account. For example, you might have soft delete on your table and while MySQL key would not let you enter, Model would and the model-way makes more sense too for business logic.

Why don't you want to run simple select to check if email is already taken?

Or make it INSERT IGNORE and then check affected_rows