Switching Between Sets of Tables On The Fly
I did. Here's how I did it.
First, I gave each table in each set a two-part name, two words separated by an underscore. The first part of the name was descriptive of the data in the table ("chapters","sections", etc.) while the second part of the name identified the data set the table belonged to ("live", "archive", whatever). So the "chapters_live" and the "chapters_archive" tables were exactly the same in structure.
Then I created an application variable called "application.tableSuffix" and set its initial value to "live".
(Can you see where this is leading?)
Finally, I changed my select queries to use table names comprised of the first, data-descriptive part of the table name, the underscore, and the value of application.tableSuffix:
select chapterTitle, chapterIntro
from chapters_#application.tableSuffix#
order by chapterOrder ASC
</cfquery>
Once that was done, I could change the table set my select queries ran against at any time by simply changing the value of application.tableSuffix.
Two things worth noting:
- This technique only makes sense if only one set of tables can be updated by the user: if users can make changes to either set, you'll have to figure out how to resolve the differences between the data in each set.
- If the switch could potentially be in place for awhile, you might want to store the current tableSuffix value in a database record or a file and then retrieve that data in the onApplicationStart method of your Application.cfc file so that the current value is preserved even if the application scope expires.
There are no comments for this entry.
[Add Comment]