Deleting Individual Location History Entries

[UPGRADE released  2/9/2008 that allows you to do this now] 

No, I haven’t figured out how to do this yet, but I figured I’d start a blog item on the subject anyways in hopes that someone will help figure this out.   Many of us MioMap users in general (not just C230 users) would like to have the ability of deleting individual items in the “Find Location” History list, which is part of the SQLite 3.0 database file iGo.db, since right now it’s Clear All or nothing.

igo_db.jpg

If this tool would have let me see the actual data in any of the tables, we’d probably be done, in that we could simply scrub the History entries we no longer cared about and then Save the file with those changes.  Unfortunately, the data appears to be “blob”, so this is probably a dead-end.   The screenshot does give you an idea of what data is in the iGo.db file though.

The iGo program POIexplorer is able to work with the POI data in the file, so it may give a hacker a clue as to how to proceed.   Perhaps it is tied to the ADO.NET wrapper shown here?

igo_poi.jpg

[UPDATE 1/28/2008]   WildBill has looked into this further using SQLite Expert  (which does a much better job of letting you see the blob data) and figured out a manual “crude” method to delete intermediate records.
I’ve done some additional experimentation with the SQLite Expert app and have had limited success.
First, I restored the database, ran SQlite Expert and discovered that there were seven records with ID labels of 2 thru 8. I then cleared the table, using the MIO program, and discovered an additional record. I assume this was record ID 1, I’ve no idea where it was stored or why MIO did not clear it. I then cleared this additional record and built a new table, the Id’s of the new records started at 1.
  
I was able to delete the last entry and reload the database correctly.
I then tried to delete a record from the middle of the table and the reloaded database actually had two additional records deleted.
With some additional time, this app might prove to be very useful.

When you open a table in the database you will notice that both the RecNo and ID fields start at 1 and increase by one for each succeeding record. The F1 field starts at 0 and then counts up.
If an intermediate record is deleted, the RecNo fields are automatically updated, but the ID and F1 fields have to be manually edited to maintain the correct numerical progression. If this is not done, the records out of progression are not displayed when the revised database is copied back into the MIO. The SQE app returned an error while I was deleting and revising multiple records, so I would recommend correcting several records at a time, I did three, and then saving the database before resuming.
Is the effort required to manually edit the counters worth it???
It appears that someone experienced in SQL programming might be able to wrap this up with a script that we could use in conjunction with this free tool, rather than writing a dedicated app.

      Output the history entries
>———->   Select an unwanted record
|                        N = Save & Exit
|                        Y = Process that record per your strategy retaining
|                                the integrity of the database
|                                |   
<———————–<

[UPDATE 2/7/2008]  

I changed the title from Deleting Route History Individual Entries, which wasn’t really correct to Deleting Individual Location History Entries.

Bill has figured out the steps needed to accomplish this and they are listed in my comment dated 2/2, minus the SQL script which actually repairs the database after you’ve deleted the locations no longer wanted in the list. I was going to try to automate the entire process with Mortscript, but controlling the actions within a program running on a PC (not the controlled screen of your Mio unit) is proving to be a challenge, so instead I’ve written scripts for my Menu program to “Prep Location History file for SQL edit” (step 1) and “Save edited Location History file” (step 9). Steps 2 thru 8 will be done on your PC while your SD card is in a card reader or in your Mio C230 linked to your PC. I will create a file that you will download to your SD card that will hold the working file and will contain the SQL script.

Advertisements

26 Comments

  1. robb1 said

    Why would they encrypt this data? Its nothing of extreme secrecy. I can use a hex editor and see the names in the history tree and other information. If they were encrypted I would not be able to see them. I know nothing about sq but the first text I see in this file is SQLite Format 3.

  2. c230 said

    OK robb1, bad choice of words on my part. But I wasn’t able to do a SQL query and see the data either. See, 1 comment so far – like I said I need help on this one. 😀

    I’ll use “non-standard” for now ’till a learned scholar comes to the rescue.

  3. amida168 said

    c230,
    The data format for the field is blob. It’s binary and not encrypted. I have used a better SQL tool to see the actual data. Part of the data, that is. I forgot what is the tool’s name, though. 😉

  4. c230 said

    Thanks Amida. This project would probably require someone writing a program akin to POI Explorer then.

  5. haco81 said

    I was wondering: what is stored in this database, every second your position, or something else?
    It would be great allready if we could read out!

  6. robb1 said

    Open it with Notepad and you can get an idea. It won’t be pretty but you can peek around. Looks like history, license, poi’s.

  7. c230 said

    The illustration at the top of this blog is a catalog of its data tables.

  8. Wild Bill said

    Dan,
    Have you tried SQLite Expert (freeware), http://www.sqliteexpert.com? I was able to open the database and then view the blob data for each record, using the built-in editor.

  9. c230 said

    Nice find Bill! No I hadn’t even heard of it, but I see the newest version just came out Thursday. So, should we take a chance and just delete a city record to see what happens? 🙂

  10. Wild Bill said

    I tried to delete just one city from the table but the entire list was cleared when I copied the revised db to the MIO. Looks like it will take a little more effort to perfect the process.

  11. c230 said

    I hope you had backed up the file first; now that I think of that after the fact, the trial really wouldn’t have been that big a deal. Thanks for trying! You;re right, the tool does let you read the blobs in a more orderly fashion than Notepad..

  12. dominique said

    hi all,
    i’ve read this :
    “One characteristic that a programmer can count on when writing Windows CE applications is Unicode. Unicode is a standard for representing a character as a 16-bit value as opposed to the ASCII standard of encoding a character into a single 8-bit value. Unicode allows for fairly simple porting of programs to different international markets because all the world’s known characters can be represented in one of the 65,536 available Unicode values. Dealing with Unicode is relatively painless as long as you avoid the dual assumptions made by most programmers that strings are represented in ASCII and that characters are stored in single bytes.”

    seems that it is our problem …

  13. c230 said

    [UPDATED 1/26/2008] Calling on SQL savvy scripter(s) – see above

  14. Wild Bill said

    Dan,

    I’ve found the logic flow and the required code to automate the process of resetting the counters. I’ll email you the results so that you can verify them and then post them.

  15. c230 said

    Fantastic! You’ve invested a lot of time into this and I’ll bet it will draw a lot of interest once it’s up and running.

    I see there’s a PC version of MortScript, so perhaps we’ll be able to script all of this, pausing only for the user to select the to-be-deleted records 🙂

    If I understand your flow (not verified yet), we’ll break it into mortscript instructions that need to be turned into code. After linking C230 to PC with SD disk inserted…

    1. Copy \My Flash Disk\MioMap\MioMap2\iGo.db to \Storage Card\iGo_Work\iGO_working.db3
    2. Run SQLite Expert Personal and open the working file \Storage Card\iGo_Work\iGO_working.db3
    3. The tables and views will be displayed in the left tree panel. Click on the HISTORY_V4 table to display it in the right panel.
    4. Click on the DATA tab to display the records in the table.
    5. To reveal the blob information stored the DATA field for a record, double click on the BLOB to open the hex editor.
    6. To delete a highlighted record, click on the delete record button ( – ).
    7. Repeat for any other entries you wish to delete from the HISTORY_V4 table. Then click on the SQL tab to open the upper SQL edit window.
    8. Select SQL >> Load SQL Script >> Execute SQL.
    9. Copy \Storage Card\iGo_Work\iGO_working.db3 back to \My Flash Disk\MioMap\MioMap2\iGo.db

  16. c230 said

    –Update–

    I changed the title from Deleting Route History Individual Entries, which wasn’t really correct to Deleting Individual Location History Entries.

    Bill has figured out the steps needed to accomplish this and they are listed above, minus the SQL script which actually repairs the database after you’ve deleted the locations no longer wanted in the list. I was going to try to automate the entire process with Mortscript, but controlling the actions within a program running on a PC (not the controlled screen of your Mio unit) is proving to be a challenge, so instead I’ve written scripts for my Menu program to “Prep Location History file for SQL edit” (step 1) and “Save edited Location History file” (step 9). Steps 2 thru 8 will be done on your PC while your SD card is in a card reader or in your Mio C230 linked to your PC. I will create a file that you will download to your SD card that will hold the working file and will contain the SQL script.

    [Don’t try this yet, until the SQL file is published]

  17. CD said

    I tried these steps. The only one I do no understand is step #8. I can do the end of #7. I can even find Select SQL >> Load SQL Script. But then I have no idea what >> Execute SQL is. So when I out the iGO.db back in…. all entries are erased. I guess I missed the rebuilding step?

  18. c230 said

    Sorry CD,

    I was just providing an update to where I’m at on this blog. I hadn’t meant for anyone to try this yet, since the most important part (the sql file) hasn’t been published yet. I’ve been beta testing to make sure the modified iGo.db doesn’t mess up any skins, etc.

    I’m planning on releasing an upgrade package this weekend that will upgrade the MioMap menu only, so that if you are using my version 3.3, version 3.3 LITE, version 3.4, or version 3.5, you will have all of the latest menu options including the 2 mentioned above that will allow editing of Location History entries.

    The file will include Version 3.6 of Routes.mscr and the SQL file that WildBill wrote. The menu has been rewritten to coincide with the LITE Unlock as well, and will serve as my upgrade for that version.

  19. CD said

    Gotcha, thanks!

  20. Wild Bill said

    Dan,

    CD might be able to save the data in his revised table. If he has not closed the database file yet, I believe he can still run the SQL on his revised data. CD if you start SQL Expert is the revised data still available without restarting the iGO.db ? If it is, then you can still run the SQL script and not lose all the data in your original table.

  21. CD said

    I do not know how to answer. Here’s what I did:

    I dragged a copy of iGO.db to my desktop from the Mio flash drive.
    I opened iGO.db with SQL Expert Personal
    I clicked on the HISTORY_V4 folder on the left pane
    I clicked the DATA TAB on the right pane
    I could click any ‘(BLOB)’ under the ‘DATA’ column and see the information.

  22. CD said

    Whoops. Hit the SUBMIT button accidentally…

    I then deleted 28 of the 31 records with the ‘-‘ button.
    I then Clicked the ‘SQL’ Tab in the right pane. There is nothing listed.

    This is where I was stuck. I could go to the top and pick SQL>>>Load SQL Script… but then it wanted to load something which I have no idea about.

    What do I do after I have deleted the unwanted records?

  23. Wild Bill said

    CD,

    Since all data vanished, I assumed you must have deleted the first record in the table. MIO will not recognize all the data in a revised table if the ID and F1 fields are not consecutively numbered, MIO will ignore any records out of sequence. If you did not close the database, ie File >> Close Database, before exiting SQL Expert, you should be able to reopen SQL Expert, your revised History table will still be shown and you can then run the SQL script to reset the numerical order in the ID and F1 fields. Let us know if the table is still available in SQL Expert and Dan or I will get you a copy of the SQL script that will complete the process.

  24. Wild Bill said

    CD,

    Since you only have three of the original records, you could manually edit the ID and F1 fields. The ID fields should start at 1 and the F1 fields should start at 0. Use the edit record and post edit buttons to make the changes needed, or you could use the SQL script. Either proceedure still requires that you did not close the database before exiting SQL Expert.

  25. c230 said

    CD,

    Just got home and see you’re trying to repair your iGo.db. I’ll email you the sql script in a moment. It works great for me!

  26. […] vs. your only option currently to CLEAR ALL.    I wish to thank WildBill very much for seeing this project through to fruition.   It serves it’s mission quite nicely considering that it isn’t […]

RSS feed for comments on this post · TrackBack URI

Comments are closed.

%d bloggers like this: