CFLib.org – Common Function Library Project

queryDiff(q1, q2)

Last updated May 26, 2003

author

Nathan Dintenfass

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Given two queries queryDiff() returns a structure with information about which rows/cells changed and which rows were added or removed. NOTE: This version works ONLY with 2 queries with the same columns! The resulting structures contains the following keys: CHANGED: a structure of structures -- the top level keys are the row indexes, and the second level keys are the column names. Each column keyed struct contains the following keys: - THEN: the value in q1 - NOW: the value in q2 - ROW: the row index - COL: the column name ADDED: A struct where the keys and values are the row indexes in Q2 of rows that aren't in Q1 REMOVED: A struct where the keys and values are the row indexes in Q1 that aren't in Q2 QUERY1: a reference to the first query QUERY2: a reference to the second query

Return Values:
Returns a structure.

Example:

<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#">

Parameters:

Name Description Required
q1 The first query. Yes
q2 The second query. Yes

Full UDF Source:

/**
 * Returns information about the differences between 2 queries with the same columns.
 * 
 * @param q1 	 The first query. (Required)
 * @param q2 	 The second query. (Required)
 * @return Returns a structure. 
 * @author Nathan Dintenfass (nathan@changemedia.com) 
 * @version 1, May 26, 2003 
 */
function queryDiff(q1,q2){
	//vars for looping
	var ii = 0;
	var cc = 0;
	//a struct to hold the result
	var result = structNew();
	//grab the columns -- NOTE: THIS VERSION ASSUMES THEY HAVE THE SAME COLUMNS!!
	var cols = listToArray(q1.columnList);
	var thisCol = "";
	//we'll loop whichever query is shortest.  by default, we'll loop query1
	var shorterQuery = q1;
	var longerQuery = q2;
	var keyToUseForLonger = "added";
	var sameSize = true;
	var rowDiff = 0;
	//vars to use in the loop
	var q1Value = "";
	var q2Value = "";
	var thenNow = structNew();
	//make the standard keys in the result
	result.changed = structNew();
	result.added = structNew();
	result.removed = structNew();
	result.query1 = q1;
	result.query2 = q2;
	//if the queries are not the same size, indicate that
	if(q1.recordCount NEQ q2.recordCount){
		sameSize = false;
		//if q2 is shorter, use that instead
		if(q1.recordCount GT q2.recordCount){
			shorterQuery = q2;
			longerQuery = q1;
			keyToUseForLonger = "removed";
		}
	}
	//loop the correct query to get rows that are different in Q2 from Q1
	for(ii = 1; ii LTE shorterQuery.recordCount; ii = ii + 1){
		for(cc = 1; cc LTE arrayLen(cols); cc = cc + 1){
			thisCol = cols[cc];
			q1Value = q1[thisCol][ii];
			q2Value = q2[thisCol][ii];
			//if this col is different, grab the row index
			if(compare(q1Value,q2Value)){
				//if we don't already have this row in the changed group, put it there
				if(NOT structKeyExists(result.changed,ii))
					result.changed[ii] = structNew();
				thenNow = structNew();
				thenNow.then = q1Value;
				thenNow.now = q2Value;
				thenNow.row = ii;
				thenNow.col = thisCol;
				result.changed[ii][thisCol] = thenNow;
			}
		}
	}
	//if they are not the same size, add the row index to the appropriate key
	if(NOT sameSize){
		rowDiff = longerQuery.recordCount - shorterQuery.recordCount;
		for(ii = rowDiff + shorterQuery.recordCount; ii LTE longerQuery.recordCount; ii = ii + 1){
			result[keyToUseForLonger][ii] = ii;
		}
	}
	//return the result
	return result;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson