SQLite – Compare tables by Column names

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