Update Soundings UUID values to INT

soundings_uuid_2_int.cfm

<cfsetting showdebugoutput="false" requesttimeout="10000" />
<h1>Update Soundings UUID values to INT</h1>

<cfset dsn = "YOUR_DSN_HERE" />


<cfquery datasource="#dsn#">DROP TABLE IF EXISTS  `uuids`</cfquery>

<!--- Create the UUID table --->
<cfquery datasource="#dsn#">
	CREATE TABLE `uuids` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `uuid` varchar(40) DEFAULT NULL,
	  PRIMARY KEY (`id`),
	  UNIQUE KEY `uuidk` (`uuid`)
	) ENGINE=MyISAM 	
</cfquery>

<cfflush/>

<!--- Get all the UUID's you can find and record them. The Auto Number ID will give us our new int reference--->
<!--- ANSWERS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT id FROM answers
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT questionidfk FROM answers
</cfquery>

<!--- QUESTIONS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT id FROM questions
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT surveyidfk FROM questions
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT questiontypeidfk FROM questions
</cfquery>

<!--- QUESTIONTYPES --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT id FROM questiontypes
</cfquery>

<!--- RESULTS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT owneridfk FROM results
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT questionidfk FROM results
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT answeridfk FROM results
</cfquery>

<!--- SURVEY_RESULTS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT ownerid FROM survey_results
</cfquery>
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT surveyidfk FROM survey_results
</cfquery>

<!--- SURVEYS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT id FROM surveys
</cfquery>

<!--- USERS --->
<cfquery datasource="#dsn#">
	INSERT IGNORE INTO uuids (uuid) SELECT DISTINCT id FROM users
</cfquery>
<h2>All the UUID's captured</h2>
<cfflush/>


<!--- Now go and get all those UUID's and there new INT reference --->

<cfquery datasource="#dsn#" name="q" cachedwithin="#createTimeSpan(0,1,0,0)#">
	SELECT * FROM uuids
</cfquery>
<cfset s = {} />
<cfloop query="q" >
	<cfset s[q.uuid] = q.id />
</cfloop>

<h3>Retrieve all the new references</h3>
<cfflush/>

<!--- Lets go back to the tables and start updating the UUID's --->

<!--- ANSWERS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM answers
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE answers SET id = '#structFind(s,q1.id)#' WHERE id = '#q1.id#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE answers SET questionidfk = '#structFind(s,q1.questionidfk)#' WHERE questionidfk = '#q1.questionidfk#'
	</cfquery>
</cfloop>

<!--- QUESTIONS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM questions
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE questions SET id = '#structFind(s,q1.id)#' WHERE id = '#q1.id#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE questions SET surveyidfk = '#structFind(s,q1.surveyidfk	)#' WHERE surveyidfk = '#q1.surveyidfk#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE questions SET questiontypeidfk = '#structFind(s,q1.questiontypeidfk)#' WHERE questiontypeidfk = '#q1.questiontypeidfk#'
	</cfquery>
</cfloop>

<!--- QUESTIONTYPES --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM questiontypes
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE questiontypes SET id = '#structFind(s,q1.id)#' WHERE id = '#q1.id#'
	</cfquery>
</cfloop>

<!--- RESULTS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM results
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE results SET owneridfk = '#structFind(s,q1.owneridfk)#' WHERE owneridfk = '#q1.owneridfk#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE results SET questionidfk = '#structFind(s,q1.questionidfk)#' WHERE questionidfk = '#q1.questionidfk#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE results SET answeridfk = '#structFind(s,q1.answeridfk)#' WHERE answeridfk = '#q1.answeridfk#'
	</cfquery>
</cfloop>

<!--- SURVEY_RESULTS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM survey_results
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE survey_results SET ownerid = '#structFind(s,q1.ownerid)#' WHERE ownerid = '#q1.ownerid#'
	</cfquery>
	<cfquery datasource="#dsn#" >
		UPDATE survey_results SET surveyidfk = '#structFind(s,q1.surveyidfk)#' WHERE surveyidfk = '#q1.surveyidfk#'
	</cfquery>
</cfloop>

<!--- SURVEYS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM surveys
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE surveys SET id = '#structFind(s,q1.id)#' WHERE id = '#q1.id#'
	</cfquery>
</cfloop>

<!--- USERS --->
<cfquery datasource="#dsn#" name="q1">
	SELECT * FROM users
</cfquery>
<cfloop query="q1" >
	<cfquery datasource="#dsn#" >
		UPDATE users SET id = '#structFind(s,q1.id)#' WHERE id = '#q1.id#'
	</cfquery>
</cfloop>


<h3>New ID's updated</h3>
<cfflush/>




<!--- Go back to the tables and set the VARCHARs to ints --->
<cfquery datasource="#dsn#">ALTER TABLE `users` CHANGE `id` `id` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `surveys` CHANGE `id` `id` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `survey_results` CHANGE `ownerid` `ownerid` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `survey_results` CHANGE `surveyidfk` `surveyidfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `results` CHANGE `owneridfk` `owneridfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `results` CHANGE `questionidfk` `questionidfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `results` CHANGE `answeridfk` `answeridfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `questiontypes` CHANGE `id` `id` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `questions` CHANGE `id` `id` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `questions` CHANGE `surveyidfk` `surveyidfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `questions` CHANGE `questiontypeidfk` `questiontypeidfk` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `answers` CHANGE `id` `id` int(11) NULL DEFAULT NULL;</cfquery>
<cfquery datasource="#dsn#">ALTER TABLE `answers` CHANGE `questionidfk` `questionidfk` int(11) NULL DEFAULT NULL;</cfquery>