mobile/android/base/db/BrowserDatabaseHelper.java@fc2d59ddac77
mobile/android/base/db/BrowserDatabaseHelper.java
Wed, 31 Dec 2014 07:22:50 +0100
- author
- Michael Schloh von Bennewitz <michael@schloh.com>
- date
- Wed, 31 Dec 2014 07:22:50 +0100
- branch
- TOR_BUG_3246
- changeset 4
- fc2d59ddac77
- permissions
- -rw-r--r--
Correct previous dual key logic pending first delivery installment.
1 /* -*- Mode: Java; c-basic-offset: 4; tab-width: 20; indent-tabs-mode: nil; -*- */
2 /* This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this file,
4 * You can obtain one at http://mozilla.org/MPL/2.0/. */
6 package org.mozilla.gecko.db;
8 import java.io.ByteArrayOutputStream;
9 import java.io.File;
10 import java.lang.reflect.Field;
11 import java.util.ArrayList;
12 import java.util.List;
13 import java.util.Locale;
14 import java.util.regex.Matcher;
15 import java.util.regex.Pattern;
17 import org.json.JSONArray;
18 import org.json.JSONException;
19 import org.json.JSONObject;
20 import org.mozilla.gecko.AppConstants;
21 import org.mozilla.gecko.Distribution;
22 import org.mozilla.gecko.R;
23 import org.mozilla.gecko.db.BrowserContract.Bookmarks;
24 import org.mozilla.gecko.db.BrowserContract.Combined;
25 import org.mozilla.gecko.db.BrowserContract.FaviconColumns;
26 import org.mozilla.gecko.db.BrowserContract.Favicons;
27 import org.mozilla.gecko.db.BrowserContract.History;
28 import org.mozilla.gecko.db.BrowserContract.Obsolete;
29 import org.mozilla.gecko.db.BrowserContract.ReadingListItems;
30 import org.mozilla.gecko.db.BrowserContract.Thumbnails;
31 import org.mozilla.gecko.gfx.BitmapUtils;
32 import org.mozilla.gecko.sync.Utils;
33 import org.mozilla.gecko.util.GeckoJarReader;
34 import org.mozilla.gecko.util.ThreadUtils;
36 import android.content.ContentValues;
37 import android.content.Context;
38 import android.database.Cursor;
39 import android.database.DatabaseUtils;
40 import android.database.SQLException;
41 import android.database.sqlite.SQLiteDatabase;
42 import android.database.sqlite.SQLiteOpenHelper;
43 import android.graphics.Bitmap;
44 import android.net.Uri;
45 import android.os.Build;
46 import android.text.TextUtils;
47 import android.util.Log;
50 final class BrowserDatabaseHelper extends SQLiteOpenHelper {
52 private static final String LOGTAG = "GeckoBrowserDBHelper";
53 public static final int DATABASE_VERSION = 18;
54 public static final String DATABASE_NAME = "browser.db";
56 final protected Context mContext;
58 static final String TABLE_BOOKMARKS = Bookmarks.TABLE_NAME;
59 static final String TABLE_HISTORY = History.TABLE_NAME;
60 static final String TABLE_FAVICONS = Favicons.TABLE_NAME;
61 static final String TABLE_THUMBNAILS = Thumbnails.TABLE_NAME;
62 static final String TABLE_READING_LIST = ReadingListItems.TABLE_NAME;
64 static final String VIEW_COMBINED = Combined.VIEW_NAME;
65 static final String VIEW_BOOKMARKS_WITH_FAVICONS = Bookmarks.VIEW_WITH_FAVICONS;
66 static final String VIEW_HISTORY_WITH_FAVICONS = History.VIEW_WITH_FAVICONS;
67 static final String VIEW_COMBINED_WITH_FAVICONS = Combined.VIEW_WITH_FAVICONS;
69 static final String TABLE_BOOKMARKS_JOIN_FAVICONS = TABLE_BOOKMARKS + " LEFT OUTER JOIN " +
70 TABLE_FAVICONS + " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " = " +
71 qualifyColumn(TABLE_FAVICONS, Favicons._ID);
73 static final String TABLE_HISTORY_JOIN_FAVICONS = TABLE_HISTORY + " LEFT OUTER JOIN " +
74 TABLE_FAVICONS + " ON " + qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " = " +
75 qualifyColumn(TABLE_FAVICONS, Favicons._ID);
77 static final String TABLE_BOOKMARKS_TMP = TABLE_BOOKMARKS + "_tmp";
78 static final String TABLE_HISTORY_TMP = TABLE_HISTORY + "_tmp";
79 static final String TABLE_IMAGES_TMP = Obsolete.TABLE_IMAGES + "_tmp";
81 private static final String[] mobileIdColumns = new String[] { Bookmarks._ID };
82 private static final String[] mobileIdSelectionArgs = new String[] { Bookmarks.MOBILE_FOLDER_GUID };
84 public BrowserDatabaseHelper(Context context, String databasePath) {
85 super(context, databasePath, null, DATABASE_VERSION);
86 mContext = context;
87 }
89 private void createBookmarksTable(SQLiteDatabase db) {
90 debug("Creating " + TABLE_BOOKMARKS + " table");
92 // Android versions older than Froyo ship with an sqlite
93 // that doesn't support foreign keys.
94 String foreignKeyOnParent = null;
95 if (Build.VERSION.SDK_INT >= 8) {
96 foreignKeyOnParent = ", FOREIGN KEY (" + Bookmarks.PARENT +
97 ") REFERENCES " + TABLE_BOOKMARKS + "(" + Bookmarks._ID + ")";
98 }
100 db.execSQL("CREATE TABLE " + TABLE_BOOKMARKS + "(" +
101 Bookmarks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
102 Bookmarks.TITLE + " TEXT," +
103 Bookmarks.URL + " TEXT," +
104 Bookmarks.TYPE + " INTEGER NOT NULL DEFAULT " + Bookmarks.TYPE_BOOKMARK + "," +
105 Bookmarks.PARENT + " INTEGER," +
106 Bookmarks.POSITION + " INTEGER NOT NULL," +
107 Bookmarks.KEYWORD + " TEXT," +
108 Bookmarks.DESCRIPTION + " TEXT," +
109 Bookmarks.TAGS + " TEXT," +
110 Bookmarks.DATE_CREATED + " INTEGER," +
111 Bookmarks.DATE_MODIFIED + " INTEGER," +
112 Bookmarks.GUID + " TEXT NOT NULL," +
113 Bookmarks.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
114 (foreignKeyOnParent != null ? foreignKeyOnParent : "") +
115 ");");
117 db.execSQL("CREATE INDEX bookmarks_url_index ON " + TABLE_BOOKMARKS + "("
118 + Bookmarks.URL + ")");
119 db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "("
120 + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")");
121 db.execSQL("CREATE UNIQUE INDEX bookmarks_guid_index ON " + TABLE_BOOKMARKS + "("
122 + Bookmarks.GUID + ")");
123 db.execSQL("CREATE INDEX bookmarks_modified_index ON " + TABLE_BOOKMARKS + "("
124 + Bookmarks.DATE_MODIFIED + ")");
125 }
127 private void createBookmarksTableOn13(SQLiteDatabase db) {
128 debug("Creating " + TABLE_BOOKMARKS + " table");
130 // Android versions older than Froyo ship with an sqlite
131 // that doesn't support foreign keys.
132 String foreignKeyOnParent = null;
133 if (Build.VERSION.SDK_INT >= 8) {
134 foreignKeyOnParent = ", FOREIGN KEY (" + Bookmarks.PARENT +
135 ") REFERENCES " + TABLE_BOOKMARKS + "(" + Bookmarks._ID + ")";
136 }
138 db.execSQL("CREATE TABLE " + TABLE_BOOKMARKS + "(" +
139 Bookmarks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
140 Bookmarks.TITLE + " TEXT," +
141 Bookmarks.URL + " TEXT," +
142 Bookmarks.TYPE + " INTEGER NOT NULL DEFAULT " + Bookmarks.TYPE_BOOKMARK + "," +
143 Bookmarks.PARENT + " INTEGER," +
144 Bookmarks.POSITION + " INTEGER NOT NULL," +
145 Bookmarks.KEYWORD + " TEXT," +
146 Bookmarks.DESCRIPTION + " TEXT," +
147 Bookmarks.TAGS + " TEXT," +
148 Bookmarks.FAVICON_ID + " INTEGER," +
149 Bookmarks.DATE_CREATED + " INTEGER," +
150 Bookmarks.DATE_MODIFIED + " INTEGER," +
151 Bookmarks.GUID + " TEXT NOT NULL," +
152 Bookmarks.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
153 (foreignKeyOnParent != null ? foreignKeyOnParent : "") +
154 ");");
156 db.execSQL("CREATE INDEX bookmarks_url_index ON " + TABLE_BOOKMARKS + "("
157 + Bookmarks.URL + ")");
158 db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "("
159 + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")");
160 db.execSQL("CREATE UNIQUE INDEX bookmarks_guid_index ON " + TABLE_BOOKMARKS + "("
161 + Bookmarks.GUID + ")");
162 db.execSQL("CREATE INDEX bookmarks_modified_index ON " + TABLE_BOOKMARKS + "("
163 + Bookmarks.DATE_MODIFIED + ")");
164 }
166 private void createHistoryTable(SQLiteDatabase db) {
167 debug("Creating " + TABLE_HISTORY + " table");
168 db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" +
169 History._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
170 History.TITLE + " TEXT," +
171 History.URL + " TEXT NOT NULL," +
172 History.VISITS + " INTEGER NOT NULL DEFAULT 0," +
173 History.DATE_LAST_VISITED + " INTEGER," +
174 History.DATE_CREATED + " INTEGER," +
175 History.DATE_MODIFIED + " INTEGER," +
176 History.GUID + " TEXT NOT NULL," +
177 History.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
178 ");");
180 db.execSQL("CREATE INDEX history_url_index ON " + TABLE_HISTORY + "("
181 + History.URL + ")");
182 db.execSQL("CREATE UNIQUE INDEX history_guid_index ON " + TABLE_HISTORY + "("
183 + History.GUID + ")");
184 db.execSQL("CREATE INDEX history_modified_index ON " + TABLE_HISTORY + "("
185 + History.DATE_MODIFIED + ")");
186 db.execSQL("CREATE INDEX history_visited_index ON " + TABLE_HISTORY + "("
187 + History.DATE_LAST_VISITED + ")");
188 }
190 private void createHistoryTableOn13(SQLiteDatabase db) {
191 debug("Creating " + TABLE_HISTORY + " table");
192 db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" +
193 History._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
194 History.TITLE + " TEXT," +
195 History.URL + " TEXT NOT NULL," +
196 History.VISITS + " INTEGER NOT NULL DEFAULT 0," +
197 History.FAVICON_ID + " INTEGER," +
198 History.DATE_LAST_VISITED + " INTEGER," +
199 History.DATE_CREATED + " INTEGER," +
200 History.DATE_MODIFIED + " INTEGER," +
201 History.GUID + " TEXT NOT NULL," +
202 History.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
203 ");");
205 db.execSQL("CREATE INDEX history_url_index ON " + TABLE_HISTORY + "("
206 + History.URL + ")");
207 db.execSQL("CREATE UNIQUE INDEX history_guid_index ON " + TABLE_HISTORY + "("
208 + History.GUID + ")");
209 db.execSQL("CREATE INDEX history_modified_index ON " + TABLE_HISTORY + "("
210 + History.DATE_MODIFIED + ")");
211 db.execSQL("CREATE INDEX history_visited_index ON " + TABLE_HISTORY + "("
212 + History.DATE_LAST_VISITED + ")");
213 }
215 private void createImagesTable(SQLiteDatabase db) {
216 debug("Creating " + Obsolete.TABLE_IMAGES + " table");
217 db.execSQL("CREATE TABLE " + Obsolete.TABLE_IMAGES + " (" +
218 Obsolete.Images._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
219 Obsolete.Images.URL + " TEXT UNIQUE NOT NULL," +
220 Obsolete.Images.FAVICON + " BLOB," +
221 Obsolete.Images.FAVICON_URL + " TEXT," +
222 Obsolete.Images.THUMBNAIL + " BLOB," +
223 Obsolete.Images.DATE_CREATED + " INTEGER," +
224 Obsolete.Images.DATE_MODIFIED + " INTEGER," +
225 Obsolete.Images.GUID + " TEXT NOT NULL," +
226 Obsolete.Images.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" +
227 ");");
229 db.execSQL("CREATE INDEX images_url_index ON " + Obsolete.TABLE_IMAGES + "("
230 + Obsolete.Images.URL + ")");
231 db.execSQL("CREATE UNIQUE INDEX images_guid_index ON " + Obsolete.TABLE_IMAGES + "("
232 + Obsolete.Images.GUID + ")");
233 db.execSQL("CREATE INDEX images_modified_index ON " + Obsolete.TABLE_IMAGES + "("
234 + Obsolete.Images.DATE_MODIFIED + ")");
235 }
237 private void createFaviconsTable(SQLiteDatabase db) {
238 debug("Creating " + TABLE_FAVICONS + " table");
239 db.execSQL("CREATE TABLE " + TABLE_FAVICONS + " (" +
240 Favicons._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
241 Favicons.URL + " TEXT UNIQUE," +
242 Favicons.DATA + " BLOB," +
243 Favicons.DATE_CREATED + " INTEGER," +
244 Favicons.DATE_MODIFIED + " INTEGER" +
245 ");");
247 db.execSQL("CREATE INDEX favicons_url_index ON " + TABLE_FAVICONS + "("
248 + Favicons.URL + ")");
249 db.execSQL("CREATE INDEX favicons_modified_index ON " + TABLE_FAVICONS + "("
250 + Favicons.DATE_MODIFIED + ")");
251 }
253 private void createThumbnailsTable(SQLiteDatabase db) {
254 debug("Creating " + TABLE_THUMBNAILS + " table");
255 db.execSQL("CREATE TABLE " + TABLE_THUMBNAILS + " (" +
256 Thumbnails._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
257 Thumbnails.URL + " TEXT UNIQUE," +
258 Thumbnails.DATA + " BLOB" +
259 ");");
261 db.execSQL("CREATE INDEX thumbnails_url_index ON " + TABLE_THUMBNAILS + "("
262 + Thumbnails.URL + ")");
263 }
265 private void createBookmarksWithImagesView(SQLiteDatabase db) {
266 debug("Creating " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES + " view");
268 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES + " AS " +
269 "SELECT " + qualifyColumn(TABLE_BOOKMARKS, "*") +
270 ", " + Obsolete.Images.FAVICON + ", " + Obsolete.Images.THUMBNAIL + " FROM " +
271 Obsolete.TABLE_BOOKMARKS_JOIN_IMAGES);
272 }
274 private void createBookmarksWithFaviconsView(SQLiteDatabase db) {
275 debug("Creating " + VIEW_BOOKMARKS_WITH_FAVICONS + " view");
277 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_BOOKMARKS_WITH_FAVICONS + " AS " +
278 "SELECT " + qualifyColumn(TABLE_BOOKMARKS, "*") +
279 ", " + qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Bookmarks.FAVICON +
280 ", " + qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Bookmarks.FAVICON_URL +
281 " FROM " + TABLE_BOOKMARKS_JOIN_FAVICONS);
282 }
284 private void createHistoryWithImagesView(SQLiteDatabase db) {
285 debug("Creating " + Obsolete.VIEW_HISTORY_WITH_IMAGES + " view");
287 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES + " AS " +
288 "SELECT " + qualifyColumn(TABLE_HISTORY, "*") +
289 ", " + Obsolete.Images.FAVICON + ", " + Obsolete.Images.THUMBNAIL + " FROM " +
290 Obsolete.TABLE_HISTORY_JOIN_IMAGES);
291 }
293 private void createHistoryWithFaviconsView(SQLiteDatabase db) {
294 debug("Creating " + VIEW_HISTORY_WITH_FAVICONS + " view");
296 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_HISTORY_WITH_FAVICONS + " AS " +
297 "SELECT " + qualifyColumn(TABLE_HISTORY, "*") +
298 ", " + qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + History.FAVICON +
299 ", " + qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + History.FAVICON_URL +
300 " FROM " + TABLE_HISTORY_JOIN_FAVICONS);
301 }
303 private void createCombinedWithImagesView(SQLiteDatabase db) {
304 debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view");
306 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" +
307 " SELECT " + Combined.BOOKMARK_ID + ", " +
308 Combined.HISTORY_ID + ", " +
309 // We need to return an _id column because CursorAdapter requires it for its
310 // default implementation for the getItemId() method. However, since
311 // we're not using this feature in the parts of the UI using this view,
312 // we can just use 0 for all rows.
313 "0 AS " + Combined._ID + ", " +
314 Combined.URL + ", " +
315 Combined.TITLE + ", " +
316 Combined.VISITS + ", " +
317 Combined.DATE_LAST_VISITED + ", " +
318 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
319 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL +
320 " FROM (" +
321 // Bookmarks without history.
322 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
323 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
324 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
325 "-1 AS " + Combined.HISTORY_ID + ", " +
326 "-1 AS " + Combined.VISITS + ", " +
327 "-1 AS " + Combined.DATE_LAST_VISITED +
328 " FROM " + TABLE_BOOKMARKS +
329 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
330 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
331 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
332 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
333 " UNION ALL" +
334 // History with and without bookmark.
335 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
336 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
337 // Prioritze bookmark titles over history titles, since the user may have
338 // customized the title for a bookmark.
339 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
340 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
341 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
342 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
343 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
344 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
345 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
346 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
347 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
348 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
349 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" +
350 ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES +
351 " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL));
352 }
354 private void createCombinedWithImagesViewOn9(SQLiteDatabase db) {
355 debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view");
357 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" +
358 " SELECT " + Combined.BOOKMARK_ID + ", " +
359 Combined.HISTORY_ID + ", " +
360 // We need to return an _id column because CursorAdapter requires it for its
361 // default implementation for the getItemId() method. However, since
362 // we're not using this feature in the parts of the UI using this view,
363 // we can just use 0 for all rows.
364 "0 AS " + Combined._ID + ", " +
365 Combined.URL + ", " +
366 Combined.TITLE + ", " +
367 Combined.VISITS + ", " +
368 Combined.DISPLAY + ", " +
369 Combined.DATE_LAST_VISITED + ", " +
370 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
371 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL +
372 " FROM (" +
373 // Bookmarks without history.
374 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
375 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
376 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
377 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
378 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
379 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
380 "-1 AS " + Combined.HISTORY_ID + ", " +
381 "-1 AS " + Combined.VISITS + ", " +
382 "-1 AS " + Combined.DATE_LAST_VISITED +
383 " FROM " + TABLE_BOOKMARKS +
384 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
385 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
386 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
387 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
388 " UNION ALL" +
389 // History with and without bookmark.
390 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
391 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
392 // Prioritze bookmark titles over history titles, since the user may have
393 // customized the title for a bookmark.
394 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
395 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
396 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
397 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
398 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
399 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
400 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
401 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
402 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
403 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
404 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
405 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
406 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
407 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" +
408 ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES +
409 " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL));
410 }
412 private void createCombinedWithImagesViewOn10(SQLiteDatabase db) {
413 debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view");
415 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" +
416 " SELECT " + Combined.BOOKMARK_ID + ", " +
417 Combined.HISTORY_ID + ", " +
418 // We need to return an _id column because CursorAdapter requires it for its
419 // default implementation for the getItemId() method. However, since
420 // we're not using this feature in the parts of the UI using this view,
421 // we can just use 0 for all rows.
422 "0 AS " + Combined._ID + ", " +
423 Combined.URL + ", " +
424 Combined.TITLE + ", " +
425 Combined.VISITS + ", " +
426 Combined.DISPLAY + ", " +
427 Combined.DATE_LAST_VISITED + ", " +
428 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
429 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL +
430 " FROM (" +
431 // Bookmarks without history.
432 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
433 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
434 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
435 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
436 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
437 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
438 "-1 AS " + Combined.HISTORY_ID + ", " +
439 "-1 AS " + Combined.VISITS + ", " +
440 "-1 AS " + Combined.DATE_LAST_VISITED +
441 " FROM " + TABLE_BOOKMARKS +
442 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
443 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
444 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
445 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
446 " UNION ALL" +
447 // History with and without bookmark.
448 " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " +
449 qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " +
450 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
451 // Prioritze bookmark titles over history titles, since the user may have
452 // customized the title for a bookmark.
453 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
454 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
455 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
456 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
457 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
458 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
459 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
460 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
461 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
462 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
463 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
464 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
465 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
466 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" +
467 ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES +
468 " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL));
469 }
471 private void createCombinedWithImagesViewOn11(SQLiteDatabase db) {
472 debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view");
474 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" +
475 " SELECT " + Combined.BOOKMARK_ID + ", " +
476 Combined.HISTORY_ID + ", " +
477 // We need to return an _id column because CursorAdapter requires it for its
478 // default implementation for the getItemId() method. However, since
479 // we're not using this feature in the parts of the UI using this view,
480 // we can just use 0 for all rows.
481 "0 AS " + Combined._ID + ", " +
482 Combined.URL + ", " +
483 Combined.TITLE + ", " +
484 Combined.VISITS + ", " +
485 Combined.DISPLAY + ", " +
486 Combined.DATE_LAST_VISITED + ", " +
487 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
488 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL +
489 " FROM (" +
490 // Bookmarks without history.
491 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
492 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
493 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
494 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
495 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
496 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
497 "-1 AS " + Combined.HISTORY_ID + ", " +
498 "-1 AS " + Combined.VISITS + ", " +
499 "-1 AS " + Combined.DATE_LAST_VISITED +
500 " FROM " + TABLE_BOOKMARKS +
501 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
502 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
503 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
504 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
505 " UNION ALL" +
506 // History with and without bookmark.
507 " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " +
508 qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " +
509 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
510 // Prioritze bookmark titles over history titles, since the user may have
511 // customized the title for a bookmark.
512 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
513 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
514 // Only use DISPLAY_READER if the matching bookmark entry inside reading
515 // list folder is not marked as deleted.
516 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " +
517 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID +
518 " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " +
519 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
520 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
521 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
522 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
523 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
524 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
525 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
526 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
527 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
528 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " +
529 ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES +
530 " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL));
531 }
533 private void createCombinedViewOn12(SQLiteDatabase db) {
534 debug("Creating " + VIEW_COMBINED + " view");
536 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" +
537 " SELECT " + Combined.BOOKMARK_ID + ", " +
538 Combined.HISTORY_ID + ", " +
539 // We need to return an _id column because CursorAdapter requires it for its
540 // default implementation for the getItemId() method. However, since
541 // we're not using this feature in the parts of the UI using this view,
542 // we can just use 0 for all rows.
543 "0 AS " + Combined._ID + ", " +
544 Combined.URL + ", " +
545 Combined.TITLE + ", " +
546 Combined.VISITS + ", " +
547 Combined.DISPLAY + ", " +
548 Combined.DATE_LAST_VISITED +
549 " FROM (" +
550 // Bookmarks without history.
551 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
552 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
553 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
554 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
555 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
556 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
557 "-1 AS " + Combined.HISTORY_ID + ", " +
558 "-1 AS " + Combined.VISITS + ", " +
559 "-1 AS " + Combined.DATE_LAST_VISITED +
560 " FROM " + TABLE_BOOKMARKS +
561 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
562 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
563 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
564 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
565 " UNION ALL" +
566 // History with and without bookmark.
567 " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " +
568 qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " +
569 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
570 // Prioritze bookmark titles over history titles, since the user may have
571 // customized the title for a bookmark.
572 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
573 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
574 // Only use DISPLAY_READER if the matching bookmark entry inside reading
575 // list folder is not marked as deleted.
576 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " +
577 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID +
578 " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " +
579 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
580 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
581 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
582 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED +
583 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
584 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
585 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
586 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
587 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
588 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " +
589 ")");
591 debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view");
593 db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" +
594 " SELECT *, " +
595 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " +
596 qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL +
597 " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES +
598 " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL));
599 }
601 private void createCombinedViewOn13(SQLiteDatabase db) {
602 debug("Creating " + VIEW_COMBINED + " view");
604 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" +
605 " SELECT " + Combined.BOOKMARK_ID + ", " +
606 Combined.HISTORY_ID + ", " +
607 // We need to return an _id column because CursorAdapter requires it for its
608 // default implementation for the getItemId() method. However, since
609 // we're not using this feature in the parts of the UI using this view,
610 // we can just use 0 for all rows.
611 "0 AS " + Combined._ID + ", " +
612 Combined.URL + ", " +
613 Combined.TITLE + ", " +
614 Combined.VISITS + ", " +
615 Combined.DISPLAY + ", " +
616 Combined.DATE_LAST_VISITED + ", " +
617 Combined.FAVICON_ID +
618 " FROM (" +
619 // Bookmarks without history.
620 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
621 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
622 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
623 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
624 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
625 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
626 "-1 AS " + Combined.HISTORY_ID + ", " +
627 "-1 AS " + Combined.VISITS + ", " +
628 "-1 AS " + Combined.DATE_LAST_VISITED + ", " +
629 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " AS " + Combined.FAVICON_ID +
630 " FROM " + TABLE_BOOKMARKS +
631 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
632 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
633 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
634 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
635 " UNION ALL" +
636 // History with and without bookmark.
637 " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " +
638 qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " +
639 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
640 // Prioritize bookmark titles over history titles, since the user may have
641 // customized the title for a bookmark.
642 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
643 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
644 // Only use DISPLAY_READER if the matching bookmark entry inside reading
645 // list folder is not marked as deleted.
646 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " +
647 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID +
648 " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " +
649 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
650 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
651 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
652 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + ", " +
653 qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " AS " + Combined.FAVICON_ID +
654 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
655 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
656 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
657 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
658 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
659 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " +
660 ")");
662 debug("Creating " + VIEW_COMBINED_WITH_FAVICONS + " view");
664 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED_WITH_FAVICONS + " AS" +
665 " SELECT " + qualifyColumn(VIEW_COMBINED, "*") + ", " +
666 qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Combined.FAVICON_URL + ", " +
667 qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Combined.FAVICON +
668 " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + TABLE_FAVICONS +
669 " ON " + Combined.FAVICON_ID + " = " + qualifyColumn(TABLE_FAVICONS, Favicons._ID));
670 }
672 private void createCombinedViewOn16(SQLiteDatabase db) {
673 debug("Creating " + VIEW_COMBINED + " view");
675 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" +
676 " SELECT " + Combined.BOOKMARK_ID + ", " +
677 Combined.HISTORY_ID + ", " +
678 // We need to return an _id column because CursorAdapter requires it for its
679 // default implementation for the getItemId() method. However, since
680 // we're not using this feature in the parts of the UI using this view,
681 // we can just use 0 for all rows.
682 "0 AS " + Combined._ID + ", " +
683 Combined.URL + ", " +
684 Combined.TITLE + ", " +
685 Combined.VISITS + ", " +
686 Combined.DISPLAY + ", " +
687 Combined.DATE_LAST_VISITED + ", " +
688 Combined.FAVICON_ID +
689 " FROM (" +
690 // Bookmarks without history.
691 " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " +
692 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " +
693 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " +
694 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
695 Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " +
696 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
697 "-1 AS " + Combined.HISTORY_ID + ", " +
698 "-1 AS " + Combined.VISITS + ", " +
699 "-1 AS " + Combined.DATE_LAST_VISITED + ", " +
700 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " AS " + Combined.FAVICON_ID +
701 " FROM " + TABLE_BOOKMARKS +
702 " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " +
703 // Ignore pinned bookmarks.
704 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " <> " + Bookmarks.FIXED_PINNED_LIST_ID + " AND " +
705 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " +
706 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) +
707 " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" +
708 " UNION ALL" +
709 // History with and without bookmark.
710 " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " +
711 // Give pinned bookmarks a NULL ID so that they're not treated as bookmarks. We can't
712 // completely ignore them here because they're joined with history entries we care about.
713 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " +
714 Bookmarks.FIXED_PINNED_LIST_ID + " THEN NULL ELSE " +
715 qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " END " +
716 "ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " +
717 qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " +
718 // Prioritize bookmark titles over history titles, since the user may have
719 // customized the title for a bookmark.
720 "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " +
721 qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " +
722 // Only use DISPLAY_READER if the matching bookmark entry inside reading
723 // list folder is not marked as deleted.
724 "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " +
725 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID +
726 " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " +
727 Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " +
728 qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " +
729 qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " +
730 qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + ", " +
731 qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " AS " + Combined.FAVICON_ID +
732 " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS +
733 " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) +
734 " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " +
735 qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" +
736 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " +
737 qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " +
738 ")");
740 debug("Creating " + VIEW_COMBINED_WITH_FAVICONS + " view");
742 db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED_WITH_FAVICONS + " AS" +
743 " SELECT " + qualifyColumn(VIEW_COMBINED, "*") + ", " +
744 qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Combined.FAVICON_URL + ", " +
745 qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Combined.FAVICON +
746 " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + TABLE_FAVICONS +
747 " ON " + Combined.FAVICON_ID + " = " + qualifyColumn(TABLE_FAVICONS, Favicons._ID));
748 }
750 @Override
751 public void onCreate(SQLiteDatabase db) {
752 debug("Creating browser.db: " + db.getPath());
754 createBookmarksTableOn13(db);
755 createHistoryTableOn13(db);
756 createFaviconsTable(db);
757 createThumbnailsTable(db);
759 createBookmarksWithFaviconsView(db);
760 createHistoryWithFaviconsView(db);
761 createCombinedViewOn16(db);
763 createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID,
764 R.string.bookmarks_folder_places, 0);
766 createOrUpdateAllSpecialFolders(db);
768 // Create distribution bookmarks before our own default bookmarks
769 int pos = createDistributionBookmarks(db);
770 createDefaultBookmarks(db, pos);
772 createReadingListTable(db);
773 }
775 private String getLocalizedProperty(JSONObject bookmark, String property, Locale locale) throws JSONException {
776 // Try the full locale
777 String fullLocale = property + "." + locale.toString();
778 if (bookmark.has(fullLocale)) {
779 return bookmark.getString(fullLocale);
780 }
781 // Try without a variant
782 if (!TextUtils.isEmpty(locale.getVariant())) {
783 String noVariant = fullLocale.substring(0, fullLocale.lastIndexOf("_"));
784 if (bookmark.has(noVariant)) {
785 return bookmark.getString(noVariant);
786 }
787 }
788 // Try just the language
789 String lang = property + "." + locale.getLanguage();
790 if (bookmark.has(lang)) {
791 return bookmark.getString(lang);
792 }
793 // Default to the non-localized property name
794 return bookmark.getString(property);
795 }
797 // Returns the number of bookmarks inserted in the db
798 private int createDistributionBookmarks(SQLiteDatabase db) {
799 JSONArray bookmarks = Distribution.getBookmarks(mContext);
800 if (bookmarks == null) {
801 return 0;
802 }
804 Locale locale = Locale.getDefault();
805 int pos = 0;
806 Integer mobileFolderId = getMobileFolderId(db);
807 if (mobileFolderId == null) {
808 Log.e(LOGTAG, "Error creating distribution bookmarks: mobileFolderId is null");
809 return 0;
810 }
812 for (int i = 0; i < bookmarks.length(); i++) {
813 try {
814 final JSONObject bookmark = bookmarks.getJSONObject(i);
816 String title = getLocalizedProperty(bookmark, "title", locale);
817 final String url = getLocalizedProperty(bookmark, "url", locale);
818 createBookmark(db, title, url, pos, mobileFolderId);
820 if (bookmark.has("pinned")) {
821 try {
822 // Create a fake bookmark in the hidden pinned folder to pin bookmark
823 // to about:home top sites. Pass pos as the pinned position to pin
824 // sites in the order that bookmarks are specified in bookmarks.json.
825 if (bookmark.getBoolean("pinned")) {
826 createBookmark(db, title, url, pos, Bookmarks.FIXED_PINNED_LIST_ID);
827 }
828 } catch (JSONException e) {
829 Log.e(LOGTAG, "Error pinning bookmark to top sites", e);
830 }
831 }
833 pos++;
835 // return early if there is no icon for this bookmark
836 if (!bookmark.has("icon")) {
837 continue;
838 }
840 // create icons in a separate thread to avoid blocking about:home on startup
841 ThreadUtils.postToBackgroundThread(new Runnable() {
842 @Override
843 public void run() {
844 SQLiteDatabase db = getWritableDatabase();
845 try {
846 String iconData = bookmark.getString("icon");
847 Bitmap icon = BitmapUtils.getBitmapFromDataURI(iconData);
848 if (icon != null) {
849 createFavicon(db, url, icon);
850 }
851 } catch (JSONException e) {
852 Log.e(LOGTAG, "Error creating distribution bookmark icon", e);
853 }
854 }
855 });
856 } catch (JSONException e) {
857 Log.e(LOGTAG, "Error creating distribution bookmark", e);
858 }
859 }
860 return pos;
861 }
863 private void createReadingListTable(SQLiteDatabase db) {
864 debug("Creating " + TABLE_READING_LIST + " table");
866 db.execSQL("CREATE TABLE " + TABLE_READING_LIST + "(" +
867 ReadingListItems._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
868 ReadingListItems.URL + " TEXT NOT NULL, " +
869 ReadingListItems.TITLE + " TEXT, " +
870 ReadingListItems.EXCERPT + " TEXT, " +
871 ReadingListItems.READ + " TINYINT DEFAULT 0, " +
872 ReadingListItems.IS_DELETED + " TINYINT DEFAULT 0, " +
873 ReadingListItems.GUID + " TEXT UNIQUE NOT NULL, " +
874 ReadingListItems.DATE_MODIFIED + " INTEGER NOT NULL, " +
875 ReadingListItems.DATE_CREATED + " INTEGER NOT NULL, " +
876 ReadingListItems.LENGTH + " INTEGER DEFAULT 0 ); ");
878 db.execSQL("CREATE INDEX reading_list_url ON " + TABLE_READING_LIST + "("
879 + ReadingListItems.URL + ")");
880 db.execSQL("CREATE UNIQUE INDEX reading_list_guid ON " + TABLE_READING_LIST + "("
881 + ReadingListItems.GUID + ")");
882 }
884 // Inserts default bookmarks, starting at a specified position
885 private void createDefaultBookmarks(SQLiteDatabase db, int pos) {
886 Class<?> stringsClass = R.string.class;
887 Field[] fields = stringsClass.getFields();
888 Pattern p = Pattern.compile("^bookmarkdefaults_title_");
890 Integer mobileFolderId = getMobileFolderId(db);
891 if (mobileFolderId == null) {
892 Log.e(LOGTAG, "Error creating default bookmarks: mobileFolderId is null");
893 return;
894 }
896 for (int i = 0; i < fields.length; i++) {
897 final String name = fields[i].getName();
898 Matcher m = p.matcher(name);
899 if (!m.find()) {
900 continue;
901 }
902 try {
903 int titleid = fields[i].getInt(null);
904 String title = mContext.getString(titleid);
906 Field urlField = stringsClass.getField(name.replace("_title_", "_url_"));
907 int urlId = urlField.getInt(null);
908 final String url = mContext.getString(urlId);
909 createBookmark(db, title, url, pos, mobileFolderId);
911 // create icons in a separate thread to avoid blocking about:home on startup
912 ThreadUtils.postToBackgroundThread(new Runnable() {
913 @Override
914 public void run() {
915 SQLiteDatabase db = getWritableDatabase();
916 Bitmap icon = getDefaultFaviconFromPath(name);
917 if (icon == null) {
918 icon = getDefaultFaviconFromDrawable(name);
919 }
920 if (icon != null) {
921 createFavicon(db, url, icon);
922 }
923 }
924 });
925 pos++;
926 } catch (java.lang.IllegalAccessException ex) {
927 Log.e(LOGTAG, "Can't create bookmark " + name, ex);
928 } catch (java.lang.NoSuchFieldException ex) {
929 Log.e(LOGTAG, "Can't create bookmark " + name, ex);
930 }
931 }
932 }
934 private void createBookmark(SQLiteDatabase db, String title, String url, int pos, int parent) {
935 ContentValues bookmarkValues = new ContentValues();
936 bookmarkValues.put(Bookmarks.PARENT, parent);
938 long now = System.currentTimeMillis();
939 bookmarkValues.put(Bookmarks.DATE_CREATED, now);
940 bookmarkValues.put(Bookmarks.DATE_MODIFIED, now);
942 bookmarkValues.put(Bookmarks.TITLE, title);
943 bookmarkValues.put(Bookmarks.URL, url);
944 bookmarkValues.put(Bookmarks.GUID, Utils.generateGuid());
945 bookmarkValues.put(Bookmarks.POSITION, pos);
946 db.insertOrThrow(TABLE_BOOKMARKS, Bookmarks.TITLE, bookmarkValues);
947 }
949 private void createFavicon(SQLiteDatabase db, String url, Bitmap icon) {
950 ByteArrayOutputStream stream = new ByteArrayOutputStream();
952 ContentValues iconValues = new ContentValues();
953 iconValues.put(Favicons.PAGE_URL, url);
955 byte[] data = null;
956 if (icon.compress(Bitmap.CompressFormat.PNG, 100, stream)) {
957 data = stream.toByteArray();
958 } else {
959 Log.w(LOGTAG, "Favicon compression failed.");
960 }
961 iconValues.put(Favicons.DATA, data);
963 insertFavicon(db, iconValues);
964 }
966 private Bitmap getDefaultFaviconFromPath(String name) {
967 Class<?> stringClass = R.string.class;
968 try {
969 // Look for a drawable with the id R.drawable.bookmarkdefaults_favicon_*
970 Field faviconField = stringClass.getField(name.replace("_title_", "_favicon_"));
971 if (faviconField == null) {
972 return null;
973 }
974 int faviconId = faviconField.getInt(null);
975 String path = mContext.getString(faviconId);
977 String apkPath = mContext.getPackageResourcePath();
978 File apkFile = new File(apkPath);
979 String bitmapPath = "jar:jar:" + apkFile.toURI() + "!/" + AppConstants.OMNIJAR_NAME + "!/" + path;
980 return GeckoJarReader.getBitmap(mContext.getResources(), bitmapPath);
981 } catch (java.lang.IllegalAccessException ex) {
982 Log.e(LOGTAG, "[Path] Can't create favicon " + name, ex);
983 } catch (java.lang.NoSuchFieldException ex) {
984 // If the field does not exist, that means we intend to load via a drawable
985 }
986 return null;
987 }
989 private Bitmap getDefaultFaviconFromDrawable(String name) {
990 Class<?> drawablesClass = R.drawable.class;
991 try {
992 // Look for a drawable with the id R.drawable.bookmarkdefaults_favicon_*
993 Field faviconField = drawablesClass.getField(name.replace("_title_", "_favicon_"));
994 if (faviconField == null) {
995 return null;
996 }
997 int faviconId = faviconField.getInt(null);
998 return BitmapUtils.decodeResource(mContext, faviconId);
999 } catch (java.lang.IllegalAccessException ex) {
1000 Log.e(LOGTAG, "[Drawable] Can't create favicon " + name, ex);
1001 } catch (java.lang.NoSuchFieldException ex) {
1002 // If the field does not exist, that means we intend to load via a file path
1003 }
1004 return null;
1005 }
1007 private void createOrUpdateAllSpecialFolders(SQLiteDatabase db) {
1008 createOrUpdateSpecialFolder(db, Bookmarks.MOBILE_FOLDER_GUID,
1009 R.string.bookmarks_folder_mobile, 0);
1010 createOrUpdateSpecialFolder(db, Bookmarks.TOOLBAR_FOLDER_GUID,
1011 R.string.bookmarks_folder_toolbar, 1);
1012 createOrUpdateSpecialFolder(db, Bookmarks.MENU_FOLDER_GUID,
1013 R.string.bookmarks_folder_menu, 2);
1014 createOrUpdateSpecialFolder(db, Bookmarks.TAGS_FOLDER_GUID,
1015 R.string.bookmarks_folder_tags, 3);
1016 createOrUpdateSpecialFolder(db, Bookmarks.UNFILED_FOLDER_GUID,
1017 R.string.bookmarks_folder_unfiled, 4);
1018 createOrUpdateSpecialFolder(db, Bookmarks.READING_LIST_FOLDER_GUID,
1019 R.string.bookmarks_folder_reading_list, 5);
1020 createOrUpdateSpecialFolder(db, Bookmarks.PINNED_FOLDER_GUID,
1021 R.string.bookmarks_folder_pinned, 6);
1022 }
1024 private void createOrUpdateSpecialFolder(SQLiteDatabase db,
1025 String guid, int titleId, int position) {
1026 ContentValues values = new ContentValues();
1027 values.put(Bookmarks.GUID, guid);
1028 values.put(Bookmarks.TYPE, Bookmarks.TYPE_FOLDER);
1029 values.put(Bookmarks.POSITION, position);
1031 if (guid.equals(Bookmarks.PLACES_FOLDER_GUID))
1032 values.put(Bookmarks._ID, Bookmarks.FIXED_ROOT_ID);
1033 else if (guid.equals(Bookmarks.READING_LIST_FOLDER_GUID))
1034 values.put(Bookmarks._ID, Bookmarks.FIXED_READING_LIST_ID);
1035 else if (guid.equals(Bookmarks.PINNED_FOLDER_GUID))
1036 values.put(Bookmarks._ID, Bookmarks.FIXED_PINNED_LIST_ID);
1038 // Set the parent to 0, which sync assumes is the root
1039 values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID);
1041 String title = mContext.getResources().getString(titleId);
1042 values.put(Bookmarks.TITLE, title);
1044 long now = System.currentTimeMillis();
1045 values.put(Bookmarks.DATE_CREATED, now);
1046 values.put(Bookmarks.DATE_MODIFIED, now);
1048 int updated = db.update(TABLE_BOOKMARKS, values,
1049 Bookmarks.GUID + " = ?",
1050 new String[] { guid });
1052 if (updated == 0) {
1053 db.insert(TABLE_BOOKMARKS, Bookmarks.GUID, values);
1054 debug("Inserted special folder: " + guid);
1055 } else {
1056 debug("Updated special folder: " + guid);
1057 }
1058 }
1060 private boolean isSpecialFolder(ContentValues values) {
1061 String guid = values.getAsString(Bookmarks.GUID);
1062 if (guid == null)
1063 return false;
1065 return guid.equals(Bookmarks.MOBILE_FOLDER_GUID) ||
1066 guid.equals(Bookmarks.MENU_FOLDER_GUID) ||
1067 guid.equals(Bookmarks.TOOLBAR_FOLDER_GUID) ||
1068 guid.equals(Bookmarks.UNFILED_FOLDER_GUID) ||
1069 guid.equals(Bookmarks.TAGS_FOLDER_GUID);
1070 }
1072 private void migrateBookmarkFolder(SQLiteDatabase db, int folderId,
1073 BookmarkMigrator migrator) {
1074 Cursor c = null;
1076 debug("Migrating bookmark folder with id = " + folderId);
1078 String selection = Bookmarks.PARENT + " = " + folderId;
1079 String[] selectionArgs = null;
1081 boolean isRootFolder = (folderId == Bookmarks.FIXED_ROOT_ID);
1083 // If we're loading the root folder, we have to account for
1084 // any previously created special folder that was created without
1085 // setting a parent id (e.g. mobile folder) and making sure we're
1086 // not adding any infinite recursion as root's parent is root itself.
1087 if (isRootFolder) {
1088 selection = Bookmarks.GUID + " != ?" + " AND (" +
1089 selection + " OR " + Bookmarks.PARENT + " = NULL)";
1090 selectionArgs = new String[] { Bookmarks.PLACES_FOLDER_GUID };
1091 }
1093 List<Integer> subFolders = new ArrayList<Integer>();
1094 List<ContentValues> invalidSpecialEntries = new ArrayList<ContentValues>();
1096 try {
1097 c = db.query(TABLE_BOOKMARKS_TMP,
1098 null,
1099 selection,
1100 selectionArgs,
1101 null, null, null);
1103 // The key point here is that bookmarks should be added in
1104 // parent order to avoid any problems with the foreign key
1105 // in Bookmarks.PARENT.
1106 while (c.moveToNext()) {
1107 ContentValues values = new ContentValues();
1109 // We're using a null projection in the query which
1110 // means we're getting all columns from the table.
1111 // It's safe to simply transform the row into the
1112 // values to be inserted on the new table.
1113 DatabaseUtils.cursorRowToContentValues(c, values);
1115 boolean isSpecialFolder = isSpecialFolder(values);
1117 // The mobile folder used to be created with PARENT = NULL.
1118 // We want fix that here.
1119 if (values.getAsLong(Bookmarks.PARENT) == null && isSpecialFolder)
1120 values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID);
1122 if (isRootFolder && !isSpecialFolder) {
1123 invalidSpecialEntries.add(values);
1124 continue;
1125 }
1127 if (migrator != null)
1128 migrator.updateForNewTable(values);
1130 debug("Migrating bookmark: " + values.getAsString(Bookmarks.TITLE));
1131 db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values);
1133 Integer type = values.getAsInteger(Bookmarks.TYPE);
1134 if (type != null && type == Bookmarks.TYPE_FOLDER)
1135 subFolders.add(values.getAsInteger(Bookmarks._ID));
1136 }
1137 } finally {
1138 if (c != null)
1139 c.close();
1140 }
1142 // At this point is safe to assume that the mobile folder is
1143 // in the new table given that we've always created it on
1144 // database creation time.
1145 final int nInvalidSpecialEntries = invalidSpecialEntries.size();
1146 if (nInvalidSpecialEntries > 0) {
1147 Integer mobileFolderId = getMobileFolderId(db);
1148 if (mobileFolderId == null) {
1149 Log.e(LOGTAG, "Error migrating invalid special folder entries: mobile folder id is null");
1150 return;
1151 }
1153 debug("Found " + nInvalidSpecialEntries + " invalid special folder entries");
1154 for (int i = 0; i < nInvalidSpecialEntries; i++) {
1155 ContentValues values = invalidSpecialEntries.get(i);
1156 values.put(Bookmarks.PARENT, mobileFolderId);
1158 db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values);
1159 }
1160 }
1162 final int nSubFolders = subFolders.size();
1163 for (int i = 0; i < nSubFolders; i++) {
1164 int subFolderId = subFolders.get(i);
1165 migrateBookmarkFolder(db, subFolderId, migrator);
1166 }
1167 }
1169 private void migrateBookmarksTable(SQLiteDatabase db) {
1170 migrateBookmarksTable(db, null);
1171 }
1173 private void migrateBookmarksTable(SQLiteDatabase db, BookmarkMigrator migrator) {
1174 debug("Renaming bookmarks table to " + TABLE_BOOKMARKS_TMP);
1175 db.execSQL("ALTER TABLE " + TABLE_BOOKMARKS +
1176 " RENAME TO " + TABLE_BOOKMARKS_TMP);
1178 debug("Dropping views and indexes related to " + TABLE_BOOKMARKS);
1179 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES);
1181 db.execSQL("DROP INDEX IF EXISTS bookmarks_url_index");
1182 db.execSQL("DROP INDEX IF EXISTS bookmarks_type_deleted_index");
1183 db.execSQL("DROP INDEX IF EXISTS bookmarks_guid_index");
1184 db.execSQL("DROP INDEX IF EXISTS bookmarks_modified_index");
1186 createBookmarksTable(db);
1187 createBookmarksWithImagesView(db);
1189 createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID,
1190 R.string.bookmarks_folder_places, 0);
1192 migrateBookmarkFolder(db, Bookmarks.FIXED_ROOT_ID, migrator);
1194 // Ensure all special folders exist and have the
1195 // right folder hierarchy.
1196 createOrUpdateAllSpecialFolders(db);
1198 debug("Dropping bookmarks temporary table");
1199 db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKMARKS_TMP);
1200 }
1203 private void migrateHistoryTable(SQLiteDatabase db) {
1204 debug("Renaming history table to " + TABLE_HISTORY_TMP);
1205 db.execSQL("ALTER TABLE " + TABLE_HISTORY +
1206 " RENAME TO " + TABLE_HISTORY_TMP);
1208 debug("Dropping views and indexes related to " + TABLE_HISTORY);
1209 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES);
1210 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1212 db.execSQL("DROP INDEX IF EXISTS history_url_index");
1213 db.execSQL("DROP INDEX IF EXISTS history_guid_index");
1214 db.execSQL("DROP INDEX IF EXISTS history_modified_index");
1215 db.execSQL("DROP INDEX IF EXISTS history_visited_index");
1217 createHistoryTable(db);
1218 createHistoryWithImagesView(db);
1219 createCombinedWithImagesView(db);
1221 db.execSQL("INSERT INTO " + TABLE_HISTORY + " SELECT * FROM " + TABLE_HISTORY_TMP);
1223 debug("Dropping history temporary table");
1224 db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISTORY_TMP);
1225 }
1227 private void migrateImagesTable(SQLiteDatabase db) {
1228 debug("Renaming images table to " + TABLE_IMAGES_TMP);
1229 db.execSQL("ALTER TABLE " + Obsolete.TABLE_IMAGES +
1230 " RENAME TO " + TABLE_IMAGES_TMP);
1232 debug("Dropping views and indexes related to " + Obsolete.TABLE_IMAGES);
1233 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES);
1234 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1236 db.execSQL("DROP INDEX IF EXISTS images_url_index");
1237 db.execSQL("DROP INDEX IF EXISTS images_guid_index");
1238 db.execSQL("DROP INDEX IF EXISTS images_modified_index");
1240 createImagesTable(db);
1241 createHistoryWithImagesView(db);
1242 createCombinedWithImagesView(db);
1244 db.execSQL("INSERT INTO " + Obsolete.TABLE_IMAGES + " SELECT * FROM " + TABLE_IMAGES_TMP);
1246 debug("Dropping images temporary table");
1247 db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGES_TMP);
1248 }
1250 private void upgradeDatabaseFrom1to2(SQLiteDatabase db) {
1251 migrateBookmarksTable(db);
1252 }
1254 private void upgradeDatabaseFrom2to3(SQLiteDatabase db) {
1255 debug("Dropping view: " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES);
1256 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES);
1258 createBookmarksWithImagesView(db);
1260 debug("Dropping view: " + Obsolete.VIEW_HISTORY_WITH_IMAGES);
1261 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES);
1263 createHistoryWithImagesView(db);
1264 }
1266 private void upgradeDatabaseFrom3to4(SQLiteDatabase db) {
1267 migrateBookmarksTable(db, new BookmarkMigrator3to4());
1268 }
1270 private void upgradeDatabaseFrom4to5(SQLiteDatabase db) {
1271 createCombinedWithImagesView(db);
1272 }
1274 private void upgradeDatabaseFrom5to6(SQLiteDatabase db) {
1275 debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1276 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1278 createCombinedWithImagesView(db);
1279 }
1281 private void upgradeDatabaseFrom6to7(SQLiteDatabase db) {
1282 debug("Removing history visits with NULL GUIDs");
1283 db.execSQL("DELETE FROM " + TABLE_HISTORY + " WHERE " + History.GUID + " IS NULL");
1285 debug("Update images with NULL GUIDs");
1286 String[] columns = new String[] { Obsolete.Images._ID };
1287 Cursor cursor = null;
1288 try {
1289 cursor = db.query(Obsolete.TABLE_IMAGES, columns, Obsolete.Images.GUID + " IS NULL", null, null ,null, null, null);
1290 ContentValues values = new ContentValues();
1291 if (cursor.moveToFirst()) {
1292 do {
1293 values.put(Obsolete.Images.GUID, Utils.generateGuid());
1294 db.update(Obsolete.TABLE_IMAGES, values, Obsolete.Images._ID + " = ?", new String[] {
1295 cursor.getString(cursor.getColumnIndexOrThrow(Obsolete.Images._ID))
1296 });
1297 } while (cursor.moveToNext());
1298 }
1299 } finally {
1300 if (cursor != null)
1301 cursor.close();
1302 }
1304 migrateBookmarksTable(db);
1305 migrateHistoryTable(db);
1306 migrateImagesTable(db);
1307 }
1309 private void upgradeDatabaseFrom7to8(SQLiteDatabase db) {
1310 debug("Combining history entries with the same URL");
1312 final String TABLE_DUPES = "duped_urls";
1313 final String TOTAL = "total";
1314 final String LATEST = "latest";
1315 final String WINNER = "winner";
1317 db.execSQL("CREATE TEMP TABLE " + TABLE_DUPES + " AS" +
1318 " SELECT " + History.URL + ", " +
1319 "SUM(" + History.VISITS + ") AS " + TOTAL + ", " +
1320 "MAX(" + History.DATE_MODIFIED + ") AS " + LATEST + ", " +
1321 "MAX(" + History._ID + ") AS " + WINNER +
1322 " FROM " + TABLE_HISTORY +
1323 " GROUP BY " + History.URL +
1324 " HAVING count(" + History.URL + ") > 1");
1326 db.execSQL("CREATE UNIQUE INDEX " + TABLE_DUPES + "_url_index ON " +
1327 TABLE_DUPES + " (" + History.URL + ")");
1329 final String fromClause = " FROM " + TABLE_DUPES + " WHERE " +
1330 qualifyColumn(TABLE_DUPES, History.URL) + " = " +
1331 qualifyColumn(TABLE_HISTORY, History.URL);
1333 db.execSQL("UPDATE " + TABLE_HISTORY +
1334 " SET " + History.VISITS + " = (SELECT " + TOTAL + fromClause + "), " +
1335 History.DATE_MODIFIED + " = (SELECT " + LATEST + fromClause + "), " +
1336 History.IS_DELETED + " = " +
1337 "(" + History._ID + " <> (SELECT " + WINNER + fromClause + "))" +
1338 " WHERE " + History.URL + " IN (SELECT " + History.URL + " FROM " + TABLE_DUPES + ")");
1340 db.execSQL("DROP TABLE " + TABLE_DUPES);
1341 }
1343 private void upgradeDatabaseFrom8to9(SQLiteDatabase db) {
1344 createOrUpdateSpecialFolder(db, Bookmarks.READING_LIST_FOLDER_GUID,
1345 R.string.bookmarks_folder_reading_list, 5);
1347 debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1348 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1350 createCombinedWithImagesViewOn9(db);
1351 }
1353 private void upgradeDatabaseFrom9to10(SQLiteDatabase db) {
1354 debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1355 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1357 createCombinedWithImagesViewOn10(db);
1358 }
1360 private void upgradeDatabaseFrom10to11(SQLiteDatabase db) {
1361 debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1362 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1364 db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "("
1365 + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")");
1367 createCombinedWithImagesViewOn11(db);
1368 }
1370 private void upgradeDatabaseFrom11to12(SQLiteDatabase db) {
1371 debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1372 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1374 createCombinedViewOn12(db);
1375 }
1377 private void upgradeDatabaseFrom12to13(SQLiteDatabase db) {
1378 // Update images table with favicon URLs
1379 SQLiteDatabase faviconsDb = null;
1380 Cursor c = null;
1381 try {
1382 final String FAVICON_TABLE = "favicon_urls";
1383 final String FAVICON_URL = "favicon_url";
1384 final String FAVICON_PAGE = "page_url";
1386 String dbPath = mContext.getDatabasePath(Obsolete.FAVICON_DB).getPath();
1387 faviconsDb = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READONLY);
1388 String[] columns = new String[] { FAVICON_URL, FAVICON_PAGE };
1389 c = faviconsDb.query(FAVICON_TABLE, columns, null, null, null, null, null, null);
1390 int faviconIndex = c.getColumnIndexOrThrow(FAVICON_URL);
1391 int pageIndex = c.getColumnIndexOrThrow(FAVICON_PAGE);
1392 while (c.moveToNext()) {
1393 ContentValues values = new ContentValues(1);
1394 String faviconUrl = c.getString(faviconIndex);
1395 String pageUrl = c.getString(pageIndex);
1396 values.put(FAVICON_URL, faviconUrl);
1397 db.update(Obsolete.TABLE_IMAGES, values, Obsolete.Images.URL + " = ?", new String[] { pageUrl });
1398 }
1399 } catch (SQLException e) {
1400 // If we can't read from the database for some reason, we won't
1401 // be able to import the favicon URLs. This isn't a fatal
1402 // error, so continue the upgrade.
1403 Log.e(LOGTAG, "Exception importing from " + Obsolete.FAVICON_DB, e);
1404 } finally {
1405 if (c != null)
1406 c.close();
1407 if (faviconsDb != null)
1408 faviconsDb.close();
1409 }
1411 createFaviconsTable(db);
1413 // Import favicons into the favicons table
1414 db.execSQL("ALTER TABLE " + TABLE_HISTORY
1415 + " ADD COLUMN " + History.FAVICON_ID + " INTEGER");
1416 db.execSQL("ALTER TABLE " + TABLE_BOOKMARKS
1417 + " ADD COLUMN " + Bookmarks.FAVICON_ID + " INTEGER");
1419 try {
1420 c = db.query(Obsolete.TABLE_IMAGES,
1421 new String[] {
1422 Obsolete.Images.URL,
1423 Obsolete.Images.FAVICON_URL,
1424 Obsolete.Images.FAVICON,
1425 Obsolete.Images.DATE_MODIFIED,
1426 Obsolete.Images.DATE_CREATED
1427 },
1428 Obsolete.Images.FAVICON + " IS NOT NULL",
1429 null, null, null, null);
1431 while (c.moveToNext()) {
1432 long faviconId = -1;
1433 int faviconUrlIndex = c.getColumnIndexOrThrow(Obsolete.Images.FAVICON_URL);
1434 String faviconUrl = null;
1435 if (!c.isNull(faviconUrlIndex)) {
1436 faviconUrl = c.getString(faviconUrlIndex);
1437 Cursor c2 = null;
1438 try {
1439 c2 = db.query(TABLE_FAVICONS,
1440 new String[] { Favicons._ID },
1441 Favicons.URL + " = ?",
1442 new String[] { faviconUrl },
1443 null, null, null);
1444 if (c2.moveToFirst()) {
1445 faviconId = c2.getLong(c2.getColumnIndexOrThrow(Favicons._ID));
1446 }
1447 } finally {
1448 if (c2 != null)
1449 c2.close();
1450 }
1451 }
1453 if (faviconId == -1) {
1454 ContentValues values = new ContentValues(4);
1455 values.put(Favicons.URL, faviconUrl);
1456 values.put(Favicons.DATA, c.getBlob(c.getColumnIndexOrThrow(Obsolete.Images.FAVICON)));
1457 values.put(Favicons.DATE_MODIFIED, c.getLong(c.getColumnIndexOrThrow(Obsolete.Images.DATE_MODIFIED)));
1458 values.put(Favicons.DATE_CREATED, c.getLong(c.getColumnIndexOrThrow(Obsolete.Images.DATE_CREATED)));
1459 faviconId = db.insert(TABLE_FAVICONS, null, values);
1460 }
1462 ContentValues values = new ContentValues(1);
1463 values.put(FaviconColumns.FAVICON_ID, faviconId);
1464 db.update(TABLE_HISTORY, values, History.URL + " = ?",
1465 new String[] { c.getString(c.getColumnIndexOrThrow(Obsolete.Images.URL)) });
1466 db.update(TABLE_BOOKMARKS, values, Bookmarks.URL + " = ?",
1467 new String[] { c.getString(c.getColumnIndexOrThrow(Obsolete.Images.URL)) });
1468 }
1469 } finally {
1470 if (c != null)
1471 c.close();
1472 }
1474 createThumbnailsTable(db);
1476 // Import thumbnails into the thumbnails table
1477 db.execSQL("INSERT INTO " + TABLE_THUMBNAILS + " ("
1478 + Thumbnails.URL + ", "
1479 + Thumbnails.DATA + ") "
1480 + "SELECT " + Obsolete.Images.URL + ", " + Obsolete.Images.THUMBNAIL
1481 + " FROM " + Obsolete.TABLE_IMAGES
1482 + " WHERE " + Obsolete.Images.THUMBNAIL + " IS NOT NULL");
1484 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES);
1485 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES);
1486 db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES);
1487 db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED);
1489 createBookmarksWithFaviconsView(db);
1490 createHistoryWithFaviconsView(db);
1491 createCombinedViewOn13(db);
1493 db.execSQL("DROP TABLE IF EXISTS " + Obsolete.TABLE_IMAGES);
1494 }
1496 private void upgradeDatabaseFrom13to14(SQLiteDatabase db) {
1497 createOrUpdateSpecialFolder(db, Bookmarks.PINNED_FOLDER_GUID,
1498 R.string.bookmarks_folder_pinned, 6);
1499 }
1501 private void upgradeDatabaseFrom14to15(SQLiteDatabase db) {
1502 Cursor c = null;
1503 try {
1504 // Get all the pinned bookmarks
1505 c = db.query(TABLE_BOOKMARKS,
1506 new String[] { Bookmarks._ID, Bookmarks.URL },
1507 Bookmarks.PARENT + " = ?",
1508 new String[] { Integer.toString(Bookmarks.FIXED_PINNED_LIST_ID) },
1509 null, null, null);
1511 while (c.moveToNext()) {
1512 // Check if this URL can be parsed as a URI with a valid scheme.
1513 String url = c.getString(c.getColumnIndexOrThrow(Bookmarks.URL));
1514 if (Uri.parse(url).getScheme() != null) {
1515 continue;
1516 }
1518 // If it can't, update the URL to be an encoded "user-entered" value.
1519 ContentValues values = new ContentValues(1);
1520 String newUrl = Uri.fromParts("user-entered", url, null).toString();
1521 values.put(Bookmarks.URL, newUrl);
1522 db.update(TABLE_BOOKMARKS, values, Bookmarks._ID + " = ?",
1523 new String[] { Integer.toString(c.getInt(c.getColumnIndexOrThrow(Bookmarks._ID))) });
1524 }
1525 } finally {
1526 if (c != null) {
1527 c.close();
1528 }
1529 }
1530 }
1532 private void upgradeDatabaseFrom15to16(SQLiteDatabase db) {
1533 db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED);
1534 db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED_WITH_FAVICONS);
1536 createCombinedViewOn16(db);
1537 }
1539 private void upgradeDatabaseFrom16to17(SQLiteDatabase db) {
1540 // Purge any 0-byte favicons/thumbnails
1541 try {
1542 db.execSQL("DELETE FROM " + TABLE_FAVICONS +
1543 " WHERE length(" + Favicons.DATA + ") = 0");
1544 db.execSQL("DELETE FROM " + TABLE_THUMBNAILS +
1545 " WHERE length(" + Thumbnails.DATA + ") = 0");
1546 } catch (SQLException e) {
1547 Log.e(LOGTAG, "Error purging invalid favicons or thumbnails", e);
1548 }
1549 }
1551 /*
1552 * Moves reading list items from 'bookmarks' table to 'reading_list' table. Uses the
1553 * same item GUID.
1554 */
1555 private void upgradeDatabaseFrom17to18(SQLiteDatabase db) {
1556 debug("Moving reading list items from 'bookmarks' table to 'reading_list' table");
1558 final String selection = Bookmarks.PARENT + " = ? AND " + Bookmarks.IS_DELETED + " = ? ";
1559 final String[] selectionArgs = { String.valueOf(Bookmarks.FIXED_READING_LIST_ID), "0" };
1560 final String[] projection = { Bookmarks._ID,
1561 Bookmarks.GUID,
1562 Bookmarks.URL,
1563 Bookmarks.DATE_MODIFIED,
1564 Bookmarks.DATE_CREATED,
1565 Bookmarks.TITLE };
1566 Cursor cursor = null;
1567 try {
1568 // Start transaction
1569 db.beginTransaction();
1571 // Create 'reading_list' table
1572 createReadingListTable(db);
1574 // Get all the reading list items from bookmarks table
1575 cursor = db.query(TABLE_BOOKMARKS, projection, selection, selectionArgs,
1576 null, null, null);
1578 // Insert reading list items into reading_list table
1579 while (cursor.moveToNext()) {
1580 debug(DatabaseUtils.dumpCurrentRowToString(cursor));
1581 ContentValues values = new ContentValues();
1582 DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.URL, values, ReadingListItems.URL);
1583 DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.GUID, values, ReadingListItems.GUID);
1584 DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.TITLE, values, ReadingListItems.TITLE);
1585 DatabaseUtils.cursorLongToContentValues(cursor, Bookmarks.DATE_CREATED, values, ReadingListItems.DATE_CREATED);
1586 DatabaseUtils.cursorLongToContentValues(cursor, Bookmarks.DATE_MODIFIED, values, ReadingListItems.DATE_MODIFIED);
1588 db.insertOrThrow(TABLE_READING_LIST, null, values);
1589 }
1591 // Delete reading list items from bookmarks table
1592 db.delete(TABLE_BOOKMARKS,
1593 Bookmarks.PARENT + " = ? ",
1594 new String[] { String.valueOf(Bookmarks.FIXED_READING_LIST_ID) });
1596 // Delete reading list special folder
1597 db.delete(TABLE_BOOKMARKS,
1598 Bookmarks._ID + " = ? ",
1599 new String[] { String.valueOf(Bookmarks.FIXED_READING_LIST_ID) });
1600 // Done
1601 db.setTransactionSuccessful();
1603 } catch (SQLException e) {
1604 Log.e(LOGTAG, "Error migrating reading list items", e);
1605 } finally {
1606 if (cursor != null) {
1607 cursor.close();
1608 }
1609 db.endTransaction();
1610 }
1611 }
1613 @Override
1614 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
1615 debug("Upgrading browser.db: " + db.getPath() + " from " +
1616 oldVersion + " to " + newVersion);
1618 // We have to do incremental upgrades until we reach the current
1619 // database schema version.
1620 for (int v = oldVersion + 1; v <= newVersion; v++) {
1621 switch(v) {
1622 case 2:
1623 upgradeDatabaseFrom1to2(db);
1624 break;
1626 case 3:
1627 upgradeDatabaseFrom2to3(db);
1628 break;
1630 case 4:
1631 upgradeDatabaseFrom3to4(db);
1632 break;
1634 case 5:
1635 upgradeDatabaseFrom4to5(db);
1636 break;
1638 case 6:
1639 upgradeDatabaseFrom5to6(db);
1640 break;
1642 case 7:
1643 upgradeDatabaseFrom6to7(db);
1644 break;
1646 case 8:
1647 upgradeDatabaseFrom7to8(db);
1648 break;
1650 case 9:
1651 upgradeDatabaseFrom8to9(db);
1652 break;
1654 case 10:
1655 upgradeDatabaseFrom9to10(db);
1656 break;
1658 case 11:
1659 upgradeDatabaseFrom10to11(db);
1660 break;
1662 case 12:
1663 upgradeDatabaseFrom11to12(db);
1664 break;
1666 case 13:
1667 upgradeDatabaseFrom12to13(db);
1668 break;
1670 case 14:
1671 upgradeDatabaseFrom13to14(db);
1672 break;
1674 case 15:
1675 upgradeDatabaseFrom14to15(db);
1676 break;
1678 case 16:
1679 upgradeDatabaseFrom15to16(db);
1680 break;
1682 case 17:
1683 upgradeDatabaseFrom16to17(db);
1684 break;
1686 case 18:
1687 upgradeDatabaseFrom17to18(db);
1688 break;
1689 }
1690 }
1692 // If an upgrade after 12->13 fails, the entire upgrade is rolled
1693 // back, but we can't undo the deletion of favicon_urls.db if we
1694 // delete this in step 13; therefore, we wait until all steps are
1695 // complete before removing it.
1696 if (oldVersion < 13 && newVersion >= 13
1697 && mContext.getDatabasePath(Obsolete.FAVICON_DB).exists()
1698 && !mContext.deleteDatabase(Obsolete.FAVICON_DB)) {
1699 throw new SQLException("Could not delete " + Obsolete.FAVICON_DB);
1700 }
1701 }
1703 @Override
1704 public void onOpen(SQLiteDatabase db) {
1705 debug("Opening browser.db: " + db.getPath());
1707 Cursor cursor = null;
1708 try {
1709 cursor = db.rawQuery("PRAGMA foreign_keys=ON", null);
1710 } finally {
1711 if (cursor != null)
1712 cursor.close();
1713 }
1714 cursor = null;
1715 try {
1716 cursor = db.rawQuery("PRAGMA synchronous=NORMAL", null);
1717 } finally {
1718 if (cursor != null)
1719 cursor.close();
1720 }
1722 // From Honeycomb on, it's possible to run several db
1723 // commands in parallel using multiple connections.
1724 if (Build.VERSION.SDK_INT >= 11) {
1725 db.enableWriteAheadLogging();
1726 db.setLockingEnabled(false);
1727 } else {
1728 // Pre-Honeycomb, we can do some lesser optimizations.
1729 cursor = null;
1730 try {
1731 cursor = db.rawQuery("PRAGMA journal_mode=PERSIST", null);
1732 } finally {
1733 if (cursor != null)
1734 cursor.close();
1735 }
1736 }
1737 }
1739 static final String qualifyColumn(String table, String column) {
1740 return DBUtils.qualifyColumn(table, column);
1741 }
1743 // Calculate these once, at initialization. isLoggable is too expensive to
1744 // have in-line in each log call.
1745 private static boolean logDebug = Log.isLoggable(LOGTAG, Log.DEBUG);
1746 private static boolean logVerbose = Log.isLoggable(LOGTAG, Log.VERBOSE);
1747 protected static void trace(String message) {
1748 if (logVerbose) {
1749 Log.v(LOGTAG, message);
1750 }
1751 }
1753 protected static void debug(String message) {
1754 if (logDebug) {
1755 Log.d(LOGTAG, message);
1756 }
1757 }
1759 private Integer getMobileFolderId(SQLiteDatabase db) {
1760 Cursor c = null;
1762 try {
1763 c = db.query(TABLE_BOOKMARKS,
1764 mobileIdColumns,
1765 Bookmarks.GUID + " = ?",
1766 mobileIdSelectionArgs,
1767 null, null, null);
1769 if (c == null || !c.moveToFirst())
1770 return null;
1772 return c.getInt(c.getColumnIndex(Bookmarks._ID));
1773 } finally {
1774 if (c != null)
1775 c.close();
1776 }
1777 }
1779 private long insertFavicon(SQLiteDatabase db, ContentValues values) {
1780 // This method is a dupicate of BrowserProvider.insertFavicon.
1781 // If changes are needed, please update both
1782 String faviconUrl = values.getAsString(Favicons.URL);
1783 String pageUrl = null;
1784 long faviconId;
1786 trace("Inserting favicon for URL: " + faviconUrl);
1788 DBUtils.stripEmptyByteArray(values, Favicons.DATA);
1790 // Extract the page URL from the ContentValues
1791 if (values.containsKey(Favicons.PAGE_URL)) {
1792 pageUrl = values.getAsString(Favicons.PAGE_URL);
1793 values.remove(Favicons.PAGE_URL);
1794 }
1796 // If no URL is provided, insert using the default one.
1797 if (TextUtils.isEmpty(faviconUrl) && !TextUtils.isEmpty(pageUrl)) {
1798 values.put(Favicons.URL, org.mozilla.gecko.favicons.Favicons.guessDefaultFaviconURL(pageUrl));
1799 }
1801 long now = System.currentTimeMillis();
1802 values.put(Favicons.DATE_CREATED, now);
1803 values.put(Favicons.DATE_MODIFIED, now);
1804 faviconId = db.insertOrThrow(TABLE_FAVICONS, null, values);
1806 if (pageUrl != null) {
1807 ContentValues updateValues = new ContentValues(1);
1808 updateValues.put(FaviconColumns.FAVICON_ID, faviconId);
1809 db.update(TABLE_HISTORY,
1810 updateValues,
1811 History.URL + " = ?",
1812 new String[] { pageUrl });
1813 db.update(TABLE_BOOKMARKS,
1814 updateValues,
1815 Bookmarks.URL + " = ?",
1816 new String[] { pageUrl });
1817 }
1819 return faviconId;
1820 }
1822 private interface BookmarkMigrator {
1823 public void updateForNewTable(ContentValues bookmark);
1824 }
1826 private class BookmarkMigrator3to4 implements BookmarkMigrator {
1827 @Override
1828 public void updateForNewTable(ContentValues bookmark) {
1829 Integer isFolder = bookmark.getAsInteger("folder");
1830 if (isFolder == null || isFolder != 1) {
1831 bookmark.put(Bookmarks.TYPE, Bookmarks.TYPE_BOOKMARK);
1832 } else {
1833 bookmark.put(Bookmarks.TYPE, Bookmarks.TYPE_FOLDER);
1834 }
1836 bookmark.remove("folder");
1837 }
1838 }
1839 }
