FileMaker 17: Data Migration Tool - Part Four

FileMaker 17: Data Migration Tool - Part Four

Putting it all together

Colin Keefe, FileMaker Consultant, IT Solutions 

Through this mini-series of articles we've tried to help identify where the Data Migration Tool fits into the problem space of managing FileMaker deployments, by outlining: 

  • What DMT does 
  • How to script it 
  • Some practical deployment considerations that surround using a tool like DMT 

In this final article we're going to circle back to scripting the DMT with an eye to helping manage the deployment process.  To follow along, download your copy of the DMT Helper File:

Download your copy of the DMT-Helper Sample File 

In this scenario we imagine a development team that must manage deployments for several FileMaker apps, each of which may contain one or more FileMaker files, each with their own settings. To keep it simple, we're going to ignore security considerations and store account names/passwords and encryption keys in the file we're creating. One way to mitigate security concerns is to use special extended privileges to allow non-Admin accounts to manage migrations. 

We're going to build out a tool that manages a multiple-file migration, and stores the migration settings for separate client solutions. Some core user stories are: 

Here are some sample screens from our scenario:

Solution Profile Management Screen

Here we have a list of solutions on the upper left, and a list of files for each.  No solutions have been created yet.

Clicking an Add button lets the user add a New Solution, with a few fields

This creates a new record as shown:

Once we add required fields, we should be able to run a migration on the selected file:

Then when we run the migration, we want to see the last run time, the result and output, something like this:

We ought to be able to add another file, as shown, and have its own run/result values.

This allows us to run migrations in batch, and then see individual pass/fail results.  In the example below, the first file had incorrect authentication supplied, and so it failed, but the second was fine.

 

With this suggested model, you could store Migration Profiles for multiple clients, as shown:

 

Scripting in depth
Here's the script that does the individual DMT calls.  It does a few things:

  • We use JSON to pass parameters to this script, so we specify what the script expects in a comment at the top.
  • We capture the JSON
  • We build a terminal command line instruction based on the supplied parameter values
  • We determine what OS we're running under and make the appropriate external event call
  • We capture the result and update the FileMaker record with it.  On the Mac this is easy - just use AppleScript.  On Windows we resort to some trickery.

Note that this file is dumb in the sense that it assumes all files are in the same directory as the DataMigrationToolHelper file.  Feel free to make it smarter!

 

#-------------------------------
#10/24/17 Colin Keefe, IT Solutions Consulting, Inc.
#2/20/18 Robin Story, IT Solutions Consulting, Inc. (added Windows scripting)
#www.itsolutions-inc.com/filemaker

#-------------------------------
#Parameters to supply to this script:
#JSONSetElement(""; // source ["src_path"; MigrationProfile::src_path; ""]; ["src_account"; MigrationProfile::src_account; ""]; ["src_pwd"; MigrationProfile::src_pwd; ""]; ["src_key"; MigrationProfile::src_key; ""]; //clone ["clone_path"; MigrationProfile::clone_path; ""]; ["clone_account"; MigrationProfile::clone_account; ""]; ["clone_pwd"; MigrationProfile::clone_pwd; ""]; ["clone_key"; MigrationProfile::clone_key; ""]; ["target_path"; MigrationProfile::target_path; ""]; ["ignore_valuelists"; MigrationProfile::ignore_valuelists; JSONString]; ["ignore_accounts"; MigrationProfile::ignore_accounts; JSONString]; ["mode"; MigrationProfile::mode; JSONString]; ["cache_size"; MigrationProfile::cache_size; JSONString]; ["opl"; MigrationProfile::opl; JSONString] )
Set Error Capture [ On ]
Commit Records/Requests [ No dialog ]
#export FMDataMigration Tool to Temp Directory
If [ IsEmpty (Get(ScriptParameter) ) ]
Show Custom Dialog [ Title: "Error"; Message: "No parameters were supplied. Please see script documentation to learn how to pass required parameters to this script."; Default Button: “OK”, Commit: “No” ]
Exit Script [ ]
End If
# 2/12/18 RHS - Let's create a variable to store if this is windows or mac
Set Variable [ $isMac; Value:Get(SystemPlatform) = 1 ]
 
#This is dumb in the sense that it doesn't understand relative file paths - it assumes all files are in the same directory as the current file. 10/24/17 CK
If [ $isMac ]
Set Variable [ $q; Value:"'" ]
Set Variable [ $tmp; Value:Get(FilePath) & "FMDataMigrationTool/FMDataMigration" ]
Set Variable [ $scriptLocation; Value:"'" & Substitute($tmp; ["file:/Macintosh HD";""];[Get(FileName);""];[".fmp12";""]) & "'" ]
Set Variable [ $filesLocation; Value:Substitute(Get(FilePath); ["file:/Macintosh HD";""];[Get(FileName);""];[".fmp12";""]) ]
Else
# reset the result sniffers
Set Field [ MigrationProfile::Result; "" ]
Set Field [ MigrationProfile::win_container_result; "" ]
Export Field Contents [ “file:result.txt”; Create directories:No ]

Set Variable [ $q; Value:"\"" ]
Set Variable [ $tmp; Value:Get(FilePath) & "FMDataMigrationTool_WIN/FMDataMigration.exe" ]
Set Variable [ $filesLocation; Value:Substitute(Get(FilePath); ["file:/";""];[Get(FileName);""];[".fmp12";""]; ["/"; "\\"]) ]
Set Variable [ $scriptLocation; Value:$q & Substitute($tmp; ["file:/";""];[Get(FileName);""];[".fmp12";""]; ["/"; "\\"]) & $q ]
End If
 
#load parameters to pass to tool
#source file variables
Set Variable [ $src_path; Value:$q & $filesLocation & JSONGetElement ( Get(ScriptParameter) ; "src_path" ) & $q ]
Set Variable [ $src_account; Value:JSONGetElement ( Get(ScriptParameter) ; "src_account" ) ]
Set Variable [ $src_pwd; Value:JSONGetElement ( Get(ScriptParameter) ; "src_pwd" ) ]
Set Variable [ $src_key; Value:JSONGetElement ( Get(ScriptParameter) ; "src_key" ) ]
#destination file variables
Set Variable [ $clone_path; Value:$q & $filesLocation & JSONGetElement ( Get(ScriptParameter) ; "clone_path" ) & $q ]
Set Variable [ $clone_account; Value:JSONGetElement ( Get(ScriptParameter) ; "clone_account" ) ]
Set Variable [ $clone_pwd; Value:JSONGetElement ( Get(ScriptParameter) ; "clone_pwd" ) ]
Set Variable [ $clone_key; Value:JSONGetElement ( Get(ScriptParameter) ; "clone_key" ) ]
Set Variable [ $target_path; Value:Let([         _targetPath = JSONGetElement ( Get(ScriptParameter) ; "target_path" )     ];         Case(IsEmpty(_targetPath);"";         $q & _targetPath & $q         ) ) ]
Set Variable [ $ignore_accounts; Value:JSONGetElement ( Get(ScriptParameter) ; "ignore_accounts" ) ]
Set Variable [ $ignore_valuelists; Value:JSONGetElement ( Get(ScriptParameter) ; "ignore_valuelists" ) ]
Set Variable [ $mode; Value:JSONGetElement ( Get(ScriptParameter) ; "mode" ) ]
Set Variable [ $cache_size; Value:JSONGetElement ( Get(ScriptParameter) ; "cache_size" ) ]
Set Variable [ $opl; Value:JSONGetElement ( Get(ScriptParameter) ; "opl" ) ]
 
#error capture
If [ IsEmpty($src_path) ]
Show Custom Dialog [ Title: "Error"; Message: "Missing source path."; Default Button: “OK”, Commit: “No” ]
Exit Script [ ]
Else If [ IsEmpty($clone_path) ]
Show Custom Dialog [ Title: "Error"; Message: "Missing clone path."; Default Button: “OK”, Commit: “No” ]
Exit Script [ ]
End If
Set Variable [ $parameters; Value:" -src_path " & $src_path & If(not IsEmpty($src_account); " -src_account " & $src_account;"") & If(not IsEmpty($src_pwd); " -src_pwd " & $src_pwd;"") & If(not IsEmpty($src_key); " -src_key " & $src_key ;"")& " -clone_path " & $clone_path & If(not IsEmpty($clone_account); " -clone_account " & $clone_account; "") & If(not IsEmpty($clone_pwd); " -clone_pwd " & $clone_pwd; "") & If(not IsEmpty($clone_key); " -clone_key " & $clone_key; "") & If(not IsEmpty($target_path); " -target_path " & $target_path; "") & If(not IsEmpty($ignore_valuelists); " -ignore_valuelists"; "") & If(not IsEmpty($ignore_accounts); " -ignore_accounts"; "") & If(not IsEmpty($mode); " -mode " & $mode; "") & If(not IsEmpty($cache_size); " -cache_size " & $cache_size; "") & If(not IsEmpty($opl); $opl; "") ]
 
If [ $isMac ]
#Set Applescript code to run
Set Variable [ $command; Value:"set mySlug to do shell script \"" & $scriptLocation & $parameters & "\" " & ¶ & "tell application \"FileMaker Pro Advanced\"¶set cell \"Result\" of current record to mySlug¶end tell" ]
 
#run tool
Perform AppleScript [ Calculated AppleScript: $command ]
Set Variable [ $lastError; Value:Get(LastError) ]
Set Field [ MigrationProfile::LastRunTimestamp; Get(CurrentTimestamp) ]
 
Else
#Set Windows Batch code to run
Set Variable [ $command; Value:"cmd /c \"" & $scriptLocation & $parameters & " > " & Quote($filesLocation & "result.txt") & "\" " ]
 
# run tool
Send Event [ open document; $command ] [ Bring application to foreground ]
Set Variable [ $lastError; Value:Get(LastError) ]
 
#wait for result
Set Variable [ $resultFileLocation; Value:Substitute($filesLocation; "\\"; "/") & "result.txt" ]
If [ $lastError = 0 ]
Loop
Insert File [ MigrationProfile::win_container_result; “file:result.txt” ]
Exit Loop If [ Get(LastError) = 0 ]
Pause/Resume Script [ Duration (seconds): 1 ]
End Loop
End If
 
Set Field [ MigrationProfile::LastRunTimestamp; Get(CurrentTimestamp) ]
 
# insert the text of result.txt into Result
Insert from URL [ MigrationProfile::Result; "file:///" & $resultFileLocation ] [ Do not automatically encode URL; Select; No dialog ]
Commit Records/Requests [ No dialog ]
 
End If
If [ IsEmpty ( MigrationProfile::Result ) and MigrationProfile::mode="-q" ]
Set Field [ MigrationProfile::Result; "Successful run in Quiet Mode. If you really wanted this to run without messages you could edit the Run Migration script to not write this message! But hey, we're testing features." ]
Set Field [ MigrationProfile::LastRunSuccess; 1 ]
Else If [ $lastError ≠ 0 or Left(GetValue ( MigrationProfile::Result ; If($isMac; 12; 23));5) ≠ "Start" ]
Set Field [ MigrationProfile::Result; Let([     _message =     If ($lastError ≠ 0;"Error: " & $lastError & "¶¶";"") &     "Migration Failed.¶¶Command attempted was:¶¶" &     $command     ];     TextColor ( _message ; RGB ( 255 ; 0 ; 0 ) ) ) & "¶¶" & "Result was:¶¶" & MigrationProfile::Result ]
Set Field [ MigrationProfile::LastRunSuccess; 0 ]
Else
Set Variable [ $start; Value:Let([ _Timestamp = GetValue ( MigrationProfile::Result ; 14); _TimestampValue = Substitute(_Timestamp;"Start: "; ""); _TS = Left ( _TimestampValue ; Length ( _TimestampValue )-5 ) ]; _TS ) ]
Set Variable [ $end; Value:Let([ _Timestamp = GetValue ( MigrationProfile::Result ; ValueCount ( MigrationProfile::Result )); _TimestampValue = Substitute(_Timestamp;"End: "; ""); _TS = Left ( _TimestampValue ; Length ( _TimestampValue )-5 ) ]; _TS ) ]
Set Field [ MigrationProfile::LastRunSuccess; 1 ]
Show Custom Dialog [ Title: "Time Elapsed"; Message: $start & " - " & $end; Default Button: “OK”, Commit: “No” ]
End If

ITS Email Newsletter Archive

© 2018 IT Solutions Consulting, Inc. All rights reserved. Privacy Statement
IT Solutions
Top