11 Replies Latest reply: May 18, 2009 1:09 PM by Austin Kinsella1
MacBookWales Level 1 Level 1 (0 points)
I need to figure out how to read event entries in the Cache file stored in /Library/Calenders/ as my actual events have been corrupted due to a file system problem. The example below shows the information I have for each entry, where the event in the entry was Bon Jovi Concert, however I am confused how I can read the date and time of the event, information I believe is stored as 218215639,219613081,219524400,219540600. Any help is greatly appreciated.

INSERT INTO "ZCALENDARITEM" VALUES(0,NULL,0,NULL,0,5,NULL,6,4039,NULL,0,0,0,0,0,0,2,23,0,0,0,0,6,0,21821563 9,219613081,219524400,219540600,NULL,NULL,NULL,NULL,NULL,'local_AFB8D342-2DAE-4F A1-A9A6-3FA9B28B5C7C','Bon Jovi Concert',NULL,NULL,NULL,'Europe/London',NULL,'0E17BBB6-0E76-4024-8DD7-60E43D38D 35B');

MacBook, Mac OS X (10.5.6), 2Ghz Intel CoreDuo, 2GB RAM
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    You can use sqlite commands in the terminal window to examine the database. This page might help. So for example


    sqlite3 Calendar\ Cache


    will open the database and


    sqlite> .schema ZCALENDARITEM


    will give the schema - in effect, the field names for the data you show.


    select ZSTARTDATE from ZCALENDARITEM where ZTITLE = "Bon Jovi Concert";


    will return a value such as 219524400 for the start date of any items with title Bon Jovi concert. Outside of sqlite3 you can convert this to a human-readable format at the terminal prompt with


    date -r 219524400


    but this will give something like Wed 15 Dec 1976, as date works in seconds from 1970, whereas iCal works from 2001, so instead use


    date -r 219524400 -v+31y


    to get Sat 15 Dec 2007.

    It might also be helpful to use a database browser such as Razor SQL.

    AK
  • MacBookWales Level 1 Level 1 (0 points)
    thanks for the info, exactly what I wanted to know! I have a couple more questions as you appear to know a lot more about it than I do!

    - how can I enter a date and find out the equivalent number? I want to know this so that I can search within the SQL program for events within a certain date range.
    - is there anyway I can import these SQL entries into iCal as new events? Even if I used automator somehow to read the entries and make new ones?

    I posted my main problem here http://discussions.apple.com/thread.jspa?threadID=2006448 but I havent recieved any suggestions,

    thanks again.
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    Actually most of what I know about it has been found from searching around after seeing your post.

    How did you get your data?


    INSERT INTO "ZCALENDARITEM" VALUES(0,NULL,0,NULL,0,5,NULL,6,4039,NULL,0,0,0,0,0,0,2,23,0,0,0,0,6,0,21821563 9,219613081,219524400,219540600,NULL,NULL,NULL,NULL,NULL,'local_AFB8D342-2DAE-4F A1-A9A6-3FA9B28B5C7C','Bon Jovi Concert',NULL,NULL,NULL,'Europe/London',NULL,'0E17BBB6-0E76-4024-8DD7-60E43D38D 35B');



    'Cos that is the sqlite command to put the data into iCal's database. But I don't know if iCal would then make the corresponding .ics file in the Events folder of the calendar folder. Why not try it? Backup everything first.

    AK
  • MacBookWales Level 1 Level 1 (0 points)
    the data came from the Cache files in the Calender folder. If you open them in TextEdit, the file is a little messed up. I ran it through File Juicer, which then gave me a text file that is a little tidier, with the entries like the one above. There are over 4,000 in total.

    How would I run the sqlite command? I've tried putting it in the terminal window but it shows an error. I assume I need to choose the folder for iCal or it's database somehow first?
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    In Terminal give the sqlite3 Calendar\ Cache command, then at the sqlite prompt give the INSERT command. Make sure iCal isn't running.

    But if you pulled it from Calendar Cache in the first place I can't see it doing any good. Perhaps we should start again. What is the actual problem, and what steps have you taken to resolve it?

    AK
  • MacBookWales Level 1 Level 1 (0 points)
    My original problem is this:

    I had an issue with iCal whereby I wasn't able to search for events, so I followed the advice of one of the other forums, and ran a command in Terminal which forced iCal to rebuild it's cache. In doing this, it exposed another issue which I thought had been resolved. My file system corrupted a few months ago, and I spent a lot of money recovering data, some of which was unrecoverable. I was happy to see that my iCal files had been fully recoverable.

    But now that iCal has rebuilt its cache, it seems that many of the events iCal stores in the Calenders folder in Library, hadn't been recovered, and were only showing in iCal because they were still in the most recent cache. I was partially aware of this issue when I had tried making a backup from inside iCal, but it only saved some of the events.

    I have looked through the file system, and some of the events are in the Calenders folder and they still appear in iCal. However the majority, do not appear there, however I can find references to them in the older Cache files. I have tried replacing the latest Cache file with and older version, but iCal always rebuilds the file upon startup. TimeMachine only stores backups of files, not of Caches that it can rebuild, and so whilst the older caches are in the backup, everytime I restore the backup from TimeMachine and run iCal, it says upgrading calenders and it builds a new cache, based on the events that physically exist in the Calenders folder, which is only a small proportion of the ones I had.

    I either need to work out a way of forcing iCal to use an old version of the cache or a way that I can import the data from the cache, the events entries like the one above, so I can put them back into iCal.

    Thanks again for your help, all is appreciated!
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    OK, I think this is do-able. First we extract the event info into a file, then process the file with Appletalk to make it back into events. I want to see if getting the stuff into a file is going to work the way I think it will, so please try this:

    Put a copy of one of the cache files that you want to try to recover on your Desktop, and change its name to Hope. Then run Terminal, paste in this command and press return.


    sqlite3 Desktop/Hope 'select ZSTARTDATE, ZCALENDARITEM.ZTITLE, ZENDDATE, ZNODE.ZTITLE from ZCALENDARITEM inner join ZNODE on ZCALENDARITEM.ZCALENDAR = ZNODE.Z_PK where ZSTARTDATE not null' >hope.txt


    Open the new file hope.txt on the Desktop with Text Edit, copy about 10 lines and paste them in your reply.

    Are any of the events you are trying to recover recurring events?
    Are any of them all-day events?
    Are any of them in the Birthdays calendar?
    Are any of them To Dos rather than events?
    Have any of them notes?
    Have any of them other non-basic features (invitations, attachments ...) that might trip up a poor Applescript?

    AK
  • MacBookWales Level 1 Level 1 (0 points)
    Hi Austin, I followed the instructions, here are some of the lines from the file it produced:

    166003200|Malaria Tablet|166004100|Important
    233514000|Vic Chiropracter Appointment|233517600|Important
    256892400|LON|256896000|Travel
    162378000|GV Int Org Tutorial O.G.33|162383400|Lectures + Tutorial
    162518400|Matt 1200-2100|162604800|Unclassified
    161002800|Alex iPod CD|161006400|Work
    176250600|Orange £35 DD|176252400|Financial
    201945600|Day Off|201949200|Unclassified
    202420800|Stay in Vancouver|202424400|Travel
    166042800|Train to Delhi|166046400|Travel

    I believe some of the events are recurring events.
    Some of them are all day events.
    None are in the birthdays calender.
    None are To Do's.
    None have notes.
    No other non basic features.

    There are about 4000 events in total. They cover about 5 years in total, however it's only about 1.5 years that I have lost, and I used an sql application to narrow them down and only show the events that are missing (ie. within the 1.5 years lost) using the startdate and enddate for the events either side of the missing period. So I think any applescript would be great if it could focus on just these missing events. Let me know if you need any more info.
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    OK, here we go ... I hope. I have tried it on my iCal, with about 2000 entries, and it worked. I cannot guarantee that it will work for you.

    Make a new text file from sql as before, but using this modified command to get additional data for each event:


    sqlite3 Desktop/Hope 'select ZSTARTDATE, ZCALENDARITEM.ZTITLE, ZENDDATE, ZNODE.ZTITLE, ZISALLDAY, ZRECURRENCERULE, ZISDETACHED from ZCALENDARITEM inner join ZNODE on ZCALENDARITEM.ZCALENDAR = ZNODE.Z_PK where ZSTARTDATE not null' >hope.txt



    I suggest you make a new user account, then copy the text file hope.txt and the script to the Public/Drop Box folder for that account. Log on to the new account and copy the two files from the drop box to the Desktop. Start iCal and make new calendars to match those in your ordinary account. Double click the script to open it in Script Editor and CHANGE THE DATE RANGE in the "set ThePeriod" line. Stand well back and click the run button. Every 50 records processed it will pop up a progress box, which will pop down again after a second. Go for lunch.

    When it is finished see if things look OK in iCal. If they do export each of the calendars, copy them to the drop box of your normal account and return to your normal account to import them.

    If there is an error make a note of the error message and line number, find that line in the text file, and post it here with a couple of lines either side.

    Note that for recurring items iCal normally only makes a single event, the first occurrence, then uses a recurrence rule to calculate if the event should be displayed in the current window. If there are any recurring events in the period you have selected where the first occurrence is before the period they will not be recreated. Where however you have changed a single occurrence of a recurring event iCal makes a new, detached, event. Any of these in the period will be detected. If their original event was also in the period they will now appear in the iCal display as duplicates. For easy spotting of these, I have prefixed "XX-" to the title of any detached events.

    AK

    <pre style="font-family: 'Monaco', 'Courier New', Courier, monospace; overflow:auto; color: #222; background: #DDD; padding: 0.2em; font-size: 10px; width:400px">on run
    set ThePeriod to {date ("jan 1 2008"), date ("dec 31 2008")} --CHANGE THIS BEFORE RUNNIN
    set TheFile to open for access (path to desktop as text) & "temp.txt"
    set TheContents to read TheFile --until return
    close TheFile
    set TheLines to paragraphs of TheContents
    set OldDelim to AppleScript's text item delimiters
    set AppleScript's text item delimiters to {"|"}
    set LCount to 0
    set ACount to 0
    set RCount to 0
    set DCount to 0
    set SCount to 0
    set HowMany to (count of TheLines)
    try
    repeat with ThisLine in TheLines
    if (count of ThisLine) is 0 then exit repeat
    -- Start Date, Title, End Date, Calendar, All Day, Recurs, Detached
    set LCount to LCount + 1
    set Details to text items of ThisLine
    set MyStartDate to FixMyDate(item 1 of Details)
    set MyTitle to item 2 of Details
    set MyEndDate to FixMyDate(item 3 of Details)
    set MyCalendar to item 4 of Details
    set MyAllDay to item 5 of Details
    set MyRecurs to item 6 of Details
    set MyDetached to item 7 of Details
    if MyAllDay is "1" then set ACount to ACount + 1
    if (count of MyRecurs) > 0 then set RCount to RCount + 1
    if MyDetached is "1" then set MyTitle to "XX-" & MyTitle
    if MyDetached is "1" then set DCount to DCount + 1
    if (MyCalendar is not "Birthdays") and (MyStartDate ≥ item 1 of ThePeriod) and (MyStartDate ≤ item 2 of ThePeriod) then
    tell application "iCal"
    tell calendar MyCalendar
    set ThisItem to make new event at end of events with properties {summary:MyTitle, start date:MyStartDate}
    end tell
    tell ThisItem
    if MyAllDay is "1" then set allday event to true
    if (count of MyRecurs) > 0 then set recurrence to MyRecurs
    set end date to MyEndDate
    end tell
    end tell
    else
    set SCount to SCount + 1
    end if
    if (LCount mod 50) = 0 then
    set the_message to "Processed " & (LCount as string) & " of " & (HowMany as string)
    display dialog the_message buttons {"Cancel"} giving up after 1
    end if
    end repeat
    on error TheError
    display dialog "Error: " & TheError & " about line " & LCount
    end try
    set AppleScript's text item delimiters to OldDelim
    display dialog (LCount as string) & " lines processed" & return & "All Day: " & (ACount as string) & return & "Recurs: " & (RCount as string) & return & "Detach: " & (DCount as string) & return & "Skipped: " & (SCount as string)
    end run

    on FixMyDate(MyDate)
    date (do shell script "date -r " & MyDate & " -v+31y +%e'/'%m'/'%y' '%T")
    end FixMyDate
    </pre>
  • MacBookWales Level 1 Level 1 (0 points)
    Hey, I believe I followed your instructions correctly:

    - made a txt file 'Hope.txt' from sql using the modified command (it creates the txt file in the top folder for my user, I then move it to the desktop)
    - created a new user account and new calenders (its possible that one or two have different names to what they used to be, but the other 5 or so are accurate)
    - copy and paste the script into script editor, change the dates to dec 1 2007 and jan 30 2009 then click run

    The script stops pretty much instantly, showing the dialog box - AppleScript Error, End of file error.

    It then returns to the script window, with the underlined part of the lines below highlighted

    set TheFile to open for access (path to desktop as text) & "temp.txt"
    set TheContents to _read TheFile_ --until return
    close TheFile

    It creates a file called temp.txt on the desktop, but it's empty.

    I have never run a script before so I hope I am doing it correctly. How does the script know to look inside the Hope.txt file, I don't see any reference to it in the script? I feel like we are really close so as always, your suggestions are greatly appreciated.
  • Austin Kinsella1 Level 6 Level 6 (11,520 points)
    Ooops, sorry - my test file was temp.txt. Change the temp in


    set TheFile to open for access (path to desktop as text) & "temp.txt"


    to Hope and try again.

    It will throw an error if there is a calendar that does not match.

    AK