批准数据库编辑-mysql [英] Database Edit with Approval - mysql

问题描述

我目前有一个可供许多用户访问和更改的数据库.也是一个日志数据库，它使用触发器将所有更改存储到数据库中的表中.

I currently have a database which many users can access and make changes to. The is also a log database that stores all changes to tables within the database using triggers.

我想添加在数据库中更改之前批准编辑的功能.

I would like to add the ability to approve edits before they are changed in the database.

解决这个问题的最佳方法是什么?

What would be the best way to go about this?

推荐答案

在我们的一个网站上，我们有一些类似的东西，我们添加了一堆表:

We have something similar on one of our sites, we've added a bunch of tables:

users sites ...等等

然后我们有一堆影子表:

Then we have a bunch of shadow tables:

users-shadow sites-shadow ...等等

影子表具有与实际表相同的结构，除了为进行更改的用户增加了一行.因此，首先，当需要批准其数据库操作的用户提交更改时，我们将使用此查询:

The shadow tables have identical structures to the real tables except for an added line for the user who made the change. So first we use this query when a change is submitted by a user who needs to have his/her database actions approved:

REPLACE INTO users-shadow (user_mod,id,username,password,salt...) VALUES (16,50,'bob','stuff','salt'...);

显然，请确保未对它开放以进行注入，请使用获得批准后，只需将shadow表中的一行从shadow表中删除，删除user_mod值并将更改(非空值)插入到真实表中(如果是使用 REPLACE 语法指定的).我们在perl中执行此逻辑，因此可悲的是手头没有任何SQL.

When approved, a row in the shadow table is simply removed from the shadow table, the user_mod value dropped and changes (non-null values) inserted into the real table (or updated if an id is specified, using REPLACE syntax). We do this logic in perl so sadly don't have any SQL on hand for it.

请记住，SQL REPLACE执行DELETE和INSERT而不是UPDATE.您将需要修改所有触发器以允许这种行为.

Remember that SQL REPLACE does a DELETE and an INSERT rather than an UPDATE. You will need to amend any triggers to allow for this behaviour.

注意:我们之所以没有使用批准"标志，是因为我们需要修改现有记录的能力，当然我们不能使用相同的主键拥有多个记录.

这篇关于批准数据库编辑-mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！