The customers for my existing program (non-Servoy) have two or three dates during the year where they ask me to provide them with an offline copy of the data, so that their users can continue making relevant changes on the live server. The offline copy represents a “frozen” or official record for reporting purposes. The admins run it in a single-user (desktop application) mode to clean it up, generate reports of discrepancies, and correct them. For the larger customers, the data file is in the range of 2 to 6 GB (that includes indexes).
With a Servoy solution, what’s the best way to do this? I was thinking –
Some way to write the data files to a place where the admin can download them. (They would then need a single-user version of Servoy.)
Create a special “as-of”, time-stamp field in all tables on the live server, and then have the server make a copy of each record in the program. These records would normally be invisible, but could be shown if the admin wanted to see the records for a particular “as-of” date. (Problem: how to deal with issuing/matching new pks and foreign keys, and how to deal with the space and time of duplicating the entire data file each time they want to run a copy.)
Some other idea?
I’m writing the program in Servoy as a SaaS, so I wouldn’t want the admin for customer 1 to have an offline copy of data belonging to the other customers.
Perhaps I have misinterpreted your requirements, however if you simply need to make those records you have frozen unavailable to the general application user, you could add a column to your table (the main table to which other records are related) called something like ‘hidden’ as an int type (and set as a chekbox if you need to see it on your forms).
In your application start-up run a table filter parameter so that the records with that column true are never generally shown.
When the records need to be frozen, mark this column true, the table filter parameter will exclude those records.
Your admin will be able to see those records exclusively if you show a form on which he has access and which removes the table filter parameter so he can do his work, and then replaces the filter when he is done and closes the form!
In this way - unless it is essential to have this data in another Db (in which case you will need to duplicate your Db’s and transfer the data with the hidden column checked) this keeps all of the data in the same repository but hides it when it is frozen.
An enhancement would be to add a date stamp column too so you know when it was marked ‘hidden’. Admin can then go back to certain blocks for editing or reporting, and you can still run reports on ALL of the data if required at some future date
Hope that makes sense and was to the point of your question?
Yes, that is what I was thinking with Option 2, except I was using a date-time field rather than an integer (ersatz Boolean) field. The table filter would allow the admin to run the db as if it were, say, December 1, 2011 regardless of what today’s date is, and how many changes the other users have made since then.
What concerns me with this approach is how large the data file could become, and how long it would take to duplicate all those records. For example, if (for one customer) there are 80,000 students, 100,000 contacts, 240,000 services, 500,000 attendance records, etc. (going through 25 or so tables), then the number of records to be handled becomes large. Each record gets duplicated, and then each needs to be re-linked to related items with new keys. What happens if one of the admins decides to perform three or four of these freezes on the same day, in the middle of the day – does it bring the server to its knees?
There’s another potential issue - the customers have wanted to protect their data by having a copy in their own hands, so that if everything else fails, at least they will have something to fall back on. It’s particularly important for the “frozen” records related to a reporting date, as they can be asked many months later to produce justifications for their reporting numbers. Is there a way to address this perceived need? Is there a way, procedurally, to create a separate db containing just their data on a particular date, and if so are there any advantages to this approach?
I see your challenge Don. Where data needs to be kicked out for others to keep safe etc and it wont be re-integrated with the main Db then you could consider exporting it as XML. Where others simply need to look at data segregated by date (and frozen so not changeable) you are much better off not copying the data -just make it available to the necessary users. The idea of keeping full copies together with related data too is very expensive in server work.