Results 1 to 4 of 4
  1. tcurtin's Avatar
    Posts
    150 Posts
    Global Posts
    151 Global Posts
       #1  
    Ok - the /var/luna/data/emails directory is filling with loads of emails that no longer exist. People have been just deleting the directories en masse, but there are issues with that - if the phone tries to look at an email that's been deleted, it won't show anything. Users have been starting from scratch by deleting all of their email accounts and readding, but that seems like overkill to. So - I wanted to try to figure out a way to do this safely. Here's my first attempt - I'd appreciate any comments from folks in this forum.

    I did some digging using an email with a unique subject line:

    cd /var/luna/data/emails
    find . -exec grep -l 'Can You Make Smart Calorie' {} \;
    ./bucket-2/email_111378175484570.html


    sqlite3 -line /tmp/PalmDatabase.db3 "select * from com_palm_pim_FolderEntry where summary like '%Can You Make Smart Calorie%'"
    status =
    statusErrorCode =
    deviceTimeStamp = 1260256204265
    messageType =
    messageServiceName =
    callbackNumber =
    fromAddress =
    invitationGroupId =
    smsClass =
    smsType =
    smsc =
    encoding =
    validity =
    isVoicemail =
    simMessage =
    mmsTransactionId =
    mmsVersion =
    mmsMessageClass =
    mmsMessageId =
    mmsPriority =
    mmsDeliveryReport =
    mmsReadReply =
    mmsContentType =
    isInProgress =
    retryTally =
    uniqueAttachmentFolderName =
    mmsRelayType =
    mmsExpiry =
    mmsStatus =
    mmsReportAllowed =
    mmsTo =
    mmsCc =
    mmsDate =
    mmsMessageSize =
    mmsContentLocation =
    mmsResponseStatus =
    mmsResponseText =
    timeStamp = 1260255984281
    summary = Quiz: Can You Make Smart Calorie Swaps?
    messageText =
    flags = 4
    priority = 0
    displayName = The Weekly Spark
    firstName =
    lastName =
    remoteId =
    extraDetails =
    email = 1
    com_palm_accounts_ActiveRecordFolder_id = 76965813944346
    id = 59373627904841
    _class_id = 54
    _mod_num = 747663
    _flags =
    html = 1
    messageTextLoaded = 1
    meetingResponseCode = 0
    subCount = 0
    previewText =   Can You Make Smart Calorie Swaps? Take this quiz to find out ho
    envelope = ("Tue, 08 Dec 2009 02:03:36 -0500" "Quiz: Can You Make Smart Calorie Swaps?" (("The Weekly Spark" NIL "mailer" "sparkpeople.com")) (("The Weekly Spark" NIL "mailer" "sparkpeople.com")) ((NIL NIL "mailer" "sparkpeople.com")) (("--fullname deleted--")) NIL NIL NIL "<1231890964.3804@sparkpeople.com>")
    uid = 44013
    com_palm_pim_CalendarEvent_id =
    moveFolder =
    fullyLoaded = 1
    messageId =
    serverId =
    textLocation = 1
    textCacheKey = email_111378175484570.html
    invisible = 0
    htmlLocation = 2
    altTextCacheKey =
    modTime = 1260256207632
    replyId =
    summaryCRC32 = 3331232089
    lastSyncedFlags = 4

    Notice that textCacheKey line? That matches the file in the bucket-2 directory. Unfortunately, from checking some other files, it doesn't look like there's any field in com_palm_pim_FolderEntry that corresponds reliably to a bucket folder. No problem. Check out this perl script - anyone see any major issues with this?


    #!/usr/bin/perl

    open(DBIN,"/usr/bin/sqlite3 /tmp/PalmDatabase.db3 'select textCacheKey from com_palm_pim_FolderEntry where textCacheKey is not null' |") || die "sqlite3 call failed: $!";
    while (<DBIN>) {
    chomp;
    $hDBData{$_}++;
    }
    close(DBIN);

    open(FINDIN,"find /Volumes/Bruno/Backups/Pre/Daily/var/luna/data/emails |") or die "Couldn't open find: $!";
    while (<FINDIN>) {
    chomp;
    next if (! /(.html|.txt)$/);
    my (@tokens) = split('/');
    if ($hDBData{$tokens[-1]}) {
    # this file is still present
    #print "KEEP: $_\n";
    } else {
    # this file's not in the data anymore
    my $cmd = "rm $_";
    print "$cmd\n";
    #system($cmd);
    }
    }
    close(FINDIN);

    I found 3217 files that don't match, and 777 files that should be kept. I haven't dug into this much more yet - I will. In the meantime, if you choose to use the above script, you'll obviously need to modify the locations in the open commands, and all usual disclaimers apply. If you don't understand how to modify the script, please don't use it until its been cleaned up!
  2. #2  
    Please be careful. You may run into some issues for those with bad entries in PalmDatabase. For example, I am unable to VACUUM my db due to hanging PIM contacts.

    What I have been doing is removing everything but the last 7 days and then rebooting. Seems to work so far.
  3. tcurtin's Avatar
    Posts
    150 Posts
    Global Posts
    151 Global Posts
       #3  
    I'm not sure I understand? I'm assuming I'll be safe by doing something like the following:

    Open email app, hit sync button, allow to complete.
    Close email app.
    Put pre into "airplane" mode to prevent new emails from coming in.
    Run cleanup script.


    I'm not sure what problems you're referring to or how they would manifest. Would they mean I couldn't trust the query? I'm not planning to actually modify the database at all, and if I've managed to stop the email app from doing anything new while I'm doing the purge, it seems to me that all my bases would be covered.

    Anything I'm leaving out? It sounds like there's some background to PalmDatabase that I'm not aware of, so any info would be much appreciated!
  4. tcurtin's Avatar
    Posts
    150 Posts
    Global Posts
    151 Global Posts
       #4  
    Quote Originally Posted by jauderho View Post
    Please be careful. You may run into some issues for those with bad entries in PalmDatabase. For example, I am unable to VACUUM my db due to hanging PIM contacts.

    What I have been doing is removing everything but the last 7 days and then rebooting. Seems to work so far.

    Hmm - I just looked into the vacuum command, and it sounds useful. But, it dies on me with a not-so-useful message. (SQL error: SQL logic error or missing database) I'm curious how you were able to find the root cause for not being able to vacuum?

Posting Permissions