Related parent project: "Firefox Delicious Replacement Project".

Intro

This is an at-own-risk runthrough of migrating your (old yahoo/Avos-related) delicious bookmarks to firefox bookmarks. And pretty manually!

Alas, this is the choice I have made.  But there are a lot of migration projects to transfer firefox bookmarks to yet another format or service if you are not satisfied with this.

Notes

  • Several delicious bookmarks features will be lost (such as history of delicious bookmarks).
    Time is not on my side, you know?
  • Do NOT run the SQL file without reading and executing the comments in there.

Run-through

  1. Make 2 copies of your delicious bookmarks en firefox bookmarks.
    • Both can be found under your Firefox profile folder, respectivily:
      C:\Users\[YOURUSERNAME]\AppData\Roaming\Mozilla\Firefox\Profiles\[YOURPROFILE]\ybookmarks.sqlite
      C:\Users\[YOURUSERNAME]\AppData\Roaming\Mozilla\Firefox\Profiles\[YOURPROFILE]\places.sqlite
    • One copy will serve as a sandbox / a working copy to release our in between sql commands on. And we wíll make mistakes, Murphy!
      The other one will serve as backup if things go wrong further on the road. Best to make these even read-only to protect mistakes in which is which.
    • If you don't have firefox bookmarks that you want to save, it's even betere to create a new profile and grab a fresh database from there.
  2. Download and install an sqlite client.
    Here are some and I can strongly recommend Navicat if you ever wish to manage databases again.
  3. Open both working copies above in your database client. You should be able to see and open some tables.
  4. Read the comments in the SQL file below very carefully and perform the SQL queries (=commands) accordingly!
  5. Close Firefox.
  6. Copy your new places.sqlite over the existing one.
  7. Restart Firefox and check your bookmarks and tags.
  8. Since there are some things that Firefox needs (for syncing and searching fast), it's adviced to then backup all your bookmarks (in json format), exit firefox, delete all the places.sql related files in your profile, restart firefox (which creates a blank one) and then restoring them.

SQL Migration file

Warning: Do not just execute this file!  Read the comments and do every query one by one.

  1. /* ------
  2. TRANSFER TABLES TO WORKING DATABASE (=delicious)
  3. ------
  4. Make hardcopy of both the firefox sql database (places.sqlite) and delicious database (ybookmarks.sqlite) in there.
  5. Copy the following firefox tables inside the delicious database: moz_items_annos, moz_places, moz_bookmarks
  6. Why?
  7. 1. We do not know how to do queries on different databases :-/
  8. 2. We don't want to lose the ROWID relation that delicious uses to link tables together.
  9.  
  10. Note: moz_places transfer:
  11. If you have an error like: -- near "." -- you can temp empty rev_host column in the FF database, before transferring
  12. UPDATE "main"."moz_places" SET "rev_host"=NULL WHERE "url" LIKE 'file%'
  13.  
  14. /* ------
  15. BOOKMARKS
  16. ------
  17. /* ------ Make temporary table for places ------ */
  18. CREATETABLE"DBMxFPL"(
  19. "DBM_id"INTEGERNOTNULL,
  20. "FPL_id"INTEGERNOTNULL,
  21. PRIMARY KEY("DBM_id","FPL_id")
  22. );
  23.  
  24. /* ------ Insert non-existing places in FF places from Del Bookmarks ------ */
  25. INSERTINTO moz_places ( url, title, visit_count, last_visit_date)
  26. SELECT url, name, visit_count, last_visited FROM
  27. (
  28. SELECT DBM.url, DBM.name, DBM.visit_count, DBM.last_visited, DBM.ROWID as RID
  29. FROM bookmarks DBM
  30. LEFTJOIN moz_places FPL ON FPL.url=DBM.url
  31. WHERE FPL.url isnull
  32. ORDER BY DBM.added_date
  33. );
  34.  
  35. /* ------ Insert delicious bookmarks in temporary id-link table ------ */
  36. INSERTINTO DBMxFPL
  37. SELECT DBM.ROWID, FPL.id
  38. FROM bookmarks AS DBM
  39. INNERJOIN moz_places AS FPL ON FPL.url=DBM.url
  40. ORDER BY FPL.id
  41.  
  42. /* ------ Double check temporary table if you want ------ */
  43. SELECT DBM.url, FPL.url, DBM.url = FPL.url
  44. FROM DBMxFPL
  45. LEFTJOIN bookmarks AS DBM ON DBM.ROWID=DBMxFPL.DBM_id
  46. LEFTJOIN moz_places AS FPL ON FPL.id=DBMxFPL.FPL_id;
  47.  
  48. /* ------ Add bookmarks
  49. the 5 below says under which folder they will appear in firefox; You might first want to check this in moz_bookmarks;
  50. ------ */
  51. INSERTINTO moz_bookmarks (type, parent, fk,position, title, lastModified, dateAdded)
  52. SELECT1,5, DBMxFPL.FPL_id, DBM.ROWID AS DBMid, DBM.name, DBM.last_modified, DBM.added_date
  53. FROM bookmarks DBM
  54. LEFTJOIN DBMxFPL ON DBMxFPL.DBM_id = DBMid
  55.  
  56. /* ------
  57. TAGS
  58. ------
  59. /* ------ Check the maximum tag position in firefox ------ */
  60. SELECT*
  61. FROM moz_bookmarks
  62. WHERE"type"='2'AND parent ='4'
  63. ORDER BYpositionDESC
  64.  
  65. /* ------ Add tags. We make "position" here from the tag's ROWID.
  66. Use the maximum from above, minus one!, nextto "+" below (0 if no tags)
  67. ------ */
  68. INSERTINTO moz_bookmarks (type, parent,position, title)
  69. SELECT2,4, DTid, name FROM
  70. (
  71. SELECT t.ROWID +5AS DTid, t.name
  72. FROM tags AS t
  73. ORDER BY t.ROWID
  74. )
  75.  
  76. /* ------ Before doing the complex stuff you might want to check the query below if the url, name and tags seem OK, as a precaution
  77. ------ */
  78. SELECT FPL.id, FPL.url, DBM.name, DT.name
  79. FROM bookmarks DBM
  80. LEFTJOIN moz_places FPL ON FPL.url=DBM.url /* (to get moz_places id) */
  81. LEFTJOIN bookmarks_tags DBT ON DBM.ROWID = DBT.bookmark_id /* THE delicious tag relation*/
  82. LEFTJOIN tags DT ON DT.ROWID = DBT.tag_id /* to link the tags below */
  83. LEFTJOIN moz_bookmarks FBM ON FBM.type=2and FBM.parent=4AND FBM.title=DT.name
  84. ORDER BY FPL.id, DT.name
  85.  
  86.  
  87. /* ------ Link tags = Put tag cross links inside bookmarks (FK=FPL_id & parent = FF bookmarks tag id)
  88. WARNING: DUNNO HOW TO MAKE A POSITION HERE, LET'S TRY WITHOUT POSITION AND SEE HOW FIREFOX LIKES IT
  89. ------ */
  90.  
  91. INSERTINTO moz_bookmarks (type, fk, parent)
  92. SELECT1, FPL_id, FBM_id FROM
  93. (
  94. SELECT FPL.id AS FPL_id, FBM.id AS FBM_id
  95. FROM bookmarks DBM
  96. LEFTJOIN moz_places FPL ON FPL.url=DBM.url /* (to get moz_places id) */
  97. LEFTJOIN bookmarks_tags DBT ON DBM.ROWID = DBT.bookmark_id /* THE delicious tag relation*/
  98. LEFTJOIN tags DT ON DT.ROWID = DBT.tag_id /* to link the tags below */
  99. LEFTJOIN moz_bookmarks FBM ON FBM.type=2and FBM.parent=4AND FBM.title=DT.name
  100.  
  101. ORDER BY FPL.id, DT.name
  102. )
  103.  
  104. /* ------ Let's double check if FF tags data is now all right
  105. ------ */
  106. SELECT FBM.title, FPL.url, FBMT.title
  107. FROM moz_bookmarks AS FBM
  108. INNERJOIN moz_places AS FPL ON FBM.fk = FPL.id
  109. LEFTJOIN moz_bookmarks AS FBMT ON FBMT.type=2and FBMT.parent=4AND FBM.parent=FBMT.id
  110. ORDER BY FPL.id, FBMT.title
  111.  
  112.  
  113. /* ------
  114. DESCRIPTIONS
  115. ------
  116. Search in moz_anno_attributes for the id of "bookmarkProperties/description". For me this is 1. We'll use it below.
  117. Before doing the insert, one might want to check the sub-select first with these extra fields:BM.name, DBM.url, FPL.url
  118. ------ */
  119. INSERTINTO moz_items_annos (item_id, anno_attribute_id, content, flags, expiration,type)
  120. SELECT FBM.id,1, DBM.description,0,4,3
  121. FROM bookmarks DBM
  122. LEFTJOIN moz_places FPL ON DBM.url = FPL.url
  123. LEFTJOIN moz_bookmarks FBM ON FBM.fk = FPL.id
  124. WHERENOT FBM.title ISNULLANDNOT FPL.id ISNULL
  125.  
  126. /* ------
  127. TRANSFER TABLES BACK TO DATABASE (=FIREFOX WORKING DB)
  128. ------ Now copy moz_items_annos, moz_bookmarks and moz_places back to your firefox database
  129. and copy that database over your current profile (after closing firefox)
  130.  
  131. Note: moz_places transfer:
  132. If you had an error like: -- near "." at the start of this file you need to return our earlier workaround in the FF database
  133. UPDATE "main"."moz_places" SET "rev_host"="." WHERE "url" LIKE 'file%'
  134. ------ */
  135.  
  136. /* NOTES:
  137. /* Will some things be filled in autom. after revisist? Favicons? YES. rev_host? YES. guid? NO? maybe after sync? YES!
  138. /* Not included: visit_count for existing moz_places
  139. /* Not included: livemark in deli bookmarks, nog niet behandeld
  140. /* Not included: tags, description lastmodified and lastAdded! (Probably doesn't exist in delicious and does not have meaning */

Some extra scripts

See all tags from Firefox Bookmark
  1. SELECT * FROM "moz_bookmarks" AS B
  2. LEFT JOIN moz_places AS P ON P.id=B.fk
  3. WHERE fk=9;
See all Firefox Bookmark with certain tags
  1. SELECT * FROM "moz_bookmarks" AS B
  2. LEFT JOIN moz_places AS P ON P.id=B.fk
  3. WHERE fk=9;