'---------------------------
' a script to create / maintain a back-up of a SOURCE folder (and all of its contents).
' 2021-10-28 v02
' You need to customise 2 variables to match your specific environment
'---------------------------
' First we set the executable to a known and empty state
'---------------------------
' close all open databases

dbclose

'---------------------------
' delete all variables/constants/macros(functions and subroutines)
' reload the contents of the   "System Macros"   panel
' reload the contents of the   "User Macros"     panel

newscript

'---------------------------

report ()  ' clear the  "Report" panel

'---------------------------
' define variables about the location of the SOURCE folder (and its subfolders)

dim srcFolder = "C:\Users\User\Desktop\source\\"             ' *** CUSTOMISE this variable ***
dim srclength = length ( srcFolder )

'---------------------------
' define variables about the location of the back-up / DESTINATION folder (and its subfolders)

dim dstFolder = "C:\Users\User\Desktop\destination\\"        ' *** CUSTOMISE this variable ***
dim dstlength = length ( dstFolder )
dim dstPoint

'---------------------------
' create a empty memory only database

dbopen ( "memory"   )

'---------------------------
' scan all the folders/files in the SOURCE folder and place relevant info into
' a new table with the name   "src"
'   dbFileList  is a built-in macro   to know more use      help("dbFileList")

dbFileList (  , "src" ,  srcFolder   ,-1)

'---------------------------

' add an extra column to the   src   table
' and then populate the new column with info from other columns

alter table src add column key text

' careful, we  want the path   RELATIVE  to the src folder
' and the  file's name, size in bytes  and  "last write to" date
' this will provide us with enough info to identify    missing / new / modified     entries

> update src set key = substr ( path,
freestring = freestring &  srcLength
>> ) || name || size || write  where type like 'file'
sql = freestring

> update src set key = substr ( path,
freestring = freestring & srcLength
>> ) || name   where type like 'folder'
sql = freestring

' ----------------------------------------------------------------------------------------------------------------------
' sync the src   FOLDERs structure , without contents , to the dst 
' i.e. for now, ignore the FILES only handle FOLDER entires

dbFileList (  , "dst" ,  dstFolder   ,-1)
alter table dst add column key text

' careful, we  want the path   RELATIVE  to the dst folder
' and the  file's name, size in bytes  and  "last write to" date
' this will provide us with enough info to identify    missing / new / modified     entries

> update dst set key = substr ( path,
freestring = freestring & dstLength
>> ) || name || size || write  where type like 'file'
sql = freestring

> update dst set key = substr ( path,
freestring = freestring & dstLength
>> ) || name   where type like 'folder'
sql = freestring

report ()
 
WITHQUERY ( " select path || name || '\'   from src where not key in ( select key from dst )  and    type like 'folder'  group by path||name  order by level ")

   ' the specific dst FOLDER is 
   '   the dst base folder   
   '   PLUS the relative PATH of the specific src FOLDER to the src base folder
   dstPoint = dstFolder &     LEFT ( wqText(1) , -1* srcLength)   
   ' report = dstPoint             ' *** optional if you want a list of files that are SYNCRONISED
   IF ( isFile ( dstPoint)   ) then
   ELSE
      mkdirPath ( dstPoint )
      IF    ( isFile ( dstPoint)   )   then
      ELSE
         message = "Problem at : "  & dstPoint
      END IF
   END if
END withquery	  

' ----------------------------------------------------------------------------------------------------------------------
' now that the FOLDER structure is okay we can
' sync the FOLDER's contents i.e. the files

' because we might have just changes the   dst FOLDER structure we MUST  re-read the dst 
' file entry information

dbFileList (  , "dst" ,  dstFolder   ,-1)
alter table dst add column key text

> update dst set key = substr ( path,
freestring = freestring & dstLength
>> ) || name || size || write   where type like 'file'
sql = freestring

> update dst set key = substr ( path,
freestring = freestring & dstLength
>> ) || name   where type like 'folder'
sql = freestring

'---------------------------
' remove any dst  FILES which are no longer in    src

' delete the file fron the file system
WITHQUERY ( " select path , name  from dst where not key in ( select key from src ) and type like 'file'  ")
   deleteFileNow ( wqtext(1) & wqtext(2) )
END withquery

' delete the file entry in dst
delete from dst where not key in ( select key from src )   and type like 'file'
 
'---------------------------
' remove from the  dst any  FOLDERS which are no longer in    src

' we use the   Scripting.FileSystemObject   from the operating system to do the delete a folder!

dim fso = createobject ("Scripting.FileSystemObject")
IF ( ! fso ) THEN
   Message ="Could not create object"
   STOP
END IF   
   
' delete the FOLDER fron the file system
WITHQUERY ( " select path , name  from dst where not key in ( select key from src ) and type like 'folder'  order by level desc ")
   fso.DeleteFolder  ( wqtext(1) & wqtext(2) )
   '  report = "Delete folder " & wqtext(1) & wqtext(2)   ' *** optional 
END withquery

' delete the FOLDER entry in dst
delete from dst where not key in ( select key from src )   and type like 'FOLDER'

'---------------------------
' copy across src files which have changed

' we use the   Scripting.FileSystemObject   from the operating system to do the file copy !
 
WITHQUERY ( " select path , name , rowid from src where not key in ( select key from dst )  and type = 'file'    ")

   dstPoint = dstFolder &     LEFT ( wqText(1) , -1 * srcLength)   ' uses negative length trick to "remove" leading chars from PATH
   ' report = "Copy file " &  wqtext(1) & wqtext(2)     ' *** optional
   fso.copyFile  ( wqtext(1) & wqtext(2) , dstPoint )

END withquery

'---------------------------
' FINISHED
'---------------------------