Skip to content

Instantly share code, notes, and snippets.

@andyj
Created May 24, 2011 22:45
Show Gist options
  • Save andyj/989931 to your computer and use it in GitHub Desktop.
Save andyj/989931 to your computer and use it in GitHub Desktop.
Update Soundings UUID values to INT
<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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment