I have found that CF is an excellent tool to do this type of
restructuring... you can do this semi-interactively.
I use a combination of home-grown interactive tools (written in CF):
1) Interactive query processor
2) Batch Qyery Processor (a skeleton CF program to copy or restore
a database)
This includes the <cfquery> tags, but not the SQL statements nor any data
manipulation logic.
Query tags are provided to:
Conditionally Drop any Existing Table Named n (New Structure)
Conditionally Create Table n (New Structure)
Conditionally Extract data from Table o (Old Structure)
Conditionally Extract data from Table o (Old Structure)
Conditionally Loop over rows from Table o (Old Structure) and
Insert into Table n (New Structure)
Conditionally Loop over rows from Table o (Old Structure) and
Update Table n (New Structure)
Display Count of Extract Query Rows
Display Count of Insert Query Rows
Display Count of Update Query Rows
Any of the queries can access a remote database or a local database
(through either cfquery of cfhttp/wddx).
3) Remote query processor (Remote WDDX stub for above)
Here's how the process usually works:
1) Analyze the requirements and determine the new table structures, Names,
etc.
2) If remote processing of from, to (or both) databases is required, this
is controlled by a settings in the Interactive and Batch programs as is
the datasource, username, password, and any appendage to the
FROM clause to
use a different datasource (e.g, a different MS_Access db)
3) With the Interactive Query Processor, develop the queries to:
a) Create the new tables
b) Develop and test the queries necessary to extract data from existing
tables and insert/update into new tables. At this point, we're only
talking about the queries for extracting and inserting data columns...
not the logic for manipulation of the data.
c) Copy/Paste the create, extract, insert, update queries (there may be
several) into the skeleton batch query program.
d) Add any data manipulation logic, e.g. reformat Name into FirstName and
LastName.
e) Save the modified skeleton as a unique, self-contained batch conversion
program for this particular table(s).
f) Test the process and adjust as necessary
g) Examine/validate the process with the Interactive Query Program
4) Batch run the conversion program
5) Copy/adjust programs to use new structures
6) Test programs against new structure
Repeat the above process for each table change and or to include
(synchronize) data changed since conversion.
This probably takes longer to describe than to execute.
I have successfully used this to restructure/normalize large existing
databases and to (simultaneously) migrate to other sites.
It works equally well going from database A to database B directly
(online), or using an intermediary database as intermediary
(download/upload).
HTH
Dick
At 11:05 AM -0700 6/14/01, Jeffry Houser wrote:
> This has happened to me more than once, I'm wondering how everyone else
>handles it.
>
> An application is needed, the database is designed, the application is
>built debugged, and rolled out.
>
> Time passes, things change, and the original application no longer meets
>the needs of the growing company. Someone is hired (possibly even the same
>developer who did the original application) to modify the application. The
>database table structure has to be re-worked.
>
> I doubt that this situation is unique to projects I have worked on. The
>question is, how do you go about migrating data from the old structure to
>the new structure? Are their third party tools for this purpose? Or do
>ya'll tend to write your own migration code (and in what language?)?
>
> In some cases I have been able to use the SQL Server DTS to transfer data.
> In other cases I have written ColdFusion code for this purpose.
>
> As an example from one project I'm currently working on:
>
> The application was built around a bunch of different companies, and
>everything in the app is based on the companyID.
> This was the company table: (CompanyID, CompanyName, address, city,
>state, zip, url)
>
> However, 2 years down the line (today), this has become a bit
>problematic. Now they want to modify the application so that a single
>company can have multiple locations, and everything is based around the
>location. So, now we are splitting up the company table into two tables:
>
> Company (companyID, companyname, url)
> CompanyLocation (companylocationID, companyID, address, city, state, zip)
>
>
> How would you migrate the company information from the old structure to
>the other, while still preserving the relevant relationships between data?
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
