How can I disable some mysql dataset for a user specified time frame in my webapp and restore it automatically

i am developing a web app to admininstrate a mysql database.
So far this work is making a good progress.

Now i need a feature to deactivate some datasets for a time frame the user can choose in the web app.

After deactivating: The dataset shouldn´t be active in the database, but the user should be able to restore the dataset anytime. (i will create a page: “Deactivated”)

If the user doesnt restore the dataset. Then the dataset should be restored automatically when the specified time frame by the user is over.

How can i build this feature and disable some dataset for a time frame and restore it again after?

hope u understand and can help me 😉

i mean with dataset=rows in a database

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You cannot solve this on the MySQL level. This database engine doesn’t recognize data sets and time-framing them as you describe (As far as I know).

Because of that, you have to solve this on the application level.
I don’t know what the structure of your database is, so let’s say you store your data in the table named dataset.

1. part: Deactivated

I need a feature to deactivate some datasets

A potential solution here is to add a flag to the table dataset. And let’s name it active. See MySQL Bool.

For this page

I will create a page: “Deactivated”
you can fetch data by this query

SELECT *
FROM dataset
WHERE active = false;

The consequence of this update (adding flag).
You should update all existing queries with this addition to the where clause: WHERE ... AND active = true.
For example, when you fetching data for a user with ID 10, the query is:

SELECT * 
FROM dataset
WHERE user_id = 10
    AND active = true;

2. part: Time frame

For this part, I propose to use another column in the dataset table – let’s name it activation_date.

So, what is an idea here?
In this column, you store when that row should become active again.

For activation data, you need a job-scheduling framework. And each midnight, a background process executes this query.

UPDATE dataset
SET active = true
WHERE activation_date >= CURRENT_DATE();

Of course, you can use some more granular time; or activate your job at another time. It depends on the requirements.

Note for the job-scheduling framework. For Java, it could be Quartz. I am not sure which programming language you use; I guess, it has an equivalent tool like Quartz.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0