CFLib.org – Common Function Library Project

<cfscript>
	//BUILD TWO SAMPLE QUERIES
	q1 = queryNew("foo,bar,thing");
	q2 = queryNew("foo,bar,thing");
	
	queryAddRow(q1,3);
	queryAddRow(q2,4);
	
	querySetCell(q1,"foo","1",1);
	querySetCell(q1,"foo","2",2);
	querySetCell(q1,"foo","3",3);
	querySetCell(q1,"bar","a",1);
	querySetCell(q1,"bar","b",2);
	querySetCell(q1,"bar","c",3);
	querySetCell(q1,"thing","w",1);
	querySetCell(q1,"thing","x",2);
	querySetCell(q1,"thing","y",3);
	
	querySetCell(q2,"foo","1",1);
	querySetCell(q2,"foo","new",2);
	querySetCell(q2,"foo","3",3);
	querySetCell(q2,"foo","4",4);
	querySetCell(q2,"bar","aaa",1);
	querySetCell(q2,"bar","b",2);
	querySetCell(q2,"bar","c",3);	
	querySetCell(q2,"bar","d",4);
	querySetCell(q2,"thing","w",1);
	querySetCell(q2,"thing","changed",2);
	querySetCell(q2,"thing","y",3);
	querySetCell(q2,"thing","z",4);
</cfscript>

A DUMP OF THE ORIGINAL QUERIES:
<table>
	<tr>
		<td valign="top"><cfdump var="#q1#"></td>
		<td valign="top"><cfdump var="#q2#"></td>
	</tr>
</table>
<br /><br />
<hr>
<cfset diff = queryDiff(q1,q2)>
Were any rows changed?: <strong><cfoutput>#yesNoFormat(structCount(diff.changed))#</cfoutput></strong>
<br />
<cfif structCount(diff.changed)>
Which rows were changed?: <cfoutput>#structKeyList(diff.changed)#</cfoutput>
<br />
Show the columns that changed in each of those rows:
<br />
<cfoutput>
<cfloop collection="#diff.changed#" item="rowIndex">
	In Row <strong>#rowIndex#:</strong>
	<ul>
	<cfloop collection="#diff.changed[rowIndex]#" item="colName">
		<li><strong>#colName#</strong> was <strong>#diff.changed[rowIndex][colName].then#</strong> and now is <strong>#diff.changed[rowIndex][colName].now#</strong>
	</cfloop>
	</ul>
</cfloop>
</cfoutput>
</cfif>
<hr>
Were any rows added?: <strong><cfoutput>#yesNoFormat(structCount(diff.added))#</cfoutput></strong>
<cfif structCount(diff.added)>
<br />
How many rows were added?: <strong><cfoutput>#structCount(diff.added)#</cfoutput></strong>
<br />
Show the rows that were added:
<cfset cols = listToArray(diff.query1.columnList)>
<cfoutput>
<table border=1>
	<tr>
		<td><em>row</em></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<td>#cols[ii]#</td>
		</cfloop>
	</tr>
	<cfloop collection="#diff.added#" item="rowIndex">
	<tr>
		<td><em>#rowIndex#</em></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<cfset thisColName = cols[ii]>
			<td>#diff.query2[thisColName][rowIndex]#</td>
		</cfloop>		
	</tr>
	</cfloop>
</table>
</cfoutput>
</cfif>
<br />
<hr>
Were any rows removed?: <strong><cfoutput>#yesNoFormat(structCount(diff.removed))#</cfoutput></strong>
<cfif structCount(diff.removed)>
<br />
How many rows were added?: <strong><cfoutput>#structCount(diff.removed)#</cfoutput></strong>
<br />
Show the rows that were removed:
<cfset cols = listToArray(diff.query1.columnList)>
<cfoutput>
<table border=1>
	<tr>
		<td><em>row</em></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<td>#cols[ii]#</td>
		</cfloop>
	</tr>
	<cfloop collection="#diff.removed#" item="rowIndex">
	<tr>
		<td><em>#rowIndex#</em></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<cfset thisColName = cols[ii]>
			<td>#diff.query1[thisColName][rowIndex]#</td>
		</cfloop>		
	</tr>
	</cfloop>
</table>
</cfoutput>
</cfif>

<br />
<hr>
SHOW A COMPLETE COMPARISON OF THE QUERIES:

<cfset cols = listToArray(diff.query1.columnList)>
<cfoutput>
<table border=1>
	<tr>
		<td><em>row</em></td>
		<td colspan="#arrayLen(cols)#">
			<em>ORIGINAL QUERY</em>
		</td>
		<td>.</td>
		<td colspan="#arrayLen(cols)#">
			<em>NEW QUERY</em>
		</td>
	</tr>
	<tr>
		<td></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<td><strong>#cols[ii]#</strong></td>
		</cfloop>
			<td>.</td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<td><strong>#cols[ii]#</strong></td>
		</cfloop>		
	</tr>
	<cfloop from="1" to="#iif(diff.query1.recordCount LT diff.query2.recordCount,diff.query2.recordCount,diff.query1.recordCount)#" index="rowIndex">
	<tr>
		<td><em>#rowIndex#</em></td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			
			<cfif rowIndex LTE diff.query1.recordCount>
				<cfset thisColName = cols[ii]>
				<td>
				#diff.query1[thisColName][rowIndex]#
				</td>
			<cfelse>
				<td style="background:##cccccc;"></td>	
			</cfif>
		</cfloop>
		<td>.</td>
		<cfloop from="1" to="#arrayLen(cols)#" index="ii">
			<cfif rowIndex LTE diff.query2.recordCount>
				<cfscript>
					thisColName = cols[ii];
					thisStyle = "font-weight:normal;";
					//if it's a changed value, outline it in red
					if(structKeyExists(diff.changed,rowIndex) AND structKeyExists(diff.changed[rowIndex],thisColName)){
						thisStyle = "border: 2px red dotted;";
					}
					if(structKeyExists(diff.added,rowIndex)){
						thisStyle = "background: ##ffffcc;";
					}
				</cfscript>			
				<td style="#thisStyle#">
				#diff.query2[thisColName][rowIndex]#
				</td>
			<cfelse>
				<td style="background:##cccccc;"></td>	
			</cfif>	
		</cfloop>		
	</tr>
	</cfloop>
</table>
</cfoutput>
<br /><br />
<hr>
A DUMP OF THE RESULT OF queryDiff()
<cfdump var="#diff#">