Table Compare

by Jim Chapman

Alpha Five's string handling capabilities have always been very good, but with the release of version 6, Alpha's string handling abilities have taken a leap forward. Support for Regular Expressions as well as a number of new functions specifically geared toward string/list manipulation have taken Alpha Five version 6 to the forefront of string handling. In future issues we plan to explore some of these new capabilities. For this article I want to present a script that was made easy to create with the table.external_field_name_get() method, or the new 't.table_info_dump()' method available in version 6. The script will work in either version 5 or 6 using the table.external_field_name_get().

The impetus for this script was a request to easily compare a table in development with a table that was in use. After a database has been put into use, it makes sense to try out changes and additions on a copy of the production database. The improvement and extension of a working database can take place over an extended period of time and it becomes difficult to remember all the structural changes and additions that may have been made to the table. Bill Parker's indispensable tool, A5 Doc, can be used for this along with a host of other capabilities, but the request was for a quick visual comparison.

Alpha Five version 6 made creation of this script a simple task. But after quickly kicking this out, some improvement come quickly to mind:

1. A table by table comparison is fine, but it sure would be nice if it compared all tables in a development database with the corresponding tables in the production database, and highlighted missing tables.
2. It would be nice to have the option to print the output as well as being able to view it.
3. The script should be totally self contained and portable, ie: not relying on any underlying table or reports.
4. Error checking, ie: what if a pointer to a table could not be gained.
5. It would be nice if the visual output was a little better.

Anyone out there in Alpha land care to add in this functionality and present it in the Alpha community in a future edition of the Newsletter?

The script:

'*****************************************
'Xdialog box created with the Xbasic Genie
'Create an XDialog dialog box to prompt for parameters.
DIM SHARED vDevelopmentTable as c
DIM SHARED vProductionTable as c
DIM SHARED varC_result as C
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
TryAgain:
varC_result = ui_dlg_box("Compare Tables",<<%dlg%
{region}
Select Development Table:| [%fTables(*.dbf)%.80vDevelopmentTable];
Select Production Table:| [%fTables(*.dbf)%.80vProductionTable];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)
'*****************************************

if varC_result <> "OK"
	end
end if 

if vDevelopmentTable = "" .or. vProductionTable = ""
	goto TryAgain
end if 

vDevFldList = table.external_field_name_get(vDevelopmentTable,"n,t,w,d")

vProdFldList = table.external_field_name_get(vProductionTable,"n,t,w,d")

vLineCount = line_count(vDevFldList)
dim vResultList as c = ""

for i = 1 to vLineCount
	vDevFldDef = word(vDevFldList,i,crlf())
	vDevFldName = word(vDevFldDef,1)
	if .not. word_exists(vProdFldList,vDevFldDef,crlf())
		if word_exists(vProdFldList,vDevFldName,",")
			vResultList = vResultList + vDevFldDef + " field name exists in Production Table, but field definitions do not match." + crlf()
		else
			vResultList = vResultList + vDevFldDef + " field does not exist in Production Table." + crlf()
		end if 
	end if 
	
next

if vResultList = ""
	ui_msg_box("Table Comparison","Development table's structure exists in Production table.",UI_INFORMATION_SYMBOL)
	else
		if version() = 5
			a5_show_variable(vResultList)
		else
			a5_show_variable(vResultList,"Table Comparison")
		end if 
end if