Reminders.app sub-reminders are invisible to AppleScript

So, I wrote a script to backup my Reminders.app tasks, since the “upgraded” Reminders data format that started in Catalina (I think?) removes the Export menu command from the Reminders.app.
It worked well, up until I started using some of the other features of the upgraded format, like assigning a reminder to someone, or (even worse) ANY sub-reminder. The new format lets you “indent” reminders to be sub-reminders of another reminder. When you do that, however, sub-reminders seem to be unreachable by AppleScript. The app’s dictionary doesn’t indicate that reminders can contain other reminders. And, getting the reminders of a list only returns the top-level reminders.

So, in case anyone has thoughts, or is interested in the script itself, I’ll show that here. If you have multiple Reminders accounts, it asks which account you want to backup lists for. If you only have one account, it just uses that without asking. It then asks which list(s) you want to back up (select multiple by shift-clicking a range or command-clicking individual list names). It saves the resulting data as a text file in your Documents folder. I have another script that reads that text file, and will restore the items into the specified list(s), even creating the list if it does not exist.

-- Reminders - Lists Backup

(*
	Creates a backup text file of the (top-level) reminders of the CHOSEN list(s) of the chosen account. 
	NOTE: Does NOT completely handle the new features in the "upgraded" Reminders format in iOS 13 and macOS Catalina and later. It cannot backup sub-reminders, or assignee info, etc. It only can see and backup features that existed in the original format. 
	DEV NOTE: even if properties are 'missing value', back them up so that the restore script does not have to check whether hash key exists. Restore will just disregard them, but won't get error trying to read.  

HISTORY: 

	2023-03-27 ( Krioni ): created. Based off "Reminders - Account Backup" script. 

*)


property ScriptName : "Reminders Lists Backup"

property oneAccountName : ""
property listNames : {} -- a LIST of the reminder lists to backup.

property promptChooseAccount : "Choose which account to backup Reminders items for:"
property promptChooseList : "Choose which reminder LIST(s) to backup Reminders items for:"

on run
	
	if length of oneAccountName is 0 then
		tell me to activate
		tell application "Reminders"
			set accountNames to name of every account
		end tell
		if (count of accountNames) is equal to 1 then
			set oneAccountName to item 1 of accountNames
		else
			set chooseAccountDialog to (choose from list accountNames with title ScriptName with prompt promptChooseAccount OK button name "Backup" cancel button name "Cancel" without multiple selections allowed and empty selection allowed)
			if class of chooseAccountDialog is boolean then return false -- user canceled
			set oneAccountName to item 1 of chooseAccountDialog
		end if
		
	end if
	if (count of listNames) is 0 then
		tell me to activate
		tell application "Reminders"
			set listNames to name of every list of account oneAccountName
		end tell
		set chooseListsDialog to (choose from list listNames with title ScriptName with prompt promptChooseList OK button name "Backup" cancel button name "Cancel" with multiple selections allowed without empty selection allowed)
		
		if class of chooseListsDialog is boolean then return false -- user canceled
		
		set listNames to chooseListsDialog
		
	end if
	
	
	set backupTS to (current date)
	
	set backupFolderPath to (path to documents folder) as string
	set timestampString to timestampISO8601(backupTS)
	if (count of listNames) is equal to 1 then
		set listDesc to " (" & item 1 of listNames & ")"
	else
		set listDesc to " (" & (count of listNames) & " lists)"
	end if
	set backupFilePath to backupFolderPath & "RemindersBackup - Account " & oneAccountName & listDesc & " - " & timestampString & ".txt"
	
	
	tell application "Reminders"
		set oneAccount to account oneAccountName
		set backupData to {}
		repeat with oneListName in listNames
			set oneListRef to list oneListName of oneAccount
			set oneListName to name of oneListRef
			set oneListColor to color of oneListRef
			set oneListEmblem to emblem of oneListRef
			set activeReminders to (reminders of oneListRef whose completed is false)
			repeat with oneReminder in activeReminders
				
				set oneListReminderProps to properties of reminders of oneListRef
				set oneListReminders to {}
				repeat with rawProps in oneListReminderProps
					-- only backup properties that can be restored (so, e.g. NOT container)
					set oneReminderProps to {name:name of rawProps} ¬
						& {id:id of rawProps} ¬
						& {creation date:creation date of rawProps} ¬
						& {modification date:modification date of rawProps} ¬
						& {body:body of rawProps} ¬
						& {completed:completed of rawProps} ¬
						& {completion date:completion date of rawProps} ¬
						& {due date:due date of rawProps} ¬
						& {allday due date:allday due date of rawProps} ¬
						& {remind me date:remind me date of rawProps} ¬
						& {priority:priority of rawProps} ¬
						& {flagged:flagged of rawProps}
					
					copy oneReminderProps to end of oneListReminders
				end repeat
			end repeat
			
			set oneListBackup to {listName:oneListName, listColor:oneListColor, listEmblem:oneListEmblem, listReminders:oneListReminders}
			copy oneListBackup to end of backupData
		end repeat
		
		--return backupData
		
		set backupDataString to my coerceToString(backupData)
		
	end tell
	
	
	writeToFile({outputText:backupDataString, fullFilePath:backupFilePath})
	
	return backupDataString
	
end run


on coerceToString(incomingObject)
	-- version 2.2
	
	if class of incomingObject is string then
		set {text:incomingObject} to (incomingObject as string)
		return incomingObject
	else if class of incomingObject is integer then
		set {text:incomingObject} to (incomingObject as string)
		return incomingObject as string
	else if class of incomingObject is real then
		set {text:incomingObject} to (incomingObject as string)
		return incomingObject as string
	else if class of incomingObject is Unicode text then
		set {text:incomingObject} to (incomingObject as string)
		return incomingObject as string
	else
		-- LIST, RECORD, styled text, or unknown
		try
			try
				set some_UUID_Property_54F827C7379E4073B5A216BB9CDE575D of "XXXX" to "some_UUID_Value_54F827C7379E4073B5A216BB9CDE575D"
				
				-- GENERATE the error message for a known 'object' (here, a string) so we can get 
				-- the 'lead' and 'trail' part of the error message
			on error errMsg number errNum
				set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, {"\"XXXX\""}}
				set {errMsgLead, errMsgTrail} to text items of errMsg
				set AppleScript's text item delimiters to oldDelims
			end try
			
			-- now, generate error message for the SPECIFIED object: 
			set some_UUID_Property_54F827C7379E4073B5A216BB9CDE575D of incomingObject to "some_UUID_Value_54F827C7379E4073B5A216BB9CDE575D"
			
			
		on error errMsg
			if errMsg starts with "System Events got an error: Can’t make some_UUID_Property_54F827C7379E4073B5A216BB9CDE575D of " and errMsg ends with "into type specifier." then
				set errMsgLead to "System Events got an error: Can’t make some_UUID_Property_54F827C7379E4073B5A216BB9CDE575D of "
				set errMsgTrail to " into type specifier."
				
				set {od, AppleScript's text item delimiters} to {AppleScript's text item delimiters, errMsgLead}
				
				set objectString to text item 2 of errMsg
				set AppleScript's text item delimiters to errMsgTrail
				
				set objectString to text item 1 of objectString
				set AppleScript's text item delimiters to od
				
				
				
			else
				--tell me to log errMsg
				set objectString to errMsg
				
				if objectString contains errMsgLead then
					set {od, AppleScript's text item delimiters} to {AppleScript's text item delimiters, errMsgLead}
					set objectString to text item 2 of objectString
					set AppleScript's text item delimiters to od
				end if
				
				if objectString contains errMsgTrail then
					set {od, AppleScript's text item delimiters} to {AppleScript's text item delimiters, errMsgTrail}
					set AppleScript's text item delimiters to errMsgTrail
					set objectString to text item 1 of objectString
					set AppleScript's text item delimiters to od
				end if
				
				--set {text:objectString} to (objectString as string) -- what does THIS do?
			end if
		end try
		
		return objectString
	end if
end coerceToString



on timestampISO8601(incomingDate)
	-- version 1.0
	
	if class of incomingDate is not date then
		try
			set incomingDate to date incomingDate
		on error
			set incomingDate to (current date)
		end try
	end if
	
	set numHours to (time of incomingDate) div hours
	set textHours to text -2 through -1 of ("0" & (numHours as string))
	
	set numMinutes to (time of incomingDate) mod hours div minutes
	set textMinutes to text -2 through -1 of ("0" & (numMinutes as string))
	
	set numSeconds to (time of incomingDate) mod minutes
	set textSeconds to text -2 through -1 of ("0" & (numSeconds as string))
	
	set numDay to day of incomingDate as number
	set textDay to text -2 through -1 of ("0" & (numDay as string))
	
	set numYear to year of incomingDate as number
	set textYear to text -4 through -1 of (numYear as string)
	
	set numMonth to (month of (incomingDate)) as number
	set textMonth to text -2 through -1 of ("0" & (numMonth as string))
	
	set customDateString to textYear & "-" & textMonth & "-" & textDay & "--" & textHours & "-" & textMinutes & "-" & textSeconds
	
	return customDateString
end timestampISO8601



on writeToFile(prefs)
	-- version 1.2
	
	set defaultPrefs to {outputText:"", fullFilePath:null, fileName:null, fileDirectory:(path to desktop) as string, appendText:false, appendLine:true}
	set prefs to prefs & defaultPrefs
	set outputText to outputText of prefs
	
	-- determine file path
	if fullFilePath of prefs is not null then
		set outputFile to fullFilePath of prefs
	else if fileName of prefs is not null then
		set outputFile to fileDirectory of prefs & fileName of prefs
	else
		set outputFile to ((path to desktop) as string) & "ASFileWrittenBy_htcLib.txt"
	end if
	
	-- now write output text to file
	try
		try
			set fileReference to open for access file outputFile with write permission
		on error
			set fileReference to open for access POSIX file outputFile with write permission
		end try
		if appendText of prefs is false then
			set eof of the fileReference to 0
		else if appendLine of prefs is true then
			set outputText to return & outputText
		end if
		write outputText to fileReference starting at eof
		close access fileReference
		return true
	on error
		try
			close access file outputFile
			return true
		end try
	end try
	return false
end writeToFile

So, if anyone has come across this and figured out a way to access sub-reminders or other “new” properties that don’t appear in the dictionary for Reminders.app, I would love to know.

Since the Reminders app saves it’s data in a sqlite database, i wrote a script to find the reminders in database using sqlite.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

property Head : ""
property Database : ""
property Tail : quote
property DBLocation : (path to home folder as text) & "Library:Reminders:Container_v1:Stores:"
property schema : missing value
property foundRows : {}

on run
	set schema to getSchema()
	set reminderLists to getLists()
	set listNames to {}
	repeat with i from 1 to count reminderLists -- get names
		set end of listNames to item 4 of item i of reminderLists
	end repeat
	set myList to choose from list listNames
	if class of myList is boolean then return
	set myList to item 1 of myList
	set i to (count reminderLists)
	repeat while i > 0
		if (item i of listNames) = myList then exit repeat
		set i to i - 1
	end repeat
	if i = 0 then return
	set myList to item i of reminderLists
	getReminders(myList)
end run

on getReminders(aList)
	local Query, myFields, QueryResult, aRow, n, aTable, aField --, compFields, diffFields
	set text item delimiters to ", "
	if false then
		set myFields to {"ZACCOUNT", "ZLIST", "ZSTATUS", "ZCKIDENTIFIER", "ZCKPARENTREMINDERIDENTIFIER", "ZDACALENDARITEMUNIQUEIDENTIFIER", "ZNAME", "ZNAME2", "ZTITLE1"}
	else if true then
		repeat with aTable in schema
			set aTable to contents of aTable
			if tableName of aTable = "ZREMCDREMINDER" then exit repeat
		end repeat
		set myFields to {}
		repeat with aField in fields of aTable
			set aField to contents of aField
			if (fieldType of aField) is not in {"TIMESTAMP", "BLOB"} then --"INTEGER", "FLOAT",
				set end of myFields to fieldName of aField --set end of theFields to aField
			end if
		end repeat
	end if
	set Query to "Select " & (myFields as text) & " from " & "ZREMCDREMINDER" & " where ZLIST = " & (item 1 of aList) & " and ZCOMPLETED = 0 " & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		--display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	set foundRows to {}
	repeat with i from 1 to count QueryResult
		set end of foundRows to text items of item i of QueryResult
	end repeat
	return foundRows --ZCKPARENTREMINDERIDENTIFIER if it has a parent
end getReminders

on getLists()
	local myFields, aTable, aField, Query, QueryResult, aRow
	if schema is in {{}, missing value} then set schema to getSchema()
	repeat with aTable in schema
		set aTable to contents of aTable
		if (tableName of aTable) = "ZREMCDOBJECT" then exit repeat
	end repeat
	set text item delimiters to ", "
	set myFields to {"Z_PK", "ZACCOUNT", "ZCKIDENTIFIER", "ZNAME2"}
	set Query to "Select " & (myFields as text) & " from " & (tableName of aTable) & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	repeat with n from (count myFields) to 0 by -1
		if n > 0 then
			if (item n of myFields) is "ZNAME2" then exit repeat
		end if
	end repeat
	if n = 0 then return
	set foundRows to {}
	repeat with i from 1 to count QueryResult
		set aRow to text items of item i of QueryResult
		try
			if (item n of aRow) ≠ "" then
				set end of foundRows to aRow
			end if
		end try
	end repeat
	return foundRows
end getLists

on getSchema()
	local Query, sqlData, tblName, fldType, schema
	set schema to {}
	if Database is in {"", missing value} then
		if DBLocation is in {"", missing value} then
			return false
		else
			getDatabase()
		end if
	end if
	if Head is in {"", missing value} then set Head to "sqlite3 " & (quoted form of Database) & space & quote
	--SELECT name tableName, sql FROM sqlite_master WHERE type = 'table' AND tableName = 'COD'
	if class of schema is not list then set schema to {}
	set Query to ".tables"
	try
		set sqlData to do shell script Head & Query & Tail
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
		return
	end try
	set sqlData to get words of sqlData
	repeat with row in sqlData
		set end of schema to {tableName:contents of row, fields:{}}
		set tblName to row
		set Query to "PRAGMA table_info('" & tblName & "')"
		try
			set sqlData to do shell script Head & Query & Tail
		on error errMsg number errorNumber
			display dialog errMsg & ", " & (errorNumber as text)
			return
		end try
		set sqlData to get paragraphs of sqlData
		set text item delimiters to "|"
		repeat with row in sqlData
			set tmp to text items of row
			set fldType to item 3 of tmp
			if fldType = "" then
				set fldType to "TEXT"
			else if fldType = "INT" then
				if item 2 of tmp ends with "_bool" then
					set fldType to "BOOL"
				else if item 2 of tmp contains "date" then
					set fldType to "DATE"
				end if
			else if fldType = "REAL" then
				-- do nothing
			end if
			set contents of row to {fieldName:item 2 of tmp, fieldType:fldType}
		end repeat
		set fields of last item of schema to sqlData
	end repeat
	return schema
end getSchema

on getDatabase()
	local folderList, modDate, diff, myInfo, flag
	set folderList to list folder DBLocation
	set modDate to date "Thursday, January 1, 1970 at 12:00:00 AM"
	set prevSize to 0
	repeat with aFile in folderList
		set aFile to (DBLocation & (contents of aFile)) as alias
		set myInfo to (info for aFile)
		if name extension of myInfo = "sqlite" then
			set flag to false
			set diff to (modification date of myInfo) - modDate
			if diff < 60 and diff > -60 then
				if (size of myInfo) > prevSize then
					set flag to true
				end if
			else if diff > 0 then
				set flag to true
			end if
			if flag then
				set prevSize to size of myInfo
				set modDate to modification date of myInfo
				set Database to POSIX path of aFile
			end if
		end if
	end repeat
end getDatabase

in the table ‘ZREMCDREMINDER’ is where the Reminders are saved.
The field ‘ZCKPARENTREMINDERIDENTIFIER’ has data if it has a parent reminder

3 Likes

This is great, @robertfern! I’ll probably want to play around with this to understand it better, but this is really fascinating. Do you think it would be safe/reasonable for me to try to use this as a starting point to build a matching “restore” script, as well? Or, would trying to edit the sqlite file this way be dangerous, or introduce reliability problems?

It would be very dangerous. Some tables in the database contain over 200 fields.
Since there is no documentation as to what they all are and do, I wouldn’t risk it.

It took me awhile to decipher the fields needed just to read them.

I made a modified version to fix some problems I was having

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

property Head : ""
property Database : ""
property Tail : quote
property DBLocation : (path to home folder as text) & "Library:Reminders:Container_v1:Stores:" --sqlite-wal
property schema : missing value
property lastTable : missing value
property foundRows : {}
property otherRows : {}

on run
	local myFields, aTable, aField, Query, QueryResult, aRow, reminderLists, listNames, myList
	--set Head to "sqlite3 " & (quoted form of Database) & space & quote
	if Database is in {"", missing value} then
		if DBLocation is in {"", missing value} then
			return false
		else
			set Database to getDatabase()
		end if
	end if
	set schema to getSchema for Database given table:{"ZREMCDOBJECT", "ZREMCDREMINDER"}
	--set schema to getSchema for Database
	set reminderLists to getLists()
	set listNames to {}
	repeat with i from 1 to count reminderLists -- get names
		set end of listNames to item 4 of item i of reminderLists
	end repeat
	set myList to choose from list listNames
	if class of myList is boolean then return
	set myList to item 1 of myList
	set i to (count reminderLists)
	repeat while i > 0
		if (item i of listNames) = myList then exit repeat
		set i to i - 1
	end repeat
	if i = 0 then return
	set myList to item i of reminderLists
	return getReminders(myList)
end run

on getReminders(aList)
	local Query, myFields, QueryResult, aRow, n, aTable, aField, flag --, compFields, diffFields
	set text item delimiters to ", "
	repeat with findTable in {{"ZREMCDREMINDER", {"ZACCOUNT", "ZLIST", "ZSTATUS", "ZCKIDENTIFIER", "ZCKPARENTREMINDERIDENTIFIER", "ZDACALENDARITEMUNIQUEIDENTIFIER", "ZNAME", "ZNAME2", "ZTITLE1"}}, {"ZREMCDOBJECT", {"ZACCOUNT", "ZLIST", "ZSTATUS", "ZCKIDENTIFIER", "ZCKPARENTREMINDERIDENTIFIER", "ZDACALENDARITEMUNIQUEIDENTIFIER", "ZNAME", "ZNAME2", "ZTITLE1"}}}
		set flag to false
		repeat with aTable in schema
			set aTable to contents of aTable
			if tableName of aTable = (item 1 of findTable) then
				set flag to true
				exit repeat
			end if
		end repeat
		if flag then exit repeat
	end repeat
	set Query to "Select " & ((item 2 of findTable) as text) & " from " & (item 1 of findTable) & " where ZLIST = " & (item 1 of aList) & " and ZCOMPLETED = 0 " & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	set foundRows to {}
	repeat with aRow in QueryResult
		set contents of aRow to text items of contents of aRow
	end repeat
	return QueryResult --ZCKPARENTREMINDERIDENTIFIER if it has a parent
end getReminders

on getLists() -- get all Reminder lists
	local myFields, aTable, aField, Query, QueryResult, aRow
	if schema is in {{}, missing value} then return false
	set flag to true
	repeat with aTable in schema
		set aTable to contents of aTable
		if (tableName of aTable) = "ZREMCDOBJECT" then
			set flag to false
			exit repeat
		end if
	end repeat
	if flag then return false -- table wasn't found
	set text item delimiters to ", "
	set myFields to {"Z_PK", "ZACCOUNT", "ZCKIDENTIFIER", "ZNAME2"}
	set Query to "Select " & (myFields as text) & " from " & (tableName of aTable) & " where ZNAME2 !=  ''" & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	repeat with aRow in QueryResult
		set contents of aRow to text items of contents of aRow
	end repeat
	return QueryResult --foundRows
end getLists

on getSchema for Database given table:tblName : missing value
	local Query, sqlData, fldType, schema, i
	set schema to {}
	if Database is in {"", missing value} then return false
	if Head is in {"", missing value} then set Head to "sqlite3 " & (quoted form of Database) & space & quote
	--SELECT name tableName, sql FROM sqlite_master WHERE type = 'table' AND tableName = 'COD'
	if class of schema is not list then set schema to {}
	set Query to ".tables"
	try
		set sqlData to do shell script Head & Query & Tail
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
		return
	end try
	set sqlData to get words of sqlData
	if tblName ≠ missing value then
		if (class of tblName) is not list then
			if (class of tblName) is text then
				set tblName to {tblName}
			else
				return false
			end if
		end if
		set c to 1
		repeat with i from 1 to count tblName
			if item i of tblName is not in sqlData then
				set item i of tblName to item c of tblName
				set c to c + 1
			end if
		end repeat
		set sqlData to items c thru -1 of tblName
	end if
	repeat with aRow in sqlData
		set end of schema to {tableName:contents of aRow, fields:{}}
		set tblName to contents of aRow
		set Query to "PRAGMA table_info('" & tblName & "')"
		try
			set sqlData to do shell script Head & Query & Tail
		on error errMsg number errorNumber
			display dialog errMsg & ", " & (errorNumber as text)
			return
		end try
		set sqlData to paragraphs of sqlData
		set text item delimiters to "|"
		repeat with aRow in sqlData
			set tmp to text items of aRow
			set fldType to item 3 of tmp
			if fldType = "" then
				set fldType to "TEXT"
			else if fldType = "INT" then
				if item 2 of tmp ends with "_bool" then
					set fldType to "BOOL"
				else if item 2 of tmp contains "date" then
					set fldType to "DATE"
				end if
			else if fldType = "REAL" then
				-- do nothing
			end if
			set contents of aRow to {fieldName:item 2 of tmp, fieldType:fldType}
		end repeat
		set fields of last item of schema to sqlData
	end repeat
	return schema
end getSchema

on getDatabase()
	local Database, folderList, modDate, diff, myInfo, flag
	set folderList to list folder DBLocation
	set modDate to date "Thursday, January 1, 1970 at 12:00:00 AM"
	set prevSize to 0
	repeat with aFile in folderList
		set aFile to (DBLocation & (contents of aFile)) as alias
		set myInfo to (info for aFile)
		if name extension of myInfo = "sqlite" then
			set flag to false
			set diff to (modification date of myInfo) - modDate
			if diff < 360 and diff > -360 then
				if (size of myInfo) > prevSize then
					set flag to true
				end if
			else if diff > 0 then
				set flag to true
			end if
			if flag then
				set prevSize to size of myInfo
				set modDate to modification date of myInfo
				set Database to POSIX path of aFile
			end if
		end if
	end repeat
	return Database
end getDatabase

On my Mac at the Office with Ventura, the fields for table ‘ZREMCDREMINDER’ are a little different

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

property Head : ""
property Database : ""
property Tail : quote
property DBLocation : (path to home folder as text) & "Library:Reminders:Container_v1:Stores:" --sqlite-wal
property schema : missing value
property lastTable : missing value
property foundRows : {}
property otherRows : {}

on run
	local myFields, aTable, aField, Query, QueryResult, aRow, reminderLists, listNames, myList
	--set Head to "sqlite3 " & (quoted form of Database) & space & quote
	if Database is in {"", missing value} then
		if DBLocation is in {"", missing value} then
			return false
		else
			set Database to getDatabase()
		end if
	end if
	set schema to getSchema for Database given table:{"ZREMCDOBJECT", "ZREMCDREMINDER"}
	-- TESTING
	repeat with aTable in schema
		set aTable to contents of aTable
		if (tableName of aTable) = "ZREMCDREMINDER" then
			repeat with aField in fields of aTable
				set aField to contents of aField
				set end of otherRows to fieldName of aField
			end repeat
		end if
	end repeat
	set text item delimiters to return
	get otherRows as text
	-- END testing
	--set schema to getSchema for Database
	set reminderLists to getLists()
	set listNames to {}
	repeat with i from 1 to count reminderLists -- get names
		set end of listNames to item 4 of item i of reminderLists
	end repeat
	set myList to choose from list listNames
	if class of myList is boolean then return
	set myList to item 1 of myList
	set i to (count reminderLists)
	repeat while i > 0
		if (item i of listNames) = myList then exit repeat
		set i to i - 1
	end repeat
	if i = 0 then return
	set myList to item i of reminderLists
	return getReminders(myList)
end run

on getReminders(aList)
	local Query, myFields, QueryResult, aRow, n, aTable, aField, flag --, compFields, diffFields
	set text item delimiters to ", "
	repeat with findTable in {{"ZREMCDREMINDER", {"ZACCOUNT", "ZLIST", "ZCKIDENTIFIER", "ZCKPARENTREMINDERIDENTIFIER", "ZDACALENDARITEMUNIQUEIDENTIFIER", "ZTITLE"}}, {"ZREMCDOBJECT", {"ZACCOUNT", "ZLIST", "ZSTATUS", "ZCKIDENTIFIER", "ZCKPARENTREMINDERIDENTIFIER", "ZDACALENDARITEMUNIQUEIDENTIFIER", "ZNAME", "ZNAME2", "ZTITLE1"}}}
		set flag to false
		repeat with aTable in schema
			set aTable to contents of aTable
			if tableName of aTable = (item 1 of findTable) then
				set flag to true
				exit repeat
			end if
		end repeat
		if flag then exit repeat
	end repeat
	set Query to "Select " & ((item 2 of findTable) as text) & " from " & (item 1 of findTable) & " where ZLIST = " & (item 1 of aList) & " and ZCOMPLETED = 0 " & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	set foundRows to {}
	repeat with aRow in QueryResult
		set contents of aRow to text items of contents of aRow
	end repeat
	return QueryResult --ZCKPARENTREMINDERIDENTIFIER if it has a parent
end getReminders

on getLists() -- get all Reminder lists
	local myFields, aTable, aField, Query, QueryResult, aRow
	if schema is in {{}, missing value} then return false
	set flag to true
	repeat with aTable in schema
		set aTable to contents of aTable
		if (tableName of aTable) = "ZREMCDOBJECT" then
			set flag to false
			exit repeat
		end if
	end repeat
	if flag then return false -- table wasn't found
	set text item delimiters to ", "
	set myFields to {"Z_PK", "ZACCOUNT", "ZCKIDENTIFIER", "ZNAME2"}
	set Query to "Select " & (myFields as text) & " from " & (tableName of aTable) & " where ZNAME2 !=  ''" & ";"
	try
		set QueryResult to paragraphs of (do shell script Head & Query & Tail)
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
	end try
	set text item delimiters to "|"
	repeat with aRow in QueryResult
		set contents of aRow to text items of contents of aRow
	end repeat
	return QueryResult --foundRows
end getLists

on getSchema for Database given table:tblName : missing value
	local Query, sqlData, fldType, schema, i
	set schema to {}
	if Database is in {"", missing value} then return false
	if Head is in {"", missing value} then set Head to "sqlite3 " & (quoted form of Database) & space & quote
	--SELECT name tableName, sql FROM sqlite_master WHERE type = 'table' AND tableName = 'COD'
	if class of schema is not list then set schema to {}
	set Query to ".tables"
	try
		set sqlData to do shell script Head & Query & Tail
	on error errMsg number errorNumber
		display dialog errMsg & ", " & (errorNumber as text)
		return
	end try
	set sqlData to get words of sqlData
	if tblName ≠ missing value then
		if (class of tblName) is not list then
			if (class of tblName) is text then
				set tblName to {tblName}
			else
				return false
			end if
		end if
		set c to 1
		repeat with i from 1 to count tblName
			if item i of tblName is not in sqlData then
				set item i of tblName to item c of tblName
				set c to c + 1
			end if
		end repeat
		set sqlData to items c thru -1 of tblName
	end if
	repeat with aRow in sqlData
		set end of schema to {tableName:contents of aRow, fields:{}}
		set tblName to contents of aRow
		set Query to "PRAGMA table_info('" & tblName & "')"
		try
			set sqlData to do shell script Head & Query & Tail
		on error errMsg number errorNumber
			display dialog errMsg & ", " & (errorNumber as text)
			return
		end try
		set sqlData to paragraphs of sqlData
		set text item delimiters to "|"
		repeat with aRow in sqlData
			set tmp to text items of aRow
			set fldType to item 3 of tmp
			if fldType = "" then
				set fldType to "TEXT"
			else if fldType = "INT" then
				if item 2 of tmp ends with "_bool" then
					set fldType to "BOOL"
				else if item 2 of tmp contains "date" then
					set fldType to "DATE"
				end if
			else if fldType = "REAL" then
				-- do nothing
			end if
			set contents of aRow to {fieldName:item 2 of tmp, fieldType:fldType}
		end repeat
		set fields of last item of schema to sqlData
	end repeat
	return schema
end getSchema

on getDatabase()
	local Database, folderList, modDate, diff, prevSize, myInfo, aFile, flag
	set folderList to list folder DBLocation
	set modDate to date "Thursday, January 1, 1970 at 12:00:00 AM"
	set prevSize to 0
	repeat with aFile in folderList
		set aFile to (DBLocation & (contents of aFile)) as alias
		set myInfo to (info for aFile)
		if name extension of myInfo = "sqlite" then
			set flag to false
			set diff to (modification date of myInfo) - modDate
			if diff < 360 and diff > -360 then
				if (size of myInfo) > prevSize then set flag to true
			else if diff > 0 then
				set flag to true
			end if
			if flag then
				set prevSize to size of myInfo
				set modDate to modification date of myInfo
				set Database to POSIX path of aFile
			end if
		end if
	end repeat
	return Database
end getDatabase