Import tasks from 2do database to OmniFocus 2

gistfile1.applescript

--------------------------------------------------
--------------------------------------------------
-- Import tasks from 2do database to OmniFocus 2
--------------------------------------------------
--------------------------------------------------
--
-- Original Script taken from: http://forums.omnigroup.com/showthread.php?t=14846&page=2
-- This script forked from https://gist.github.com/hvolkmer/4020468
--
-- Step 1: Start with an empty Omnifocus database.
-- Step 2: Make a copy of the 2Do database (Preferences -> Backups -> Plus button, Select the new backup -> Save As)
-- Step 3: Update the 'databasePath' property below to the correct db
-- Step 4: Run this script. 

-- Notes: 
--   Omnifocus doesn't have support for tags, alarms or priorities so those are not migrated
--   Start times are lost (start dates are migrated)
--   The basics are migrated: projects, checklists, tasks, title, notes, due dates/times, basic recurrence
--   Check the logged output for recurrences that were not migrated
-- Tested with Omnifocus v2.02 and  2Do v1.5.2

-- TODO: Update the line below ******
property databasePath : "/Users/anil/Documents/2Do.db"

StartMigration()

on StartMigration()
	MigrateProjects()
	MigrateCheckLists()
	MigrateTasks()
	CleanAllTitles()
end StartMigration

on MigrateProjects()
	set results to GetProjects()
	repeat with result in results
		CreateProject(result)
	end repeat
end MigrateProjects

on MigrateCheckLists()
	set results to GetChecklists()
	repeat with result in results
		CreateChecklist(result)
	end repeat
end MigrateCheckLists

on MigrateTasks()
	set results to GetTasks()
	repeat with result in results
		CreateTask(result)
	end repeat
end MigrateTasks

on CleanAllTitles()
	tell application "OmniFocus"
		tell default document
			set lstTasks to folders
			repeat with oTask in lstTasks
				set n to name of oTask
				set name of oTask to (characters 1 thru -7 of n) as string
			end repeat
			
			set lstTasks to flattened tasks
			repeat with oTask in lstTasks
				set n to name of oTask
				set name of oTask to (characters 1 thru -7 of n) as string
			end repeat
			
		end tell
	end tell
end CleanAllTitles
--------------------------------------------------
--------------------------------------------------
-- 2do database helpers
--------------------------------------------------
--------------------------------------------------
on GetProjects()
	return GetEntity(2)
end GetProjects

on GetChecklists()
	return GetEntity(1)
end GetChecklists

on GetTasks()
	return GetEntity(0)
end GetTasks

on GetEntity(type)
	set sql to "SELECT calendars.title || '_' || substr('00000'|| calendars.primid, -5,  5) as folder, tasks.title || '_' || substr('00000'|| tasks.primid, -5,  5) as title, tasks.notes as notes, tasks.url, t2.title || '_' || substr('00000'|| t2.primid, -5,  5)as parent, tasks.iscompleted, tasks.priority, tasks.startdate, tasks.duedate, tasks.duetime, tasks.primid, tasks.recurrence, tasks.repeattype, tasks.repeatvalue, tasks.starred FROM tasks inner join calendars on calendars.uid = tasks.calendaruid left join tasks as t2 on tasks.parent = t2.uid where tasks.tasktype = " & type & " and tasks.isdeleted = 0 order by tasks.displayorder"
	
	set resultList to SQLExecuteSelect(sql)
	return resultList
end GetEntity

on CreateProject(result)
	
	set folderTitle to item 1 in result
	set title to item 2 in result
	set notes to item 3 in result
	set urlStr to item 4 in result
	set isCompleted to item 6 in result as integer
	set priority to item 7 in result as integer
	set startEpoch to item 8 in result
	set dueEpoch to item 9 in result
	set dueTime to item 10 in result
	set starred to item 15 in result as integer
	
	if folderTitle is "" then
		set folderTitle to "Unknown"
	end if
	
	set startDate to epoch2datetime(startEpoch)
	set dueDate to epoch2datetime(dueEpoch)
	if dueDate is not missing value and dueTime is not "999999.0" then
		set dueHours to text 1 thru 2 of dueTime
		set dueMinute to text 3 thru 4 of dueTime
		set dueDate to dueDate + (dueHours * hours) + (dueMinute * minutes)
	end if
	
	tell application "OmniFocus"
		
		tell default document
			
			if folder folderTitle exists then
				set theFolder to folder folderTitle
			else
				set theFolder to make new folder with properties {name:folderTitle}
			end if
			
			tell theFolder
				set rr to my setRepetitionRule(result, theFolder)
				if rr is not missing value then
					set theEntry to make new project with properties {name:title, repetition rule:rr}
				else
					set theEntry to make new project with properties {name:title}
				end if
				set note of theEntry to notes & " " & urlStr
				set defer date of theEntry to startDate
				set completed of theEntry to isCompleted
				set due date of theEntry to dueDate
				if starred is 1 then
					set flagged of theEntry to true
				end if
				
			end tell
		end tell -- document
	end tell -- OF application
end CreateProject

on CreateChecklist(result)
	
	set folderTitle to item 1 in result
	set title to item 2 in result
	set notes to item 3 in result
	set urlStr to item 4 in result
	set isCompleted to item 6 in result as integer
	set priority to item 7 in result as integer
	set startEpoch to item 8 in result
	set dueEpoch to item 9 in result
	set dueTime to item 10 in result
	set starred to item 15 in result as integer
	
	if folderTitle is "" then
		set folderTitle to "Unknown"
	end if
	
	set startDate to epoch2datetime(startEpoch)
	set dueDate to epoch2datetime(dueEpoch)
	if dueDate is not missing value and dueTime is not "999999.0" then
		set dueHours to text 1 thru 2 of dueTime
		set dueMinute to text 3 thru 4 of dueTime
		set dueDate to dueDate + (dueHours * hours) + (dueMinute * minutes)
	end if
	
	tell application "OmniFocus"
		
		tell default document
			
			if folder folderTitle exists then
				set theFolder to folder folderTitle
			else
				set theFolder to make new folder with properties {name:folderTitle}
			end if
			
			tell theFolder
				set rr to my setRepetitionRule(result, theFolder)
				if rr is not missing value then
					set theEntry to make new project with properties {name:title, repetition rule:rr}
				else
					set theEntry to make new project with properties {name:title}
				end if
				
				set note of theEntry to notes & " " & urlStr
				set defer date of theEntry to startDate
				set completed of theEntry to isCompleted
				set due date of theEntry to dueDate
				if starred is 1 then
					set flagged of theEntry to true
				end if
				set singleton action holder of theEntry to true
			end tell
		end tell -- document
	end tell -- OF application
end CreateChecklist

on CreateTask(result)
	
	set folderTitle to item 1 in result
	set taskTitle to item 2 in result
	set notes to item 3 in result
	set urlStr to item 4 in result
	set parentTitle to item 5 in result
	set isCompleted to item 6 in result as integer
	set priority to item 7 in result as integer
	set startEpoch to item 8 in result
	set dueEpoch to item 9 in result
	set dueTime to item 10 in result
	set starred to item 15 in result as integer
	
	if folderTitle is "" then
		set folderTitle to "Unknown"
	end if
	
	set startDate to epoch2datetime(startEpoch)
	set dueDate to epoch2datetime(dueEpoch)
	if dueDate is not missing value and dueTime is not "999999.0" then
		set dueHours to text 1 thru 2 of dueTime
		set dueMinute to text 3 thru 4 of dueTime
		set dueDate to dueDate + (dueHours * hours) + (dueMinute * minutes)
	end if
	
	tell application "OmniFocus"
		tell default document
			if folder folderTitle exists then
				set theFolder to folder folderTitle
			else
				set theFolder to make new folder with properties {name:folderTitle}
			end if
			
			if parentTitle is "" then
				if (first flattened project where its name = "Single Actions_00001") exists then
					set parentProject to first flattened project where its name = "Single Actions_00001"
				else
					set parentProject to make new project with properties {name:"Single Actions_00001", singleton action holder:true}
				end if
			else
				set parentProject to first flattened project where its name = parentTitle
			end if
			
			tell theFolder
				if parentProject is not missing value then
					-- set containing project to parentProject
					tell parentProject
						set rr to my setRepetitionRule(result, theFolder)
						if rr is not missing value then
							set theEntry to make new task with properties {name:taskTitle, repetition rule:rr}
						else
							set theEntry to make new task with properties {name:taskTitle}
						end if
						
						set note of theEntry to notes & " " & urlStr
						set defer date of theEntry to startDate
						set completed of theEntry to isCompleted
						set due date of theEntry to dueDate
						if starred is 1 then
							set flagged of theEntry to true
						end if
					end tell
				else
					set theEntry to make new task with properties {name:taskTitle}
				end if
			end tell
		end tell -- document
	end tell -- OF application
end CreateTask

on setRepetitionRule(result, theParent)
	tell application "OmniFocus"
		tell default document
			
			tell theParent
				set rec to item 12 in result as integer
				set repeatType to item 13 in result as integer
				set repeatValue to item 14 in result as integer
				
				if rec = 0 then
					return missing value
				end if
				
				if repeatType = 259 then
					set freq to "YEARLY"
				else if repeatType = 258 then
					set freq to "MONTHLY"
				else if repeatType = 257 then
					set freq to "WEEKLY"
				else if repeatType = 256 then
					set freq to "DAILY"
				else
					set taskTitle to item 2 in result
					log "WARNING: Repetition type for task '" & taskTitle & "'is not supported. Please update manually."
					return missing value
				end if
				
				if rec = 1 then
					return {recurrence:"FREQ=" & freq, repetition method:fixed repetition}
				else if rec = 2 then
					return {recurrence:"FREQ=" & freq, repetition method:due after completion}
				end if
				
			end tell
		end tell
	end tell
	return
end setRepetitionRule

--
-- SQLLite access
-- Taken from http://lists.apple.com/archives/applescript-users/2010/Mar/msg00284.html
-- 
property sqlite3Path : "/usr/bin/sqlite3"
property columnSeparator : character id 3
property rowSeparator : character id 2
on SQLExecuteSelect(sqlCommand)
	-- set databasePath to
	-- 2008 BareFeet http://www.tandb.com.au
	set sqlCommandWithSeparators to "select *, '" & rowSeparator & "' from (" & sqlCommand & ");"
	set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " | " & sqlite3Path & space & "-separator " & (quoted form of columnSeparator) & space & quoted form of databasePath
	set recordsText to do shell script shellCommand
	script speedyObject
		property recordList : {}
	end script
	if recordsText is not "" then
		set oldDelimiters to AppleScript's text item delimiters
		set AppleScript's text item delimiters to (columnSeparator & rowSeparator & return)
		set speedyObject's recordList to text items in recordsText
		set last item in speedyObject's recordList to text 1 thru -(1 + (length of (columnSeparator & rowSeparator))) in last item in speedyObject's recordList
		set AppleScript's text item delimiters to columnSeparator
		set recordCount to (count speedyObject's recordList)
		repeat with recordN from 1 to recordCount
			set item recordN in speedyObject's recordList to text items in item recordN in speedyObject's recordList
		end repeat
		set AppleScript's text item delimiters to oldDelimiters
	end if
	return speedyObject's recordList
end SQLExecuteSelect

on epoch2datetime(epochseconds)
	set myshell1 to "date -r "
	set myshell2 to " \"+%m/%d/%Y %H:%M\""
	set firstDigit to text 1 thru 2 of epochseconds
	set epochseconds to text 1 thru 10 of epochseconds
	if firstDigit is "14" then
		set theDatetime to do shell script (myshell1 & epochseconds & myshell2)
		return date theDatetime
	else
		return missing value
	end if
	
end epoch2datetime