This new Starfish ETL functionality builds on top of the SQLite Xref and SQLite Connector functionality already in Starfish. The SQLite Xref functionality greatly increased the speed and reliability of the Xref system and allowed us to update Xref lists as opposed to just inserting new, possibly duplicate, records. That's great, but it was still kind of limiting. We wanted a way for users to be able to fully interact with the local Starfish SQLite database. Thus, we've introduced 3 new functions:
Insert
var = SQLiteInsert("test_table", "field1,field2", "'@@ORG:DirectoryRelative@@','@@ORG:Name@@'")
This returns a boolean, true if successful.
Update
var = SQLiteUpdate(“test_table”,” textfield1='hey', numfield3=45", "name=@@ORG:Name@@")
This returns a boolean, true if successful.
Delete
var = SQLiteDelete("test_table", "field2='@@ORG:Name@@'")
Select
var = SQLiteSelect("test_table", "field1,field2", "field2='@@ORG:Name@@'")
This returns a multi dimensional array which you can loop through:
if UBound(var)>-1 then 'there is data
For i = 0 to uBound(var)
logmessage var(i,0)
logmessage var(i,1)
Next
else
logmessage "no data"
end if
Now, you have the Origin you're reading from and the Destination your
writing to AND you also have your local SQLite database you can fully
interact with. What does this do for you? Well, you could pull down
some values from the Destination to your local SQLite database and use
those local values to compare against as opposed to trying to do
possibly slow lookups into the Destination. The possibilities are
endless.
New Starfish ETL Functionality
The second new piece of functionality is adding the SQLite Levenshtein distance function to the SQLite connector AND the new functions mentioned above. Levenshtein distance is a great way to do "fuzzy" comparisons to check for duplicates. This part gets fairly complicated fairly quickly, but you can essentially populate your local SQLite database with Account names, for example, and as your going, look for possible duplicates. If you find a possible duplicate, mark it as a possible duplicate!
Stay tuned to the Starfish Wiki and Starfish News for more information.