处理数据库中重复错误的正确方法是什么

发布于 2024-12-08 11:23:46 字数 787 浏览 1 评论 0原文

我正在数据库中实现订阅。电子邮件必须是唯一的，所以我在数据库中有一个唯一的索引。我的页面初始化中有此代码：

$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
    }
}

SQLException 中的唯一信息是格式化的 HTML 消息，这是检测错误是否来自重复条目的唯一方法吗？

原文

I'm implementing a subscription 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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

菩提树下叶撕阳。 2024-12-15 11:23:46

这是一种方法：

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

虽然如果你想对重复执行自定义操作，你应该将 getBy 移到模型，进入页面的逻辑。

回复收藏 0 原文

阿楠 2024-12-15 11:23:46

正如@Col建议的，我们想使用“插入忽略”。

$form->update() 依赖于 Model->update()，然后 Model->update() 又依赖于 DSQL 类来构建查询。 DSQL 确实支持选项，但模型会生成为您提供新鲜的 SQL。

Model->dsql() 为模型构建查询。它可以与多个“实例”一起运行，其中每个实例都有一个单独的查询。我不太喜欢这种方法，可能会添加新的模型类，但它目前有效。

看看这里：
https://github.com/atk4/atk4 -addons/blob/master/mvc/Model/MVCTable.php#L933

insertRecord() 函数多次调用 dsql('modify',false) 来构建查询。您可以做的最简单的事情可能是：

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

插入记录后，Agile Toolkit 将自动尝试加载新添加的记录。但是，它将使用相关条件。我认为如果忽略记录，您会得到无论如何都会引发异常。如果可能，请避免工作流程中出现异常。异常是 CPU 密集型的，因为它们捕获回溯。

唯一的方法可能是您完全重新定义 insertRecord 。这并不理想，但它允许您按照自己的意愿执行单个查询。

我更喜欢使用 loadBy（或 getBy）手动检查条件，因为它会考虑模型条件和连接。例如，您的表上可能有软删除，虽然 MySQL 键不允许您输入，但模型可以，并且模型方式对于业务逻辑也更有意义。

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.

回复收藏 0 原文