For this website, I have just built a public demo for the web app (with restricted functionality and a shared public login etc) so you can test drive the service before signing up.
At the moment it resets the data every 60mins by truncating all the relevant tables and then reading in SQL statements from a file, which repopulates the database with the fresh test data again. This is to delete all the test data from users and restore all the deletions etc, tidying up all the playing that users will do during the demo.
So far so good and all working fine.
However, a lot of the test data includes dates, like due dates and start dates etc. Over time these dates will all become in the past and the restored data will be restoring data that now looks messy. So a new user logging into the demo system will see loads of alerts about overdue stuff etc. The calendar will be blank on the current month because all the events will be in the past. All the trackers and reminders will be alerted as 'overdue' as well etc etc.
So my question is:
If I am resetting the data by reading in each sql insert statement, one line at a time, and running the query from that line of text, how can I adjust any dates? Is there a way to do an insert statement that has a relative date, like say, make this date two weeks after today? And even if there is, I generate the SQL file by exporting my tables in an SQL file. A simple and quick procedure. I create all the data on the system, export the file and use that to restore the data on the demo site. Without changing the database (ie adding fields for relative from today for all the dates) how can I make these dates relative to today and keep my simple procedure for generating the test data?
The system will change and develop over time, so as it is now, I just replicate the database, upload the sql file, and away the demo goes again all fresh and up to date. So I need to be able to do this 'date' thing without changing the database tables.
Does anyone have any suggestions or ideas about how I could approach getting these dates to stay current? Or do I have to just regularly reset my Sql file with new data. (Which is a pain as there is so much of it).
I hope that all makes sense,
Thanks in advance for any help,
Best wishes,
Paul.
