Hello ~ I’m Milo! This is a complete series of interface test platform tutorials, hoping to learn with you, from 0 to 1 to build an open source platform. Welcome to pay attention to my public number test development pit goods, get the latest article tutorial!

review

In the last section, we basically solved the problem of adding, deleting, and modifying data constructors. In this article, we will talk about soft deletion.

What is soft delete

First of all, I did not refer to the specific soft delete information, we are interested in their own search, WHAT I said is their own understanding.

Delete data, generally we delete data from a database or other storage medium, for example 🌰 chestnut:

We need to delete user A from the data table, so the most common way is:

delete from user where name='A';
Copy the code

After the deletion, there is no more information about this data in our data table. If we still need this user, we need to insert again.

What about soft deletes? Soft delete can also be called logical delete. For example, we define a field for the User table: deleted_AT (representing the time when the data was deleted). If this field is not empty, it indicates that the User was not deleted; otherwise, it indicates that the User was deleted. Because it is a logical “delete”, it is also called a logical delete because it does not actually delete the data.

Advantages of soft deletion

What are the advantages of soft deletion versus physical deletion? I think there are the following aspects:

  • Data has not beenTrue to deleteThe data is more reliable
  • You can know the time of the deletion by assigning the timestamp of the deletion to deleted_at, which implicitly records the user’s deletion
  • It’s no different for the user than a physical delete, right
  • Have a natural recycling station function

Disadvantages of soft deletion

The benefits of soft delete have been mentioned above, but here’s what people love and hate about soft delete.

  • Querying data is more complex

    Enter deleted_at is NULL in the query condition; otherwise, deleted data is displayed.

    Apart from this, there is a more difficult problem, let me explain it slowly.

The index regression of

Indexing is a tricky issue, especially if we have unique indexes, so let’s look at a scenario.

  • The users table

    We have an email field in our user table, but as we all know, email cannot be repeated, so we need to add a unique index to it.

The scene of a

In the first scenario, we create a single piece of data for the user table:

email  -> 123456@qq.com
deleted_at -> null
Copy the code

We will delete this data, but because of soft delete we will write SQL like this:

update user set deleted_at = now() where email='[email protected]';
Copy the code

You think this is all over?

The next thing I know is that I can’t insert another [email protected] because of the unique index email_uidx.

performInsert statement/ / duplicate index error is reported.

So how can we solve this problem? Let’s look at scenario two.

Try to work it out

To solve the unique index problem in scenario 1, we thought of a federated index. What is a federated unique index? If multiple fields are the same, the data is duplicated. In other words, the duplicate index error is triggered.

So we create a joint unique index:

ALTER TABLE user ADD UNIQUE INDEX(email, deleted_at);
Copy the code

At this point, let’s look at scene 1:

  • Create a user
email=12345@qq.com
deleted_at=null
Copy the code

Then, we delete it, and it becomes:

email=12345@qq.com
deleted_at=2021- 09 -12 20:13:00
Copy the code

Then we continued to add the [email protected] user and found that it was ready to be added.

You think this is the end of it? Let’s look at scenario two:

Scenario 2

This is easier. We insert2 the same data:

insert into user (email, deleted_at) values ('[email protected]'.null);

insert into user (email, deleted_at) values ('[email protected]'.null);
Copy the code

Haven’t written SQL for a long time, also don’t know to write right, ok look, about this meaning.

At this point, a miracle occurs, it can be found that both data inserts successfully, that is to say, the uniqueness of the previous email cannot be guaranteed.

Why is that?

If a column in a joint index is null, the joint index is automatically invalidated.

That is really very uncomfortable, it can be said that fixing one bug led to another bug.

Solution 1

We can set deleted_at to the same increment id as the primary key. When deleted_at is deleted, it is +1, and when it is restored, it is +1. The default value is 0.

Solution 2

We can set deleted_at to a timestamp, which defaults to 0 (not deleted). In general, the timestamp of a manually deleted operation must change slightly so that the index will not take effect, that is, the previous data will not be affected.

However, there is a disadvantage: if a data is deleted more than once, the database will have many of the same deleted data, of course, the average person will not do boring things.

class Model(Base) :
    deleted_at = Column(BIGINT, nullable=False, default=0)
Copy the code

You can see that deleted_at is defined this way. When there is a delete operation, we set deleted_at = time.time().

import time
model.deleted_at = time.time()
Copy the code

If you have a more suitable plan, welcome to discuss together, thank you very much!