Farcry MYSQL Table Name Case Fixer

Annoyingly, depending on the setup of the MYSQL server at your host (and more specifically if they host their MYSQL server on a Windows box) the case sensitivity setting of the database may be set to be ‘case insensitive’… which is a pain if you are moving from a Linux MYSQL server to Windows and back etc because Farcry will not think that any of the object tables have been deployed along with all sorts of other issues. So to get around this issue I wrote a script that produces a nice bunch of sql statements to plant into your MYSQL GUI (eg. sqlYog) and it will rename all the tables back ito the case expected by farcry. This beats the heck out of doing it manually…. zippedy doo!


<cfset lcoretables = "container_aRules,dmExternalGroupToPolicyGroup,dmGroup,dmPermission,dmPermissionBarnacle,dmPolicyGroup,dmUser,dmUserToGroup,fqAudit,refCategories,refContainers,refDSAS,reffriendlyURL,refObjects,statsCountries,statsDays,statsHours,statsSearch" />

<cfloop index="i" list="#lcoretables#">
	<cfoutput>ALTER table #lcase(i)# RENAME _#i#;</cfoutput>
</cfloop>

<cfloop index="i" list="#lcoretables#">
	<cfoutput>ALTER table _#i# RENAME #i#;</cfoutput>
</cfloop>

<cfset lTypes = "#structKeyList(application.types)#">

<cfloop list="#lTypes#" index="st">
	<cfoutput>ALTER table #lcase(st)# RENAME _#st#;</cfoutput>
</cfloop>

<cfloop list="#lTypes#" index="st">
	<cfoutput>ALTER table _#st# RENAME #st#;</cfoutput>
</cfloop>

</cfset><cfset lRules = "#structKeyList(application.rules)#">

<cfloop list="#lRules#" index="st">
	<cfoutput>ALTER table #lcase(st)# RENAME _#st#;</cfoutput>
</cfloop>

<cfloop list="#lRules#" index="st">
	<cfoutput>ALTER table _#st# RENAME #st#;</cfoutput>
</cfloop>

<cfloop index="i" list="#lTypes#"></cfloop><cfloop collection="#application.types[i].stProps#" item="st"><cfif application.types[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table #lcase(i&"_"&st)# RENAME _#i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lTypes#"></cfloop><cfloop collection="#application.types[i].stProps#" item="st"><cfif application.types[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table _#i&"_"&st# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lRules#"></cfloop><cfloop collection="#application.rules[i].stProps#" item="st"><cfif application.rules[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table #lcase(i&"_"&st)# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lRules#"></cfloop><cfloop collection="#application.rules[i].stProps#" item="st"><cfif application.rules[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table _#i&"_"&st# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>

Post a Comment

*
*