• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to manage dates on an auto refreshing demo site

#11
Because the original text file that now contains all sql insert statements I generated by exporting the database to a text file.

However, if you are suggesting that I could go through all the dates, and replace them with expressions based around NOW, that would take some time, but worse, it would mean that when I next generate an SQL export with test data, to keep the demo up to date, I would have to do it all again. (I might have to do this for instance if the table structures change when functionality is added.)

In fact, thinking about it now, when I regenerate all the sample data when it comes time to update on the test server, I am going to have this same date problem.

I think I am going to have to update the SQL text file directly or with some other routine.

So what do other demos do when it come to date sensitivity for refreshed or reset data?

Thanks for your input on this, am still stuck though as to not just what I should do now, but what it is exactly I should have done to repopulate the demo on the website in terms of dates.

Best wishes,

Paul.
Reply

#12
I thought you said the database was completely wiped periodically? So it would be the exact same data except with the dates using NOW() instead of being hard coded.

That's why I don't get what you mean by every time you do an export you would have the same issue. Unless, you're NOT completely wiping the database every month? But if it is a demo site I think you should wipe it.

In short, your 'create database' function/method can all be hard coded except the dates. Right???

What I would do is import all the hard coded values that do not change using a standard import sql. For the inserts I would use codeiniter's database library to do a db->insert() using today's date dynamically.
Practical guide to IgnitedCMS - Book coming soon
Reply

#13
Hi,

Yes, the demo site database is wiped hourly. I use a text file with sql insert statements as posted earlier to read line by line and reinsert the data, replacing all the user generated test data on the demo site.

Live site -> with user generated data
Demo site -> Test data, wiped hourly
Test/Development site -> Uses Test data too, wiped when needed

The test site is used to generate new test data for the demo site when developments are implemented. That is done simply by exporting the sql to a text file (sample was posted earlier).

Since I am just reading the file line by line as a string, which I am then running through a query, I cannot change the contents of the dates fields. Except by string operations like finds and replaces, and even that is going to be complicated by the fact that the dates are not all one date, but a spread over 2-3 months or so. Some inserts will have no dates, other inserts will have multiple date fields, and some insert strings have hundreds of rows they insert.

There are tons of tables so I do not want to write separate code for every table, especially since these table structures may well change during development.

Muuuueeegghhhh :-(

What a mess I seem to have got myself into.

I cannot see there being an easy answer to this.
Reply

#14
I think I have decided that I need a new script, that I run periodically on the test/development site when I am ready to implement an update on the demo and live sites.

This script will have to be written for each table. It will rewrite date fields with new date information with normal php processing. Then I can just generate the sql text file as it is now and use this on the demo site. At least that way I am dealing with normal database fields in the normal way instead of a giant text file.

This script will have to be revisited on every site development to make sure any db changes are reflected in it, but that seems the least tedious of all the options.

I think that is what I am going to do, although I am still frustrated and a bit annoyed with it.

Best wishes,

Paul.
Reply

#15
No sorry I still don't understand the problem.

It doesn't matter whether the dates are spread over 2-3 months or if they are in different columns.

Let's take an example, you have meticulously hand coded your demo system to work with today's date.

Ok let's say you have an office reminder set for 15th December 2015 on your demo site.

But let's say it is now 18th of December 2015 and a demo user is accessing your site.

So all you do is take today's date which is 18th December 2015 and simply minus 15th of December.

This equals three days. Therefore your adjust formula is simple. simply increase ALL your dates and reminders in the database by 3 days. Couldn't be simpler. If you want I can write a simple script showing this.
Practical guide to IgnitedCMS - Book coming soon
Reply

#16
Quote:Hmm, if that is the case then it sounds like an initial bad planning phase for your data( please don't take that the wrong way.)

It was definitely bad planning on my part.

The problem was how I decided to implement the wiping. With a text file containing all the sql for updating the data, how could I even find the date data to implement a php change on. Especially since any hunt of that data might pull out text inputs of dates in user input fields.

However, I have a solution.

On the development site I have added the demo wiping script to update the data to the same as on the demo site. Then yesterday I spent ages writing a script that updates all the date fields, based around today and the current month. One I have run that update I can then simply export the database data back into an sql file, to use on the demo site for wiping.

Apart from a few tables, most of the dates I could do as random intervals from a given date. I also managed to code it all into a single function that I call table by table, from a singe array of table names and date fields, which I was quite pleased with. There were still a few more complicated tables that I had to write individual scripts for, but it is done now.

I also now have a mechanism to easily refresh and edit all the demo data, making it better or to fit new functionality. I am quite pleased with that.

Sorry I went on for so long. And if my explanations were a bit confusing. It was a strange situation that was definitely my fault in that I didn't realize my demo data dates would go out of date. With hindsight I should have seen that coming.

Thanks for all your input and help, it really did help me to clarify the problem and possible solution in my own mind.

Best wishes,

Paul.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.