This script was written to compare two tables to see if they have exactly the same column names.
My work required me to weekly get an Excel file from a colleague, and compare it with last weeks file.
At one point the colleague started changing the Excel file structure. Sometime adding a blank column A, sometime renaming columns, or even inserting and adding columns.
I wanted a script that would quickly check, when the files were imported to a database, if two tables had the same column names in the same order.
The script was easier than I thought.
Here is a version of the script.
dbclose ' close / discard any open database, so we start with a clean environment
newscript ' reset the interpreter, discard any user defined variables, constants, functions and subroutines
dbopen ("memory") ' create an empty database in memory
' define 3 tables with almost column names
create table familyA ( Role text , Age int, Id TEXT , Pic BLOB )
create table familyB ( role text , Age int, Id TEXT , Pic BLOB )
create table familyC ( Roles text , Age int, Id TEXT , Pic BLOB )
' create strings contain the column names
dim tblA = dbColumnList ( "main", "familyA")
dim tblB = dbColumnList ( "main", "familyB")
dim tblC = dbColumnList ( "main", "familyC")
' compare the strings
if ( tbla == tblb ) then ' does case insensitive compare
message = "A & B same"
else
message = "A & B different"
End if
' note how we force the comparision to be case-sensitive
if ( tbla CEQU tblb ) then ' does case SENSITIVE compare
message = "A & B same"
else
message = "A & B different"
End if
'
if ( tbla == tblc ) then ' does case insensitive compare
message = "A & C same"
else
message = "A & C different"
End if
The function dbColumnList is in the System macros Panel.
Note that the equal == in the IF statements is not an assign but a compare .
Actually the IF command tries to be intelligent and will convert a following single Equal = to a == command. It was a bit of a nightmare getting the executable to do this!
However I strongly recommend that you adopt the policy of writing an explicit == command for a comparision, rather than using a =.
Overloading = in this way reduces legibility / maintainability. So I implemented some commands that are more explicitly in their meaning:
| Keyword | Meaning |
:= |
assign a value to a variable |
== |
that 2 values (numbers) are mathematically equal, or that 2 strings are case-insensitive identical |
cequ |
that 2 values (numbers) are mathematically equal, or that 2 strings are case-sensitive identical |
The following script will try to strip a table to the same leading columns as a reference table.
dbclose ' close / discard any open database, so we start with a clean environment
newscript ' reset the interpreter, discard any user defined variable, constant, function and subroutines
dbopen ("memory") ' create an empty database in memory
' define 3 tables with almost column names
create table familyA ( Role text , Age int, Id TEXT , Pic BLOB )
create table familyB ( Role text , Age int, Id TEXT , Pic BLOB , Hobby text )
' create variables containing the number of column
dim tblAn = dbObjectChildren ( "main", "familyA")
dim tblBn = dbObjectChildren ( "main", "familyB")
if ( tblBn > tblAn ) then
' B is wider than A
' create a temporary table based on familyB
' we do not need any rows, so we LIMIT 1
' even if familyB is empty the create will work
create temp table fred as select * from familyB limit 1
' now reduce fred to have the same number of columns as familyA
dbColumnDropTrailing ( "temp", "fred" , dbObjectName( "main", "familyB", tblAn + 1 ) )
' now see if familyA and the reduced fred have the same column names
dim tblA = dbColumnList ( "main", "familyA")
dim tblF = dbColumnList ( "temp", "fred")
IF ( tblA == tblF ) then
' implication: the tables familyA and familyB have the same leading columns
' so go ahead and remove the trailing columns from familyB
dbColumnDropTrailing ( "main", "familyB" , dbObjectName( "main", "familyB", tblAn + 1 ) )
message = " FamilyB has been ""shortened"" to the same width as familyA"
ELSE
message = "FamilyA and FamilyB do NOT share the same leading column names"
End IF
drop table temp.fred
END IF
drop table temp.fred
END IF